A high proportion of my research projects end up with my writing code that solves a set of test problem and spews results that eventually need to be analyzed. Once upon a time this mean parsing log files, transcribing data to a spreadsheet or statistics program and proceeding from there. In recent years I've taken to adding a database interface to the program, so that it records results (both intermediate and final) to a database. Add records of problem characteristics (as seen by the program) and parameter values (as passed to the program) and the database becomes a one-stop-shop for any data I need when it comes time to write the associated paper.
SQLite has proven to be a great choice for the back end, because it does not require a server. It understands a reasonably complete dialect of SQL and is more than sufficiently efficient for my purposes. Drivers are readily available for it. (I program in Java, and I've been having very good luck with SQLite JDBC.) As with pretty much everything I use, it is open-source.
I typically create the database outside my program, and once my program has done its thing I need to access the results stored in the database. There are a variety of client programs for SQLite. The best one I've found so far is SQLite Studio (open-source, available for multiple platforms). It has a very complete feature set and is remarkably easy to use. I recently had to merge data from multiple tables of one database into the corresponding tables of another one. SQLite Studio allowed me to open both databases and then do the merge with simple SQL queries; it handled the mechanics of "attaching" one database to the other silently in the background.
So I thought I should give SQLite, SQLite JDBC and especially SQLite Studio a shout-out.
Thursday, May 23, 2013
6 comments:
Due to intermittent spamming, comments are being moderated. If this is your first time commenting on the blog, please read the Ground Rules for Comments. In particular, if you want to ask an operations research-related question not relevant to this post, consider asking it on Operations Research Stack Exchange.
Subscribe to:
Post Comments (Atom)
Take a look on a free tool -- Valentina Studio. Amazing product! IMO this is the best manager for SQLite for all platforms. http://www.valentina-db.com/en/valentina-studio-overview
ReplyDeleteThanks for the tip! I just installed the free version, and it looks very impressive. It will take me a while to give it a full test, but it does have quite a few features, and the ability to handle both SQLite and MySQL with a single program appeals to me.
DeleteMr Rubin, I was wondering if you could help shed some light on my dilemma. I have recently updated my phone, and in the process it somehow made two sms databases. I have SQ Lite Studio, and being the beginner I am, can't seem to find out How to merge them. Would you happen to know how? I have both databases on my computer now, and can see their contents.
ReplyDeleteAndrew,
DeleteI assume that you've looked at the contents and confirmed that the older of the two databases is not a subset or backup of the newer one (else there would be no need to merge them).
I can't tell you how to merge them without knowing their structure. If the database is a single table, merging should be easy. If it has multiple tables, merging gets much more complicated.
It has 20 tables? I'm not sure... this is what I see. https://docs.google.com/file/d/0B9LpVLAINqS1T2FrSmQ0M01xazA/edit?usp=docslist_api
DeleteAndrew,
DeleteWith that many tables, it's pretty likely you're going to have a serious slog doing the merge. Unfortunately, the instructions to do the merge would be way more complicated than I can put into a comment. Merging one DB into the other (after backing it up), you want to insert records from Table X of the first DB into Table X of the second (merged) DB, while removing duplicates.
The gnarly part comes when Table X has "foreign key" fields linking to Tables Y, Z etc. You need to make sure that Tables Y, Z, ... are merged before you try to merge Table X, but that's only half the battle. If a record in Table X of the incoming DB refers to a record in Table Y (of the incoming DB) that already existed in the target DB (meaning the Table Y record of the incoming DB was a duplicate), you need to alter the foreign key field of Table X to match the ID of the Table Y record.
I have half an idea about how to do this, but it would be a blog post and a half all by itself. You might try searching for "merge databases" to see if someone has spelled this out already.