• Introduction
• Details
• What API is available at the moment?
• How do I begin to work with MooSQL?
• Creating tables
• Table 1. Column types
• Inserting values into a table
• Performing select requests
• Parameters:
• Return value:
• Deleting rows
• Updating rows
• Return values
• Notes

Introduction

The MooSQL is a project which has the intention to build an RDBMS in pure PHP with support of MySQL syntax for queries and which would have relatively high performance on big data sets.

Details

What API is available at the moment?

MooSQL now only supports the core API, primarily YNDb.

It supports create() / select() / insert() / delete() and update() methods which allow you to create new tables, perform simple select queries from tables, insert data into the table, delete rows and update contents of existing rows (you can extend text fields, but in this case row splitting will occur which can decrease performance).

The YNDb will throw an Exception() in case of any error, so no need to check return values for insert/delete/update methods.

YNDb supports indexed and auto-incremented fields with ability to have UNIQUE indexes as well. Text fields cannot be indexed at the moment.

How do I begin to work with MooSQL?

First, do svn checkout of the latest version from /trunk to some directory, e.g. moosql.

Then, in your PHP script, include moosql/Client.php and create new YNDb instance:

<?php
include 'moosql/Client.php';

$MOO = new YNDb('data_directory');
...

The data_directory is the directory where MooSQL will store its' data. This directory must be readable and writable for a PHP script. Also it is recommended to deny web access to this directory either by placing it outside DocumentRoot or by placing .htaccess file with the following contents (Apache with enabled AllowOverride only):

deny from all

Creating tables

To create table, call $MOO->create(string $name, array $fields, array $params) method, with the following format for parameres:

$name:

Table name. Table name is directly translated to file names, so it can be case-sensitive if the server filesystem is.

$fields:

array(
	'keyname1' => 'type1',
	...,
	'keynameN' => 'typeN',
);

Table 1. Column types

$params:

array(
	'AUTO_INCREMENT' => 'autofield',
	['UNIQUE'        => array('uniquefield1', ..., 'uniquefieldN'), ]
	['INDEX'         => array('indexfield1',  ..., 'indexfieldN'), ]
);

Inserting values into a table

To insert a row, call $MOO->insert(string $name, array $data) with the following parameters:

$name: Table name.

$data:

array([
	'field1' => 'value1',
	...,
	'fieldN' => 'valueN',
]);

Value for AUTO_INCREMENT column can be supplied in case you want to insert a row with ID which has been previously deleted. Arbitrary values for AUTO_INCREMENT column are not supported, use UNIQUE index for that purpose.

You can skip fields so that they get the default value (0 for integer/double types, empty string for text types). NULL values are not currently supported.

Performing select requests

To select some data from the table use $MOO->select(string $name[, array $crit = array()]); method.

Parameters:

$name: Table name

$crit:

Array with select criteria. All keys are optional.

$crit keys:

Supported tokens:

array( COLUMN_NAME, OPERATOR, VALUE ) -- usual condition

Supported operators:

Operators =, >, < and IN are supported. In case you use IN, VALUE must be presented in a form of array(value1, value2, ...)

Return value:

Returns array( array('column_name1' => 'value1', ...), ... ) -- a list of associative arrays with column_name => value pairs.

Deleting rows

To delete rows from the table use $MOO->delete(string $name[, array $crit = array()]); method.

The parameters and return values are absolutely identical to select() with the exception that the returned rows are deleted.

Updating rows

To update rows in the table use $MOO->update(string $name, array $crit, array $new_data); method with the following parameters:

$name: Table name

$crit: Criteria for rows to update in the same format as in select() method.

$new_data: Associative array of values to replace old ones in format column_name => new_value

Return values

The return value is the same as in select() method.

Notes

New AUTO_INCREMENT column values cannot exceed internal AUTO_INCREMENT counter and can only have values of previously deleted rows.