|
|
Sample ApplicationThis page gives a brief tutorial on the use of the librta by building a trivial application. We define the task, define the tables, give the code, describe how to build and link the application, and describe how to the test the application using the database interface. Problem StatementSay you want to build an application that has a configuration table that you want to make visible to various UI front-ends. The table is an array of data structures with twenty rows where each row is an instance of a data structure that has a writeable integer, a writeable float, a writeable string, and a read-only string. Both strings are thirty characters in length. A callback on the writeable string does two things: it replaces any '<' and '>' characters with a '.', and it copies the reversed string into the the read-only string. The structure is defined as:
#define NOTE_LEN 30
struct MyData {
int myint;
float myfloat;
char notes[NOTE_LEN];
char seton[NOTE_LEN];
};
Allocate storage for the data: #define ROW_COUNT 20 struct MyData mydata[ROW_COUNT]; Externally, the array of structures to be seen as a Postgres table called "mytable". Table DefinitionsYou need to tell the librta about the table. To do this you need to build an RTA_COLDEF structure for each of the four columns, and to build a RTA_TBLDEF structure for the array of structures. Use an array of four RTA_COLDEFs to describe your columns. This is pretty simple (but can get tedious if you have lots of columns).
RTA_COLDEF mycolumns[] = {
{
"mytable", /* the table name */
"myint", /* the column name */
RTA_INT, /* it is an integer */
sizeof(int), /* number of bytes */
offsetof(struct MyData, myint), /* location in struct */
0, /* no flags */
(int (*)()) 0, /* called before read */
(int (*)()) 0, /* called after write */
"A sample integer in a table"
},
{
"mytable", /* the table name */
"myfloat", /* the column name */
RTA_FLOAT, /* it is a float */
sizeof(float), /* number of bytes */
offsetof(struct MyData, myfloat), /* location in struct */
0, /* no flags */
(int (*)()) 0, /* called before read */
(int (*)()) 0, /* called after write */
"A sample float in a table"
},
{
"mytable", /* the table name */
"notes", /* the column name */
RTA_STR, /* it is a string */
NOTE_LEN, /* number of bytes */
offsetof(struct MyData, notes), /* location in struct */
0, /* no flags */
(int (*)()) 0, /* called before read */
reverse_str, /* called after write */
"A sample note string in a table"
},
{
"mytable", /* the table name */
"seton", /* the column name */
RTA_STR, /* it is a string */
NOTE_LEN, /* number of bytes */
offsetof(struct MyData, seton), /* location in struct */
RTA_READONLY, /* a read-only column */
(int (*)()) 0, /* called before read */
(int (*)()) 0, /* called after write */
"Another sample note string in a table"
},
};
For each of the four structure elements you have to give the associated table name, column name, type, size, position in the struct, flags, read and write callbacks, and a short string to describe it. Define the table in a similar way:
RTA_TBLDEF mytbldef {
"mytable", /* table name */
mydata, /* address of table */
sizeof(struct MyData), /* length of each row */
ROW_COUNT, /* number of rows */
mycolumns, /* array of column defs */
sizeof(mycolumns) / sizeof(RTA_COLDEF), /* number of columns */
"", /* no save file */
"A sample table"
};
Note the double quotes to specify no save file. This field is a pointer-to-string and the pointer can not be null, although the string can be. Database InterfaceC CodeThe full source code for this simple application is available in the .../test directory in the package source. The first section of code of note is the list of include files: #include <stdio.h> #include <stdlib.h> #include <stddef.h> /* for 'offsetof' */ #include <string.h> /* for 'strlen' */ #include <unistd.h> /* for 'read/write/close' */ #include <sys/socket.h> #include <netinet/in.h> #include "../src/rta.h" You will need to set the path to the rta.h file to reflect where you the librta include files. The main() routine starts by initializing the table and calling rta_init() and rta_addtable() to let librta know about the table and about its column attributes.
int main()
{
int i; /* a loop counter */
int srvfd; /* FD for our server socket */
int connfd; /* FD for conn to client */
struct sockaddr_in srvskt; /* server listen socket */
struct sockaddr_in cliskt; /* socket to the UI/DB client */
int adrlen;
char inbuf[INSZ]; /* Buffer for incoming SQL commands */
char outbuf[OUTSZ]; /* response back to the client */
int incnt; /* SQL command input count */
int outcnt; /* SQL command output count */
int dbret; /* return value from SQL command */
/* init mydata */
for (i=0; i<ROW_COUNT; i++) {
mydata[i].myint = 0;
mydata[i].myfloat = 0.0;
mydata[i].notes[0] = (char) 0;
mydata[i].seton[0] = (char) 0;
}
/* init librta and tell it about mydata */
rta_init();
if (rta_add_table(&mytbldef) != RTA_SUCCESS) {
fprintf(stderr, "Table definition error!\n");
exit(1);
}
The above code allocates the socket structures and other local variables. The final piece of initialization sets up the socket to listen for incoming client connections. Remember that each UI program will treat your program as if it were a Postgres database. You can use TCP socket for the this example but don't forget that Unix sockets work just as well. (By-the-way: the following code is pretty horrendous. It uses blocking IO, ignores error conditions, and makes wildly optimistic assumptions about socket IO. My goal is to make the code understandable by getting it into as few lines as possible.)
/* We now need to open a socket to listen for incoming
* client connections. */
adrlen = sizeof (struct sockaddr_in);
(void) memset ((void *) &srvskt, 0, (size_t) adrlen);
srvskt.sin_family = AF_INET;
srvskt.sin_addr.s_addr = INADDR_ANY;
srvskt.sin_port = htons (8888);
srvfd = socket(AF_INET, SOCK_STREAM, 0); /* no error checks! */
bind(srvfd, (struct sockaddr *) &srvskt, adrlen);
listen (srvfd, 4);
The main loop in the program waits for a TCP connection from a client and then loops reading the Postgres encoded stream of SQL requests, processing the requests with repeated calls to rta_dbcommand(), and writing any results back to the client. While a connection can close for errors, we normally expect the client to request an orderly close to the connection.
/* Loop forever accepting client connections */
while (1) {
connfd = accept(srvfd, (struct sockaddr *) &cliskt, &adrlen);
if (connfd < 0) {
fprintf(stderr, "Error on socket/bind/listen/accept\n");
exit(1);
}
incnt = 0;
while (connfd >= 0) {
incnt = read(connfd, &inbuf[incnt], INSZ-incnt);
if (incnt <= 0) {
close(connfd);
connfd = -1;
}
outcnt = OUTSZ;
dbret = rta_dbcommand(inbuf, &incnt, outbuf, &outcnt);
switch (dbret) {
case RTA_SUCCESS:
write(connfd, outbuf, (OUTSZ - outcnt));
incnt = 0;
break;
case RTA_NOCMD:
break;
case RTA_CLOSE:
close(connfd);
connfd = -1;
break;
case RTA_NOBUF:
close(connfd);
connfd = -1;
break;
}
}
}
The callback function is fairly straightforward. Remember that the write callback is called on a column after the write of all update data to the columns. The write callback on the 'notes' field is called after the data has been written. It loops through the string replacing greater-than and less-than symbols with a period, and copying a reverse of the string into the 'seton' field.
int reverse_str(char *tbl, char *col, char *sql, void *pr,
int rowid, void *poldrow)
{
int i,j; /* loop counters */
i = strlen(mydata[rowid].notes) -1; /* -1 to ignore NULL */
for(j=0 ; i>=0; i--,j++) {
if (mydata[rowid].notes[i] == '<' ||
mydata[rowid].notes[i] == '>')
mydata[rowid].notes[i] = '.';
mydata[rowid].seton[j] = mydata[rowid].notes[i];
}
mydata[rowid].seton[j] = (char) 0;
return(0); /* no errors */
}
Build and LinkThe information in this section should be sufficient to download, build, and run the sample application. Download the librta source code from one of the links on the Downloads page. Untar it. Go to the src directory and do a make. (You will need flex and bison installed.)
# tar -xzf rta-X.Y.Z.tgz
# cd rta-X.Y.Z
# cd src
# make
# sudo make install # or the Red Hat equivalent
Change working directory to test and do a make.
# cd ../test
# make
Run the application with the command:
# ./app &
That is all there is to it. You may want to use the statically linked version if you did not install the shared object libraries. TestIf all has gone well, you now have an application running which looks a little like a Postgres database. Only instead of a database, it is your sample application offering up its internal tables for use by Postgres clients. PythonThe Python program presented here shows how to import the the Postgresql binding for Python, how to connect to the librta program, and how to retrieve all the rows in the 'mytable' table. Of note is setting autocommit to True. The Python binding assumes that every call to execute() is part of a transaction. It quietly adds a BEGIN statement to the front of your SQL code. Since librta does not support transactions, the BEGIN statment fails. The solution this problem is to tell the Python library that you don't want a transaction. That is, you want to automatically commit every SQL statement. This program fetches mytable and displays it.
#!/usr/bin/python3
import psycopg2
conn = psycopg2.connect(host="127.0.0.01", port="8888")
conn.autocommit = True
cur = conn.cursor()
cur.execute('SELECT * FROM mytable')
rows = cur.fetchall()
print ("\nThe 'mytable' Table:\n")
for row in rows:
print (" ", row)
psqlThe first client to try is the Postgres browser tool, psql. This tool is part of the base Postgres install, and is the tool to use if you want to manage your final application using Bash scripts. To connect psql to your application enter:
# psql -h localhost -p 8888
Depending on the version, Postgres should respond with:
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
Later versions of the PostgreSQL client, psql, might give a warning that they might not be compatible with a 7.x server. Let's try some SQL commands to play with the tables. Give me a list of the tables:
SELECT name FROM rta_tables;
name
-------------
rta_tables
rta_columns
mytable
(3 rows)
You do not need to use upper case for the SQL keywords. The psql program accepts both upper and lower case. Display the contents of mytable:
SELECT * FROM mytable;
myint | myfloat | notes | seton
-------+----------------------+-------+-------
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
0 | 0.0000000000 | |
(20 rows)
Set all of the myint values to 44 and display the new table:
UPDATE mytable SET myint=44;
UPDATE 20 (<== response from command)
SELECT * FROM mytable;
Note that psql tells us how many rows were modified. Set the notes field to "hi mom!"
UPDATE mytable SET notes="hi mom!";
UPDATE 20
Set the notes field of only row 0 to "<b>hi mom!</b>":
UPDATE mytable SET notes="<b>hi mom!</b>" LIMIT 1 OFFSET 0;
UPDATE 1
The LIMIT says how many rows to change, and the OFFSET tells where to start the changes. The default LIMIT is all rows, and the default OFFSET is zero. CThe sample C client inclulded with the source code is called librta_client.c. It illustrates how to get data out of your application from a C program. You need the postgresql-devel package to build this program. Build and run the program with:
# sudo apt-get install libpq5 libpq-dev
# gcc librta_client.c -o librta_client -lpq
# (the application should still be running)
# ./librta_client
The program sets the field myint to a value of 44 and then gets and prints the fields myint, myfloat, and notes for all twenty rows in the table. Note that you test for success in different ways depending on whether or not you expect data back from the command. Note also that the returned data is always a string. You need to scan it into a variable for other processing. PHPThe PHP program presented here, php_client.php, illustrates how to get run-time access to your application data from a PHP program. You will need the php-pgsql package to run this program, and if you are running Apache, be sure to verify that the PHP interpreter in Apache modules directory. Also verify that the PHP-Postgres library is loaded by checking for pgsql.so in the /usr/lib/php5 directory. Clearly your configuration for PHP and Postgres may be different than what is described here. This sample PHP gets and displays the fields myint, myfloat, and notes for all twenty rows in the table. Perhaps a better way to evaluate PHP is to install the table editor that comes with librta. Just put the four .php files onto your web server binary path (and sure that PHP and php-pgsql are installed). The table editor is the application running on the Live Demo elsewhere on this site. |
|
