Database issues

Hi everyone! I am developing an application that stores most of its data in a database (for the record it's SQLite). The file for that data has a very unique name and will be stored in a folder with a very unique name (NameOfTheApp + " data").

However I want to be extra cautious. Before creating the folder I check if it already exists. Before creating the file I check if it already exists. I don't want to overwrite anything by accident.

I think I should check if the tables inside the data file are "mine". I mean if they have the names and fields that my application expects or if they are from something else. Do you think this is necessary? Or am I overly paranoid?

I also think of somehow storing meta data in the file (i.e. the version number of the database format). How would you do this? I think of adding another table with only one entry for the version number and other info.

Any help, thoughts and pointers are thankfully welcomed!

Steven Miller
Monday, February 2, 2004

>Or am I overly paranoid?

I would say yes. The path/filename checking and meta data table would do fine in my oppinion. To run validations on individual tables, querying the data dictionary to see if all columns are the correct types and so forth will probably result in a performance hit. Also if the database schema is faulty (missing tables/wrong datatypes) there is no easy way to automatically recover from DDL related errors anyway.

Patrik
Monday, February 2, 2004

Thanks for the advice. I try to be too pessimistic rather than too optimistic only to avoid errors. But I guess you are right and I can save some work today. Yay!

Here I am, wearing my tinfoil hat, my "Trust no one!" poster right behind me...

Steven Miller
Monday, February 2, 2004

If you want to work late today anyway, you could always add a switch to your program to perform data model integrity tests. I would not do it "on the fly" during normal application operation due to performance concerns.

Then you can tell your users to just run

foo.exe --validateschema

or somesuch, and then mail you the log file so you can check to see if some tables or whatever became corrupt or were tampered with.

Patrik
Monday, February 2, 2004

A one time check at program launch shouldn't hurt performance, if you're not checking data. That is, check the directory, file, and table.

Also, I've found it useful in my apps to verify that it is an SQLite database (it appears to just overwrite it in that case), so I've found it useful to pull in part of the file header, which is ascii for the first few bytes on an SQLite database.

If you are distributing on linux, or any platform where the user would have a SQLite installation you didn't provide, you should check the SQLite version, because that's not stored in the database, and the DBMS has advanced very quickly, so a year old version may be missing features you use. Most databases I wouldn't worry about, but some things, like the INTEGER PRIMARY KEY thing are important, and has only been around since 2.2, as I recall.

Mike Swieton
Monday, February 2, 2004

  Recent Topics

  Fog Creek Home