SQLite3 to Tap Forms

[T.L. Ford]

I put together a script to make the conversion from SQLite3 to Tap Forms easier. It is most useful for databases with multiple tables and many fields.

This BASH script is very rough – consider it BETA.

Information/download can be found here:
http://www.Cattail.Nu/tap_forms/tap_forms_sqlite3_port/index.html

Brendan:
Documentation: addNewFieldNamedWithType needs other types
Documentation: createNewFormNamed

Useful functionality I couldn’t find:
document.importCSV(fileURL, form, createFieldsIfMissing)
form.deleteFieldNamed(‘field’)
fld.required (and properties for other options)

[Sam Moffatt]

There is a command called pbcopy that will accept input from the CLI and dump something on the clipboard. I use it to reformat text from eBay listings into NVP and dump on my clipboard to paste into Tap Forms table fields. Could skip a round trip to TextEdit in the middle there for you.

A cool feature would be a Javascript helper to import CSV files from disk to skip the import wizard.

I also haven’t seen Hungarian notation in so many years! Blast from the past.

[T.L. Ford]

I still use Hungarian notation outside of Tap Forms. It makes database programming very easy; I don’t have to think about whether I need to enclose parameters in ”’s or not. (That odd “BOOK” table was a testing table of “randomly grabbed create sql from the internet”.)

I might add pbcopy as an option in a update – likely will start with it off as I don’t like things to auto-use the clipboard.

Yes – a JavaScript helper for importing CSV is in my list of desired useful functionality above. :)

[Brendan]

I wonder if you could use the Utils.getTextFromUrl() function to import your data? You’d have to do the parsing within JavaScript of course, but it may be doable. Especially since I added the script folder access on the General Preferences panel. You can reference any files from that location.

The field types you can use in the addNewFieldNamedWithType command are all the field type names in lowercase with underscores between the words. Except for Date & Time, which is just date_time. But if you export a CSV file with field type tags enabled, you’ll see them in the header row.

There are a ton of properties on the Field object. In fact there’s about 100 or more. Some persistent, some non-persistent. Not sure if I should expose them all.

[Sam Moffatt]

If you outputted JSON instead of CSV, you could deserialise it directly via the built in JSON parser. Instead of using bash, I’d suggest something like Python that will output a properly formed JSON document for you. I’d still want to chunk it so you don’t had too big a file but that’s obtainable too. I’d also suggest writing direct to CouchDB if that’s an option as well but that’s a level more advanced.

Re: pbcoy; You don’t need to embed it in a script, it can just be another line in the terminal:

cat build_forms_js.txt | pbcopy

[T.L. Ford]

I considered Utils.getTextFromUrl() and parsing the text, but I was really hoping for a “no-code, just use this function” solution as the code has to exist for the Tap Forms menu option. Eventually the plan is to loop through fields and format the date fields in a custom SQL statement to create the CSV in a consistent format so it can be correctly/easily parsed on import.

I put a note on the page about using cat build_forms_js.txt | pbcopy.

The comment for addNewFieldNamedWithType was for a documentation update. I found date_time with some playing. SQLite3 data types are a strange application of prioritized affinities, so I have all I need for the basic data types.

JSON/Python – neither was available (on my older Mac OS X, so may be available on newer versions) without some additional installation (yes, I have things). My quick look for “sqlite3 to json” didn’t show a native export. There are many ways to tackle this data migration (my solution of choice would have been PERL), but I was going for “no additional installs; path of least resistance; easiest solution for non-programmers; source visibility for security”, which limited to “bash/sed/awk”, no gawk, no real script languages.

Forcing the Terminal use was debated. It tends to be hard/confusing for people who haven’t ever used it, but I secretly want everyone to know how to use it because it’s just so useful for so many things…

I considered building the JSON (I certainly could), but opted not to for “programming time vs. value”; I’m not sure how many people will actually need/use this script, so another day of coding isn’t justified yet. More code is pending “people are using this and need more functionality” OR “someone who wants to do it writes it for me” (I’m always happy with the later.).

– T

[Sam Moffatt]

Python has shipped with MacOS X for an exceedingly long time, I think it might have been there in the initial releases, I have a recollection of it being there in Mac OS X 10.4 at least and available all the way to Catalina and it seems Big Sur. It also has had a JSON module for a while. The biggest problem is generally that the Python versions Apple ships are old. Apple is warning that it’ll remove it in the future release but it’s still there for the time being.

