ProLinga-Data Project

Table of Contents

Data Commands
Error Handling
Multiple Data Commands
Storage Types

ProLinga-Data is a project that uses the latest libgda technologies provide access to the data providers PostgreSQL, MySQL, Oracle, Sybase, ODBC data sources, mSQL, IBM DB2, FireBird/Interbase, FreeTDS, SQLite, LDAP, MS SQL Server, Berkeley DB, MDB (MS Access) and xBase (dBase, Clipper, FoxPro) files.

Since libgda is under active development itself, we have to introduce this technology in seperate steps. The first (and implemented) step is that SQL queries can be passed on to a provider using its native language dialect (SQL92 or SQL99 for instance). ProLinga-Data is in this instance "just" the traffic agent of passing on a SQL statement and returning a result or data model (cursor).

The next step is to introduce an easy way to modify schemas allowing the management of database objects (tables, views, users, procedures, etc) in the provider environment, without using any SQL at all. This feature will be available in upcoming releases of libgda and will then get implemented in ProLinga-Data. This feature gives us the advantage for instance to build and re-build data files from within the ProLinga Environment, without having to know if the data provider is PostgreSQL or MySQL for instance. The ability to create tables and indexes is available in the mean-time and the rebuild ability is not too far away.

Fully functional updatable data models are available for the PostgreSQL and MySQL providers.

Future enhancement steps will include:

XML Queries. Queries in a generic form that are put into an XML document are send to a provider where this query is transformed into a, for the data provider, understandable (SQL) query and where results are returned to the caller.
Reports

ProLinga-Data works according the Request-Response mechanism. A request can be for instance to execute a "SELECT * ..." in a data provider environment. ProLinga-Data will send out this request to the data provider and returns a result or data model (cursor) as a response.

ProLinga-Data can be configured as a Web Service (daemon) on the machine. A sophisticated SOAP layer on top controls the flow and processing of incoming and outgoing requests/responses over the TCP/IP network. Using this concept, programs can run locally on a machine while accessing a remote database engine centrally located in an intranet environment or anywhere in the world via the internet.

Main purpose for the creation of this project, is to provide an easy way to access and store user data from the ProLinga environment. Since the result library of this project is very open, simple and easy to implement, it can be used in other non-ProLinga related projects.

ProLinga-Data has been built and tested on RedHat Fedora, Mandriva Linux, Debian, Ubuntu and openSUSE. Since all code is written in C++, it can be ported to all modern UNIX systems, Linux variants as well as Windows and Mac O/S platforms.

Data Commands

Data Commands are used to access a data provider. A request XML document containing a command is presented to the provider. This document is processed and a response XML document is returned.

Data Commands have the following format:

<ProLinga>
  <Data Version="1.0" >
    <Command Name="command" Mode="Request">
      . . .
      . . .
    </Command>
  </Data>
</ProLinga>

The following Data Commands are available:

Connect
Disconnect
ListProviders
ListDataSources
OpenDataSource
CloseDataSource
ExecuteQuery
ExecuteNonQuery
ExecuteQueryDataModel
ExecuteQueryTableDataModel
DataModelGetRow
DataModelAppendRow
DataModelUpdateRow
DataModelRemoveRow
DataModelClose
Transaction
ManageData
MetaStore
Ping

Connect

With the connect command, a session is created. A valid session is required to be able to send Data Commands. Hhandles, cursor positions etc will all be held internally in a session record.

Example Connect Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Connect" Mode="Request"/>
  </Data>
</ProLinga>

Example Connect Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Connect" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok"/>
  </Data>
</ProLinga>

Disconnect

With the disconnect command, a session is destroyed.

Example Disconnect Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Disconnect" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz"/>
  </Data>
</ProLinga>

Example Disconnect Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Disconnect" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok"/>
  </Data>
</ProLinga>

ListProviders

Administration/debug command. With the ListProviders command, providers for which libgda has been configured for, will be shown. This does not necessarily mean that the providers themselves are installed and are available.

Example ListProviders Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ListProviders" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object Name="*" />
    </Command>
  </Data>
</ProLinga>

Example ListProviders Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ListProviders" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
        <Object Name="MySQL"/>
        <Object Name="PostgreSQL"/>
        <Object Name="LDAP"/>
        <Object Name="XML"/>
    </Command>
  </Data>
