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