Merge lp:~nahiljain/to-drizzle/parse_create_table into lp:to-drizzle

[neh]

more test cases for parsing create table... made changes and stored values of length,binary etc

[Jay Pipes (jaypipes)]

Hi!

Looks good, Nahil, but there's a few issues to fix up!

1) There is some extra print happening in the parse create table test case, as shown in the output here:

jpipes@serialcoder:~/repos/movetodrizzle/parse_create_table/tests$ ./parser_createtable_test.py
............[['AUTO_INCREMENT', 5]]
.
----------------------------------------------------------------------
Ran 13 tests in 0.009s

OK

Remove the printing of the [[AUTO_INCREMENT', 5]]

2) Remove all commented-out code:

60 +# '''foreign_key_constraint : FOREIGN KEY LPAREN index_col_name_list RPAREN reference_definition'''

174 +
175 +#def p_data_types(p):
176 +# '''data_type : mathematical'''
177 +# p[0]= p[1]
178 +
179 +
180 +

303 +#print parse_string("INSERT INTO neh1 VALUES (123,456,'abc');")
304 +#print parse_string("INSERT INTO neh1 SET a1=10,b2=25;")

491 +# constraint_name= False) (index_name=None) (parent_fields= ['field1']) (reference= Reference(name= reftable) (col_name_list= ['ref_field1']) (match_option= False) (on_delete= False) (on_update= False))])]

3) Please, please be consistent in your code and spacing. :)

134 + if(len(p)>3):
135 + p[0]=p[3]

Please put a single space between if and the (

Please put a single space before and after the >

Please put a single space after the =

There are more places where the style is inconsistent. Please do fix them up :)

4) Tabs and spacing is off in your code. For instance:

188 - '''temporary : TEMPORARY
189 + '''temporary : TEMPORARY
190 | empty'''
191 - # Returns True if the TEMPORARY phrase appeared, False otherwise
192 - p[0]= len(p) > 1
193 + # Returns True if the TEMPORARY phrase appeared, False otherwise
194 + p[0]= len(p) > 1

For some reason, you are re-setting Tabs to a single space. Please make sure all indentation is exactly 2 spaces.

5) Returned data_type should not be a list.

357 - self.assertEqual(str(ct.columns[0].data_type), "INT")
358 + self.assertEqual(str(ct.columns[0].data_type[0]), "INT")

Above, the columns[0].data_type should be "INT", not ["INT"] ...

Cheers!

jay

review: Needs Fixing

=== modified file 'movetodrizzle/helpers/lexer.py'
--- movetodrizzle/helpers/lexer.py	2010-07-07 22:04:33 +0000
+++ movetodrizzle/helpers/lexer.py	2010-07-22 18:42:46 +0000
@@ -59,9 +59,7 @@
   'constraint' : 'CONSTRAINT',
   'constraint' : 'CONSTRAINT',
   'references' : 'REFERENCES',
-  'match' : 'MATCH',
   'full' : 'FULL',
-  'match' : 'MATCH',
   'partial' : 'PARTIAL',
   'match' : 'MATCH',
   'simple' : 'SIMPLE',
@@ -71,7 +69,8 @@
   'cascade' : 'CASCADE',
   'no' : 'NO',
   'action' : 'ACTION',
-  'delete' : 'DELETE'
+  'delete' : 'DELETE',
+  'comment' : 'COMMENT'
 
 
 }
@@ -87,7 +86,7 @@
   'SEMICOLON',
   'NUMBER',
   'FLOAT_NUMBER',
-  'COMMENT',
+  'COMMENT_CODE',
   'IDENTIFIER',
   'STRING',
   'DOT',
@@ -102,7 +101,7 @@
 t_RPAREN = r'\)'
 t_COMMA = r'\,'
 t_SEMICOLON = r'\;'
-t_DOT= r'.'
+t_DOT= r'\.'
 t_EQUAL= r'\='
 
 def t_NUMBER(t):
@@ -130,7 +129,7 @@
   return t
 
 #Define a rule so that we process comments
-def t_COMMENT(t):
+def t_COMMENT_CODE(t):
     r'\/\*.*\*\/'
     pass
   

=== modified file 'movetodrizzle/helpers/parser.py'
--- movetodrizzle/helpers/parser.py	2010-07-15 16:41:56 +0000
+++ movetodrizzle/helpers/parser.py	2010-07-22 18:42:46 +0000
@@ -4,7 +4,6 @@
 from lexer import *
 import re
 from statement_nodes import *
-
 # Begin grammar rules
 def p_statement_list(p):
   '''statement_list : statement_list statement'''
@@ -23,7 +22,7 @@
   p[0]=p[1]
 
 def p_create_table_simple(p):
-  '''create_table_simple : CREATE temporary TABLE if_not_exists identifier_qualified_opt LPAREN col_list RPAREN table_options'''
+  '''create_table_simple : CREATE temporary TABLE if_not_exists identifier_qualified_non_qualified_opt LPAREN col_list RPAREN table_options'''
   a= CreateTableStatement(p[5], p[7], p[4], p[2])
   a.update_table_options(p[9]);
   p[0]= a
@@ -31,17 +30,23 @@
 def p_table_options(p):
   '''table_options : table_option table_options
                     | empty'''
-  if(len(p)>2):
+  if (len(p) > 2):
     p[0]= [p[1]] + p[2]
   else:
     p[0]= []
+
+def p_identifier_qualified_non_qualified_opt(p):
+  '''identifier_qualified_non_qualified_opt : identifier_qualified_opt
+                                            | identifier_non_qualified_opt'''
+  p[0]= p[1]
+
  
 def p_qualified_identifier(p):
   '''identifier_qualified_opt : IDENTIFIER DOT IDENTIFIER'''
   p[0]= Identifier(p[1], p[3])
 
 def p_non_qualified_identifier(p):
-  '''identifier_qualified_opt : IDENTIFIER 
+  '''identifier_non_qualified_opt : IDENTIFIER 
        | STRING'''
   p[0]= Identifier(p[1])
 
