#!/usr/bin/env python # bsmx2btsqlite - convert BeerSmith bsmx ingredients exports into BrewTarget # sqlite database # Copyright (c) 2018 David Phillips # 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. 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()