I did a quick search for “python sqlite json export” and the first result was a StackOverflow post with an elegant example but unfortunately for PostgreSQL. The third option was a blog post on “Generating JSON Documents From SQLite Databases in Python” which is actually pretty succinct too but generates not JSON but a Python tuple.

I did a quick modification to make it explicitly output JSON and I didn’t have a sqlite3 database of my own handy, so I borrowed a relatively empty Tap Forms document to test with. I ran this script on High Sierra, Mojave and Catalina without any issues.

As an extra thing, I did a quick addition to also dump the schema as well. Probably write it out to different files but this is just a quick example:

#!/bin/python
import json
import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

connection = sqlite3.connect("db.sqlite3")
connection.row_factory = dict_factory
cursor = connection.cursor()

# Grab the schema of docs
cursor.execute("PRAGMA table_info('docs')")
results = cursor.fetchall()
print json.dumps(results, indent=4)

# Grab the contents of docs
cursor.execute("select * from docs")
results = cursor.fetchall()
print json.dumps(results, indent=4)

connection.close()

Sample Output:

silversaviour:db-5c59efda9b344f77a4ec339ea468fdab.cblite2 pasamio$ python dump.py 
[
    {
        "name": "doc_id", 
        "cid": 0, 
        "dflt_value": null, 
        "notnull": 0, 
        "pk": 1, 
        "type": "INTEGER"
    }, 
    {
        "name": "docid", 
        "cid": 1, 
        "dflt_value": null, 
        "notnull": 1, 
        "pk": 0, 
        "type": "TEXT"
    }, 
    {
        "name": "expiry_timestamp", 
        "cid": 2, 
        "dflt_value": null, 
        "notnull": 0, 
        "pk": 0, 
        "type": "INTEGER"
    }
]
[
    {
        "docid": "rec-e82e27a32f984a8c92caab13591f0a52", 
        "doc_id": 1, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-790840877753424395d9eb37bdfcdfa9", 
        "doc_id": 2, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-749bd3b4775347ada2e8168d680f5228", 
        "doc_id": 3, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0059f8f1688c4bd69140a296ce18930a", 
        "doc_id": 4, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "pik-59aa950c01ba41cc9e7014727d3019ee", 
        "doc_id": 5, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1cab945d81a94c93bf8cf7128ac68601", 
        "doc_id": 6, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ff84c90f17f64c388240fa259ee72d05", 
        "doc_id": 7, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-4daa3b4d14e74eca83870378701c798a", 
        "doc_id": 8, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-d124f2d5fb334385b2e68ac7cc1ad854", 
        "doc_id": 9, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-3ec681983f0a48c4b8ebe6289096ec40", 
        "doc_id": 10, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "frm-cdcfe7a8faed445daa5bcc530deba43a", 
        "doc_id": 11, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "-uncategorized-", 
        "doc_id": 12, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-06e0be082efa4a69b985b02f01c93e95", 
        "doc_id": 13, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-300e6a1188da4aafab279ad651560d4e", 
        "doc_id": 14, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f4c98ab5dd1047d0be24100a764cff3f", 
        "doc_id": 15, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-30786d9db4c249858405ce784d2930ce", 
        "doc_id": 16, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-4ae324b620ca49fda0f0f32c1974a150", 
        "doc_id": 17, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-28809156390b4db186d6b2d64d255b9b", 
        "doc_id": 18, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f076eaa7bd8c4b70bf2e5e4ea4e1d49c", 
        "doc_id": 19, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-97deda5465914308b7a7c5505a6aa3de", 
        "doc_id": 20, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-5281c855bc894e5989b1267bd23de0c3", 
        "doc_id": 21, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c64f201bf261499dbc9fdac6386b1246", 
        "doc_id": 22, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-4105ddd8730940e5a8475916732d6720", 
        "doc_id": 23, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-902e9ad25f30405a80850a483c04ebcb", 
        "doc_id": 24, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-fa075bcc9d6f474b84416ed28a74a563", 
        "doc_id": 25, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-b20dc28f75e046fa8dec24a61091cdfe", 
        "doc_id": 26, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a60af3c801db4bb983429db6f6168421", 
        "doc_id": 27, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-27787e2a762d4fbc9b03fef2c92898fa", 
        "doc_id": 28, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-3ea05f2aa83e465ab736e1d3de25345a", 
        "doc_id": 29, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e055d4a57d5646e1bb20fc35875b8da0", 
        "doc_id": 30, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-8dad1bce0bdd4d03a58d4a649b857ad9", 
        "doc_id": 31, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-34f7a17e08144979811de53023184fa3", 
        "doc_id": 32, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-0996ded4e01346c7a571f8743b9f3ae2", 
        "doc_id": 33, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-530e2513e4234c84a177347115ee580c", 
        "doc_id": 34, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "fld-8e367aaac2cf46128bdb1bebf9fe3ca4", 
        "doc_id": 35, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e8b9c23ea99e457f994745764eb33108", 
        "doc_id": 36, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2d69dc37406944f8b0dd665fdb122377", 
        "doc_id": 37, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f393cd0bab8348abbcfbafa58e17a2b3", 
        "doc_id": 38, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1b44ff88a7ad4dd7976fbf3c90fa2aac", 
        "doc_id": 39, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-e34bb38f3fc04c85add78ea9c6fbdd4c", 
        "doc_id": 40, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-cff70ff1a0d3499a860bee1ce13b1132", 
        "doc_id": 41, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-792c3785c2d6474a96c39a43bfdbcd16", 
        "doc_id": 42, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-d3273f0018034b728882d508282e9401", 
        "doc_id": 43, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c0b6902289fa4c3790e971b59e689a14", 
        "doc_id": 44, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-94c9882b0c9747c0be8e6389b946a223", 
        "doc_id": 45, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a5d71a75f15144a4807417765973a3b3", 
        "doc_id": 46, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0efc63e234644ddfbd51dde6df095e5c", 
        "doc_id": 47, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ebe591381a7541829a830ace10f38d69", 
        "doc_id": 48, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-bec8773fb11f42cdb1b085daca95a567", 
        "doc_id": 49, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2abe8df081d04d66b70ab7dec0b6104e", 
        "doc_id": 50, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-dd471061f9b34ec987aa8bcf02eb1925", 
        "doc_id": 51, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-666c5a32bb1a4729b94c945699c88663", 
        "doc_id": 52, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-70a095c188f54f94a25a864f1a87c253", 
        "doc_id": 53, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-41c2c5ce3c55426e97bba6666f149b84", 
        "doc_id": 54, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-590625056ead4ae6a1be12c11babc473", 
        "doc_id": 55, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-f8880c1442cd41138864e80e9221d070", 
        "doc_id": 56, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c345fb822d9746eb8255b81d650a6efc", 
        "doc_id": 57, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-943c3e3c285e491383dd9acb8f59e14b", 
        "doc_id": 58, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0273c42a3e0b41a5be230ff446a9697b", 
        "doc_id": 59, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-5ea6fc318ad6402993e585e31b479930", 
        "doc_id": 60, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-7365a14bdbda4d85a3d735d22a901b3d", 
        "doc_id": 61, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c35b278a5d194fe5bd84bf89a25d1801", 
        "doc_id": 62, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-2e016db126b94d489e2a295c069f9458", 
        "doc_id": 63, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-0a7621a6ad1f4d7097dc6cb9d3119a37", 
        "doc_id": 64, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-364dd2a97aa040bfb43ee5bfa4c86e04", 
        "doc_id": 65, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-98ac6534573744a58f6d58826407d185", 
        "doc_id": 66, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-ed26974d43f643b08832d09b87b65237", 
        "doc_id": 67, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-22301abadf184fa4bc6dc9787c554e5d", 
        "doc_id": 68, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-c709985047d843909bcf260c2b45de78", 
        "doc_id": 69, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-934b2773975e4635ae00e1179bcb3fcc", 
        "doc_id": 70, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-9fc093e31c7247f0a161a81a44645ac8", 
        "doc_id": 71, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-a2fdd9cc7f4141b6955189564dabef45", 
        "doc_id": 72, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-8641f1651ee043ca825cf0d5e356fe70", 
        "doc_id": 73, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-59f14c6b87b04d0bbcef706f7e4325bc", 
        "doc_id": 74, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-8819b49fbd2f437481bba04f971403d8", 
        "doc_id": 75, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-81cef75d33cf4624a1232cf3e211f53b", 
        "doc_id": 76, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-67980b57facc461f8e3891c76cf3eb27", 
        "doc_id": 77, 
        "expiry_timestamp": null
    }, 
    {
        "docid": "rec-1eaf296525614e668ad798723dfe75e2", 
        "doc_id": 78, 
        "expiry_timestamp": null
    }
]