@@ -57,11 +62,11 @@
 def p_col(p):
   '''col : col_conditional
         | col_normal'''
-  p[0]=p[1]
+  p[0]= p[1]
 
 def p_col_conditional(p):
   '''col_conditional : foreign_key_constraint'''
-  p[0]=p[1]
+  p[0]= p[1]
 
 def p_foreign_key_constraint(p):
   '''foreign_key_constraint : constraint_opt FOREIGN KEY index_name_opt LPAREN index_col_name_list RPAREN reference_definition'''
@@ -71,23 +76,29 @@
   '''constraint_opt : CONSTRAINT
            | CONSTRAINT identifier
            | empty'''
-  if(len(p)>2):
+  if (len(p) > 2):
     p[0]= p[2]
   else:
-    p[0]= len(p)>2
+    p[0]= len(p) > 2
 def p_index_name_opt(p):
   '''index_name_opt : identifier
           | empty '''
-  p[0]=p[1]
+  if(len(p) > 1):
+    p[0]= p[1]
+  else:
+    p[0]=  len(p)>1
+
 
 def p_index_col_name_list(p):
   '''index_col_name_list : index_col_name COMMA index_col_name_list
             | index_col_name'''
-  if(len(p)>2):
+  if (len(p) > 2):
     p[0]= [p[1]]+ p[3]
   else:
     p[0]= [p[1]]
 
+
+
 def p_index_col_name(p):
   '''index_col_name : identifier'''
   p[0]= p[1]
@@ -101,26 +112,26 @@
          | MATCH PARTIAL
          | MATCH SIMPLE
          | empty'''
-  if(len(p)>2):
+  if (len(p) == 3):
     p[0]= p[2]
   else:
-    p[0]= len(p)>2
+    p[0]= len(p) > 2
 
 def p_on_delete_option(p):
   '''on_delete_option : ON DELETE reference_options
            | empty'''
-  if(len(p)>3):
-    p[0]= p[2]
+  if (len(p) > 3):
+    p[0]= p[3]
   else:
-    p[0]= len(p)>3
+    p[0]= len(p) > 3
 
 def p_on_update_option(p):
   '''on_update_option : ON UPDATE reference_options
            | empty'''
-  if(len(p)>3):
-    p[0]= p[2]
+  if (len(p) > 3):
+    p[0]= p[3]
   else:
-    p[0]= len(p)>3
+    p[0]= len(p) > 3
 
 def p_reference_options(p):
   '''reference_options : RESTRICT 
