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