Mercurial > pylearn

annotate pylearn/dbdict/sql_commands.py @ 633:e242c12eb30d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged
author desjagui@atchoum.iro.umontreal.ca
date Wed, 21 Jan 2009 03:23:50 -0500
parents 798607a058bd
children 89bc88affef0
rev   line source
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
1
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
2 def crazy_sql_command(viewname, cols, keytab, linktab, id_col='id', dict_id='dict_id', pair_id='pair_id'):
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
3
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
4 #create or replace view expview as select * from (select id as v1_id, value as nhid from
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
5 #test0 where name='nhid') nhid LEFT OUTER JOIN (select id as v2_id, value as lrate from
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
6 #test0 where name='lrate') lrate on nhid.v1_id = lrate.v2_id;
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
7
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
8 col_queries = []
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
9 colname0 = None
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
10 for i, (colname, table_col) in enumerate(cols):
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
11 safe_col = colname.replace('__','')
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
12 safe_col = safe_col.replace('.','__')
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
13
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
14 cols[i][0] = safe_col
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
15
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
16 if i == 0:
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
17 q = """(select %(dict_id)s as v%(i)s_id, %(table_col)s as %(safe_col)s
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
18 from \"%(keytab)s\", \"%(linktab)s\"
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
19 where name='%(colname)s'
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
20 and \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s)
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
21 %(safe_col)s """ % locals()
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
22 colname0 = safe_col
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
23 else:
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
24 q = """ LEFT OUTER JOIN (select %(dict_id)s as v%(i)s_id, %(table_col)s as %(safe_col)s
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
25 from \"%(keytab)s\", \"%(linktab)s\"
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
26 where name='%(colname)s'
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
27 and \"%(keytab)s\".%(id_col)s = \"%(linktab)s\".%(pair_id)s)
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
28 %(safe_col)s
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
29 on %(colname0)s.v0_id = %(safe_col)s.v%(i)s_id""" % locals()
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
30
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
31 col_queries.append(q)
633
desjagui@atchoum.iro.umontreal.ca
parents: 538
diff changeset
32
538
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
33 header = " create or replace view %s as select %s.v0_id as id, %s from " \
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
34 % (viewname, colname0, (", ".join([c[0] for c in cols])))
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
35
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
36 rval = header + "\n".join(col_queries)
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
37
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
38 return rval
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
39
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
40
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
41 #print crazy_sql_command('test0', 'expview', (('nhid', 'value'), ('lrate', 'value'), ('a','value')))
798607a058bd added missing files
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
diff changeset
42