DBX v.1.0 beta - SQL queried XML Database in PHP - User's Manual


About DBX [top]

DBX is essentially a full fledged database system that is written completely in PHP, and works using XML files as its native format. The data is accessed by using standard SQL queries. It is a bridge between two different models of databases - hierarchical and relational models.

DBX is the smallest, platform independent DBMS. It doesn't require ANY installation - simply include it as a header; and is extremely easy to migrate to. For small databases, it is a good substitute for MySQL, Oracle, etc. The added advantage is that it saves the data as neat, readable, XML files - allowing easy compatibility. The world now uses Relational Databases as a standard. Hierarchial Databases are making a comeback because of the flexibility they provide. DBX acts as an interface.


Why DBX was made [top]

The primary purpose of DBX was to act as a replacement for mySQL in freely available PHP scripts. Many good scripts use mySQL/other database engines to simplify the coding process. But on the other hand, there are only a handful(probably one or two, actually) free webhosts who provide SQL database support. Even where SQL DBs were available, the servers were just too clogged with "too many connections".

In most cases, the sites using these scripts were small, simple and were built for low - moderate load. And flat file databases were perfect for this. So we wanted to come up with a way to port these SQL scripts and use them with flat file databases. The best method, we thought, was to build a SQL parser that would handle the data - so that the script would not need to be changed at all. Since we were going to store the data in plain text, we took advantage of PHP's XML capabilities (using expat) and chose XML for a reusable, easily readable file format.


How DBX works [top]

DBX works in two parts - The SQL parser, and the XML File Module.

First, the SQL query is parsed into DBX's internal functions. Then, the XML data is parsed and read into memory. DBX's internal functions then read/modify the data as required, and then save the XML data file back.

For example, in a select query, the table name is first extracted. Then that table(which will be an XML file) is read into memory. The where attributes are compiled, and are applied to each row - all rows which fulfil the where condition are dumped into the result array. The result array is then given a unique number called the SQL result handler. This SQL result handler is returned, and all data can be accessed using this handler.

Though the process might seem too simple and slow, but for small databases and moderate load, DBX is optimized enough, and works just fine.


How data is stored [top]

We have mapped the Relational data to a hierarchical scheme in this way:

    > Each database is a sub directory of the DB directory.
    > Each table is an XML file with the .TAB extension.
    > Each record(tuple) in the table is stored between a pair of record tags.


Relational Table in DBX
Tablename
attribute1 attribute2 attribute3
value1 value2 value3
<?xml version='1.0' encoding='UTF-8'?>
<DATABASENAME>
   <TABLE NAME="tablename" FIELDS="attribute1,attribute2,attribute3">
      <RECORD>
         <attribute1>value1</attribute1>
         <attribute2>value2</attribute2>
         <attribute3>value3</attribute3>
      </RECORD>
   </TABLE>
</DATABASENAME>
   


This data is simple easy to read and can also be edited by hand - you can simply open the file in a text editor, make changes to the database and save back.


Copyright & Usage [top]

Please read the file license.txt. You are required to agree to/abide by the conditions put forward in it before you can start using DBX. The file must have come with this package.


Installing DBX [top]

Since DBX is a simple header, there's not much installing that you'll actually need to do. Here's a stepwise account:

1) Edit DBX.php to include the local path - Open DBX.php, change the value of $DBXPATH to whatever path dbx.php is in. Please give an absolute path, with no trailing slashes. This is required for accessing the Database files.

For example, if the path to dbx.php is /usr/home/www/dbx.php then $DBXPATH should be set to /usr/home/www

############# START OF EDITING ###################

# ENTER FULL FILEPATH TO THIS FILE HERE - no trailing slashes
# for example "/root/public_html"

$DBXPATH="/usr/home/www";

############# END OF EDITING #####################
Please do NOT add a trailing slash.

2) Change the file permissions of the DB directory to 0666. All database will be stored in this directory.

3) In your script, include DBX by adding...

<?php

include("/usr/home/www/dbx.php"); //[path to DBX]

?>

