Kexi Scripting Handbook

Table of Contents    Introduction      Handbook      Script packages      How to create a script package      Projectscripts    Examples      Hello world      Import      GUI      KexiDB: Print table values      KexiDB: Strip whitespaces      KexiDB: Print data from external projectfile.

Introduction

Kexi uses the Kross Scripting Framework to provide support for scripting with Python and Ruby. The scripting support is a Technology Preview (BETA) version and may change in details in the next Kexi version.

Handbook

•  KrossKexiDB Reference Manual

Script packages

Script packages extend the functionality Kexi provides. They are plugins written in a scripting language and are displayed in the Tools/Scripts menu of Kexi.

The Script Manager, reachable with the Tools/Script Manager... menuitem, enables installing or uninstalling such script packages. Installing of new script packages or updating of existing ones could be archived in two ways. First one is to download a Kexi Script from kde-files.org and install the downloaded tarball (button Install in the Script Manager). The second way is to use the Get More Scripts functionality provided at the Script Manager to download and install script packages over the internet.

Kexi comes with a bunch of pre-installed script packages like for example

• "Export Data to XHTML File" allows to export a table or a query to a XHTML-file.
• "Import Data From XHTML File" allows to import the content of such a XHTML-file into an existing table.
• "Project Documentation Generator" creates a HTML-file that contains some informations about the loaded project.

The Get More Scripts functionality offers more script-packages like for example

• CopyCenter python script to copy data between database backends. The flexible plugin-architecture allows transparent copies between different backends.
• WebServer python script that provides HTTP-Servers to browse via a webbrowser through KexiDB.
• QueryTool python script to query data from a KexiDB datasource and view it.

How to create a script package

The following example shows how I created the ExportText script package.

1. I wrote a python-script named ExportText.py. The script exports the content of a KexiDB table or a query datasource to a textfile. The script will be now be packaged as script package and uploaded to the section of Kexi-scripts http://www.kde-files.org/index.php?xcontentmode=617

2. Prepare rc-file Each script package needs at least one *.rc file that defines the action(s) the script-package provides. Such rc-files are simple XML-files used by Kexi to have details about the scripts. For our ExportText.py I named the rc-file ExportText.rc and it looks like;

   <KrossScripting>
      <ScriptAction
          name="exporttext"
          version="0"
          text="Export Data to Text File"
          description="Export a table- or query-datasource to a Text File."
          icon="fileexport"
          interpreter="python"
          file="ExportText.py" />
   </KrossScripting>

The name is used internaly as unique identifier. If there are more then one ScriptAction with the same name, the version number ("0" if not defined) is used to define which one is newer and only the newer one will be used then. The text is the string displayed as caption while the description is a more detailed text to describe the action. The interpreter defines which interpreter-backend (e.g. "python" or "ruby") should be used to execute the scriptfile defined with file. If the interpreter isn't supported, not installed or disabled the action will be disabled.

3. Create a gzipped tarball that contains the ExportText.py and the ExportText.rc files. You may like to put more scriptfiles and/or rc-files into a single script package.

4. We need to sign the just created exporttext.tar.gz tarball now with our own secret key.

     # First let's look what keys you have;
     gpg --list-secret-keys
     # Let's create the signature file;
     gpg --detach-sign exporttext.tar.gz
     # The signaturefile needs to be named 'signature';
     mv exporttext.tar.gz.sig signature
     # Create a md5sum of the tarball. Needs to be named 'md5sum'.
     md5sum exporttext.tar.gz >md5sum

5. We create the final script package now by creating a new gzipped tarball that contains all of our files. The resulting archivefile is then our final script package we are able to distribute. So, in our example the final finalscriptpackage.tar.gz-file looks like;

     finalscriptpackage.tar.gz
       + exporttext.tar.gz
         + ExportText.py
         + ExportText.rc
       + md5sum
       + signature

6. Upload the script package tar.gz-file to http://www.kde-files.org/index.php?xcontentmode=617

Projectscripts

Projectscripts are scripts embedded into and bind to a project rather then standalone script packages. They normaly depend on the content of a project and should be distributed as part of the project.

Kexi provides with the Insert/Script menuitem a way to add new projectscripts into your loaded project. Such projectscripts are stored together with the project and can be modified with the Script Editor.

Examples

To try the following scripting examples out, just create within Kexi a script module (In the mainmenu choose "Insert" and "Script..."), put the scriptingcode in an press the "Execute". To test it may an idea to start Kexi from the commandline (Konsole) to be able to read all the output the scripting-code produces.

