Esempio n. 1
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)
Esempio n. 2
0
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)
Esempio n. 3
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)