Merge lp:~nahiljain/to-drizzle/parser-insert into lp:to-drizzle

[neh]

It has minor change in regular expression for strings in lexer.py.. Please check it..
It has code to parse insert table and test cases for it.

[Jay Pipes (jaypipes)]

Very nice work, Nahil! :)

review: Approve

=== modified file 'movetodrizzle/helpers/lexer.py'
--- movetodrizzle/helpers/lexer.py	2010-07-23 00:09:44 +0000
+++ movetodrizzle/helpers/lexer.py	2010-07-26 17:08:46 +0000
@@ -70,7 +70,16 @@
   'no' : 'NO',
   'action' : 'ACTION',
   'delete' : 'DELETE',
-  'comment' : 'COMMENT'
+  'comment' : 'COMMENT',
+  'insert' : 'INSERT',
+  'duplicate' : 'DUPLICATE',
+  'values' : 'VALUES',
+  'value' : 'VALUE',
+  'low_priority' : 'LOW_PRIORITY',
+  'delayed' : 'DELAYED', 
+  'high_priority' : 'HIGH_PRIORITY', 
+  'ignore' : 'IGNORE',
+  'into' : 'INTO'
 
 
 }
@@ -124,38 +133,41 @@
 
 
 def t_STRING(t):
-  r"(\'(.*)\')|(\"(.*)\")|(\`(.*)\`)"
+  #r"(\'(.*)\')|(\"(.*)\")|(\`(.*)\`)"
+  r"(\'(.*?)\')|(\"(.*?)\")|(\`(.*?)\`)"
   t.value= t.value[1:-1]
   return t
 
+
+
 #Define a rule so that we process comments
 def t_COMMENT_CODE(t):
-    r'\/\*.*\*\/'
-    pass
+  r'\/\*.*\*\/'
+  pass
   
 # Define a rule so we can track line numbers
 def t_newline(t):
-    r'\n+'
-    t.lexer.lineno += len(t.value)
+  r'\n+'
+  t.lexer.lineno += len(t.value)
 
 # A string containing ignored characters (spaces and tabs)
 t_ignore  = ' \t'
 
 # Error handling rule
 def t_error(t):
-    print "Illegal character '%s'" % t.value[0]
-    t.lexer.skip(1)
+  print "Illegal character '%s'" % t.value[0]
+  t.lexer.skip(1)
 
 # Build the lexer
-lexer = lex.lex(debug=False)
+lexer = lex.lex(debug= False)
 
 def tokenize(subject):
- """Given a string subject, tokenizes the string into LexToken objects and returns them"""
- lexer.input(subject)
- results= []
- while True:
-   t= lex.token()
-   if not t:
-     break
-   results.append(t)
- return results
+  """Given a string subject, tokenizes the string into LexToken objects and returns them"""
+  lexer.input(subject)
+  results= []
+  while True:
+    t= lex.token()
+    if not t:
+      break
+    results.append(t)
+  return results

=== modified file 'movetodrizzle/helpers/parser.py'
--- movetodrizzle/helpers/parser.py	2010-07-23 00:09:44 +0000
+++ movetodrizzle/helpers/parser.py	2010-07-26 17:08:46 +0000
@@ -17,9 +17,10 @@
   '''statement : statement_type SEMICOLON'''
   p[0]= p[1]
 
+
 def p_create_table(p):
   '''statement_type : create_table_simple'''
-  p[0]=p[1]
+  p[0]= p[1]
 
 def p_create_table_simple(p):
   '''create_table_simple : CREATE temporary TABLE if_not_exists identifier_qualified_non_qualified_opt LPAREN col_list RPAREN table_options'''
@@ -142,7 +143,7 @@
   if (len(p) == 2):
     p[0]= p[1]
   elif (len(p) == 3):
-    p[0]= str(p[1])+str(p[2])
+    p[0]= str(p[1]) + str(p[2])
   else:
     p[0]= None
 
@@ -285,21 +286,16 @@
     p[0]= [p[2]]
   else:
     p[0]= len(p) > 6;
-	
-    
 
 def p_unsigned_opt(p):
   '''unsigned_opt : UNSIGNED
                   | empty'''
   p[0]= len(p) > 1
 
-
 def p_zerofill_opt(p):
   '''zerofill_opt : ZEROFILL
                   | empty'''
   p[0]= len(p) > 1
-
-
   
   
 def p_mathematical(p):
@@ -365,7 +361,6 @@
         | DEFAULT CHARACTER SET'''
   pass
 
-
 def p_table_option_1(p):
   """table_option : ENGINE opt_equal identifier
   | AUTO_INCREMENT opt_equal NUMBER
@@ -400,6 +395,122 @@
 
 
 
+
+
+def p_insert(p):
+  '''statement_type : insert_simple
+                    | insert_set'''
+  p[0]= p[1]
+
+def p_insert_simple(p):
+  '''insert_simple : INSERT priority_opt ignore_opt into_opt identifier index_col_name_list_opt value_values LPAREN expr_default_list  RPAREN on_duplicate_opt'''
+  p[0]=  InsertStatement(p[2],p[3],p[5],p[6],p[9],p[11],None);
+
+def p_expr_default_list(p):
+  '''expr_default_list : expr_default COMMA expr_default_list
+                        | expr_default'''
+  if(len(p) > 2):
+    p[0]= [p[1]] + p[3]
+  else:
+    p[0]= [p[1]]
+def p_expr_default(p):
+  '''expr_default : DEFAULT
+                  | FLOAT
+                  | STRING
+                  | NUMBER'''
+  p[0]= p[1]
+
+def p_expr(p):
+  '''expr : STRING
+                  | FLOAT
+                  | NUMBER'''
+
+  p[0]= p[1]
+
+def p_on_duplicate_option(p):
+  '''on_duplicate_opt :  ON DUPLICATE KEY UPDATE col_name_expr_list
+                      | empty'''
+  if(len(p) > 5):
+    p[0]= p[5]
+  else:
+    p[0]= None
+
+
+def p_col_name_expr_list(p):
+  '''col_name_expr_list : col_name EQUAL expr COMMA col_name_expr_list
+                          | col_name EQUAL expr'''
+
+  if(len(p) <= 4):
+    p[0]= [ColumnNameExprPair(p[1],p[3])];
+
+  else:
+    p[0]= [ColumnNameExprPair(p[1],p[3])] + p[5]
+
+def p_col_name(p):
+  '''col_name : identifier'''
+  p[0 = p[1]
+
+def p_value_values(p):
+  '''value_values : VALUES
+                  | VALUE'''
+  p[0]= p[1]
+  
+def p_priority_opt(p):
+  '''priority_opt : LOW_PRIORITY 
+                  | DELAYED 
+                  | HIGH_PRIORITY
+                  | empty'''
+  if(len(p) > 1):
+    p[0]= p[1]
+  else:
+    p[0]= len(p) > 1;
+
+def p_ignore_opt(p):
+  '''ignore_opt : IGNORE
+              | empty'''
+  if(len(p) > 1):
+    p[0]= p[1];
+  else:
+    p[0]= len(p) > 1;
+
+def p_into_opt(p):
+  '''into_opt : INTO
+              | empty'''
+  if(len(p) > 1):
+    p[0]= p[1];
+  else:
+    p[0]= len(p) > 1;
+
+
+
+
+def p_index_col_name_list_opt(p):
+  '''index_col_name_list_opt : LPAREN index_col_name_list RPAREN
+                             | empty '''
+  if(len(p) == 4):
+    p[0]= p[2]
+  else:
+    p[0]= None
+
+def p_insert_set(p):
+  '''insert_set : INSERT priority_opt ignore_opt into_opt identifier SET col_name_expr_default_list on_duplicate_opt'''
+  p[0]=  InsertStatement(p[2],p[3],p[5],None,None,p[8],p[7]);
+  '''insert_simple : INSERT priority_opt ignore_opt into_opt identifier index_col_name_list_opt value_values LPAREN expr_default_list  RPAREN on_duplicate_opt'''
+
+
+
+
+def p_col_name_expr_default_list(p):
+  '''col_name_expr_default_list : col_name EQUAL expr_default COMMA  col_name_expr_default_list
+                          | col_name EQUAL expr_default'''
+
+  if(len(p) <= 4):
+    p[0]= [ColumnNameExprPair(p[1],p[3])];
+
+  else:
+    p[0]= [ColumnNameExprPair(p[1],p[3])] + p[5]
+
+
 def p_empty(p):
   'empty : '
   pass # an empty rule that represents an optional

=== modified file 'movetodrizzle/helpers/statement_nodes.py'
--- movetodrizzle/helpers/statement_nodes.py	2010-07-23 00:09:44 +0000
+++ movetodrizzle/helpers/statement_nodes.py	2010-07-26 17:08:46 +0000
@@ -21,14 +21,14 @@
 
 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;
+    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)" %(
@@ -43,14 +43,14 @@
 
 class ColumnDefinition():
   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_desc= data_type_desc;
-    self.nullable= nullable;
-    self.default= default;
-    self.auto_increment= auto_increment;
-    self.unique= unique;
-    self.primary_key= primary_key;
-    self.comment= comment;
+    self.name= name
+    self.data_type_desc= data_type_desc
+    self.nullable= nullable
+    self.default= default
+    self.auto_increment= auto_increment
+    self.unique= unique
+    self.primary_key= primary_key
+    self.comment= comment
   def __repr__(self):
     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,
@@ -66,16 +66,16 @@
 class Identifier():
   def __init__(self, name, qualifier= None):
     self.name= name
-    self.qualifier= qualifier;
+    self.qualifier= qualifier
   def __repr__(self):
     return "Identifier(name= %s, qualifier= %s)" % (self.name, str(self.qualifier))
 
 class ForeignKeyConstraint():
   def __init__(self, constarint_name, index_name, parent_fields, reference):
-    self.constarint_name= constarint_name;
-    self.index_name= index_name;
-    self.reference= reference;
-    self.parent_fields= parent_fields;
+    self.constarint_name= constarint_name
+    self.index_name= index_name
+    self.reference= reference
+    self.parent_fields= parent_fields
   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)
 
@@ -84,19 +84,45 @@
     self.name= name
     self.col_name_list= col_name_list
     self.match_option= match_option
-    self.on_update= on_update;
-    self.on_delete= on_delete;
+    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):
-    self.table_name= table_name;
-    self.temporary= temporary;
-    self.if_not_exists= if_not_exists;
-    self.columns= list_columns;
+    self.table_name= table_name
+    self.temporary= temporary
+    self.if_not_exists= if_not_exists
+    self.columns= list_columns
     self.table_options= {}
   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) (table_options= %s)" % (self.table_name, self.if_not_exists, self.temporary, str(self.columns),self.table_options)
+
+class ColumnNameExprPair():
+  def __init__(self, col_name, expr):
+    self.column_name= col_name
+    self.expr= expr
+  def __repr__(self):
+    return "ColumnNameExprPair (column_name= %s, expr= %s)" %(self.column_name, self.expr)
+
+class InsertStatement(SqlStatement):
+  def __init__(self,priority, ignore, table_name, index_col_name_list, list_of_values, on_duplicate,set_list):
+    self.priority= priority
+    self.ignore= ignore
+    self.table_name= table_name
+    self.index_col_name_list= index_col_name_list
+    self.list_of_values= list_of_values
+    self.on_duplicate= on_duplicate
+    self.set_list= set_list
+  def __repr__(self):
+    return "InsertStatement(priority= %s, ignore= %s, table_name= %s, index_col_name_list= %s, list_of_values= %s, on_duplicate= %s, set_list= %s)" % ( 
+        self.priority, 
+        self.ignore, 
+        self.table_name, 
+        self.index_col_name_list, 
+        self.list_of_values, 
+        self.on_duplicate, 
+        self.set_list)

=== added file 'tests/parser_insert_test.py'
--- tests/parser_insert_test.py	1970-01-01 00:00:00 +0000
+++ tests/parser_insert_test.py	2010-07-26 17:08:46 +0000
@@ -0,0 +1,190 @@
+#!/usr/bin/python
+import sys
+import unittest
+sys.path.append("../movetodrizzle")
+import helpers.parser as parser
+ 
+class Tester( unittest.TestCase ):
+  def test_insert_table_simple( self ):
+    sql = "INSERT INTO t1 values (21,22);"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list, None )
+
+  def test_insert_table_priority( self ):
+    sql = "INSERT LOW_PRIORITY INTO t1 values (21,22);"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, "LOW_PRIORITY" )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list, None )
+
+  def test_insert_table_priority_IGNORE( self ):
+    sql = "INSERT LOW_PRIORITY IGNORE INTO t1 values (21,22);"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, "LOW_PRIORITY" )
+    self.assertEqual( ct.ignore, "IGNORE" )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list, None )
+
+
+  def test_insert_table_priority_IGNORE( self ):
+    sql = "INSERT LOW_PRIORITY IGNORE INTO t1 values (21,22);"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, "LOW_PRIORITY" )
+    self.assertEqual( ct.ignore, "IGNORE" )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list, None )
+
+  def test_insert_table_priority_IGNORE_opt_column_list( self ):
+    sql = "INSERT LOW_PRIORITY IGNORE INTO t1 (a1,a2) values (21,22);"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, "LOW_PRIORITY" )
+    self.assertEqual( ct.ignore, "IGNORE" )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, ['a1','a2'] )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list, None )
+
+  def test_insert_table_column_list_on_duplicate(self):
+    sql = "INSERT INTO t1 (a1,a2) values (21,22) ON DUPLICATE KEY UPDATE b1=200, b2='abc', b3='some_value';"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, ['a1','a2'] )
+    self.assertEqual( ct.list_of_values, [21, 22] )
+    self.assertEqual( ct.on_duplicate[0].column_name,"b1" )
+    self.assertEqual( ct.on_duplicate[0].expr,200 )
+    self.assertEqual( ct.on_duplicate[1].column_name,"b2" )
+    self.assertEqual( ct.on_duplicate[1].expr,'abc' )
+    self.assertEqual( ct.on_duplicate[2].column_name,"b3" )
+    self.assertEqual( ct.on_duplicate[2].expr,'some_value' )
+    self.assertEqual( ct.set_list, None )
+
+  def test_insert_table_set_basic_default( self ):
+    sql = "INSERT INTO t1 SET a1=DEFAULT;"
+    statements = parser.parse_string (sql) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, "DEFAULT" )
+
+
+  def test_insert_table_set_basic_string( self ):
+    sql = "INSERT INTO t1 SET a1='abc';"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, "abc" )
+
+
+  def test_insert_table_set_basic_number( self ):
+    sql = "INSERT INTO t1 SET a1=10;"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None )
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, 10 )
+
+  def test_insert_table_set_basic_number_PRIORITY_IGNORE( self ):
+    sql = "INSERT LOW_PRIORITY IGNORE INTO t1 SET a1=10;"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, "LOW_PRIORITY" )
+    self.assertEqual( ct.ignore, "IGNORE" )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None)
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, 10 )
+
+  def test_insert_table_set_multiple_number_string(self):
+    sql = "INSERT INTO t1 SET a1= 10,a2='abc',a3=DEFAULT;"
+    statements = parser.parse_string (sql) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None)
+    self.assertEqual( ct.on_duplicate, None )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, 10 )
+    self.assertEqual( ct.set_list[1].column_name, "a2" )
+    self.assertEqual( ct.set_list[1].expr, 'abc' )
+    self.assertEqual( ct.set_list[2].column_name, "a3" )
+    self.assertEqual( ct.set_list[2].expr, 'DEFAULT' )
+
+  def test_insert_table_set_basic_number_on_duplicate_single( self ):
+    sql = "INSERT INTO t1 SET a1=10 ON DUPLICATE KEY UPDATE b1=200;"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None )
+    self.assertEqual( ct.on_duplicate[0].column_name,"b1" )
+    self.assertEqual( ct.on_duplicate[0].expr,200 )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, 10 )
+
+  def test_insert_table_set_basic_number_on_duplicate_multiple( self ):
+    sql = "INSERT INTO t1 SET a1=10 ON DUPLICATE KEY UPDATE b1=200, b2='abc', b3='some_value';"
+    statements = parser.parse_string ( sql ) 
+    ct = statements[0]
+    self.assertEqual( ct.priority, None )
+    self.assertEqual( ct.ignore, None )
+    self.assertEqual( ct.table_name, "t1" )
+    self.assertEqual( ct.index_col_name_list, None )
+    self.assertEqual( ct.list_of_values, None)
+    self.assertEqual( ct.on_duplicate[0].column_name,"b1" )
+    self.assertEqual( ct.on_duplicate[0].expr,200 )
+    self.assertEqual( ct.on_duplicate[1].column_name,"b2" )
+    self.assertEqual( ct.on_duplicate[1].expr,'abc' )
+    self.assertEqual( ct.on_duplicate[2].column_name,"b3" )
+    self.assertEqual( ct.on_duplicate[2].expr,'some_value' )
+    self.assertEqual( ct.set_list[0].column_name, "a1" )
+    self.assertEqual( ct.set_list[0].expr, 10 )
+
+if __name__ == '__main__':
+    unittest.main()