From 9606df3ddc5da274bee5256666cc188b2ed50a4e Mon Sep 17 00:00:00 2001 From: David Phillips Date: Sun, 18 Nov 2018 22:11:25 +1300 Subject: Dump semi-working product Still has to operate on live files, probably brewtarget wants all of the tables present in a mergee database --- bsmx2btsqlite.py | 202 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 202 insertions(+) create mode 100755 bsmx2btsqlite.py (limited to 'bsmx2btsqlite.py') diff --git a/bsmx2btsqlite.py b/bsmx2btsqlite.py new file mode 100755 index 0000000..25292f6 --- /dev/null +++ b/bsmx2btsqlite.py @@ -0,0 +1,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. 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 = """ + + + + +]>""" + + +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() -- cgit v1.1