| ## picoSQL-2.0.2 | Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

- License GPL
- Introduction
- Istallattion
- Accessing the DB
- SQL language
- Test DB
- Tools
  - Driver ODBC - Win32 ODBC Driver
  - Driver JDBC - Java JDBC Driver
  - iSQLViewer - tool scritto in linguaggio Java, non realizzato da Picosoft per l'amministrazione/interrogazione del DB che ha una propria licenza (vedi isql.sourceforge.net)
  - picosql - Command line query tool
  - pIcheck - Testing and rebuilding tool
  - pIrecover -
- Stored procedures

Introduction

The project of picoSQL was born in 1995. It was an ODBC driver for COBOL indexed files. It consisted of a DLL written in C++ on Windows 3.1 operating system. In the beginning the main effort was made in order to obtain a good compatibity with existing front-end (mainly the MS-Access ©, MS-Query © and VisualBasic ©) and in order to see COBOL files as a normalized relational DB.

The obtained success concurs to develop ulteriorly the product, refining the query optimizer and implementing an extended SQL, to exclusion of the DDL (Data Description Language). A later version, called PicoDB, were devoloped on UNIX system in client/server architecture.

PicoSql is released under GPL license; it derives from PicoDB (that remains a Picosoft proprietary product) with the addition of some SQL-DDL statement (CREATE/DROP TABLE(INDEX) and excluding COBOL files compatibility). The C/C++ library that allow the database access from C/C++ programs using the ODBC 2.5 API is released under LGPL license; this allows to link the library with programs that are not under GPL.

PicoSQL can be used from Windows applications (like MS-ACCESS ;©, MS-Query ©, VB © etc.) in client/server modality using the ODBC driver picoSQLNet. It can moreover be used from appropriate Java programs through the JDBC driver comprised in the distribution. Finally it can be used from programs written in C and C++ through its set of ODBC 2.5 compliant API.

PicoSQL lacks at the moment of some common characteristics to the most common RDBMS (like the schema and catalogue concepts and the management of the user permissions who can be obtained anyway using the permissions of the host operating system) but they are just these lacks to make it particularly simple to install and to use and fast in the logons and the queries.

Installation

The installation is particularly simple: if you have the binary distribution, is sufficient to extract the compressed files in one directory (\ under Windows NT/2000/XP) and execute:

    startTestDb.sh (Unix/Linux)

    startTestDb.bat (Windows)

in order to activate the DB with a test database already configured.
Warning! under Windows NT/2000/XP the script setReg.js is launched to insert some informations in the registry file. One of this information is about the test DB location and it must be changed if the installation directory is not \
At this point, if you have Java on your computer, you can run:

    iSQLViewerTest.sh (Unix/Linux)

    iSQLViewerTest.bat (Windows)

in order to query the DB. If you does not have Java, you can use the program

    picosql.sh (Unix/Linux)

    picosql.bat (Windows)

in order to make SQL query to the DB from the command line.

The installation directory contains the following subdirectory:

- bin - where all the executables programs reside;
- bin - where all the libraries reside;
- inc - where all the header files reside;
- java - where all the Java programs and the JDBC driver reside;
- doc - where the documentation reside;

PicoSQL consists fundamentally in a program, picosqld, to activate through inetd (or xinetd) or picoserv. In such a way it becomes a net service binded to a port, that is to a entire number comprised between 1 and 65535; this number (as an example 6789) must always be specified on the command line of picosqld. As an example:

    picosqld 6789

For the activation with inetd (or xinetd), you must see the relative documentation. picoserv is a simple program that replaces inetd, mainly for scopes of test or debugging: its usage is the following:

    picoserv [-f] commandLine portNumber

In our case the activation of picosqld can be obtained with the following command line:

    picoserv "picosqld 6789" 6789

To notice as the port number is specified two times, as an argument of picosqld and as an argument of picoserv.

When picosqld receive a connection request, it reads a configuration file to find the necessary informations. This file is a text file on Unix/Linux systems and is the system registry file on Windows. In the latter case you can modify the configuration using the regedit program.

- Unix/Lunixthe file read for configuration is

      $HOME/.picosql.ini

  or, if this file is missing

      /etc/picosql.ini

  The file consists of sections and parameters. A section begins with the name of the section in square brackets and continues until the next section begins. Sections contain attributes of the form

      name=value

  The file is line-based - that is, each newline-terminated line represents either a comment, a section name or a parameter. Section and parameter names are not case sensitive. Only the first equals sign in a parameter is significant. Any line beginning with a semicolon (';') or a hash ('#') character is ignored, as are lines containing only whitespace. Each section corresponds to a database and comprises information like user, password and DB location. In the following example there are two database declared, the former called picoSqlTest, is accessible by anyone while the latter, called rubrica is accessible only by user pico giving the appropriate password.

      [picoSqlTest]
      DataDirectory=/home/test.db

      [rubrica]
      DataDirectory=/home/rubrica.db
      User=pico
      Password=soft
      PicoOdbcTrace=NO
      SuspensiveLock=YES
      ; for picosql client only
      server=localhost
      port=6789

