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

[Quallenauge]

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





--- Comment #12 from Quallenauge <Hamsi2k freenet de>  2009-07-19 18:11:54 ---
Created an attachment (id=35461)
 --> (http://bugs.kde.org/attachment.cgi?id=35461)
First proposal for a generic database access.

Okay, I have today synchronized with the head revision. No troubles - good :)

1) I would recommend you to work in an SVN branch, unless you are using local
>Git already. A pity KDE has not yet moved to Git. Anyway, working in the open
>is better for complex changes.

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

2) Text handling:
>SQLite has only one type for text with no imposed length limit, so we never
>thought about this For MySQL we need to choose an appropriate VARCHAR or TEXT
>value for the expected and allows length of each text field.

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.

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.

4) There are IIRC five source files where we use SQL, this is albumdb.cpp (95%
>of statements), schemaupdater.cpp (defining the schema), haariface.cpp (a few
>statements), imagelister.cpp (for album listing) and imagequerybuilder
>(building select clauses)

I don't have touched all statements, I think this should be done step by step
(everytime
we make changes on them).

5) Config dialogs are not critical, we need that of course but getting the
>backend to work has priority

Okay.

6) If we have MySQL support, we will probably default to MySQL embedded,
>providing full server support only as expert setting. But as I understand this
>is only a matter of linking against a different library and providing the right
>parameters.

Right.

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.

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.

Okay, know I would explain the patch:
The file dbconfig.xml (the lookup path is hardcoded in "void
DatabaseParameters::readConfig()" method) contains all db relevant data:
- The connection data is stored there (atm).
- DBActions wich contains one or more DB Statements.
<dbaction name="CreateDB" mode="transaction">
  <statement mode="query">CREATE TABLE AlbumRoots
            (id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
              label VARCHAR(255),
              status INTEGER NOT NULL,
              type INTEGER NOT NULL,
              identifier VARCHAR(255),
              specificPath VARCHAR(255),
              UNIQUE(identifier, specificPath));
  </statement>
  <statement mode="query">....
  </statement>
  ...
</dbaction

The "mode" parameter defines the execution mode. If it is set to "transaction",
a transaction is opened and if all statements are successfully executed a
commit is executed.
In other cases a rollback is executed.

The statement element has also a "mode" parameter which defines if a "query" or 
a plain SQL command (for creating triggers, procedures, etc.) is executed.

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()
  }


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 :(

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