Using a flat file database rather than SQL

[Stew_822]

Hi guys,

 

This isn't a question; more of a, hi, here's what I've been up to, maybe it's interesting to you. 

 

Excuse the lengthy post, I've been working on finding a neat implementation of how to represent the schema of a database (and from that, generate a usable database) and I think I've finally found it.

 

I've been developing a website that enables users to create a virtual representation of their aquarium, add species to it and see how it pans out virtually before buying their fish and killing them. There is a lot of links between the different tables, eg. a species has a habitat, the users who collatorated to create the profile of the species. Users have their tanks; tanks have species; and so on. 

 

I don't like creating a table in SQL to materialise a relationship, so I'm designing my own database system for this project which uses files to store the data, and symlinks to create references to other pieces of data ('records', rather than 'rows').

 

It's a hobby project.

 

I've been doing a lot of reading on designing databases and object oriented patterns, and I've come up with the following database system (which has a little way to go before it's usable). I've tried to make it double as a database abstraction layer so that if I find the performance ain't that great, I have the option of switching to SQL.

 

On to the code; an example of creating some records and then finding them:

// everything under the 'my' namespace is specific to the custom 
// database we're talking about. The my\database class isn't 
// anything special, it's just a class
$db = new my\database();

// the $db->users variable is an instance of zest\database\handle
$frank = $db->users->create();
$john =  $db->users->create();

// $frank and $john are instances of zest\database\record
$frank->name = "Frank the Farmer";
$john->name = "John Doe";

// you have to insert users before assigning them as references
$db->users->insert( $frank );
$db->users->insert( $john );

$species = $db->species->create();
$species->common_name = "Platy";

// contributors is an array containing references to users
$species->contributors = [ $user, $user2 ];
$db->species->insert( $record );

$species = $db->species->find( $species->id );
// it's all lazy loaded, which is OK because it's a flat file system
echo $species->common_name; // Platy

// you can load many at once - which would hopefully increase performance if swapping to mySQL
$species->load( 'common_name', 'contributors' /* ... */ );
// or, being a little bit fancy here
$species = $db->species->find( $species->id )->load( "common_name", "contributors" );

// you can search for species - not sure how performance would be for SQL queries though.
// it might be better to implement SQL-style queries, but screw that - I have a life
$species_list = $db->species->search( function( $record ) {
	if( $record->ph_lower > 4.5 and $record->ph_upper < 8 ) {
		return true; // and so add it to the list
	}
	else return false;
} );

In the file my/database, which is basically a singleton that is the database object (not related to my database classes, but a convenience):

<?php

namespace my;

class database {
	public $species;
	public $users;
	
	function __construct() {
		$root = "data";
		$this->species = new \zest\database\handle(
			"$root/species",   // folder you want to use to store records
			species\schema( $this ), // the schema of your database - how your records should look
			'\my\species\record' // the class you want the records returned as
		);
		$this->users = new \zest\database\handle( 
			"$root/user",    
			user\schema( $this )
		);
	}
}

And finally, an example of a schema, which is usually the most confusing bit of a database mapper or ORM (my\species\schema):

$schema = new \zest\database\schema();
$schema
	->property([
		'name' => 'common_name', 
		'type' => new types\string(),
		'required' => true
	])
	->property([
		'name' => 'contributors',
		'type' => new types\vector( new types\reference( $database->users ) ),
		'default' => array()
	])
	//->behaviour( new behaviours\timestampable() )
;

The most powerful thing going on here is the 'type' section of the property function. You can have a vector of a vector of vectors containing a reference to whatever other database handle that you want. Or, you could have a vector of string, or an int field - or you could define your own type. If you have a one-to-one relationship, you'd put just a reference in there. It's easy and type-safe. The name is obviously the name of the property, 'default' is what it defaults to if it isn't given and 'required' is whether it can or can't be null.

 

 

If you just pass this schema to the database\handle, it'll only accept records which match the schema. Schemas will also eventually be able to define behaviours, like in Propel - because that is a really cool concept. But the fact that I had to define cross reference tables in Propel ruined it for me; the ORM should be able to figure out if you need cross reference tables, and, if you do, it should create them for you. That's what I tried to code first and still use SQL, but it was pretty difficult (not impossible, mind) so I decided to persist data using regular files instead. 

 

I'm curious as to if anyone has implemented a flat file database similar to what I am doing, and whether or not performance suffers from multiple reads and writes? Obviously, I cache the data in the record (in a pretty fancy way, if I do say so myself) but each property is stored in a single file, eg. the "common_name" of a species is stored in it's own file.

 

I've attached the current files for this database project, but it isn't finished yet. 

 

I hope you've enjoyed reading and maybe have a new perspectives on databases. They shouldn't have to be as difficult and confusing as they currently are. In a perfect world, you wouldn't have to know about cross referencing tables - your ORM should take care of that automatically. It's easier with a flat file database, but it wouldn't be impossible to write some convoluted code that converts the schema I presented above to a schema of a mySQL database - but ideally it should happen behind the scenes. Which would be the case if my site ever takes off, I start actually earning money and have reason to optimise the database. Until then, the flat file thing is much simpler and will suffice! 

database.zip

my.zip

Edited February 12, 2015 by Stew_822

[elemen121]

Just wondering what you find difficult and confusing about databases?

Personally I've worked with them for years and never thought that.