I am creating local database tables using LongRange Studio and have a problem with resulting tables created in SQLLite. When I check the Is Key checkbox for a column, I expect that column to be a unique key in the SQLLite database, but when I query the SQLLite master, I see that the only primary key assigned with the CREATE TABLE command is 'rowid'.
Is this a bug or by design?
Thanks,
Edward
(RDMLX) Local Database Table
-
tsupartono
- Posts: 289
- Joined: Wed Apr 18, 2012 10:21 am
Re: (RDMLX) Local Database Table
Hi Edward,
Yes you are right. The columns that are designated as 'keys" are used only for row identification (for example during transfer between server/client, table update, deletion etc), there is absolutely no unique constraints created on them.
Our believe is that maintaining database integrity is not something that LR client app should do. It's best to think of the tables on the client as an unstructured temporary storage.
Having any constraints defined on the client tables can be an issue as it would stop end-user from entering an input that's not valid (according to the constraint). We can warn the end-user that the data is not valid, but we don't want to stop the user from entering the data (in mobile context, stopping user from entering an invalid value could end up in lost information - at least by letting them entering the invalid data, we know that there is a conflict that needs to be resolved later).
Data constraints can then be enforced on the server database, during the time when the data gets uploaded to the server. Any invalid values can be reported to the end-user at this time.
I do understand that this approach could be an issue for you, depending on how you are using your tables on the client.
Yes you are right. The columns that are designated as 'keys" are used only for row identification (for example during transfer between server/client, table update, deletion etc), there is absolutely no unique constraints created on them.
Our believe is that maintaining database integrity is not something that LR client app should do. It's best to think of the tables on the client as an unstructured temporary storage.
Having any constraints defined on the client tables can be an issue as it would stop end-user from entering an input that's not valid (according to the constraint). We can warn the end-user that the data is not valid, but we don't want to stop the user from entering the data (in mobile context, stopping user from entering an invalid value could end up in lost information - at least by letting them entering the invalid data, we know that there is a conflict that needs to be resolved later).
Data constraints can then be enforced on the server database, during the time when the data gets uploaded to the server. Any invalid values can be reported to the end-user at this time.
I do understand that this approach could be an issue for you, depending on how you are using your tables on the client.
Re: (RDMLX) Local Database Table
Hi Tony,
Thanks for the explanation. I think it would be nice to allow the option to enforce restraints on the local database table, I think their could be use-case either way. In my circumstance, remote users are gathering lab samples and scanning barcodes on unique sample containers. I want the user to know when they scan a duplicate right away, not later when they are uploading. I can code around this, however, by first doing a select and checking the result.rows.length value before inserting. I have a similar issue when populating the local database, I was trying to allow incremental updates using the INSERT OR REPLACE technique, but ended up with duplicates...I will just have to clear and replace all table data.
Thanks,
Edward
Thanks for the explanation. I think it would be nice to allow the option to enforce restraints on the local database table, I think their could be use-case either way. In my circumstance, remote users are gathering lab samples and scanning barcodes on unique sample containers. I want the user to know when they scan a duplicate right away, not later when they are uploading. I can code around this, however, by first doing a select and checking the result.rows.length value before inserting. I have a similar issue when populating the local database, I was trying to allow incremental updates using the INSERT OR REPLACE technique, but ended up with duplicates...I will just have to clear and replace all table data.
Thanks,
Edward