Re: [sqlite] How to use PRIMARY KEY AUTOINCREMENTwhenmodellingweakentities?
"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 seems that there are two choices available: go with your model, > which is simpler and certainly more efficient, but loses an aspect of > the desired model (namely that rooms are by themselves not uniquely > identifiable). Again, it's not the model that loses the property - it's your initial decision of using unique room numbers. After that, you just insist on a syntactical game of make-believe, for no other reason that to make yourself feel better, as far as I can tell. You say: I want to assign unique numbers to rooms, but I want to pretend the rooms are not uniquely identifiable by those unique numbers. Does this really make sense to you? Igor Tandetnik ___ 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
"Neville Franks" <[EMAIL PROTECTED]> wrote 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. update tableName set field=replace(field, ' ', ','); Igor Tandetnik ___ 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 AUTOINCREMENT when modellingweak entities?
"Andreas Ntaflos" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > CREATE TABLE Room ( >room_number INTEGER PRIMARY KEY AUTOINCREMENT, >building_id INTEGER CONSTRAINT fk_room_building_id REFERENCES > Building(building_id) ON DELETE CASCADE > ); > > CREATE UNIQUE INDEX idx_building_room ON TABLE Room(room_number, > building_id); This index has no benefit whatsoever. It only adds performance overhead. Igor Tandetnik ___ 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 AUTOINCREMENT whenmodellingweakentities?
On Friday 05 September 2008 23:59:25 Igor Tandetnik wrote: > Andreas Ntaflos > > <[EMAIL PROTECTED]> wrote: > > My problem statement remains partially valid, though. > > Auto-incrementing the roomID in the Room table would make each room > > have its very own room number but the primary key still would need to > > consist of both buildingID and roomID > > Why? Because only then the room entity is truly modelled as a weak entity. Otherwise it is not weak. > > or else the modelling of a room > > as a weak entity wouldn't work correctly. > > 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. That's what the theory on relational modelling defines. And it makes sense, too. You could work around that by having all rooms of all buildings in the city (or the country or the world) in a single table and ignoring the existential dependency on the building that contains the room. This would of course not scale well and not correctly represent the real world. 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. > > The buildingID is still a > > foreign key so SELECTing all rooms of one particular building is > > still possible but that's only part of the conceptual model. > > In what way having a unique roomID violates your conceptual model? As I described above, the model no longer represents the existential dependency of the room on the building it is in. > > I suppose it's not something the authors of books on database systems > > would approve of. > > To the best of my knowledge, they are not the ones paying my salary, so > personally I'm not too worried about their disapproval. Of course not, and when practicality dictates it there's no reason not to ignore a few theoretical concepts as long as the results stay favourable. Nevertheless I want to try to keep the theory in mind when creating the model I need and it happens that weak entities are quite an integral part of it. That's why I asked the question in the first place, although, as I explained, I didn't realise that auto-incrementing a key value would make it unique in and on itself. 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. Granted, no roomID could be duplicated this way, anyway, but I'd prefer to implement the desired model as faithfully as possible while still being a lazy typist (this means implementing as little magic as possible to retrieve the next bigger number to use as room number). It seems that there are two choices available: go with your model, which is simpler and certainly more efficient, but loses an aspect of the desired model (namely that rooms are by themselves not uniquely identifiable). Or implement the model the way Nicolas proposed [1] (provided I understood it correctly). This would include some (database-internal) redundancy but the property of the combined primary key for a room in a building could be kept valid. In PostgreSQL the latter model could be implemented like this: CREATE TABLE Building ( buildingID SERIAL8 PRIMARY KEY ); CREATE TABLE Room ( buildingID BIGINT, room_number SERIAL8, FOREIGN KEY (buildingID) REFERENCES Building(buildingID) ON DELETE CASCADE, PRIMARY KEY (buildingID, room_number) ); This is what I initially wanted to accomplish in SQLite. I thought I could substitute SERIAL8 (which is really "just" some magic with sequences and nextval()) for AUTOINCREMENT, which is not possible. I guess SERIAL is one step higher on the abstraction ladder than AUTOINCREMENT? Anyway, thanks for your help and the discussion :) Andreas [1] http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-September/005650.html -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ 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 AUTOINCREMENT when modellingweakentities?
On Saturday 06 September 2008 00:11:01 [EMAIL PROTECTED] wrote:
> I suspect somes confusion is at work here because the books you refer to
> which discuss this issue are likely referring to what is sometimes called
> 'natural keys' versus a key based on AUTOINCREMENTed numbers which have no
> direct correspondence to the object. Some entities have the property of
> serving as the own key (a 'natural' key), e.g, phone numbers. A table of
> phone numbers does not need a separate key from the phone number itself.
> However, room numbers (actual room numbers as seen and used in the real
> world) are not like this, hence the books describe room numbers as a
> 'weak' entity, where knowing the actual room number by itself is
> insufficient to specify the room in a multi-building database.
That's exactly what I was taught (or drilled in) in Data Modelling and
Database Systems 101 :)
> In the database schema, you can, as discussed in this thread, use an
> AUTOINCREMENT or SERIAL field in the rooms table to provide a unique ID
> number for each room. If your purpose is just to have a primary key in the
> database table, you are done. But if you want to store actual room numbers
> from real buildings, you need to store those numbers in a separate,
> non-unique field. Then you decide whether to use room+building as the
> 'natural' primary key, or a separate ('unnatural') AUTOINCREMENT value as
> a key.
This is what I failed to realise in the beginning. Thank you, too, for
clearing it up :) I want to keep the concept of rooms as weak entities intact
and need a way in SQLite to do it while still having the benefit of using an
auto-increment function. I believe Nicolas described a way of doing that [1].
> Thus, the confusion arises from the lack of distinction between a
> 'natural' primary key consisting of real room numbers and real building
> numbers, versus an internal primary key consisting of a serialized (but
> inherently meaningless) number. The database will do whatever you want,
> you need to decide which schema fits your needs.
I must confess I never really thought about primary keys any other way
than "natural". I am no DBMS designer; instead I rely on the DBMS to shield
me from as many internals as possible :) But then I have never before worked
with SQLite. One apparently works a bit closer to the internals here than in
PostgreSQL.
Anyway, thanks for clearing up the confusion a bit :)
Andreas
[1]
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-September/005650.html
--
Andreas Ntaflos
Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
___
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 AUTOINCREMENT when modelling weak entities?
On Friday 05 September 2008 22:43:15 Nicolas Williams wrote: > On Fri, Sep 05, 2008 at 09:31:43PM +0200, Andreas Ntaflos wrote: > > In the Room table the primary key is a combination of two attributes. How > > could I auto-increment the roomID? "roomID INTEGER AUTOINCREMENT" is > > syntactically incorrect and the AUTOINCREMENT keyword seems to cannot > > appear anywhere else. > > > > What is the SQLite-way to auto-increment when the AUTOINCREMENT keyword > > is not available? > > I think you should make the roomID the primary key, and autoincrement, > then create a UNIQUE INDEX for the Room table on the roomID and > buildingID columns. > > Finally, you already know you need to use triggers to enforce the > foreign key constraint. > > What does it matter, then, that you can't declare those two columns of > the Room table as PRIMARY KEY and get autoincrement for roomID? You can > get autoincrement behavior and primary key behavior as described above. Thank you, too, for your reply! Does creating a UNIQUE INDEX really emulate the behaviour of the following to (Postgre)SQL statements which are not supported in SQLite directly? CREATE TABLE Building ( building_id SERIAL8 PRIMARY KEY ); CREATE TABLE Room ( building_id BIGINT, room_number SERIAL8, FOREIGN KEY (building_id) REFERENCES Building(building_id) ON DELETE CASCADE, PRIMARY KEY (building_id, room_number) ); Would this look like this in SQLite: CREATE TABLE Building ( building_id INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE TABLE Room ( room_number INTEGER PRIMARY KEY AUTOINCREMENT, building_id INTEGER CONSTRAINT fk_room_building_id REFERENCES Building(building_id) ON DELETE CASCADE ); CREATE UNIQUE INDEX idx_building_room ON TABLE Room(room_number, building_id); That is exactly what I'll need to use, then. Is there anything else I need to take care of? Thank you very much! Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ 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
Hi Igor, Saturday, September 6, 2008, 8:27:01 AM, you wrote: IT> Neville Franks <[EMAIL PROTECTED]> wrote: >> I need to update a column in a set of rows in a table. For each row I >> need to extract the columns value, change it and update the row. >> >> My question is, is it valid to perform an SQL UPDATE inside a >> sqlite3_step() loop. IT> Yes. Thanks. >> Finally is there a better way to accomplish. IT> What is the nature of the change you need to perform? 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. -- 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] SQL Update while Stepping through Select results
Neville Franks <[EMAIL PROTECTED]> wrote: > I need to update a column in a set of rows in a table. For each row I > need to extract the columns value, change it and update the row. > > My question is, is it valid to perform an SQL UPDATE inside a > sqlite3_step() loop. Yes. > Finally is there a better way to accomplish. What is the nature of the change you need to perform? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.6.2 core dump again.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ken wrote: > Sqlite version 3.6.2, Amalgamation > Backtrace from an abort (6) You have memory corruption. valgrind will help you figure out the problem. A backtrace from after memory is corrupted isn't helpful :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIwbExmOOfHg372QQRAnDEAKDjnMgsw9tAWHWX/pT+n0Q1oLPfbgCePWEn ShAgEMWxnJI7zeDhiJu/DUw= =VIPD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Profiling
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hardy, Andrew wrote: > How ever the elapsed time in the profile always shows zero. > > Does any one have a guess at what I may have left out? The granularity of the timers on your operating system is greater than the amount of time it took to execute the query. In my tests for the profiling functionality I create a table containing 100,000 rows each with a random integer. I then find the maximum value with and without an index. You don't say what operating system you are using but are using a Windows mailer. SQLite uses the GetSystemTimeAsFileTime API on Windows to get the time. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIwbDhmOOfHg372QQRAiIFAJ43YFzUZU5OjRh7dOZSZIT01xp7owCfTUsp whtsGoLZvvRO80iXjqguDfM= =wEbR -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Update while Stepping through Select results
I need to update a column in a set of rows in a table. For each row I need to extract the columns value, change it and update the row. My question is, is it valid to perform an SQL UPDATE inside a sqlite3_step() loop. Or put another way will subsequent calls to sqlite3_step() behave correctly following an UPDATE. I assume so as this is a fairly basic operation. I have done a quick documentation search to no avail. Finally is there a better way to accomplish. Maybe an UPDATE with a TRIGGER using a user defined function. Thanks. -- 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] How to use PRIMARY KEY AUTOINCREMENT when modellingweakentities?
I suspect somes confusion is at work here because the books you refer to
which discuss this issue are likely referring to what is sometimes called
'natural keys' versus a key based on AUTOINCREMENTed numbers which have no
direct correspondence to the object. Some entities have the property of
serving as the own key (a 'natural' key), e.g, phone numbers. A table of
phone numbers does not need a separate key from the phone number itself.
However, room numbers (actual room numbers as seen and used in the real
world) are not like this, hence the books describe room numbers as a
'weak' entity, where knowing the actual room number by itself is
insufficient to specify the room in a multi-building database.
In the database schema, you can, as discussed in this thread, use an
AUTOINCREMENT or SERIAL field in the rooms table to provide a unique ID
number for each room. If your purpose is just to have a primary key in the
database table, you are done. But if you want to store actual room numbers
from real buildings, you need to store those numbers in a separate,
non-unique field. Then you decide whether to use room+building as the
'natural' primary key, or a separate ('unnatural') AUTOINCREMENT value as
a key.
Thus, the confusion arises from the lack of distinction between a
'natural' primary key consisting of real room numbers and real building
numbers, versus an internal primary key consisting of a serialized (but
inherently meaningless) number. The database will do whatever you want,
you need to decide which schema fits your needs.
Chris
On Fri, 5 Sep 2008, Andreas Ntaflos wrote:
> On Friday 05 September 2008 22:41:14 Igor Tandetnik wrote:
>> Andreas Ntaflos
>>
>> <[EMAIL PROTECTED]> wrote:
>>> On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote:
Andreas Ntaflos
> Naturally a room cannot be identified without a building so it is a
> weak entity (this seems to be the canonical example in all database
> books I've seen).
>
> I'd like to auto-increment the ID numbers in both tables but as far
> as I can see the AUTOINCREMENT keyword only works the way it is used
> in the Building table. That is, only "INTEGER PRIMARY KEY
> AUTOINCREMENT" is syntactically correct.
Wait: if you want roomID to be autoincrement, then it's going to be
unique (which, after all, is the point of autoincrement). Run it by
my again: why can't you identify a room without a building?
>>>
>>> Well a room's existence depends on the building in which it is.
>>> Different buildings can use the same room numbering scheme so in
>>> order to know which room we are looking at we need to know its owning
>>> building. Hence a room is a weak entity.
>>
>> If you expect rooms in different buildings to have the same ID, then you
>> can't use autoincrement for it anyway.
>
> I understand now what you mean. Somehow I failed to realise that
> auto-incrementing (whether using a SERIAL or AUTOINCREMENT) increments an
> attribute in the table regardless of what it is of which the table is a
> representation. There would not be any two rooms with the same room number,
> regardless of the building they are in.
>
>>> But it seems I misunderstood the point of AUTOINCREMENT. I am looking
>>> for something like PostgreSQL's SERIAL data type [1] so when creating
>>> new rooms I don't have to manually specify the roomID. Instead the
>>> next possible roomID should be chosen automatically when INSERTing.
>>
>> But then you won't have two rooms with the same ID, and there won't be
>> any problem making roomID a primary key.
>>
>> I don't see how you plan to have it both ways.
>
> It doesn't make much sense, yes. I am sorry for the confusion.
>
> My problem statement remains partially valid, though. Auto-incrementing the
> roomID in the Room table would make each room have its very own room number
> but the primary key still would need to consist of both buildingID and roomID
> or else the modelling of a room as a weak entity wouldn't work correctly. The
> buildingID is still a foreign key so SELECTing all rooms of one particular
> building is still possible but that's only part of the conceptual model.
>
> I suppose it's not something the authors of books on database systems would
> approve of. A weak entity's primary key has to include the primary key of its
> owner entity or else it isn't weak. Yet in the case of SQLite and
> AUTOINCREMENT this does not seem possible.
>
> Anyway, thanks for opening my eyes to the obvious :)
>
> Andreas
> --
> Andreas Ntaflos
> Vienna, Austria
>
> GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
> ___
> 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 AUTOINCREMENT whenmodellingweakentities?
Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > My problem statement remains partially valid, though. > Auto-incrementing the roomID in the Room table would make each room > have its very own room number but the primary key still would need to > consist of both buildingID and roomID Why? > or else the modelling of a room > as a weak entity wouldn't work correctly. Define "weak entity". Precisely what properties does it have that are not represented in the model? > The buildingID is still a > foreign key so SELECTing all rooms of one particular building is > still possible but that's only part of the conceptual model. In what way having a unique roomID violates your conceptual model? > I suppose it's not something the authors of books on database systems > would approve of. To the best of my knowledge, they are not the ones paying my salary, so personally I'm not too worried about their disapproval. Igor Tandetnik ___ 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 AUTOINCREMENT when modelling weakentities?
Andreas Ntaflos wrote: > > But it seems I misunderstood the point of AUTOINCREMENT. I am looking for > something like PostgreSQL's SERIAL data type [1] so when creating new rooms I > don't have to manually specify the roomID. Instead the next possible roomID > should be chosen automatically when INSERTing. > > What is the correct SQLite-way of doing this? > Well you could use a select to find the largest roomID the already exists in the building you are inserting into. insert into room values ( (select max(roomID) from room where buildingID = :theBuilding) + 1, :theBuilding); Now you only need to specify the building and sqlite will calculate the lowest unused room number in that building. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl 8.3 and SQLite 3.6.2
How can I use the Tcl extentions of SQL 3.6.2 with Tcl 8.3? ___ 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 AUTOINCREMENT when modellingweakentities?
On Friday 05 September 2008 22:41:14 Igor Tandetnik wrote: > Andreas Ntaflos > > <[EMAIL PROTECTED]> wrote: > > On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote: > >> Andreas Ntaflos > >> > >>> Naturally a room cannot be identified without a building so it is a > >>> weak entity (this seems to be the canonical example in all database > >>> books I've seen). > >>> > >>> I'd like to auto-increment the ID numbers in both tables but as far > >>> as I can see the AUTOINCREMENT keyword only works the way it is used > >>> in the Building table. That is, only "INTEGER PRIMARY KEY > >>> AUTOINCREMENT" is syntactically correct. > >> > >> Wait: if you want roomID to be autoincrement, then it's going to be > >> unique (which, after all, is the point of autoincrement). Run it by > >> my again: why can't you identify a room without a building? > > > > Well a room's existence depends on the building in which it is. > > Different buildings can use the same room numbering scheme so in > > order to know which room we are looking at we need to know its owning > > building. Hence a room is a weak entity. > > If you expect rooms in different buildings to have the same ID, then you > can't use autoincrement for it anyway. I understand now what you mean. Somehow I failed to realise that auto-incrementing (whether using a SERIAL or AUTOINCREMENT) increments an attribute in the table regardless of what it is of which the table is a representation. There would not be any two rooms with the same room number, regardless of the building they are in. > > But it seems I misunderstood the point of AUTOINCREMENT. I am looking > > for something like PostgreSQL's SERIAL data type [1] so when creating > > new rooms I don't have to manually specify the roomID. Instead the > > next possible roomID should be chosen automatically when INSERTing. > > But then you won't have two rooms with the same ID, and there won't be > any problem making roomID a primary key. > > I don't see how you plan to have it both ways. It doesn't make much sense, yes. I am sorry for the confusion. My problem statement remains partially valid, though. Auto-incrementing the roomID in the Room table would make each room have its very own room number but the primary key still would need to consist of both buildingID and roomID or else the modelling of a room as a weak entity wouldn't work correctly. The buildingID is still a foreign key so SELECTing all rooms of one particular building is still possible but that's only part of the conceptual model. I suppose it's not something the authors of books on database systems would approve of. A weak entity's primary key has to include the primary key of its owner entity or else it isn't weak. Yet in the case of SQLite and AUTOINCREMENT this does not seem possible. Anyway, thanks for opening my eyes to the obvious :) Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ 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 AUTOINCREMENT when modelling weak entities?
Andreas Ntaflos wrote: > > Naturally a room cannot be identified without a building so it is a weak > entity (this seems to be the canonical example in all database books I've > seen). > That is only true if you allow the same roomID to be used in multiple buildings (i.e. there exists a room 101 in building A, and a room 101 in building B). In this case, you do *not* want to auto increment the roomID values. Now you require the combination of the roomID and the buildingID to identify a particular room. HTH Dennis Cote ___ 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 AUTOINCREMENT when modellingweak entities?
Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Fri, Sep 05, 2008 at 09:31:43PM +0200, Andreas Ntaflos wrote: >> In the Room table the primary key is a combination of two >> attributes. How could I auto-increment the roomID? "roomID INTEGER >> AUTOINCREMENT" is syntactically incorrect and the AUTOINCREMENT >> keyword seems to cannot appear anywhere else. >> >> What is the SQLite-way to auto-increment when the AUTOINCREMENT >> keyword is not available? > > I think you should make the roomID the primary key, and autoincrement, > then create a UNIQUE INDEX for the Room table on the roomID and > buildingID columns. If roomID is already unique by itself, an index on (roomID, buildingID) is rather pointless. There can't be two records with the same (roomID, buildingID) because that would require two recors with the same roomID. Igor Tandetnik ___ 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 AUTOINCREMENT when modelling weak entities?
On Fri, Sep 05, 2008 at 09:31:43PM +0200, Andreas Ntaflos wrote: > In the Room table the primary key is a combination of two attributes. How > could I auto-increment the roomID? "roomID INTEGER AUTOINCREMENT" is > syntactically incorrect and the AUTOINCREMENT keyword seems to cannot appear > anywhere else. > > What is the SQLite-way to auto-increment when the AUTOINCREMENT keyword is > not > available? I think you should make the roomID the primary key, and autoincrement, then create a UNIQUE INDEX for the Room table on the roomID and buildingID columns. Finally, you already know you need to use triggers to enforce the foreign key constraint. What does it matter, then, that you can't declare those two columns of the Room table as PRIMARY KEY and get autoincrement for roomID? You can get autoincrement behavior and primary key behavior as described above. Nico -- ___ 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 AUTOINCREMENT when modelling weakentities?
On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote: > Andreas Ntaflos > > <[EMAIL PROTECTED]> wrote: > > this question is about the AUTOINCREMENT usage in SQLite when > > modelling weak entities. Suppose there are two tables 'Building' and > > 'Room': > > > > CREATE TABLE Building ( > >buildingID INTEGER PRIMARY KEY AUTOINCREMENT, > >... > > ); > > > > CREATE TABLE Room ( > >roomID INTEGER, > >buildingID INTEGER CONSTRAINT fk_room_building_id REFERENCES > > Building(buildingID), > >PRIMARY KEY(roomID, buildingID) > > ); > > > > CREATE TRIGGER statements follow to enforce the foreign key > > constraint. > > > > Naturally a room cannot be identified without a building so it is a > > weak entity (this seems to be the canonical example in all database > > books I've seen). > > > > I'd like to auto-increment the ID numbers in both tables but as far > > as I can see the AUTOINCREMENT keyword only works the way it is used > > in the Building table. That is, only "INTEGER PRIMARY KEY > > AUTOINCREMENT" is syntactically correct. > > Wait: if you want roomID to be autoincrement, then it's going to be > unique (which, after all, is the point of autoincrement). Run it by my > again: why can't you identify a room without a building? Thank you for the quick reply! Well a room's existence depends on the building in which it is. Different buildings can use the same room numbering scheme so in order to know which room we are looking at we need to know its owning building. Hence a room is a weak entity. But it seems I misunderstood the point of AUTOINCREMENT. I am looking for something like PostgreSQL's SERIAL data type [1] so when creating new rooms I don't have to manually specify the roomID. Instead the next possible roomID should be chosen automatically when INSERTing. What is the correct SQLite-way of doing this? Thanks, Andreas [1] http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ 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 AUTOINCREMENT when modelling weakentities?
Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > this question is about the AUTOINCREMENT usage in SQLite when > modelling weak entities. Suppose there are two tables 'Building' and > 'Room': > > CREATE TABLE Building ( >buildingID INTEGER PRIMARY KEY AUTOINCREMENT, >... > ); > > CREATE TABLE Room ( >roomID INTEGER, >buildingID INTEGER CONSTRAINT fk_room_building_id REFERENCES > Building(buildingID), >PRIMARY KEY(roomID, buildingID) > ); > > CREATE TRIGGER statements follow to enforce the foreign key > constraint. > > Naturally a room cannot be identified without a building so it is a > weak entity (this seems to be the canonical example in all database > books I've seen). > > I'd like to auto-increment the ID numbers in both tables but as far > as I can see the AUTOINCREMENT keyword only works the way it is used > in the Building table. That is, only "INTEGER PRIMARY KEY > AUTOINCREMENT" is syntactically correct. Wait: if you want roomID to be autoincrement, then it's going to be unique (which, after all, is the point of autoincrement). Run it by my again: why can't you identify a room without a building? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use PRIMARY KEY AUTOINCREMENT when modelling weak entities?
Hi list, this question is about the AUTOINCREMENT usage in SQLite when modelling weak entities. Suppose there are two tables 'Building' and 'Room': CREATE TABLE Building ( buildingID INTEGER PRIMARY KEY AUTOINCREMENT, ... ); CREATE TABLE Room ( roomID INTEGER, buildingID INTEGER CONSTRAINT fk_room_building_id REFERENCES Building(buildingID), PRIMARY KEY(roomID, buildingID) ); CREATE TRIGGER statements follow to enforce the foreign key constraint. Naturally a room cannot be identified without a building so it is a weak entity (this seems to be the canonical example in all database books I've seen). I'd like to auto-increment the ID numbers in both tables but as far as I can see the AUTOINCREMENT keyword only works the way it is used in the Building table. That is, only "INTEGER PRIMARY KEY AUTOINCREMENT" is syntactically correct. In the Room table the primary key is a combination of two attributes. How could I auto-increment the roomID? "roomID INTEGER AUTOINCREMENT" is syntactically incorrect and the AUTOINCREMENT keyword seems to cannot appear anywhere else. What is the SQLite-way to auto-increment when the AUTOINCREMENT keyword is not available? Pointers to relevant sections of the FM are most welcome. Thanks! Andreas -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Profiling
I am registering a trace function and a profile function and both aoppear to be getting correctly called back and the text looks in order. How ever the elapsed time in the profile always shows zero. Does any one have a guess at what I may have left out? Regards, Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Setting vatb->zErrMsg in vt_update
Hello,
I have a vtable implementation that wraps a storage layer of my own
using Sqlite 3.5.6 (which is excellent--kudos!).
If the user does an SQL INSERT statement but provides the wrong type
(according to my storage manager) for one of the values, I'd like to
report a descriptive error message. For example, if the user has a
table "foo", with one column, "bar", of type integer, and says:
sqlite> INSERT INTO foo VALUES ("Hello, world!");
I'd like sqlite to say:
Error: datatype mismatch: column "bar" of table "foo" is of type INTEGER.
Currently, my code looks something like this:
static int
vt_update(sqlite3_vtab *pVTab, int argc, sqlite3_value **argv,
sqlite3_int64 *pRowid)
{
...
for (int i = 2; i < argc; ++i) {
if (sqlite3_value_type(argv[i]) != my_table_types[i]) {
if (vtab->zErrMsg)
sqlite3_free(vtab->zErrMsg);
vtab->zErrMsg = sqlite3_mprintf("column %s of table %s is
of type %s", ...);
return SQLITE_MISMATCH;
}
...
}
}
When I try the example above, I get:
sqlite> INSERT INTO foo VALUES ("Hello, world!");
SQL error: datatype mismatch
The documentation at http://www.sqlite.org/cvstrac/wiki?p=VirtualTables says:
"The virtual table implementation can pass error message text to the
core by putting an error message string obtained from
sqlite3_mprintf() in zErrMsg. Prior to assigning a new value to
zErrMsg, the virtual table implementation should free any prior
content of zErrMsg using sqlite3_free(). Failure to do this might
result in a memory leak. The SQLite core will free and zero the
content of zErrMsg when it delivers the error message text to the
client application or when it destroys the virtual table."
That leads me to believe I should see my message ('column "bar" of
table "foo" is of type INTEGER') echoed to the user. Is that not the
case? Am I missing something obvious?
Thanks in advance for your help,
Sean
--
"We're borrowing money from China to buy oil from the Persian Gulf to
burn it in ways that destroy the planet. Every bit of that's got to
change." -- Al Gore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.6.2 core dump again.
Sqlite version 3.6.2, Amalgamation Backtrace from an abort (6) #0 0x2ad3186beb45 in raise () from /lib64/libc.so.6 (gdb) backtrace #0 0x2ad3186beb45 in raise () from /lib64/libc.so.6 #1 0x2ad3186c00e0 in abort () from /lib64/libc.so.6 #2 0x2ad3186f6fbb in ?? () from /lib64/libc.so.6 #3 0x2ad3186fc21d in ?? () from /lib64/libc.so.6 #4 0x2ad3186fc2fc in ?? () from /lib64/libc.so.6 #5 0x2ad3186fe535 in ?? () from /lib64/libc.so.6 #6 0x2ad3186ffc0c in malloc () from /lib64/libc.so.6 #7 0x004b5822 in sqlite3MemMalloc () #8 0x0047a7e1 in mallocWithAlarm () #9 0x00495656 in balance_nonroot () #10 0x004965ee in balance () #11 0x00496abf in sqlite3BtreeInsert () #12 0x004b032a in sqlite3VdbeExec () #13 0x004a56e8 in sqlite3_step () #14 0x00463689 in ixsqlt_stmt_exec (sqltc=0x82ca88) at ix_sqlite3.c:2016 =Output from stderr from system libs== *** glibc detected *** prgname: corrupted double-linked list: 0x2c068cd0 *** === Backtrace: = ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get declared Datatype of SQLite Virtual Table
"paul schindler" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> sqlite3_column_decltype("select amount from myvirtualtable", 0)
> returns "INTEGER" -> good!
>
> BUT:
>
> sqlite3_column_decltype("select sum(amount) from myvirtualtable", 0)
> returns NULL -> very bad!
What did you expect? Only column names have declared type. Expressions
don't, whether they use aggregate functions or otherwise.
> Is there a possibility to get the declared datatype of an aggregate
> function?
Aggregate function is not declared, so "declared datatype" is
meaningless for it.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Get declared Datatype of SQLite Virtual Table
Hello,
I have a virtual table and some (by me) declared datatypes are "TEXT10" or
"INTEGER" or "DATE"...
When I am using the function sqlite3_column_decltype() then I get those
types back, that's great. But I have a problem with aggregate functions.
E.g. the field "amount" with the datatype "INTEGER":
sqlite3_column_decltype("select amount from myvirtualtable", 0) returns
"INTEGER" -> good!
BUT:
sqlite3_column_decltype("select sum(amount) from myvirtualtable", 0) returns
NULL -> very bad!
Is there a possibility to get the declared datatype of an aggregate
function?
Because the function sqlite3_column_type() always returns SQLITE_NULL for my
virtual tables...
Thanks for every help!
Paul
--
View this message in context:
http://www.nabble.com/Get-declared-Datatype-of-SQLite-Virtual-Table-tp19330789p19330789.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] On UNIQUE and PRIMARY KEY
On Sep 5, 2008, at 3:01 AM, [EMAIL PROTECTED] wrote: > >> One occasionally sees SQLite schemas of the following form: >> >> CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); >> >> In other words, one sometimes finds a PRIMARY KEY and a UNIQUE >> declaration on the same column. This works fine in SQLite, but it is >> wasteful, both of disk space and of CPU time. > > I'm trying to reproduce the issue, but I only see a single index. > What am I missing? > I stand corrected. What I said was true for SQLite version 2.x. But the problem was fixed during the file format change going from the 2.8.14 series to 3.0.0. The fix occurred with check-in #1575 on 2004-05-12. Dan made that fix and I never noticed. I've been telling people ever since that UNIQUE PRIMARY KEY generated a redundant index. Good that I've finally been straightened out on that point. Sorry for the false alarm D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] On UNIQUE and PRIMARY KEY
> One occasionally sees SQLite schemas of the following form: > >CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ); > > In other words, one sometimes finds a PRIMARY KEY and a UNIQUE > declaration on the same column. This works fine in SQLite, but it is > wasteful, both of disk space and of CPU time. I'm trying to reproduce the issue, but I only see a single index. What am I missing? SQLite version 3.5.3 Enter ".help" for instructions sqlite> CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|meta|meta|2|CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_meta_1|meta|3| The database file above has 3 pages, as one would expect. SQLite version 3.6.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t1(id LONGVARCHAR UNIQUE, foo, bar); sqlite> create table t2(id LONGVARCHAR PRIMARY KEY, foo, bar); sqlite> create table t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|t1|t1|2|CREATE TABLE t1(id LONGVARCHAR UNIQUE, foo, bar) index|sqlite_autoindex_t1_1|t1|3| table|t2|t2|4|CREATE TABLE t2(id LONGVARCHAR PRIMARY KEY, foo, bar) index|sqlite_autoindex_t2_1|t2|5| table|t3|t3|6|CREATE TABLE t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_t3_1|t3|7| And this database has 7 pages. -- View this message in context: http://www.nabble.com/On-UNIQUE-and-PRIMARY-KEY-tp19313570p19325957.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
