Documentation Query/Correction

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Documentation Query/Correction

John McMahon-2

In documentation for version 3.21.0:

in datatypes3.html
...
3. Type Affinity
...
Each column in an SQLite 3 database is assigned one of the following
type affinities:

     TEXT
     NUMERIC
     INTEGER
     REAL
     BLOB

(Historical note: The "BLOB" type affinity used to be called "NONE". But
that term was easy to confuse with "no affinity" and so it was renamed.)

and in lang_createtable.html
...
CREATE TABLE ... AS SELECT Statements
...
  The declared type of each column is determined by the expression
affinity of the corresponding expression in the result set of the SELECT
statement, as follows:

Expression Affinity Column Declared Type
TEXT "TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL "REAL"
NONE "" (empty string)

In the Expression Affinity table above, should the Expression Affinity
'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical
note:' as per section '3. Type Affinity' in datatypes.html above.

NOTE: I have checked the current on line documents and they match the above.

For consideration.

Regards,
John


--
Regards
    John McMahon
       [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation Query/Correction

David Raymond
Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, as you would think from the phrase "When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column." It gives it no explicit type at all. However, according to...

http://www.sqlite.org/datatype3.html#determination_of_column_affinity
"3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB."

...that lack of any explicit column type will results in an implicit blob affinity. So I guess it still winds up as blob in the end, but in a roundabout way. Though it doesn't show up in things like pragma table_info.



SQLite version 3.23.0 2018-04-02 11:04:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n);

sqlite> create table bar as select i, nu, r, t, b, n from foo;

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n)
table|bar|bar|3|CREATE TABLE bar(
  i INT,
  nu NUM,
  r REAL,
  t TEXT,
  b,
  n
)

sqlite> pragma table_info(foo);
cid|name|type|notnull|dflt_value|pk
0|i|int|0||0
1|nu|numeric|0||0
2|r|real|0||0
3|t|text|0||0
4|b|blob|0||0
5|n||0||0

sqlite> pragma table_info(bar);
cid|name|type|notnull|dflt_value|pk
0|i|INT|0||0
1|nu|NUM|0||0
2|r|REAL|0||0
3|t|TEXT|0||0
4|b||0||0
5|n||0||0


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of John McMahon
Sent: Wednesday, April 04, 2018 3:54 PM
To: SQLite Users
Subject: [sqlite] Documentation Query/Correction


In documentation for version 3.21.0:

in datatypes3.html
...
3. Type Affinity
...
Each column in an SQLite 3 database is assigned one of the following
type affinities:

     TEXT
     NUMERIC
     INTEGER
     REAL
     BLOB

(Historical note: The "BLOB" type affinity used to be called "NONE". But
that term was easy to confuse with "no affinity" and so it was renamed.)

and in lang_createtable.html
...
CREATE TABLE ... AS SELECT Statements
...
  The declared type of each column is determined by the expression
affinity of the corresponding expression in the result set of the SELECT
statement, as follows:

Expression Affinity Column Declared Type
TEXT "TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL "REAL"
NONE "" (empty string)

In the Expression Affinity table above, should the Expression Affinity
'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical
note:' as per section '3. Type Affinity' in datatypes.html above.

NOTE: I have checked the current on line documents and they match the above.

For consideration.

Regards,
John


--
Regards
    John McMahon
       [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Documentation Query/Correction

John McMahon
David

My point point was that in one section of the documentation 'Type
Affinity' was changed from 'NONE' to 'BLOB' with an explanatory note as
to why and in another section it was unchanged. AFAIK type affinity of
'NONE' is the same as 'BLOB' as per the explanatory note. I was just
bringing to attention what I thought was an inconsistency in the
documentation.

John


On 05/04/2018 06:25, David Raymond wrote:

> Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, as you would think from the phrase "When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column." It gives it no explicit type at all. However, according to...
>
> http://www.sqlite.org/datatype3.html#determination_of_column_affinity
> "3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB."
>
> ...that lack of any explicit column type will results in an implicit blob affinity. So I guess it still winds up as blob in the end, but in a roundabout way. Though it doesn't show up in things like pragma table_info.
>
>
>
> SQLite version 3.23.0 2018-04-02 11:04:16
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n);
>
> sqlite> create table bar as select i, nu, r, t, b, n from foo;
>
> sqlite> select * from sqlite_master;
> type|name|tbl_name|rootpage|sql
> table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n)
> table|bar|bar|3|CREATE TABLE bar(
>    i INT,
>    nu NUM,
>    r REAL,
>    t TEXT,
>    b,
>    n
> )
>
> sqlite> pragma table_info(foo);
> cid|name|type|notnull|dflt_value|pk
> 0|i|int|0||0
> 1|nu|numeric|0||0
> 2|r|real|0||0
> 3|t|text|0||0
> 4|b|blob|0||0
> 5|n||0||0
>
> sqlite> pragma table_info(bar);
> cid|name|type|notnull|dflt_value|pk
> 0|i|INT|0||0
> 1|nu|NUM|0||0
> 2|r|REAL|0||0
> 3|t|TEXT|0||0
> 4|b||0||0
> 5|n||0||0
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of John McMahon
> Sent: Wednesday, April 04, 2018 3:54 PM
> To: SQLite Users
> Subject: [sqlite] Documentation Query/Correction
>
>
> In documentation for version 3.21.0:
>
> in datatypes3.html
> ...
> 3. Type Affinity
> ...
> Each column in an SQLite 3 database is assigned one of the following
> type affinities:
>
>       TEXT
>       NUMERIC
>       INTEGER
>       REAL
>       BLOB
>
> (Historical note: The "BLOB" type affinity used to be called "NONE". But
> that term was easy to confuse with "no affinity" and so it was renamed.)
>
> and in lang_createtable.html
> ...
> CREATE TABLE ... AS SELECT Statements
> ...
>    The declared type of each column is determined by the expression
> affinity of the corresponding expression in the result set of the SELECT
> statement, as follows:
>
> Expression Affinity Column Declared Type
> TEXT "TEXT"
> NUMERIC "NUM"
> INTEGER "INT"
> REAL "REAL"
> NONE "" (empty string)
>
> In the Expression Affinity table above, should the Expression Affinity
> 'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical
> note:' as per section '3. Type Affinity' in datatypes.html above.
>
> NOTE: I have checked the current on line documents and they match the above.
>
> For consideration.
>
> Regards,
> John
>
>

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users