Re: [sqlite] Relations in sqlite and checking consistency of existing data base

[Agrawal, Manish]

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kees Nuyt
Sent: Monday, February 06, 2012 3:29 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Relations in sqlite and checking consistency of existing 
data base

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

Visual Studio can do it using System.Data.SQLite. I have not had much luck 
getting it to write to the scheme, but it can read and show the relationships 
very well.

Thanks
Manish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Relations in sqlite and checking consistency of existing data base

[Kees Nuyt]

On Sun, 5 Feb 2012 18:39:40 +0100, Marek Staniewski
 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

[sqlite] Relations in sqlite and checking consistency of existing data base

[Marek Staniewski]

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)

2.   Let say I would like to check consistency/errors of my sqlite data 
base. Is there any plan how to star with it. Interesting is for example how to 
search for lost unused records

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.

4.   Is it difficult to switch my program from simple db use to 
client-server version. Does it need may work?

Marek Staniewski



AMSORT Sp. z o.o.
01-309 Warszawa,  ul. Okr?towa 70 B, Poland
tel: +48 22 664 38 87, fax: +48 22 664 38 89., 
www.amsort.com




Tre?? tej wiadomo?ci jest poufna i prawnie chroniona. Odbiorc? mo?e by? jedynie 
jej adresat z wy??czeniem dost?pu os?b trzecich. Je?eli nie jeste? adresatem 
niniejszej wiadomo?ci, jej rozpowszechnianie, kopiowanie, rozprowadzanie lub 
inne dzia?anie o podobnym charakterze jest prawnie zabronione i mo?e by? 
karalne. Je?eli wiadomo?? ta jest adresowana do naszych Klient?w, jakakolwiek 
opinia lub porada w niej zawarta podlega warunkom wyra?onym w odpowiedniej 
umowie o ?wiadczenie us?ug na rzecz Klienta przez AMSORT Sp. z o.o.
Niniejsza wiadomo?? nie stanowi oferty w rozumieniu Kodeksu cywilnego.

The information in this email is confidential and may be legally privileged. It 
is intended solely for the addressee. Access to this email by anyone else is 
unauthorized. If you are not the intended recipient, any disclosure, copying, 
distribution or any action taken or omitted to be taken in reliance on it, is 
prohibited and may be unlawful. When addressed to our client, any opinion or 
advice contained in this email are subject to the terms and conditions 
expressed in the governing AMSORT Sp. z o.o. client engagement letter.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users