[Interest] Storing complex SQL relations with Qt

[Constantin Makshin]

Usually if you don't explicitly ask for a transaction, RDBMS creates an
implicit one for each statement. AFAIK, SQLite uses this idea too.

And the next to last paragraph at http://www.sqlite.org/autoinc.html
gives an impression that uniqueness of values in AUTOINCREMENT columns
isn't affected by transactions, i.e. automatically generated customer
IDs will be unique no matter what happens to your application after step 1.

SQLite driver's lastInsertId() returns the result of
sqlite3_last_insert_rowid(), so step 2 looks safe enough (according to
SQLite documentation, INTEGER PRIMARY KEY [AUTOINCREMENT] column is just
an alias to row ID). Just remember to get it from the same query object
you use to insert the customer. ;)

Conclusion — your idea looks good to me. Not very portable (different
RDBMSes [may] have different notation of "last insert ID"), but should
work with SQLite.

On 12/12/2013 10:57 AM, Knut Krause wrote:
> Hi,
> 
> my application uses sqlite for its data and I modeled some complex relations 
> for customers, appointments and so on.
> 
> Now I want to edit such a relation using a custom dialog where the 
> appointments go to a list and some other stuff to a table (all are 
> QSqlTableModels).
> 
> The problem is: How do I insert the data depending on other things? Let's say 
> an appointment needs a customer. I insert the new customer data and the 
> appointments and when I click on submit I'd have to do something like
> 
> 1. insert customer (I think with primary key autoincrement I can simply do 
> that)
> 
> 2. retrieve the inserted customers ID. Can I use the models query() to get 
> lastInsertId()? Is this save?
> 
> 3. Set this ID to all models that depends on customer (by hand?) and submit 
> them as well
> 
> Is this approach safe? I mean AFAIK there are no transactions involved and 
> what happens for example if I insert the customer and then my application 
> crashes leaving my database in an inconsistent state?
> 
> Would be nice if someone could explain how I'm supposed to do this.
> 
> regards
> 
> 
> Knut

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: OpenPGP digital signature
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20131212/c335b842/attachment.sig>