Putting a friendly face on SQL since August 2009!

Sqool stands for Standard Query Object-Oriented Language and is a user-friendly Object Relational Database Facade for PHP (a database abstraction layer that mimics an object-oriented database management system, for PHP). It is similar to an ORM, but doesn't need you to explicitly specify the mapping - you can just use it without worrying about the database layer! Sqool is made to have both extendable operations and extendable optimizations. Right now, Sqool uses php's mysqli extension. Hopefully in the future, Sqool will also provide a way to plug in support for other database systems.

Currently Sqool supports primitives and objects. Lists are almost fully supported.

Download the php: Sqool.zip
Download the php and tutorials here: Sqool-Tutorials.zip
Contribute on github!

One of the great things about Sqool is that there is nothing to set up. All you need to do is download cept.php and SQOOL_0.6.php, make sure you have a database server set up with your name and password, then run the following source code (using your name and password instead). You don't even have to change the database name - it will create it for you if you don't already have that database on your server.

The following class extends 'sqool' (the only class defined by the Sqool library). To define a Sqool class type, you need to create a function called 'sclass'.

This method will affectively only be executed once (when you instantiate the first object of this type), and defines the members you want to have in the class and their types, just like you would in a stronly typed language like C++. The members are written inside a string, with the format "type: name type: name" etc. You don't need to put commas or anything between each member definition, just whitespace.

	class boo extends sqool
	{	function sclass()
		{	return 
			'string:	moose
			 bool:		barbarastreiszand
			 string:	flu
			 float:		moose2
			 int:		fly

The first thing you should probably do is define a new connection. sqool:connect doesn't actually connect immediately, but it returns a sqool object that represents the database. The sqool object ($a in this case) will connect to the database the first time it needs to (lazy connection).

Also, since this is a tutorial, its nice to show some debugging information. By turning debugging on (true) with the 'debug' method, the SQL commands sent to the server will be printed out for you to see when it sent. The 'queue' method tells the sqool class ($a) to start storing methods for later execution. Don't worry about it for now, we'll explain it a little later.

	$a = sqool::connect("yourUserName", "yourPassword", "aDatabaseName");
	$a->debug(true); // this will print out all the SQL queries that run

This next bit of code creates a new object from the user-defined class 'boo'. It then sets one of its string members to "some string value", just as you could with a normal object. Lastly, it uses sqool's 'insert' method to insert this new object into the database (represented by $a).

If the table 'boo' does not exist, don't worry, it will be created for you. Note that it doesn't check to make sure the table exists first, it simply expects the table to be there and creates it if SQL returns a 'table doesn't exist' error.

Also notice that the 'insert' method returns a new object (captured by $newObject). This new object has an ID, which means it can use the 'save' and 'fetch' methods (explained in just a second). The $object variable, on the other hand, does not have an ID and thus cannot use methods that require it to be "in" a database.

Note that 'insert' does not modify its argument, you can safely use this to copy an object from one database into another.

	$object = new boo();
	$object->moose = "some string value";
	$newObject = $a->insert($object);
Here, the code assigns $newObject's member 'fly' with the value 45. Then saves it. Since $newObject has an ID and represents an object/table in the database, saving does what you would expect - the value 45 will be written into the database for that member.
	$newObject->fly = 45;   

The next method call is the final major method in the sqool class. As you might expect, 'fetch' gets objects and object members from the database.

Fetch can take semi-complicated arguments describing exactly what objects, object members, and sub-object members (etc) to grab from the database, however here it is simply getting the object "boo".

As you can see above, 'boo' is a sqool class type we already defined. The sqool object representing the database ($a in our case) treats database classes as array members (lists of objects of each class type). Each of these list-members is named after their class. So in this case, the fetch method is geting the whole list of "boo" objects.

Fetch can have much more complicated arguments than this. For example, if we had wanted to grab only the members moose and flu from the first 3 boo objects whose moose2 member is less than 0.5 and sort them by the fly member, we could do that by doing

$a->fetch("boo[members: flu moose cond: moose2 < .5 range: 0 2 sort: fly]")
, but thats for another tutorial.


Finally, we can see some data "in motion" so to speak. Remember that 'queue' method from earlier in the tutorial? Now its time to process that queue. The 'go' method is what does that. To explain, 'queue' tells the sqool object representing the database to queue up all the database commands you ask it to make, so sqool never actually executes them until you say 'go'.

This is important for two reasons:
  1. It is faster, because sqool (ideally) only has to make one request to the database server per 'go' (rather than making one request for every sqool call you make).
  2. It allows optimizations that would otherwise be impossible, including optimization plugins users can write themselves.
But using the 'queue' and 'go' methods isn't strictly neccessary. But if you don't use 'queue' and 'go', sqool may have to make a database request every time you call a sqool method. This can lead to significant delay caused by communication beween the script and the database server.
Lastly, we can print out the data you wrote to the database. Here, the number of 'boo' objects is printed, then the code grabs the first 'boo' object from $a's list, and prints out the values of its members. As you can see if you run the tutorial script, string members are initialized to the empty string "", bool members are initialized to false, and other members are initialized to 0.
	echo "count($a->boo) == ".count($a->boo)."
\n"; $boo0 = $a->boo[0]; echo '$boo0->moose == "' .$boo0->moose.'"'."
\n"; echo '$boo0->barbarastreiszand == "'.$boo0->barbarastreiszand.'"'."
\n"; echo '$boo0->flu == ' .$boo0->flu."
\n"; echo '$boo0->moose2 == ' .$boo0->moose2."
\n"; echo '$boo0->fly == ' .$boo0->fly."
Extending Sqool
Sqool is written to be extendable by anyone. There are three ways to extend Sqool:
Why I don't use PDO
There's a very simple reason: PDO can't do multi-queries. A multi-query is when you bundle multiple SQL commands up and send them to the database server all at once. There are a few reasons to do this:
  1. It is faster, because there is significant overhead in setting up a request to a database server.
  2. It allows Sqool to operate like a RISC architecture - multiple simple instructions can create faster and more elegant code than complicated instructions. Its true when building a CPU ISA, and its true here too. Rather than building complicated join statements, which return redundant data and are difficult to put together, Sqool does multiple separate SELECTs in a multi-query.
If you want to write or help write a Sqool extension for doing queries with PDO, feel free (once I get around to adding that capability...). I almost guarantee it will be slower though.