chaozz.nl
Free software by Elmar Wenners
News: My entire website now runs on chaozzForum
Welcome guest

Topic: chaozzDB
posted on 2019-05-05 10:45:13
chaozzDB - A full-featured, easy-to-use flatfile (noSQL) database system
©2008-2019 by E. Wenners, The Netherlands


ABOUT
I originally wrote this flat file 'database engine' in a one-night programming session in 2008. I was fiddling around with file handling in PHP and chaozzDB was a sort of proof of concept. Eleven years later, in 2019, I rewrote the database engine from scratch, adding a better query language, better structure and better reliability.


INSTALLATION
Place chaozzDB.php in a folder of your project. Next open chaozzDB.php and find the line that reads "// settings". Here you can change the following settings:

code:
$chaozzdb_delimiter = "\t";

By default the delimiter is a TAB (\t). Please note that changing the delimiter is tricky. It needs to be unique, and nothing a user could type in a free form field. TAB is the best option.

code:
$chaozzdb_location = "./db/";

This is the folder that holds your database files, relative to the script you include chaozzDB in. This folder needs to be chmod'ded to 777

For APACHE users there is a .htaccess file in the DB folder that prevents direct access to the database files (a vulnerability of version 1.2 and lower).
IIS users read this: https://docs.microsoft.com/en-us/iis/manage/configuring-security/use-request-filtering

code:
$chaozzdb_extension = ".tsv";

This is the default file extention for the database files. Each file is a database table. Files need to be CHMODded to 666.

code:
$chaozzdb_salt = "some random string";

If you want to store passwords in your database, you should hash them. chaozzDB has a function built in for this, and that function uses this salt.

code:
$chaozzdb_max_records = 999;

Here you set the maximum number of records a SELECT-query will return.

code:
$chaozzdb_last_error = "";

This variable can be called to display the last error produced by chaozzDB.

 
SETTING UP YOUR DATABASE
To create a table, you need to create a text file (extension must match $chaozzdb_extension) in the database folder ($chaozzdb_location). The name of the textfile will be the table name.
For example:

user.tsv

The first line of a table will define the table fields, seperated by the delimiter (default: TAB).

There are some things you need to keep in mind:
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: 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:
code:
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.


CURRENT LIMITATIONS
There is no way to join tables in one query; LEFT JOIN, RIGHT JOIN or INNER JOIN are not supported. You will have to do multiple queries to achieve the same result.

Make sure that linked tables have fields that link them together.

For example: if you have a table that has one or several records related to a user, make sure that table has a field called user_id. This was you can query like this:
code:
$user_result = chaozzdb_query ("SELECT id, name FROM user WHERE id = 4");
$record_count = count($user_result);
for ($a = 0; $a < $record_count; $a  )
{
	$user_id = $user_result[$a]['id'];
	$car_result = chaozzdb_query ("SELECT id, brand FROM car WHERE user_id = $user_id");
	echo "This user drives a {$car_result[0]['brand']}");
}

Another limitation is that the WHERE part of queries only supports either the AND-operator or the OR-operator. They can not be mixed.

So you CAN do this
code:
$result = chaozzdb_query ("SELECT name FROM user WHERE id > 10 AND name != Bill");
$result = chaozzdb_query ("SELECT name FROM user WHERE name = Bill OR id = 2");

But NOT this:
code:
$result = chaozzdb_query ("SELECT name FROM user WHERE id > 10 AND (name != Bill OR name != Gates)");



NOTE ABOUT STORING DATA
Everything you store in chaozzDB must first be encoded. The best practice for chaozzDB is:

For Integers, use: intval($value);
Every other value, use: urlencode($value);

Here is a short example:
code:
$car = urlencode("Mercedes, convertible"); // the comma after Mercedes would have messed up the Query if we hadn't encoded it
$result = chaozzdb_query ("UPDATE driver SET car = $car WHERE id = 1");

To read this value back:
code:
$cars = chaozzdb_query ("SELECT * FROM driver WHERE id = 1");
echo "Driver 1 drives a ".urldecode($cars[0]['car']);

 

USE CHAOZZDB IN YOUR SCRIPT
To use the database in PHP add this line to the page you want to use it on:
code:
require_once("./chaozzDB.php");

 

CHAOZZDB QUERIES
chaozzDB uses a format very simular to SQL.

Lets go through all the available command. Parameters between [ and ] are optional.


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


Examples:
code:
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:
code:
$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']}";
}

NOTE: It's usually best to use SELECT * instead of a selection of fields. The selection of fields requires extra code, while tiny. The only reason is perhaps to save memory, as the array returned will be smaller.


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


Examples:
code:
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:
code:
$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:
code:
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:
code:
$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:
code:
INSERT INTO user VALUES (chaozz, password123, 1)

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

NOTE: You can use the function chaozzdb_password() to hash passwords, which uses the $chaozzdb_salt as the salt.

The WHERE-part of a query can only use either the AND-operator or the OR-operator. You can use the following comparisons:
code:
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 > 10 // user the OR operator to combine conditions



ERROR CHECKING
There is some 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 caught by chaozzDB, check the variable $chaozzdb_last_error.

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


FINAL NOTES
See 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.

Elmar Wenners - 2019 The Netherlands - www.chaozz.nl

Powered by chaozzForum and chaozzDB