[Digikam-devel] [Bug 127321] allow use of other database backends such as postgres and mysql

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=127321





--- Comment #15 from Marcel Wiesweg <marcel wiesweg gmx de>  2009-07-20 16:33:10 ---

> I don't have an SVN account for KDE, also I don't have experience how to use
> git :(

It's all right to do this with patches. Though, depending on how much work this
is,
it may be more convenient to get a KDE SVN account;
getting an account is easy, see
http://techbase.kde.org/Contribute/Get_a_SVN_Account
No need for git.


> According the url http://www.htmlite.com/mysql003.php we can choose
> "LONGTEXT    A string with a maximum length of 4294967295 characters"
> for extra long datafields.
> I have choosen VARCHAR(255) for the most text fields.

Some will be longer (comments, copyright entries), some have a fixed length
(uniqueHash),
for some a short string like 255 may be all right (make, model), paths
are difficult to predict (260? No. 1024? 4096? Even longer?)

>
> 3) Which version of MySQL can we expect as minimum dependency? 5.0.3?
> >I've using the version 5.0.75. But I think your proposed version is also okay, since I
> >don't use something special. Maybe the statements are compatible for the 4.x.x versions.

Best choice is to be compatible with amarok here. They say 5.0. We should pick
any version widely available on all modern distributions, not too recent and
not too old.


> 7) "DB locking - only one Digikam-App should access the DB" In fact we intend a
> >usage scenario where multiple applications access the same DB concurrently.
> >Currently you can open two digikam instances on the same db with no problems.
> >Change messages are currently distributed over DBus, for network scenarios this
> >must be done over a small network protocol (no priority for now).
> >Table locking issues that come with real concurrent access require some
> >separate thinking.
>
> I also thought about the communication between the digikam instances.
> I hope KDE provides a generic solution for this problem.

In AlbumDB, there are a lot of statements backend->recordChangeset(...). All
these changesets
are already broadcast as DBus signals to other digikam apps.
They are as well exclusively used for application-internal change notification.

>
> 8) Transactions: We use transactions in a few places, but only as an SQLite
> >specific optimization, not because the operation need be atomic. The only place
> >where a transaction shall really ensure atomicity is the schema updating.
>
> If that would needed, the DBAction can modified with the "transaction" mode
> parameter.

Ok.


> I think there is only one problem with predefined statements: Dynamically
> produced SQL queries.
> I suggest to use special DBActions with mode "fragment" wich contains only one
> statement with a specific part
> of a query. Eg.
> <dbaction name="ImageTextCondition1" mode="fragment">
>   <statement mode="query">UserText=:Text
>   </statement>
> </dbaction
>
> And in code it would look like:
> select * from TextTable where
>
> m_access->backend()->getDBAction(QString("ImageTextCondition1")).getStatement()
>   if (user has clicked imagetext){
>     "OR"
>
> m_access->backend()->getDBAction(QString("ImageTextCondition2")).getStatement()
>   }

I guess this will work. There are quite a lot of small fragments in
imagequerybuilder.cpp. Some are trivial, like single brackets, and maybe only a
fraction of this needs to be DBMS specific.

>
>
> There are new methods in databasecorebackend class:
>
> databaseAction getDBAction(const QString &actionName);
> With this method, a DB Action can be retrieved with its ID.
>
> This action is a parameter for the next method:
> bool execDBAction(const databaseAction &action, const QMap<QString, QVariant>*
> bindingMap = 0, QList<QVariant>* values = 0, QVariant *lastInsertId = 0);
>
> where the DBAction is executed. Note that this method has also a bindingMap
> which contains a list of named placeholders. With them you
> can use a statement like:
> select * from table where id=:ID. A entry with the key ":ID" within the map
> contains the concrete value and will be replaced.
> (The current implementation allows SQL injection because of simple replacement
> via regexp, but I'm working on the replacing algorithm. The QT provided methods
> for binding named
> placeholder doesn't work unfortunately :(

Yes, if you look at the source of the Qt MySQL driver, it supports only
positional placeholders, no named placeholders. Same for SQLite btw, that's why
we only use positional placeholders so far.

There are a few batch operations at performance critical points. No need for
true batch operations here, but prepared queries should be used.

There will be a specified number and order of expected bound values for each
action anyway as far as I see this. So why not use positional binding?


> I also have used hibernate on java and I must agree:
> Building of dynamic queries is really cool with an ORM layer!!!^2

I shortly thought about this two years ago when we decided to take Qt SQL. For
there there is an additional layer involved, adding complexity, where direct
SQL writing comes easily.

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.