chaozzDB

An easy-to-use flatfile nomysql database system for PHP

About

I originally wrote this flat file ‘database engine’ as a proof of concept in 2008. This concept didn’t include instructions for securing the database files (chmod of the db folder, and files) and was therefor deemed unsecure.

In 2019 I rewrote the database engine entirely, adding a better query language, structure and reliability. The manual you’re currently reading also explains how to secure the database files. The files are on GitHub, so everyone can improve the engine.

 

Install and config

Place chaozzDB.php in a folder of your project. chaozzDB.php contains the following settings you can change:

• $chaozzdb_delimiter = “\t”;
  By default the delimiter is a TAB (\t).
• $chaozzdb_location = “./db/”;
  This is the folder that holds your database files, relative to the script you include chaozzDB in.
  For APACHE users there is a .htaccess file in the DB folder that prevents direct access to the database files.
  IIS users read this: https://docs.microsoft.com/en-us/iis/manage/configuring-security/use-request-filtering
• $chaozzdb_extension = “.tsv”;
  This is the default file extention for the database files.
• $chaozzdb_salt = “some random string”;
  This salt is used by chaozzdb_password();. You must change this to a random string of your own.
• $chaozzdb_max_records = 999;
  Here you set the maximum number of records a SELECT-query will return.
• $chaozzdb_last_error = “”;
  After running a query with chaozzdb_query();, you should check if $chaozz_db_last_error == “”. If it’s not, it contains the error description as a string.

 

Setting up your database

You need to create the database folder ($db_location) and CHMOD it to 777.
To create a table, you need to create a text file (extension must match $db_extension) in the database folder and CHMOD it to 666. The name of the textfile will be the table name.

chaozzdb.php
[database] <-- chmod 777
  user.tsv <-- chmod 666
  device.tsv <-- chmod 666

Example table:
user.tsv

The first line of a table will define the table fields, seperated by the delimiter ($chaozzdb_delimiter).

Here are the requirements for a table:

1. The first field **must** always be ‘id’
2. The cursor **must** always be on a new empty line (so press ENTER↵ after you entered that first line)
3. The field names should be lowercase and alphanumeric (underscore is allowed): id, name, group_id, field9, etc
4. Field names can not contain these words in uppercase:
   SELECT, FROM, WHERE, DELETE, UPDATE, VALUES, INSERT

So for a user table you could create the following fields:

id	name	password	email	group_id

After this line, press enter, so the cursor is on a new empty line.
Now save the file, and you’re done.

 

PHP usage

To use the database in PHP add this line to the page you want to use it on:

require_once("./chaozzDB.php");

 

Functions

• • chaozzdb_password();
    You can use this to salt passwords before storing them into chaozzdb.

    $password = chaozzdb_password('ThisIsMyPassword123');

  • chaozzdb_error();
    Used internally by chaozzdb_query();
  • chaozzdb_query();
    chaozzDB uses a format very simular to SQL. Its syntax is explained below.

 

Table relations

chaozzdb_query(); does not support LEFT JOIN, RIGHT JOIN or INNER JOIN.

If tables have a relation, there should be a field in one of the tables to emphasize that relation.

For example: You have a table named user, and you have a second table that has one or several records related to a user, named permissions. The table permissions should then have a field called user_id.

Using this logic you can query like this:

$user_result = chaozzdb_query ("SELECT * FROM user WHERE id = 4");
$record_count = count($user_result);
for ($a = 0; $a < $record_count; $a++)
{
	$user_id = $user_result[$a]['id'];
	$permissions_result = chaozzdb_query ("SELECT id, isadmin FROM permissions WHERE user_id = $user_id");
	echo "Is this user and admin? {$permissions_result[0]['isadmin']}");
}

 

“WHERE” limitations

For comparing numeric values you can use:

• id = 1 (SQL: id = 1)
• id != 1 (SQL: id != 1)
• id > 1 (SQL: id > 1)
• id < 1 (SQL: id < 1)

For comparing string values you can use:

• name = elmar (SQL: name = ‘elmar’)
• name != elmar (SQL: name != ‘elmar’)
• name ~= lma (SQL: name LIKE ‘%lma%’)

