SQL script and SQLite
-
Just did some digging into SQLite sources. ".read" command of sqlite3 parses all .sql file and feed to sqlite3_exec() separate statements.
-
And QSQLiteResult doesn't seem to use sqlite3_exec(). As i can see from it's sources for exec(QString) is uses sequential calls for prepare(QString) and exec(). And former uses sqlite3_prepare16_v2() for queries. I'll try to code with SQLite's API and figure out what's wrong.
-
And bingo! sqlite3_exec() can handle sequential CREATE TABLE statements. And sqlite3_prepare16_v2() as it's fifth argument takes a pointer to a pointer where it can store unprepared part of the query! And QSQLiteResult::prepare(const QString &query) just passes there null! Why? Is it a bug?
Sorry for slander of SQLite.Offtopic: didn't ever think that rather intensive coding implies so much digging in someone else's code.
-
Just did a "bugreport":https://bugreports.qt.nokia.com/browse/QTBUG-21884
-
I wouldn't call it a bug. The exec call made this way is compatible to the other Qt SQL drivers, which may not support this feature.
-
[quote author="p-himik" date="1317963986"]I really don't want to parse my .sql file (another bicycle, don't need it) or create artificial separators of statements (trying not to rely on my memory).[/quote]
In case you decide to do so it -is- can be as easy as
@
if(_database.tables().isEmpty())
{
QFile schemaFile(":/sql/schema.sql");schemaFile.open(QFile::ReadOnly); QStringList schemaTableList = QString(schemaFile.readAll()).split(";"); foreach(const QString schemaTable, schemaTableList) { if(!schemaTable.trimmed().isEmpty()) { _database.exec(schemaTable); } } schemaFile.close(); }
@
-
Volker, than how can you call this feature? I'm not an expert in SQL so i'm just curios. And why doesn't QSqlDriver::DriverFeature include such feature? Also, QPSQL driver successfully executes query with multiple CREATE TABLE so i'm not so sure about compatibility.
Lukas, what about CREATE TRIGGER? It may include ";" inside statement.
-
Be aware that this solution only works if there is no semikolon (";") contained in your SQL workload. It will fail on the following SQL:
@
CREATE TABLE qdn1 (
id int default 0,
name varchar(20) default 'not given;' -- it fails on the semicolon of the default value!
);CREATE TABLE qdn2 (
id int
);
@ -
I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.
-
Well, that's true. The snippet posted will not work in all cases and might require some adjustments in cases where not only simple CREATE TABLE statements are used.
-
[quote author="Volker" date="1317979869"]I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.[/quote]
Yes, maybe it is so. But i don not understand why exec(QString) is made from prepare(QString) and exec(). As i can see from sources, the only reason for this is unwillingness to write similar code in exec(QString) and prepare(QString). Actually i can write exec(QString) with native SQLite's sqlite3_exec() which, as i mentioned above, can handle multiple statements in a single query.