[PEAR-BUG] Req #18648 [Opn->Fbk]: Add support for CONSTRAINT and INDEX in declaration CREATE TABLE

Edit report at http://pear.php.net/bugs/bug.php?id=18648&edit=1

 ID:               18648
 Updated by:       [email protected]
 Reported By:      thomas dot ernest at gmail dot com
 Summary:          Add support for CONSTRAINT and INDEX in declaration
                    CREATE TABLE
-Status:           Open
+Status:           Feedback
 Type:             Feature/Change Request
 Package:          SQL_Parser
 Package Version:  0.6.0
 PHP Version:      5.3.6
 Roadmap Versions: 
 New Comment:

-Status: Open
+Status: Feedback



Previous Comments:
------------------------------------------------------------------------

[2011-07-03 16:04:20] alan_k

Thanks for the patch,

I had a few issues applying it, 
a) can you work against SVN trunk. 
svn co http://svn.php.net/repository/pear/packages/SQL_Parser

b) please use either diff -u, or svn diff to generate output. 


If you feel upto the re-factoring below, It would be nice, otherwise
please just upload a working diff 
against trunk, and I will commit that.

At present, you have added a bit of 'mysql' sepecific code to the
generic parser, This in some respects 
is a problem with the design of SQL Parser, It should be quite usefull
to 
a) throw a warning if the current constructor is called... saying it may
not work to well....
b) add a factory() method, that includes Dialect/Msql or Dialect/Ansi ,
returns the correct object and 
make those an extension of the base Parser, rather than just array's of
information.

You can then move some of the things like engine arrays etc.. to that
new class..

------------------------------------------------------------------------

[2011-07-01 14:56:43] ternest

Added #patch
bug:18648;patch:support-for-index-constraints-table_opts;revision:1309528603;.

------------------------------------------------------------------------

[2011-07-01 14:55:47] ternest

Description:
------------
CREATE TABLE declaration is explained at
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

On the third rule definition there are :
---
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
---
...
---
  | [CONSTRAINT [symbol]] FOREIGN KEY
---
Constraints and indexes are not supported by SQL_Parser 0.6.0

You can see a rule 'table_options'. It isn't managed with SQL_Parser
version 0.6.0.

I know that this ticket report several bug/features at once, but I've a
patch for all of them, sorry.

Test script:
---------------
SQL query :
CREATE TABLE `test_table` (id BIGINT AUTO_INCREMENT, email VARCHAR(64)
NOT NULL, boobool TINYINT(1) DEFAULT 0, CONSTRAINT UNIQUE_EMAIL
UNIQUE(email), PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARACTER SET
utf8;

Expected result:
----------------
Array
(
    [0] => Array
        (
            [command] => create_table
            [table_names] => Array
                (
                    [0] => test_table
                )

            [column_defs] => Array
                (
                    [id] => Array
                        (
                            [type] => bigint
                            [0] => Array
                                (
                                    [type] => auto_increment
                                    [value] => 1
                                )

                            [constraints] => Array
                                (
                                    [0] => Array
                                        (
                                            [type] => primary_key
                                            [value] => 1
                                        )
                                )
                        )

                    [email] => Array
                        (
                            [type] => varchar
                            [length] => 64
                            [0] => Array
                                (
                                    [type] => not_null
                                    [value] => 1
                                )
                        )

                    [boobool] => Array
                        (
                            [type] => tinyint
                            [length] => 1
                            [0] => Array
                                (
                                    [type] => default_value
                                    [value] => 0
                                )
                        )
                )

            [constraint_defs] => Array
                (
                    [UNIQUE_EMAIL] => Array
                        (
                            [type] => unique
                            [column_names] => Array
                                (
                                    [0] => email
                                )
                        )
                )

            [table_options] => Array
                (
                    [0] => Array
                        (
                            [option_name] => engine
                            [value] => INNODB
                        )
                    [1] => Array
                        (
                            [option_name] => character set
                            [value] => utf8
                            [is_default] => 1
                        )
                )
        )
    [1] => ;
)

------------------------------------------------------------------------


-- 
Edit this bug report at http://pear.php.net/bugs/bug.php?id=18648&edit=1