Re: [sqlite] importing data to a table that has generated-columns
On Mon, 27 Jan 2020 12:00:52 -0700
"Keith Medcalf" wrote:
> 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) ...
Thank you, Keith. Yes, that's what I was referring to.
> >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:
Good to know. I realize of course that computed columns are a new
feature. Knowing that their "computed" property is easily ascertained,
I'm optimistic that the shell's .import will one day avail itself of
that information.
What's old is always new again. ISTR when Microsoft SQL Server added
computed columns, they also got in the way of bulk-loading at first.
The rule for a while was that the buik-copy utility (bcp) couldn't be
used with such tables.
--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing data to a table that has generated-columns
On Monday, 27 January, 2020 10:31, James K. Lowden
wrote:
>On Sun, 26 Jan 2020 12:01:32 -0700
>"Keith Medcalf" wrote:
>> Now that the table exists, use "SELECT * FROM " 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,
SrcIntIDinteger not null references Interfaces(InterfaceID) on
delete cascade,
DstIntIDinteger 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 nametypeaff collnotnull
dflt_value pk rowid autoinc hidden
-- -- -- -- -- --
-- -- -- -- --
-1 INTEGER 0
1 1 0 1
0 ApplianceI integer INTEGER 1
0 0 0 0
1 SrcIntIDinteger INTEGER 1
0 0 0 0
2 DstIntIDinteger INTEGER 1
0 0 0 0
3 Transport textTEXTnocase 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 nametypeaff collnotnull
dflt_value pk rowid autoinc hidden
-- -- -- -- -- --
-- -- -- -- --
0 id integer INTEGER 0
1 1 0 0
1 a datetimeNUMERIC 1
0 0 0 0
2 c BLOB0
0 0 0 2
3 d BLOB0
0 0 0 3
4 e BLOB0
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;
schematype name
Re: [sqlite] importing data to a table that has generated-columns
On Sun, 26 Jan 2020 12:01:32 -0700 "Keith Medcalf" wrote: > Now that the table exists, use "SELECT * FROM " 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. 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? --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing data to a table that has generated-columns
Is there a missed parsing on the input file? It's likely to be a bare word situation where an extra delimiter is encountered in the record. It's probably only on one record in particular, although I don't know if the error message reads that back. The shell is sensitive to these, as it's expected the input file to be CSV compliant. Regards. Brian P Curley On Sun, Jan 26, 2020, 1:37 PM Scott Robison wrote: > On Sun, Jan 26, 2020 at 11:01 AM chiahui chen > wrote: > > > Hi, > > > > After creating a table (total 8 columns including 1 generated column) , I > > tried to import data from a csv file (each record has values for 7 > columns > > that match the non-generated column names and data types, no headers ). > > > > The system issued " error: table has 7 columns but 8 values were > supplied. > > ' I wonder why. > > > > After experimenting different ways to import data to a table that has a > > generated column, so far I only found that .read command with a .sql > file > > that was output as a result of 'mode insert' is successful. Is there > any > > other ways to import data to an existing generated-column table? > > > > I would be inclined to import the csv as a temp table, then write an INSERT > INTO ... SELECT ... query to move the appropriate columns from the temp > table into the new table. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] importing data to a table that has generated-columns
On Sunday, 26 January, 2020 10:29, chiahui chen wrote: >After creating a table (total 8 columns including 1 generated column), I >tried to import data from a csv file (each record has values for 7 >columns that match the non-generated column names and data types, no >headers ). >The system issued "error: table has 7 columns but 8 values were supplied." >I wonder why. This is because of the way the .import (currently) command works in the CLI. Looking at the code this is how it presently works: Check if exists, and if it does not, then create it, optionally using the column names from the CSV import file in the order they appear. Now that the table exists, use "SELECT * FROM " to determine the number of columns in the table (which will include computed always columns, if any). Generate a statement of the form "INSERT INTO VALUES (? ...)" where there is one ? parameter for each column in the table. Loop through the CSV file and bind the parameters to the above statement, executing a sqlite3_step at the end of each row. You will note that: 1. Column Names in the CSV are *only* used if is created. 2. Column Names in the CSV are *not* used for the INSERT statement. 3. The number of column names from which you can "SELECT *" is not the same as the number of columns you can "INSERT INTO" if the table pre-exists and contains generated always columns (which cannot be INSERT INTO'd). 4. Specifying column names (a header line) does not "match up" those column names to the INSERT INTO column names. >After experimenting different ways to import data to a table that has a >generated column, so far I only found that .read command with a .sql >file that was output as a result of 'mode insert' is successful. Is >there any other ways to import data to an existing generated-column table? 1. .import into a temporary then use INSERT INTO () SELECT * FROM and then drop the temporary table. 2. include/load the csv extension and use an insert with an explicit column list to insert into from the csv virtual table. Otherwise, Richard *may* make some changes to the .import logic which *might* appear in the next release version. Otherwise you will have to use one of the three noted word-arounds. -- 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
Re: [sqlite] importing data to a table that has generated-columns
On Sun, Jan 26, 2020 at 11:01 AM chiahui chen wrote: > Hi, > > After creating a table (total 8 columns including 1 generated column) , I > tried to import data from a csv file (each record has values for 7 columns > that match the non-generated column names and data types, no headers ). > > The system issued " error: table has 7 columns but 8 values were supplied. > ' I wonder why. > > After experimenting different ways to import data to a table that has a > generated column, so far I only found that .read command with a .sql file > that was output as a result of 'mode insert' is successful. Is there any > other ways to import data to an existing generated-column table? > I would be inclined to import the csv as a temp table, then write an INSERT INTO ... SELECT ... query to move the appropriate columns from the temp table into the new table. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
