I am ready to rebuild sqlite3 with the foreign_key support enabled as default. to do this, I should UNDEFINE SQLITE_OMIT_FOREIGN_KEY. I can't find a decent way to do this undef. I use g++ on linux.
Thanks, Shalom On Thu, Mar 24, 2011 at 7:41 AM, Shalom Elkin <[email protected]>wrote: > Sorry - it doesn't work. > sqlite3_exec with the pragma directive returns no error, but the program > still agrees to insert a record that violates foreign_key constraint. > > Here is the tables creation > ============== > CREATE TABLE people( > id integer, > nm text); > INSERT INTO "people" VALUES(1,'Jack'); > INSERT INTO "people" VALUES(2,'Jill'); > CREATE TABLE activity( > aid integer, > act text, > foreign key (aid) references people(id) > ); > ============= > Here's the simple c++ program (it is actually c...) > ============ > #include <stdio.h> > #include <sqlite3.h> > #include <unistd.h> > int main(int argc, char **argv){ > sqlite3 *db; > char *zErrMsg = 0,*P0; > const char *Q0; > const char *Z0= {"insert into activity values (1,\"play\")"}; > const char *Z1= {"insert into activity values (3,\"eat\")"}; > int rc; > if( argc<2 ){ > fprintf(stderr, "Usage: %s DATABASE\n", argv[0]); > return 101; > } > //open > rc = sqlite3_open(argv[1], &db); > if( rc ){ > fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); > return 111; > } > rc = sqlite3_exec(db,"PRAGMA foreign_keys = ON;",NULL,NULL,&P0); > printf("pragma returns &d |%s|\n",rc,P0); > //clear > rc = sqlite3_exec(db,"delete from activity;",NULL,NULL,&P0); > // > rc = sqlite3_exec(db,"begin transaction;",NULL,NULL,&P0); > rc = sqlite3_exec(db,Z0,NULL,NULL,&P0); > Q0 = sqlite3_errmsg(db); > printf("|%s| - should succeed : %d,|%s|=>|%s|\n",Z0,rc,P0,Q0); > // > rc = sqlite3_exec(db,Z1,NULL,NULL,&P0); > Q0 = sqlite3_errmsg(db); > printf("|%s| - should fail : %d,|%s|=>|%s|\n",Z1,rc,P0,Q0); > rc = sqlite3_exec(db,"commit;",NULL,NULL,&P0); > sqlite3_close(db); > } > ========= > and the results: > [shalom@pato sqlite]$ ./porta tik > pragma returns &d |(null)| > |insert into activity values (1,"play")| - should succeed : > 0,|(null)|=>|not an error| > |insert into activity values (3,"eat")| - should fail : 0,|(null)|=>|not an > error| > [shalom@pato sqlite]$ sqlite3 tik "select * from activity" > 1|play > 3|eat > ============= > > ???? > > Shalom > On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare > <[email protected]>wrote: > >> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote: >> >> > I appreciate the input. Some of the advice comes obviously from very >> good >> > and talented people who find a challenge at doing things WITHOUT >> reverting >> > to code writing. >> >> Doing as much (or most often, all) of the logic in SQL (instead of >> application code) removes a level of complexity, but is also generally >> faster and internally consistent. >> >> > I did a small program. Current show -stopper : >> > >> > what is the API equivalent of >> > >> > PRAGMA foreign_keys = ON; >> >> You can just send each of the SQL commands, including the pragma >> statement, in sqlite_exec (or you can get fancy with sqlite_prepare etc >> where it makes sense). >> >> Tom >> BareFeetWare >> >> -- >> iPhone/iPad/iPod and Mac software development, specialising in databases >> [email protected] >> -- >> Comparison of SQLite GUI tools: >> http://www.barefeetware.com/sqlite/compare/?ml >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Shalom Elkin > +972-544-704994 > -- Shalom Elkin +972-544-704994 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
