SQLite Foreign Key Constraint

[TerryL]

Hi all. Given three therapists Beth, Nancy, and James, the user wants to enter a therapist name assigned to a patient. The db wants the therapist primary key id integer. I invented a gadget to show the three names for the user to choose but put the corresponding primary key integer into the data entry field using a popup button. It works, but what a production. A dancing bear would complete the circus. Please tell me there's an easier way to extract a referenced table name and the foreign key field name that links to it. Or a better way to populate the popup. Right now I'm using PRAGMA foreign_key_list("Patient").

Code: Select all

on mouseUp  --foreign key choices  **works**
   local tID, tSQL, tFKData, tData, tFldNames
   put the cDatabaseID of this stack into tID
   --PRAGMA foreign_key_list():  --item3=table referenced, item4=foreign key field
   put "PRAGMA foreign_key_list(" &quote& the label of btn "Tables" &quote& ");" into tSQL
   put revDataFromQuery(comma,cr,tID,tSQL) into tFKData
   if item 1 of tFKData = "revdberr" then ErrorTest tFKData  --in stack
   ----
    --PRAGMA table_info(): number,name,fieldtype,notnull=1,defaultvalue,primkey=1
   put "PRAGMA table_info(" &quote& item 3 of tFKData &quote& ");" into tSQL  --table referenced
   put revDataFromQuery(comma,cr,tID,tSQL) into tData
   if item 1 of tData = "revdberr" then ErrorTest tData
   repeat for each line i in tData  --comma list of fld names
      put quote& item 2 of i &quote&"," after tFldNames
      if number(items in tFldNames) = "3" then exit repeat  --only first 3 columns
   end repeat
   delete char -1 of tFldNames  --last comma
   ----
   put "SELECT" && tFldNames&& "FROM" &&quote& item 3 of tFKData &quote& ";" into tSQL  --select first 3 columns table referenced
   put revDataFromQuery(comma,cr,tID,tSQL) into tData
   if item 1 of tData = "revdberr" then ErrorTest tData
   ----
   set the text of btn "PopUp" to tData  --referenced table records
   set the topRight of btn "PopUp" to the topRight of fld 2 of grp (item 4 of tFKData)  --foreign key field
   set the layer of btn "PopUp" to "top"  --bring to front
   show btn "PopUp"  --visible
end mouseUp

LC's stack "SQLite Sampler" (c > program files > runrev > livecode community > resources > examples)
and MaxV's stack "SQLite Browser" ( http://livecodeshare.runrev.com/stack/7 ... administer )
were very helpful making my program, thank you.

These SQLite links were also helpful:
https://sqlite.org/lang.html --SQLite Syntax
https://sqlite.org/datatype3.html --SQLite Data Types
https://sqlite.org/foreignkeys.html --SQLite Foreign Keys
https://www.sqlite.org/faq.html --SQLite FAQ

SQLite FAQ #22: Does SQLite support foreign keys? As of version 3.6.19 (2009-10-14), SQLite supports foreign key constraints. But enforcement of foreign key constraints is turned off by default (for backwards compatibility). To enable foreign key constraint enforcement, run PRAGMA foreign_keys=ON; Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary. The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. (I found only one reference to this in the forum...from fMaxV. Thank you.)

LiveCode 9.0.3 stable when released will use updated SQLite v3.26.0. Thanks LC.

I'd also like to personally thank all the people who have offered advice and help. Thank you especially to Klaus, Richard, DunbarX, Jacque, Bogs, [-hh], and Panos. This forum is the best thing LiveCode has going for it. Terry

[dunbarx]

HI.

I do not use databases at all, though I make my own poor simulations either wholly within LC or in and out of external files.

But is your question LC related or DB related. Because if LC related, there are many ways to store and access associative dataSets. So if all you want to do is, say, be able to access any "record" of:

Beth,BethKey,patient1,patient2,...
Nancy,NancyKey,patient33,patient34,...
...

Is that what you meant?

Craig Newman

[TerryL]

Hi Dunbarx. This project was a front-end for any SQLite db. It would have been much easier hard coding for one SQLite db. I agree that a flat-file LibreOffice spreadsheet with some validity cells, or a flat-file all LC solution would be easier still. I just wanted to try my hand at SQLite. I'm not convinced a relational db with foreign keys is so much better. I erroneously thought an SQLite db was easy to migrate to a MySQL online multi-user db, which might be useful, but SQLite and MySQL syntax are much different.

Eventually I'll release the stack. I'm still writing the Help section, and I'm stuck on the binary BLOB field type, for which LC requires special consideration.

If anyone has a solution for inserting and selecting BLOB I'm interested!

LC's stack "SQLite Sampler" brings up the *b notation, but kind of glosses over the practical by inserting and selecting just one BLOB from one record. I think I have to check every field of every record for BLOB with revDatabaseColumnTypes() and somehow set the text of all those images to the binary data. Currently I've taken the "white flag" way out and just prohibit with "not supported at this time." Terry

[bogs]

If anyone has a solution for inserting and selecting BLOB I'm interested!

Well, there are lots of solutions. Did you read Max's Wiki article on it?

Other than that, right here in the forums there are plenty of posts about the topic. 99% recommend using encode/decode to work with blobs, and it *does* lower the bar for entry.

[TerryL]

Hi Bogs. Thank you for the BLOB links. Yes I will look into it. For now I've concluded its too difficult for me to implement, so I'm posting the stack.

Stack "DB SQLite" for personal use or create an SQLite database for your client's use as a front-end. I recommend the Splash Screen standalone method.

Features auto-connect to last database, CREATE TABLE assist, auto-data entry fields with defaults, foreign key popup, subtractive search, column sorting, row delete, row insert, field update, SELECT assist, query storage, query report to paper or pdf in up to 4 columns, help. If you don't know SQL there are enough examples and help to squeek by. Terry

Edit: updated to v1.1, fixed PRAGMA foreign_keys OFF/ON to be outside multi-statement transaction, improved help text.

[TerryL]

I updated the attached stack to v1.1 fixing errors while creating tables with foreign keys activated. I found buried in the SQLite documentation, https://sqlite.org/foreignkeys.html

"It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect."

And a clear example online.
--foreign keys must be disabled to create tables and enabled/disabled outside multi-statement transactions
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
CREATE TABLE table1 (
column1 datatype,
column2 datatype,
...
CONSTRAINT fk_column FOREIGN KEY (column_n) REFERENCES parent_table (column_n)
);
COMMIT;
PRAGMA foreign_keys = ON;

@ Bogs: I'm still against BLOB input with too many difficulties to overcome. It seems easier to me to reference images from a folder. Maybe all BLOBs could be in a separate table. It makes my head hurts. Terry

[bogs]

Heh, I have no problem with that, you should have seen some of the twists and dodges I went through recently because I had issues pulling pictures out I had stuck in with a different language

Before I had put them into a db, I went exactly the route your thinking, stuff em in a folder with unique information as the name. Pulling them at that point became an easy exercise of taking different parts of the record I was working with