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

[Gilles Caulier]

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...

Gilles Caulier
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/4c5eae22/attachment.html>