Re: [SQLObject] BoolCol and the backend datatypes

[Oleg B. <ph...@ph...>]

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.