Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Simon Slavin]

On 28 Jun 2010, at 7:31pm, Pavel Ivanov wrote:

>> Such freedom is not suitable for data interchange between two systems. Not
>> that SQLite or any other database would change the PK during import-export,
>>  but they are free to do so as long as the *intramural* integrity is
>> preserved.
> 
> Can you point out some documentation supporting this claim?
> I've no time now to search internet on this matter but I believe DBMS
> changing data that *I stored* in it is fundamentally broken. I
> wouldn't advise anybody to use it. DBMS is allowed to change only
> internal details which do not appear in CREATE TABLE and INSERT
> statements (like ROWID for example).

This /is/ how SQLite behaves.  If you allow it to do its own thing with 
_rowid_, and never refer to it, it feels free to change _rowid_ values any time 
it likes.  If you define a column and it says "Hey, I can save space because I 
already have _rowid_ which fits those requirements." then since it knows you 
can access the values in that column it won't ever change them.  Makes perfect 
sense to me.

By the way, these cases where SQLite may change _rowid_ values are not mundane. 
 I think the only place I've seen it documented is for VACUUM or for 
autovacuums.

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

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Tim Romano]

My remarks were made in the context of AUTOINCREMENTING primary keys. With
auto-incremented keys, the database is free to implement the incrementation
in the manner it sees fit. It may skip numbers. It may re-generate keys on
import/restore and cascade the changes out to child tables.  Given these
possibilities, it is not best practice to exchange autoincremented keys
between systems but to use instead an alternate unique key that is
guaranteed to remain constant.

Regards
Tim Romano
Swarthmore PA



On Mon, Jun 28, 2010 at 2:31 PM, Pavel Ivanov  wrote:

> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> >  but they are free to do so as long as the *intramural* integrity is
> > preserved.
>
> Can you point out some documentation supporting this claim?
> I've no time now to search internet on this matter but I believe DBMS
> changing data that *I stored* in it is fundamentally broken. I
> wouldn't advise anybody to use it. DBMS is allowed to change only
> internal details which do not appear in CREATE TABLE and INSERT
> statements (like ROWID for example). And AFAIK semantically primary
> key is no difference with unique constraint (except ability to be
> referenced by foreign key of course). And even more: I believe in a
> completely normalized database there couldn't be any unique
> constraints other than primary key. And to advise to developers either
> to not use primary key or to declare all columns referencing to it as
> foreign key is too much of a restrain.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano 
> wrote:
> > Pavel,
> > Although you are right that SQLite persists the rowid for INTEGER PRIMARY
> > KEYS across VACUUMs and suchlike, I too am right.
> >
> > I was focusing on the OP's use of the words "guaranteed" and "globally"
> and
> > on this requirement:
> >
> > The OP wrote:
> > "BTW, in my story it is necessary to store the unique IDs as an integer
> > type not something like "uuid" or "hash" because the unique ID also
> > standard for a position in a string in exchanging protocol between 2
> > system."
> >
> > Since no SQL standard requires the primary key to do anything other than
> be
> > unique within the relation and with respect to its foreign references.
>  As
> > long as the database maintains meets those requirements, it is free to
> > change the PK value as an "implementation detail"  -- provided RI is not
> > broken in the process.   The purist in me says the PK is for nothing but
> > uniqueness. It should have no other meaning whatsoever.
> >
> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> >  but they are free to do so as long as the *intramural* integrity is
> > preserved.  Once you move into the extra-mural realm (data exchange, or
> > replication) I would advise against relying upon the PK value.
> >
> > The safest "guaranteed" way to achieve what the OP wants is to add
> another
> > column to the table and to make it an alternate unique key. This value
> > carries for him the specific meaning "position in a string in exchange
> > protocol between 2 systems".
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> > On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov 
> wrote:
> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique.  There is no "be constant" clause in its contract, so to
> >>
> >> Tim, you are not right here. You are right that as is ROWID is not
> >> guaranteed to be constant over time. But if you define some column as
> >> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> >> SQLite guarantees that the value of this column will persist through
> >> any internal operations (like VACUUM or dumping and loading database
> >> again). Of course nobody will block UPDATEs on this column (as long as
> >> uniqueness remains valid), but that's a different story.
> >>
> >>
> >> Pavel
> >>
> >> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano 
> >> wrote:
> >> > In this example:
> >> >
> >> > CREATE TABLE  tableA {
> >> >  id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> >  name TEXT NOT NULL UNIQUE,
> >> >  myspecialvalue TEXT NOT NULL UNIQUE
> >> > }
> >> >
> >> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique.  There is no "be constant" clause in its contract, so to
> >> > speak. Therefore, you should 

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Jay A. Kreibich]

