[Digikam-users] Mysql/MariaDb database expert needs...

[Henrique Santos Fernandes]

I thought support for interval database had been  dropped!
My bad

Em qua, 18 de nov de 2015 12:19, Gilles Caulier <caulier.gilles at gmail.com>
escreveu:

> Certainly, but this will not solve the problem with internal server
> solution, because in this case there is no way to tune table creation from
> mysql prompt... All is done in background by digiKam database server (i
> don't yet investigate in this code).
>
> Gilles
>
> 2015-11-18 14:56 GMT+01:00 Henrique Santos Fernandes <sf.rique at gmail.com>:
>
>>
>> Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <
>> caulier.gilles at gmail.com> escreveu:
>>
>>> 2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <sf.rique at gmail.com
>>> >:
>>>
>>>> I dont understando that much but i have 2 questions.
>>>>
>>>> Why dont create the index normaly instead of using this if exist
>>>> procedure?
>>>>
>>>
>>> I don't know. I don't write the SQL code for MySQL. This have been done
>>> by an old contributor, few years ago...
>>>
>>> If you look into this procedure code, it's sound complicated. I don't
>>> know why ?
>>>
>>> CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50),
>>> index_name_vc varchar(50), field_list_vc varchar(1024))
>>>                     BEGIN
>>>
>>>                     set @Index_cnt = (
>>>                         SELECT COUNT(1) cnt
>>>                         FROM INFORMATION_SCHEMA.STATISTICS
>>>                         WHERE CONVERT(DATABASE() USING latin1) =
>>> CONVERT(TABLE_SCHEMA USING latin1)
>>>                         AND 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;
>>>
>>> Calls of this procedure is done like this (for thumbnails DB for ex) :
>>>
>>> CALL
>>> create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');
>>> CALL create_index_if_not_exists('FilePaths','id_filePaths','thumbId');
>>> CALL
>>> create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');
>>>
>>> Why this complexity with this procedure. No idea...
>>>
>>>
>>>> If the procedure is really needed, you need to create it with digikam
>>>> user?
>>>>
>>>
>>> it's already the case with current code.
>>>
>>>
>>>> You should be able to create the procedure when seting up the
>>>> databases, grant privilges and stuff. right?
>>>>
>>>>
>>> So user digikam dont need to create the procedure right? the root/admin
>>>> could do this..
>>>>
>>>>
>>> That i can see on Internet, procedure and function need Mysql grant
>>> priviledges to be executed, not created... But i'm not an expert...
>>>
>>
>> Can you try the explained ins thsi link and see if you have permissiosn?
>>
>> http://stackoverflow.com/questions/10089308/mysql-stored-procedure-permissions
>>
>> I guess it would be somethng like this:
>>
>> GRANT EXECUTE ON PROCEDURE digikamthumbsdb.create_index_if_not_exists TO 'digikam'@'localhost';
>>
>> Read the comments, because the user said it needed to add something in
>> this query!
>>
>> You may add to all procedures as well..
>>
>> http://dba.stackexchange.com/questions/20221/mysql-user-permission-on-stored-procedure
>>
>> Hope this help!
>>
>>
>>>
>>> Gilles Caulier
>>> _______________________________________________
>>> Digikam-users mailing list
>>> Digikam-users at kde.org
>>> https://mail.kde.org/mailman/listinfo/digikam-users
>>>
>>
>> _______________________________________________
>> Digikam-users mailing list
>> Digikam-users at kde.org
>> https://mail.kde.org/mailman/listinfo/digikam-users
>>
>>
> _______________________________________________
> Digikam-users mailing list
> Digikam-users at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/0b6536d7/attachment.html>