# HG changeset patch
# User desjagui@atchoum.iro.umontreal.ca
# Date 1232582015 18000
# Node ID 4b1bb5810423b0047269319f76bae2c4140969b3
# Parent  83397981a118c59add2a1ee333432f3d3d029bef
Updated sql commands for creating the view
gets rid of the dotted notation in dictionary and replaces with __

diff -r 83397981a118 -r 4b1bb5810423 pylearn/dbdict/api0.py
--- a/pylearn/dbdict/api0.py	Wed Jan 21 16:02:07 2009 -0500
+++ b/pylearn/dbdict/api0.py	Wed Jan 21 18:53:35 2009 -0500
@@ -472,6 +472,7 @@
         
         # generate raw sql command string
         viewsql = crazy_sql_command(view.name, cols, \
+                                    h_self._dict_table.name, \
                                     h_self._pair_table.name, \
                                     h_self._link_table.name);
         
diff -r 83397981a118 -r 4b1bb5810423 pylearn/dbdict/sql_commands.py
--- a/pylearn/dbdict/sql_commands.py	Wed Jan 21 16:02:07 2009 -0500
+++ b/pylearn/dbdict/sql_commands.py	Wed Jan 21 18:53:35 2009 -0500
@@ -1,5 +1,5 @@
 
-def crazy_sql_command(viewname, cols, keytab, linktab, id_col='id', dict_id='dict_id', pair_id='pair_id'):
+def crazy_sql_command(viewname, cols, dicttab, keytab, linktab, id_col='id', dict_id='dict_id', pair_id='pair_id'):
 
     #create or replace view expview as select * from (select id as v1_id, value as nhid from
     #test0 where name='nhid') nhid LEFT OUTER JOIN (select id as v2_id, value as lrate from
@@ -8,22 +8,30 @@
     col_queries = []
     colname0 = None
     for i, (colname, table_col) in enumerate(cols):
+        safe_col = colname.replace('__','')
+        safe_col = safe_col.replace('.','__')
+      
+        cols[i][0] = safe_col
+
         if i == 0:
-            q = """(select %(dict_id)s as v%(i)s_id, %(table_col)s as %(colname)s 
-                    from \"%(keytab)s\", \"%(linktab)s\"
+            q = """(select %(dict_id)s as v%(i)s_id, %(table_col)s as %(safe_col)s 
+                    from \"%(dicttab)s\", \"%(keytab)s\", \"%(linktab)s\"
                     where name='%(colname)s'
-                    and \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s)
-                    %(colname)s """ % locals()
-            colname0 = colname
+                    and \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s
+                    and \"%(dicttab)s\".%(id_col)s = \"%(linktab)s\".%(dict_id)s)
+                    %(safe_col)s """ % locals()
+            colname0 = safe_col
         else:
-            q = """ LEFT OUTER JOIN (select %(dict_id)s as v%(i)s_id, %(table_col)s as %(colname)s 
-                    from \"%(keytab)s\", \"%(linktab)s\" 
+            q = """ LEFT OUTER JOIN (select %(dict_id)s as v%(i)s_id, %(table_col)s as %(safe_col)s 
+                    from \"%(dicttab)s\", \"%(keytab)s\", \"%(linktab)s\" 
                     where name='%(colname)s'
-                    and  \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s)
-                    %(colname)s 
-                    on %(colname0)s.v0_id = %(colname)s.v%(i)s_id""" % locals()
+                    and \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s
+                    and \"%(dicttab)s\".%(id_col)s = \"%(linktab)s\".%(dict_id)s)
+                    %(safe_col)s 
+                    on %(colname0)s.v0_id = %(safe_col)s.v%(i)s_id""" % locals()
+
         col_queries.append(q)
-
+       
     header = " create or replace view %s as select %s.v0_id as id, %s from " \
             % (viewname, colname0, (", ".join([c[0] for c in cols])))