[T.L. Ford]

Awesome! I could swear I had to do an install last time I played with python, but I may have been using a feature that wasn’t supported on the version I had? I don’t recall. It’s been a while.

– T

[Sam Moffatt]

Entirely possible something you wanted wasn’t there and you had to install it. If you want to do data science stuff or install third party packages, generally the first thing is to install a newer version. Apple still ship Python 2.7 by default which ran out of support a while back and while they ship Python 3, generally it’s a release from earlier in the year that MacOS was released (Catalina has 3.7.3 from March 2019 for example, Big Sur seems to ship 3.8.3 from May this year). If you’re on an earlier Mac then obviously you’ll have a relatively recent Python build but stuck in that year. Depending on what feature you’re after, you might need to upgrade. Python gets icky with packages at time as well so having a clean non-system install can help for different versions of things.

In any case for this task it’s reasonably concise to extract out the data :)

[T.L. Ford]

I modified your script to accept an argument, to dump all of the tables in the data file, and to split the schema and data JSON into separate files for each table. Python is not in my personal language-set so there’s no argument verification, niceties.

I couldn’t get Tap Forms to import the JSON consistently (mostly returned undefined, even for one that previously worked). This could be a firewall/security issue on my machine for the file://. The JSON would still need the JavaScript to create the forms and parse the data into those forms.

