· 8 years ago · Mar 01, 2018, 01:26 PM
1#!/usr/bin/env python
2# encoding: utf-8
3
4# Copyright © Domenico Carbotta, 2007
5# <domenico.carbotta@gmail.com>
6# Released under the GNU General Public License
7
8
9import sys
10import os
11import random
12import time
13import csv
14import optparse
15
16
17class Domain (object):
18
19 def __init__(self, name, count):
20 self.name = name
21 self.count = count
22 self.padding = len(str(count - 1))
23
24 def new_item(self, prng):
25 num = prng.randint(0, self.count - 1)
26 return self.name + "_" + str(num).zfill(self.padding)
27
28
29class Relation (object):
30
31 def __init__(self, name, count, fields, domains, key, bindings):
32 assert count <= prod(d.count for d in projection(domains, key))
33 self.name = name
34 self.count = count
35 self.fields = fields
36 self.domains = domains
37 self.key = key
38 self.bindings = bindings
39 self.seed = None
40
41 def rows(self, seed=None):
42 prng = random.Random(seed)
43 used_keys = []
44 for i in range(self.count):
45 row = None
46 this_key = None
47 while True:
48 row = tuple(d.new_item(prng) for d in self.domains)
49 this_key = projection(row, self.key)
50 if this_key not in used_keys:
51 break
52 used_keys.append(this_key)
53 yield row
54
55
56def prod(items):
57 return reduce(int.__mul__, items, 1)
58
59
60def projection(t, indexes):
61 return tuple(t[i] for i in indexes)
62
63
64def parse(spec):
65 ## Specifications are in the format:
66 ## # comment
67 ## dbname <name>
68 ## (domain <name> <count>)+
69 ## (relation <name> <count> (<field>:<domain>(:b?k?)?)+)+
70
71 lines = [str.strip(line) for line in spec.split("\n")
72 if not line.startswith("#")]
73
74 dbns = [l for l in lines if l.startswith("dbname ")]
75 doms = [l for l in lines if l.startswith("domain ")]
76 rels = [l for l in lines if l.startswith("relation ")]
77
78 assert len(dbns) == 1, "no database name specified"
79 dbname = dbns[0].split()[1]
80
81 domains_dict = {}
82 for d in doms:
83 _, name, count = d.split()
84 domains_dict[name] = Domain(name, int(count))
85
86 relations = []
87 for r in rels:
88 _, name, count, fdesc = r.split(None, 3)
89 fields = []
90 domains = []
91 key = []
92 bindings = []
93 i = 0
94 for f in fdesc.split():
95 if f.count(":") == 2:
96 f, flags = f.rsplit(":", 1)
97 if "k" in flags:
98 key.append(i)
99 if "b" in flags:
100 bindings.append(i)
101 field, domain = f.split(":")
102 fields.append(field)
103 domains.append(domains_dict[domain])
104 i += 1
105 assert len(key) != 0
106 relations.append(Relation(name, int(count), fields,
107 domains, key, bindings))
108
109 assert len(relations) != 0
110 assert dbname is not None
111
112 return dbname, relations
113
114
115def dump_schema(relations, dbname, directory):
116 sql = []
117 sql.append("DROP DATABASE IF EXISTS " + dbname)
118 sql.append("CREATE DATABASE " + dbname)
119 sql.append("USE " + dbname)
120 for r in relations:
121 create = "CREATE TABLE " + r.name + "("
122 create += ", ".join([f + " VARCHAR(255)" for f in r.fields])
123 create += ", PRIMARY KEY ("
124 create += ", ".join(projection(r.fields, r.key))
125 create += "))"
126 sql.append(create)
127 sql.append("LOAD DATA LOCAL INFILE 'rel_" + r.name + ".csv'" +
128 " INTO TABLE " + r.name +
129 " FIELDS TERMINATED BY ','")
130 batchfile = open(directory + "/generate.sql", "w")
131 print >> batchfile, ";\n".join(sql)
132
133
134def dump_data(relations, directory, seed=None):
135 for r in relations:
136 print "creating relation " + r.name + "..."
137 filename = directory + "/rel_" + r.name + ".csv"
138 writer = csv.writer(open(filename, "w"))
139 writer.writerows(r.rows(seed))
140 print "done."
141
142
143def main():
144
145 parser = optparse.OptionParser(usage="%prog [options]")
146
147 parser.add_option("-s", dest="seed", default=None,
148 help="use the specified seed to generate random data")
149 parser.add_option("-o", dest="directory", default=None,
150 help="create the database the specified directory")
151 parser.add_option("-f", dest="file", default=None,
152 help="read specification from given file; " +
153 "if not specified, read from stdin")
154
155 options, args = parser.parse_args()
156
157 if len(args) != 0:
158 parser.print_usage()
159 raise SystemExit(255)
160
161 spec = None
162 if options.file is None:
163 spec = sys.stdin.read()
164 else:
165 try:
166 specfile = open(options.file)
167 spec = specfile.read()
168 except IOError:
169 print "cannot read from file " + options.file
170 raise SystemExit(255)
171 finally:
172 specfile.close()
173
174 seed = options.seed
175 if seed is None:
176 seed = ''.join(random.sample("ABCDEFGHIJKLMNOPQRSTUVWXYZ", 4))
177
178 dbname, relations = parse(spec)
179
180 directory = options.directory
181 if directory is None:
182 directory = dbname + "_" + seed
183
184 if os.path.exists(directory):
185 print "directory " + directory + " already exists"
186 raise SystemExit(255)
187
188 os.mkdir(directory)
189
190
191 dump_schema(relations, dbname, directory)
192 dump_data(relations, directory, seed)
193
194 print "database " + dbname + " created with seed " + seed
195 print "to load in mysql, use the commands"
196 print " cd " + directory
197 print " mysql -u<user> -p < generate.sql"
198
199
200if __name__ == '__main__':
201 main()