On Mon, Jun 28, 2010 at 02:15:01PM -0400, Tim Romano scratched on the wall:

> Since no SQL standard requires the primary key to do anything other than be
> unique within the relation and with respect to its foreign references.  As
> long as the database maintains meets those requirements, it is free to
> change the PK value as an "implementation detail"  -- provided RI is not
> broken in the process.   The purist in me says the PK is for nothing but
> uniqueness. It should have no other meaning whatsoever.

  That is definitely not true in the SQL world or the Relational Model.
  Not in the general case, anyways.  One must assume that a PK (in SQL)
  or a candidate key (RM) contains real data that consist of meaningful
  values.  For example, a store register database might use UPC for a
  PK value.  The concept of a primary key (or more specifically, a
  candidate key) is an attribute that is applied to existing columns,
  not the other way around.  This becomes even more apparent when you
  consider that the Relational Model does not have PKs, but only
  candidate keys-- and, unlike SQL PKs, a relation (table) is allowed to
  have more than one candidate key.  They can't all be arbitrary.

  Now, it is true that many database designs choose to omit a meaningful
  PK and use a so-called "surrogate key."  This is typically an
  arbitrary numeric ID field, but it could be anything.  Surrogate keys
  are commonly used when there isn't a very strong natural PK (such as
  with people) or when the natural PK is clumsy or large (say, several
  long text columns). 

  It is also true that this technique is so common and so popular that
  many databases (including SQLite) support some type of auto-assignment
  system (such as INTEGER PRIMARY KEY).  But it is equally valid to
  define a text field or a manually assigned integer field as the PK of
  a table.  Just because a PK can be arbitrary doesn't mean it must be.

  While you might argue that the database engine can do as it pleases
  with surrogate keys, that is most definitely not true of PKs in
  general, and I think there are strong arguments to say that it would
  be a Bad Idea to do it with surrogate keys.  For one thing, you'd
  have to very strictly notify the database when it was dealing with
  a surrogate key.  Simply having something be an INTEGER PRIMARY KEY
  would not be good enough, for example.  I can still define a PK as
  such and then enter my own meaningful numbers.




  If you speak of internal cross references, such as ROWIDs, that's a
  whole different story.  General ROWIDs, OIDs, or whatever the
  database wants to call them, are typically the domain of the database
  engine, and not to be used or exposed.  They're still used as keys
  (for example, index record references) and I would quickly agree that
  they are open to changes and modifications whenever and however the
  database system wants.  SQLite does this with a VACUUM, for example.
  ROWID values can change, but PK values cannot be changed or altered.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Pavel Ivanov]

> Such freedom is not suitable for data interchange between two systems. Not
> that SQLite or any other database would change the PK during import-export,
>  but they are free to do so as long as the *intramural* integrity is
> preserved.

Can you point out some documentation supporting this claim?
I've no time now to search internet on this matter but I believe DBMS
changing data that *I stored* in it is fundamentally broken. I
wouldn't advise anybody to use it. DBMS is allowed to change only
internal details which do not appear in CREATE TABLE and INSERT
statements (like ROWID for example). And AFAIK semantically primary
key is no difference with unique constraint (except ability to be
referenced by foreign key of course). And even more: I believe in a
completely normalized database there couldn't be any unique
constraints other than primary key. And to advise to developers either
to not use primary key or to declare all columns referencing to it as
foreign key is too much of a restrain.


Pavel