That's just about it. You are now ready to use DBX.


Protecting your database [top]

Since the data is stored in simple plain text XML files, they can be downloaded freely if one knows the path to them, over the website. Hence, it is a good idea to secure the directory where the data is stored. There are two ways to do this:

1) Put dbx.php and the DB directory in a directory above the www root - if you save the dbx.php file and the DB directory in a directory that's not accesible by a URL, then it's secure. Most servers have public_html and www_root as their www roots. Store DBX in a directory above them.

2) If you're using Apache, you can use the .htaccess files to password protect the DB directory. This way you can access the database files only if you know the password.


How to use DBX [top]

Simply include the dbx.php file in your php script (using the full path). Then create an object of class dbx, and you're ready to perform queries/ functions on the Database!

Here's a small sample :
<?php

// A Sample Test File.... Shows how to use DBX
// note how it is almost identical to mySQL/other programming.

// Include the DBX parser
include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

//Create / select a database
$DBase->dbx_query("create database test");

$DBase->dbx_query("select database test");

//Run query
$DBase->dbx_query("create table testtable(attrib1 char(30),attrib2 varchar(10),attrib3 integer not null,attrib4 bool)");

?>

Available Functions [top]

All functions are called using a dbx object, like this:
<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_error();

?>

Here's a list of the functions:


• dbx_fetch_array(<queryid>)
Fetches a result row as an associative array.

dbx_fetch_array() fetches one row of data from the result set associated with the specified <queryid>(<queryid> is called SQL result handler). The row is returned as an associative array. Subsequent call to dbx_fetch_array() will return the next row in the result set, or false if there are no more rows.

<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("SELECT DATABASE test");

$q=$DBase->dbx_query("SELECT COUNT(*),MAX(attrib3),COUNT(attrib1) FROM testtable"); //$q is SQL result handler

$result=$DBase->dbx_fetch_array($q); //returns one row(in this case, there is only one row in the result set!)

echo $result[COUNTALL];      //this's how we can access the fetched data when the query performs some aggregate functions.
echo $result[MAX_ATTRIB3];   
echo $result[COUNT_ATTRIB1]; //Note all keys are in capitals.

$q=$DBase->dbx_query("SELECT * FROM testtable"); //$q is SQL result handler

while($result=$DBase->dbx_fetch_array($q))
{
echo $result[ATTRIB1]; //this's how we can access the fetched data when the query performs a simple projection.
echo $result[ATTRIB2]; 
echo $result[ATTRIB3]; //Note all keys are in capitals.
echo $result[ATTRIB4];
}

?>
All the keys used to access data should be in capitals. dbx_fetch_array() returns one row at a time. So, a while loop may be used to access the full result set.
• dbx_query(<SQLquery>)
Sends a SQL query.

dbx_query() sends a SQL query to the currently selected database.

dbx_query() returns TRUE (non-zero) or FALSE (zero) to indicate whether or not the query succeeded. A return value of TRUE means that the query was legal and could be executed by DBX. It does not indicate anything about the number of rows affected or returned. It is perfectly possible for a query to succeed but affect no rows or return no rows. In case of a successful select query, dbx_query() returns a SQL result handler, which can be used to fetch data using dbx_fetch_array().

