#!/usr/bin/python """ Script to export Photos from Digikam to NGG Copyright (C) 2008 Andreas Goelzer This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see . This program exports pictures from your DigiKam (http://www.digikam.org/) database to a NGG (http://alexrabe.boelinger.com/category/nextgengallery/) database. It is at the moment highly experimental and in a works-for-me-right-now state. Be really really careful if trying to use it, it will - determine which photos are tagged "public" - upload them to a dir, kill everything in the target dir not tagged public - rewrite the remote database - automatically create thumbnails along the way I don't know much about databases, so this code is quite inefficient. It doesn't pose a big problem for my ~5k local, 100 remote photos, though Once again, it *will* erase the remote directory, and possibly more. NO WARRANTY! see http://andreas.goelzer.de/synchronizing-digikam-and-ngg for updates. """ import sqlite3; import os; import sys; import re; print "Calculating new remote DB" con = sqlite3.connect("digikam3.db") target = "www.domain.de@ssh.strato.de" targetdir = "subdir" openmysql = "mysql -h rdbms -u username --password=pw database" def mymask(obj): if type(obj) in (str, unicode): return "\'" + obj.replace('"', '\\"').replace("'", "\\'").replace("\n","\\n") + "\'" else: return repr(obj) def myrepr(obj): str="" if type(obj) in (tuple, list): for elem in obj: str += mymask(elem) + ","; return "(" + str[:-1] + ")" else: return repr(obj) def joinselect(con, query): return ",".join(list(myrepr(row) for row in con.execute(query))) def remoteexec(cmd): os.system("ssh " + target + " \"" + cmd + "\"") def copyremote(file,tsubdir): os.system("scp -p " + file + " " + target + ":" + re.escape(targetdir + tsubdir)); dbprefix = "wp_ngg_" chooseidsstring = "(SELECT DISTINCT imageid FROM imagetags WHERE tagid = (SELECT id FROM tags WHERE name = \"public\"))" sqlcommands = "DELETE FROM " + dbprefix + "pic2tags;\n"; sqlcommands += "DELETE FROM " + dbprefix + "tags;\n"; sqlcommands += "DELETE FROM " + dbprefix + "pictures;\n"; sqlcommands += "DELETE FROM " + dbprefix + "gallery;\n"; sqlcommands += "DELETE FROM " + dbprefix + "album;\n"; sqlcommands += "INSERT INTO " + dbprefix + "pic2tags(picid,tagid) VALUES " + joinselect(con, "SELECT imageid, tagid FROM imagetags WHERE imageid IN " + chooseidsstring) + ";\n" sqlcommands += "INSERT INTO " + dbprefix + "tags(id, name, slug) VALUES " + joinselect(con, "SELECT id,name,name FROM tags WHERE id IN (SELECT DISTINCT tagid FROM imagetags WHERE imageid IN " + chooseidsstring + ")") + ";\n" sqlcommands += "INSERT INTO " + dbprefix + "pictures(pid, galleryid, filename, description) VALUES " + joinselect(con, "SELECT id,dirid,name,caption FROM images WHERE id IN " + chooseidsstring ) + ";\n" sqlcommands += "INSERT INTO " + dbprefix + "gallery(gid, name, path, title,previewpic) VALUES " allalbums=""; j=0; for row in con.execute("SELECT id,url,icon FROM albums WHERE id IN (SELECT DISTINCT dirid FROM images WHERE id IN " + chooseidsstring + ") ORDER BY url"): allalbums += "i:" + str(j) + ";i:" + str(row[0]) + ";" j += 1 #if(row[2] == None): icon = "NULL" #check whether icon is public icon = str(row[2]) if(row[2] == None or con.execute(chooseidsstring[1:-1] + " AND imageid = " + icon).fetchone() == None): nrow = con.execute("SELECT id FROM images WHERE id IN " + chooseidsstring + " AND dirid = " + str(row[0])).fetchone() icon=str(nrow[0]); sqlcommands += "(" + str(row[0]) + ", \"" + os.path.basename(row[1]) + "\" , \"photos" + row[1] + "\", \""+ os.path.basename(row[1]).replace("_"," ") + "\"," + icon + ")," sqlcommands = sqlcommands[:-1] + ";\n" allalbums = "a:" + str(j) + ":{" + allalbums + "}" sqlcommands += "INSERT INTO "+ dbprefix + "album(id, name, sortorder) VALUES (1, 'Main Gallery', '" + allalbums + "');\n" #print s f = open('remotedb.sql','w') f.write(sqlcommands.encode('latin-1')) f.close() copyremote('remotedb.sql','') remoteexec(openmysql + '<' + targetdir + '/' + 'remotedb.sql') #exit() print "creating remote directories" #remotely create dirs con2 = sqlite3.connect(":memory:") con2.execute("CREATE TABLE remotedirs (file varchar(255), PRIMARY KEY (file))") a = os.popen("ssh " + target + " find " + re.escape(targetdir) + " -type d").readlines(); for elem in a: if(elem != ''): con2.execute("INSERT INTO remotedirs VALUES (\"" + elem[len(targetdir):-1] + "\")"); con2.execute("CREATE TABLE localdirs (file varchar(255))") for row in con.execute("SELECT DISTINCT albums.url from images INNER JOIN albums ON images.dirid = albums.id WHERE images.id IN " + chooseidsstring): dirs=row[0].split("/") dire="" for dirss in dirs[1:]: dire += "/" + dirss con2.execute("INSERT INTO localdirs VALUES (\"" + dire + "\")"); con2.execute("INSERT INTO localdirs VALUES (\"" + row[0]+"/thumbs\")"); #delete dirs for row in con2.execute("SELECT file FROM remotedirs WHERE file NOT IN (SELECT DISTINCT file FROM localdirs) ORDER BY length(file) DESC"): print "deleting " + row[0]; remoteexec("rmdir " + re.escape(targetdir + row[0])); #create dirs for row in con2.execute("SELECT file FROM localdirs WHERE file NOT IN (SELECT file FROM remotedirs)"): print "creating " + row[0]; remoteexec("mkdir " + re.escape(targetdir + row[0])); #handle files a = os.popen("ssh " + target + " find " + re.escape(targetdir) + " -type f").readlines(); con2.execute("CREATE TABLE remote (file varchar(255), PRIMARY KEY (file))") for elem in a: if(elem != ''): con2.execute("INSERT INTO remote VALUES (\"" + elem[len(targetdir):-1] + "\")"); con2.execute("CREATE TABLE local (file varchar(255), isthumb bool , PRIMARY KEY (file))") for row in con.execute("SELECT albums.url, images.name from images INNER JOIN albums ON images.dirid = albums.id WHERE images.id IN " + chooseidsstring): con2.execute("INSERT INTO local VALUES (\"" + row[0]+"/"+row[1] + "\", 0)"); con2.execute("INSERT INTO local VALUES (\"" + row[0]+"/thumbs/thumbs_"+row[1] + "\", 1)"); print "Deleting leftover files" #delete files no longer in the db for row in con2.execute("SELECT file FROM remote WHERE file NOT IN (SELECT file FROM local)"): print "deleting " + row[0]; remoteexec("rm " + re.escape(targetdir + row[0])); print "Uploading files" #upload files for row in con2.execute("SELECT file,isthumb FROM local WHERE file NOT IN (SELECT file FROM remote)"): if(row[1]): print "creating and uploading thumbnail: " + row[0]; os.system("convert \"." + row[0].replace("thumbs/thumbs_","") + "\" -thumbnail 100x75 thumb.jpg") copyremote("thumb.jpg", re.escape(row[0])) else: print "resizing and uploading image: " + row[0]; os.system("convert \"." + row[0] + "\" -resize \"1024x768>\" small.jpg") copyremote("small.jpg", re.escape(row[0]))