About Vitalware's Database Engine

Details about the Vitalware Database Engine

database docsVitalware's Database Engine is a multi-user database management system with a rich suite of tools and utilities.

Read on

Database Documentation

Database Documentation

DatabaseDocComprehensive documentation about Vitalware's Database Engine for Users and Administrators.

Read on

Texxmlserver and PHP Objects

Texxmlserver was designed to provide a standards-compliant database interface to Texpress for the development of web applications and services.  Texxmlserver provides XML over HTTP access to Texpress Databases.

Texxmlserver conforms to the following standards:

  • eXtended Markup Language  (XML)
  • Hyper Text Transfer Protocol Version 1.0 (HTTP/1.0)

The majority of web development is undertaken using scripting language such as PHP, JSP, ASP, or Perl.  Interfacing a database with a script language usually involves language and database specific drivers. Texxmlserver improves on traditional database interfacing by using the web's own connection protocol; HTTP.  This has the following advantages:

  • Database interface is independent of scripting language or operating platform
  • HTTP protocol is clear-text and easy to firewall (unlike ODBC/JDBC/Corba)
  • Web developers are familiar with HTTP & XML
  • HTTP connection support is built into all major web scripting languages
  • XML is the de-facto data exchange format for web development
  • No language specific database drivers are required

How it works

Texxmlserver is a resident server daemon like Apache or Sendmail. It listens on a TCP port for incoming HTTP connections.  When a HTTP request is received, Texxmlserver will look for a TEXQL (KE Texpress' enhanced SQL) query request in the prescribed format.  The server executes the query or statement and return the results formatted as an XML document.

A scripting language would perform the following functions to interface with the Texxmlserver:

  1. URL-encode the TEXQL query
             Raw TEXQL:  "Select Title, Year from Movies Where Year = 2001"
             URL Encoded:  "Select+Title%2C+Year+from+Movies+Where+Year+%3D+2001"
    Web scripting languages have inbuilt support for URL encoding.
  2. Construct a URL using the prescribed syntax:
             http://[server.address]/?texql=[URL Encoded TEXQL], e.g. :
             http://texpress.kesoftware.com/?texql=Select+Title%2C+Year+from+Movies+Where+Year+%3D+2001
  3. Use the URL to request the "page" from the Texxmlserver using HTTP GET (or POST).  The "page" returned will be an XML document containing the results and status of the query. Scripting languages have inbuilt support for remote HTTP requests.
  4. Parse the returned XML document to extract the data returned from query.  All web scripting languages provide XML parsing support.  Commonly used parser standards are DOM and SAX.  Both parser types are compatible with the XML returned from Texxmlserver.
  5. Use the parsed XML data to construct a dynamic web page. 

XML Format

The XML returned from Texxmlserver is designed to be light-weight, fast and easily parsed.  KE Texpress is an object oriented database.  XML is an ideal format for representing Texpress's hierarchical data.  Users can continue to take advantage of Texpress's nested table/column support.

Installation

Planning Your Installation

Texxmlserver is a resident daemon process. It should be installed under the express account on the same server that hosts the KE Texpress databases.  Texxmlserver should be started at system boot.

Texxmlserver is usually accessed via a scripting language run on a Web Server.  The Web Server and the Database Server running Texxmlserver may be located on the same machine. Alternatively the Database Server may be installed on a separate system (possibly behind a firewall).  The Database Server and the Web Server should be connected via TCP/IP. 

texxmlserver.gif

Installing Texxmlserver

Texxmlserver is a component of the Texpress API.  The TexAPI is a separate component of Texpress.  Please see the KE Software website for details. Texxmlserver is available in TexAPI version 3.1.002 or higher.

Step 1.  Install TexAPI

Install TexAPI as per the TexAPI install notes.  Ensure that the texapi/bin directory is on the PATH. 

Step 2.  Create a working directory 

Texxmlserver needs a working directory [base directory].  It expects to find the texxmlserver.conf file in this directory and will also store its access logs here.  The texxmlserver.conf file is stored in texapi/etc.  You may opt to use this as your base directory.  If you set-up an alternate location, copy texapi/etc/texxmlserver.conf into this location. 

Step 3.  Preparing your databases 

KE Texpress databases should be in an accessible state before starting Texxmlserver. 

Step 4.  Edit the configuration file 

The Texxmlserver configuration file should be edited as appropriate.  The key setting to modify is the "AllowedHosts".  Your Web Server's address/name should be added to the list.  If the Web Server is running on the same physical system as Texxmlserver, then the default setting of "localhost" will be satisfactory. 

Configuration is also possible with command-line options on texxmlctl.  Run

         texxmlctl start --help

for details. Any command-line options override the configuration file. 

Step 5.  Starting Texxmlserver 

Texxmlserver is controlled by texxmlctl.  This command accepts "start", "stop" and "restart" options.  Start Texxmlserver manually with the command:

         texxmlctl -b ~/texapi/etc start 

Step 6.  Testing 

The simplest method of testing Texxmlserver is with a Web Browser such as MS Internet Explorer.  Open the browser and enter a URL such as:

         http://myserver.address/?texql=select+all+from+database

[ replace myserver.address with the address of the server running Texxmlserver - "database" should be replaced with a name of a valid Texpress database. ]

The contents of the database should display in XML.

Note:  The system you are attempting to connect from should be an "AllowedHost" set in texxmlserver.conf.

Starting Texxmlserver on Boot

The following command can be placed in your system's local boot file to start texxmlserver automatically after boot:

        /etc/rc.local

    su texpress -c "(. /home/texpress/.profile;
                   cd /home/texpress/texapi/etc; 
           texxmlctl start)"

