[Gambas-user] Connection.Tables

[Benoît Minisini]

> hi,
> i noticed something in my test with Connection.Edit() (sqlite3):
> i created a table with connection.exec()
> 
> hConnection.Exec("create table test(id integer primary key, name
> varchar(10));")
> 
> then inserted some data
> hConnection.Exec("insert into test(name) values(\"Aaron\");")
> hConnection.Exec("insert into test(name) values('Zacharias');")
> 
> now, because i thought, this is the only way to prevent the error "table
> test has no primary key", i set the primary key property of the table
> object:
> hConnection.Tables["test"].PrimaryKey = ["id"]
> 
> not this brought me "Read-only property".
> is this because i created the table via exec() and there is no
> synchronization (possible)?
> 
> regards,
> tobi
> 

You can't update the primary index of a table once it has been created anyway.

I have understood where the bug with primary key comes from. It is just a 
matter of case: write "integer" in upper case, and the database driver will 
detect the primary key!

To understand all that, you must be aware that SQLite is a non-typed database, 
i.e. the field datatypes given in the CREATE TABLE statement are mostly 
ignored. A SQLite field can hold any datatype, whatever its definition!

"Mostly", because there is a big exception: if you declare a field as "integer 
primary key", you actually declare a 64 bits integer-only field that will 
increment automatically as each record creation. And this special primary key 
does not appear in the index list returned by SQLite (because it is actually 
an index present in each table, named "rowid").

See http://www.sqlite.org/lang_createtable.html for more information.

To detect it, I added a test on the table SQL declaration: if I don't see any 
index in the table, and if the table has an integer field, I assume it is the 
primary key. 

The test is not perfect yet (a table could have an "integer" field and no 
primary key), but, more important, it was buggy, as it assumed that "integer" 
was written in upper case!

I will fix that in the next revision, and your "id integer primary key" will 
be correctly detected as the primary key of the table.

Note that if you create your table by using the Gambas interface, and not by 
sending "CREATE TABLE" statements directly, you won't have the problem, 
because Gambas only uses "integer" when creating an autoincrement integer 
field. For all other integer fields, it uses "INT4".

Regards,

-- 
Benoît Minisini