On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano  wrote:
> Pavel,
> Although you are right that SQLite persists the rowid for INTEGER PRIMARY
> KEYS across VACUUMs and suchlike, I too am right.
>
> I was focusing on the OP's use of the words "guaranteed" and "globally" and
> on this requirement:
>
> The OP wrote:
> "BTW, in my story it is necessary to store the unique IDs as an integer
> type not something like "uuid" or "hash" because the unique ID also
> standard for a position in a string in exchanging protocol between 2
> system."
>
> Since no SQL standard requires the primary key to do anything other than be
> unique within the relation and with respect to its foreign references.  As
> long as the database maintains meets those requirements, it is free to
> change the PK value as an "implementation detail"  -- provided RI is not
> broken in the process.   The purist in me says the PK is for nothing but
> uniqueness. It should have no other meaning whatsoever.
>
> Such freedom is not suitable for data interchange between two systems. Not
> that SQLite or any other database would change the PK during import-export,
>  but they are free to do so as long as the *intramural* integrity is
> preserved.  Once you move into the extra-mural realm (data exchange, or
> replication) I would advise against relying upon the PK value.
>
> The safest "guaranteed" way to achieve what the OP wants is to add another
> column to the table and to make it an alternate unique key. This value
> carries for him the specific meaning "position in a string in exchange
> protocol between 2 systems".
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
> On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov  wrote:
>
>> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
>> > SQLite will treat this column as an alias for the ROWID. There is no
>> > guarantee that ROWID will remain constant over time: its job is very
>> simple:
>> > to be unique.  There is no "be constant" clause in its contract, so to
>>
>> Tim, you are not right here. You are right that as is ROWID is not
>> guaranteed to be constant over time. But if you define some column as
>> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
>> SQLite guarantees that the value of this column will persist through
>> any internal operations (like VACUUM or dumping and loading database
>> again). Of course nobody will block UPDATEs on this column (as long as
>> uniqueness remains valid), but that's a different story.
>>
>>
>> Pavel
>>
>> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano 
>> wrote:
>> > In this example:
>> >
>> > CREATE TABLE  tableA {
>> >  id INTEGER PRIMARY KEY AUTOINCREMENT,
>> >  name TEXT NOT NULL UNIQUE,
>> >  myspecialvalue TEXT NOT NULL UNIQUE
>> > }
>> >
>> >
>> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
>> > SQLite will treat this column as an alias for the ROWID. There is no
>> > guarantee that ROWID will remain constant over time: its job is very
>> simple:
>> > to be unique.  There is no "be constant" clause in its contract, so to
>> > speak. Therefore, you should add another column [myspecialvalue] and make
>> it
>> > unique if you want to associate a value with a tuple and also want to
>> > guarantee that the associated value is both unique and remains
>> unchanging.
>> >  Of course you have to prevent edits to the associated value to enforce
>> its
>> > immutability.
>> >
>> > Regards
>> > Tim Romano
>> > Swarthmore PA
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Sat, Jun 26, 2010 at 11:34 AM, kee  wrote:
>> >
>> >> Dear all
>> >>
>> >> I have 2 string lists, listA and listB as raw data which need to be
>> >> store in the SQLITE database, both of them may have duplicated records
>> >>
>> >> listA                                   listB
>> >> ===
>> >> orange                                

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Tim Romano]

Pavel,
Although you are right that SQLite persists the rowid for INTEGER PRIMARY
KEYS across VACUUMs and suchlike, I too am right.

I was focusing on the OP's use of the words "guaranteed" and "globally" and
on this requirement:

The OP wrote:
"BTW, in my story it is necessary to store the unique IDs as an integer
type not something like "uuid" or "hash" because the unique ID also
standard for a position in a string in exchanging protocol between 2
system."

Since no SQL standard requires the primary key to do anything other than be
unique within the relation and with respect to its foreign references.  As
long as the database maintains meets those requirements, it is free to
change the PK value as an "implementation detail"  -- provided RI is not
broken in the process.   The purist in me says the PK is for nothing but
uniqueness. It should have no other meaning whatsoever.

Such freedom is not suitable for data interchange between two systems. Not
that SQLite or any other database would change the PK during import-export,
 but they are free to do so as long as the *intramural* integrity is
preserved.  Once you move into the extra-mural realm (data exchange, or
replication) I would advise against relying upon the PK value.

The safest "guaranteed" way to achieve what the OP wants is to add another
column to the table and to make it an alternate unique key. This value
carries for him the specific meaning "position in a string in exchange
protocol between 2 systems".

Regards
Tim Romano
Swarthmore PA


