SQLite does not cut it .. other small solutions? Topic is solved

This forum is reserved for everything you want to talk about. It could be about programming, opinions, open source programs, development in general, or just cool stuff to share!
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

SQLite does not cut it .. other small solutions?

Post by Jorg » Fri Oct 14, 2005 10:09 am

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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

Frank
Filthy Rich wx Solver
Filthy Rich wx Solver
Posts: 211
Joined: Sat Jan 01, 2005 6:19 pm

Post by Frank » Fri Oct 14, 2005 10:35 am

You can use Triggers in SQLite to enforce the integrity.

Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 11:14 am

Hi Frank!

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   
    );    
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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

lowjoel
Moderator
Moderator
Posts: 1511
Joined: Sun Jun 19, 2005 11:37 am
Location: Singapore
Contact:

Post by lowjoel » Fri Oct 14, 2005 11:59 am

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?)

priyank_bolia
wxWorld Domination!
wxWorld Domination!
Posts: 1339
Joined: Wed Aug 03, 2005 8:10 am
Location: BANGALORE, INDIA
Contact:

Post by priyank_bolia » Fri Oct 14, 2005 12:10 pm

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;


Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 12:22 pm

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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

priyank_bolia
wxWorld Domination!
wxWorld Domination!
Posts: 1339
Joined: Wed Aug 03, 2005 8:10 am
Location: BANGALORE, INDIA
Contact:

Post by priyank_bolia » Fri Oct 14, 2005 1:40 pm

Also you talked about MySQL, it has added stored procedures and triggers recentlty, earlier releases don't have this functionality.

Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 2:29 pm

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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

leio
Can't get richer than this
Can't get richer than this
Posts: 802
Joined: Mon Dec 27, 2004 10:46 am
Location: Estonia, Tallinn
Contact:

Post by leio » Fri Oct 14, 2005 2:36 pm

I really shouldn't open my mouth here, but I'll do that regardless :twisted:
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/

benedicte
wxWorld Domination!
wxWorld Domination!
Posts: 1409
Joined: Wed Jan 19, 2005 3:44 pm
Location: Paris, France

Post by benedicte » Fri Oct 14, 2005 2:36 pm

Jorg wrote:Any idea how I can retrieve the primary key of the just added item?
in MSSQL, you can do

Code: Select all

SELECT @@IDENTITY [AS col_name] 
there must be an equivalent in MySQL.

Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 3:11 pm

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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

priyank_bolia
wxWorld Domination!
wxWorld Domination!
Posts: 1339
Joined: Wed Aug 03, 2005 8:10 am
Location: BANGALORE, INDIA
Contact:

Post by priyank_bolia » Fri Oct 14, 2005 3:56 pm

Code: Select all

     CREATE TRIGGER insertName AFTER  INSERT ON Projects

     BEGIN

      UPDATE Projects SET ProjectName = 'Priyank'  WHERE ID = new.ID;

     END;


Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 4:24 pm

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();
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg » Fri Oct 14, 2005 4:29 pm

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.

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 (...)
then later on in the code (perhaps in another session);

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

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
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb

Frank
Filthy Rich wx Solver
Filthy Rich wx Solver
Posts: 211
Joined: Sat Jan 01, 2005 6:19 pm

Post by Frank » Fri Oct 14, 2005 5:26 pm

The SQLite-API has the function sqlite_last_insert_rowid() to retrieve the ID of the last inserted record.

I'm using SQLite 2.x, but it should be in SQLite 3 too.

Post Reply