JSONDB_{PHP Edition}

JSONDB is installable through composer:

$ composer require na2axl/jsondb

You can also download JSONDB on github and install it yourself: JSONDB on Github

All JSONDB classes are accessible through the namespace JSONDB. So, you have to load JSONDB with:

try {
    $jsondb = new \JSONDB\JSONDB();
} catch (\JSONDB\Exception $e) {
    echo $e->getMessage();
}

When JSONDB is instantiated, you can connect to a server. If you haven't created a server, you can create it using JSONDB::createServer()

$jsondb->createServer('server_name', 'username', 'password', $connect);

The parameter $connect is an optional boolean used to set if JSONDB have to be connected to the server after the creation. If it's set to TRUE, you have to assign the returned connection handler to a variable:

$database = $jsondb->createServer('server_name', 'username', 'password', TRUE);

Example:

$jsondb->createServer('test_server', 'root', '');

Once instantiated, you have to connect to a server before send queries.

$db = $jsondb->connect('server_name', 'username', 'password', 'database_name');

• The 'server_name' is the name to the server. The server have to be created before with JSONDB::createServer()
• The 'username' and the 'password' are the information used to connect to the database. These information are the same used when creating the server
• The 'database_name' is the name of the database to use with current connection. This parameter is optional and can be set manually later.

Example:

$db = $jsondb->connect('test_server', 'root', '');

After connected to server, you can create a database:

$db->createDatabase('database_name');

Your new database will be a folder in your server in which each table will be a .json file.

Example:

$db->createDatabase('test_database');

To use a database, you can specify his name when connecting to a server, or use JSONDB::setDatabase() after a connection:

$db->setDatabase('database_name')

Example:

$db->setDatabase('test_database');

Once JSONDB is connected to a server and use a database, you can create a new table with JSONDB::createTable():

$db->createTable('table_name', $prototype);

The $prototype is an array of 'column_name' => $column_properties pairs. Each $column_properties are also an array which contains a list of 'property_name' => 'property_value' pairs.

There is a list of currently supported column properties:

• 'type': Defines the type of values that the column accepts. This property can take only one value. Supported types are:
  • 'int', 'integer', 'number'
  • 'decimal', 'float'
  • 'string'
  • 'char'
  • 'bool', 'boolean'
  • 'array'
• 'default': Sets the default value of column. The default value must be match the 'type' of the column.
• 'max_length': Used by some type:
  • When used with 'float', the number of decimals is reduced to his value
  • When used with 'string', the number of characters is reduced to his value (starting with the first character)
• 'not_null': Can take values TRUE or FALSE. Defines if a column have to be set to null when there is no value in the row. Doesn't work when a 'default' value is given
• 'auto_increment': Can take values TRUE or FALSE. Defines if a column will be an auto incremented column. When used, the column is automatically set to UNIQUE KEY
• 'primary_key': Can take values TRUE or FALSE. Defines if a column is a PRIMARY KEY
• 'unique_key': Can take values TRUE or FALSE. Defines if a column is an UNIQUE KEY

Example:

$db->createTable('users', array('id' => array('type' => 'int', 'auto_increment' => TRUE),
                                'name' => array('type' => 'string', 'max_length' => 30, 'not_null' => TRUE),
                                'surname' => array('type' => 'string', 'max_length' => 30, 'not_null' => TRUE),
                                'pseudo' => array('type' => 'string', 'max_length' => 15, 'unique_key' => TRUE),
                                'mail' => array('type' => 'string', 'unique_key' => TRUE),
                                'password' => array('type' => 'string', 'not_null' => TRUE),
                                'website' => array('type' => 'string'),
                                'activated' => array('type' => 'bool', 'default' => FALSE),
                                'banished' => array('type' => 'bool', 'default' => FALSE)));

JSONDB can send both direct and prepared queries.

Direct queries

$results = $db->query($my_query_string);

//// Specially for select() queries
// You can change the fetch mode
$results->setFetchMode(\JSONDB\JSONDB::FETCH_ARRAY);
// or...
$results->setFetchMode(\JSONDB\JSONDB::FETCH_OBJECT);
// or...
$results->setFetchMode(\JSONDB\JSONDB::FETCH_CLASS, 'MyCustomClassName');
// Explore results using a while loop
while ($result = $results->fetch()) {
    // Do stuff...
}
// Explore results using a foreach loop
foreach ($results as $result) {
    // Do stuff...
}

Prepared queries

$query = $db->prepare($my_prepared_query);
$query->bindValue(':key1', $val1, \JSONDB\JSONDB::PARAM_INT);
$query->bindValue(':key2', $val2, \JSONDB\JSONDB::PARAM_STRING);
$query->bindValue(':key3', $val3, \JSONDB\JSONDB::PARAM_BOOL);
$query->bindValue(':key4', $val4, \JSONDB\JSONDB::PARAM_NULL);
$query->bindValue(':key5', $val5, \JSONDB\JSONDB::PARAM_ARRAY);
$results = $query->execute();

