Mintoris Forum

Author Topic: SQLite Foreign Key Constraints  (Read 2898 times)

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
SQLite Foreign Key Constraints
« on: Oct 12, 2016, 06:11 AM »
I am trying to use Sqlite in Mintoris to develop a simple database which the main table is ailiased to itself.  The table is designrd with foreign key constraint and should not allow the user to add a record or delete a record which would violate the constraint. Mintoris allows all operations and acts like there is no foreign key constraint.
  
  I have checked my table structure and querry operations out on two different Android SQLite programs and they work properly, allowing additional records when the constraints allow. The other programs deny adding a record or deleting a record when the constraints are violated and return with an error message.    

My question is should I solve this problem by writing a work around in Mintoris or is there a switch or command I can use to improve the functionality of SQLite in Mintoris.

Please help

Alan
« Last Edit: Oct 14, 2016, 06:11 PM by Chuck »

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: What limitations are there in SQLite support in Mintoris?
« Reply #1 on: Oct 12, 2016, 06:27 AM »
I've never used this feature, but the interweb says the following command will enable the constraints.

SqlExec n, "PRAGMA foreign_keys=ON;"

Let us know if it works.

-Chuck

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
Re: What limitations are there in SQLite support in Mintoris?
« Reply #2 on: Oct 12, 2016, 08:03 AM »
Chuck,
Thanks for the quick response.

I modified your contacts.bas to check out the pragma command and it works as expected.   Adding of records or the deletion of records is as the foreign key constraint allows.

I now get a runtime error if a record violates the foreign key constraints.  Is there a way for Mintoris to handle the runtime error and let me program a routine allowig the user to correct the error without terminating the program?

I tried to attach the program so you can see the error for yourself but the forum will not allow me to select a file to attach.

Any ideas?

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: What limitations are there in SQLite support in Mintoris?
« Reply #3 on: Oct 12, 2016, 06:10 PM »
I've been thinking about how to indicate a constraints condition. How about an Event Sub that gets called automatically when this happens?

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
Re: What limitations are there in SQLite support in Mintoris?
« Reply #4 on: Oct 12, 2016, 06:55 PM »
An event sub that returns the specific error code would be great.  Thanks.

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: What limitations are there in SQLite support in Mintoris?
« Reply #5 on: Oct 12, 2016, 07:10 PM »
What should we call this?


SetOnSqlException n, onSqlException()


Sub onSqlException(n, exceptionCode, exceptionMsg$)

End Sub

Sql_Exception_Other = 0
Sql_Exception_Constraints = 1


Give me a few days. Funny how these things come up the day after an update.

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
Re: What limitations are there in SQLite support in Mintoris?
« Reply #6 on: Oct 13, 2016, 02:41 AM »
Thanks, I can build my SQLite db and the associated i/o screens without worring about db integrity (inporting initial values from know good source).  I will build the constraint routines when you release the required function.

Thanks again, great program,

Alan

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: What limitations are there in SQLite support in Mintoris?
« Reply #7 on: Oct 14, 2016, 06:08 PM »
Should the foreign key constraints be on by default? Is there any reason they shouldn't be on?

Alan, if you can do the testing, please send an email to support@mintoris.com. Thanks.

After I get this new SQLite event sub working, I think I will add an accelerometer event sub and call this next release 7.5
« Last Edit: Oct 14, 2016, 10:25 PM by Mintoris »

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
Re: SQLite Foreign Key Constraints
« Reply #8 on: Oct 15, 2016, 05:11 PM »
As of now SQLite defaults to 'off' but reserve the right to change the default in future releases.  The recommendation from SQLite is to explictly declare 'Pragma foreign_key='on'"

I have modfied your contacts program for testing the foreign key pragma and can turn it on and off as needed and Sqlite works as it should.  Thanks.

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: SQLite Foreign Key Constraints
« Reply #9 on: Oct 21, 2016, 06:01 AM »
How is the 7.5 upgrade working? Does the SqlExceptionSub work for you?

Alan McGee

  • Jr. Member
  • **
  • Posts: 8
Re: SQLite Foreign Key Constraints
« Reply #10 on: Oct 22, 2016, 04:30 AM »
I have not tried to function as of yet.  I am building I/O routines and will test sometime Sunday.  I see where the function uses a file number, I think it would be better to be a global function which affect all SQLite databases. 

Chuck

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1899
Re: SQLite Foreign Key Constraints
« Reply #11 on: Oct 23, 2016, 06:36 AM »
I see where the function uses a file number, I think it would be better to be a global function which affect all SQLite databases.  

You can set each sqlite file to the same event sub if you want. This way gives you the option of different subs for different files.

sraffens

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 220
Re: SQLite Foreign Key Constraints
« Reply #12 on: Dec 19, 2016, 12:21 AM »
SetOnSqlException n, onSqlException() works great I added a demo program http://www.mintoris.com/forum/index.php?topic=1349.0