@@ -128,12 +139,12 @@
            | SET NULL 
            | NO ACTION
            | empty'''
-  if(len(p)==2):
-    p[0]=p[1]
-  elif(len(p)==3):
-    p[0]=str(p[1])+str(p[2])
+  if (len(p) == 2):
+    p[0]= p[1]
+  elif (len(p) == 3):
+    p[0]= str(p[1])+str(p[2])
   else:
-    p[0]=None
+    p[0]= None
 
 def p_col_normal(p):
   '''col_normal : col_definition'''
@@ -141,8 +152,10 @@
 
 def p_col_definition(p):
   '''col_definition : identifier data_type nullable_constraint default_p auto_increment_p unique_p primary_key comment_p'''
+
   p[0]= ColumnDefinition(p[1], p[2], p[3], p[4], p[5], p[6], p[7], p[8])
 
+
 def p_nullable_constraint(p):
   '''nullable_constraint : NOT NULL
                          | NULL
@@ -152,7 +165,7 @@
 def p_default_p(p):
   '''default_p : DEFAULT identifier
        | empty'''
-  if(len(p) > 2):
+  if (len(p) > 2):
     p[0]= p[2]
   else:
     p[0]= None
@@ -174,15 +187,15 @@
   p[0]= len(p) > 2
 
 def p_comment_p(p):
- '''comment_p : COMMENT STRING
+  '''comment_p : COMMENT STRING
        | empty'''
- if(len(p)>2):
-  p[0]= p[2]
- else:
-  p[0]= len(p)>2
+  if (len(p) > 2):
+    p[0]= p[2]
+  else:
+    p[0]= len(p) > 2
 
-def p_data_types(p):
- '''data_type : BIT
+def p_data_types_1(p):
+  '''data_type : BIT
           | mathematical length_opt unsigned_opt zerofill_opt
           | DATE
           | TIME
@@ -195,15 +208,34 @@
           | TINYBLOB
           | BLOB
           | MEDIUMBLOB
-          | LONGBLOB
-          | texts binary_opt character_set_opt collate_opt
+          | LONGBLOB'''
+  p[0]= DataTypeDesc(p[1])
+  if (len(p) == 1):
+    p[0]= [p[1]]
+  if (len(p) == 5):
+    if(p[2] == False):
+      length= None;
+      decimal= None;
+    elif(len(p[2]) == 2):
+      length= p[2][0];
+      decimal= p[2][1];
+    else:
+      length= p[2][0];
+      decimal= None;
+    p[0]=DataTypeDesc(p[1],length,decimal,p[3],p[4])
+  if (len(p) == 4):
+    p[0]= DataTypeDesc(p[1],None,None,None,None,p[2],p[3])
+
+def p_data_types_2(p):
+  '''data_type : texts binary_opt character_set_opt collate_opt
           | set_enum character_set_opt collate_opt'''
- p[0]= p[1]
+  p[0]= DataTypeDesc(p[1]);
 
 def p_set_enum(p):
   '''set_enum : SET LPAREN value_list RPAREN
               | ENUM LPAREN value_list RPAREN'''
-  p[0]=p[1]
+  p[0]= p[1]
+   
 
 def p_value_list(p):
   '''value_list : STRING COMMA value_list
@@ -229,29 +261,47 @@
 def p_character_set_opt(p):
   '''character_set_opt : CHARACTER SET identifier
                       | empty'''
-  pass
+  if (len(p) > 3):
+    p[0]= p[3]
+  else:
+    p[0]= len(p) > 3
 
 def p_collate_opt(p):
   '''collate_opt : COLLATE identifier
                   | empty'''
-  pass
+  if (len(p) > 2):
+    p[0]= p[2]
+  else:
+    p[0]= len(p) > 2
 
 def p_length_opt(p):
   '''length_opt : LPAREN NUMBER RPAREN 
                  | LPAREN NUMBER COMMA NUMBER RPAREN
                  | empty'''
-  pass
+  if (len(p) >= 6):
+    p[0]= [p[2],p[4]]
+    
+  elif (len(p) == 4):
+    p[0]= [p[2]]
+  else:
+    p[0]= len(p) > 6;
+	
+    
 
 def p_unsigned_opt(p):
   '''unsigned_opt : UNSIGNED
                   | empty'''
-  pass
+  p[0]= len(p) > 1
+
 
 def p_zerofill_opt(p):
   '''zerofill_opt : ZEROFILL
                   | empty'''
-  pass
-
+  p[0]= len(p) > 1
+
+
+  
+  
 def p_mathematical(p):
   '''mathematical : TINYINT 
           | SMALLINT
