Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » SQLite On delete cascade .sch ?
SQLite On delete cascade .sch ? [message #51958] Wed, 26 June 2019 20:30 Go to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Hello community,

Is it possible to set "DELETE ON CASCADE" to a foreign key on .SCH ?

I've seen the " REFERENCES_CASCADE_(table,column) " but it don't work.

Thanks in advance.

Best Regards

[Updated on: Fri, 28 June 2019 22:16]

Report message to a moderator

Re: SQLite On delete cascade .sch ? [message #51962 is a reply to message #51958] Thu, 27 June 2019 08:28 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
It looks like it was not implemented (yet?).

It is defined in plugin/sqlite3/Sqlite3Schema.h

My first guess is that definitions from PGSQL should work:

#ifndef REFERENCES
#define REFERENCES(x)              ATTRIBUTE("alter table @t add constraint FK_@x foreign key "\
                                             "(@c) references " #x ";",\
                                             "alter table @t drop constraint FK_@x;")
#endif

#ifndef REFERENCES_CASCADE
#define REFERENCES_CASCADE(x)      ATTRIBUTE("alter table @t add constraint FK_@x foreign key "\
                                             "(@c) references " #x " on delete cascade;",\
                                             "alter table @t drop constraint FK_@x;")
#endif

#ifndef REFERENCES_
#define REFERENCES_(n, x)          ATTRIBUTE("alter table @t add constraint FK_@x$" #n " foreign key "\
                                             "(@c) references " #x ";",\
                                             "alter table @t drop constraint FK_@x$" #n ";")
#endif

#ifndef REFERENCES_CASCADE_
#define REFERENCES_CASCADE_(n, x)  ATTRIBUTE("alter table @t add constraint FK_@x$" #n " foreign key "\
                                             "(@c) references " #x " on delete cascade;",\
                                             "alter table @t drop constraint FK_@x$" #n ";")
#endif


