Script createDB_in_py
[hide private]
[frames] | no frames]

Source Code for Script script-createDB_in_py

  1  #!/usr/bin/env python 
  2  # -*- python -*- 
  3  # 
  4  # Git Version: @git@ 
  5   
  6  #----------------------------------------------------------------------- 
  7  # XALT: A tool that tracks users jobs and environments on a cluster. 
  8  # Copyright (C) 2013-2014 University of Texas at Austin 
  9  # Copyright (C) 2013-2014 University of Tennessee 
 10  #  
 11  # This library is free software; you can redistribute it and/or modify 
 12  # it under the terms of the GNU Lesser General Public License as 
 13  # published by the Free Software Foundation; either version 2.1 of  
 14  # the License, or (at your option) any later version.  
 15  # 
 16  # This library is distributed in the hope that it will be useful, 
 17  # but WITHOUT ANY WARRANTY; without even the implied warranty of 
 18  # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 
 19  # Lesser  General Public License for more details.  
 20  # 
 21  # You should have received a copy of the GNU Lesser General Public 
 22  # License along with this library; if not, write to the Free 
 23  # Software Foundation, Inc., 59 Temple Place, Suite 330, 
 24  # Boston, MA 02111-1307 USA 
 25  #----------------------------------------------------------------------- 
 26   
 27  from __future__ import print_function 
 28  import os, sys, re, MySQLdb 
 29   
 30  dirNm, execName = os.path.split(os.path.realpath(sys.argv[0])) 
 31  sys.path.append(os.path.realpath(os.path.join(dirNm, "../libexec"))) 
 32   
 33  from XALTdb     import XALTdb 
 34  from xalt_util  import dbConfigFn 
 35  import argparse 
