On Monday, 27 January, 2020 10:31, James K. Lowden <jklow...@schemamania.org>
wrote:
>On Sun, 26 Jan 2020 12:01:32 -0700
>"Keith Medcalf" <kmedc...@dessus.com> wrote:
>> Now that the table exists, use "SELECT * FROM <table>" to determine
>> the number of columns in the table (which will include computed
>> always columns, if any).
>...
>> Otherwise, Richard *may* make some changes to the .import logic which
>> *might* appear in the next release version.
>I imagine it's already being considered: if pragma table_info included
>a column with the SQL for generated columns (and NULL) otherwise, the
>shell could use that instead of SELECT * to determine the number of
>insertable columns.
That data is already in the schema structures as is the assigned affinity:
SQLite version 3.31.0 2020-01-27 17:01:49
Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
ApplianceID integer not null references Appliances(ApplianceID) on
delete cascade,
SrcIntID integer not null references Interfaces(InterfaceID) on
delete cascade,
DstIntID integer not null references Interfaces(InterfaceID) on
delete cascade,
Transport text not null collate nocase,
SrcHostID integer not null references Hosts(HostID) on delete cascade,
SrcPort integer not null,
DstHostID integer not null references Hosts(HostID) on delete cascade,
DstPort integer not null,
Action integer not null,
Count integer not null,
FileID integer not null references Files(FileID) on delete cascade
);
sqlite> .mode col
sqlite> .head on
sqlite> pragma table_xinfo(details);
cid name type aff coll notnull
dflt_value pk rowid autoinc hidden
---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ----------
-1 INTEGER 0
1 1 0 1
0 ApplianceI integer INTEGER 1
0 0 0 0
1 SrcIntID integer INTEGER 1
0 0 0 0
2 DstIntID integer INTEGER 1
0 0 0 0
3 Transport text TEXT nocase 1
0 0 0 0
4 SrcHostID integer INTEGER 1
0 0 0 0
5 SrcPort integer INTEGER 1
0 0 0 0
6 DstHostID integer INTEGER 1
0 0 0 0
7 DstPort integer INTEGER 1
0 0 0 0
8 Action integer INTEGER 1
0 0 0 0
9 Count integer INTEGER 1
0 0 0 0
10 FileID integer INTEGER 1
0 0 0 0
sqlite> create table x(id integer primary key, a datetime not null, c as (id +
1), d as (id + 2) stored, e as (id + 3));
sqlite> pragma table_xinfo(x);
cid name type aff coll notnull
dflt_value pk rowid autoinc hidden
---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ----------
0 id integer INTEGER 0
1 1 0 0
1 a datetime NUMERIC 1
0 0 0 0
2 c BLOB 0
0 0 0 2
3 d BLOB 0
0 0 0 3
4 e BLOB 0
0 0 0 2
This is my "modified" table_info pragma that returns somewhat more information
for each column (all of which comes from the in the in-memory schema). The
"hidden" value is 0 for visible columns, 1 for "hidden" columns, 2 for computed
virtual columns and 3 for computed stored columns. I also added a
database_info pragma that returns a list of objects in all attached schema's so
that it is easier to define useful information views.
sqlite> create temporary table x(x);
sqlite> .width 8 8 64
sqlite> pragma database_info;
schema type name
-------- --------
----------------------------------------------------------------
main view Details_v
main table sqlite_master
main table Hosts
main table Appliances
main table Files
main table Interfaces
main table RuleSet
main table Details
main table sqlite_stat1
main table sqlite_stat4
main index DetailPortClean
main index DetailsDstIntID
main index DetailsSrcIntID
main index DetailsDstHostID
main index DetailCoalesce
main index sqlite_autoindex_Hosts_1
main index sqlite_autoindex_Appliances_1
main index sqlite_autoindex_Files_1
main index sqlite_autoindex_Interfaces_1
main index sqlite_autoindex_RuleSet_1
main index HostsByName
main index DetailsSrcHostId
main index FilesApplianceID
main index DetailsApplianceID
main index DetailsFileID
temp table x
temp table sqlite_temp_master
main etable sqlite_stmt
main etable json_tree
main etable fts3tokenize
main etable pragma_module_list
main etable wholenumber
main etable pragma_database_list
main etable zipfile
main etable sqlite_dbpage
main etable sqlite_memstat
main etable completion
main etable generate_series
main etable delta_parse
main etable json_each
main etable sqlite_btreeinfo
main etable dbstat
main etable prefixes
main etable carray
main etable transitive_closure
main etable fsdir
>I'm a little confused, though. ISTR the shell does something clever
>with .import, because constraints that enforce numeric types are
>violated even when the data are numeric. Is that simply because the
>shell uses sqlite3_bind_text for every column, and the system doesn't
>attempt to convert numeric text to a numeric type, regardless of the
>column's declared type?
It depends if you are importing into a table that already exists or into a new
one. If you are importing into a new table, then the column names have no
affinity (they are BLOBs) and therefore store the text as text (and the first
row contains column names). If you import into a pre-existing table then
column affinity is applied when importing the data and no header row is parsed
(ie, the header is treated as data).
sqlite> .mode csv
sqlite> .import ../sqlite/t.csv test1
sqlite> .mode col
sqlite> select * from test1;
a b c d
---------- ---------- ---------- ----------
1 2 3 4
2 3 4 5
3 4 5 6
4 5 6 7
sqlite> select typeof(a), a from test1;
typeof(a) a
---------- ----------
text 1
text 2
text 3
text 4
sqlite> drop table test1;
sqlite> create table test1 (a numeric, b numeric, c numeric, d numeric);
sqlite> .mode csv
sqlite> .import ../sqlite/t.csv test1
sqlite> .mode col
sqlite> select * from test1;
a b c d
---------- ---------- ---------- ----------
a b c d
1 2 3 4
2 3 4 5
3 4 5 6
4 5 6 7
sqlite> select typeof(a), a from test1;
typeof(a) a
---------- ----------
text a
integer 1
integer 2
integer 3
integer 4
If you mean importing into a pre-existing table as above that is declared with
"check typeof(a) in ('integer', 'real')" then yes. The check constraint is run
before affinity is applied rather than after (unfortunately) ...
sqlite> drop table test1;
sqlite> create table test1(a numeric check (typeof(a) in ('integer','real')), b
numeric, c numeric, d numeric);
sqlite> .mode csv
sqlite> .import ../sqlite/s.csv test1
../sqlite/s.csv:1: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:2: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:3: INSERT failed: CHECK constraint failed: test1
../sqlite/s.csv:4: INSERT failed: CHECK constraint failed: test1
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