</ProLinga>

ListDataSources

Administration/debug command. With the ListDataSources, datasources for which libgda has been configured, will be shown.

Example ListDataSources Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ListDataSources" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object Name="*" />
    </Command>
  </Data>
</ProLinga>

Example ListDataSources Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ListDataSources" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object Name="stock_msql">
        <Provider>MySQL</Provider>
        <Description>ProLinga MySQL.</Description>
        <Cnc>PORT=3306;USER=prolinga;DATABASE=stock;HOST=localhost</Cnc>
        <UserName/>
      </Object>
      <Object Name="stock_psql">
        <Provider>PostgreSQL</Provider>
        <Description>Stock database in PostgreSQL</Description>
        <Cnc>HOSTADDR=127.0.0.1;PORT=5432;USER=prolinga;DATABASE=stock;HOST=localhost</Cnc>
        <UserName>admin</UserName>
      </Object>
    </Command>
  </Data>
</ProLinga>

OpenDataSource

With the OpenDataSource command, a connection to a data source is established.

Example OpenDataSource Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="OpenDataSource" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" >
        <UserName>prolinga</UserName>
        <Password/>
        <ReadOnlyConnection>False</ReadOnlyConnection>
        <SqlIdentifiersCaseSensitive>False</SqlIdentifiersCaseSensitive>
        <ThreadSafe>True</ThreadSafe>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example OpenDataSource Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="OpenDataSource" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" />
    </Command>
  </Data>
</ProLinga>

CloseDataSource

With the CloseDataSource command, a connection is destroyed. This command is not required when followed by a disconnect command, since a disconnect command will close all data sources linked to that session.

Example CloseDataSource Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="CloseDataSource" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" />
    </Command>
  </Data>
</ProLinga>

Example CloseDataSource Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Connect" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" />
    </Command>
  </Data>
</ProLinga>

ExecuteNonQuery

With the ExecuteNonQuery command, SQL queries as a DELETE or UPDATE can be executed that do not return any data. Only the number of rows affected will be returned.

Example ExecuteNonQuery Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteNonQuery" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql">
        <NonQuery>
          <Sql>INSERT INTO Parts VALUES ('12345','Nail U12','2.95','54')</Sql>
        </NonQuery>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example ExecuteNonQuery Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteNonQuery" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql">
        <NonQuery>
          <RowsAffected>1</RowsAffected>
        </NonQuery>
      </Object>
    </Command>
  </Data>
</ProLinga>

ExecuteQuery

With the ExecuteQuery command, SQL queries can be executed that return data. All data is returned in a single XML response file. For large responses, ExecuteQueryDataModel can be used instead to "just" load the datamodel (cursor), which can be processed using DataModel commands.

Example ExecuteQuery Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQuery" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql">
        <Query>
          <Sql>SELECT * FROM Parts</Sql>
        </Query>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example ExecuteQuery Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQuery" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql">
        <Query>
          <Result>
            <ColumnHeaderInstances>
              <ColumnHeaderInstance SequenceNo="1" Name="Part_Id" StorageType="string"/>
              <ColumnHeaderInstance SequenceNo="2" Name="Desc" StorageType="string"/>
              <ColumnHeaderInstance SequenceNo="3" Name="Price" StorageType="double"/>
              <ColumnHeaderInstance SequenceNo="4" Name="InStock" StorageType="integer"/>
            </ColumnHeaderInstances>
            <DataInstances>
              <DataInstance SequenceNo="1">
                <Part_Id>12345</Part_Id>
                <Desc>Nail U12</Desc>
                <Price>2.95</Price>
                <InStock>54</InStock>
              </DataInstance>
              <DataInstance SequenceNo="2">
                <Part_Id>12346</Part_Id>
                <Desc>Nail U15</Desc>
                <Price>2.90</Price>
                <InStock>14</InStock>
              </DataInstance>
            </DataInstances>
          </Result>
        </Query>
      </Object>
    </Command>
  </Data>
</ProLinga>

ExecuteQueryDataModel

With the ExecuteQueryDataModel command, SQL queries can be executed that return data. All data is loaded in a DataModel. DataModel commands can then be used to retrieve this data.

