MikeTeo.net

A Software Technologist's Blog (Wanna Email Me?)

PostgreSQL Extension for CLIPS Expert System

October 30, 2011 By miketeo

The PostgreSQL extension for CLIPS Expert System provides access to a PostgreSQL database from the CLIPS environment by exposing a number of CLIPS functions. CLIPS users who are familiar with SQL should not have much difficulties in learning how to use the additional functions to interact with the database from the CLIPS environment.

The PostgreSQL extension for CLIPS Expert System has been released to the public as public domain software on Oct 2011.

Installation for CLIPS version 6.24

You need to install the development files for PostgreSQL, and have some experience in compiling and linking software from source. If you are using RPMs, you will need to install the postgresql-devel RPM.

Manual Patching

  1. Download the PostgreSQL extension patch for CLIPS
  2. Download the CLIPS source from sourceforge and extract the contents to your local machine.
  3. Change your current working folder to CLIPSSrc/CLIPSSrc. You should see a bunch of .c and .h files there.
  4. Run patch -p1 < clips-6.24-postgresql.diff
    This will patch sysdep.c, and generate pgext.h and pgext.c
  5. Now compile CLIPS. You will need to specify -I<path to postgresql include> for your CFLAGS in the gcc compile command line, and -lpq -L<path to postgresql libs> for your LDFLAGS in the gcc link command line.

Skipping the Patch

If you want to skip the patching procedures, you can download the post-patched version of CLIPS here. Then extract the contents and follow step 5.

Pre-compiled Win32 Binary

A pre-compiled Win32 binary can be downloaded here. It is linked against postgresql 9.1.3.

Installation for pyClips

You need to install the development files for PostgreSQL, and have some experience in building python modules from source. If you are using RPMs, you will need to install the postgresql-devel RPM.

Manual Patching

  1. Download the PostgreSQL extension patch for pyclips
  2. Download the source for pyclips from sourceforge and extract the contents to your local machine.
  3. Run python setup.py config
    This will download the latest CLIPS source for you
  4. At the same folder where the setup.py resides,
    run patch -p1 < pyclips-1.0.7-postgresql.diff
    This will patch setup.py and sysdep.c, and also generate pgext.h and pgext.c
    If your postgresql include files and libraries are not installed in common system folders (like /usr/include and /usr/lib), please modify the setup() command in the setup.py accordingly. More details can be found in the patched setup.py.
  5. Run python setup.py build
  6. Run python setup.py install

Skipping the Patch

If you want to skip the patching procedures, you can download the post-patched version of pyclips here. Then extract the contents and follow step 5 and 6.

List of PostgreSQL Functions for CLIPS

Showing License

A simple function which shows the “license” for this PostgreSQL extension.

Syntax: (pg-license)

Connect to PostgreSQL Database

Establishes a database connection to the PostgreSQL database for the current CLIPS environment. Each CLIPS environment maintains its own database connection, so you can establish different connections to different databases in each environment. This function must be called before calling pg-count and pg-exec. If there is an existing connection, it will be closed first before the new connection is attempted.

The connection string must a CLIPS string containing information on the target PostgreSQL service. It can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = ‘a value’. Single quotes and backslashes within the value must be escaped with a backslash, i.e., \’ and \\. Examples of commonly used keywords are host, port, dbname, user and password.

Returns the symbol TRUE if connection is successfully established, or symbol FALSE if an error has occurred. Details on the connection error will be printed to the CLIPS error stream.

Syntax: (pg-connect <postgresql-connection-string>)

Showing Database Connection String

Returns the database string which is passed in the latest call to pg-connect function, or the symbol FALSE if pg-connect has not been called yet.

Syntax: (pg-conninfo)

Execute and Return the Number of Rows in Query

Execute a parameterized SQL SELECT query  on the database and returns an integer indicating the number of rows returned from the query. If there is an error executing the query, the symbol FALSE is returned. Details on the connection error will be printed to the CLIPS error stream.

This function is similar to pg-exec except that it focuses on the row count and does not require pg-closeresult for cleanup. pg-count is targeted for use in LHS of rules; usually to check for existence of matching rows. Henceforth, the query should be a SELECT SQL query.

To provide parameters to the SQL query, your SQL query string must contain the placeholders from $1, $2, etc, and provide the parameter values to the pg-count function. pg-count function accepts parameter values of type string, symbol, integer and float.

Syntax: (pg-count <SQL-string> <SQL-parameter>*)

Executing Query for Result Retrieval

Execute a parameterized SQL query on the database. This function can be used to execute common forms of SQL query like SELECT, INSERT, UPDATE and DELETE.

To provide parameters to the SQL query, your SQL query string must contain the placeholders from $1, $2, etc, and provide the parameter values in the pg-exec function call. pg-exec function call accepts parameter values of type string, symbol, integer and float.