A limitation is that the WHERE part of queries only supports either the AND-operator or the OR-operator. They can not be mixed. Nor does it respect any left or right parenthesis.

Examples:

WHERE user_id = 10 // user_id equals 10
WHERE user_id !=10 // user_id does not equal 10
WHERE name ~= admin // name contains the word admin (best practice is to urlencode this value if it's not an integer)
WHERE user_id < 10 // user_id is smaller then 10
WHERE user_id > 10 // user_id is bigger then 10
WHERE user_id < 10 AND name = admin // use the AND operator to combine conditions
WHERE user_id = 1 OR user_id = 5 OR user_id > 10 // user the OR operator to combine conditions

 

Encoding and decoding data

Eve

rything you write to or read back from chaozzDB must first be encoded or decoded.

For writing or reading Integers, use: intval();
For every other value, use: urlencode(); for writing or use urldecode(); for reading.

Write example:

$car = urlencode("Mercedes, convertible"); // this comma would mess up the Query if we didn't encode it
$result = chaozzdb_query ("UPDATE driver SET car = $car WHERE id = 1");

Read example:

$cars = chaozzdb_query ("SELECT * FROM driver WHERE id = 1");
echo "Driver 1 drives a ".urldecode($cars[0]['car']);

 

SELECT (FROM, [WHERE], [ORDER BY] and [LIMIT])
Return value: multidimensional array or an empty array (empty array means an error occured)

Examples:

SELECT * FROM user
SELECT id, name FROM user WHERE group_id > 1
SELECT id FROM user WHERE name ~= admi ORDER BY name DESC LIMIT 1
SELECT id FROM user WHERE id > 1 AND id < 10
SELECT id FROM user WHERE name = Bill OR name = Gates

PHP example:

$result = chaozzdb_query ("SELECT id, name FROM user WHERE group_id = 1");
if (count($result) > 0)
{
	// loop through the results
	for ($i = 0; $i < count($result); $i++)
		echo "The user called ".urldecode($result[$i]['name'])." has the ID {$result[$i]['id']}";
}

SELECT * is faster than SELECT field1, field2 because it executes less code. It does however return a bigger array, thus is less memory efficient.

 

DELETE (FROM and [WHERE])
Return value: true or false (false means an error occured)

Examples:

DELETE FROM user
DELETE FROM user WHERE name != administrator
DELETE FROM user WHERE id > 1 AND id < 10
DELETE FROM user WHERE name = Bill OR name = Gates

PHP example:

$name = "Gates, Bill";
$name = urlencode($name);
$result = chaozzdb_query ("DELETE FROM user WHERE name != $name");

 

UPDATE (SET and [WHERE])
Return value: true or false (false means an error occured)

Examples:

UPDATE user SET name = bill, group_id = 2 WHERE id > 1
UPDATE user SET name = bill, group_id = 3 WHERE id > 1 AND name = Hank
UPDATE user SET name = Bill Gates WHERE name = Bill OR name = Gates

PHP example:

$name = "Gates, Bill";
$name = urlencode($name);
$result = chaozzdb_query ("UPDATE user SET name = $name, group_id = 2 WHERE id > 1");

 

INSERT (INTO and VALUES)
Return value: ID of new record or 0 (0 means an error occured)

examples:

INSERT INTO user VALUES (chaozz, password123, 1)

PHP example:

$name = urlencode('Gates, Bill');
$password = chaozzdb_password ($password);
$group_id = 1;
$result = chaozzdb_query ("INSERT INTO user VALUES $name, $password, $group_id");
echo "The ID of this new user is $result";

 

Error checking

There is basic error checking in chaozzDB; it will check for the existence of database files, and if there are any records present, but it does not check for bad queries. Use the proper syntax as explained in this document.

If you want to see the last error that was thrown by chaozzDB, check the variable $chaozzdb_last_error.

If $chaozzdb_last_error is an empty string then the last query was succesful.

Examples:

if ($chaozzdb_last_error != "")
{
	echo "An error occured: $chaozzdb_last_error");
	// panic here
}

 

Final notes

See the included licence.txt for more details about this product’s licence.
If you find it useful, perhaps you can show appreciation by making a small donation.

Download chaozzDB from Github.