Re: [sqlite] SQL Update while Stepping through Select results
> Unfortunately I oversimplified my explanation, and the processing > that is required is somewhat more complex. Perhaps you could give more detail about your data. It seems to me that you should restructure your data so it is "atomic", ie so that each bit of data appears in its own row in a related table, rather than having multiple values in one row. As you're probably aware, this is a fundamental of database design. Otherwise you're unscrambling the egg, which is very error prone and inefficient. Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Update while Stepping through Select results
Saturday, September 6, 2008, 10:03:00 AM, you wrote: IT> "Neville Franks" <[EMAIL PROTECTED]> wrote IT> in message news:[EMAIL PROTECTED] >> At present the clm data is a list of one or more numeric id's >> separated by either a space or comma. I need to update this so the >> id's are always comma separated. IT> update tableName set field=replace(field, ' ', ','); Thanks Igor. Unfortunately I oversimplified my explanation, and the processing that is required is somewhat more complex. However this opened my eyes to the possibility of writing a user defined function to use with update. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
Gerry Snyder wrote: > I am not sure what all this means when taken together. > > It sounds as if defining id as shown above is unnecessary, since it is > just an alias for ROWID, and if one is stable the other has to be. Correct? > > So now I can't interpret "ROWID can change" as meaning anything other > than "the ROWID supplied by SQLite while doing an insertion, if none is > supplied by the user, can change at any time." Is this correct? > > My little Tcl/Tk routines for displaying and managing SQLite files have > always assumed ROWID is a safe stable way of determining a row. Have I > missed something? (again?) If you want to truly be safe, then *never* use ROWID (that is, ignore its existence), and *always* declare your own primary key, even if that is just an integer, and always reference your explicit primary key rather than ROWID. This way, what columns exist and their values are always controlled by you, and moreover your schemata would then be more portable between different DBMSs. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts search, quoting and sippet generation
Is there a common practice on quoting/sanitizing the snippets generated by the fts search? If I have "foo bar lee" in one field and search for bar, the snippet function will return "foo bar lee", which is obvious not what I want and can easily be used for cross-site scripting. I can think about two strategies to avoid this: 1) The first one is to strip out all tags before inserting new text into the virtual table. I don't want to do this, because I have a pure text interface too, where the tags in results won't hurt. 2) Specify two random strings to the snippet function for quoting, eg, snippet(poem, '23453242', '435345345', '...') and use them to split the snippet afterwards and do the sanitizing then. Any other solutions? Maybe the sanitize function has a hidden feature for this? Regards Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
D. Richard Hipp wrote: > On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: > > >> On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> >> wrote: >> >>> But the documentation states the ROWID >>> can change at any time so I'm not sure what I can do safely. >>> >> Do this: >> >> CREATE TABLE t ( >> id INTEGER PRIMARY KEY, >> a TEXT NOT NULL, >> b INTEGER NOT NULL, >> UNIQUE (a, b) >> ); >> >> (a,b) will be just as unique as in the first case, but now you can use >> id as a stable alias for rowid >> > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. > I am not sure what all this means when taken together. It sounds as if defining id as shown above is unnecessary, since it is just an alias for ROWID, and if one is stable the other has to be. Correct? So now I can't interpret "ROWID can change" as meaning anything other than "the ROWID supplied by SQLite while doing an insertion, if none is supplied by the user, can change at any time." Is this correct? My little Tcl/Tk routines for displaying and managing SQLite files have always assumed ROWID is a safe stable way of determining a row. Have I missed something? (again?) Thanks, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sat, Sep 6, 2008 at 7:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: >> As a bonus, if the implementation of SQLite changes, SQL itself won't, >> so id will _still_ be a valid integer primary key, even if the >> implementation detail of rowid changes. > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. :-). I'm just trying to discourage relying on implementation details when you can easily enough be explicit (in this case, there's no penalty for doing so). -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sat, Sep 6, 2008 at 10:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > > so id will _still_ be a valid integer primary key, even if the > > implementation detail of rowid changes. > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. I recall there was an issue a while back where the rowid of a record could change if the table was vacuumed and it did not have an explicit INTEGER PRIMARY KEY field. If that's still (or ever was, assuming I'm remembering correctly) the case, then having an explicit INTEGER PRIMARY KEY is important if you want to reference the single-field rowid (by whatever name) and assume it won't change over the life of the record. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: > On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> > wrote: >> I have a table where the primary key consists of two columns. I'm >> doing this >> because these two columns combined must be unique, while each >> column is not >> unique on its own. >> >> Because of the increased complexity of a dual-column primary key >> for some >> operations, I'd like to use ROWID. But the documentation states the >> ROWID >> can change at any time so I'm not sure what I can do safely. > > Do this: > > CREATE TABLE t ( > id INTEGER PRIMARY KEY, > a TEXT NOT NULL, > b INTEGER NOT NULL, > UNIQUE (a, b) > ); > > (a,b) will be just as unique as in the first case, but now you can use > id as a stable alias for rowid. There will be the same number of > btrees (one keyed by rowid/id for the table, one for the index on > a,b), and things should take the same amount of space (since id is an > alias for rowid, not a new column). > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > so id will _still_ be a valid integer primary key, even if the > implementation detail of rowid changes. I promise that INTEGER PRIMARY KEY will always be an alias for the rowid in SQLite. This will not change. > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test suite failures on 3.6.2 on x86_64
When building sqlite 3.6.2 on Mandriva Linux Cooker x86_64, some tests fail: 12 errors out of 23377 tests Failures on these tests: lookaside-1.4 lookaside-1.5 memsubsys1-2.3 memsubsys1-2.4 memsubsys1-3.2.4 memsubsys1-4.3 memsubsys1-4.4 memsubsys1-5.3 memsubsys1-6.3 memsubsys1-6.4 memsubsys1-7.4 memsubsys1-7.5 on Mandriva Cooker i586 there is no problem at all, and with sqlite 3.6.1 on x86_64 the testsuite succeeds too, so this seems like a regression. Any idea what could be wrong? Complete x86_64 build log: http://artipc10.vub.ac.be/files/log.sqlite3.gz -- Frederik Himpe ___ 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
Re: [sqlite] Help Using RowID
Scott Hess wrote: > > CREATE TABLE t ( > id INTEGER PRIMARY KEY, > a TEXT NOT NULL, > b INTEGER NOT NULL, > UNIQUE (a, b) > ); > > (a,b) will be just as unique as in the first case, but now you can use > id as a stable alias for rowid. There will be the same number of > btrees (one keyed by rowid/id for the table, one for the index on > a,b), and things should take the same amount of space (since id is an > alias for rowid, not a new column). > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > so id will _still_ be a valid integer primary key, even if the > implementation detail of rowid changes. > Many thanks for the speedy response. I need a little while to absorb this and consider all the ramifications. But my initial thought is that this solves the issues in an easy and stable way. Thanks again. -- View this message in context: http://www.nabble.com/Help-Using-RowID-tp19343483p19343677.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> wrote: > I have a table where the primary key consists of two columns. I'm doing this > because these two columns combined must be unique, while each column is not > unique on its own. > > Because of the increased complexity of a dual-column primary key for some > operations, I'd like to use ROWID. But the documentation states the ROWID > can change at any time so I'm not sure what I can do safely. Instead of doing this: CREATE TABLE t ( a TEXT NOT NULL, b INTEGER NOT NULL, PRIMARY KEY (a, b) ); Do this: CREATE TABLE t ( id INTEGER PRIMARY KEY, a TEXT NOT NULL, b INTEGER NOT NULL, UNIQUE (a, b) ); (a,b) will be just as unique as in the first case, but now you can use id as a stable alias for rowid. There will be the same number of btrees (one keyed by rowid/id for the table, one for the index on a,b), and things should take the same amount of space (since id is an alias for rowid, not a new column). As a bonus, if the implementation of SQLite changes, SQL itself won't, so id will _still_ be a valid integer primary key, even if the implementation detail of rowid changes. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help Using RowID
I have a table where the primary key consists of two columns. I'm doing this because these two columns combined must be unique, while each column is not unique on its own. Because of the increased complexity of a dual-column primary key for some operations, I'd like to use ROWID. But the documentation states the ROWID can change at any time so I'm not sure what I can do safely. The first operation I have in mind is to allow the user to edit a record with the option of modifying one of the columns that make up the primary key. The WHERE clause of my UPDATE statement would be far simpler if I could use the ROWID instead of two keys where one could've changed. But hwat if the ROWID has changed? The second operation I have in mind is to populate a Windows listview control with the contents of a table and store a unique ID in the DataItem of each row in the control. But if I use the ROWID as this ID, how do I know it will still be valid if I need to reference the row in the database. Can anyone offer any advice here? Thanks! -- View this message in context: http://www.nabble.com/Help-Using-RowID-tp19343483p19343483.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
