Docsqlite Part Two

Back again...

In Part 1 I made a very poor but workable nosql engine on top of a sqlite, a sql relational database. Response from a few folks on twitter was great. I've decided that this could actually be put to some use. Especially for smaller websites or for mockup prototypes.

Here in Part 2 I wanted to go some steps further. I decided on the following:

Here is my new file: db.php

Some Sample Play Code


  // Testing the document system here.
  // Delete all the documents in the "squares" collection.
  deleteDocuments( findDocuments( "squares" ) );

  // Create 100 documents from 1 - 100 with odd and even and square values.
  $i = 1;
  while( $i <= 100 ) {

    $doc = array();
    $doc['type'] = ( $i % 2 == 0 ) ? "even" : "odd";
    $doc['base'] = $i;
    $doc['square'] = $doc['base'] * $doc['base'];
    // Save it to the squares collection.
    saveDocument( "squares" , $doc );

    $i++; // Next

  // Load all the documents from the squares collection
  // that have a base 5 or larger
  // CAST AS INTEGER for sorting.
  // OFFSET 5 so begin at 10.
  // LIMIT 2 only give me 2 back.

  $docs = loadDocuments( findDocuments( "squares" , 'base' , "5" , ">=" , "INTEGER" , "ASC" , "5" , "2" ) );

  // Print out the documents.
  die( "<pre>" . print_r( $docs , TRUE ) . "</pre>" );


    [0] => Array
            [_collection] => squares
            [_timestamp] => 1385634629
            [base] => 10
            [square] => 100
            [type] => even
            [_document_id] => doc_52971b45279b83.59470686

    [1] => Array
            [_collection] => squares
            [_timestamp] => 1385634629
            [base] => 11
            [square] => 121
            [type] => odd
            [_document_id] => doc_52971b4528adb7.82481724


What de fah was that findDocuments function?

Well, the document system now has a findDocuments function:

    function findDocuments( $collection, 
                            $key = NULL, 
                            $value = NULL, 
                            $op = "=", 
                            $cast = "TEXT", 
                            $order = "ASC", 
                            $offset = NULL, 
                            $limit = NULL )

To be honest, I have no idea how I dreamt this up. I just started with a simple find documents with key = value. Then I began adding from there. At this point I am still not 100% comfortable with it. So I am not going to go into it a great deal to explain it because I am probably going to change it later. Needless to say findDocuments let's you query your documents. The cast variable was needed for sorting things numerically or alphabetically. Also, because almost every website has some sort pagination, offset and limit had to come in.

I need to do more reading how mongo and couch do there querying, this is the hardest part of nosql.

Why collections?

It's not that I am MongoDB fan persay that I added collections. It's because I can see a situation where two documents have a key/value of color='blue'. However, document A) is a car and document B) is a house. Without a collection, in order to get just the "Houses with blue color" I would need to conduct multiple searches and loops. By having a collection we can store documents atleast in a organized "drawer" and get them back later more easily.

Why the timestamp?

A unique id is not really good for ordering later on. Addionally we can't tell when this document was born. At the end of the day it's going to be alot better to have a timestamp of when the document was created. For example, if you want to make sure the same IP doesn't sumbit a form more than once in 5 minutes. You are going to need to see the timestamp of the last IP="x.x.x.x".