View all essays

Koken database repair

Albums disappearing, albums don't sort properly, albums jumping from one Set to another: Your koken database is confused. Easy to fix if you have a MySQL editor like phpMyAdmin or Navicat.

Open the table koken_albums and sort the table on the following columns:
level, left_id, right_id and visibility

visibility
visibility = 0: the album is public
visibility = 1: the album is unlisted
visibility = 2: the album is private

The same logic applies applies to the content tables for individual photos in the koken database

listed

The table koken_albums also has a column listed. What it does is unclear for me, most likely it's a relic from the past and overruled by visibility which supports private, a new function of more recent koken releases.

level is the level of the album/set in koken's collection tree 

level = 1: the top level either Public, unlisted or private in the collection tree
Any album directly under Public has visibility = 0 and level = 1 
level = 2: A set or album inside a Set in the top levels (public, unlisted or private) of the collection tree. For instance a Set in the unlisted tree has visibilty = 1 and level = 1. Albums within that Set have visibility = 1 and level = 2.
level = 3: etc etc

Grouping and sorting

It gets a little complicated with sorting and grouping of albums and sets. Differentiating between Sets and Albums and sorting and grouping is handled by left_id and right_id. 

For instance, three (public) albums are sorted like this (id could be any number and is controlled by your koken install)

id, left_id, right_id
21, 1,       2
6,  3,       4
3,  5,       6

A set could be

id, level, left_id, right_id
9,  1,     3,       10

Albums within that set are sorted like this:

id, level, left_id, right_id
35, 2,     4,       5
7,  2,     6,       7
10, 2,     8,       9

level = 2 because the albums are part of the set

left_id of the albums within the Set starts with the left_id + 1 of the corresponding Set, in this example left_id = 4. The left_id and the right_id of the Set should span the left_id and right_id's of the albums within that set.

If you move albums and or sets around in Koken the left_id and right_id is changed by koken and the resulting database structure should be correct. Somehow my koken install managed to confuse itself. Maybe due to some bugs in the past which never showed it ugly face in the past. You can fix the logic yourself with any MySQL database editor! After your done save the database table, clear the system cache of koken and refresh your browser.

If you make a mistake and the collection tree is totally messed up, don't panic and try again. The albums and set don't disappear if you don't delete records in the koken_albums table.

The logic above is what I discovered with trial and error. I could be wrong, it worked for me. A good backup is a very good idea before you start hacking your database!

Or:
Run this query:

UPDATE koken_albums a,
(SELECT @i := 0) m SET level = 1,
a.left_id = @i := @i + 1,
a.right_id = @i := @i + 1;

  • Logitech Boom speaker repair

    The Logitech Squeezebox Boom has a major fault. The rubber of the woofers is falling apart after a few years:What you need: New speaker rings R31C from speakerrepairshop Glue A mini hot air gun. Most gas operated soldering irons have a special tip which…

    Koken export original images

    Koken organizes photos in Albums (and Sets), The original uploaded photos are stored in an obscure folder structure like:/YourPathToKoken/koken/storage/originals/g2/5n/originalfilename. jpgEvery uploaded photo has two unique subfolders in the koken…