dbinit.py revision 2343:a2b4a6ccee56
1# Copyright (c) 2003-2004 The Regents of The University of Michigan 2# All rights reserved. 3# 4# Redistribution and use in source and binary forms, with or without 5# modification, are permitted provided that the following conditions are 6# met: redistributions of source code must retain the above copyright 7# notice, this list of conditions and the following disclaimer; 8# redistributions in binary form must reproduce the above copyright 9# notice, this list of conditions and the following disclaimer in the 10# documentation and/or other materials provided with the distribution; 11# neither the name of the copyright holders nor the names of its 12# contributors may be used to endorse or promote products derived from 13# this software without specific prior written permission. 14# 15# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 16# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 17# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 18# A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 19# OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 20# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 21# LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 22# DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 23# THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 24# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 25# OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 26 27import MySQLdb 28 29class MyDB(object): 30 def __init__(self, options): 31 self.name = options.db 32 self.host = options.host 33 self.user = options.user 34 self.passwd = options.passwd 35 self.mydb = None 36 self.cursor = None 37 38 def admin(self): 39 self.close() 40 self.mydb = MySQLdb.connect(db='mysql', host=self.host, user=self.user, 41 passwd=self.passwd) 42 self.cursor = self.mydb.cursor() 43 44 def connect(self): 45 self.close() 46 self.mydb = MySQLdb.connect(db=self.name, host=self.host, 47 user=self.user, passwd=self.passwd) 48 self.cursor = self.mydb.cursor() 49 50 def close(self): 51 if self.mydb is not None: 52 self.mydb.close() 53 self.cursor = None 54 55 def query(self, sql): 56 self.cursor.execute(sql) 57 58 def drop(self): 59 self.query('DROP DATABASE IF EXISTS %s' % self.name) 60 61 def create(self): 62 self.query('CREATE DATABASE %s' % self.name) 63 64 def populate(self): 65 # 66 # Each run (or simulation) gets its own entry in the runs table to 67 # group stats by where they were generated 68 # 69 # COLUMNS: 70 # 'id' is a unique identifier for each run to be used in other 71 # tables. 72 # 'name' is the user designated name for the data generated. It is 73 # configured in the simulator. 74 # 'user' identifies the user that generated the data for the given 75 # run. 76 # 'project' another name to identify runs for a specific goal 77 # 'date' is a timestamp for when the data was generated. It can be 78 # used to easily expire data that was generated in the past. 79 # 'expire' is a timestamp for when the data should be removed from 80 # the database so we don't have years worth of junk. 81 # 82 # INDEXES: 83 # 'run' is indexed so you can find out details of a run if the run 84 # was retreived from the data table. 85 # 'name' is indexed so that two all run names are forced to be unique 86 # 87 self.query(''' 88 CREATE TABLE runs( 89 rn_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 90 rn_name VARCHAR(200) NOT NULL, 91 rn_sample VARCHAR(32) NOT NULL, 92 rn_user VARCHAR(32) NOT NULL, 93 rn_project VARCHAR(100) NOT NULL, 94 rn_date TIMESTAMP NOT NULL, 95 rn_expire TIMESTAMP NOT NULL, 96 PRIMARY KEY (rn_id), 97 UNIQUE (rn_name,rn_sample) 98 ) TYPE=InnoDB''') 99 100 # 101 # The stat table gives us all of the data for a particular stat. 102 # 103 # COLUMNS: 104 # 'stat' is a unique identifier for each stat to be used in other 105 # tables for references. 106 # 'name' is simply the simulator derived name for a given 107 # statistic. 108 # 'descr' is the description of the statistic and what it tells 109 # you. 110 # 'type' defines what the stat tells you. Types are: 111 # SCALAR: A simple scalar statistic that holds one value 112 # VECTOR: An array of statistic values. Such a something that 113 # is generated per-thread. Vectors exist to give averages, 114 # pdfs, cdfs, means, standard deviations, etc across the 115 # stat values. 116 # DIST: Is a distribution of data. When the statistic value is 117 # sampled, its value is counted in a particular bucket. 118 # Useful for keeping track of utilization of a resource. 119 # (e.g. fraction of time it is 25% used vs. 50% vs. 100%) 120 # VECTORDIST: Can be used when the distribution needs to be 121 # factored out into a per-thread distribution of data for 122 # example. It can still be summed across threads to find 123 # the total distribution. 124 # VECTOR2D: Can be used when you have a stat that is not only 125 # per-thread, but it is per-something else. Like 126 # per-message type. 127 # FORMULA: This statistic is a formula, and its data must be 128 # looked up in the formula table, for indicating how to 129 # present its values. 130 # 'subdata' is potentially used by any of the vector types to 131 # give a specific name to all of the data elements within a 132 # stat. 133 # 'print' indicates whether this stat should be printed ever. 134 # (Unnamed stats don't usually get printed) 135 # 'prereq' only print the stat if the prereq is not zero. 136 # 'prec' number of decimal places to print 137 # 'nozero' don't print zero values 138 # 'nonan' don't print NaN values 139 # 'total' for vector type stats, print the total. 140 # 'pdf' for vector type stats, print the pdf. 141 # 'cdf' for vector type stats, print the cdf. 142 # 143 # The Following are for dist type stats: 144 # 'min' is the minimum bucket value. Anything less is an underflow. 145 # 'max' is the maximum bucket value. Anything more is an overflow. 146 # 'bktsize' is the approximate number of entries in each bucket. 147 # 'size' is the number of buckets. equal to (min/max)/bktsize. 148 # 149 # INDEXES: 150 # 'stat' is indexed so that you can find out details about a stat 151 # if the stat id was retrieved from the data table. 152 # 'name' is indexed so that you can simply look up data about a 153 # named stat. 154 # 155 self.query(''' 156 CREATE TABLE stats( 157 st_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 158 st_name VARCHAR(255) NOT NULL, 159 st_descr TEXT NOT NULL, 160 st_type ENUM("SCALAR", "VECTOR", "DIST", "VECTORDIST", 161 "VECTOR2D", "FORMULA") NOT NULL, 162 st_print BOOL NOT NULL, 163 st_prereq SMALLINT UNSIGNED NOT NULL, 164 st_prec TINYINT NOT NULL, 165 st_nozero BOOL NOT NULL, 166 st_nonan BOOL NOT NULL, 167 st_total BOOL NOT NULL, 168 st_pdf BOOL NOT NULL, 169 st_cdf BOOL NOT NULL, 170 st_min DOUBLE NOT NULL, 171 st_max DOUBLE NOT NULL, 172 st_bktsize DOUBLE NOT NULL, 173 st_size SMALLINT UNSIGNED NOT NULL, 174 PRIMARY KEY (st_id), 175 UNIQUE (st_name) 176 ) TYPE=InnoDB''') 177 178 # 179 # This is the main table of data for stats. 180 # 181 # COLUMNS: 182 # 'stat' refers to the stat field given in the stat table. 183 # 184 # 'x' referrs to the first dimension of a multi-dimensional stat. For 185 # a vector, x will start at 0 and increase for each vector 186 # element. 187 # For a distribution: 188 # -1: sum (for calculating standard deviation) 189 # -2: sum of squares (for calculating standard deviation) 190 # -3: total number of samples taken (for calculating 191 # standard deviation) 192 # -4: minimum value 193 # -5: maximum value 194 # -6: underflow 195 # -7: overflow 196 # 'y' is used by a VECTORDIST and the VECTOR2D to describe the second 197 # dimension. 198 # 'run' is the run that the data was generated from. Details up in 199 # the run table 200 # 'tick' is a timestamp generated by the simulator. 201 # 'data' is the actual stat value. 202 # 203 # INDEXES: 204 # 'stat' is indexed so that a user can find all of the data for a 205 # particular stat. It is not unique, because that specific stat 206 # can be found in many runs and samples, in addition to 207 # having entries for the mulidimensional cases. 208 # 'run' is indexed to allow a user to remove all of the data for a 209 # particular execution run. It can also be used to allow the 210 # user to print out all of the data for a given run. 211 # 212 self.query(''' 213 CREATE TABLE data( 214 dt_stat SMALLINT UNSIGNED NOT NULL, 215 dt_x SMALLINT NOT NULL, 216 dt_y SMALLINT NOT NULL, 217 dt_run SMALLINT UNSIGNED NOT NULL, 218 dt_tick BIGINT UNSIGNED NOT NULL, 219 dt_data DOUBLE NOT NULL, 220 INDEX (dt_stat), 221 INDEX (dt_run), 222 UNIQUE (dt_stat,dt_x,dt_y,dt_run,dt_tick) 223 ) TYPE=InnoDB;''') 224 225 # 226 # Names and descriptions for multi-dimensional stats (vectors, etc.) 227 # are stored here instead of having their own entry in the statistics 228 # table. This allows all parts of a single stat to easily share a 229 # single id. 230 # 231 # COLUMNS: 232 # 'stat' is the unique stat identifier from the stat table. 233 # 'x' is the first dimension for multi-dimensional stats 234 # corresponding to the data table above. 235 # 'y' is the second dimension for multi-dimensional stats 236 # corresponding to the data table above. 237 # 'name' is the specific subname for the unique stat,x,y combination. 238 # 'descr' is the specific description for the uniqe stat,x,y 239 # combination. 240 # 241 # INDEXES: 242 # 'stat' is indexed so you can get the subdata for a specific stat. 243 # 244 self.query(''' 245 CREATE TABLE subdata( 246 sd_stat SMALLINT UNSIGNED NOT NULL, 247 sd_x SMALLINT NOT NULL, 248 sd_y SMALLINT NOT NULL, 249 sd_name VARCHAR(255) NOT NULL, 250 sd_descr TEXT, 251 UNIQUE (sd_stat,sd_x,sd_y) 252 ) TYPE=InnoDB''') 253 254 255 # 256 # The formula table is maintained separately from the data table 257 # because formula data, unlike other stat data cannot be represented 258 # there. 259 # 260 # COLUMNS: 261 # 'stat' refers to the stat field generated in the stat table. 262 # 'formula' is the actual string representation of the formula 263 # itself. 264 # 265 # INDEXES: 266 # 'stat' is indexed so that you can just look up a formula. 267 # 268 self.query(''' 269 CREATE TABLE formulas( 270 fm_stat SMALLINT UNSIGNED NOT NULL, 271 fm_formula BLOB NOT NULL, 272 PRIMARY KEY(fm_stat) 273 ) TYPE=InnoDB''') 274 275 # 276 # Each stat used in each formula is kept in this table. This way, if 277 # you want to print out a particular formula, you can simply find out 278 # which stats you need by looking in this table. Additionally, when 279 # you remove a stat from the stats table and data table, you remove 280 # any references to the formula in this table. When a formula is no 281 # longer referred to, you remove its entry. 282 # 283 # COLUMNS: 284 # 'stat' is the stat id from the stat table above. 285 # 'child' is the stat id of a stat that is used for this formula. 286 # There may be many children for any given 'stat' (formula) 287 # 288 # INDEXES: 289 # 'stat' is indexed so you can look up all of the children for a 290 # particular stat. 291 # 'child' is indexed so that you can remove an entry when a stat is 292 # removed. 293 # 294 self.query(''' 295 CREATE TABLE formula_ref( 296 fr_stat SMALLINT UNSIGNED NOT NULL, 297 fr_run SMALLINT UNSIGNED NOT NULL, 298 UNIQUE (fr_stat,fr_run), 299 INDEX (fr_stat), 300 INDEX (fr_run) 301 ) TYPE=InnoDB''') 302 303 # COLUMNS: 304 # 'event' is the unique event id from the event_desc table 305 # 'run' is simulation run id that this event took place in 306 # 'tick' is the tick when the event happened 307 # 308 # INDEXES: 309 # 'event' is indexed so you can look up all occurences of a 310 # specific event 311 # 'run' is indexed so you can find all events in a run 312 # 'tick' is indexed because we want the unique thing anyway 313 # 'event,run,tick' is unique combination 314 self.query(''' 315 CREATE TABLE events( 316 ev_event SMALLINT UNSIGNED NOT NULL, 317 ev_run SMALLINT UNSIGNED NOT NULL, 318 ev_tick BIGINT UNSIGNED NOT NULL, 319 INDEX(ev_event), 320 INDEX(ev_run), 321 INDEX(ev_tick), 322 UNIQUE(ev_event,ev_run,ev_tick) 323 ) TYPE=InnoDB''') 324 325 # COLUMNS: 326 # 'id' is the unique description id 327 # 'name' is the name of the event that occurred 328 # 329 # INDEXES: 330 # 'id' is indexed because it is the primary key and is what you use 331 # to look up the descriptions 332 # 'name' is indexed so one can find the event based on name 333 # 334 self.query(''' 335 CREATE TABLE event_names( 336 en_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 337 en_name VARCHAR(255) NOT NULL, 338 PRIMARY KEY (en_id), 339 UNIQUE (en_name) 340 ) TYPE=InnoDB''') 341 342 def clean(self): 343 self.query(''' 344 DELETE data 345 FROM data 346 LEFT JOIN runs ON dt_run=rn_id 347 WHERE rn_id IS NULL''') 348 349 self.query(''' 350 DELETE formula_ref 351 FROM formula_ref 352 LEFT JOIN runs ON fr_run=rn_id 353 WHERE rn_id IS NULL''') 354 355 self.query(''' 356 DELETE formulas 357 FROM formulas 358 LEFT JOIN formula_ref ON fm_stat=fr_stat 359 WHERE fr_stat IS NULL''') 360 361 self.query(''' 362 DELETE stats 363 FROM stats 364 LEFT JOIN data ON st_id=dt_stat 365 WHERE dt_stat IS NULL''') 366 367 self.query(''' 368 DELETE subdata 369 FROM subdata 370 LEFT JOIN data ON sd_stat=dt_stat 371 WHERE dt_stat IS NULL''') 372 373 self.query(''' 374 DELETE events 375 FROM events 376 LEFT JOIN runs ON ev_run=rn_id 377 WHERE rn_id IS NULL''') 378 379 self.query(''' 380 DELETE event_names 381 FROM event_names 382 LEFT JOIN events ON en_id=ev_event 383 WHERE ev_event IS NULL''') 384