class SQL(object):
## Test schema helper SQL
drop_test_schema = td("""
DROP SCHEMA IF EXISTS test CASCADE""")
create_test_schema = td("""
CREATE SCHEMA test""")
## Common schema helper SQL
delete_openarc_rpc = td("""
DELETE FROM openarc.rpc_registry""")
class SQL(TestOABase.SQL):
"""Boilerplate SQL needed for rest of class"""
get_search_path = td("""
SHOW search_path""")
create_sample_table = td("""
CREATE TABLE test.sample_table( field1 serial, field2 int NOT NULL)"""
)
insert_sample_row = td("""
INSERT INTO test.sample_table( field2 ) VALUES ( %s )""")
get_rows_from_sample_table = td("""
SELECT field2 FROM test.sample_table""")
def SQL(self):
# Default SQL defined for all tables
default_sql = {
"read" : {
"id" : self.SQLpp("""
SELECT *
FROM {0}.{1}
WHERE {2}=%s
ORDER BY {2} {3}"""),
},
"update" : {
"id" : self.SQLpp("""
UPDATE {0}.{1}
SET %s
WHERE {2}=%s""")
},
"insert" : {
"id" : self.SQLpp("""
INSERT INTO {0}.{1}(%s)
VALUES (%s)
RETURNING {2}""")
},
"delete" : {
"id" : self.SQLpp("""
DELETE FROM {0}.{1}
WHERE {2}=%s""")
},
"admin" : {
"fkeys" : self.SQLpp("""
SELECT tc.constraint_name,
kcu.column_name as id,
kcu.constraint_schema as schema,
tc.table_name as table,
ccu.table_schema as points_to_schema,
ccu.table_name as points_to_table_name,
ccu.column_name as points_to_id
FROM information_schema.table_constraints as tc
INNER JOIN information_schema.key_column_usage as kcu
ON tc.constraint_name=kcu.constraint_name
INNER JOIN information_schema.constraint_column_usage as ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND ccu.table_schema='{0}'
AND ccu.table_name='{1}'"""),
"mkindex" : self.SQLpp("""
CREATE %s INDEX IF NOT EXISTS {1}_%s ON {0}.{1} (%s) %s"""),
"mkschema" : self.SQLpp("""
SELECT public.frieze_schema_create('{0}')"""),
"mktable" : self.SQLpp("""
CREATE table {0}.{1}({2} serial primary key)"""),
"schema" : self.SQLpp("""
SELECT 1
FROM information_schema.schemata
WHERE schema_name='{0}'"""),
"table" : self.SQLpp("""
SELECT *
FROM {0}.{1}
WHERE 1=0""")
}
}
# Add in id retrieval for oagprops
for stream, streaminfo in self._oag.streams.items():
if self._oag.is_oagnode(stream):
stream_sql_key = 'by_'+stream
stream_sql = td("""
SELECT *
FROM {0}.{1}
WHERE {2}=%s
ORDER BY {3} {4}""").format(self._oag.context, self._oag.dbtable, streaminfo[0].dbpkname[1:]+'_'+stream, self._oag.dbpkname, self.SQLorderdir)
default_sql['read'][stream_sql_key] = stream_sql
# Add in update/delegate by indices
for index, idxinfo in self._oag.dbindices.items():
select_sql = self.SQLpp("""
SELECT *
FROM {0}.{1}
WHERE %s
ORDER BY {2} {3}""")
update_sql = self.SQLpp("""
UPDATE {0}.{1}
SET %%s
WHERE %s""")
# Genrate where clauses
where_clauses = []
for f in idxinfo[0]:
where_clauses.append("{0}=%s".format(self._oag.stream_db_mapping[f] if self._oag.is_oagnode(f) else f))
default_sql['read']['by_'+index] = select_sql % ' AND '.join(where_clauses)
default_sql['update']['by_'+index] = update_sql % ' AND '.join(where_clauses)
# Add in "all" search
default_sql['read']['by_all'] = self.SQLpp("""
SELECT *
FROM {0}.{1}
WHERE 1=1
ORDER BY {2}""")
# Add in user defined SQL
for action, sqlinfo in self._oag.dblocalsql.items():
for index, sql in sqlinfo.items():
default_sql[action]['by_'+index] = sql.format(self._oag.context, self._oag.dbtable, self._oag.dbpkname)
return default_sql
def SQLpp(self, SQL):
"""Pretty prints SQL and populates schema{0}.table{1} and its primary
key{2} in given SQL string"""
return td(
SQL.format(self._oag.context, self._oag.dbtable,
self._oag.dbpkname))
class SQL(object):
get_current_time = td("""
select now() at time zone 'utc'""")