txtSQL

Code: Select all

<?php

/**
* A lexer, used to parse a SQL query
*
* @package txtSQL_Tokenizer::txtSQL_Lexer
* @author Faraz Ali <FarazAli at gmail dot com>
* @version 4.0.0 Beta
* @access public
*/
class txtSQL_Lexer extends txtSQL_Tokenizer{}

?>

I have added some new SQL tags like:
INTEGER(max), PRIMARY, KEY, CHAR(max), VARCHAR(max), UNIQUE

Here is test script of my modded version of txtSQL_Lexer+txtSQL_Tokenizer:

Code: Select all

<?php

define('TXTSQL_TOKENIZER_PATH','.');
require_once('txtSQL.lexer.php');

// SQL Query
$query = <<<SQL
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name CHAR(32) UNIQUE,
mail VARCHAR(64) UNIQUE,
timejoin INT(10)
)
SQL;

// Init Lexer with Query
$q = new txtSQL_Lexer($query);
// Parse the query
$q->parse();

// Display result = txtSQL_Lexer::$_arg
echo '<pre>'.$query.'<hr />';
print_r( $q->_arg );

?>

Resulting output of script above

Code: Select all

Array
(
    [command] => create_table

    [table] => users

    [cols] => Array
        (
            [id] => Array
                (
                    [type] => int
                    [primary] => 1
                    [key] => 1
                    [auto_increment] => 1
                )

            [name] => Array
                (
                    [type] => char
                    [max] => 32
                    [unique] => 1
                )

            [mail] => Array
                (
                    [type] => varchar
                    [max] => 64
                    [unique] => 1
                )

            [timejoin] => Array
                (
                    [type] => int
                    [max] => 10
                )

        )

)

What can we use this for?

Yes, we can make our functions: create, insert, update delete and select
and use our own Database Classes to execute using parameters from the above array.

Data may be stored in XML, CSV, Excel, flatfile, text files or any other custom format.
And we can still use the SQL Syntax Language.

Cheers

The result, $_arg of SELECT has a little difference, from CREATE TABLE:
[command] => select
[tables] => Array
(
[users] =>
)
[where] => Array

[command] => create_table
[table] => users
[cols] => Array
(
[id] => Array
So SELECT has got an array of 'tables',
while CREATE TABLE has got a variable 'table'

Trying this:
SELECT id, name FROM users WHERE id<50 ORDER BY name DESC LIMIT 5
gives:

Code: Select all

Array
(
    [command] => select
    [select] => Array
        (
            [id] => Array
                (
                    [alias] =>
                    [variant] => 3
                )

            [name] => Array
                (
                    [alias] =>
                    [variant] => 3
                )

        )

    [tables] => Array
        (
            [users] =>
        )

    [where] => Array
        (
            [0] => Array
                (
                    [col] => Array
                        (
                            [col] => id<50
                            [variant] => 3
                        )

                )

        )

    [order] => Array
        (
            [name] => DESC
        )

    [limit] => Array
        (
            [offset] => 5
        )

)

I'm glad to see that someone actually decided to use the tokenizer and lexer classes that I've written.
The reason that the select() has an array of tables, versus one variable for the table in create_table is because in SELECT queries, we can reference more than one table; whereas in creating a table, you're only creating a single table, so I thought it would be useless to have an array containing one item. Although it would be a good idea in terms of consistency.

BTW- in your run of the lexer class, can you post the results of the following query (lexical content)?

SELECT id, name FROM users WHERE `id`<50 ORDER BY name DESC LIMIT 5

It seems as tho "id<50" isn't being recognized as three separate items, as it should be. The problem should be fixed by placing the column name inside of quotes (either ` or " )

> I'm glad to see that someone actually decided to use the tokenizer and lexer classes that I've written.

I am glad to see a good work of php coding!
It is a bit sad you haven't time to do more such. But that's life.

> The reason that the select() has an array of tables ... we can reference more than one
> .. one item ... would be a good idea in terms of consistency.

Consistency, yes, my first thought.
When writing a snippet to check for 'table', it can be good to use same way of storage.
Instead of having to write 2 functions/methods to get the table name(s).
But I knew there was a reason you use [tables] in place of [table]. And so your key names are descriptive.

I just want to know why not use this:
[tables] => Array( [0]=>'users', [1]=>'members', [2]=>'people' )
We have more functions to handle values, than to handle keys.
Are there cases we need attribute to a table?
[tables] => Array( [users]=>'yellow', [members]=>3, [people]=>null )

Code: Select all

SELECT id, name FROM users WHERE `id`<50 ORDER BY name DESC LIMIT 5

>It seems as tho "id<50" isn't being recognized ... placing the column name inside of quotes (either ` or " )

If you have a look at my code, you see my way of writing query is with minimum use of quotes.
Wherever is possible I would omit any putting inside quotes.
I know there can be safety issues doing this. So I mostly use a custom escaping function.

If following doesn't work, I may try to modify the way your classes look for WHERE arguments.
But I will first try following.

Code: Select all

WHERE id < 50 ORDER BY ....
WHERE id< 50 ORDER BY ....
WHERE id <50 ORDER BY ....

Generally it is good, such a lexer should be allowing for several way of putting it.
It should be forgiving, as to suit many ways to do the practical coding.
Unless there is valid reasons to avoid some ways of expression.

A hint would be:
Should accept, without Warnings, what MySQL accepts without Warnings.

My reason for saying this, is that MYSQL SQL Syntax is what millions of people are used to.
For good or bad! Because MySQL Syntax is many times not Standard SQL.
But MySQL IS dominant in the world of PHP 4 and PHP 5 and PHP 6.

You will not easily change the way millions of people write SQL.
It is perhaps more clever to try to conform as much as possible to what people are used to.

Compare:
Better to
Prepare your children with knowledge and tools to live in The Real World

than to
Try to protect them from what is in The Real World

or even to
Try to change The Real World to suit your children.
Originally quote by phpmaster 2008

Besides, as being from Sweden, I must say MySQL SQL Syntax is pretty well documented.
If a SQL Lexer could conform to MySQL syntax allowance as much as possible,
you will not need to write as much documentations of exceptions to major ways of SQL.

There will be less many Bug Reports and Frequently Asked Questions from users.

Instead You can refer to:
MySQL AB :: MySQL 5.0 Reference Manual


Regards, phpmaster

http://www.fashion-products.com [url=http://www.fashion-products.com ]is a sell air force one,
air jordan,bape,kicks,nike,Jerseys,Shoes,Clothing,Bag,Glasses,Caps&Hats,sneakers,sunglasses,
watches,Jewelry,Electronics,NBA,NFL,NHL,MLB[/url]
shoes
clothing
Handbags Wallets
Hat
Sunglasses
Belts
Watches
Jewelry