36 -class CmdLineOptions(object):
37 """ Command line Options class """ 38
39 - def __init__(self):
40 """ Empty Ctor """ 41 pass
42
43 - def execute(self):
44 """ Specify command line arguments and parse the command line""" 45 parser = argparse.ArgumentParser() 46 parser.add_argument("--dbname", dest='dbname', action="store", default = "xalt", help="xalt") 47 args = parser.parse_args() 48 return args
49
50 -def main():
51 """ 52 This program creates the Database used by XALT. 53 """ 54 55 args = CmdLineOptions().execute() 56 configFn = dbConfigFn(args.dbname) 57 58 if (not os.path.isfile(configFn)): 59 dirNm, exe = os.path.split(sys.argv[0]) 60 fn = os.path.join(dirNm, configFn) 61 if (os.path.isfile(fn)): 62 configFn = fn 63 else: 64 configFn = os.path.abspath(os.path.join(dirNm, "../site", configFn)) 65 66 xalt = XALTdb(configFn) 67 db = xalt.db() 68 69 try: 70 conn = xalt.connect() 71 cursor = conn.cursor() 72 73 # If MySQL version < 4.1, comment out the line below 74 cursor.execute("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"") 75 # If the database does not exist, create it, otherwise, switch to the database. 76 cursor.execute("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" % xalt.db()) 77 cursor.execute("USE "+xalt.db()) 78 79 idx = 1 80 81 82 print("start") 83 84 # 1 85 cursor.execute(""" 86 CREATE TABLE `xalt_link` ( 87 `link_id` int(11) NOT NULL auto_increment, 88 `uuid` char(36) NOT NULL, 89 `hash_id` char(40) NOT NULL, 90 `date` DATETIME NOT NULL, 91 `link_program` varchar(10) NOT NULL, 92 `build_user` varchar(64) NOT NULL, 93 `build_syshost` varchar(64) NOT NULL, 94 `build_epoch` double NOT NULL, 95 `exit_code` tinyint(4) NOT NULL, 96 `exec_path` varchar(1024) NOT NULL, 97 PRIMARY KEY (`link_id`), 98 UNIQUE KEY `uuid` (`uuid`) 99 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 100 """) 101 print("(%d) create xalt_link table" % idx); idx += 1 102 103 # 2 104 cursor.execute(""" 105 CREATE TABLE `xalt_object` ( 106 `obj_id` int(11) NOT NULL auto_increment, 107 `object_path` varchar(1024) NOT NULL, 108 `syshost` varchar(64) NOT NULL, 109 `hash_id` char(40) NOT NULL, 110 `module_name` varchar(64) , 111 `timestamp` TIMESTAMP , 112 `lib_type` char(2) NOT NULL, 113 PRIMARY KEY (`obj_id`), 114 INDEX `index_hash_id` (`hash_id`), 115 UNIQUE KEY `thekey` (`object_path`(512), `hash_id`, `syshost`) 116 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 117 """) 118 print("(%d) create xalt_object table" % idx ); idx += 1; 119 120 121 # 3 122 cursor.execute(""" 123 CREATE TABLE `join_link_object` ( 124 `join_id` int(11) NOT NULL auto_increment, 125 `obj_id` int(11) NOT NULL, 126 `link_id` int(11) NOT NULL, 127 PRIMARY KEY (`join_id`), 128 FOREIGN KEY (`link_id`) REFERENCES `xalt_link`(`link_id`), 129 FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`) 130 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 131 """) 132 print("(%d) create join_link_object table" % idx); idx += 1 133 134 # 4 135 cursor.execute(""" 136 CREATE TABLE `xalt_run` ( 137 `run_id` int(11) NOT NULL auto_increment, 138 `job_id` char(11) NOT NULL, 139 `run_uuid` char(36) NOT NULL, 140 `date` datetime NOT NULL, 141 142 `syshost` varchar(64) NOT NULL, 143 `uuid` char(36) , 144 `hash_id` char(40) NOT NULL, 145 146 `account` char(11) NOT NULL, 147 `exec_type` char(7) NOT NULL, 148 `start_time` double NOT NULL, 149 150 `end_time` double NOT NULL, 151 `run_time` double NOT NULL, 152 `num_cores` int(11) NOT NULL, 153 154 `num_nodes` int(11) NOT NULL, 155 `num_threads` tinyint(4) NOT NULL, 156 `queue` varchar(32) NOT NULL, 157 158 `user` varchar(32) NOT NULL, 159 `exec_path` varchar(1024) NOT NULL, 160 `module_name` varchar(64) , 161 `cwd` varchar(1024) NOT NULL, 162 PRIMARY KEY (`run_id`), 163 INDEX `index_run_uuid` (`run_uuid`), 164 INDEX `thekey` (`job_id`, `syshost`) 165 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 166 """) 167 print("(%d) create xalt_run table" % idx) 168 idx += 1 169 170 # 5 171 cursor.execute(""" 172 CREATE TABLE `join_run_object` ( 173 `join_id` int(11) NOT NULL auto_increment, 174 `obj_id` int(11) NOT NULL, 175 `run_id` int(11) NOT NULL, 176 177 PRIMARY KEY (`join_id`), 178 FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`), 179 FOREIGN KEY (`obj_id`) REFERENCES `xalt_object`(`obj_id`) 180 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 181 """) 182 print("(%d) create join_run_object table" % idx); idx += 1 183 184 185 # 6 186 cursor.execute(""" 187 CREATE TABLE `xalt_env_name` ( 188 `env_id` int(11) NOT NULL auto_increment, 189 `env_name` varchar(64) NOT NULL, 190 PRIMARY KEY (`env_id`), 191 UNIQUE KEY `env_name` (`env_name`) 192 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 193 """) 194 print("(%d) create xalt_env_name table" % idx); idx += 1 195 196 # 7 197 cursor.execute(""" 198 CREATE TABLE `join_run_env` ( 199 `join_id` int(11) NOT NULL auto_increment, 200 `env_id` int(11) NOT NULL, 201 `run_id` int(11) NOT NULL, 202 `env_value` blob NOT NULL, 203 PRIMARY KEY (`join_id`), 204 FOREIGN KEY (`env_id`) REFERENCES `xalt_env_name`(`env_id`), 205 FOREIGN KEY (`run_id`) REFERENCES `xalt_run`(`run_id`) 206 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 207 """) 208 print("(%d) create join_run_env table" % idx); idx += 1 209 210 # 8 211 cursor.execute(""" 212 CREATE TABLE `xalt_job_id` ( 213 `inc_id` int(11) NOT NULL auto_increment, 214 `job_id` int(11) NOT NULL, 215 PRIMARY KEY (`inc_id`) 216 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=1 217 """) 218 query = "INSERT INTO xalt_job_id VALUES(NULL, '0')" 219 conn.query(query) 220 221 cursor.close() 222 except MySQLdb.Error, e: 223 print ("Error %d: %s" % (e.args[0], e.args[1])) 224 sys.exit (1)
225 226 if ( __name__ == '__main__'): main() 227