@@ -268,7 +318,7 @@
 def p_optional_temporary(p):
   '''temporary : TEMPORARY
           | empty'''
-  # Returns True if the TEMPORARY phrase appeared, False otherwise
+ # Returns True if the TEMPORARY phrase appeared, False otherwise
   p[0]= len(p) > 1
 
 def p_create_schema(p):
@@ -295,41 +345,45 @@
   p[0]= CreateSchemaStatement(p[4], p[6], p[8], p[3])
 
 def p_optional_if_not_exists(p):
- '''if_not_exists : IF NOT EXISTS
+  '''if_not_exists : IF NOT EXISTS
           | empty'''
-  # Returns True if the IF NOT EXISTS phrase appeared, False otherwise
+ # Returns True if the IF NOT EXISTS phrase appeared, False otherwise
   p[0]= len(p) > 2
 
 def p_schema_or_database(p):
- '''schema_or_database : SCHEMA
+  '''schema_or_database : SCHEMA
             | DATABASE'''
- p[0]= p[1]
+  p[0]= p[1]
 
 def p_identifier(p):
- '''identifier : IDENTIFIER
+  '''identifier : IDENTIFIER
         | STRING'''
- p[0]= p[1]
+  p[0]= p[1]
 
 def p_charset(p):
- '''charset : CHARACTER SET
+  '''charset : CHARACTER SET
         | DEFAULT CHARACTER SET'''
   pass
 
-def p_table_option(p):
+
+def p_table_option_1(p):
   """table_option : ENGINE opt_equal identifier
   | AUTO_INCREMENT opt_equal NUMBER
   | COMMENT opt_equal STRING"""
-  temp=[]
+  temp= []
   temp.append(p[1])
   temp.append(p[3])
-  p[0]=temp;
+  p[0]= temp;
+
   
-def p_table_option(p):
+def p_table_option_2(p):
   '''table_option : default_opt CHARACTER SET opt_equal identifier'''
-  temp=[]
-  temp.append("CHARACTER SET",p[5]);
+  temp= []
+  temp.append("CHARACTER SET");
+  temp.append(p[5]);
   p[0]= temp;
 
+
 def p_default_opt(p):
   '''default_opt : DEFAULT
                   | empty'''
@@ -338,17 +392,21 @@
 def p_opt_equal(p):
   ''' opt_equal : EQUAL
                 | empty'''
-  if(len(p)>1):
+  if (len(p) > 1):
     p[0]= p[1]
   else:
-    p[0]= len(p)>1;
+    p[0]= len(p) > 1;
+
+
+
 
 def p_empty(p):
   'empty : '
-  pass # an empty rule that represents an optional piece
+  pass # an empty rule that represents an optional
+    # piece of a rule
 
 def p_error(p):
-  # Called when a syntax or parse error occurs
+ # Called when a syntax or parse error occurs
   print "A parse error occurred at token %s" % p
 
 # Create the parser
@@ -359,3 +417,4 @@
     return parser.parse(subject)
   except Exception as e:
     print e
+

=== modified file 'movetodrizzle/helpers/statement_nodes.py'
--- movetodrizzle/helpers/statement_nodes.py	2010-07-15 16:41:56 +0000
+++ movetodrizzle/helpers/statement_nodes.py	2010-07-22 18:42:46 +0000
@@ -19,10 +19,32 @@
   def __repr__(self):
     return "CreateSchemaStatement(schema= %s) (character_set= %s) (collate= %s) (if_not_exists= %s)" % (self.schema_name, self.character_set, self.collate_name, self.if_not_exists)
 
+class DataTypeDesc():
+  def __init__(self, data_type, length= None, decimals= None, unsigned= None, zerofill= None, charset_name= None, collate_name= None, binary= None):
+    self.data_type= data_type;
+    self.length= length;
+    self.unsigned= unsigned;
+    self.zerofill= zerofill;
+    self.decimals= decimals;
+    self.collation_name= collate_name;
+    self.charset_name= charset_name;
+    self.binary= binary;
+
+  def __repr__(self):
+    return "DataTypeDesc(data_type= %s, length= %s, decimals= %s, zerofill= %s, unsigned= %s, collation_name= %s, charset_name= %s, binary= %s)" %(
+        self.data_type,
+        self.length,
+        self.decimals,
+        self.zerofill,
+        self.unsigned,
+        self.collation_name,
+        self.charset_name,
+        self.binary)
+
 class ColumnDefinition():
