PHP Database - Real! Introducing MooSQL!

I think many in their lives faced a situation where you don't have MySQL at hand (for various reasons, for example, the hoster does not allow), but you still have something like that, or even MySQL itself is desirable. Now you have hope :)! I and nblxa want to present a project called MooSQL , the purpose of which is to provide a MySQL-compatible DBMS in pure PHP in case the usual MySQL has ended in the house.

Welcome under the cut! So, let's continue :).

What is MooSQL?

MooSQL [mu-school] is the name of our DBMS, with a hint that it will support MySQL-compatible syntax. It will be - at the moment such support is missing. The DBMS itself is a logical continuation of YNDb, my data storage engine for PHP, described in this habratopic: habrahabr.ru/blogs/i_am_insane/70140 .

One of the goals is good (for a PHP database :)) scalability and high performance due to the use of field indexing.
')

What can?

MooSQL is not able to do much - at the moment only the API of YNDb itself is available, which is described on this wiki page: code.google.com/p/moosql/wiki/HOWTO .

Since then, YNDb has undergone some changes:

• Fully OOP and PHP5, learned to throw exceptions for any critical errors.
• Improved support for multi-thread access - it is possible to simultaneously make several selects from the same table
• Added two new types of columns that can be indexed - BYTE and DOUBLE. Text fields cannot be indexed so far.
• ! Row splitting - now it is possible to increase the length of text fields! This is achieved by introducing a new type of records, which consist of several pieces.

Show an example of work !!

First, I would like to provide a link that was given in the topic about YNDb - Database forum.dklab.ru (old), imported into a self-made forum . Since then, spam bots have settled on it and managed to write about 600 (!) MB of text, writing about 120,000 new messages (to the 130,000 that were). The forum is still working and shows no signs of death :).

I didn’t touch it, so I re-imported the same database into a new place: Forum running on the new version of MooSQL . This link uses a slightly improved (taking into account the MooSQL features that have appeared since), the forum version that is working on the latest MooSQL version at the time of writing.

Technical Details

The DBMS is written in PHP5, without reference to any frameworks, PHP extensions, etc. Physically, tables and databases are files and folders, respectively. One table consists of 4-6 files, depending on the use of indexes. When reading from a table, a shared lock is imposed on the file table_name .lock, which allows you to do parallel samples from the same table. For write operations, an exclusive lock is placed on the same file.

Indices are of three types - PRIMARY (AUTO_INCREMENT), UNIQUE and INDEX. The format of the PRIMARY index is very simple - an offset is written in the data file (32-bit) at value * 4. This imposes a limit on the size of the data file of 2 GB. For UNIQUE indexes, B-trees are used ( Article about B-trees on Wikipedia ), for INDEX - a combination of B-trees and lists for storing duplicate values.

Data is stored in binary format as a sequence of lines, without separators and any information about the structure of the table. We call this format "MooISAM". It is also planned to make the format “MooDB” (similar to InnoDB), which would allow for highly multi-threaded access to the data of the same table (MooISAM currently works more or less normally with a degree of access parallelism less than 10). In addition, if MooDB is, it will be designed with the ability to add support for transactions and foreign keys.

Conclusion

If you are interested in this project, I invite you to participate in it! The MooSQL kernel called YNDb is already approaching production-ready in terms of stability, and I would like to attract as many people as possible to test, first of all - writing automatic tests for my DBMS. At the moment I know about at least one error that can spoil your data when working correctly with the API. Well, and, of course, are looking for those who wish to take part either in the development of the database engine, or SQL parts. Contact, for example, in Skype, I will tell in more detail if interested.

Project website on Google Code: code.google.com/p/moosql
PhpMooAdmin: moosql.googlecode.com/files/PhpMooAdmin.0.0.1.php