Note that the query string should not end with a semicolon. Also note that all the data items given in the <SQLquery> should be enclosed within single quotes( ' ) only.

See section on Available SQL for list of SQL queries.
• dbx_num_rows(<queryid>)
Get number of rows in result.

dbx_num_rows() returns the number of rows in the result set specified by <queryid>. This function is valid only for select queries.
• dbx_errno()
Returns the numerical value of the error message from most recent DBX operation.

Returns the error number from the last DBX operation, or 0 (zero) if no error occurred. Note that this function only returns the error code from the most recently executed DBX function (not including dbx_error() and dbx_errno()), so if you want to use it, make sure you check the value before calling another DBX function.
• dbx_error()
Returns the text of the error message from most recent DBX operation.

Returns the error text from the last DBX operation, or ' ' (the empty string) if no error occurred. Note that this function only returns the error text from the most recently executed DBX function (not including dbx_error() and dbx_errno()), so if you want to use it, make sure you check the value before calling another DBX function.
All functions return 0 in case of error.

Note: Unlike other Database Systems, there is no connect function. The creation of the dbx object is analogous to a connect function.


List of Allowed SQL [top]

All queries are sent using the dbx_query function, like this:
<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("SELECT DATABASE test");

$DBase->dbx_query("INSERT INTO testtable VALUES('value1','value2','value3','value4')"); //Note single quotes around all data items.

?>

Here's a list of the available SQL queries:


• create database <databasename>
Create a database.

Creates a directory(database) by the name of <databasename> under the DB directory. Such a directory(database) shouldn't be existing before.
• select database <databasename>
Select a database.

Selects an existing database <databasename> to work with. All subsequent queries involving tables will look for the tables in the selected database. You need to send this query at least once to work with DBX.
• create table <tablename>(<fields>)
Create a table.

Creates a .tab file(table) named <tablename>.tab in the selected database directory. Such a file(table) shouldn't be existing before.

<fields> is a list(seperated by comma) of attributes/fields of the table. Data types and other constraints for the fields may or may not be given. DBX doesn't require them, and will ignore them if given. Also, there is no concept of primary or foreign key. So, they should not be given.
<?php

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("select database test");

$DBase->dbx_query("create table testtable(attrib1 char(30),attrib2 varchar(10),attrib3 integer not null,attrib4 bool)");

$DBase->dbx_query("create table testtable(attrib1,attrib2,attrib3,attrib4)"); //equivalent to above query.

?>
• drop table <tablename>
Drop a table.

Deletes the file(table) named <tablename>.tab from the selected database directory.
• insert into <tablename>[(<fields>)] values(<values>)
Insert a new row.

Inserts a new row into table <tablename>. <values> gives the list(seperated by comma) of values to be put in the new row. All data items(both numeric and others) should be enclosed within single quotes( ' ).

<fields> is a list(seperated by comma) used to specify any particular fields/attributes of the table that should be assigned values from <values>. This is optional, if given, values from <values> are assigned only to fields/attributes specified in <fields>, other fields are assigned NULL.
<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("SELECT DATABASE test");

$DBase->dbx_query("INSERT INTO testtable VALUES('value1','value2','value3','value4')"); //Note single quotes around all data items.

$DBase->dbx_query("INSERT INTO testtable(attrib4,attrib1,attrib3) VALUES('value4','value1','value3')");

?>
• delete from <tablename>[ where <conditions>]
Delete row(s) from a table.

Deletes a row or rows from table <tablename>.

<conditions> is optional, if given it deletes only those row(s) which satisfy <conditions>. Otherwise, it deletes all the rows. For detailed rules on how to form <condition> expressions, refer to select query.
• update <tablename> set <updates>[ where <conditions>]
Update a table.

Modifies the values of attributes/fields in some or all rows of a table <tablename>.

<updates> is a list(seperated by comma) of attributes/fields alongwith their new values.

<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("SELECT DATABASE test");

$DBase->dbx_query("UPDATE testtable SET attrib2='newvalue2',attrib4='newvalue4'"); //Note single quotes around all data items.

$DBase->dbx_query("UPDATE testtable SET attrib1='newvalue1' WHERE attrib3='value3' AND attrib4 IS NULL");

$DBase->dbx_query("UPDATE testtable SET attrib1='newvalue1' WHERE 1"); //updates all rows

$DBase->dbx_query("UPDATE testtable SET attrib2=''"); //NULL values are signified by ''

$DBase->dbx_query("UPDATE testtable SET attrib1=attrib1*1.1"); //not supported

?>
<updates> like SET attrib1=attrib1*1.1 aren't supported. Also, to set a field's value to NULL, use two consecutive single quotes( '' ).

<conditions> is a simple expression(not nested) which can be used to selectively update some rows that satisfy the <conditions>. This clause is optional, if omitted, update modifies every row. For detailed rules on how to form <condition> expressions, refer to select query.
• select <fields> from <tablename>[ where <conditions>][ order by <fields [desc|asc]>]
Retrieve some data.

Used to retrieve data from table <tablename>. <fields> (after select) may be one of the following three types:
(a) * - This makes DBX to project all fields/attributes present in the table. So the returned rows in the result set have all the fields.
(b) Field names(seperated by comma) - Only specified fields/attributes are projected.
(c) Aggregate functions - They are count, max, min, avg, sum.
(a), (b), (c) can only be used exclusively. Inline mathematical operations in <fields> (after select) like select attrib*1.1 are not supported.

<tablename> specifies the table from which data is to be retrieved. Queries spanning multiple tables aren't supported, hence an attempt to do so should not be made.

<conditions> is a simple expression(not nested) which can be used to selectively select some rows that satisfy the <conditions>. This clause is optional, if omitted, all rows are selected. Rules to form <conditions> expression:
(a) <conditions> must be a simple expression. Nested expressions aren't allowed.
(b) AND or OR keywords can be used to combine two or more conditions in a single <conditions> expression. Alternatively, && or || can also be used.
(c) IS and IS NOT keywords are supported and can be used interchangeably with = and <>(or !=) operators.
(d) NULL keyword is supported. So to signify an empty value either consecutive single quotes( '' ) or NULL can be used. Note that NULL keyword is supported only while forming a <conditions> expression.
(e) LIKE and BETWEEN keywords are not supported. Also, other keywords, unless otherwise mentioned, are not supported.
(f) <conditions> like where attrib4=attrib3*10 are not supported.
<fields> (after order by) is optional, if omitted it selects rows as they are read. Otherwise, it sorts the selected data by <fields>.

<fields> (after order by) is a list(seperated by comma) used to specify any particular fields/attributes according to which the selected data has to be sorted. If you give order by attrib1,attrib2,attrib3 it first sorts the data by attrib1, if any values of attrib1 are same then those rows are sorted by attrib2 and so on. With each field we may also specify the type of sorting i.e. Ascending or Descending, by using asc or desc keywords alongwith each field. If we dont specify the type of sorting, default is ascending (asc).

A successful select query forces dbx_query() to return a SQL result handler, which can be later used to access the result set using dbx_fetch_array() function.
<?

include("/path/to/dbx/dbx.php"); //[path to DBX]

$DBase=new dbx;

$DBase->dbx_query("SELECT DATABASE test");

$q=$DBase->dbx_query("SELECT COUNT(*),MAX(attrib3),COUNT(attrib1) FROM testtable");

$q=$DBase->dbx_query("SELECT attrib3,attrib1 FROM testtable WHERE attrib1<90 AND attrib2 IS NULL");

$q=$DBase->dbx_query("SELECT * FROM testtable WHERE attrib2 IS NOT NULL ORDER BY attrib3 DESC,attrib1,attrib2 ASC");

$q=$DBase->dbx_query("SELECT * FROM testtable");

$q=$DBase->dbx_query("SELECT * FROM testtable WHERE 1"); //equivalent to not giving any where clause

?>
• alter table <tablename> add|drop <fields>
Alter a table.

Alters the structure of the table <tablename>. By altering the structure, we mean adding or dropping field(s) to or from the table <tablename>.

<fields> is a list(seperated by comma) of attributes/fields of the table that are to be added or dropped. When adding field(s), the value(s) of the newly added field(s) are initialized to NULL.
If any error occurs in the execution of a query, dbx_error() returns FALSE(0). Otherwise, it returns TRUE(1). For successful select queries, a SQL Result Handler is returned. This can be later used to access the result set using dbx_fetch_array() function.


More Information Regarding DBX [top]

More information regarding DBX, including scripts that use/have been ported to DBX, updates, tips, tricks etc. and a whole lot of other database related research material is available on the DBX website.

Also, you can contact us for bug reports, feature requests, and just about anything else.

Please visit http://dbx.idya.net for all this.


© 2002 Idya Research. All rights reserved.