Thread: [SQLObject] Problem with idType
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <py...@to...> - 2006-07-21 14:22:26
|
Hello, When I try to create in MySql using "createTable" the class... class Country(SQLObject): class sqlmeta: idName =3D "iso" idType =3D str name =3D StringCol(notNone =3D True) I get an exception : _mysql_exceptions.OperationalError: (1170, "BLOB/TEXT column 'iso' used=20 in key specification without a key length") How can I fix the lenght of this id ? Fran=E7ois |
From: Oleg B. <ph...@ma...> - 2006-07-25 16:32:26
|
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/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <py...@to...> - 2006-07-27 10:19:02
|
(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 |
From: Oleg B. <ph...@ma...> - 2006-07-27 17:22:02
|
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/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <py...@to...> - 2006-07-28 01:59:56
|
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 |