On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov  wrote:

> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique.  There is no "be constant" clause in its contract, so to
>
> Tim, you are not right here. You are right that as is ROWID is not
> guaranteed to be constant over time. But if you define some column as
> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> SQLite guarantees that the value of this column will persist through
> any internal operations (like VACUUM or dumping and loading database
> again). Of course nobody will block UPDATEs on this column (as long as
> uniqueness remains valid), but that's a different story.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano 
> wrote:
> > In this example:
> >
> > CREATE TABLE  tableA {
> >  id INTEGER PRIMARY KEY AUTOINCREMENT,
> >  name TEXT NOT NULL UNIQUE,
> >  myspecialvalue TEXT NOT NULL UNIQUE
> > }
> >
> >
> > the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> > SQLite will treat this column as an alias for the ROWID. There is no
> > guarantee that ROWID will remain constant over time: its job is very
> simple:
> > to be unique.  There is no "be constant" clause in its contract, so to
> > speak. Therefore, you should add another column [myspecialvalue] and make
> it
> > unique if you want to associate a value with a tuple and also want to
> > guarantee that the associated value is both unique and remains
> unchanging.
> >  Of course you have to prevent edits to the associated value to enforce
> its
> > immutability.
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> >
> >
> >
> >
> >
> >
> > On Sat, Jun 26, 2010 at 11:34 AM, kee  wrote:
> >
> >> Dear all
> >>
> >> I have 2 string lists, listA and listB as raw data which need to be
> >> store in the SQLITE database, both of them may have duplicated records
> >>
> >> listA   listB
> >> ===
> >> orangejapan
> >> pearchina
> >> orangechina
> >> apple   american
> >> cherry   india
> >> lemon   china
> >> lemon   japan
> >> strawberry   korea
> >> banana   thailand
> >>   australia
> >>
> >> I want all items in listA and listB have a runtime defined global ID and
> >> fix it, which means no matter how the lists changed later after the
> >> first time running, all the item always have an unique int type ID bind
> >> with, looks like:
> >> A  B
> >> 
> >> 1orange   1  japan
> >> 2pear   2  china
> >> 3apple 3  american
> >> 4cherry   4  india
> >> 5lemon5  taiwan
> >> 6strawberry6  korea
> >> 7banana  

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Pavel Ivanov]

> the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> SQLite will treat this column as an alias for the ROWID. There is no
> guarantee that ROWID will remain constant over time: its job is very simple:
> to be unique.  There is no "be constant" clause in its contract, so to

Tim, you are not right here. You are right that as is ROWID is not
guaranteed to be constant over time. But if you define some column as
an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
SQLite guarantees that the value of this column will persist through
any internal operations (like VACUUM or dumping and loading database
again). Of course nobody will block UPDATEs on this column (as long as
uniqueness remains valid), but that's a different story.


Pavel

