Thread: [SQLObject] BoolCol and the backend datatypes
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Markus G. <m.g...@gm...> - 2007-10-19 07:39:16
|
Hi, in SQLObject a BoolCol() is stored as TINYINT(4) in the MySQL backend and as TINYINT in the SQLite backend. Both are not optimal IMO: *) MySQL knows BOOL (and since version 4.1 also BOOLEAN), which is a synonym for TINYINT(1). (http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html) *) TINYINT is not part of ANSI SQL and should IMO therefore not be used in the SQLite backend. SQLAlchemy uses BOOLEAN. Kind regards, Markus |
From: Oleg B. <ph...@ph...> - 2007-10-19 13:48:39
|
Hello! On Fri, Oct 19, 2007 at 09:39:05AM +0200, Markus Gritsch wrote: > in SQLObject a BoolCol() is stored as TINYINT(4) in the MySQL backend > and as TINYINT in the SQLite backend. Both are not optimal IMO: Booleans are stored as TINYINT which is TINYINT(1). Why do you think it is TINYINT(4)?! See http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html > *) MySQL knows BOOL (and since version 4.1 also BOOLEAN), which is a > synonym for TINYINT(1). > (http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html) Ok. As they're only synonyms I think I can use them in the current branches. Expect a new round of betas today or in a few days. > *) TINYINT is not part of ANSI SQL and should IMO therefore not be > used in the SQLite backend. SQLAlchemy uses BOOLEAN. Due to "column affinity" I'd better use a name that contains "INT" - just INT(1) or INTEGER(1): http://sqlite.org/datatype3.html Ok? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Markus G. <m.g...@gm...> - 2007-10-19 18:17:30
Attachments:
screenshot.png
|
On 10/19/07, Oleg Broytmann <ph...@ph...> wrote: > Hello! Hi Oleg, > On Fri, Oct 19, 2007 at 09:39:05AM +0200, Markus Gritsch wrote: > > in SQLObject a BoolCol() is stored as TINYINT(4) in the MySQL backend > > and as TINYINT in the SQLite backend. Both are not optimal IMO: > > Booleans are stored as TINYINT which is TINYINT(1). Why do you think it > is TINYINT(4)?! > > See http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html >From this page I cannot tell that TINYINT is the equivalent to TINYINT(1), maybe I just didn't see it. When running the following code #--- from sqlobject import * class Table1( SQLObject ): name = BoolCol() sqlhub.threadConnection = connectionForURI( 'mysql://markus@localhost/test?debug=True' ) Table1.dropTable( ifExists = True ) Table1.createTable( ifNotExists = True ) #--- which results in the following debug output 1/Query : DESCRIBE table1 1/Query : DESCRIBE table1 1/Query : CREATE TABLE table1 ( id INT PRIMARY KEY AUTO_INCREMENT, name TINYINT ) the table seen in the attachment is created. It says TINYINT(4). > > *) MySQL knows BOOL (and since version 4.1 also BOOLEAN), which is a > > synonym for TINYINT(1). > > (http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html) > > Ok. As they're only synonyms I think I can use them in the current > branches. Expect a new round of betas today or in a few days. > > > *) TINYINT is not part of ANSI SQL and should IMO therefore not be > > used in the SQLite backend. SQLAlchemy uses BOOLEAN. > > Due to "column affinity" I'd better use a name that contains "INT" > - just INT(1) or INTEGER(1): http://sqlite.org/datatype3.html > Ok? Specifying INT or INTEGER or something else does IMO not really have any impact on the column affinity of SQLite. As I see it, the affinity changes according to the data stored in the column. Maybe I'm wrong. However, SQLite also accepts 'FOO' or 'BAR' as the column type when creating the table. As of INT(1): From looking at the ANSI SQL standard, I think it is not possible to add parenthesis after INT: http://savage.net.au/SQL/sql-99.bnf.html#exact%20numeric%20type A few lines below there is a boolean type specified, which should IMO be used (as it is done in SQLAlchemy): http://savage.net.au/SQL/sql-99.bnf.html#boolean%20type Kind regards, Markus |
From: Oleg B. <ph...@ph...> - 2007-10-19 19:36:59
|
On Fri, Oct 19, 2007 at 08:17:25PM +0200, Markus Gritsch wrote: > On 10/19/07, Oleg Broytmann <ph...@ph...> wrote: > > Booleans are stored as TINYINT which is TINYINT(1). Why do you think it > > is TINYINT(4)?! > > > > See http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html > > >From this page I cannot tell that TINYINT is the equivalent to > TINYINT(1), maybe I just didn't see it. It is in the table: Type Bytes ---- ----- TINYINT 1 1 byte. TINYINT(1). > 1/Query : DESCRIBE table1 > 1/Query : DESCRIBE table1 > 1/Query : CREATE TABLE table1 ( > id INT PRIMARY KEY AUTO_INCREMENT, > name TINYINT > ) > > the table seen in the attachment is created. It says TINYINT(4). Very strange. By definition TINYINT is 1-byte INT. > > > *) TINYINT is not part of ANSI SQL and should IMO therefore not be > > > used in the SQLite backend. SQLAlchemy uses BOOLEAN. > > > > Due to "column affinity" I'd better use a name that contains "INT" > > - just INT(1) or INTEGER(1): http://sqlite.org/datatype3.html > > Specifying INT or INTEGER or something else does IMO not really have > any impact on the column affinity of SQLite. As I see it, the > affinity changes according to the data stored in the column. No. The affinity (*recommended* but not required type) is derived from the declared type: "2.1 Determination Of Column Affinity The type affinity of a column is determined by the declared type of the column, according to the following rules: 1. If the datatype contains the string "INT" then it is assigned INTEGER affinity." Hence I'd like to give a type for the column as "*INT*" - INT or INTEGER, but not BOOL. Yes, it could be BOOL, but what is the point of using so-called "standard SQL" (what DBMS really implements it instead of implementing an extended subset?) in such non-standard DBMS as SQLite? The only reason I can think of is to help fromDatabase machinery to guess the type of the column. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Dan P. <da...@ag...> - 2007-10-19 19:52:31
|
On Friday 19 October 2007, Markus Gritsch wrote: > From this page I cannot tell that TINYINT is the equivalent to > TINYINT(1), maybe I just didn't see it. integer_type(M) doesn't restrict the size of the storage, it only specifies the maximum display width. In other words M doesn't specify the number of bytes to use but the max expected display width. Here is an excerpt from the mysql manual: M indicates the maximum display width for integer types. The maximum legal display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 10.2, “Numeric Types”. source: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html > > When running the following code > > #--- > from sqlobject import * > > class Table1( SQLObject ): > name = BoolCol() > > sqlhub.threadConnection = connectionForURI( > 'mysql://markus@localhost/test?debug=True' ) > > Table1.dropTable( ifExists = True ) > Table1.createTable( ifNotExists = True ) > #--- > > which results in the following debug output > > 1/Query : DESCRIBE table1 > 1/Query : DESCRIBE table1 > 1/Query : CREATE TABLE table1 ( > id INT PRIMARY KEY AUTO_INCREMENT, > name TINYINT > ) > > the table seen in the attachment is created. It says TINYINT(4). Again, TINYINT(1) doesn't use less space than TINYINT(4). They both use 1 byte as per this documentation reference: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html The only difference is that tinyint(4) will be displayed with a maximum width of 4 characters to be able to accommodate a displayed value from -128 to +127 (4 chars considering the sign). Alas it still uses 1 byte. tinyint(1) uses 1 byte as well but it has a max display width of 1, because you do not expect to have more than 1 digit there and you know that you will not display a sign with it. Even more this is just a convention and an indication of what you expect to have in there because is not enforced anywhere (i.e. it won't truncate the displayed values if they need more chars to display). -- Dan |
From: Dan P. <da...@ag...> - 2007-10-19 20:00:07
|
On Friday 19 October 2007, Markus Gritsch wrote: > On 10/19/07, Oleg Broytmann <ph...@ph...> wrote: > > Hello! > > Hi Oleg, > > > On Fri, Oct 19, 2007 at 09:39:05AM +0200, Markus Gritsch wrote: > > > in SQLObject a BoolCol() is stored as TINYINT(4) in the MySQL > > > backend and as TINYINT in the SQLite backend. Both are not optimal > > > IMO: > > > > Booleans are stored as TINYINT which is TINYINT(1). Why do you > > think it is TINYINT(4)?! > > > > See http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html > > From this page I cannot tell that TINYINT is the equivalent to > TINYINT(1), maybe I just didn't see it. That page clearly states that tinyint uses 1 byte and then a paragraph below it says this: ------------- Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces. The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters. ------------- Which I believe pretty much explains that it doesn't really matter if it is tinyint(1) or tinyint(4) (the default), as I explained in the previous email. -- Dan |
From: Oleg B. <ph...@ph...> - 2007-10-19 20:15:17
|
On Fri, Oct 19, 2007 at 10:59:56PM +0300, Dan Pascu wrote: > > > See http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html > > > > From this page I cannot tell that TINYINT is the equivalent to > > TINYINT(1), maybe I just didn't see it. > > That page clearly states that tinyint uses 1 byte and then a paragraph > below it says this: > > ------------- > Another extension is supported by MySQL for optionally specifying the > display width of integer data types in parentheses following the base > keyword for the type (for example, INT(4)). This optional display width > is used to display integer values having a width less than the width > specified for the column by left-padding them with spaces. > The display width does not constrain the range of values that can be > stored in the column, nor the number of digits that are displayed for > values having a width exceeding that specified for the column. For > example, a column specified as SMALLINT(3) has the usual SMALLINT range > of -32768 to 32767, and values outside the range allowed by three > characters are displayed using more than three characters. > ------------- > > Which I believe pretty much explains that it doesn't really matter if it > is tinyint(1) or tinyint(4) (the default), as I explained in the previous > email. Thank you! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Markus G. <m.g...@gm...> - 2007-10-20 13:21:40
|
On 10/19/07, Dan Pascu <da...@ag...> wrote: > > Which I believe pretty much explains that it doesn't really matter if it > is tinyint(1) or tinyint(4) (the default), as I explained in the previous > email. Ah, your explanation makes it very clear, thank you. In this case I think it would be better to use BOOL (or if MySQL 4.1 can be assumed BOOLEAN) for BoolCol in the MySQL backend. On 10/19/07, Oleg Broytmann <ph...@ph...> wrote: > > Hence I'd like to give a type for the column as "*INT*" - INT or > INTEGER, but not BOOL. Yes, it could be BOOL, but what is the point of > using so-called "standard SQL" (what DBMS really implements it instead of > implementing an extended subset?) in such non-standard DBMS as SQLite? The > only reason I can think of is to help fromDatabase machinery to guess the > type of the column. OK, thank you for explaining SQLites behavior to me. I understand it better now. After reading http://sqlite.org/datatype3.html again, I still think that BOOLEAN should be used for the SQLite backend: *) It has the advantage you already pointed out in helping fromDatabase. *) According to point 2.3 subpoint 5. at http://sqlite.org/datatype3.html, the affinity when specifying BOOLEAN (or FOO or BAR) is NUMERIC. And a bit above the docu says """A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values. A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.""" So the BoolCol value would end up being stored in the INTEGER storage class. IMO using INTEGER affinity has in this case no advantage over using NUMERIC affinity, which can be achieved by using BOOLEAN in the column definition. Kind regards, Markus |
From: Oleg B. <ph...@ph...> - 2007-10-20 13:27:39
|
On Sat, Oct 20, 2007 at 03:21:38PM +0200, Markus Gritsch wrote: > In this case I think it would be better to use BOOL (or if MySQL 4.1 > can be assumed BOOLEAN) for BoolCol in the MySQL backend. > > After reading http://sqlite.org/datatype3.html again, I > still think that BOOLEAN should be used for the SQLite backend: > > *) It has the advantage you already pointed out in helping fromDatabase. I agree. I'll change TINYINT to BOOL for MySQL in all branches and I'll change it to BOOL for SQLite in the trunk; I will also extend fromDatabase for SQLite to recognize BOOL. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2007-10-22 11:29:10
|
On Fri, Oct 19, 2007 at 09:39:05AM +0200, Markus Gritsch wrote: > *) MySQL knows BOOL (and since version 4.1 also BOOLEAN), which is a > synonym for TINYINT(1). > (http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html) Fixed in the 0.7, 0.8, 0.9 branches and in the trunk in the revisions 3107-3111. > *) TINYINT is not part of ANSI SQL and should IMO therefore not be > used in the SQLite backend. SQLAlchemy uses BOOLEAN. Changed in the trunk along with fromDatabase in the revs 3112-3113. They are released as versions 0.7.9b3, 0.8.6b3 and 0.9.2b3. Please test and report. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Markus G. <m.g...@gm...> - 2007-10-22 12:09:43
|
On 10/22/07, Oleg Broytmann <ph...@ph...> wrote: > > > *) TINYINT is not part of ANSI SQL and should IMO therefore not be > > used in the SQLite backend. SQLAlchemy uses BOOLEAN. > > Changed in the trunk along with fromDatabase in the revs 3112-3113. > > They are released as versions 0.7.9b3, 0.8.6b3 and 0.9.2b3. Please test > and report. *) guessClass() in sqliteconnection.py still contains the bug that the find() result is tested to be > 0 whereas it should be tested != -1. I see that it is fixed in the trunk. *) The trunk contains a copy & paste error: guessClass returns col.DecimalCol for 'BOOL' columns. Kind regards, Markus |
From: Oleg B. <ph...@ph...> - 2007-10-22 12:23:06
|
On Mon, Oct 22, 2007 at 02:09:37PM +0200, Markus Gritsch wrote: > *) guessClass() in sqliteconnection.py still contains the bug that the > find() result is tested to be > 0 whereas it should be tested != -1. > I see that it is fixed in the trunk. > > *) The trunk contains a copy & paste error: guessClass returns > col.DecimalCol for 'BOOL' columns. Fixed in the revisions 3123-3125. Thank you very much! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Markus G. <m.g...@gm...> - 2007-10-22 14:13:15
|
On 10/22/07, Oleg Broytmann <ph...@ph...> wrote: > On Mon, Oct 22, 2007 at 02:09:37PM +0200, Markus Gritsch wrote: > > *) guessClass() in sqliteconnection.py still contains the bug that the > > find() result is tested to be > 0 whereas it should be tested != -1. > > I see that it is fixed in the trunk. Will this bug also be fixed for the bugfix-branches? Kind regards, Markus |
From: Markus G. <m.g...@gm...> - 2007-10-22 14:15:37
|
On 10/22/07, Markus Gritsch <m.g...@gm...> wrote: > On 10/22/07, Oleg Broytmann <ph...@ph...> wrote: > > On Mon, Oct 22, 2007 at 02:09:37PM +0200, Markus Gritsch wrote: > > > *) guessClass() in sqliteconnection.py still contains the bug that the > > > find() result is tested to be > 0 whereas it should be tested != -1. > > > I see that it is fixed in the trunk. > > Will this bug also be fixed for the bugfix-branches? I'm sorry, it is already fixed. Kind regards, Markus |