-  def __init__(self, name, data_type, nullable= True, default= None, auto_increment= False, unique= False, primary_key= False, comment= None):
+  def __init__(self, name, data_type_desc, nullable= True, default= None, auto_increment= False, unique= False, primary_key= False, comment= None):
     self.name= name;
-    self.data_type= data_type;
+    self.data_type_desc= data_type_desc;
     self.nullable= nullable;
     self.default= default;
     self.auto_increment= auto_increment;
@@ -30,9 +52,9 @@
     self.primary_key= primary_key;
     self.comment= comment;
   def __repr__(self):
-    return "Column(col_name= %s, data_type= %s, nullable= %s, default= %s, auto_increment= %s, unique= %s, primary_key= %s, comment= %s)" % (
+    return "Column(col_name= %s, data_type_desc= %s, nullable= %s, default= %s, auto_increment= %s, unique= %s, primary_key= %s, comment= %s)" % (
       self.name,
-      self.data_type,
+      self.data_type_desc,
       self.nullable,
       str(self.default),
       self.auto_increment,
@@ -50,13 +72,12 @@
 
 class ForeignKeyConstraint():
   def __init__(self, constarint_name, index_name, parent_fields, reference):
-    print constarint_name;
     self.constarint_name= constarint_name;
     self.index_name= index_name;
     self.reference= reference;
     self.parent_fields= parent_fields;
- def __repr__(self):
-    return "Foreign Key(constraint_name= %s) (index_name=%s) (parent_fields= %s) (reference= %s)" %(self.constarint_name, self.index_name, self.parent_fields, self.reference)
+  def __repr__(self):
+    return "ForeignKeyConstraint(constraint_name= %s, index_name= %s, parent_fields= %s, reference= %s)" %(self.constarint_name, self.index_name, self.parent_fields, self.reference)
 
 class ReferenceDefinition():
   def __init__(self, name, col_name_list, match_option, on_delete, on_update):
@@ -66,7 +87,7 @@
     self.on_update= on_update;
     self.on_delete= on_delete;
   def __repr__(self):
-    return "Reference(name= %s) (col_name_list= %s) (match_option= %s) (on_delete= %s) (on_update= %s)" %(self.name, self.col_name_list, self.match_option, self.on_delete, self.on_update);
+    return "Reference(name= %s, col_name_list= %s, match_option= %s, on_delete= %s, on_update= %s)" %(self.name, self.col_name_list, self.match_option, self.on_delete, self.on_update);
 
 class CreateTableStatement(SqlStatement):
   def __init__(self, table_name, list_columns, if_not_exists= False, temporary = False):
@@ -78,4 +99,4 @@
   def update_table_options(self, options):
     self.table_options= options
   def __repr__(self):
-    return "CreateTableStatement(table= %s) (if_not_exists= %s) (temporary= %s) (columns= %s)" % (self.table_name, self.if_not_exists, self.temporary, str(self.columns))
+    return "CreateTableStatement(table= %s) (if_not_exists= %s) (temporary= %s) (columns= %s) (table_options= %s)" % (self.table_name, self.if_not_exists, self.temporary, str(self.columns),self.table_options)

=== modified file 'tests/parser_createtable_test.py'
--- tests/parser_createtable_test.py	2010-07-07 22:04:33 +0000
+++ tests/parser_createtable_test.py	2010-07-22 18:42:46 +0000
@@ -13,7 +13,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].nullable, False)
 
   def test_create_table_nullable_explicit(self):
@@ -23,7 +23,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].nullable, True)
 
   def test_create_table_nullable_implicit(self):
@@ -33,7 +33,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].nullable, True)
 
   def test_create_table_no_default(self):
@@ -43,7 +43,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].default, None)
 
   def test_create_table_default_string(self):
@@ -53,7 +53,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].default, "42")
 
   def test_create_table_default_string_with_nullable(self):
