Edit report at http://pear.php.net/bugs/bug.php?id=18648&edit=1
ID: 18648
Comment by: [email protected]
Reported By: thomas dot ernest at gmail dot com
Summary: Add support for CONSTRAINT and INDEX in declaration
CREATE TABLE
Status: Feedback
Type: Feature/Change Request
Package: SQL_Parser
Package Version: 0.6.0
PHP Version: 5.3.6
Roadmap Versions:
New Comment:
Hello Alan,
Thanks for sharing your ideas.
I applied my changes against SVN trunk and I generated the new patch
with svn diff.
It must require additional reserved words available in the patch
associated to request 18647. https://pear.php.net/bugs/bug.php?id=18647
It provides support for INDEX declaration in CREATE TABLE command as
well as table options like ENGINE, CHARACTER SET, etc...
This new patch doesn't support CONSTRAINT declaration in CREATE TABLE
command, since I don't need support for it anymore.
Ideas you shared are interesting, but I don't really have time to
develop them now. Sorry. If I do that, I will open a new report.
Cheers.
Previous Comments:
------------------------------------------------------------------------
[2011-07-04 12:44:07] ternest
Added #patch
bug:18648;patch:add-support-for-index-table-options;revision:1309779847;.
------------------------------------------------------------------------
[2011-07-03 16:05:07] alan_k
-Status: Open
+Status: Feedback
------------------------------------------------------------------------
[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
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.