def main():
"""
This program removes the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site",
configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute("DROP DATABASE IF EXISTS %s " % xalt.db())
cursor.close()
except MySQLdb.Error, e:
print("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit(1)
def main():
"""
This program removes the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute("DROP DATABASE IF EXISTS %s " % xalt.db())
cursor.close()
except MySQLdb.Error as e:
print ("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit (1)
def main():
"""
This program upgrade the XALT database schema from 0.7.5 to 1.1.3
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site",
configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
cursor.execute("USE " + xalt.db())
idx = 1
print("start")
# 1
cursor.execute("""
ALTER TABLE `xalt_link`
ADD COLUMN `cwd` varchar(1024) NULL AFTER `link_line`
""")
print("(%d) upgraded xalt_link table" % idx)
idx += 1
cursor.close()
except MySQLdb.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit(1)
def main():
"""
This program upgrade the XALT database schema from 0.7.5 to 1.1.3
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
cursor.execute("USE "+xalt.db())
idx = 1
print("start")
# 1
cursor.execute("""
ALTER TABLE `xalt_link`
ADD COLUMN `cwd` varchar(1024) NULL AFTER `link_line`
""")
print("(%d) upgraded xalt_link table" % idx); idx += 1
cursor.close()
except MySQLdb.Error as e:
print ("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit (1)
def main():
"""
This program upgrade the Database used by XALT to the current version
from the version specified in script name.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
cursor.execute("USE "+xalt.db())
idx = 1
print("start")
###########################################################################
# NOTE: New DB schema createDB.py uses unsigned int for some columns, but #
# modifying a signed to unsigned is problematic for columns already #
# referenced as FOREIGN KEYS. Therefor this script does not update #
# those columns. #
###########################################################################
# 1
cursor.execute("""
ALTER TABLE `xalt_link`
MODIFY COLUMN `link_program` varchar(64) NOT NULL,
ADD COLUMN `link_path` varchar(1024) NOT NULL AFTER `link_program`,
ADD COLUMN `link_module_name` varchar(64) AFTER `link_path`,
ADD COLUMN `link_line` blob AFTER `link_module_name`,
ADD INDEX `index_date` (`date`)
""")
print("(%d) upgraded xalt_link table" % idx); idx += 1
# 4
cursor.execute("""
ALTER TABLE `xalt_run`
MODIFY COLUMN `job_id` char(64) NOT NULL,
MODIFY COLUMN `num_cores` int(11) unsigned NOT NULL,
MODIFY COLUMN `job_num_cores` int(11) unsigned NOT NULL,
MODIFY COLUMN `num_nodes` int(11) unsigned NOT NULL,
MODIFY COLUMN `num_threads` smallint(6) unsigned NOT NULL,
MODIFY COLUMN `exit_code` smallint(6) NOT NULL,
ADD COLUMN `cmdline` blob NOT NULL AFTER `cwd`,
ADD INDEX `index_date` (`date` )
""")
print("(%d) upgraded xalt_run table" % idx); idx += 1
# 6
cursor.execute("""
ALTER TABLE `xalt_env_name`
ADD INDEX `a_env_name` (`env_name`)
""")
print("(%d) upgraded xalt_env_name table" % idx)
idx += 1
# 7
cursor.execute("""
ALTER TABLE `join_run_env`
MODIFY COLUMN `join_id` bigint(20) unsigned NOT NULL auto_increment
""")
print("(%d) upgraded join_run_env table" % idx)
idx += 1
#11
cursor.execute("""
ALTER TABLE `join_link_function`
MODIFY COLUMN `join_id` int(11) unsigned NOT NULL auto_increment
""")
print("(%d) upgraded xalt_function table" % idx)
idx += 1
# 12
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_total_env` (
`envT_id` bigint(20) unsigned NOT NULL auto_increment,
`run_id` int(11) NOT NULL,
`env_blob` blob NOT NULL,
PRIMARY KEY (`envT_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) created xalt_env table" % idx); idx += 1
cursor.close()
except MySQLdb.Error as e:
print ("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit (1)
def main():
"""
This program creates the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (args.confFn):
configFn = args.confFn
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site",
configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
cursor.execute("SHOW VARIABLES like 'version'")
row = cursor.fetchone()
mysqlV = row[1]
currentV = parseVersion(mysqlV)
if (currentV >= parseVersion("6.0")):
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
elif (currentV > parseVersion("4.1")):
cursor.execute(
"SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER\"")
# drop db if requested.
if (args.drop):
cursor.execute("DROP DATABASE IF EXISTS %s " % xalt.db())
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute(
"CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_bin"
% xalt.db())
cursor.execute("USE " + xalt.db())
idx = 1
print("start")
# 1
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_link` (
`link_id` int(11) unsigned NOT NULL auto_increment,
`uuid` char(36) NOT NULL,
`hash_id` char(40) NOT NULL,
`date` DATETIME NOT NULL,
`link_program` varchar(64) NOT NULL,
`link_path` varchar(1024) NOT NULL,
`link_module_name` varchar(64) ,
`link_line` blob ,
`cwd` varchar(1024) ,
`build_user` varchar(64) NOT NULL,
`build_syshost` varchar(64) NOT NULL,
`build_epoch` double NOT NULL,
`exec_path` varchar(1024) NOT NULL,
PRIMARY KEY (`link_id`),
INDEX `index_date` (`date`),
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_link table" % idx)
idx += 1
# 2
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_object` (
`obj_id` int(11) unsigned NOT NULL auto_increment,
`object_path` varchar(1024) NOT NULL,
`syshost` varchar(64) NOT NULL,
`hash_id` char(40) NOT NULL,
`module_name` varchar(64) ,
`timestamp` TIMESTAMP ,
`lib_type` char(2) NOT NULL,
PRIMARY KEY (`obj_id`),
INDEX `index_hash_id` (`hash_id`),
INDEX `thekey` (`object_path`(128), `hash_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_object table" % idx)
idx += 1
# 3
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_link_object` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`obj_id` int(11) unsigned NOT NULL,
`link_id` int(11) unsigned NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create join_link_object table" % idx)
idx += 1
# 4
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_run` (
`run_id` int(11) unsigned NOT NULL auto_increment,
`job_id` char(64) NOT NULL,
`run_uuid` char(36) NOT NULL,
`date` datetime NOT NULL,
`syshost` varchar(64) NOT NULL,
`uuid` char(36) ,
`hash_id` char(40) NOT NULL,
`account` varchar(20) NOT NULL,
`exec_type` char(7) NOT NULL,
`start_time` double NOT NULL,
`end_time` double NOT NULL,
`run_time` double NOT NULL,
`probability` double NOT NULL,
`num_cores` int(11) unsigned NOT NULL,
`num_nodes` int(11) unsigned NOT NULL,
`num_threads` smallint(6) unsigned NOT NULL,
`num_gpus` int(11) unsigned NOT NULL,
`queue` varchar(32) NOT NULL,
`sum_runs` int(11) unsigned NOT NULL,
`sum_time` double NOT NULL,
`user` varchar(32) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
`module_name` varchar(64) ,
`cwd` varchar(1024) NOT NULL,
`cmdline` blob NOT NULL,
`container` varchar(32) ,
PRIMARY KEY (`run_id` ),
INDEX `index_date` (`date` ),
INDEX `index_run_uuid` (`run_uuid` ),
INDEX `thekey` (`job_id`, `syshost` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_run table" % idx)
idx += 1
# 5
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_run_object` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`obj_id` int(11) unsigned NOT NULL,
`run_id` int(11) unsigned NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create join_run_object table" % idx)
idx += 1
# 6
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_env_name` (
`env_id` int(20) unsigned NOT NULL auto_increment,
`env_name` varchar(64) NOT NULL,
PRIMARY KEY (`env_id`),
UNIQUE KEY `env_name` (`env_name`),
INDEX `a_env_name` (`env_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_env_name table" % idx)
idx += 1
# 7
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_run_env` (
`join_id` bigint(20) unsigned NOT NULL auto_increment,
`env_id` int(11) unsigned NOT NULL,
`run_id` int(11) unsigned NOT NULL,
`date` DATE NOT NULL,
`env_value` blob NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`env_id`) REFERENCES `xalt_env_name`(`env_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create join_run_env table" % idx)
idx += 1
# 8
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_user` (
`usr_id` int(11) unsigned NOT NULL auto_increment,
`user` varchar(32) NOT NULL,
`anon_user` varchar(12) NOT NULL,
PRIMARY KEY (`usr_id`),
INDEX `the_user` (`user`),
INDEX `a_user` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_user table" % idx)
idx += 1
# 9
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_account` (
`acct_id` int(11) unsigned NOT NULL auto_increment,
`account` varchar(32) NOT NULL,
`anon_user` varchar(10) NOT NULL,
`field_of_science` varchar(64) NOT NULL,
PRIMARY KEY (`acct_id`),
INDEX `the_account` (`account`),
INDEX `a_acct` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_account table" % idx)
idx += 1
# 10
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_function` (
`func_id` int(11) unsigned NOT NULL auto_increment,
`function_name` varchar(255) NOT NULL,
PRIMARY KEY (`func_id`),
UNIQUE KEY `function_name` (`function_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_function table" % idx)
idx += 1
# 11
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_link_function` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`func_id` int(11) unsigned NOT NULL,
`link_id` int(11) unsigned NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`func_id`) REFERENCES `xalt_function`(`func_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create join_link_function table" % idx)
idx += 1
# 12
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_total_env` (
`envT_id` bigint(20) unsigned NOT NULL auto_increment,
`run_id` int(11) unsigned NOT NULL,
`date` DATE NOT NULL,
`env_blob` blob NOT NULL,
PRIMARY KEY (`envT_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_env table" % idx)
idx += 1
# 13
cursor.execute("""
ALTER TABLE `join_link_function`
ADD UNIQUE `unique_func_link_id` ( `func_id`, `link_id` )
""")
print("(%d) create unique key on join_link_function table" % idx)
idx += 1
# 14
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_pkg` (
`pkg_id` bigint(20) unsigned NOT NULL auto_increment,
`run_id` int(11) unsigned NOT NULL,
`program` varchar(12) NOT NULL,
`pkg_name` varchar(64) NOT NULL,
`pkg_version` varchar(32) ,
`pkg_path` varchar(1024) ,
PRIMARY KEY (`pkg_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_pkg table" % idx)
idx += 1
# 15
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_field_of_science` (
`fos_id` int(11) unsigned NOT NULL auto_increment,
`account` varchar(20) NOT NULL,
`field_of_science` varchar(80) NOT NULL,
`directorate` varchar(80) NOT NULL,
PRIMARY KEY (`fos_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_field_of_science table" % idx)
idx += 1
cursor.close()
except MySQLdb.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit(1)
def main():
"""
This program creates the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" % xalt.db())
cursor.execute("USE "+xalt.db())
idx = 1
print("start")
# 1
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_link` (
`link_id` int(11) unsigned NOT NULL auto_increment,
`uuid` char(36) NOT NULL,
`hash_id` char(40) NOT NULL,
`date` DATETIME NOT NULL,
`link_program` varchar(64) NOT NULL,
`link_path` varchar(1024) NOT NULL,
`link_module_name` varchar(64) ,
`link_line` blob ,
`cwd` varchar(1024) ,
`build_user` varchar(64) NOT NULL,
`build_syshost` varchar(64) NOT NULL,
`build_epoch` double NOT NULL,
`exit_code` tinyint(4) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
PRIMARY KEY (`link_id`),
INDEX `index_date` (`date`),
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_link table" % idx); idx += 1
# 2
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_object` (
`obj_id` int(11) unsigned NOT NULL auto_increment,
`object_path` varchar(1024) NOT NULL,
`syshost` varchar(64) NOT NULL,
`hash_id` char(40) NOT NULL,
`module_name` varchar(64) ,
`timestamp` TIMESTAMP ,
`lib_type` char(2) NOT NULL,
PRIMARY KEY (`obj_id`),
INDEX `index_hash_id` (`hash_id`),
INDEX `thekey` (`object_path`(128), `hash_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_object table" % idx ); idx += 1;
# 3
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_link_object` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`obj_id` int(11) unsigned NOT NULL,
`link_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_link_object table" % idx); idx += 1
# 4
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_run` (
`run_id` int(11) unsigned NOT NULL auto_increment,
`job_id` char(64) NOT NULL,
`run_uuid` char(36) NOT NULL,
`date` datetime NOT NULL,
`syshost` varchar(64) NOT NULL,
`uuid` char(36) ,
`hash_id` char(40) NOT NULL,
`account` varchar(20) NOT NULL,
`exec_type` char(7) NOT NULL,
`start_time` double NOT NULL,
`end_time` double NOT NULL,
`run_time` double NOT NULL,
`num_cores` int(11) unsigned NOT NULL,
`job_num_cores` int(11) unsigned NOT NULL,
`num_nodes` int(11) unsigned NOT NULL,
`num_threads` smallint(6) unsigned NOT NULL,
`queue` varchar(32) NOT NULL,
`exit_code` smallint(6) NOT NULL,
`user` varchar(32) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
`module_name` varchar(64) ,
`cwd` varchar(1024) NOT NULL,
`cmdline` blob NOT NULL,
PRIMARY KEY (`run_id` ),
INDEX `index_date` (`date` ),
INDEX `index_run_uuid` (`run_uuid` ),
INDEX `thekey` (`job_id`, `syshost` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_run table" % idx)
idx += 1
# 5
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_run_object` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`obj_id` int(11) unsigned NOT NULL,
`run_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_object table" % idx); idx += 1
# 6
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_env_name` (
`env_id` int(20) unsigned NOT NULL auto_increment,
`env_name` varchar(64) NOT NULL,
PRIMARY KEY (`env_id`),
UNIQUE KEY `env_name` (`env_name`),
INDEX `a_env_name` (`env_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_env_name table" % idx); idx += 1
# 7
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_run_env` (
`join_id` bigint(20) unsigned NOT NULL auto_increment,
`env_id` int(11) unsigned NOT NULL,
`run_id` int(11) unsigned NOT NULL,
`env_value` blob NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`env_id`) REFERENCES `xalt_env_name`(`env_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_env table" % idx); idx += 1
# 8
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_user` (
`usr_id` int(11) unsigned NOT NULL auto_increment,
`user` varchar(32) NOT NULL,
`anon_user` varchar(12) NOT NULL,
PRIMARY KEY (`usr_id`),
INDEX `the_user` (`user`),
INDEX `a_user` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_user table" % idx); idx += 1
# 9
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_account` (
`acct_id` int(11) unsigned NOT NULL auto_increment,
`account` varchar(32) NOT NULL,
`anon_user` varchar(10) NOT NULL,
`field_of_science` varchar(64) NOT NULL,
PRIMARY KEY (`acct_id`),
INDEX `the_account` (`account`),
INDEX `a_acct` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_account table" % idx ); idx += 1;
# 10
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_function` (
`func_id` int(11) unsigned NOT NULL auto_increment,
`function_name` varchar(255) NOT NULL,
PRIMARY KEY (`func_id`),
UNIQUE KEY `function_name` (`function_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_function table" % idx ); idx += 1;
# 11
cursor.execute("""
CREATE TABLE IF NOT EXISTS `join_link_function` (
`join_id` int(11) unsigned NOT NULL auto_increment,
`func_id` int(11) unsigned NOT NULL,
`link_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`func_id`) REFERENCES `xalt_function`(`func_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_link_function table" % idx); idx += 1
# 12
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_total_env` (
`envT_id` bigint(20) unsigned NOT NULL auto_increment,
`run_id` int(11) unsigned NOT NULL,
`env_blob` blob NOT NULL,
PRIMARY KEY (`envT_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_env table" % idx); idx += 1
# 13
cursor.execute("""
ALTER TABLE `join_link_function`
ADD UNIQUE `unique_func_link_id` ( `func_id`, `link_id` )
""")
print("(%d) create unique key on join_link_function table" % idx); idx += 1
cursor.close()
except MySQLdb.Error as e:
print ("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit (1)
def main():
"""
This program upgrade the Database used by XALT to the current version
from the version specified in script name.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site",
configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
cursor.execute("USE " + xalt.db())
idx = 1
print("start")
###########################################################################
# NOTE: New DB schema createDB.py uses unsigned int for some columns, but #
# modifying a signed to unsigned is problematic for columns already #
# referenced as FOREIGN KEYS. Therefor this script does not update #
# those columns. #
###########################################################################
# 1
cursor.execute("""
ALTER TABLE `xalt_link`
MODIFY COLUMN `link_program` varchar(64) NOT NULL,
ADD COLUMN `link_path` varchar(1024) NOT NULL AFTER `link_program`,
ADD COLUMN `link_module_name` varchar(64) AFTER `link_path`,
ADD COLUMN `link_line` blob AFTER `link_module_name`,
ADD INDEX `index_date` (`date`)
""")
print("(%d) upgraded xalt_link table" % idx)
idx += 1
# 4
cursor.execute("""
ALTER TABLE `xalt_run`
MODIFY COLUMN `job_id` char(64) NOT NULL,
MODIFY COLUMN `num_cores` int(11) unsigned NOT NULL,
MODIFY COLUMN `job_num_cores` int(11) unsigned NOT NULL,
MODIFY COLUMN `num_nodes` int(11) unsigned NOT NULL,
MODIFY COLUMN `num_threads` smallint(6) unsigned NOT NULL,
MODIFY COLUMN `exit_code` smallint(6) NOT NULL,
ADD COLUMN `cmdline` blob NOT NULL AFTER `cwd`,
ADD INDEX `index_date` (`date` )
""")
print("(%d) upgraded xalt_run table" % idx)
idx += 1
# 6
cursor.execute("""
ALTER TABLE `xalt_env_name`
ADD INDEX `a_env_name` (`env_name`)
""")
print("(%d) upgraded xalt_env_name table" % idx)
idx += 1
# 7
cursor.execute("""
ALTER TABLE `join_run_env`
MODIFY COLUMN `join_id` bigint(20) unsigned NOT NULL auto_increment
""")
print("(%d) upgraded join_run_env table" % idx)
idx += 1
#11
cursor.execute("""
ALTER TABLE `join_link_function`
MODIFY COLUMN `join_id` int(11) unsigned NOT NULL auto_increment
""")
print("(%d) upgraded xalt_function table" % idx)
idx += 1
# 12
cursor.execute("""
CREATE TABLE IF NOT EXISTS `xalt_total_env` (
`envT_id` bigint(20) unsigned NOT NULL auto_increment,
`run_id` int(11) NOT NULL,
`env_blob` blob NOT NULL,
PRIMARY KEY (`envT_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) created xalt_env table" % idx)
idx += 1
cursor.close()
except MySQLdb.Error as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit(1)
def main():
"""
This program creates the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site",
configFn))
xalt = XALTdb(configFn)
db = xalt.db()
try:
conn = xalt.connect()
cursor = conn.cursor()
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute(
"CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"
% xalt.db())
cursor.execute("USE " + xalt.db())
idx = 1
print("start")
# 1
cursor.execute("""
CREATE TABLE `xalt_link` (
`link_id` int(11) NOT NULL auto_increment,
`uuid` char(36) NOT NULL,
`hash_id` char(40) NOT NULL,
`date` DATETIME NOT NULL,
`link_program` varchar(10) NOT NULL,
`build_user` varchar(64) NOT NULL,
`build_syshost` varchar(64) NOT NULL,
`build_epoch` double NOT NULL,
`exit_code` tinyint(4) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
PRIMARY KEY (`link_id`),
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_link table" % idx)
idx += 1
# 2
cursor.execute("""
CREATE TABLE `xalt_object` (
`obj_id` int(11) NOT NULL auto_increment,
`object_path` varchar(1024) NOT NULL,
`syshost` varchar(64) NOT NULL,
`hash_id` char(40) NOT NULL,
`module_name` varchar(64) ,
`timestamp` TIMESTAMP ,
`lib_type` char(2) NOT NULL,
PRIMARY KEY (`obj_id`),
INDEX `index_hash_id` (`hash_id`),
INDEX `thekey` (`object_path`(128), `hash_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_object table" % idx)
idx += 1
# 3
cursor.execute("""
CREATE TABLE `join_link_object` (
`join_id` int(11) NOT NULL auto_increment,
`obj_id` int(11) NOT NULL,
`link_id` int(11) NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_link_object table" % idx)
idx += 1
# 4
cursor.execute("""
CREATE TABLE `xalt_run` (
`run_id` int(11) NOT NULL auto_increment,
`job_id` char(11) NOT NULL,
`run_uuid` char(36) NOT NULL,
`date` datetime NOT NULL,
`syshost` varchar(64) NOT NULL,
`uuid` char(36) ,
`hash_id` char(40) NOT NULL,
`account` char(11) NOT NULL,
`exec_type` char(7) NOT NULL,
`start_time` double NOT NULL,
`end_time` double NOT NULL,
`run_time` double NOT NULL,
`num_cores` int(11) NOT NULL,
`job_num_cores` int(11) NOT NULL,
`num_nodes` int(11) NOT NULL,
`num_threads` tinyint(4) NOT NULL,
`queue` varchar(32) NOT NULL,
`exit_code` tinyint(4) NOT NULL,
`user` varchar(32) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
`module_name` varchar(64) ,
`cwd` varchar(1024) NOT NULL,
PRIMARY KEY (`run_id`),
INDEX `index_run_uuid` (`run_uuid`),
INDEX `thekey` (`job_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_run table" % idx)
idx += 1
# 5
cursor.execute("""
CREATE TABLE `join_run_object` (
`join_id` int(11) NOT NULL auto_increment,
`obj_id` int(11) NOT NULL,
`run_id` int(11) NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_object table" % idx)
idx += 1
# 6
cursor.execute("""
CREATE TABLE `xalt_env_name` (
`env_id` int(11) NOT NULL auto_increment,
`env_name` varchar(64) NOT NULL,
PRIMARY KEY (`env_id`),
UNIQUE KEY `env_name` (`env_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_env_name table" % idx)
idx += 1
# 7
cursor.execute("""
CREATE TABLE `join_run_env` (
`join_id` int(11) NOT NULL auto_increment,
`env_id` int(11) NOT NULL,
`run_id` int(11) NOT NULL,
`env_value` blob NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`env_id`) REFERENCES `xalt_env_name`(`env_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_env table" % idx)
idx += 1
# 8
cursor.execute("""
CREATE TABLE `xalt_user` (
`usr_id` int(11) NOT NULL auto_increment,
`user` varchar(32) NOT NULL,
`anon_user` varchar(12) NOT NULL,
PRIMARY KEY (`usr_id`),
INDEX `the_user` (`user`),
INDEX `a_user` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_user table" % idx)
idx += 1
# 9
cursor.execute("""
CREATE TABLE `xalt_account` (
`acct_id` int(11) NOT NULL auto_increment,
`account` varchar(32) NOT NULL,
`anon_user` varchar(10) NOT NULL,
`field_of_science` varchar(64) NOT NULL,
PRIMARY KEY (`acct_id`),
INDEX `the_account` (`account`),
INDEX `a_acct` (`anon_acct`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
cursor.close()
except MySQLdb.Error, e:
print("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit(1)
def main():
"""
This program creates the Database used by XALT.
"""
args = CmdLineOptions().execute()
configFn = dbConfigFn(args.dbname)
if (not os.path.isfile(configFn)):
dirNm, exe = os.path.split(sys.argv[0])
fn = os.path.join(dirNm, configFn)
if (os.path.isfile(fn)):
configFn = fn
else:
configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn))
xalt = XALTdb(configFn)
db = xalt.db()
print (db)
try:
conn = xalt.connect()
cursor = conn.cursor()
# ftrack2015 start >>
# Modified below script to -
# 1) Make a new db if no 'xalt' db is present
# 2) If xalt db already exists then check for what all tables present
# 3) Create new tables (xalt_function & join_function_link)
#
tables = []
# If MySQL version < 4.1, comment out the line below
cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"")
# Check if database exists, if yes get all table names.
query = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'test'"
conn.query(query)
result = conn.store_result()
database = result.fetch_row()
if len(database) != 0 :
if (database[0][0] == 'test'):
cursor.execute("USE "+xalt.db()) # Use Database
# Get list of tables that exists already
cursor.execute("SHOW TABLES")
for (table_names,) in cursor:
tables.append(table_names)
print (tables)
else:
# If the database does not exist, create it, otherwise, switch to the database.
cursor.execute("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" % xalt.db())
cursor.execute("USE "+xalt.db())
idx = 1
print("start")
if ('xalt_link' not in tables):
# 1
cursor.execute("""
CREATE TABLE `xalt_link` (
`link_id` int(11) NOT NULL auto_increment,
`uuid` char(36) NOT NULL,
`hash_id` char(40) NOT NULL,
`date` DATETIME NOT NULL,
`link_program` varchar(10) NOT NULL,
`build_user` varchar(64) NOT NULL,
`build_syshost` varchar(64) NOT NULL,
`build_epoch` double NOT NULL,
`exit_code` tinyint(4) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
PRIMARY KEY (`link_id`),
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_link table" % idx); idx += 1
if ('xalt_object' not in tables):
# 2
cursor.execute("""
CREATE TABLE `xalt_object` (
`obj_id` int(11) NOT NULL auto_increment,
`object_path` varchar(1024) NOT NULL,
`syshost` varchar(64) NOT NULL,
`hash_id` char(40) NOT NULL,
`module_name` varchar(64) ,
`timestamp` TIMESTAMP ,
`lib_type` char(2) NOT NULL,
PRIMARY KEY (`obj_id`),
INDEX `index_hash_id` (`hash_id`),
INDEX `thekey` (`object_path`(128), `hash_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_object table" % idx ); idx += 1;
if ('join_link_object' not in tables):
# 3
cursor.execute("""
CREATE TABLE `join_link_object` (
`join_id` int(11) NOT NULL auto_increment,
`obj_id` int(11) NOT NULL,
`link_id` int(11) NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_link_object table" % idx); idx += 1
if ('xalt_run' not in tables):
# 4
cursor.execute("""
CREATE TABLE `xalt_run` (
`run_id` int(11) NOT NULL auto_increment,
`job_id` char(11) NOT NULL,
`run_uuid` char(36) NOT NULL,
`date` datetime NOT NULL,
`syshost` varchar(64) NOT NULL,
`uuid` char(36) ,
`hash_id` char(40) NOT NULL,
`account` char(11) NOT NULL,
`exec_type` char(7) NOT NULL,
`start_time` double NOT NULL,
`end_time` double NOT NULL,
`run_time` double NOT NULL,
`num_cores` int(11) NOT NULL,
`job_num_cores` int(11) NOT NULL,
`num_nodes` int(11) NOT NULL,
`num_threads` tinyint(4) NOT NULL,
`queue` varchar(32) NOT NULL,
`exit_code` tinyint(4) NOT NULL,
`user` varchar(32) NOT NULL,
`exec_path` varchar(1024) NOT NULL,
`module_name` varchar(64) ,
`cwd` varchar(1024) NOT NULL,
PRIMARY KEY (`run_id`),
INDEX `index_run_uuid` (`run_uuid`),
INDEX `thekey` (`job_id`, `syshost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_run table" % idx)
idx += 1
if ('join_run_object' not in tables):
# 5
cursor.execute("""
CREATE TABLE `join_run_object` (
`join_id` int(11) NOT NULL auto_increment,
`obj_id` int(11) NOT NULL,
`run_id` int(11) NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`),
FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_object table" % idx); idx += 1
if ('xalt_env_name' not in tables):
# 6
cursor.execute("""
CREATE TABLE `xalt_env_name` (
`env_id` int(11) NOT NULL auto_increment,
`env_name` varchar(64) NOT NULL,
PRIMARY KEY (`env_id`),
UNIQUE KEY `env_name` (`env_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_env_name table" % idx); idx += 1
if ('join_run_env' not in tables):
# 7
cursor.execute("""
CREATE TABLE `join_run_env` (
`join_id` int(11) NOT NULL auto_increment,
`env_id` int(11) NOT NULL,
`run_id` int(11) NOT NULL,
`env_value` blob NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`env_id`) REFERENCES `xalt_env_name`(`env_id`),
FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create join_run_env table" % idx); idx += 1
if ('xalt_user' not in tables):
# 8
cursor.execute("""
CREATE TABLE `xalt_user` (
`usr_id` int(11) NOT NULL auto_increment,
`user` varchar(32) NOT NULL,
`anon_user` varchar(12) NOT NULL,
PRIMARY KEY (`usr_id`),
INDEX `the_user` (`user`),
INDEX `a_user` (`anon_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_user table" % idx); idx += 1
if ('xalt_account' not in tables):
# 9
cursor.execute("""
CREATE TABLE `xalt_account` (
`acct_id` int(11) NOT NULL auto_increment,
`account` varchar(32) NOT NULL,
`anon_acct` varchar(10) NOT NULL,
`field_of_science` varchar(64) NOT NULL,
PRIMARY KEY (`acct_id`),
INDEX `the_account` (`account`),
INDEX `a_acct` (`anon_acct`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1
""")
print("(%d) create xalt_account table" % idx); idx += 1
if ('xalt_function' not in tables):
# 10
cursor.execute("""
CREATE TABLE `xalt_function` (
`func_id` int(11) NOT NULL auto_increment,
`function_name` varchar(1024) NOT NULL,
PRIMARY KEY (`func_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1
""")
print("(%d) create xalt_function table" % idx); idx += 1
if ('join_function_link' not in tables):
# 11
cursor.execute("""
CREATE TABLE `join_function_link` (
`join_id` int(11) NOT NULL auto_increment,
`func_id` int(11) NOT NULL,
`link_id` int(11) NOT NULL,
PRIMARY KEY (`join_id`),
FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`),
FOREIGN KEY (`func_id`) REFERENCES `xalt_function`(`func_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1
""")
print("(%d) create join_function_link table" % idx); idx += 1
# ftrack2015 ends <<
cursor.close()
except MySQLdb.Error, e:
print ("Error %d: %s" % (e.args[0], e.args[1]))
sys.exit (1)
