search

greenlion / PHP-SQL-Parser

A pure PHP SQL (non validating) parser w/ focus on MySQL dialect of SQL
BSD 3-Clause "New" or "Revised" License
603 stars 156 forks source link

Support for parsing the CREATE TABLE SQL statement #59

Open witchi opened 9 years ago

witchi commented 9 years ago

From preston....@gmail.com on March 02, 2012 07:45:12

I would like to see the parser support the CREATE TABLE statement.

Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=33

witchi commented 9 years ago

From pho...@gmx.de on March 04, 2012 13:05:01

I would like to have it too :-)

witchi commented 9 years ago

From greenlion@gmail.com on March 12, 2012 15:13:07

Status: Accepted
Labels: -Type-Defect Type-Enhancement

witchi commented 9 years ago

From pho...@gmx.de on March 23, 2012 02:50:01

You can follow the progress on this issue on http://php-sql-parser.googlecode.com/svn/branches/create-table . Let me some days to think about a solution.

Status: Started
Owner: pho...@gmx.de

witchi commented 9 years ago

From pho...@gmx.de on March 23, 2012 03:15:24

Can you provide an example of your CREATE statements? If I implement all features of the MySQL statement, I will have to do till Xmas. Which feature subset would help you?

witchi commented 9 years ago

From kilobyte...@gmail.com on February 16, 2013 04:29:56

I tried it, and find useful - but I cant see other things, like "ENGINE", "CHARSET", "ROW_FORMAT" in case of showing create table, am I doing something wrong?

witchi commented 9 years ago

From cmptrwiz...@gmail.com on September 19, 2013 06:14:13

Not sure if i am not using the branch correctly but i still get the same result as with the main branch that does not support CREATE.

Lets say i have this create statement:

CREATE TABLE "cachetable01" ( "sp_id" varchar(240) DEFAULT NULL, "ro" varchar(240) DEFAULT NULL, "balance" varchar(240) DEFAULT NULL, "last_cache_timestamp" varchar(25) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

It would be awesome if i can just get the field names and the field attributes separated.

witchi commented 9 years ago

From pho...@gmx.de on October 25, 2013 00:37:38

I had started the branch during a travel by rail, but the CREATE TABLE support isn't finished yet. There were a lot of open problems.

witchi commented 9 years ago

From pho...@gmx.de on November 08, 2013 18:11:59

The parser supports now CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name; See r496 . The LIKE must not set inset parenthesis.

Actually I can close the issue, we have CREATE TABLE support now .... LOL.

witchi commented 9 years ago

From pho...@gmx.de on November 08, 2013 18:19:24

It's r502 , I forgot to check-in a file.

witchi commented 9 years ago

From pho...@gmx.de on November 11, 2013 01:04:22

r515 knows all the table options like ENGINE or CHARACTER SET.

witchi commented 9 years ago

From pho...@gmx.de on November 15, 2013 00:43:09

r525 generates some column-def output for the example above. But there is still a lot to do.

witchi commented 9 years ago

From pho...@gmx.de on November 27, 2013 00:13:38

Yesterday I had time to implement some things. The parser knows now datatypes and columns. There are some errors but the test cases for issue #33 should output some helpful thing.

witchi commented 9 years ago

From zzgli...@gmail.com on December 06, 2013 03:39:11

I want to parse sql statements from sqlite_master. They have de form: CREATE TABLE turma(id text NOT NULL , nome text NOT NULL , nota1 int NOT NULL , nota2 int NOT NULL )

The current stable release does that? I am getting the following error: [06-Dec-2013 11:31:31] PHP Fatal error: Uncaught exception 'UnableToCalculatePositionException' with message 'cannot calculate position of id text NOT NULL within ' in /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php:188 Stack trace:

0 /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php(204): PositionCalculator->lookForBaseExpression('CREATE TABLE tu...', 101, Array, 0, Array)

1 /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php(204): PositionCalculator->lookForBaseExpression('CREATE TABLE tu...', 101, Array, 'sub_tree', Array)

2 /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php(204): PositionCalculator->lookForBaseExpression('CREATE TABLE tu...', 101, Array, 'create-def', Array)

3 /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php(204): PositionCalculator->lookForBaseExpression('CREATE TABLE tu...', 101, Array, 'TABLE', Array)

4 /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php(71): PositionCalculator->lookFor in /Applications/MAMP/htdocs/PHP-SQL-Parser/classes/position-calculator.php on line 188

TIA jcr

witchi commented 9 years ago

From pho...@gmx.de on December 06, 2013 03:53:46

Hm, the issue has not been fixed. Try to disable the position calculator with $parser->parse($sql, false). I have not tested the CREATE statement with the position calculator, so there are possible problems.

witchi commented 9 years ago

From pho...@gmx.de on December 06, 2013 04:12:53

@zzglider

I have to enhance the position calculator with the new ExpressionType constants. In your example the type "create-def" is not known within the calculator and results in a wrong output. For the CREATE TABLE statement I have defined a lot of new types, so after the parser is complete for CREATE TABLE, I will come back to the calculator.

Because the parser output still is not finished, it makes no sense to enhance the calculator now. Please wait some days or try to implement your own solution :-)

witchi commented 9 years ago

From pho...@gmx.de on December 07, 2013 17:29:16

I have fixed some problems in r623 , you can test CREATE TABLE statements in your client code. These things are open:

  1. union keywords doesn't work
  2. partition handling doesn't work
  3. CREATE TABLE followed by SELECT doesn't work
  4. PositionCalculator doesn't work with CREATE TABLE
  5. Creator doesn't know CREATE TABLE statements

All other features should be okay. I would handle point 3 till 5 now, the other are a lot of work, they will be done on the next release.

witchi commented 9 years ago

From pho...@gmx.de on December 09, 2013 16:28:03

Parser should now understand SELECT statements which follow a CREATE TABLE. See r631 .

witchi commented 9 years ago

From pho...@gmx.de on December 11, 2013 04:38:52

Parser can calculate the positions for CREATE TABLE statements now. See r725 .

witchi commented 9 years ago

From pho...@gmx.de on January 08, 2014 06:28:31

The current tag 2014-01-08 contains the point 3 till 5 (see comment #16). I'll start with partition handling soon.

witchi commented 9 years ago

From pho...@gmx.de on January 10, 2014 06:56:54

Some PARTITION output is available in r988 .

witchi commented 9 years ago

From pho...@gmx.de on January 23, 2014 07:07:58

There are some problems within the partition-definition, don't use it at the moment. Partition-options should be fine.

witchi commented 9 years ago

From pho...@gmx.de on January 24, 2014 05:59:57

The parser output for partitions and subpartitions have been finished. The parser can also calculate the string positions. The Creator can not build the clauses at the moment.

From the list above, we still have open the first point, the UNION keyword.