//// Specially for select() queries
// You can change the fetch mode
$results->setFetchMode(\JSONDB\JSONDB::FETCH_ARRAY);
// or...
$results->setFetchMode(\JSONDB\JSONDB::FETCH_OBJECT);
// or...
$results->setFetchMode(\JSONDB\JSONDB::FETCH_CLASS, 'MyCustomClassName');
// Explore results using a while loop
while ($result = $results->fetch()) {
    // Do stuff...
}
// Explore results using a foreach loop
foreach ($results as $result) {
    // Do stuff...
}

JSONDB support a lot of common database queries, and use JQL, a custom query language...

JQL is the query language used by JSONDB to send queries. It's a very easy language based on extensions. A JQL query is in this form:

$db->query('table_name.query(parameters, ...).extensions(parameters, ...)...');

Query example with select():

// Select all from table `users` where `pseudo` = $id and `password` = $pass or where `mail` = $id and `password` = $pass
$id = \JSONDB\JSONDB::quote($_POST['id']);
$pass = \JSONDB\JSONDB::quote($_POST['password']);
$db->query("users.select(*).where(pseudo={$id},password={$pass}).where(mail={$id},password={$pass})");

// Select `pseudo` and `mail` from table `users` where `activated` = true, order the results by `pseudo` with `desc`endant method, limit the results to the 10 users after the 5th.
$db->query("users.select(pseudo, mail).where(activated = true).order(pseudo, desc).limit(5, 10)");

Query example with insert():

// Insert a new user in table `users`
$pseudo = \JSONDB\JSONDB::quote($_POST['pseudo']);
$pass = \JSONDB\JSONDB::quote($_POST['password']);
$mail = \JSONDB\JSONDB::quote($_POST['mail']);
$db->query("users.insert({$pseudo},{$pass},{$mail}).in(pseudo,password,mail)");

// Multiple insertion...
$db->query("users.insert({$pseudo1},{$pass1},{$mail1}).and({$pseudo2},{$pass2},{$mail2}).and({$pseudo3},{$pass3},{$mail3}).in(pseudo,password,mail)");

Query example with replace():

// Replace information of the first user
$db->query("users.replace({$pseudo},{$pass},{$mail}).in(pseudo,password,mail)");

// Multiple replacement...
$db->query("users.replace({$pseudo1},{$pass1},{$mail1}).and({$pseudo2},{$pass2},{$mail2}).and({$pseudo3},{$pass3},{$mail3}).in(pseudo,password,mail)");

Query example with delete():

// Delete all users
$db->query("users.delete()");

// Delete all banished users
$db->query("users.delete().where(banished = true)");

// Delete a specific user
$db->query("users.delete().where(pseudo = {$pseudo}, mail = {$mail})");

Query example with update():

// Activate all users
$db->query("users.update(activated).with(true)");

// Update my information ;-)
$db->query("users.update(mail, password, activated, banished).with({$mail}, {$pseudo}, true, false).where(pseudo = 'na2axl')");

Query example with count():

// Count all `banished` users
$db->query("users.count(*).as(banished_nb).where(banished = true)");

// Count all users and group by `activated`
$db->query("users.count(*).as(users_nb).group(activated)");

Query example with truncate():

// Reset the table `users`
$db->query("users.truncate()");

try {
    $jsondb = new \JSONDB\JSONDB();

    $db = $jsondb->createServer('test_server', 'root', '', TRUE);

    $db->createDatabase('test_database')
       ->setDatabase('test_database'); // Yes, is chainable ! ;-)

    $db->createTable('users',
                     array('id' => array('type' => 'int', 'auto_increment' => TRUE),
                           'name' => array('type' => 'string', 'max_length' => 30, 'not_null' => TRUE),
                           'surname' => array('type' => 'string', 'max_length' => 30, 'not_null' => TRUE),
                           'pseudo' => array('type' => 'string', 'max_length' => 15, 'unique_key' => TRUE),
                           'mail' => array('type' => 'string', 'unique_key' => TRUE),
                           'password' => array('type' => 'string', 'not_null' => TRUE),
                           'website' => array('type' => 'string'),
                           'activated' => array('type' => 'bool', 'default' => FALSE),
                           'banished' => array('type' => 'bool', 'default' => FALSE)));

    // A prepared query
    $query = $db->prepare('users.insert(:name, :sname, :pseudo, :mail, :pass).in(name, surname, pseudo, mail, password)');
    $query->bindValue(':name', 'Nana', \JSONDB\JSONDB::PARAM_STRING);
    $query->bindValue(':sname', 'Axel', \JSONDB\JSONDB::PARAM_STRING);
    $query->bindValue(':pseudo', 'na2axl', \JSONDB\JSONDB::PARAM_STRING);
    $query->bindValue(':mail', 'ax.lnana@outlook.com', \JSONDB\JSONDB::PARAM_STRING);
    $query->bindValue(':pass', $password, \JSONDB\JSONDB::PARAM_STRING);
    $query->execute();

    // After some insertions...

    // Select all users
    $results = $db->query('users.select(id, name, surname, pseudo)');
    // Fetch as object
    while ($result = $results->fetch(\JSONDB\JSONDB::FETCH_OBJECT)) {
        echo "The user with id: {$result->id} has the name: {$result->name} {$result->surname} and the pseudo: {$result->pseudo}.\n";
    }

    // All done! Disconnect from the server...
    $db->disconnect();
}
catch (\JSONDB\Exception $e) {
    echo $e->getMessage();
}