[Modeling-users] SQLite schema generation with -c does not use semicolons

[Ernesto Revilla]

Hi,

I've installed modeling 0.9-pre15, with sqlite (2.8.6)

when I create the schema with mdl_generate_DB_schema.py -c then I get output with no semicolons (;) separating the SQL commands, and this is not understood by sqlite.

I modified line 99 of the script (mdl_generate_DB_schema.py) to include a semicolon:
str+='%s;\n'%sqlExpr.statement()

now I can do a:
mdl_generate_DB_schema.py -c -C mymodel.py >schema.sql
sqlite mymodel.sqlite < schema.py

Directly creating the schema (not specifying the -c option) worked correctly.

[Sebastien Bigaret]

Hi Ernesto,

Sorry for the delay in the answer.

Post by Ernesto Revilla
I've installed modeling 0.9-pre15, with sqlite (2.8.6)
when I create the schema with mdl_generate_DB_schema.py -c then I get output
with no semicolons (;) separating the SQL commands, and this is not
understood by sqlite.
str+='%s;\n'%sqlExpr.statement()
mdl_generate_DB_schema.py -c -C mymodel.py >schema.sql
sqlite mymodel.sqlite < schema.py
Directly creating the schema (not specifying the -c option) worked correctly.

That's a reasonable request, I'll probably change the behaviour of the
script in the coming release when '-c' is specified --and add an option
for disabling it. Could you add a feature request to the project's RFE?

-- Sébastien.

[Sebastien Bigaret]

Hi,

Post by Ernesto Revilla
Hi,
I've installed modeling 0.9-pre15, with sqlite (2.8.6)
when I create the schema with mdl_generate_DB_schema.py -c then I get output with no semicolons (;) separating the SQL commands, and this is not understood by sqlite.
str+='%s;\n'%sqlExpr.statement()
mdl_generate_DB_schema.py -c -C mymodel.py >schema.sql
sqlite mymodel.sqlite < schema.py
Directly creating the schema (not specifying the -c option) worked correctly.

Okay, your RFE #812708 has been addressed, see the patch attached to:
https://sf.net/tracker/index.php?func=detail&aid=812708&group_id=58935&atid=489338

(the patch was checked-in the cvs main trunk for
mdl_generate_DB_schema.py rev1.8)


Note that this has been added to the script itself, not to the core
(in DatabaseAdaptors/): it seems a common habit to cursor.execute()
SQL statements that do not end with ';', even though this is in the
SQL standard.

There's an another reason why I do not want that the framework appends
semicolons to the sql statements: it gives the impression that
statements can be concatenated and executed in a single call to
cursor.execute(). But that's not the case, and worse: some python
db-frontends simply ignores all but the last statement, while others
actually return the result for each statements.

This will be in the next release. Note that the old behaviour can
still be obtained by supplying an empty string to the new option
-e/--end-with.

Last note, when things like these change from a version to another, I
now always append a line in the file MIGRATION in the root directory.
This is a good habit to keep an eye on this file before installing a new
version.

Cheers,

-- Sébastien.