Example ExecuteQueryDataModel Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQueryDataModel" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <Query>
          <Sql>SELECT * FROM Parts</Sql>
        </Query>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example ExecuteQueryDataModel Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQueryDataModel" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts" />
        <Query>
          <ColumnsDataModel>5</ColumnsDataModel>
          <RowsDataModel>12</RowsDataModel>
        </Query>
    </Command>
  </Data>
</ProLinga>

ExecuteQueryTableDataModel

With the ExecuteQueryTableDataModel command, a table can be loaded in the data model for update, so records can easily be changed, appended or removed resulting in the underlying data provider to be updated.

Example ExecuteQueryTableDataModel Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQueryTableDataModel" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <Query>
          <Table>Parts</Table>
          <Condition>where price > '10.00'</Condition>
        </Query>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example ExecuteQueryTableDataModel Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ExecuteQueryTableDataModel" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts" />
        <Query>
          <ColumnsDataModel>5</ColumnsDataModel>
          <RowsDataModel>12</RowsDataModel>
        </Query>
    </Command>
  </Data>
</ProLinga>

DataModelGetRow

With the DataModelGetRow command, a row can be retrieved from a DataModel.

Example DataModelGetRow Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelGetRow" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelRow RowId="2"/>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example DataModelGetRow Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelGetRow" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelRow RowId="2">
          <Result>
            <ColumnHeaderInstances>
              <ColumnHeaderInstance SequenceNo="1" Name="Part_Id" StorageType="string"/>
              <ColumnHeaderInstance SequenceNo="2" Name="Desc" StorageType="string"/>
              <ColumnHeaderInstance SequenceNo="3" Name="Price" StorageType="double"/>
              <ColumnHeaderInstance SequenceNo="4" Name="InStock" StorageType="integer"/>
            </ColumnHeaderInstances>
            <DataInstances>
              <DataInstance SequenceNo="1">
                <Part_Id>12346</Part_Id>
                <Desc>Nail U15</Desc>
                <Price>2.90</Price>
                <InStock>14</InStock>
              </DataInstance>
            </DataInstances>
          </Result>
        </DataModelRow>
      </Object>
    </Command>
  </Data>
</ProLinga>

DataModelAppendRow

With the DataModelAppendRow command, a row can be appended to the DataModel generated with the ExecuteQueryTableDataModel command. At time of writing, this functionality has not been implemented yet with all data providers supported by the libgda project. Please check the provider specific notes or mailing list (archive) for more info.

Example DataModelAppendRow Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelAppendRow" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelAppendRow>
          <DataInstances>
            <DataInstance SequenceNo="1">
              <Part_Id>12346</Part_Id>
              <Desc>Nail U15</Desc>
              <Price>2.90</Price>
              <InStock>14</InStock>
            </DataInstance>
          </DataInstances>
        </DataModelAppendRow>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example DataModelAppendRow Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelAppendRow" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelAppendRow RowId="8"/>
      </Object>
    </Command>
  </Data>
</ProLinga>

DataModelUpdateRow

With the DataModelUpdateRow command, a row can be updated in the DataModel generated with the ExecuteQueryTableDataModel command. This results in the underlying database row's values being changed. At time of writing, this functionality has not been implemented yet with all data providers supported by the libgda project. Please check their provider specific notes or mailing list (archive) for more info.

Example DataModelUpdateRow Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelUpdateRow" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelUpdateRow RowId="8">
          <DataInstances>
            <DataInstance SequenceNo="1">
              <Part_Id>12346</Part_Id>
              <Desc>Nail U15</Desc>
              <Price>2.95</Price>
              <InStock>14</InStock>
            </DataInstance>
          </DataInstances>
        </DataModelUpdateRow>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example DataModelUpdateRow Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelUpdateRow" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelUpdateRow RowId="8"/>
      </Object>
    </Command>
  </Data>
</ProLinga>

DataModelRemoveRow

With the DataModelRemoveRow command, a row can be removed from a DataModel generated with the ExecuteQueryTableDataModel command. This results in the underlying database row being removed from the database. At time of writing, this functionality has not been implemented yet with all data providers supported by the libgda project. Please check their provider specific notes or mailing list (archive) for more info.

Example DataModelRemoveRow Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelRemoveRow" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts">
        <DataModelRemoveRow RowId="2"/>
      </Object>
    </Command>
  </Data>