@@ -63,7 +63,7 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
     self.assertEqual(ct.columns[0].default, "42")
     self.assertEqual(ct.columns[0].nullable, False)
 
@@ -96,7 +96,8 @@
       statements= parser.parse_string(sql)
       self.assertNotEqual(statements, None, "using data_type %s" % data_type)
       ct= statements[0]
-      self.assertEqual(str(ct.columns[0].data_type), data_type)
+      self.assertEqual(str(ct.columns[0].data_type_desc.data_type), data_type)
+
 
   def test_create_table_primary_key(self):
     sql= "CREATE TABLE t1 (field1 INT NOT NULL PRIMARY KEY);"
@@ -105,9 +106,220 @@
     self.assertEqual(ct.table_name.qualifier, None)
     self.assertEqual(ct.table_name.name, "t1")
     self.assertEqual(ct.columns[0].name, "field1")
-    self.assertEqual(str(ct.columns[0].data_type), "INT")
-    self.assertEqual(ct.columns[0].nullable, False)
-    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+
+  def test_create_table__length(self):
+    sql= "CREATE TABLE t1 (field1 INT (26) NOT NULL PRIMARY KEY);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(str(ct.columns[0].data_type_desc.length), "26")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+
+  def test_create_table_length_length(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL (5,2) NOT NULL PRIMARY KEY);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(str(ct.columns[0].data_type_desc.length), "5")
+    self.assertEqual(str(ct.columns[0].data_type_desc.decimals), "2")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+
+  def test_create_table_length_length_unsigned_zerofill(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL (5,2) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(str(ct.columns[0].data_type_desc.length), "5")
+    self.assertEqual(str(ct.columns[0].data_type_desc.decimals), "2")
+    self.assertEqual(ct.columns[0].data_type_desc.unsigned, True)
+    self.assertEqual(ct.columns[0].data_type_desc.zerofill, True)
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+
+  def test_create_table_char_varchar(self):
+    sql= "CREATE TABLE t1 (field1 CHAR CHARACTER SET utf8 COLLATE col1);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(ct.columns[0].data_type_desc.data_type, "CHAR")
+    self.assertEqual(ct.columns[0].data_type_desc.length, None)
+    self.assertEqual(ct.columns[0].data_type_desc.decimals, None)
+    self.assertEqual(ct.columns[0].data_type_desc.unsigned, None)
+    self.assertEqual(ct.columns[0].data_type_desc.zerofill, None)
+    self.assertEqual(ct.columns[0].data_type_desc.charset_name, 'utf8')
+    self.assertEqual(ct.columns[0].data_type_desc.collation_name, 'col1')
+    self.assertEqual(ct.columns[0].nullable, True)
+    self.assertEqual(ct.columns[0].primary_key, False)
+
+  def test_create_table_foreign_constraint(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL NOT NULL PRIMARY KEY, FOREIGN KEY (field1) REFERENCES reftable (ref_field1));"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.columns[1].constarint_name, False);
+    self.assertEqual(ct.columns[1].index_name, None);
+    self.assertEqual(ct.columns[1].parent_fields[0],"field1");
+    self.assertEqual(ct.columns[1].reference.name,"reftable");
+    self.assertEqual(ct.columns[1].reference.col_name_list[0],"ref_field1");
+    self.assertEqual(ct.columns[1].reference.on_update,False);
+    self.assertEqual(ct.columns[1].reference.on_delete,False);
+    self.assertEqual(ct.columns[1].reference.match_option,False);
+
+  def test_create_table_foreign_constraint_options(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL NOT NULL PRIMARY KEY,FOREIGN KEY (field1) REFERENCES reftable (ref_field1) MATCH FULL ON DELETE CASCADE); "
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.columns[1].constarint_name, False);
+    self.assertEqual(ct.columns[1].index_name, None);
+    self.assertEqual(ct.columns[1].parent_fields[0],"field1");
+    self.assertEqual(ct.columns[1].reference.name,"reftable");
+    self.assertEqual(ct.columns[1].reference.col_name_list[0],"ref_field1");
+    self.assertEqual(ct.columns[1].reference.on_update,False);
+    self.assertEqual(ct.columns[1].reference.on_delete,"CASCADE");
+    self.assertEqual(ct.columns[1].reference.match_option,"FULL");
+
+
+  def test_create_table_foreign_constraint_options_constraint_name(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL NOT NULL PRIMARY KEY,CONSTRAINT cons1 FOREIGN KEY (field1) REFERENCES reftable (ref_field1) MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT); "
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.columns[1].constarint_name, 'cons1');
+    self.assertEqual(ct.columns[1].index_name, None);
+    self.assertEqual(ct.columns[1].parent_fields[0],"field1");
+    self.assertEqual(ct.columns[1].reference.name,"reftable");
+    self.assertEqual(ct.columns[1].reference.col_name_list[0],"ref_field1");
+    self.assertEqual(ct.columns[1].reference.on_update,"RESTRICT");
+    self.assertEqual(ct.columns[1].reference.on_delete,"CASCADE");
+    self.assertEqual(ct.columns[1].reference.match_option,"FULL");
+
+  def test_create_table_foreign_constraint_options_constraint_no_name(self):
+    sql= "CREATE TABLE t1 (field1 DECIMAL NOT NULL PRIMARY KEY,CONSTRAINT FOREIGN KEY (field1) REFERENCES reftable (ref_field1) MATCH FULL ON DELETE CASCADE); "
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "DECIMAL")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.columns[1].constarint_name, False);
+    self.assertEqual(ct.columns[1].index_name, None);
+    self.assertEqual(ct.columns[1].parent_fields[0],"field1");
+    self.assertEqual(ct.columns[1].reference.name,"reftable");
+    self.assertEqual(ct.columns[1].reference.col_name_list[0],"ref_field1");
+    self.assertEqual(ct.columns[1].reference.on_update,False);
+    self.assertEqual(ct.columns[1].reference.on_delete,"CASCADE");
+    self.assertEqual(ct.columns[1].reference.match_option,"FULL");
+
+  def test_create_table_table_option(self):
+    sql= "CREATE TABLE t1 (field1 INT NOT NULL PRIMARY KEY) AUTO_INCREMENT 5;"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.table_options[0][0],"AUTO_INCREMENT");
+    self.assertEqual(ct.table_options[0][1],5);
+
+  def test_create_table_table_option_multiple(self):
+    sql= "CREATE TABLE t1 (field1 INT NOT NULL PRIMARY KEY) AUTO_INCREMENT 5 CHARACTER SET utf8;"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.table_options[0][0],"AUTO_INCREMENT");
+    self.assertEqual(ct.table_options[0][1],5);
+    self.assertEqual(ct.table_options[1][0],"CHARACTER SET");
+    self.assertEqual(ct.table_options[1][1],"utf8");
+
+  def test_create_table_table_option_comment(self):
+    sql= "CREATE TABLE t1 (field1 INT) COMMENT 'comment1';"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.table_options[0][0],"COMMENT");
+    self.assertEqual(ct.table_options[0][1],"comment1");
+
+  def test_create_table_qualified_identifier(self):
+    sql= "CREATE TABLE t2.t1 (field1 INT);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, "t1")
+    self.assertEqual(ct.table_name.name, "t2")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+
+
+  def test_create_table_col_list_multiple_ol(self):
+    sql= "CREATE TABLE t1 (field1 INT, field2 CHAR,field3 BLOB);"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.columns[1].name, "field2")
+    self.assertEqual(str(ct.columns[1].data_type_desc.data_type), "CHAR")
+    self.assertEqual(ct.columns[2].name, "field3")
+    self.assertEqual(str(ct.columns[2].data_type_desc.data_type), "BLOB")
+ 
+  def test_create_table_table_option(self):
+    sql= "CREATE TABLE t1 (field1 INT NOT NULL DEFAULT a AUTO_INCREMENT PRIMARY KEY COMMENT 'comment1');"
+    statements= parser.parse_string(sql)
+    ct= statements[0]
+    self.assertEqual(ct.table_name.qualifier, None)
+    self.assertEqual(ct.table_name.name, "t1")
+    self.assertEqual(ct.columns[0].name, "field1")
+    self.assertEqual(str(ct.columns[0].data_type_desc.data_type), "INT")
+    self.assertEqual(ct.columns[0].nullable, False)
+    self.assertEqual(ct.columns[0].primary_key, True)
+    self.assertEqual(ct.columns[0].auto_increment, True)
+    self.assertEqual(ct.columns[0].comment,"comment1")
+
+
 
 if __name__ == '__main__':
   unittest.main()