pg-exec returns the following result type:

  • An external-address for SQL queries which return rows like SELECT. The external-address can be passed to pg-fetchrow, pg-rowcount and pg-fieldcount to retrieve information about the returned rows. After use, call pg-closeresult to release the resources held by the query result.
  • Symbol TRUE if the SQL query does not return any SQL rows (for example, a DELETE, INSERT or UPDATE SQL statement)
  • Symbol FALSE if an error has occurred. Details on the connection error will be printed to the CLIPS error stream.

Fetching One Row From Query Result

Retrieves one row of information from the query result returned from the pg-exec function call. The column values are converted to the nearest matching CLIPS data type, e.g. SQL integer to CLIPS integer, SQL float to CLIPS float. Certain SQL types like INET, Point are returned as CLIPS string. The row-number is an integer (starting from 1) reflecting the row you want to retrieve.

Returns a multifield with its fields populated with the column values of the selected row, or symbol FALSE if the provided row-number exceeds the number of rows returned in the query result.

Syntax: (pg-fetchrow <external-address-from-pg-exec> <row-number>)

Returning the Number of Rows in Query Result

Returns an integer indicating the number of rows in the query result. pg-rowcount accepts only one parameter which must be the external-address result returned from a pg-exec function call. If other parameter types are passed in, the function will return -1.

Syntax: (pg-rowcount <external-address-from-pg-exec>)

Returning the Number of Fields/Columns in Query Result

Returns an integer indicating the number of fields/columns in the query result. pg-fieldcount accepts only one parameter which must be the external-address result returned from a pg-exec function call. If other parameter types are passed in, the function will return -1.

Syntax: (pg-fieldcount <external-address-from-pg-exec>)

Closing Query Result

Releases the memory resources held by the query result returned from a previous pg-exec function call. Once the query result is closed, it will be invalid and must not be used again in other function calls.

pg-closeresult must be called once and only once for any external-address that is returned from the pg-exec function call; calling it multiple times for the same external-address may cause CLIPS to crash. It is recommended that you call pg-closeresult once you are done processing all the rows in the query result.

Syntax: (pg-closeresult <external-address-from-pg-exec>)

Closing Database Connection

Closes the PostgreSQL datatabase connection if it is open. The function does not do anything if there is no open database connection. Note that subsequent calls to pg-count or pg-exec will automatically re-establish the database connection using the current database connection string. It is recommended that database connection be closed if it will be not utilized for a prolonged period of time.

This function does not return anything.

Syntax: (pg-close)

Example

CLIPS> (pg-connect "dbname=postgres port=5432 user=postgres password=")
TRUE
CLIPS>
(defrule print-table-info
  (input ?table-name)
  (test (> (pg-count "SELECT 1 FROM pg_class WHERE relname=$1" ?table-name) 0))
=>
  (bind ?result (pg-exec "SELECT relpages,reltuples FROM pg_class WHERE relname=$1" ?table-name))
  (bind ?rowno 1)
  (while TRUE do
    (bind ?row (pg-fetchrow ?result ?rowno))
    (if (neq ?row FALSE)
     then
      (printout t "Row " ?rowno ": " (nth$ 1 ?row) " pages, " (nth$ 2 ?row) " rows" crlf)
      (bind ?rowno (+ ?rowno 1))
     else
      (printout t "All rows returned" crlf)
      (break)
    )
  )
  (pg-closeresult ?result)
)
CLIPS> (assert (input "pg_attribute"))
<Fact-1>
CLIPS> (run)
Row 1: 23 pages, 4300.0 rows
All rows returned

 

 

 

  1. PostgreSQL Extension for CLIPS Expert System « DbRunas – Noticias y Recursos sobre Bases de Datos Said,

    […] http://miketeo.net/wp/index.php/projects/clips-postgresql October 30, 2011   //   PostgreSQL   //   No Comments   //   […]

  2. Yurii Rashkovskii Said,

    FYI ? I needed to change ‘i’ return type in rowcount and fieldcount declarations to ‘u’ to make them return correct results

  3. Umit Karabiyik Said,

    Hi Mike,

    This is a great post. Thanks for such a great work. I’ve been trying to apply this patch to my system but I am having trouble with the makefile. Would mind to send me or publish a makefile example so that we can make sure we did everything properly. I am basically stacked at stage 5 for compiling.

    Thanks in advance,
    Umit

  4. miketeo Said,

    @Umit:

    I’m assuming you have installed postgresql in /usr/local/pgsql.

    $> gcc -I /usr/local/pgsql/include -c *.c
    $> gcc -L /usr/local/pgsql/lib -lpq -lm -o clips *.o

    These are the most basic commands. If your postgresql installation utilizes additional libraries like openssl, then you may need to add more -l arguments to the 2nd gcc command. You can run “pg_config –libs” to find out what additional libraries are needed.

Add A Comment