Docsqlite

Waaa?

After searching nosql database systems such as mongo and couch, I got my self to thinking: "Would it be possible to make a poor man's DOC store in PHP with sQlite?".

Why da Fahq would you want to do that?

For most small websites and projects I use sqlite as the database. I like it for it's simplicity and embeddable nature. So after looking and finding no natively built embeddable nosql options that worked with PHP, I set out to create my own.

Not sure if I am actually going to use this in a production site. Maybe just keep it around as an example.

However, I am happy with the result for now. With this you are able to make dynamic growing, shrinking PHP array documents, stick them in the DB, and get them back later.

Wait a minute!

Yes, I am going to be using a relation database, "sQlite". To store dynamic documents. No this is not a recommended practice.

This is purely an excercise to see if it can be done and what will be the performance.

NO!, this will be not anything near as complete/awesome/finished as Mongo DB. Additionally, this will work only with PHP arrays. So no objects. With some more playing around it probably can be object orientated.

Let's Start

There are 4 functions and 1 database table to get this working. There are some other helper functions but they are not critical for what we are doing right now. You can get the full db.php file later on.



// This function creates a table to which we will make the DKVS ( Document, Key, Value, Store ) on.
function initDKVS() {

  // If we don't have the base of teh DKVS system create the table.
  if( ! dbTableExists( "dkvs" ) ) {
    $q = "CREATE TABLE 'dkvs' ".
         "('document_id' TEXT NOT NULL, 'key' TEXT NOT NULL, 'value' BLOB, PRIMARY KEY ('document_id', 'key'))";
    $qr = dbQuery( $q );
  }

}


// This function will save a document ( PHP Array ), recursively.
function saveDocument( &$document ) {

  // This is a poor man's document store meant to work only with PHP arrays.
  if( ! is_array( $document ) ) {
      die( "DKVS ERROR :: At this time only arrays are supported in the DKVS system." );
  }


  // Do we even have a document key value table?
  // You can probably take this out if you are sure.
  initDKVS();


  // When we save we don't want to save the docment_id
  $document_id = false;
  if( isset( $document['_document_id'] ) ) {
    $document_id = $document['_document_id'];
    unset( $document['_document_id'] );
  }

  // Did we have a document id?
  if( $document_id ) {
    // Did we already have document in the system with this id?
    // Delete it out be fore resaving.
    deleteDocument( $document_id );

    // This is the hulk smash way. Porbably not so nice.
    // Delete the entire document out before we say again.
    // Then again maybe this is what we want.

  } else{
    // other wise we set a document_id
    // cause this is a new one.

    // It's probably bad to do this too. However, UUID is not
    // What I am concerned about right now.
    $document_id = uniqid( "doc_" , true ); 
  }

  
  // Go through the array and save it to the db.
  foreach( $document as $k => $v ) {
    
    $data = array();
    $data['key'] = $k;

    // Handle sub arrays
    if( is_array( $v ) ) {
      $v = saveDocument( $v );
    }

    // Set the value and doc id.
    $data['value'] = $v;
    $data['document_id'] = $document_id;
    
    // Insert it into the DB.
    $q = dbInsertFromVals( "dkvs" , $data );
    $qr = dbQuery( $q );

  }

  // Reset this to what it was or the new one.
  $document['_document_id'] = $document_id;
  return $document['_document_id'];

}


// This function will recursively load a document and return it.
function loadDocument( $document_id ) {

  // Do we even have a document key value table?
  initDKVS(); // if we don't, we probbably got other troubles.

  $r = array(); // blank array.

  $q = "SELECT * FROM dkvs WHERE document_id = ".dbEscapeString( $document_id );
  $qr = dbQuery( $q );
  while( $qrow = dbFetch( $qr ) ) {
    $r[$qrow['key']] = $qrow['value'];

    // Hey whoa, is this a sub document, then let's load it and roll.
    if( strpos( $qrow['value'] , "doc_" ) === 0 ) {
      $r[$qrow['key']] = loadDocument( $qrow['value'] );
    }

  }

  // The document ID.
  $r['_document_id'] = $document_id;

  return $r;

}



// This function will recursively delete a document from the database.
function deleteDocument( $document_id ) {

  // Load up the document that we are going to delete
  $document = loadDocument( $document_id ); 

  // Go through and see if there are sub arrays.
  foreach( $document as $k => $v ) {
    if( is_array( $v ) && isset( $v['_document_id'] ) ) {
      deleteDocument( $v['_document_id'] );
    }
  }

  // Delete everything of me from the DB.
  $q = "DELETE FROM dkvs WHERE document_id = ".dbEscapeString( $document_id );
  $qr = dbQuery( $q );

}

How to Use It

Just create a mix of PHP arrays and then call saveDocument.

To do advanced queries with sorting and filtering, you are going to have to use SELECT sql statement. So to get all the docs where "some_key" is greater than 5 and order that by the value ascending, see other example.


        "WHERE key = 'some_key' AND CAST( value AS INTEGER ) > 5 ORDER BY CAST( value AS INTEGER ) ASC"
    

Sample PHP Play


    $test = array();

    $test[] = "Cleveland";
    $test[] = "Pittsburgh";
    $test[] = "Grand Rapids";


    $test2 = array();

    $test2['cities'] = $test;
    $test2['type'] = "cities_ive_been_in";

    $doc = saveDocument( $test2 );
    
    $test = loadDocument( $doc );
    
    deleteDocument( $test['_document_id'] );


The Result

This is the var dump of "$test" after.



Array
(
    [cities] => Array
        (
            [0] => Cleveland
            [1] => Pittsburgh
            [2] => Grand Rapids
            [_document_id] => doc_528363da0af889.06350676
        )

    [type] => cities_ive_been_in
    [_document_id] => doc_528363da0af507.03779021
)

Another Example

    $start = microtime( true );

    $i = 0;

    while( $i++ < 1000 ) {
        $t = array();
        $t['type'] = "square";
        $t['number'] = $i;
        $t['square'] = $i * $i;
        saveDocument( $t );
    }

    $q = "SELECT DISTINCT( document_id ) AS document_id FROM dkvs WHERE key = 'number' AND CAST( value AS INTEGER ) BETWEEN 13 AND 25 ORDER BY CAST( value AS INTEGER ) DESC";
    $qr = dbQuery( $q );
    $documents = dbFetchAll( $qr );

    foreach( $documents as $k => $document ) {
        $documents[$k] = loadDocument( $document['document_id'] );
    }

    $assigns['document'] = $documents;

    $q = "SELECT DISTINCT( document_id ) AS document_id FROM dkvs WHERE key = 'type' AND value = 'square'";
    $qr = dbqUery( $q );

    $documents = dbFetchAll( $qr );

    foreach( $documents as $k => $document ) {
        deleteDocument( $document['document_id'] );
    }

    $end = microtime( true );


    $assigns['time'] = $end - $start;

This took about 8.7 seconds to run for 1000 squares ( iMac i5 ). So hopefully you see this is not a enterprise level production solution. For 100 squares it was less than 1 second. Maybe this thing can be used for small things though.

I still want it

Get the db.php source with all the helpers and functions. Copy and paste this code where you like. The main function for this are at the bottom.

View db.php