Geekly Articles each Day
📜 ⬆️ ⬇️

PHP / JSON database

When developing web applications, there is often a need to store certain settings or temporary data. Usually, it uses either files or databases. If this is a database, then storing a single-row table in the database, as is often the case, is not a very good option. For this, config files of certain formats (* .php, * .ini, * .xml, * .json) are often used.

Json database


In this post I want to talk about using json files as a database. Using the json format is convenient because the information in this format is Javascript arrays and objects that can be easily accessed from the client side of the web application.
Any database includes a set of functions for writing, reading, updating and deleting data from tables. In this case, it will be a class with a set of methods for managing the database.
$jdb = new Jsondb($path);



Database management


Below is a set of methods and examples of their use.

Create
Create a table.
 $jdb->create($table, $keys);


Example:
 $keys = Array( 'id'=>Array('auto_increment'), 'title'=>Array('default'=>'habrahabr'), 'posts', 'userId' ); $jdb->create('habr', $keys);

Select
Fetching data from a table.
 $jdb->select($select, $table, $rules);


Example:
 $rules = Array( 'where'=>Array( 'id'=>Array(1,2,3,4,6,7,10), 'name'=>'habr' ), 'order'=>Array('id','desc'), 'limit'=>5 ); $jdb->select('*','habr',$rules); $rules = Array( 'order'=>Array('rand()'), 'limit'=>Array(10,4) ); $select = Array('id','title','userId'); #  ,  $select ='id,title, userId'; $jdb->select($select, 'habr', $rules);

Insert
Insert data into the table.
 $fdb->Insert($table, $data);


Example:
 $data = Array('title'=>'new title', 'userId'=>6431); $jdb->insert('habr', $data);

Update
Update data in the table.
 $jdb->update($table, $data, $where);


Example:
 $data = Array('title'=>'updated title'); $where = Array('title'=>'new title', 'userId'=>6431); $jdb->update('habr', $data, $where);

Delete
Deleting records from the table.
 $jdb->delete($table, $where);


Example:
 $where = Array('userId'=>6431); $jdb->delete('habr', $where);

Drop
Deleting a table.
 $jdb->drop($table);


Example:
 $jdb->drop('habr');

Alter
Adding and removing keys from a table.
 $jdb->alter($table, $todo, $keys);


Example:
 $jdb->alter('habr', 'drop', 'title','userId'); $keys = Array( 'postTitle'=>Array( 'default'=>'habrapost' ) ); $jdb->alter('habr', 'add', $keys); $keys = Array('acc','userId'); #  ,  $keys = 'acc, userId'; $jdb->alter('habr', 'add', $keys);

Truncate
Completely clear the table.
 $jdb->truncate($table);


Exists
Checks the existence of a table.
 $jdb->exists($table);


Identifying errors in queries

All the listed methods return the result of the query, if it was successful, and false if not. The " status " method allows you to find out the status of the previous request.
 $jdb->status($flag);


Example:
 $jdb->create('users',Array('id'=>Array('auto_increment'),'name')); $jdb->create('users',Array('id','name')); echo $jdb->status(); echo $jdb->status(true); /* * 101 * Table already exists; */ $jdb->select('phone', 'users'); echo $jdb->status(); echo $jdb->status(true); /* * 202 * Try ro select an unexisting keys from table "users"; */

List of status codes:

')

Auxiliary methods


Last_insert_id
It often happens that you need to find out the id of the added record in the database. To do this, use the last_insert_id method.
 $jdb->last_insert_id();

Example:
 $jdb->insert('users', Array('name'=>'username')); echo $jdb->last_insert_id();

Exist
Checks the existence of a table. Returns true or false;
Example:
 $jdb->exist('user');

Also, for convenience, it is possible to use sql syntax to execute queries. To do this, use another class.
 Jsonsql::request('select * from `users`', $path);



Since php has the ability to create functions and classes with the same name, you can apply the following construction:
 function Jsonsql($string, $path = false){ return Jsonsql::request($string, $path); }

Example:
 Jsonsql('create table `new` (id auto_increment, title default "untitled", text)'); Jsonsql('select `name`,`title` from `habr` where `id` in(1,34,5,9,4,100) order by rand()', '/jdb/test/');

Additional requests

Check the status of the request:
 Jsonsql('status'); #     Jsonsql('status code'); #

Find out the id of the added record in the database:
 Jsonsql('last_insert_id');

Find out whether there is a table or not:
 Jsonsql('table exists `users`');


Comparing Jsondb with MySql


Of course, there is nothing to compare here, databases are created to store data and quickly access it. But still interesting.
To compare the speed and the amount of memory used, a series of tests was performed for the insert, update and select methods.

Insert
Ten rows were written ten times in the database. Below is a graph of the script execution time versus the number of operations performed.
image
Used memory:

It can be seen that the performance of Jsondb is very dependent on the number of records in the table.

Update
100 database entries have been updated.


Select
All records from the database were selected.


And a sample of data from the database with the conditions where, order and limit.

From all this it follows that the speed of such a data storage system, especially when using the update and insert methods, very much depends on the number of records in one table. The selection of data from the tables is quite fast. Using this method of storing information is well suited for storing various application settings and temporary data. As mentioned above, this is also a big plus due to the fact that any information stored in this form can be accessed from the client side of the application. If more, then for example using the jQuery method $ .getJSON .

Here is the link where you can download the sources and examples.

Source: https://habr.com/ru/post/119719/

More articles:


All Articles