Page 1 of 2

SQLite does not cut it .. other small solutions?

Posted: Fri Oct 14, 2005 10:09 am
by Jorg
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

Posted: Fri Oct 14, 2005 10:35 am
by Frank
You can use Triggers in SQLite to enforce the integrity.

Posted: Fri Oct 14, 2005 11:14 am
by Jorg
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

Posted: Fri Oct 14, 2005 11:59 am
by lowjoel
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?)

Posted: Fri Oct 14, 2005 12:10 pm
by priyank_bolia
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;


Posted: Fri Oct 14, 2005 12:22 pm
by Jorg
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

Posted: Fri Oct 14, 2005 1:40 pm
by priyank_bolia
Also you talked about MySQL, it has added stored procedures and triggers recentlty, earlier releases don't have this functionality.

Posted: Fri Oct 14, 2005 2:29 pm
by Jorg
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

Posted: Fri Oct 14, 2005 2:36 pm
by leio
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...

Posted: Fri Oct 14, 2005 2:36 pm
by benedicte
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.

Posted: Fri Oct 14, 2005 3:11 pm
by Jorg
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

Posted: Fri Oct 14, 2005 3:56 pm
by priyank_bolia

Code: Select all

     CREATE TRIGGER insertName AFTER  INSERT ON Projects

     BEGIN

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

     END;


Posted: Fri Oct 14, 2005 4:24 pm
by Jorg
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();

Posted: Fri Oct 14, 2005 4:29 pm
by Jorg
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

Posted: Fri Oct 14, 2005 5:26 pm
by Frank
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.