#!/usr/bin/python
import json
import sqlite3
import sys

arg = sys.argv[1]

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

connection = sqlite3.connect(arg)
connection.row_factory = dict_factory
cursor = connection.cursor()

cursor.execute("select name from sqlite_master where type = 'table'")
tbls = cursor.fetchall()

for value in tbls:
	tbl=value.get('name')
	print "exporting",tbl

	# Grab the schema of docs
	cursor.execute("PRAGMA table_info('" + tbl + "')")
	results = cursor.fetchall()
	with open(tbl + '_schema.json', 'w') as outfile:
		json.dump(results, outfile, indent=4)

	# Grab the contents of docs
	cursor.execute("select * from "+tbl)
	results = cursor.fetchall()
	with open(tbl + '.json', 'w') as outfile:
		json.dump(results, outfile, indent=4)

connection.close()

As for Python installs… after some digging, I found several with varying versions. I routinely abuse my computer so this is unsurprising. I didn’t have a /bin/python.

[Sam Moffatt]

There should be a Python 2.7 install and a Python 3 install contemporaneous with your MacOS release. The /bin/python was copy paste from the OP, /usr/bin/python or /usr/bin/python3 should be the default paths though in my example I used python directly to execute it so I didn’t really hit that issue.

Since Tap Forms is sandboxed, you need to make sure you any paths you use are accessible in the sandbox. If you’ve updated to the latest Tap Forms, there is a script folder access you can use to make sure that path is accessible inside of TF. Valid JSON should parse, I’ve never really had an issue with that so file access limits via sandbox is the only thing I can think of. That said a good chunk of my JSON importing that I do is by putting it into a plain text notes field. If you’re importing particularly large files I could see some sort of memory safeguard kicking in but I’ve not personally seen that nor can I find anyone mentioning it from a quick Google search.

[T.L. Ford]

The JSON files are all small (old data set, with few records). It worked on one of the files, but not the others and then stopped working on the original. I’ll circle back around to the problem and play with it more eventually. Random other trivia when you mentioned pasting JSON into a note field: I started writing a script that generates action notices that have javascript in a hidden field to link back to the original record that generated the action notice, and then on the action form, buttons to run the javascript in the field to either open the correct record or perform a specific action on the record. I had it working but then broke it and haven’t circled back to it again yet. So many fun things to do and play with!!!

[Sam Moffatt]

I think I saw something similar a while back with a web pull that gave me an invalid value and I think it came good after a restart of TF. Wasn’t able to reliably reproduce the issue though.

[Author]

Posts