Access to databases under management SUBD POSTGRES95

In SUBD POSTGRES95 two basic opportunities of access to the databases are realized:

- Through psql - the interface of the command line of command Shell shell;

- From the applied program written in programming language Si (or the other language) with use of functions of applied interface LIBPQ.

Psql is the interactive terminal monitor allowing the user to formulate, edit and carry out sets of commands - operators of language POSTQUEL. He is started in a mode of the command line of OS UNIX with the instruction{indication} of a name of a database:

% psql polyn

The user can directly enter from the command line of the monitor one by one SQL-commands, and can pass search as a file with SQL-operators through the command line of OS UNIX:

% psql <query.sql

The interface of the command line psql managers of databases for creation usually use, updatings and removals{distances} of attitudes{relations}, institutions and grantings of the rights to new users, etc. He is convenient enough for input of the big files of the information in a database and a conclusion of simple reports. The interactive monitor does not allow to generate complex  forms of reports as with his  help it is impossible to make disassembly of the received result for formation of new searches and consequently his  use in applied programs is limited enough.

LIBPQ - applied program interface POSTGRES95. He is submitted by a set of library functions (subroutines) which allow client programs to send searches to SUBD server and to receive from him  corresponding results. For this purpose in the applied program include the main file of library libpq-fe.h, build functions LIBPQ and make compilation of a code of the program with libraries POSTGRES95. The circuit of access to databases from external programs simple enough. With the help of special function PQsetdb TCP-connection on the certain port (as a rule, - 5432) the applied program with a process - demon postmaster'om is established. Thus functions are passed parameters of values of a name of a database, the IP-address of the server, port of connection. Further at successful connection there is a performance within the framework of function PQexec of SQL-operators of language of searches POSTQUEL - opening of transaction to a database, performance of search and closing of transaction. After that occurs zaveshenie connections with a database. At performance of search at the choice of the data from DB POSTGRES95 creates the time table in which places the received result. Using the SQL-operators connected to cursors, and special functions LIBPQ on job with trains, fields of attitudes{relations}, access to elements of the resulting table that results in generation of any reports on searches of users simply enough provides. The fragment of the Ci-program realizing search to a database "polyn" is below resulted:

pghost = "ns.polyn.kiae.su"

pgport = "5432";

pgoptions=NULL;

pgtty=NULL;

dbname = "polyn"

/*ustanovka Connections with a database */

conn = PQsetdb (pghost, pgport, pgoptions, pgtty, dbname);

/* Check of the status of performance of connection */

if (PQstatus (conn) == CONNECTION_BAD)

{printf (" connection to database '%s' failed ", dbname);

printf ("%s", PQerrorMessage (conn));

PQfinish (conn);

exit (1);

}

/* The beginning of transaction with BD*/

res=PQexec (conn, "BEGIN");

/* Check of the status of performance of function */

if (PQresultStatus (res)! =PGRES_COMMAND_OK)

{printf (" BEGIN command failed ");

PQclear (res);

PQfinish (conn);

exit (1);}

PQclear (res);

/* Performance SQL-opreatora of installation of the cursor on result

Search of a choice of a field isotop from the attitude{relation} isotop */

res=PQexec (conn, " DECLARE myportal CURSOR FOR select

isotop.isotop from isotop ");

/* Performance of the operator of reading on the cursor */

res=PQexec (conn, " FETCH ALL in myportal ");

/* Definition of quantity{amount} of trains and attributes in

To the resulting table */

ntups = PQntuples (res);

nflds = PQnfields (res);

/* A conclusion of names of attributes */

for (i=0; i <nflds; i ++) {

printf (" <td bgcolor = * 20B2AA align=center> h3> %slt; </td> ",

PQfname (res, i));

}

/* A conclusion of elements of the resulting attitude{relation} */

for (i=0; i <ntups; i ++) {

for (j=0; j <nflds; j ++) {

printf (" <td bgcolor = * 20B2AA align=center> %s </td> n ",

PQgetvalue (res, i, j));

}

}

PQclear (res);

/* Closing the cursor */

res=PQexec (conn, " CLOSE myportal ");

PQclear (res);

/* Closing transaction */

res=PQexec (conn, "END");

PQclear (res);

/* Closing soedinenija*/

PQfinish (conn);

For realization of access to databases POSTGRES95 from World Wide Web it is possible to use any mechanisms described above - CGI, FastCGI, API, Java. For example, the API-module of Apache PHP server supports interaction with libraries POSTGRES95, and also two ODBC-drivers, PostODBC and OpenLink ODBC which simplify development of programs - gateways are developed. But nevertheless it is not necessary to forget and about convenient enough and simple construction tool of interactive applications - Common Gataway Interface which does not demand any additional software and it is easy enough in application. As an example of use CGI for access to databases under management POSTGRES95 it is possible to result created for RNC " Kurchatovskij institute " information system of base of the numerical data on radiating pollution of 30-km of a zone around CHAEHS "Test" on Apache Web-server. Creation of information system has been directed on performance of the following problems{tasks}:

Input of the new information in a DB for conducting a database.

Generation of reports on searches of users.

The structure of interaction of the software of information system looks as follows (fig. 5). According to technology WWW, the server of report HTTP Apache working, as a rule, on 80-mu to port of a stack of reports TCP-IP, accepts searches from the user with the help of client browsers of hypertext documents (Netscape Navigator, Internet Explorer, Lynx, etc.) . The formalized data access within the framework of information system is carried out on the basis of HTML-forms. With their help the forms entered into fields the data are passed to Apache server which causes the CGI-program specified in the form for processing these parameters and passes her management. The CGI-script with the help of functions of applied interface SUBD POSTGRES95 will transform the data to SQL-search, establishes connection with SUBD server and passes him search about performance. SUBD server carries out search, addressing to a DB "Test" and returns result to a CGI-script which, in turn, forms to "strike" the HTML-document and through Apache server passes his  client.

All navigating HTML-pages of information system are generated CGI-programs as all HTML-forms - for introduction of search criteria (fig. 6) and input of new DB given for updating (fig. 7) - contain values from files of dictionaries that provides more convenient interface and faster filling of forms.

Lacks CGI connected to generation of new process not so are essential to the given information system - loss occurs only in insignificant expenses of time for expectation of the answer of the server. But if it is necessary autentifikacija each user and his  conducting during a session of interaction with a database, that, at a sight of the author, FastCGI is the best decision of this question. That is use of this or that means depends first of all on a task in view for realization - that it is necessary to provide first of all at its{her} decision