On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano  wrote:
> In this example:
>
> CREATE TABLE  tableA {
>  id INTEGER PRIMARY KEY AUTOINCREMENT,
>  name TEXT NOT NULL UNIQUE,
>  myspecialvalue TEXT NOT NULL UNIQUE
> }
>
>
> the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
> SQLite will treat this column as an alias for the ROWID. There is no
> guarantee that ROWID will remain constant over time: its job is very simple:
> to be unique.  There is no "be constant" clause in its contract, so to
> speak. Therefore, you should add another column [myspecialvalue] and make it
> unique if you want to associate a value with a tuple and also want to
> guarantee that the associated value is both unique and remains unchanging.
>  Of course you have to prevent edits to the associated value to enforce its
> immutability.
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
>
>
>
>
>
> On Sat, Jun 26, 2010 at 11:34 AM, kee  wrote:
>
>> Dear all
>>
>> I have 2 string lists, listA and listB as raw data which need to be
>> store in the SQLITE database, both of them may have duplicated records
>>
>> listA                                   listB
>> ===
>> orange                                japan
>> pear                                    china
>> orange                                china
>> apple                                   american
>> cherry                                   india
>> lemon                                   china
>> lemon                                   japan
>> strawberry                           korea
>> banana                                   thailand
>>                                           australia
>>
>> I want all items in listA and listB have a runtime defined global ID and
>> fix it, which means no matter how the lists changed later after the
>> first time running, all the item always have an unique int type ID bind
>> with, looks like:
>> A                                              B
>> 
>> 1    orange                                   1  japan
>> 2    pear                                       2  china
>> 3    apple                                     3  american
>> 4    cherry                                   4  india
>> 5    lemon                                    5  taiwan
>> 6    strawberry                            6  korea
>> 7    banana                                  7  thailand
>>                                                    8  australia
>>
>>
>> So I defined table with such structure:
>> CREATE TABLE  tableA {
>>    uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>    name TEXT NOT NULL UNIQUE,
>> }
>> CREATE TABLE  tableB {
>>    uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>    name TEXT NOT NULL UNIQUE,
>> }
>>
>> and my plan is to use "INSERT OR FAIL" to insert data into those tables.
>>
>> Here comes my QUESTION 1, is it possible no matter what the list
>> changed, all items always get an unique ID, should  any other limitation
>> should be added into the defination, and if I use "CREATE TABLE
>> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
>> those definition be copied either?
>>
>>
>> Then, it may need to make a matrix for 2 tables:  I want list all
>> possible combination of 2 lists, for example:
>>
>> listC = listA * listB
>> 
>> japan         orange
>> china          orange
>> american   orange
>> india          orange
>> ...
>> thailand     banana
>> australia    banana
>>
>> I also want to use same table structure to store the combination result
>> and  assigned unique ID for those combined items same as before:
>> CREATE TABLE  tableC {
>>    uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>    name_combination TEXT NOT NULL UNIQUE,
>> }
>> Here comes my QUESTION 2, is it also reasonable using such a structure
>> store the combination or should there be a better way to do it?
>> I means will such a structure be a problem if the listA and listB be
>> changed, should I store uniqueIDA and uniqueIDB replace the
>> name_combination field will be a better solution?
>>
>> BTW, I using the python as the interface insert the lists into 

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Tim Romano]

You could also define your primary key as INT PRIMARY KEY (rather than
INTEGER PRIMARY KEY) and in that case SQLite will treat it as a normal
column and it will remain immutable over time (unless you change it).
However, I would advise against using INT PRIMARY KEY inasmuch as this
subtle (yet documented) difference between INT and INTEGER in primary keys
in SQLite has already been a cause of significant confusion and is an
"accident waiting to happen" IMO since not all implementations of SQLite may
be hep to the nuance.  What you want is a simple ersatz value for the entity
 (i.e. an "alternate unique key"); a second column which *explicitly* acts
in this manner will be clearer.
Regards
Tim Romano
Swarthmore PA


On Mon, Jun 28, 2010 at 8:43 AM, Tim Romano  wrote:

> And myspecialvalue can be INTEGER|TEXT.
>
>
> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote:
>
>> In this example:
>>
>> CREATE TABLE  tableA {
>>
>>   id INTEGER PRIMARY KEY AUTOINCREMENT,
>>  name TEXT NOT NULL UNIQUE,
>>   myspecialvalue TEXT NOT NULL UNIQUE
>> }
>>
>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Tim Romano]

And myspecialvalue can be INTEGER|TEXT.

On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano  wrote:

> In this example:
>
> CREATE TABLE  tableA {
>
>   id INTEGER PRIMARY KEY AUTOINCREMENT,
>  name TEXT NOT NULL UNIQUE,
>  myspecialvalue TEXT NOT NULL UNIQUE
> }
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Tim Romano]

In this example:

CREATE TABLE  tableA {
  id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL UNIQUE,
 myspecialvalue TEXT NOT NULL UNIQUE
}


the primary key column [id] is defined as INTEGER PRMARY KEY; so defined,
SQLite will treat this column as an alias for the ROWID. There is no
guarantee that ROWID will remain constant over time: its job is very simple:
to be unique.  There is no "be constant" clause in its contract, so to
speak. Therefore, you should add another column [myspecialvalue] and make it
unique if you want to associate a value with a tuple and also want to
guarantee that the associated value is both unique and remains unchanging.
 Of course you have to prevent edits to the associated value to enforce its
immutability.

Regards
Tim Romano
Swarthmore PA








On Sat, Jun 26, 2010 at 11:34 AM, kee  wrote:

