Francesco,

Thanks for your patch. It very appreciate...

Holger,

Can you review this patch. It touch DB XML config file... 

Thanks in advance

Gilles Caulier

Created attachment 59413 [details]
/home/vivo/tmp/digikam-easy-dbupdate.patch

now I've applied it directly to dbconfig.xml.cmake from core with:
patch --verbose -p1 < digikam-easy-dbupdate.patch

emptied digikam-sc/build and recompiled, it start ok but the database is already updated, so no way to really test it.

initial debug log is:
digikam(11548)/digikam (core) Digikam::AlbumManager::setDatabase: DatabaseParameters: [ Type "QMYSQL", Name "digikam" (Thumbnails Name "digikam"); Host Name and Port: "localhost" 0; Username and Password: "digikam", "digikam"]
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
digikam(11548)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: Loading SQL code from config file "/home/vivo/usr/share/apps/digikam/database/dbconfig.xml"
digikam(11548)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: false "1" 1 1
digikam(11548)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater update
digikam(11548)/digikam (core) Digikam::SchemaUpdater::startUpdates: No database file available
digikam(11548)/digikam (core) Digikam::AlbumRootLocation::AlbumRootLocation: Creating new Location  "/vivo/docs/NIKON"  uuid  "volumeid:?uuid=800b21c2-dadc-4930-829e-a96b04ce26fa"
digikam(11548)/digikam (core) Digikam::AlbumRootLocation::AlbumRootLocation: Creating new Location  "/vivo/Immagini"  uuid  "volumeid:?uuid=800b21c2-dadc-4930-829e-a96b04ce26fa"
digikam(11548)/digikam (core) Digikam::CollectionManager::updateLocations: location for  "/home/vivo/docs/NIKON"  is available  true
digikam(11548)/digikam (core) Digikam::CollectionManager::updateLocations: location for  "/home/vivo/Immagini"  is available  true
digikam(11548)/digikam (core) Digikam::LoadingCache::setCacheSize: Allowing a cache size of 200 MB
digikam(11548)/digikam (core) Digikam::ThumbnailLoadThread::initializeThumbnailDatabase: Thumbnail db ready for use
digikam(11548)/digikam (core) Digikam::AlbumManager::setDatabase: nepomuk service available true
digikam(11548)/KEXIV2 KExiv2Iface::KExiv2::sidecarFilePathForFile: File path:  "/home/vivo/docs/NIKON/BLACK/200i10s.nef"  =>  XMP sidecar path:  "/home/vivo/docs/NIKON/BLACK/200i10s.nef.xmp"
digikam(11548)/digikam (core) Digikam::DImg::load: "/home/vivo/docs/NIKON/BLACK/200i10s.nef"  : RAW file identified
digikam(11548)/digikam (core) Digikam::ImageScanner::addImage: Adding new item "/home/vivo/docs/NIKON/BLACK/200i10s.nef"
[...]

Francesco,

What about these entries ? That can be fixed with your patch ?

https://bugs.kde.org/show_bug.cgi?id=258409
https://bugs.kde.org/show_bug.cgi?id=257183
https://bugs.kde.org/show_bug.cgi?id=233763
https://bugs.kde.org/show_bug.cgi?id=267733
https://bugs.kde.org/show_bug.cgi?id=262231
https://bugs.kde.org/show_bug.cgi?id=233770

Gilles Caulier

(In reply to comment #3)
> Francesco,
> 
> What about these entries ? That can be fixed with your patch ?
> 
> https://bugs.kde.org/show_bug.cgi?id=258409
not this one, seem related to this error message:
QSqlQuery::seek: cannot seek backwards in a forward only query
Dunno what exactly can be, maybe a server side cursor where a client side one should have been used


> https://bugs.kde.org/show_bug.cgi?id=257183
yes if the triggers for the relevant table are wrong and not deleted and re-created then this bug could be fixed

> https://bugs.kde.org/show_bug.cgi?id=233763
IMHO when when one of ip,server,port change in the dialog the database should be checked (including DBVersion in digikam.settings). If not existant it should be created, or if current db is valid a warning telling to use the database migration tool.


> https://bugs.kde.org/show_bug.cgi?id=267733
"The Internal MySQL box in option menu is blank. "
In my case it was populated with correct values both in 1.9.0 and 2.0
would tell the user to re-test and close if not confirmed

> https://bugs.kde.org/show_bug.cgi?id=262231
mmh, may be best to disable triggers during mass migration, then do a cleanup following the same rules i.e. 
delete a.* from a LEFT JOIN b on a.id = b.id where ISNULL(b.id)
then reenable them
mysqldump tool may be inspirational here

> https://bugs.kde.org/show_bug.cgi?id=233770
yes should be fixed

P.S. I do grok enough of mysql sql language and behaviour, and very pleased to use digikam, feel free to contact me whenever you want also by private email

> https://bugs.kde.org/show_bug.cgi?id=262231
also both database should be write-locked

Francesco,

We will be very happy if you can help use to hack as developer MySQL database support in digiKam. Holger who have implemented this is not available, and Marcel and me we are very busy (GSOC 2011 just started)...

Are you developer ?

Gilles Caulier

My skills are very oriented at database management and interpreted languages, I do kinda suck at c++ but can read it somewhat.
Other than this to give some kind of help you should tell me what to fix, and where to search in the code to be able to do some useful work i.e. this may need some input and time from your side. Plus my english suck ;)

