Mercurial > pylearn
changeset 638:4b1bb5810423
Updated sql commands for creating the view
gets rid of the dotted notation in dictionary and replaces with __
| author | desjagui@atchoum.iro.umontreal.ca |
|---|---|
| date | Wed, 21 Jan 2009 18:53:35 -0500 |
| parents | 83397981a118 |
| children | b79c4e1bbd7d |
| files | pylearn/dbdict/api0.py pylearn/dbdict/sql_commands.py |
| diffstat | 2 files changed, 21 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- 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);
--- 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])))