KDE3.5 and other stuff

:: How to recover "lost data" in piwik database

For those who don't know, PIWIK is a free web analytics software, written in php and using mysql database.It happened recently that I lost some data and I had to go through a recovery process, using mysql binary log. So I write down how I did it here, hoping that it would beuseful for people facing similar problems.

What to do before

As our method is rather "heavy", it would be better if you could solve the problem by other means. Here are several thing you can try.

• Drop relevant archive tables and reconstruct them. Actually you can tell if this is useful if the visits can be still find in tables like piwik_log_visit. So if the data are missing from them, there is no point in trying.
• Read Piwik FAQ trouble shooting section.
• See if there isn't a similar problem reported in Piwik forum.
• If you have access log of the web sever available, try replaying log first. This is much simpler than recovering
• Ask for a help there. People there are very helpful and competent, so with a luck they may find an easier solution for you.

If none of the above works

Well, then probably you will need to use our recovery method, which will be explained in the next article. See you very soon.

:: locale.Error: unsupported locale setting (eyeD3)

Do you remember about my bash script that let me copy mp3 title tags for an entire directory? Well, somehow, if I run the script from Konsole terminal, it doesn't run, with following error messages.

Traceback (most recent call last):
  File "/usr/bin/eyeD3", line 36, in <module>
    ENCODING = locale.getpreferredencoding();
  File "/usr/lib/python2.5/locale.py", line 514, in getpreferredencoding
    setlocale(LC_CTYPE, "")
  File "/usr/lib/python2.5/locale.py", line 478, in setlocale
    return _setlocale(category, locale)
locale.Error: unsupported locale setting
</p>

But this error doesn't occur if I run the script from a xterm terminal.I haven't figured out yet what goes wrong. Stay tuned!

:: Files with names in foreign languages becomes invisible

This is probably a bug in old version of ntfs-3g, which has been already corrected,but might still interest some people using old systems.

I have a removable disk, with files whose names contain some foreign characters(accentuated letters, wide alphabets, Japanese letters and so on). There is noproblem if I connect it to a machine with recent systems. However, when I connectit to my old Kubuntu 8.04, these files become invisible. The solution I found wasextremely simple. It suffices to unmount and remount "by hand", that is it. That is, after unmounting using Konqueror,

sudo mkdir /mnt/test
sudo mount /dev/sdi1 /mnt/test

Here you can choose the mount point as you like, and replace /dev/sdi1 with thecorrect path to your media.

:: How to use ffmpeg to convert .m4a files into .mp3 keeping tags

I have been using SoundKonverter to convert audio formats. Overall, it is a very nice application. There are, however, several coveats. First of all the version thatI have on my old system has a problem with dealing with files having the character"#" in their name. This bug has been solved in more recent versions, butthere is another persistent bug that might interest you. When converting a .m4afile into mp3 format, the composer tag gets lost. Now this is not much of a problem if you convert an album dedicated to a single composer, but it may become really annoying if you convert an album in which each title has adifferent composer. One solution I found was the script here . This worksif your .m4a is in aac codec. However, if you have a lossless .m4a file, thenyou will end up with empty mp3 files. The problem is that the lossless .m4afiles use alac codec and not aac codec, so that faad can't decode. If you try faad from command line, you see better, you get the following error:

Unable to find correct AAC sound track in the MP4 file.

So what can I do to decode .m4a file in alac? Well, a relatively recent versionof ffmpeg should do the job, but what about thetags? It turns out that now ffmpeg takes care of the tags! So, I use thefollowing script to convert my .m4a files into mp3.

#!/bin/bash
# converts m4a files to mp3
# song tags should be preserved
while read m4afile; do
mp3file=$(echo "$m4afile" | sed s/\.m4a/.mp3/g)
ffmpeg  -i "$m4afile" -ab 128k  "$mp3file" < /dev/null
done < <(find . -iname '*.m4a' -or -iname '*.M4A')

Call this file, say, m4atomp3, put it in your path, go to the top level directorywhere your m4a files are, and launch the command. Then it will convert all yourm4a files in subdirectories into mp3, keeping the original tags. Well, it converts "year" tag to "TDRL" tag, there is a workaround for this, but I thinkthe article is long enough for the day. By the way, the actual script I use isas follows:

#!/bin/bash
# converts m4a files to mp3
# song tags should be preserved

export LD_LIBRARY_PATH=~/soft/src/ffmpegwebm/dist/lib
while read m4afile; do
mp3file=$(echo "$m4afile" | sed s/\.m4a/.mp3/g)
~/soft/src/ffmpegwebm/dist/ffmpeg/ffmpeg  -i "$m4afile" "$mp3file" < /dev/null
done < <(find . -iname '*.m4a' -or -iname '*.M4A')

Here, ~/soft/src/ffmpegwebm/dist/ffmpeg/ is the path to a ffmpeg that I compiledfrom the source, and ~/soft/src/ffmpegwebm/dist/lib is the path to the directorywhere I installed the related shared object files. I hope to come back to thistopic later.

:: 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!