> Dear all
>
> I have 2 string lists, listA and listB as raw data which need to be
> store in the SQLITE database, both of them may have duplicated records
>
> listA   listB
> ===
> orangejapan
> pearchina
> orangechina
> apple   american
> cherry   india
> lemon   china
> lemon   japan
> strawberry   korea
> banana   thailand
>   australia
>
> I want all items in listA and listB have a runtime defined global ID and
> fix it, which means no matter how the lists changed later after the
> first time running, all the item always have an unique int type ID bind
> with, looks like:
> A  B
> 
> 1orange   1  japan
> 2pear   2  china
> 3apple 3  american
> 4cherry   4  india
> 5lemon5  taiwan
> 6strawberry6  korea
> 7banana  7  thailand
>8  australia
>
>
> So I defined table with such structure:
> CREATE TABLE  tableA {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
> CREATE TABLE  tableB {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT NOT NULL UNIQUE,
> }
>
> and my plan is to use "INSERT OR FAIL" to insert data into those tables.
>
> Here comes my QUESTION 1, is it possible no matter what the list
> changed, all items always get an unique ID, should  any other limitation
> should be added into the defination, and if I use "CREATE TABLE
> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
> those definition be copied either?
>
>
> Then, it may need to make a matrix for 2 tables:  I want list all
> possible combination of 2 lists, for example:
>
> listC = listA * listB
> 
> japan orange
> china  orange
> american   orange
> india  orange
> ...
> thailand banana
> australiabanana
>
> I also want to use same table structure to store the combination result
> and  assigned unique ID for those combined items same as before:
> CREATE TABLE  tableC {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name_combination TEXT NOT NULL UNIQUE,
> }
> Here comes my QUESTION 2, is it also reasonable using such a structure
> store the combination or should there be a better way to do it?
> I means will such a structure be a problem if the listA and listB be
> changed, should I store uniqueIDA and uniqueIDB replace the
> name_combination field will be a better solution?
>
> BTW, I using the python as the interface insert the lists into those
> tables, also the uinque_id in database is not need to be reused if some
> items in listA and listB been deleted, just remain as is because it will
> never get to sqlite limitation.
>
> BTW, in my story it is necessary to store the unique IDs as an integer
> type not something like "uuid" or "hash" because the unique ID also
> standard for a position in a string in exchanging protocol between 2
> system.
>
>
> And : a more general question:
> Anyone has better solution to solve my problem in sqlite - the items in
> a list need to be bind with an unchangeable integer type unique ID no
> matter what the list will be modified?
>
> Any comments and suggestions will be highly appreciated!
>
> Thanks!
>
> Rgs,
>
> KC
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list

Re: [sqlite] (python) how to define unchangeable global ID in a table?

[Simon Slavin]

On 26 Jun 2010, at 4:34pm, kee wrote:

> both of them may have duplicated records

... and later ...

>name TEXT NOT NULL UNIQUE,

Those two things contradict each-other.  If you specify UNIQUE you can't have 
duplicated values.

> CREATE TABLE  tableA {

Try to get out of that habit.  if those are countries, call your table 
'countries', not 'tableA'.

> if I use "CREATE TABLE
> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
> those definition be copied either?

They will be.  You can't avoid it.

> listC = listA * listB
> 
> japan orange
> china  orange
> american   orange
> india  orange
> ...
> thailand banana
> australiabanana
> 
> I also want to use same table structure to store the combination result
> and  assigned unique ID for those combined items same as before:
> CREATE TABLE  tableC {
>uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
>name_combination TEXT NOT NULL UNIQUE,
> }


Do it in software.  If you find it easier to work with long strings, and if 
there're no commas in your names then you can use

SELECT group_concat(name,',') FROM countries

and get one long string instead of lots of records

To answer your questions:

'INTEGER PRIMARY KEY AUTOINCREMENT' is assigned when a row is created.  It 
won't change even if you change the values of all the other columns of that row.

As for storing the combinations, that'll work fine but it's not obvious why you 
want to store them.  There may be something that suits your purposes better.  
But yes, it'll work fine and it's the sort of thing people use SQL for all the 
time.

Tables don't magically update one-another.  If you create your table of 
combinations and then your fruit table changes, the combination table won't 
magically update.

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