SQLite does not cut it .. other small solutions? Topic is solved
SQLite does not cut it .. other small solutions?
Hi All,
I have played around with SQLite and implemented a data layer. To my dissapointment the data integrety management part of SQLite is poor. It does not enforce FOREIGN KEY constraints and CASCADE DELETES at all, resulting in dangling table entries.
I want to have a table that when a project entry is deleted, all relating data that uses a FOREIGN KEY back to the project entry ID is also deleted from the DB. MySQL seems to properly implement this, SQLite does not (or at least the way I do it does not work).
What other small solutions are present to do SQL? Preferrably a lib, no server back end which means the server needs to be in the LIB, and de DB is technically a file on disk, residing somewhere where I can point to (instead of a source connection).
I am aware of MySQL embedded, but fear that the overhead is a bit too big for a small app.
With regards,
- Jorgen
I have played around with SQLite and implemented a data layer. To my dissapointment the data integrety management part of SQLite is poor. It does not enforce FOREIGN KEY constraints and CASCADE DELETES at all, resulting in dangling table entries.
I want to have a table that when a project entry is deleted, all relating data that uses a FOREIGN KEY back to the project entry ID is also deleted from the DB. MySQL seems to properly implement this, SQLite does not (or at least the way I do it does not work).
What other small solutions are present to do SQL? Preferrably a lib, no server back end which means the server needs to be in the LIB, and de DB is technically a file on disk, residing somewhere where I can point to (instead of a source connection).
I am aware of MySQL embedded, but fear that the overhead is a bit too big for a small app.
With regards,
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Hi Frank!
Interesting. Can you give me a small example? These are two tables:
When a project item is deleted, the corresponding ignorelist items must be deleted too. I am quite new to triggers so I wonder how that works..
With regards,
- Jorgen
Interesting. Can you give me a small example? These are two tables:
Code: Select all
create table Projects (
ID INTEGER PRIMARY KEY,
ProjectName TEXT,
BackupInterval INTEGER,
BackupDestPath TEXT,
BackupOnScreenSaver INTEGER
);
create table IgnoreList (
ID INTEGER PRIMARY KEY,
ProjectID INTEGER,
IgnoreItem TEXT,
FOREIGN KEY (ProjectID) REFERENCES Projects(ID) ON DELETE CASCADE
);
With regards,
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
-
- Part Of The Furniture
- Posts: 1511
- Joined: Sun Jun 19, 2005 11:37 am
- Location: Singapore
- Contact:
I had another problem with SQLite, ohwell, I gave up on it, but yea might as well share. i did MANY (im talking about a few hundred thousand) transactions into SQLite and out, after which i did a DELETE * command. the output file somehow doesn't shrink, so I gave it up (OPTIMIZE is a MyISAM thing, yes?)
-
- wxWorld Domination!
- Posts: 1339
- Joined: Wed Aug 03, 2005 8:10 am
- Location: BANGALORE, INDIA
- Contact:
Not sure, but can try this:
Code: Select all
CREATE TRIGGER deleteIgnoreList AFTER DELETE ON Projects
BEGIN
DELETE * from IgnoreList WHERE ProjectID = old.ID;
END;
To lowjoel, there is a VACUUM command. It will schrink your DB.
piyank, thanks a lot! This might be what I need. I hate giving up on SQLite as it is a very easy interface, small and less overhead on my app. It might be even more elegant using triggers as you can perform more cleanup when needed.
Will try it at home!
- Jorgen
piyank, thanks a lot! This might be what I need. I hate giving up on SQLite as it is a very easy interface, small and less overhead on my app. It might be even more elegant using triggers as you can perform more cleanup when needed.
Will try it at home!
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
-
- wxWorld Domination!
- Posts: 1339
- Joined: Wed Aug 03, 2005 8:10 am
- Location: BANGALORE, INDIA
- Contact:
So I've heard. The foreign key stuff did work though in MySQL. The backside of SQLite is that foreign keys are not enforced. If I enter an ID in the table that does not exist it will not complain. But wiuth triggers I can fix a lot I assume.
I like the auto index increment of SQLite, so I don't have to provide an ID. Any idea how I can retrieve the primary key of the just added item? I assume that is also with triggers?
for example;
insert into projects (prjname, backup_path) values ('some project', 'c:\tmp\blah');
Now the row gets a unique ID called 'id'.
sqlite> select * from projects;
1|some project|blah
How do I get that ID (in this case 1) back? One way is SELECT MAX(ID) from projects; and assume the last issued ID is the hioghest, I rather get the ID back from SQLite somehow.
Regards,
- Jorgen
I like the auto index increment of SQLite, so I don't have to provide an ID. Any idea how I can retrieve the primary key of the just added item? I assume that is also with triggers?
for example;
insert into projects (prjname, backup_path) values ('some project', 'c:\tmp\blah');
Now the row gets a unique ID called 'id'.
sqlite> select * from projects;
1|some project|blah
How do I get that ID (in this case 1) back? One way is SELECT MAX(ID) from projects; and assume the last issued ID is the hioghest, I rather get the ID back from SQLite somehow.
Regards,
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
-
- Can't get richer than this
- Posts: 802
- Joined: Mon Dec 27, 2004 10:46 am
- Location: Estonia, Tallinn
- Contact:
I really shouldn't open my mouth here, but I'll do that regardless
I can't understand what's the hype about MySQL. There is PostgreSQL - it has had stored procedures and triggers for a long time. It outperforms MySQL in every proper benchmark I have read. It is comparable to Oracle regarding functionality, with the major difference that it doesn't support master-master replication yet (but something similar is achievable with python scripts and such).
I don't get it
As for SQLite, I thought it to be nice too based on a very short encounter with it. The key was to use COMMIT, instead of automatic commiting, to not make the thing dead-slow due to file I/O. However, it should be elementary to use COMMIT and ROLLBACK as appropriate, but if automatic is the default...
I can't understand what's the hype about MySQL. There is PostgreSQL - it has had stored procedures and triggers for a long time. It outperforms MySQL in every proper benchmark I have read. It is comparable to Oracle regarding functionality, with the major difference that it doesn't support master-master replication yet (but something similar is achievable with python scripts and such).
I don't get it
As for SQLite, I thought it to be nice too based on a very short encounter with it. The key was to use COMMIT, instead of automatic commiting, to not make the thing dead-slow due to file I/O. However, it should be elementary to use COMMIT and ROLLBACK as appropriate, but if automatic is the default...
Compilers: gcc-3.3.6, gcc-3.4.5, gcc-4.0.2, gcc-4.1.0 and MSVC6
OS's: Gentoo Linux, WinXP; WX: CVS HEAD
Project Manager of wxMUD - http://wxmud.sf.net/
Developer of wxGTK;
gtk+ port maintainer of OMGUI - http://www.omgui.org/
OS's: Gentoo Linux, WinXP; WX: CVS HEAD
Project Manager of wxMUD - http://wxmud.sf.net/
Developer of wxGTK;
gtk+ port maintainer of OMGUI - http://www.omgui.org/
in MSSQL, you can doJorg wrote:Any idea how I can retrieve the primary key of the just added item?
Code: Select all
SELECT @@IDENTITY [AS col_name]
Ok first test was succesful! Triggers seem to work fine in SQLite!
sqlite> create table asso (pid integer primary key, id integer, padje text);
sqlite> insert into asso (id, padje) values (1, 'c:\pfff');
sqlite> insert into asso (id, padje) values (1, 'c:\pfff2');
sqlite> insert into asso (id, padje) values (2, 'c:\haha');
sqlite> insert into asso (id, padje) values (2, 'c:\haha_2');
sqlite> select * from asso;
1|1|c:\pfff
2|1|c:\pfff2
3|2|c:\haha
4|2|c:\haha_2
sqlite> delete from project where id=1;
sqlite> select * from asso;
3|2|c:\haha
4|2|c:\haha_2
sqlite>
Now to get the ID back from the ADD method.
With regards,
- Jorgen
sqlite> create table asso (pid integer primary key, id integer, padje text);
sqlite> insert into asso (id, padje) values (1, 'c:\pfff');
sqlite> insert into asso (id, padje) values (1, 'c:\pfff2');
sqlite> insert into asso (id, padje) values (2, 'c:\haha');
sqlite> insert into asso (id, padje) values (2, 'c:\haha_2');
sqlite> select * from asso;
1|1|c:\pfff
2|1|c:\pfff2
3|2|c:\haha
4|2|c:\haha_2
sqlite> delete from project where id=1;
sqlite> select * from asso;
3|2|c:\haha
4|2|c:\haha_2
sqlite>
Now to get the ID back from the ADD method.
With regards,
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
-
- wxWorld Domination!
- Posts: 1339
- Joined: Wed Aug 03, 2005 8:10 am
- Location: BANGALORE, INDIA
- Contact:
Code: Select all
CREATE TRIGGER insertName AFTER INSERT ON Projects
BEGIN
UPDATE Projects SET ProjectName = 'Priyank' WHERE ID = new.ID;
END;
That sounds cool, but how do I get that ID back to my main app? The purpose of returning the new ID is that my object which I create in C++ can have that ID so it can restore itself when needed.
e.g.
prj = new Project();
int id = prj->CreateEntry();
prj
prj->SetId(1);
prj->Restore();
e.g.
prj = new Project();
int id = prj->CreateEntry();
prj
prj->SetId(1);
prj->Restore();
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
That sounds cool, but how do I get that ID back to my main app? The purpose of returning the new ID is that my object which I create in C++ can have that ID so it can restore itself when needed.
e.g.
then later on in the code (perhaps in another session);
Also without the object stuff, when for example you insert a row, but the ID of that inserted row will function as the "reference" in the wxChoice and where the name is the name of the project, somehow you need to know what the reference to that entry is so you can do a full restore later if needed.
Regards, and thanks for helping me this far (nearly there).
- Jorgen
e.g.
Code: Select all
prj = new Project("name", "path");
// here I retrieve an ID back, to keep as reference e.g. in a list
// so that I can restore or link it later to other tables
int id = prj->Insert(); // INSERT into project (....) values (...)
Code: Select all
prj->SetId(id); // <-- this ID must be returned earlier if possible
prj->Restore(); // select name, path from project where ID = id
prj->SetName("other name");
prj->Update(); // update command where ID = 1
Regards, and thanks for helping me this far (nearly there).
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb