# Get primary key definition - should be a list of fields
pkeys = tuple(tspec['pkey'])
# Derived fields
if 'derivedfields' in tspec:
derivedfields = tspec['derivedfields']
else:
derivedfields = []
# SQL database connection
cursor = sql_connect(db_name)
# Run pre-flight SQL script
if 'preflight' in tspec and tspec['preflight'] is not None:
sql_multistmt(cursor, tspec['preflight'])
# Delete existing table
# Means field list order will not depend on sourcefields or sql_select
# But instead on sql_get cols
# stmt = "DROP TABLE IF EXISTS %s" % tab_name
# cursor.execute(stmt)
# Decide if this is a primary table or a derived one
# i.e. if you need an sql_create statement
if tspec['type'] == 'primary' and 'sql_select' not in tspec:
# Assume that raw indexed 'source tables' always have the suffix _ix
# Does not need the sql_create statement to be specified
# CHANGED: 2012-12-21 - uses the same method as import_sql to resolve aliases
tab_ix = tab_name + '_ix'
target_fields = clean_field_aliases(tspec['sourcefields'], tab_name)
ALTER TABLE idlongDVR_respCurr ADD COLUMN response_outcome CHAR(128);
ALTER TABLE idlongDVR_respCurr MODIFY COLUMN new_response_code TINYINT;
-- create a current responses table for idwideDVR
DROP TABLE IF EXISTS idwideDVR_respCurr;
CREATE TABLE idwideDVR_respCurr
SELECT *, MAX(modifiedat) AS latest FROM idwideDVR_responses
GROUP BY sitecode, spotidno, field, value, validation_message
ORDER BY sitecode, spotidno;
ALTER TABLE idwideDVR_respCurr DROP COLUMN latest;
ALTER TABLE idwideDVR_respCurr ADD KEY (sitecode, id, field, validation_message);
ALTER TABLE idwideDVR_respCurr ADD COLUMN response_outcome CHAR(128);
ALTER TABLE idwideDVR_respCurr MODIFY COLUMN new_response_code TINYINT;
"""
mypy.sql_multistmt(my_cursor, multi_statement)
# try:
# # my_cursor.execute(multi_statement)
# except:
# print "Error: Failed to excute latest_only DVR SQL statement"
# sys.exit(1)
# my_conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)
# Now close the connection
my_conn.close()
print "Disconnected"
# return a (no erorr) success code if all went well
print (0)
# Get primary key definition - should be a list of fields
pkeys = tuple(tspec['pkey'])
# Derived fields
if 'derivedfields' in tspec:
derivedfields = tspec['derivedfields']
else:
derivedfields = []
# SQL database connection
cursor = sql_connect(db_name)
# Run pre-flight SQL script
if 'preflight' in tspec and tspec['preflight'] is not None:
sql_multistmt(cursor, tspec['preflight'])
# Delete existing table
# Means field list order will not depend on sourcefields or sql_select
# But instead on sql_get cols
# stmt = "DROP TABLE IF EXISTS %s" % tab_name
# cursor.execute(stmt)
# Decide if this is a primary table or a derived one
# i.e. if you need an sql_create statement
if tspec['type'] == 'primary' and 'sql_select' not in tspec:
# Assume that raw indexed 'source tables' always have the suffix _ix
# Does not need the sql_create statement to be specified
# CHANGED: 2012-12-21 - uses the same method as import_sql to resolve aliases
tab_ix = tab_name + '_ix'
target_fields = clean_field_aliases(tspec['sourcefields'], tab_name)