Can you try?
Re: SQLite On delete cascade .sch ? [message #51964 is a reply to message #51958] Thu, 27 June 2019 10:55 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Hello Mirek,

I'm gonna try it
I will bring back some info soon
Re: SQLite On delete cascade .sch ? [message #51966 is a reply to message #51958] Thu, 27 June 2019 20:55 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Well ,

According to stack overflow/Sqlite documentation, sqlite table cannot be altered after creation.
Since PGSQL alter table to add reference constraint, it do not work on sqlite.

Here is how we create a reference in SQLite :

CREATE TABLE "OW_EQUIPES_PLAYERS" (
	"EP_EQUIPE_ID"	integer,
	"EP_PLAYER_ID"	integer,
	FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
	FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);


So I had try something like that :
#ifndef REFERENCES
#define REFERENCES(x) 		      ATTRIBUTE("FOREIGN KEY(@c) REFERENCES (@x) ON DELETE CASCADE,","");
#endif

#ifndef REFERENCES_CASCADE
#define REFERENCES_CASCADE(x) 	      ATTRIBUTE("FOREIGN KEY(@c) REFERENCES (@x) ON DELETE CASCADE,","");
#endif

#ifndef REFERENCES_
#define REFERENCES_(n, x)	      ATTRIBUTE("FOREIGN KEY(@c) REFERENCES " #n "(@x) ON DELETE CASCADE,","");
#endif

#ifndef REFERENCES_CASCADE_
#define REFERENCES_CASCADE_(n, x)    ATTRIBUTE("FOREIGN KEY(@c) REFERENCES " #n "(@x) ON DELETE CASCADE,","");
#endif


But Seems not working, moreover my bad knowledge of Upp SQL does not really help.

Have you any idea ?

Thanks in advance
Best Regard
Re: SQLite On delete cascade .sch ? [message #51967 is a reply to message #51966] Fri, 28 June 2019 08:02 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Maybe try with INLINE_ATTRIBUTE ?

Anyway, if it cannot be altered after creation, then there will be situation where doing it will be impossible, like two tables referencing each other - because one of them will not exist during creation.

Mirek
Re: SQLite On delete cascade .sch ? [message #51969 is a reply to message #51958] Fri, 28 June 2019 12:23 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Quote:
Maybe try with INLINE_ATTRIBUTE ?


I had try but SQLite do not accept this kind of thing :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
	"EP_EQUIPE_ID"	integer FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
	"EP_PLAYER_ID"	integer FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE,
);


Quote:
Anyway, if it cannot be altered after creation, then there will be situation where doing it will be impossible, like two tables referencing each other - because one of them will not exist during creation.


It make sense for real SGBD but SQLite seems to don't care. I have been able to create my first table with this code :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
	"EP_EQUIPE_ID"	integer,
	"EP_PLAYER_ID"	integer,
	FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
	FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);


Isn't there a keyword in .sch like INLINE ATTRIBUTE/ Attribute that force the add of instruction "foreign key(..."
at the end of table definition ?
Or a way to force "free text" in sch file ?

Best Regard

Xemuth

[Updated on: Fri, 28 June 2019 18:11]

Report message to a moderator

Re: SQLite On delete cascade .sch ? [message #51975 is a reply to message #51958] Fri, 28 June 2019 18:51 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Also, I have try this kind of think :
#ifndef REFERENCE_SUFFIX
#define REFERENCE_SUFFIX(s,n,x)     INLINE_ATTRIBUTE("FOREIGN KEY(" #s ") REFERENCES " #n "(" #x ") ON DELETE CASCADE")
#endif

#ifndef FOREIGN_KEY
#define FOREIGN_KEY(s,n,x)          SCHEMA(",FOREIGN KEY(" #s ") REFERENCES " #n "(" #x ") ON DELETE CASCADE,", NULL)
#endif


Not working either.

Is there a way to see the schema stream pushed to sqlite ? I mean after sch conversion ?
Re: SQLite On delete cascade .sch ? [message #51976 is a reply to message #51958] Fri, 28 June 2019 20:38 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Quote:
Is there a way to see the schema stream pushed to sqlite ? I mean after sch conversion ?


Got it :
bool SqlSchema::UpdateNormalFile(int i, const char *dir, const char *name) const {
	String fn = NormalFileName(i, dir, name);
	if(LoadFile(fn) != Script(i)) {
		DeleteFile(fn);
		Cout() << Script(i) <<"\n"; // here we can print Schema
		SaveFile(fn, Script(i));
		return true;
	}
	return false;
}
Re: SQLite On delete cascade .sch ? [message #51977 is a reply to message #51958] Fri, 28 June 2019 21:23 Go to previous messageGo to next message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
Ok problem fixed,

SQlite3 can handle definition like that :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
	"EP_EQUIPE_ID"	integer FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
	"EP_PLAYER_ID"	integer FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE,
);


But it need to be like that :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
	"EP_EQUIPE_ID"	integer REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
	"EP_PLAYER_ID"	integer REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);


Well it's logic.
Here is definition of foreign key on sqlite :
#ifndef REFERENCES_
#define REFERENCES_(n, x)           INLINE_ATTRIBUTE("REFERENCES " #n "(" #x ")")
#endif

#ifndef REFERENCES_CASCADE_
#define REFERENCES_CASCADE_(n, x)   INLINE_ATTRIBUTE("REFERENCES " #n "(" #x ") ON DELETE CASCADE")
#endif


Also, In SQLite, for each connection you do to sqlite, you must need to enable Foreign keys :
		
Sql sql;
sql.Execute("PRAGMA foreign_keys = ON;");

Personaly I did it at BDD loading :
	
if(sqlite3.Open("myDB.db")) {
		SQL = sqlite3;
		#ifdef _DEBUG
			SqlSchema sch(SQLITE3);
			All_Tables(sch);
			
			if(sch.ScriptChanged(SqlSchema::UPGRADE)){
				SqlPerformScript(sch.Upgrade());
			}	
			if(sch.ScriptChanged(SqlSchema::ATTRIBUTES)){	
				SqlPerformScript(sch.Attributes());
			}
			if(sch.ScriptChanged(SqlSchema::CONFIG)) {
				SqlPerformScript(sch.ConfigDrop());
				SqlPerformScript(sch.Config());
			}
			sch.SaveNormal();
				
		Sql sql;
		sql.Execute("PRAGMA foreign_keys = ON;");
		#endif
	}


I guess, it is possible to enable it automaticly like lot of sqlite database viewer ?

[Updated on: Fri, 28 June 2019 22:15]

Report message to a moderator

Re: SQLite On delete cascade .sch ? [message #51989 is a reply to message #51977] Sun, 30 June 2019 19:18 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Thank you. Now in trunk... (I have added also variants without the constraint name).

I can certainly enable foreign keys during connection, but why is that an option? Does enabling have any disadvantages?

(Maybe as compromise, we can just add method to Sqlite3Session that enables them?)

Mirek
Re: SQLite On delete cascade .sch ? [message #51991 is a reply to message #51958] Sun, 30 June 2019 22:00 Go to previous message
Xemuth is currently offline  Xemuth
Messages: 387
Registered: August 2018
Location: France
Senior Member
According to Sqlite.org https://www.sqlite.org/pragma.html#pragma_foreign_keys

Quote:
Changing the foreign_keys setting affects the execution of all statements prepared using the database connection...

Also
Quote:
As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite.


Seems like having it enable do not having disadvantages.

Quote:
(Maybe as compromise, we can just add method to Sqlite3Session that enables them?)

Yeah I think it's the best compromise to do. I Will try to implement it.


Best Regard.
Xemuth
Previous Topic: Sqlite join from select
Next Topic: Multiple DUAL_UNIQUE generate conflicting statements
Goto Forum:
  


Current Time: Thu Mar 28 23:32:25 CET 2024

Total time taken to generate the page: 0.01288 seconds