Security 

The "AllowedHosts" directive in the texxmlserver.conf file offers strong access control.  Only these trusted hosts (i.e. the web server) will be granted access to Texpress databases via Texxmlserver.  

Texxmlserver can also control access via an "Authentication Token". The authentication token is a "secret" word or phrase that's defined in the texxmlserver.conf file.  Access is only granted to connections that have the secret word in a URI field called "auth".  An example is: 

The texxmlserver.conf file contains: 

         AuthenticationToken=mysecretword 

A valid request: 

         http://myserver:8002/?auth=mysecretword&texql=select+all+from+database

Additional security on the operating system level should be considered.  Methods include:

  • Using a Firewall to prevent all TCP connections on port 8000 from all hosts except the Web Server.
  • Only enabling the "AllowUpdates" setting in texxmlserver.conf if updates are absolutely required. 

Interfacing with Texxmlserver 

Any scripting language with an HTTP request library and XML Parser will be able to retrieve and use data via Texxmlserver.  All major web scripting languages provide the required libraries.  Texxmlserver currently provides a high level interface API for accessing Texxmlserver via the PHP.  The PHP library interface is located in examples/texxmlserver/php/texquery.php .  This file can be "included" or "required" in PHP pages.  PHP example code and notes are provided.

Other web script languages may interface with Texxmlserver using their HTTP/XML Libraries.  All Query/TEXQL requests must conform to the following format:

1.  TCP Protocol

2.  Valid HTTP "GET" or "POST" request as defined in the HTTP/1.0 standard.

3.  The URL request should contain a command in the form texql=[url encoded TEXQL statement]

An example of a full request is:

         GET /?texlq=Select+Title%2C+Year+from+Movies+Where+Year+%3D+2001 HTTP/1.0

TEXQL Update, Insert and Delete operations are permitted.  Developers should check the success of the TEXQL command by inspecting the "status" attribute in the top level <results> tag. (see XML Format Overview). 

XML Format Overview 

The XML produced by Texxmlserver is designed to be as light-weight as possible for fast transfer and parsing.  This is an important aspect for dynamic web development.  No Field/Column type information is transmitted (date, float, text, etc.).  The developer is expected to know the structure of the database and know how to handle the field content data.  If you require an XML data file with type information, please have a look at the command-line utility texxmlexport.  Future versions of XML server may provide more verbose XML mark-up schemas.

The following format rules are applied to a query results set.

Level 1.  The top-level (or Root) tag is <results>

name:  results
attributes:  status="success" or status="failed"
attributes:  matches="123" (number of matching records returned in the query operation)
attributes:  error="TexAPI error string".
cdata:  None
immediate descendent: <record>
repeat:  No
example 1:  <results status="success" matches="89"> ... </results>
example 2:  <results status="failed" error="Syntax Error"> ... </results>

Level 2.  Denotes the start and end of a database record

name:  record
attributes:  None
cdata:  None
immediate descendent: <[KE Texpress Column ID]>
repeat:  Yes.  Once for each record in results set.
example: <record> ... </record>

Level 3.  Top level KE Texpress Column  (Atom, Tuple or Nested Table)

name:  [KE Texpress column ID]
attributes:  None
cdata:  KE Texpress Field/Column contents for given record or none if it's an ID for a nested table.
repeat:  No.  Column IDs are unique within record
example 1:  <MovieTitle> ... </MovieTitle>
example 2:  <Actors_tab> ... </Actors_tab>

Level 4.  Item in a Nested Table.  

name:  [KE Texpress column ID]
attributes:  None
cdata: KE Texpress Field/Column contents for given record.
repeat:  Yes.  An item in a nested table can repeat within its parent tag.
example:  <Actors> ... </Actors> 

Example XML Output 

Example 1

TEXQL Query:  SELECT MovieTitle, Year FROM Movies

URL Request:  http://server.address/?texql=Select+MovieTitle%2C+Year+From+Movies

<?xml version="1.0" ?>
<results status="success" matches="3">
    <record>
        <MovieTitle>Star Wars</MovieTitle>
        <Year>1977</Year>
    </record>
    <record>
        <MovieTitle>The Empire Strikes Back</MovieTitle>
        <Year>1980</Year>
    </record>
    <record>
        <MovieTitle>Return of the Jedi</MovieTitle>
        <Year>1983</Year>
    </record>
</results>

 

Example 2

TEXQL Query:  SELECT MovieTitle, Actors_tab FROM Movies

URL Request:  http://server.address/?texql=Select+MovieTitle%2C+Actors_tab+From+Movies

<?xml version="1.0" ?>
<results status="success" matches="3">
    <record>
        <MovieTitle>Star Wars</MovieTitle>
        <Actors_tab>
            <Actors>J. Smith</Actors>
            <Actors>B. J. Jones</Actors>
        </Actors_tab>
    </record>
    <record>
        <MovieTitle>Empire Strikes Back</MovieTitle>
        <Actors_tab>
            <Actors>J. Smith</Actors>
            <Actors>Karen Smith</Actors>
        </Actors_tab>
    </record>
    <record>
        <MovieTitle>Return of the Jedi</MovieTitle>
        <Actors_tab>
            <Actors>J. Smith</Actors>
            <Actors>A. Mc Donald</Actors>
        </Actors_tab>
    </record>
</results>