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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
#!/usr/bin/env python
# bsmx2btsqlite - convert BeerSmith bsmx ingredients exports into BrewTarget
# sqlite database
# Copyright (c) 2018 David Phillips <david@yeah.nah.nz>
# All rights reserved
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS "AS IS" AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.
# 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()
|
