Support #3025
closedRe: Custom schema
0%
Description
Hi Koen/Wim & the rest of the team,
I'm a happy user of Wt, I'm working on an application which talks to an existing MySQL DB on /FreeBSD 10 (production) and Sqlite on Linux(development).
The issue that I'm facing is fairly simple, I have a table which has a 64bit auto-increment primary key.
The generated Sqlite3 Schema generates a bignum as primary key, on Sqlite - an auto-increment primary key has to be an integer / rowid alias.
Due to the existing schema, I have had to specialize dbo_traits to disable the default ID and the version field.
// ON MySQL this does the right thing
template <> struct dbo_traits : public dbo_default_traits { typedef boost::optional IdType; }
// ON Sqlite this generates a primary key of bignum, which doesn't autoincrement.
template <> struct dbo_traits : public dbo_default_traits { typedef boost::optional IdType; }
I've failed to workout how to tell Wt::Dbo that I want it to generate INTEGER rather than BIGNUM so I find myself wanting to just load my own hand rolled schema, which is proving a little difficult.
When I test with 'cat test.sql | sqlite3 test.db' - the tables are created just fine, and inserts show the correct behaviour of auto-incrementing.
When I try to call Wt::Dbo::backend::Sqlite3::executeSql() with the contents of test.sql, it succeeds but fails on subsequent inserts with Wt::Dbo::backend::Sqlite3Exception: Sqlite3: begin transaction: SQL logic error or missing database.
I've attached a stripped down example of the issue as the traditional minimal runnable example.
I'm using Wt 3.3.2 and boost 1.53 (if it helps).
Many thanks.
J
Files
Updated by Koen Deforche almost 10 years ago
- Status changed from New to Feedback
- Assignee set to Koen Deforche
Hey,
Do you require Wt::Dbo to perform the schema creation?
If not, Sqlite should be fairly robust when having the wrong type assumptions since it's phylosophy is to not care about the actual type of the stored data: i.e. it should not care whether the actual primary key type is bignum or integer, during CRUD operations.
Have you verified that it does not work properly without specializing dbo_traits, and if not, why not?
Regards,
koen
Updated by J n almost 10 years ago
Hi Koen,
I don't need Wt::Dbo to create the schema as I can create an on disk database by hand, but effectively that means I can't use an in memory database which is the desired use case for SQLite support.
As you suggest Sqlite doesn't care about the data being stored in the column, as it's all held as text anyway but the auto-increment support doesn't work except on integer primary key.
http://sqlite.org/autoinc.html says the following "AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY" (their capitalization )
Specializing dbo_traits doesn't on first blush appear related to the issue I'm experiencing so I'm not sure quite what you are asking me to try, apologies for my lack of reading comprehension.
What I want to do is insert a row without a rowid, allow sqlite to assign the next free rowid as value of the primary key for my table, and read the row back so I can create other child records which use the parent rowid as foreign key.
When Wt::Dbo generates a default id and version field, the id is correctly specified as an "integer" so what I would like to do is either override the generation of "bignum" as a 64bit type or just load my own schema as a string as I would if I called sqlite3_open_v2(":memory:) followed by sqlite3_exec("...\").
This leads me back to the example I posted which seems like it should work but as you can see it doesn't.
Any advice would be appreciated.
Regards
J
Updated by Koen Deforche almost 10 years ago
- File bug3025.cc bug3025.cc added
Hey,
I'm still not entirely following what you need. But in attachment I modified your test case so that it works.
Focusing on this line: "The issue that I'm facing is fairly simple, I have a table which has a 64bit auto-increment primary key." I devised a solution that is also fairly simple: namely, simply rename the surrogateID field to correspond to the name of the field in the database.
Also: "What I want to do is insert a row without a rowid, allow sqlite to assign the next free rowid as value of the primary key for my table, and read the row back so I can create other child records which use the parent rowid as foreign key." That really sounds like the surrogate ID field.
Am I missing something?
Regards,
koen
Updated by J n almost 10 years ago
Hi Koen,
That's great, so it looks like the issues was simply me failing to use Wt::Dbo correctly.
I was under the impression that the surrogateID was required to be disabled to use an existing schema, in hindsight, your suggestion makes perfect sense, Thanks again for clearing up the confusion - this changes does indeed solve my primary use case and therefore I no longer need to insert a custom schema.
On the related note purely out of curiosity, what is the problem with the insertion of the schema in the manner shown in my testcase?
Updated by J n almost 10 years ago
Hi Koen,
Sorry I skimmed over that part, you do insert as I was doing before, so thanks again for your help and please feel free to mark this resolved.
Incidently my license in up for renewal in a couple of months but I'll send you a mail about that privately.
Regards
J
Updated by J n almost 10 years ago
Actually just ran this again and I still get the sql logic exception but just calling session.createTables() works perfectly so it still solves my problem thanks again.
Updated by Koen Deforche almost 10 years ago
- Status changed from Feedback to Resolved
Updated by Koen Deforche almost 10 years ago
- Status changed from Resolved to Closed