- Windows NT/2000/XP On Windows system the configuration informations are read in the system registry file, starting on the key:

      HKEY_CURRENT_USER\Software\PicoSoft\PicoSQL

  or, if this key is missing:

      HKEY_LOCAL_MACHINE\Software\PicoSoft\PicoSQL

  Any key int this section correspond to a database. Under each key can be some string, each one corresponding to a database attribute, e.g. user, password and DB location. In the following example there are two database declared, the former called picoSqlTest, is accessible by anyone while the latter, called rubrica is accessible only by user pico giving the appropriate password.

       [HKEY_CURRENT_USER\Software\PicoSoft\PicoSQL\picoSqlTest]
       DataDirectory=C:\home\test.db

       [HKEY_CURRENT_USER\Software\PicoSoft\PicoSQL\rubrica]
       DataDirectory=C:\home\rubrica.db
       User=pico
       Password=soft
       PicoOdbcTrace=NO
       SuspensiveLock=YES
       server=localhost
       port=6789

A DB is simply a directory that contains all the tables created by the user more three, __TABLES __ COLUMNS and __VIEWS, created automatically at the first logon and containing the DB data dictionary. Valid attributes are:

Attribute name | Description | Valid values
    DataDirectory
| The directory path under which the data will be archived. This is the only mandatory attribute. | A valid path
    User
| The user that can access to this DB. | A user name
    Password
| The password that must be used to access to this DB. | A password name
    PLogFile
| Designates a log file in which picosql stores the log of all the transaction done. This file can be used by the program pIrecover to rebuild the DB on the bese of the last valid backup. To activate this feature is necessary to put picosql in AUTOCOMMIT=OFF mode using the appropriate ODBC/JDBC API. ##### Warning! In the current release, operation on BLOBS cannot be rollbacked. | A valid path
    PTransaction
| Set the transaction mode ON/OFF without log file. To activate this feature is necessary to put picosql in AUTOCOMMIT=OFF mode using the appropriate ODBC/JDBC API. ##### Warning! In the current release, operation on BLOBS cannot be rollbacked. | Y or N (default)
    SuspensiveLock
| When a program try to lock a row already locked by another program, using for example a "SELECT ... FOR UPDATE" statement, it receive an appropriate error. Putting Y in this attribute, picoSQL waits until the row become available. | Y or N (default)
    SysUser
| Setting this option to Y, the DB user becomes the system user too and it acts with the system user permissions. To do this option working, the daemon mut be run with the appropriate permission (from root in Linux/Unix environments and with privileges SeTcbPrivilege, SeAssignPrimary e SeIncreaseQuota on Windows; in Windows environments, the DB user and password must correspond with those of system user). | Y o N (default)
    PicoOdbcTrace
| Activate a log file reporting all API calls. The log file is located under /tmp (or C:\ in Windows) and it is called PICOSQL<pid>.log | Y or N (default)

Accessing the DB

To access the data stored in picoSQL you can use Java programs by means of driver JDBC, or you can use Windows programs by means of driver ODBC. C/C++ programs can use the libpicosqlnet.a (libpicosqlnet.lib on Windows system), who implements the ODBC API set. These libraries use a configuration file again (picosql.ini on Unix/Linux systems and the system registry file on Windows systems) to get the informations about connection. You can have a single file for both server side and client side, but you can do this only if you access the DB from the same local computer and if you do not have security issues. A more general configuration consists of a file /etc/picosql.ini (in Windows the key HKEY_LOCAL_MACHINE\Software\PicoSoft\PicoSQL ) in which are stored the server information and that can be read only by the DB administrator; each user has his own file .picosql.ini (in Windows the key HKEY_CURRENT_USER\Software\PicoSoft\PicoSQL ) under his home directory in which resides the connection information.

From a client program point of view, each section correspond to a logic DB name (the equivalent of the Data Source Name in the ODBC architecture) which may differs from the corresponding server DB name. The valid attributes in the section are:

Attribute name | Description
    Server
| Address or network name of the host where the DB is running
    Port
| Port number where picoSQL is listening
    Name
| Server DB name