Re: [sqlite] Db design question (so. like a tree)
I am not familiar with breeding business. My suggestion comes from a very different problem that i solved recently. The challenge was to describe a tree and to find the path to the root starting from any leaf or intermediate node. How to do? 1) The entity supported by the node shall support at least one relative precedence criterion which depends on the nature of the entity. Then we can specify a Node_Table(Node Primary Key,,PrecedenceCriterion) 2) Populate the Node_Table (at least 2 linkable nodes at starting time) 3) Complete the links using a selection filter mainly based on precedence (we assume that the user has the expertise to pick the right choice) 4) Repeat 2) and 3) as far as necessary If only one-to-one links are required they can be included in the Node_Table model. If one-to-several links are necessary a separate Link_Table(LinkType,OwnerNode,LinkedNode) is recommended. (for example Link types may be Father,Mother,Clone) John Machin a écrit : > On 5/06/2009 5:27 PM, Francis GAYREL wrote: > >> To build a consistent oriented tree we need to associate to the nodes a >> ranking property such as the birthdate (or any precedence criterion). >> Therefore the ancestor of someone is to be selected among older ones. >> > > "Ancestor" is a *derived* relationship, not something you'd wish to > store in the database, and is quite irrelevant at data-entry time. Don't > you mean "parent"? > > Let me get this straight: the user is entering the details of animal X > who was born yesterday and has to input somehow the identity of the > mother and of the father. > > >> To make the ancestor allocation more easy the ancestor's list may be >> filtered on birthdate credibility. >> > > So your plot is, (e.g. for the father) to show a list of all male > animals who are in some credible-parenthood age range on (say) a > drop-down list, and the user selects one, hopefully not at random. Is > that right? > > I would imagine in a planned targeted organised animal breeding program > that the mother-to-be and father-to-be are recorded at the time of > impregnation, and the identities are established from ear-tags, embedded > chips, photographs, etc and the credible-parenthood test is applied then > [note: test, NOT input selection method] and all of the above is > confirmed at birth. > > >> The ranking property eliminates the circular link concern. >> > > Indeed, but you have to use it properly to eliminate other data > integrity concerns :-) > > HTH, > > John > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
To build a consistent oriented tree we need to associate to the nodes a ranking property such as the birthdate (or any precedence criterion). Therefore the ancestor of someone is to be selected among older ones. To make the ancestor allocation more easy the ancestor's list may be filtered on birthdate credibility. The ranking property eliminates the circular link concern. Jan a écrit : > Hi Mark, > > I think that wont work: > > Scenario: A calf is born from a mother within your flock but from a > father outside. The father appears for the first time and you are not > able to gather information on his father (or grand-grand father). > Therefore his father is NULL. But later you get the information on his > father and add it to the animal list: The id of the fathers father is > then greater then the id of his grandchild (the calf). > I could start the id initially with 10 to allocate <10 ids in > theses cases, but I am unsure if this is a good way to start. > > Mark Hamburg schrieb: > >> One of the questions that I believe was raised but not answered on >> this thread was how to make sure that you don't have circular >> relationships particularly given that SQLite isn't good at scanning >> the tree. If you can control the id's then simply require that the id >> of the child be greater than the id's of the parents. >> >> Mark >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use PRIMARY KEY AUTOINCREMENTwhenmodellingweakentities?
Can I suggest another way that I adopted a long time ago to solve this
kind of issue?
Forget the autoincrement function included in your RDBM and use a
customized one more or less similar to the one I developped in my ZQLib
(adapted from ZEOSDBO) for Delphi (previously I did the same thing with
MS-Access):
{==Primary key management
Primary Key is a shaped string which consists of prefix+serial number
PkSize is the width of the serial number
Const NullString='';
M_ZQLite is a module hosting various ZEOSDBO objects, especially ZQread
which is a TZReadOnlyQuery
PrimKeyZero, PrimKeyMax and LastPrimKey may be private.
}
Function PrimKeyZero(PkSize:integer):string; //lowest serial number value
var i:integer;
begin
Result:=NullString;
For i:=1 to PkSize Do Result:=Result+'0';
end;
Function PrimKeyMax(PkSize:integer):string; //highest serial number value
var i:integer;
begin
Result:=NullString;
For i:=1 to PkSize Do Result:=Result+'9';
end;
Function LastPrimKey(Table, PkField,Prefix: String;var
PkVal:string):boolean;
begin
Result:=False;
With M_ZQLite Do
BEGIN
ZQread.SQL.Clear;
ZQread.SQL.Add('SELECT MAX('+PkField +') FROM '+ Table +
' WHERE '+ PkField + ' LIKE '+QuotedStr(Prefix+'%')+';');
Try
ZQread.Open;
if ZQread.IsEmpty
Then
PkVal:=NullString
else PkVal:=ZQread.Fields[0].AsString;
Result:=True;
Finally
ZQread.Close;
End;
END;
End;
Function NextPrimKey(Table, PkField,Prefix: String;PkSize:integer;var
PkNext:string):boolean;
var
LastK: String;
N:longint;
code:integer;
Begin
Result:=False;
If not LastPrimKey(Table, PkField, Prefix,LastK) Then exit;
If LastK=NullString
Then Begin
PkNext:=Prefix + PrimKeyZero(PkSize);
Result:=True;
Exit;
End
Else LastK := Copy(LastK,Length(Prefix)+1, PkSize);
If LastK =PrimKeyMax(PkSize)
Then begin
ShowMessage(Glob_PrimKeyOver);
exit;
End;
Val(LastK,N,code);
PkNext:= Prefix + Format('%*.*d',[PkSize,PkSize,N+1]);
Result:=True;
End;
Assuming that the Civil Work DB has to manage les than100 buildings,
each one containing less than 1 rooms.
When adding a building:
if NextPrimKey(T_Building, 'BuildingID',NullString,2,NextBuilding)
then ZQrun('INSERT INTO T_Building(BuildingID,.)
VALUES('+QuotedStr(NextBuilding)+',;);');
When adding a room:
if NextPrimKey(T_Room, 'RoomID',CurrentBuilding,4,NextRoom)
then ZQrun('INSERT INTO T_Room(RoomID,.)
VALUES('+QuotedStr(NextRoom)+',;);');
I would point out that this approach permits meaningfull keys which
offer a lot of possibilities.
Igor Tandetnik a écrit :
> "Andreas Ntaflos"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
>> On Friday 05 September 2008 23:59:25 Igor Tandetnik wrote:
>>
>>> Define "weak entity". Precisely what properties does it have that are
>>> not represented in the model?
>>>
>> A weak entity has no possible combination of attributes that would
>> uniquely identify it in the relational model. It's existence depends
>> on another entity. The building-room-example illustrates this nicely.
>> There's nothing to hinder two separate, uniquely identifiable
>> buildings to have the same room number scheme, so to identify a
>> single room the key of the building is needed in combination with the
>> room's numer.
>>
>
> Well, in this case you've violated your "conceptual model" the moment
> you decided to assign unique auto-generated numbers to your rooms, in a
> way completely divorced from how numbers would be assigned to real rooms
> in real buildings. As the room is no longer a "weak entity" by your
> definition, of course you have difficulty modeling it as one. Why again
> are you surprised?
>
>
>> The property the desired model has that are not represented in this
>> model we discuss is that a room's primary key does not contain a
>> reference to the primary key of the owning entity. It is globally and
>> uniquely identifiable without ever looking at the building.
>>
>
> And that is bad because...?
>
>
>> This is a practical consequence and thus the model you propose is
>> possible and probably even feasible, but it is no longer the model I
>> wanted to implement. Plus I don't really see why it would be wrong to
>> use an auto-incrementing key for the roomID while keeping the tuple
>> (roomID, buildingID) as a primary key.
>>
>
> What's the point? What's so special about PRIMARY KEY that you
> absolutely must have one? All it does is guarantee uniqueness - but you
> already have that. You are getting hung up on the syntax of your CREATE
> TABLE statement, rather than on the properties of the underlying
> relation.
>
> Let's put it this way: are there any queries that you can't run against
> the current model, but would be able to run against your hypothetical
> ideal model? And if there are no such queries, what's the difference
> between the two?
>
>
>> It s
Re: [sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?
I met the same issue. REPLACE is the right way to override it.
Assuming C1 is the primary key or at least unique,
instead of
UPDATE T1 INNER JOIN T2 ON T1.C1=T2.C1
SET T1.C2=T2.C2
WHERE T1.C2<>T2.C2;
you can write:
REPLACE INTO T1
SELECT T1.C1,T2.C2,C3,C4 FROM T1 INNER JOIN T2 ON T1.C1=T2.C1
WHERE T1.C2<>T2.C2;
Beware, SELECT shall include the full T1 column set
Samuel Neff a écrit :
> I'm trying to update records in one table based on joined data in another
> table. MSSQL has support for a "FROM" clause within an UPDATE statement
> which makes this type of thing very easy. Is there any equivalent in
> SQLite? The only way I've found to achive the same results is to use a
> subselect within the SET clause of the UPDATE statement, but that requires
> duplicating the WHERE clause within the subselect which is a lot of extra
> typing and I'm sure a lot of extra work for SQLite.
>
> MSSQL:
>
> UPDATE T1
> SET
> A = T2...,
> B = T2...,
> C = T2...,
> FROM T1 INNER JOIN T2 ON
>
> SQLite:
>
> UPDATE T1
> SET
> A = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> B = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> C = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> ...
>
> Here are samples of equivalent code in MSSQL and SQLite. Is there a way to
> simplify the UPDATE statement in the SQLite code?
>
> I'm not replacing the target row entirely, I don't think INSERT OR REPLACE
> will work in this scenario.
>
> Thanks,
>
> Sam
>
> --
> -- MSSQL
> --
>
> CREATE TABLE #T1(
> ID INTEGER PRIMARY KEY IDENTITY,
> A VARCHAR(100),
> B VARCHAR(100),
> C VARCHAR(100));
>
> CREATE TABLE #T2(
> ID INTEGER PRIMARY KEY IDENTITY,
> A VARCHAR(100),
> B VARCHAR(100),
> C VARCHAR(100));
>
> INSERT INTO #T1 VALUES ('a1', 'b1', 'c1');
> INSERT INTO #T1 VALUES (NULL, 'b2', 'c2');
> INSERT INTO #T1 VALUES ('a3', NULL, 'c3');
> INSERT INTO #T1 VALUES ('a4', 'b4', NULL);
>
> INSERT INTO #T2 VALUES ('A1', 'B1', 'C1');
> INSERT INTO #T2 VALUES ('A2', NULL, 'C2');
> INSERT INTO #T2 VALUES ('A3', 'B3', NULL);
> INSERT INTO #T2 VALUES (NULL, 'B4', 'C4');
>
> SELECT * FROM #T1;
> SELECT * FROM #T2;
>
> UPDATE#T1
> SETA = COALESCE(#T1.A, #T2.A),
> B = COALESCE(#T1.B, #T2.B),
> C = COALESCE(#T1.C, #T2.C)
> FROM#T1 INNER JOIN #T2 ON #T1.ID = #T2.ID;
>
> SELECT * FROM #T1;
>
> DROP TABLE #T1;
> DROP TABLE #T2;
>
> --
> -- SQLite
> --
>
> CREATE TEMP TABLE T1(
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> A TEXT,
> B TEXT,
> C TEXT);
>
> CREATE TEMP TABLE T2(
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> A TEXT,
> B TEXT,
> C TEXT);
>
> INSERT INTO T1 VALUES (NULL, 'a1', 'b1', 'c1');
> INSERT INTO T1 VALUES (NULL, NULL, 'b2', 'c2');
> INSERT INTO T1 VALUES (NULL, 'a3', NULL, 'c3');
> INSERT INTO T1 VALUES (NULL, 'a4', 'b4', NULL);
>
> INSERT INTO T2 VALUES (NULL, 'A1', 'B1', 'C1');
> INSERT INTO T2 VALUES (NULL, 'A2', NULL, 'C2');
> INSERT INTO T2 VALUES (NULL, 'A3', 'B3', NULL);
> INSERT INTO T2 VALUES (NULL, NULL, 'B4', 'C4');
>
> SELECT * FROM T1;
> SELECT * FROM T2;
>
>
> -- here's the ugly statement I'd like to simplfy
>
> UPDATET1
> SETA = (
> SELECT COALESCE(InnerT1.A, T2.A)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> ),
>B = (
> SELECT COALESCE(InnerT1.B, T2.B)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> ),
>C = (
> SELECT COALESCE(InnerT1.C, T2.B)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> )
> ;
>
> SELECT * FROM T1;
>
> DROP TABLE T1;
> DROP TABLE T2;
>
>
> -
> We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
> the Washington D.C. Contact [EMAIL PROTECTED]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?
Why two tables? Define a unique table and redefine your tables as views.
alp a écrit :
> Hello,
>
> I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID of the
> last inserted row from table TBL_OBJECTS to insert a new row in TBL_TAGS
> table with the ID_OBJECT foreign key set to the ROWID value from the
> precedent table.
>
> This is the tables definition:
>
> CREATE TABLE TBL_OBJECTS (
> IDinteger PRIMARY KEY NOT NULL,
> DATA text,
> PATH text
> );
>
> CREATE TABLE TBL_TAGS (
> ID integer PRIMARY KEY NOT NULL,
> ID_TAG_TYPE integer NOT NULL,
> ID_OBJECTinteger NOT NULL,
> TAG_DATA text NOT NULL
> );
>
>
> My solution is:
>
> INSERT INTO TBL_OBJECTS
> (DATA,
> PATH)
> VALUES ('val1', 'val2');
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, 2, 'a'); --doesn't count what value is inserted for ID_OBJECT
> as it will be changed in the next statement
>
> UPDATE TBL_TAGS SET ID_OBJECT = (SELECT MAX(ROWID) FROM TBL_OBJECTS)
> WHERE ROWID = (SELECT MAX(ROWID) FROM TBL_TAGS);
>
> but I am sure there is a less complex one that you can point out to me.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