</ProLinga>

Example DataModelRemoveRow Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelRemoveRow" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts"/>
    </Command>
  </Data>
</ProLinga>

DataModelClose

With the DataModelClose command, one or all DataModels can be closed and resources will be freed.

Example DataModelClose Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelClose" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" DataModel="dmParts"/>
    </Command>
  </Data>
</ProLinga>

Example DataModelClose Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="DataModelClose" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" DataModel="dmParts"/>
    </Command>
  </Data>
</ProLinga>

Transaction

With the Transaction command, update SQL commands can be safeley executed within a transaction environment. Transactions will only work if the underlying tables are transaction safe tables. With MySQL for instance Transactions will only work when using the type InnoDB or BDB. The default (My)ISAM type does not have a transaction engine.

Example Transaction Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Transaction" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql">
        <Transaction Mode="Begin">   *
          <IsolationLevel>Serializable</IsolationLevel>  **
        </Transaction>
      </Object>
    </Command>
  </Data>
</ProLinga>

*) Available Modes are Begin, Commit and Rollback
**) Available IsolationLevels are Unknown, ReadCommitted, ReadUncommitted, RepeatableRead and Serializable.

Example Transaction Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Transaction" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="psql" />
    </Command>
  </Data>
</ProLinga>

ManageData

With the ManageData command the following actions can be performed:

Create
Drop
Reformat (to do)
Dump to text file (to do)
Load from text file (to do)
...

Example ManageData Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="ManageData" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql" Table="Parts">
        <ManageData Option="Create">
          <ExternalTableName>Parts</ExternalTableName>
        </ManageData>
        <IndexInstances>
          <IndexInstance SequenceNo="1" Name="idxPart1" Primary="True"  Unique="True">
             <KeyInstances>
               <KeyInstance SequenceNo="1" Name="Part_Id" />
             </KeyInstances>
           </IndexInstance>
           <IndexInstance SequenceNo="2" Name="idxPart2" Primary="False" Unique="False" >
            <KeyInstances >
              <KeyInstance SequenceNo="1" Name="Desc" />
            </KeyInstances>
          </IndexInstance>
        </IndexInstances>
        <DataInstances>
          <DataInstance SequenceNo="1" Name="Part_Id" StorageType="Character" Size="5" AllowNulls="False" />
          <DataInstance SequenceNo="2" Name="Desc" StorageType="Character" Size="10" AllowNulls="True" />
          <DataInstance SequenceNo="3" Name="Price" StorageType="Double" Size="5" Decimals="2" AllowNulls="True" />
          <DataInstance SequenceNo="4" Name="InStock" StorageType="Integer" Size="5" AllowNulls="True" />
        </DataInstances>
       </Object>
    </Command>
  </Data>
</ProLinga>

Example ManageData Response:

<ProLinga>
  <Data Version="1.0" >
    <Command Name="ManageData" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" Table="Parts" />
    </Command>
  </Data>
</ProLinga>

MetaStore

With the MetaStore command the following actions can be performed:

Update
... (to do)

Example MetaStore Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="MetaStore" Mode="Request" SessionId="PD-f8l5rowk7414eif077963ooqoicz">
      <Object DataSource="stock_psql">
        <MetaStore Option="Update">
          <ExternalTableName>Parts</ExternalTableName> *
        </MetaStore>
       </Object>
    </Command>
  </Data>
</ProLinga>
*) If ExternalTableName is left empty then all tables will be updated. This process can take a while
   depending on the number of tables involved.

Example MetaStore Response:

<ProLinga>
  <Data Version="1.0" >
    <Command Name="MetaStore" Mode="Response" SessionId="PD-f8l5rowk7414eif077963ooqoicz" Status="Ok">
      <Object DataSource="stock_psql" />
    </Command>
  </Data>
</ProLinga>

Ping

The ping command can be used to test if a data host is on-line and responding to requests.

Example Ping Request:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Ping" Mode="Request"/>
  </Data>
</ProLinga>

Example Ping Response:

<ProLinga>
  <Data Version="1.0">
    <Command Name="Ping" Mode="Response" Status="Ok"/>
  </Data>
</ProLinga>

Additionial error information from the underlying SOAP layer will be presented as well in the response document in case of an error.