SQLObject

On Fri, Jul 21, 2006 at 04:22:06PM +0200, Fran?ois wrote:
> class Country(SQLObject):
>     class sqlmeta:
>         idName = "iso"
>         idType = str
>     name = StringCol(notNone = True)

   With this SQLObject creates an "iso" column declared as TEXT PRIMARY
KEY. I don't know if MySQL allows this. Please consult the docs. If it
doesn't - please report what column type it must be.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

(2nd post, 1st don't appears on list?)

Oleg Broytmann a =E9crit :
> On Fri, Jul 21, 2006 at 04:22:06PM +0200, Fran?ois wrote:
>> class Country(SQLObject):
>> class sqlmeta:
>> idName =3D "iso"
>> idType =3D str
>> name =3D StringCol(notNone =3D True)
>
> With this SQLObject creates an "iso" column declared as TEXT PRIMARY
> KEY. I don't know if MySQL allows this. Please consult the docs. If it
> doesn't - please report what column type it must be.
>
> Oleg.
Hello oleg, I hope your are better now :-)

In mysql doc [1] I've see :

[...] |CHAR|, |VARCHAR|, |BINARY|, and |VARBINARY| columns, indexes can=20
be created that use only the leading part of column values, using=20
|/|col_name|/(/|length|/)| syntax to specify an index prefix length.=20
|BLOB| and |TEXT| columns also can be indexed, but a prefix length=20
/must/ be given. Prefix lengths are given in characters for non-binary=20
string types and in bytes for binary string types. That is, index=20
entries consist of the first /|length|/ characters of each column value=20
for |CHAR|, |VARCHAR|, and |TEXT| columns, and the first /|length|/=20
bytes of each column value for |BINARY|, |VARBINARY|, and |BLOB|=20
columns. Indexing only a prefix of column values like this can make the=20
index file much smaller. See Section 7.4.3, =93Column Indexes=94=20
<http://dev.mysql.com/doc/refman/4.1/en/indexes.html>.

I have an old table created by MySQL Control center and its definition is=
 :

CREATE TABLE `country` (
`iso` char(2) NOT NULL default 'ch',
`name_en` varchar(100) NOT NULL default 'Switzerland',
...... other fileds ......
PRIMARY KEY (`iso`)
);

So the index is declared as CHAR type (and not TEXT) with the size 2. I=20
don't know if it's possible to add perhaps an 'idSize' or 'idLenght'=20
attribute in sqlmeta class ? The right definition for MySQL is :

CREATE TABLE country (
iso CHAR(2) NOT NULL PRIMARY KEY,
name varchar(100) NOT NULL
);

I'm not a SQL specialist, so I don't know if it's a right table=20
definition for other databases (SQL compliant) ?

Fran=E7ois


[1] http://dev.mysql.com/doc/refman/4.1/en/create-table.html

On Thu, Jul 27, 2006 at 12:18:40PM +0200, Fran?ois wrote:
> In mysql doc [1] I've see :
> 
> |BLOB| and |TEXT| columns also can be indexed, but a prefix length 
> /must/ be given.

   I see.

> I don't know if it's possible to add perhaps an 'idSize' or 'idLenght' 
> attribute in sqlmeta class ?

   It could be done and it should be done. In MySQL it could help to
distinguish VARCHAR(n) and TEXT(n) indices, in Postgres - serial and
bigserial, and so on.
   What do people think?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

Oleg Broytmann a =E9crit :
> On Fri, Jul 21, 2006 at 04:22:06PM +0200, Fran?ois wrote:
>  =20
>> class Country(SQLObject):
>>     class sqlmeta:
>>         idName =3D "iso"
>>         idType =3D str
>>     name =3D StringCol(notNone =3D True)
>>    =20
>
>    With this SQLObject creates an "iso" column declared as TEXT PRIMARY
> KEY. I don't know if MySQL allows this. Please consult the docs. If it
> doesn't - please report what column type it must be.
>
> Oleg.
>  =20
Hello oleg, I hope your are better now :-)

In mysql doc [1] I've see :

[...] |CHAR|, |VARCHAR|, |BINARY|, and |VARBINARY| columns, indexes can=20
be created that use only the leading part of column values, using=20
|/|col_name|/(/|length|/)| syntax to specify an index prefix length.=20
|BLOB| and |TEXT| columns also can be indexed, but a prefix length=20
/must/ be given. Prefix lengths are given in characters for non-binary=20
string types and in bytes for binary string types. That is, index=20
entries consist of the first /|length|/ characters of each column value=20
for |CHAR|, |VARCHAR|, and |TEXT| columns, and the first /|length|/=20
bytes of each column value for |BINARY|, |VARBINARY|, and |BLOB|=20
columns. Indexing only a prefix of column values like this can make the=20
index file much smaller. See Section 7.4.3, =93Column Indexes=94=20
<http://dev.mysql.com/doc/refman/4.1/en/indexes.html>.

I have an old table created by MySQL Control center and its definition is=
 :

CREATE TABLE `country` (
`iso` char(2) NOT NULL default 'ch',
`name_en` varchar(100) NOT NULL default 'Switzerland',
...... other fileds ......
PRIMARY KEY (`iso`)
);

So the index is declared as CHAR type (and not TEXT) with the size 2. I=20
don't know if it's possible to add perhaps an 'idSize' or 'idLenght'=20
attribute in sqlmeta class ? The right definition for MySQL is :

CREATE TABLE country (
iso CHAR(2) NOT NULL PRIMARY KEY,
name varchar(100) NOT NULL
);

I'm not a SQL specialist, so I don't know if it's a right table=20
definition for other databases (SQL compliant) ?

Fran=E7ois


[1] http://dev.mysql.com/doc/refman/4.1/en/create-table.html