If these characteristics meet your needs you have my email, I'm on pc from 10 a.m. to 8 p.m most working days and some weekend at your disposal

Hi Francesco,

as Guilles wrotes, I'm very busy with other things than coding for 
digikam :-(
If you want to go into the code and you do need some help or some 
explanations
about the mysql database part (also the xml based database configuration 
script),
feel free to send me an eMail. I will try to give (soon ;) ) some answers ;)

--
Holger

Am 02.05.2011 15:10, schrieb Francesco Riosa:
> https://bugs.kde.org/show_bug.cgi?id=271924
>
>
>
>
>
> --- Comment #7 from Francesco Riosa<francesco pnpitalia it>   2011-05-02 15:10:48 ---
> My skills are very oriented at database management and interpreted languages, I
> do kinda suck at c++ but can read it somewhat.
> Other than this to give some kind of help you should tell me what to fix, and
> where to search in the code to be able to do some useful work i.e. this may
> need some input and time from your side. Plus my english suck ;)
>
> If these characteristics meet your needs you have my email, I'm on pc from 10
> a.m. to 8 p.m most working days and some weekend at your disposal
>

Created attachment 59678 [details]
digikam-easy-dbupdate.patch

working on bug #258409 I've noticed a bug in stored procedure create_index_if_not_exists (my patch).

dunno where the collation of the connection is set to:
SET collation_connection = 'utf8_general_ci'
But default for database is 'utf8_unicode_ci', so when migrating it result in the error:
#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

the procedure should set explicitly collations where needed, empiric results show it's needed only in the first query of the S.P.

CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
    SELECT COUNT(1) cnt
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_name = table_name_vc COLLATE utf8_general_ci
      AND index_name = index_name_vc COLLATE utf8_general_ci
);

IF IFNULL(@Index_cnt, 0) = 0 THEN
    set @index_sql = concat('ALTER TABLE ',table_name_vc,' ADD INDEX ',index_name_vc,'(',field_list_vc,');');
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END;

the new stored procedure has been tested, the patch instead is manually modified and never applied

-- Humans should have only one language and one alphabet really --
-- to set collation is not enough, for various reason strings may be of another charachter set (probably latin1)


DROP PROCEDURE `create_index_if_not_exists`;;

CREATE DEFINER=`digikam`@`%` PROCEDURE `create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
    SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
    SELECT COUNT(1) cnt
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
      AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
);

IF IFNULL(@Index_cnt, 0) = 0 THEN
    set @index_sql = concat(
      CONVERT( 'ALTER TABLE ' USING latin1),
      CONVERT( table_name_vc USING latin1),
      CONVERT( ' ADD INDEX ' USING latin1),
      CONVERT( index_name_vc USING latin1),
      CONVERT( '(' USING latin1),
      CONVERT( field_list_vc USING latin1),
      CONVERT( ');' USING latin1)
    );
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END ;;

@bug_id = 271924
@bug_status = RESOLVED
@resolution = FIXED

Git commit ec7ceeea0d2587abb9f1bdd17109d4903998e943 by Francesco Riosa.
Committed on 09/05/2011 at 14:20.
Pushed by riosa into branch 'master'.

more resilient database upgrade and startup

All statements are made resilient to a partially upgraded database.
- CREATE TABLE are done with IF NOT EXISTS
- TRIGGERs are dropped and re-created
- a new stored procedure is added "create_index_if_not_exists"
  the procedure check in information_schema if the index exists,
  if not does create it
- all INDEXes are created using "create_index_if_not_exists"

BUG: 271924
BUG: 258409
BUG: 257183
BUG: 267733
BUG: 262231
BUG: 233770

M  +90   -56   data/database/dbconfig.xml.cmake     

http://commits.kde.org/digikam/ec7ceeea0d2587abb9f1bdd17109d4903998e943