1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
#!/usr/bin/env python
# FIXME/WARN/NOTE: This software is pretty darned janky at the moment. It
# was written to work once with a file from the website of Gladfield Malts.
# I made it public just in case anyone else is stuck out there and doesn't
# want to do a bunch of manual data entry. I hope you find it useful, but
# don't be surprised if it doesn't do what you want. It currently only
# supports grains as ingredients, and what I presume to be a narrow view
# of the bsmx format (Selection).
#
# As it stands, usage is limited to working on the main db itself. First make
# a backup of your database (you have been warned) and then something like:
#
# ./bsmx2btsqlite.py foo.bsmx ~/.config/brewtarget/database.sqlite
#
# You should see output like:
#
# Item count: 33
# Added grain Gladfield Ale Malt
# Added grain Gladfield American Ale Malt
# Added grain Gladfield Aurora Malt
# Added grain Gladfield Biscuit Malt
# Added grain Gladfield Brown Malt
# Added grain Gladfield Chocolate Rye Malt
# Added grain Gladfield Crystal Rye Malt
# Added grain Gladfield Dark Chocolate Malt
# Added grain Gladfield Dark Crystal Malt
# Added grain Gladfield Eclipse Wheat
# Added grain Gladfield German Pilsner Malt
# Added grain Gladfield Gladiator Malt
# Added grain Gladfield Lager Light
# Added grain Gladfield Light Chocolate Malt
# Added grain Gladfield Light Crystal Malt
# Added grain Gladfield Manuka Smoked Malt
# Added grain Gladfield Medium Crystal Malt
# Added grain Gladfield Munich Malt
# Added grain Gladfield Organic Pilsner
# Added grain Gladfield Pilsner Malt
# Added grain Gladfield Red Back Malt
# Added grain Gladfield Red Back Wheat
# Added grain Gladfield Roast Barley
# Added grain Gladfield Roasted Wheat
# Added grain Gladfield Rye Malt
# Added grain Gladfield Shepherds Delight Malt
# Added grain Gladfield Sour Grapes Malt
# Added grain Gladfield Supernova Malt
# Added grain Gladfield Toffee Malt
# Added grain Gladfield Vienna Malt
# Added grain Gladfield Wheat Malt
# Added grain Harraway's Rolled Oats
# Added grain Harraways Rolled Barley
import sys
import sqlite3
import xml.etree.ElementTree
def getFirstTagNamed(parent, name):
"""
Returns the first child node of `parent` that has the tag name `name`
"""
for t in list(parent):
if t.tag == name:
return t
def getTextFirstTagNamed(parent, name):
"""
Returns the text content of the first child node of `parent` that has the
tag name `name`, or an empty string if it has no text content
"""
t = getFirstTagNamed(parent, name)
if t.text is None:
return ""
else:
return t.text
def processGrain(g, dbc):
"""
Run a query on dbc to add an ingredient for the node g
"""
mappings = {
"name": "F_G_NAME",
"origin": "F_G_ORIGIN",
"supplier": "F_G_SUPPLIER",
"color": "F_G_COLOR",
"yield": "F_G_YIELD",
"coarse_fine_diff": "F_G_COARSE_FINE_DIFF",
"moisture": "F_G_MOISTURE",
"diastatic_power": "F_G_DIASTATIC_POWER",
"protein": "F_G_PROTEIN",
"max_in_batch": "F_G_MAX_IN_BATCH",
"add_after_boil": "F_G_ADD_AFTER_BOIL",
"recommend_mash": "F_G_RECOMMEND_MASH",
"is_mashed": "F_G_RECOMMEND_MASH", # FIXME? This is the same source as as recommend_mash
"notes": "F_G_NOTES",
}
assert(g.tag == "Grain")
fields = ", ".join(mappings.keys())
placeholders = ("?, " * (len(mappings) - 1)) + "?"
statement = "INSERT INTO fermentable("+fields+") VALUES("+placeholders+")"
values = [getTextFirstTagNamed(g, mappings[x]) for x in mappings.keys()]
dbc.execute(statement, values)
print ("Added grain %s" % getTextFirstTagNamed(g, mappings["name"]))
def processData(d, dbc):
"""
Run queries on cursor dbc to insert data for all of the child nodes of d,
where each child is an ingredient tag, e.g. <Grain> etc
"""
handlers = {
"Grain": processGrain
}
for t in list(d):
# FIXME make non-case-sensitive
if t.tag in handlers.keys():
handlers[t.tag](t, dbc)
else:
raise Exception("Processing of " + t.tag + " is not implemented")
# unused at the moment
#ferm_schema = """
#CREATE TABLE fermentable(
# id integer PRIMARY KEY autoincrement,
# -- BeerXML properties
# name varchar(256) not null DEFAULT '',
# ftype varchar(32) DEFAULT 'Grain',
# amount real DEFAULT 0.0,
# yield real DEFAULT 0.0,
# color real DEFAULT 0.0,
# add_after_boil boolean DEFAULT 0,
# origin varchar(32) DEFAULT '',
# supplier varchar(256) DEFAULT '',
# notes text DEFAULT '',
# coarse_fine_diff real DEFAULT 0.0,
# moisture real DEFAULT 0.0,
# diastatic_power real DEFAULT 0.0,
# protein real DEFAULT 0.0,
# max_in_batch real DEFAULT 100.0,
# recommend_mash boolean DEFAULT 0,
# is_mashed boolean DEFAULT 0,
# ibu_gal_per_lb real DEFAULT 0.0,
# -- Display stuff
# display_unit integer DEFAULT -1,
# display_scale integer DEFAULT -1,
# -- meta data
# deleted boolean DEFAULT 0,
# display boolean DEFAULT 1,
# folder varchar(256) DEFAULT ''
#)
#"""
# FIXME dirty hack. This needs farming out to some HTML parser which understands
# these entities. YMMV, you probably need to add some of these depending on your
# bsmx file you're trying to import
entities = """
<!DOCTYPE doctypeName [
<!ENTITY deg "°">
<!ENTITY rsquo """>
<!ENTITY lsquo """>
<!ENTITY ndash "-">
]>"""
if __name__ == "__main__":
if len(sys.argv) < 3:
print("Syntax: %s source.bsmx target.sqlite" % (sys.argv[0]))
sys.exit(1)
source_file = sys.argv[1]
sqlite_file = sys.argv[2]
# slurp here is related to entities hack
with open(source_file) as f:
x = f.read()
# there it is
e = xml.etree.ElementTree.fromstring(entities + x)
db = sqlite3.connect(sqlite_file)
dbc = db.cursor()
# Removed temporarily. Operate instead on the file directly
# Longer-term intention is to pump out standalone dbs that can be merged in by
# brewtarget itself
#dbc.execute(ferm_schema)
assert(e.tag == "Selections")
for t in e:
if t.tag == "Size":
print("Item count: " + t.text)
elif t.tag == "Data":
processData(t, dbc)
else:
# FIXME silently passes on remainder of tags - any other useful info?
pass
dbc.close()
db.commit()
db.close()
|
