· 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 else:
53 sys.stdout.write(".")
54 used_keys.append(this_key)
55 yield row
56
57
58def prod(items):
59 return reduce(int.__mul__, items, 1)
60
61
62def projection(t, indexes):
63 return tuple(t[i] for i in indexes)
64
65
66def parse(spec):
67 ## Specifications are in the format:
68 ## # comment
69 ## dbname <name>
70 ## (domain <name> <count>)+
71 ## (relation <name> <count> (<field>:<domain>(:b?k?)?)+)+
72
73 lines = [str.strip(line) for line in spec.split("\n")
74 if not line.startswith("#")]
75
76 dbns = [l for l in lines if l.startswith("dbname ")]
77 doms = [l for l in lines if l.startswith("domain ")]
78 rels = [l for l in lines if l.startswith("relation ")]
79
80 assert len(dbns) == 1, "no database name specified"
81 dbname = dbns[0].split()[1]
82
83 domains_dict = {}
84 for d in doms:
85 _, name, count = d.split()
86 domains_dict[name] = Domain(name, int(count))
87
88 relations = []
89 for r in rels:
90 _, name, count, fdesc = r.split(None, 3)
91 fields = []
92 domains = []
93 key = []
94 bindings = []
95 i = 0
96 for f in fdesc.split():
97 if f.count(":") == 2:
98 f, flags = f.rsplit(":", 1)
99 if "k" in flags:
100 key.append(i)
101 if "b" in flags:
102 bindings.append(i)
103 field, domain = f.split(":")
104 fields.append(field)
105 domains.append(domains_dict[domain])
106 i += 1
107 assert len(key) != 0
108 relations.append(Relation(name, int(count), fields,
109 domains, key, bindings))
110
111 assert len(relations) != 0
112 assert dbname is not None
113
114 return dbname, relations
115
116
117def dump_schema(relations, dbname, directory):
118 sql = []
119 sql.append("DROP DATABASE IF EXISTS " + dbname)
120 sql.append("CREATE DATABASE " + dbname)
121 sql.append("USE " + dbname)
122 for r in relations:
123 create = "CREATE TABLE " + r.name + "("
124 create += ", ".join([f + " VARCHAR(255)" for f in r.fields])
125 create += ", PRIMARY KEY ("
126 create += ", ".join(projection(r.fields, r.key))
127 create += "))"
128 sql.append(create)
129 sql.append("LOAD DATA LOCAL INFILE 'rel_" + r.name + ".csv'" +
130 " INTO TABLE " + r.name +
131 " FIELDS TERMINATED BY ','")
132 batchfile = open(directory + "/generate.sql", "w")
133 print >> batchfile, ";\n".join(sql)
134
135
136def dump_data(relations, directory, seed=None):
137 for r in relations:
138 print "creating relation " + r.name + "..."
139 filename = directory + "/rel_" + r.name + ".csv"
140 writer = csv.writer(open(filename, "w"))
141 writer.writerows(r.rows(seed))
142 print "done."
143
144
145def main():
146
147 parser = optparse.OptionParser(usage="%prog [options]")
148
149 parser.add_option("-s", dest="seed", default=None,
150 help="use the specified seed to generate random data")
151 parser.add_option("-o", dest="directory", default=None,
152 help="create the database the specified directory")
153 parser.add_option("-f", dest="file", default=None,
154 help="read specification from given file; " +
155 "if not specified, read from stdin")
156
157 options, args = parser.parse_args()
158
159 if len(args) != 0:
160 parser.print_usage()
161 raise SystemExit(255)
162
163 spec = None
164 if options.file is None:
165 spec = sys.stdin.read()
166 else:
167 try:
168 specfile = open(options.file)
169 spec = specfile.read()
170 except IOError:
171 print "cannot read from file " + options.file
172 raise SystemExit(255)
173 finally:
174 specfile.close()
175
176 seed = options.seed
177 if seed is None:
178 seed = ''.join(random.sample("ABCDEFGHIJKLMNOPQRSTUVWXYZ", 4))
179
180 dbname, relations = parse(spec)
181
182 directory = options.directory
183 if directory is None:
184 directory = dbname + "_" + seed
185
186 if os.path.exists(directory):
187 print "directory " + directory + " already exists"
188 raise SystemExit(255)
189
190 os.mkdir(directory)
191
192
193 dump_schema(relations, dbname, directory)
194 dump_data(relations, directory, seed)
195
196 print "database " + dbname + " created with seed " + seed
197 print "to load in mysql, use the commands"
198 print " cd " + directory
199 print " mysql -u<user> -p < generate.sql"
200
201
202if __name__ == '__main__':
203 main()