On Sun, 5 Feb 2012 18:39:40 +0100, Marek Staniewski <marek.staniew...@amsort.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users