Hello world

This little example just prints some text to stdout. The variable name contains the name of the scriptmodule while doc is our inline scriptmodule documentation.

  """ This is the script module inline documentation. """
  try:
    print "Hello world from name '%s'" % name
    print "documentation = %s" % doc
  except:
    print "Exception in python script."

Import

This example shows, that we are for sure able to import other external python modules and work with them from within the scripting code.

  def testfunc(name):
    print "testing function called; %s / %s" % (name,name)
    import os
    return str( dir(os) )
  print testfunc(name)

GUI

This example uses the TKinter GUI-toolkit to create a simple hello world dialog. That way you are able to display a messagebox to the user, get userinput or just display some informations. For sure you are not limited to TKInter. PyQt and PyKDE are working as well.

  from Tkinter import *
  root = Tk()
  w = Label(root, text="Hello, world!")
  w.pack()
  root.mainloop()

A Hello-World dialog displayed with qtruby.

  require 'Qt'
  dialog = Qt::Dialog.new(nil,nil,true,Qt::WDestructiveClose)
  dialog.setCaption('Test')
  box = Qt::VBox.new(dialog)
  box.setMargin(10)
  Qt::Label.new('Hello World.', box)
  Qt::PushButton.new('Hello World!', box)
  box.setMinimumSize( box.sizeHint() )
  dialog.exec()

KexiDB: Print table values

The following example python code uses the Kross KexiDB and the Kross KexiApp modules to iterate over a table and print the values within the currently in Kexi loaded project.

  # Use the KrossKexiApp module to get the current connection.
  import krosskexiapp
  mainwindow = krosskexiapp.get("KexiAppMainWindow")
  connection = mainwindow.getConnection()
  # Execute SQL select-statement.
  cursor = connection.executeQueryString("SELECT * FROM emp;")
  if not cursor:
    raise "Failed to execute query: %s" % connection.lastError()
  # Iterate over the table with the cursor.
  ok = cursor.moveFirst()
  while ok:
    for i in range( cursor.fieldCount() ):
      print "%s" % cursor.value(i)
    ok = cursor.moveNext()

KexiDB: Strip whitespaces

The following example python code uses the Kross KexiDB module to strip all beginning and ending whitespaces from all values in a table.

  # Use the KrossKexiApp module to get the current connection.
  import krosskexiapp
  mainwindow = krosskexiapp.get("KexiAppMainWindow")
  connection = mainwindow.getConnection()
  # Fetch the table "emp"
  table = connection.tableSchema("emp")
  # Create a cursor and iterate over the table.
  cursor = connection.executeQuerySchema( table.query() )
  if not cursor:
    raise("Query failed")
  while(not cursor.eof()):
    # We like to look at each field. If the value the field has
    # has leading or following whitespaces, just strip them.
    for i in range( cursor.fieldCount() ):
      v = str( cursor.value(i) )
      if v.startswith(' ') or v.endswith(' '):
        cursor.setValue(i, v.strip())
    cursor.moveNext()
  # Finally save all changes done on the cursor.
  if not cursor.save():
    raise "Failed to save changes"

KexiDB: Print data from external projectfile.

The following example python code uses the Kross KexiDB module to print the content of a table located within a SQLite projectfile to stdout.

  # Import the KrossKexiDB module.
  import krosskexidb
  # The drivermanager provides us access to the KexiDB framework.
  drivermanager = krosskexidb.DriverManager()
  # Create the connectiondata by using the definition in the Kexi projectfile.
  connectiondata = drivermanager.createConnectionDataByFile("/path/file.kexi")
  # Create a driver. In our case it will be a SQLite-driver cause the
  # file above is a filebased SQLite database.
  driver = drivermanager.driver( connectiondata.driverName() )
  # Create a connection, try to connect and use the database.
  connection = driver.createConnection(connectiondata)
  if not connection.connect(): raise "Failed to connect"
  if not connection.useDatabase( connectiondata.databaseName() ):
    raise "Failed to use database"
  # Execute SQL select-statement.
  cursor = connection.executeQueryString("SELECT * FROM emp;")
  if not cursor:
    raise "Failed to execute query: %s" % connection.lastError()
  # Iterate over the table with the cursor.
  ok = cursor.moveFirst()
  while ok:
    for i in range( cursor.fieldCount() ):
      print "%s" % cursor.value(i)
    ok = cursor.moveNext()