On Sun, 5 Feb 2012 18:39:40 +0100, Marek Staniewski
<[email protected]> wrote:
>Hello
>
>I am a kind of newbie in sqlite world.
>I have several questions about sqlite data base:
>
>1. Is there any way to read relations inside
> some sqlite data base (some way of deduction from data base contents)
Not in the engine itself. Probably such tools can be found. I would
guess most of htem are commercial (not for free).
>2. Let say I would like to check consistency/errors of my sqlite
> data base. Is there any plan how to start with it.
> Interesting is for example how to search for lost unused records
Define REFERENCES constraints (foreign keys) for child tables to
reference the primary key of parent tables.
Example (untested):
Check with a join, something like:
CREATE TABLE Parent (
pk INTEGER PRIMARY KEY,
tx TEXT
);
CREATE TABLE Child (
pk INTEGER PRIMARY KEY,
fk INTEGER REFERENCES Parent(pk)
ON UPDATE CASCADE ON DELETE CASCADE,
tx TEXT
);
-- some correct and false insert statements here
-- left as an exercise for the reader.
SELECT 'No parent with pk ' AS diag1, C.fk,
' for child with pk ' AS diag2, C.pk
FROM Child AS C
LEFT OUTER JOIN Parent AS P ON P.pk = C.fk
WHERE P.pk IS NULL
;
(pk=primary key column, fk=foreign key column)
Enforce referential integrity by switching foreign keys on on every
connection you make(!)
PRAGMA foreign_keys=ON;
>3. Assume I have made some program using sqlite
> data base. This is used by my users, but they may use
> different versions of my program. How to update such
> program for checking database so it may check adequately
> to db version? in case there are some changes in data base.
Database version is reflected by
PRAGMA schema_version;
which is incremented by sqlite.
Apart from that you can maintain a user version:
PRAGMA user_version[=value];
and use that in your application.
http://www.sqlite.org/pragma.html#pragma_schema_version
>4. Is it difficult to switch my program
> from simple db use to client-server version. Does it need much work?
It may require a lot of work. You can manage it a little bit by staying
as close as possible to a common SQL standard, and by abstracting the
database access functions into classmethods or functions outside the
application logic.
Hope this helps.
>Marek Staniewski
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
