PQL

[Gigaman2003]

Hey guys, here is something ive been working on (and very excited about). Its been around for some time now, I just haven't had the time to finish writing the manual for it but finally completed it today. I thought it would come in handy to the majority of us web developers who have many side-projects and next year's site to worry about.

PQL - Portable Query Language

What PQL is is a speedy, feature rich database management system that runs on flat-files. Its PERFECT for situations such as a FIRST team site which is why im posting it here. I have a full description up on the PQL site so im not going to repeat everything. Some techniques developed for PQL are pretty advanced... Don't expect to understand the query system without reading the comments and a lot of playing around .

PQL in 30 seconds:
1. Portable, since it runs on flat files
2. Relational, like mysql databases
3. Delimiter-less, like Chuck Norris (no more injections corrupting your databases!)
4. Automatic data encryption, so you data is safe
5. A very advanced and innovative query system that makes even the most complicated queries a piece of cake to type out and run.
6. Open source, so you can adapt it to your needs.
7. Easy to learn, with a very detailed manual for reference.
8. Easy to script, with powerful functions that let you do more in less code.
9. Easy to implement, since its structure is similar to mysql.
10. Dean Kamen Approved. I wish

How to use PQL:
1. include PQL.class.php

include('PQL.class.php');

2. create new database(es) as objects and boot() them with the target database.

$database = &new PQL;
$database->boot('anewdatabase');

3. start inserting,editing,deleting,selecting and a whole lot more!

$database -> insert('profiles','John Doe',array('firstname'=>'John','lastname'=>'Doe','middleinitial'=>'K'));

$database -> edit('profiles',$rid,array('middleinitial'=>'M'));

$results = select('profiles','firstname,lastname','(({middleinitial}=="M")&&({firstname}**"John"))');

PQL makes it very easy and fast to program PHP scripts that need databases to work, its been a godsend to me since i have so many small-medium sized projects and need to migrate/duplicate them to different servers quickly.

Not on the site is PQL.Manage, a set of tools in a standalone package similar to PHPmyAdmin's that allow you to do anything to a PQL database. The only things left to add are the defragmentation tool and the query tool. Here are some screenshots of its awesomeness. Note the analyze tool, which really demonstrates how PQL's internal file structure is very much like your PC's hard drive, except the files can get as big as they want.

A couple of screenshots from the current progress of PQL.Manage. (yes i know i spelt occupied wrong...)
Analyzing a table's fragmentation:

The alter ids interface:


The view tab generates an actual xhtml table with the data in the database and organized by id, index and key, but that database contains private/sensitive statistics from the PQL manual so i'm not going to show that to you

Anyway, if you are interested you can grab the latest copy of PQL from the 'download pql' page in the manual (do a search, and check out the processing time for an idea of pql's speed). I update it very frequently as I discover bugs, optimize functions or decide to expand on the function set. If you want to participate in the development of PQL or need help with your own PQL-powered site you can check out our development forums at nullpo.

Raw numbers for the mathematically inclined:
Fields read per second : 500,000 +/-1000
Records queried per second : 10,000 +/-1000, depending on how many columns were requested and query complexity. This is without query caching. Compare this to mySQL that with a huge query cache, averages 20,000 per second on the same system. Not half bad eh?

Note the queries pretty much take the same time regardless of query complexity. This is because of the logic map system that PQL uses, and how it buffers only what it needs before it runs the query, saving a lot of time.

A lot of serious innovation and hard work went into PQL, I hope you like it!

[GRaduns340]

That looks nice. I particularly like the PQL.Manage feature. Even with experience in databasing it's always nice to be able to take the easy road with a GUI. Very well done. I'll consider taking a look into it for some of my hobbying sometime.

[Gigaman2003]

thanks for the quick reply

yes, the whole reason for PQL.Manage is to make things easier for people, as well as a nice way to manage a database without burying yourself in code. PQL stores data in a format thats pretty much never going to be easily human readable. You have to first seek around the reference file that contains the location of the data, then after you get that, seek to where the data is in the data file... then you have to decrypt the data... its all really unappitizing stuff.

anyway, glad to hear you're interested, its hard to get people using something unfamiliar pql is pretty robust, the pql manual runs on it... heck even a CMS, forum and blogging engine are in the works and being powered by it.

on my list of to-dos before 1.0 are

1. stop trying to debug the query JOIN functionality and add it to PQL (im still testing it... not too sure the different types behave as they should yet)
2. query caching, pretty important this one.
3. logical map caching, not too important but could save precious microseconds
4. implement the almost completed operator preceedence preparser (right now you can't use different logical operators in the same compound clause, this will give piority to &&, then ||, then ^^ by enclosing clauses in subclauses.)
5. conform to the ACID standards... this will be pretty tough for a flat-file DBMS but doable i hope.

and get more people using PQL!

[themagic8ball]

I had thoughts of doing something similar a few years ago. Very cool to see it in action. I use MySQL for all of my websites but I'd love to give this a go if I don't get that option. Keep up the good work! (You may want to put this on Sourceforge)

[Gigaman2003]

Thanks for the comments. I thought about putting it on Sourceforge but decided to wait until i had a stable 1.0 before i start really promoting it.

Hope you find it useful!