KDE3.5 and other stuff

:: How to merge OpenNumismat databases

OpenNumismat is a niceapplication that allows us to organize our coin collections. It also comes withsome databases, so that we don't have to create them from zero by hand. So far so good. But unfortunately, the current version (as of August 19th, 2014) doesn'tallow us to merge several databases.

So why is this a problem? Well, I would like to see the collection of standardeuro coins and the commemorative euro coins at the same time. Now, they onlyoffer for the download separate databases for standard coins and euro coins.This means that if I encounter a 2 euro coin that I have never seen, I will haveto consult the two databases, and currently the only way to do is to open the twoone after another (and when I open the second, I close the first).

Before I can explain how I managed to merge databases, I need to describe what isin the OpenNumismat databases. An OpenNumismat database contains 10 tables of which I still haven't figured out use of all. Presumably the table sqlite_sequencecontains crucial information for merging tables, but we can ignore it, at leastfor the sake of merging standard euro coins database and commemorative euro coinsdatabase. We are particularly interested in three databases; coins, images andphotos. Basically the databases 'coins' contain everything about coins. The fields that concern us are 'id','image','obverseimg','reverseimg','edgeimg','photo1','photo2','photo3' and 'phto4'. The two other tables store the images,and the only field that concernes us is the 'id'. The entries of all these fieldsare integers. The entry 'image' in the table 'coins' corresponds to the entry 'id'in the table 'image', and the entries 'obverseimg','reverseimg','edgeimg','photo1','photo2','photo3' and 'phto4' correspond to the entry 'id' in the tablephoto. Fortunately for us, there is no overlap between the id numbers of the tables 'coins' and 'photos' of two databases, which allows us to simply mergethe tables 'coins' and 'photos', but if you happen to have databases with overlapping id in tables 'coins' and 'photos', you should be able to work outa method similar to what I will describe below.

So here comes the fun. Probably you should take a copy of your databasesbefore trying to merge the databases, unless you are trying to merge freshlydownloaded databases. First let's make sure there is no overwrapping id's ofcoins and photos. You can easily visualize this using a software like SQlitebrowser. In our case, we have id's in table 'coins' ranging from1 to 138 in the database for commemorative coins, from 282 to 561 in the databse for standard coins. Similarly there is no problem for the id's ofphotos. However, we see that the id's of images in both tables start from 1!Which means overwrap, thus conflict. So to avoid this, let's modify the id of images in the database for commemorative coins. Let's create a text filecontaining the sqlite commands first. Here is a bash script that accomplishesthe task.

#!/bin/bash
for i in {1..138}
# replace 138 with the highest id
do
j=$(( 1000+$i ))
echo "UPDATE coins SET image=$j WHERE rowid=$i;"
echo "UPDATE images SET id=$j WHERE rowid=$i;"
done

Let's say, we call this file script. Run this and redirect the output to afile, let's call it sqlcommands. So we type in a terminal

./script > sqlcommands

This generates a text file that looks like

UPDATE coins SET image=1001 WHERE rowid=1;
UPDATE images SET id=1001 WHERE rowid=1;
UPDATE coins SET image=1002 WHERE rowid=2;
UPDATE images SET id=1002 WHERE rowid=2;
UPDATE coins SET image=1003 WHERE rowid=3;
UPDATE images SET id=1003 WHERE rowid=3;
UPDATE coins SET image=1004 WHERE rowid=4;
UPDATE images SET id=1004 WHERE rowid=4;
...

Now, let's run these sqlite commands on the database for commemorative coins(called here commemorative_eu.db).

sqlite3 commemorative_eu.db < sqlcommands

If you wish, you can open the modified database with OpenNumismat. Nothing shouldhave changed. If in your databases to merge, there are overwraps of id's of coins or photos, you will have to do similar modifications, and don't forgetall fields in the 'coins' database that refer to photos' id's.

Now that we have prepared our databases for merging, we will merge themusing sqlite3 command line.

sqlite3 commemorative_eu.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>  attach database 'std_regular_eu.db' as 'external';
sqlite> insert into coins select * from external.coins;
sqlite> insert into photos  select * from external.photos;
sqlite> insert into images  select * from external.images;
sqlite> detach external
sqlite> .exit

Now, open again commemorative_eu.dbwith OpenNumismat, and you will see that the the new database contains also standard coins. Have fun!