Re: [sqlite] .import null values

[Kurt Welgehausen]

> Affinity Modes ... how do you activate these?

I don't believe strict affinity has been implemented;
someone correct me if that's not right.

Regards

Re: [sqlite] .import null values

[David Finlayson]

It sounds like you loose some of the economy of sqlite by needing a
more sophisticated front-end coded up to drive it (for example, to do
type checking)...

At any rate, my needs aren't too complicated. I was looking for a
portable replacement for MS Access to hold my research results (long
time series of measurements) that was easier to script. Sqlite will
do, I just need to be careful about gotchas like this.

By the way, the affinity modes I was referring to in Section 6 appear
to be different than the "normal" mode discussed in Section 2. They
are either more restrictive (Strict affinity mode), or less
restrictive (No affinity mode). Maybe these have not been implemented
yet?

Thanks for your help.

David



On 10/25/05, Dennis Cote <[EMAIL PROTECTED]> wrote:
> David Finlayson wrote:
>
> >Thanks for the link . In section
> >6 of this page there is mention of Affinity Modes, how do you activate
> >these?
> >
> >
> See section 2.1 of that page.
>
> >As it is now you can get completely irrational behavior with
> >mathematical operators:
> >
> >sqlite> create table temp (a INTEGER);
> >sqlite> insert into temp values("1");
> >sqlite> insert into temp values("1.9");
> >sqlite> insert into temp values("2hello");
> >sqlite> insert into temp values("hello2");
> >sqlite> select * from temp;
> >a
> >--
> >1
> >1.9
> >2hello
> >hello2
> >
> >sqlite> select sum(a) from temp;
> >sum(a)
> >--
> >4.9
> >
> >sqlite> select sum(a) from temp where a = "2hello";
> >sum(a)
> >--
> >2
> >
> >sqlite> select sum(a) from temp where a = "hello2";
> >sum(a)
> >--
> >0
> >
> >That is just irrational. str("2hello") should always return an error
> >or null or maybe even  0, but surely not 2. Is this some kind of
> >casting behavior inherited from C?
> >
> >
> Sort of. The text values are converted from strings to integers using
> atoi() which stops on the first illegal character.
>
> This usually isn't a problem in practice. Its is also kind of
> "irrational" to have both string and numeric data in the same column and
> expect to get a valid sum.
>
> If all the values are actually string representations of actual numeric
> data, the conversions will succeed and the sum will be correct. This
> behavior is a holdover from earlier versions of sqlite which were
> completely typeless (all data was stored as text strings).
>
> >Also, significant white space between delimiters? ",0.9," is numeric
> >", 0.9," is text. That isn't very friendly.
> >
> >
> I agree, but this is only a problem with the .import meta command and
> not something inherent in sqlite. You can always write your own program
> to read the data the way you want (ignoring whitespace) and call the
> correct sqlite API functions to bind that data with the correct type
> when it is inserted into sqlite.
>
>
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

Re: [sqlite] .import null values

[Dennis Cote]

David Finlayson wrote:


Thanks for the link . In section
6 of this page there is mention of Affinity Modes, how do you activate
these?
 


See section 2.1 of that page.


As it is now you can get completely irrational behavior with
mathematical operators:

sqlite> create table temp (a INTEGER);
sqlite> insert into temp values("1");
sqlite> insert into temp values("1.9");
sqlite> insert into temp values("2hello");
sqlite> insert into temp values("hello2");
sqlite> select * from temp;
a
--
1
1.9
2hello
hello2

sqlite> select sum(a) from temp;
sum(a)
--
4.9

sqlite> select sum(a) from temp where a = "2hello";
sum(a)
--
2

sqlite> select sum(a) from temp where a = "hello2";
sum(a)
--
0

That is just irrational. str("2hello") should always return an error
or null or maybe even  0, but surely not 2. Is this some kind of
casting behavior inherited from C?
 

Sort of. The text values are converted from strings to integers using 
atoi() which stops on the first illegal character.


This usually isn't a problem in practice. Its is also kind of 
"irrational" to have both string and numeric data in the same column and 
expect to get a valid sum.


If all the values are actually string representations of actual numeric 
data, the conversions will succeed and the sum will be correct. This 
behavior is a holdover from earlier versions of sqlite which were 
completely typeless (all data was stored as text strings).



Also, significant white space between delimiters? ",0.9," is numeric
", 0.9," is text. That isn't very friendly.
 

I agree, but this is only a problem with the .import meta command and 
not something inherent in sqlite. You can always write your own program 
to read the data the way you want (ignoring whitespace) and call the 
correct sqlite API functions to bind that data with the correct type 
when it is inserted into sqlite.

Re: [sqlite] .import null values

[David Finlayson]

Thanks for the link . In section
6 of this page there is mention of Affinity Modes, how do you activate
these?

As it is now you can get completely irrational behavior with
mathematical operators:

sqlite> create table temp (a INTEGER);
sqlite> insert into temp values("1");
sqlite> insert into temp values("1.9");
sqlite> insert into temp values("2hello");
sqlite> insert into temp values("hello2");
sqlite> select * from temp;
a
--
1
1.9
2hello
hello2

sqlite> select sum(a) from temp;
sum(a)
--
4.9

sqlite> select sum(a) from temp where a = "2hello";
sum(a)
--
2

sqlite> select sum(a) from temp where a = "hello2";
sum(a)
--
0

That is just irrational. str("2hello") should always return an error
or null or maybe even  0, but surely not 2. Is this some kind of
casting behavior inherited from C?

Also, significant white space between delimiters? ",0.9," is numeric
", 0.9," is text. That isn't very friendly.

David


On 10/25/05, Brass Tilde <[EMAIL PROTECTED]> wrote:
> > >sqlite> create table t (a, b integer, c real);
>
> > this is a great explanation, but, why does 1,2,3 return as
> > text,integer,integer and not integer,integer,integer?
>
> I suspect that it's because no type was specified for field 'a', so
> everything is interpreted as text.
>
> Brad
>
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

Re: [sqlite] .import null values

[Brass Tilde]

> >sqlite> create table t (a, b integer, c real);

> this is a great explanation, but, why does 1,2,3 return as
> text,integer,integer and not integer,integer,integer?

I suspect that it's because no type was specified for field 'a', so
everything is interpreted as text.

Brad

Re: [sqlite] .import null values

[Puneet Kishor]

On Oct 25, 2005, at 10:02 AM, Dennis Cote wrote:


..

The following shows what happens when some sample data is imported 
into tables with different column types (note the spaces after the 
commas in the last three rows).


1,2,3
3.14,1.414,2.718
5+6,7-8,9*9
1, 2, 3
3.14, 1.414, 2.718
5+6, 7-8, 9*9

   SQLite version 3.2.7
   Enter ".help" for instructions
   sqlite> create table t (a, b integer, c real);
   sqlite> .separator ,
   sqlite> .import test.csv t
   sqlite> select * from t;
   1,2,3
   3.14,1.414,2.718
   5+6,7-8,9*9
   1, 2, 3
   3.14, 1.414, 2.718
   5+6, 7-8, 9*9
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,integer,integer
   text,real,real
   text,text,text
   text,text,text
   text,text,text
   text,text,text
   sqlite> select sum(a), sum(b), sum(c) from t;
   18,10.414,14.718



this is a great explanation, but, why does 1,2,3 return as 
text,integer,integer and not integer,integer,integer?



--
Puneet Kishor

Re: [sqlite] .import null values

[Dennis Cote]

David Finlayson wrote:


OK, I can deal with the Nulls that in SQL...now, what do you mean that
"even numeric data" is imported as text?

I loaded about 85k records into a database, all numeric, and didn't
notice any problems using max() or stdev(). Should I be concerned
about something?

 


David,

Sqlite is a lot looser with data types than other databases. It actually 
assigns a type to each field value, rather than each column. Internally 
the .import command binds the data it reads from your file to a prepared 
insert statement. It always binds this data as text. Then it relies on 
the type conversion rules based on the column's type affinity to 
actually store the data.


The following shows what happens when some sample data is imported into 
tables with different column types (note the spaces after the commas in 
the last three rows).


1,2,3
3.14,1.414,2.718
5+6,7-8,9*9
1, 2, 3
3.14, 1.414, 2.718
5+6, 7-8, 9*9

   SQLite version 3.2.7
   Enter ".help" for instructions
   sqlite> create table t (a, b integer, c real);
   sqlite> .separator ,
   sqlite> .import test.csv t
   sqlite> select * from t;
   1,2,3
   3.14,1.414,2.718
   5+6,7-8,9*9
   1, 2, 3
   3.14, 1.414, 2.718
   5+6, 7-8, 9*9
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,integer,integer
   text,real,real
   text,text,text
   text,text,text
   text,text,text
   text,text,text
   sqlite> select sum(a), sum(b), sum(c) from t;
   18,10.414,14.718

As you can see, only a few cases actually store the data as numeric 
values, and even then it may store an integer in a real typed column or 
a real value in an integer typed column. Also many functions, like sum() 
above, can produce unexpected results.


The mismatches between the declared and actual types of values in a 
column can also cause problems with math operation such as integer 
results from integer division between columns that are declared as real 
(but actually hold integer data), or a real (non-integer) valued sum 
from a column that is declared integer.


Another problem you may encounter occurs when comparing text and numeric 
values. A value stored as text '1.50' in an untyped column will not 
compare equal to a real value 1.5 as shown in the example below 
(continued with the same table t as above).


   sqlite> delete from t;
   sqlite> insert into t values ('1.50', '2.50', '3.50');
   sqlite> select * from t;
   1.50,2.5,3.5
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,real,real
   sqlite> select a=1.5, b=2.5, c=3.5 from t;
   0,1,1

These issues aren't usually a big problem, but you need to keep them in 
the back of your mind. However, if you want numeric values in your 
database you must import into columns declared with some numeric type.


HTH
Dennis Cote

Re: [sqlite] .import null values

[Kurt Welgehausen]

> "even numeric data" is imported as text

Column types in Sqlite are not strictly enforced.
If you import data that 'look' numeric from your
text file into a column with a numeric type, the
data will be stored in a numeric format; if a
particular value cannot be converted to a numeric
format, it will be stored as text -- so you may
have '' in a numeric column where you want a null.
See  for a more
rigorous explanation.

BTW, you shouldn't have to type individual update
statements for each column. Sqlite is usable from
almost all scripting languages. For example, in
tcl if you get the column names into a variable
called columns, you can do something like

  load tclsqlite3.so
  sqlite dbcmd filename.db
  dbcmd eval {begin exclusive}
  foreach c $columns {
dbcmd eval {upate table set $c=null where $c=''}
  }
  dbcmd eval commit
  dbcmd close


Regards

Re: [sqlite] .import null values

[David Finlayson]

On 10/24/05, Dennis Cote <[EMAIL PROTECTED]> wrote:
> The .import command in sqlite always imports data as text  (even numeric
> data into numeric columns). There is no way to import a NULL value. The
> closest you can get is an empty string or some other sentinel value,
> like the string NULL (but that would require a couple of search and
> replace operations in your source data file). Note, this will give you a
> string containing the word NULL in these columns rather than an SQL NULL
> value.

OK, I can deal with the Nulls that in SQL...now, what do you mean that
"even numeric data" is imported as text?

I loaded about 85k records into a database, all numeric, and didn't
notice any problems using max() or stdev(). Should I be concerned
about something?

It seems like the dot command extensions really ought to have a help
file somewhere!

David



>
> This can of course also be done is SQL as Kurt suggested.
>
> Or you could write your own import program that reads in the file and
> does the inserts with the correct data bound to the columns as the
> correct type (see sqlite3_bind_double() and sqlite3_bind_null()).
>
> HTH
> Dennis Cote
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

Re: [sqlite] .import null values

[Dennis Cote]

David Finlayson wrote:


I'm new to sqlite3 and couldn't find any documentation on the .import
command, so a pointer to the docs may be all that's needed. Sorry if
this is simple...

My problem is that I have been trying to .import null values from a
delimited text file (colon is delimiter) with no success. Here is an
example:

 


David,

The .import command in sqlite always imports data as text  (even numeric 
data into numeric columns). There is no way to import a NULL value. The 
closest you can get is an empty string or some other sentinel value, 
like the string NULL (but that would require a couple of search and 
replace operations in your source data file). Note, this will give you a 
string containing the word NULL in these columns rather than an SQL NULL 
value.


This can of course also be done is SQL as Kurt suggested.

Or you could write your own import program that reads in the file and 
does the inserts with the correct data bound to the columns as the 
correct type (see sqlite3_bind_double() and sqlite3_bind_null()).


HTH
Dennis Cote

Re: [sqlite] .import null values

[David Finlayson]

This works of course (and I hadn't thought of it, honestly) but on
wide tables (lots of columns) UPDATE isn't fun to type.

David


On 10/22/05, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> If you want all the empty fields to be null, why not
> just set them, with a single sql statement (per
> affected column), after the import?
>
> Regards
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

Re: [sqlite] .import null values

[Kurt Welgehausen]

If you want all the empty fields to be null, why not
just set them, with a single sql statement (per
affected column), after the import?

Regards

[sqlite] .import null values

[David Finlayson]

I'm new to sqlite3 and couldn't find any documentation on the .import
command, so a pointer to the docs may be all that's needed. Sorry if
this is simple...

My problem is that I have been trying to .import null values from a
delimited text file (colon is delimiter) with no success. Here is an
example:

Table definition:
create table test (
col1 REAL,
col2 REAL default NULL,
col3 REAL default NULL,
col4 REAL default NULL
);


Example data (3rd column has 2 null values)
2452570.708333:9.7::0.00
2452570.718750:9.6::0.00
2452570.729167:9.6:22.2:0.00
2452570.739583:9.4:23.4:0.00

sqlite> .import /home/david/test.txt test

sqlite> select * from test;
2452570.708333:9.7::0
2452570.71875:9.6::0
2452570.729167:9.6:22.2:0
2452570.739583:9.4:23.4:0

sqlite> select * from test where col3 is null;
sqlite>

sqlite> select * from test where col3 == "";
2452570.708333:9.7::0
2452570.71875:9.6::0

sqlite> insert into test values(1,2,null,3);
sqlite> select * from test;
2452570.708333:9.7::0
2452570.71875:9.6::0
2452570.729167:9.6:22.2:0
2452570.739583:9.4:23.4:0
1:2:NULL:3
sqlite> select * from test where col3 is null;
1:2:NULL:3

So, obviously I didn't import a null value, I imported "".  I've got
100,000+ records to import, I am hoping not to have to insert them
all. Any advise?

Thanks,

David

$ sqlite3 -version
3.2.1
$ uname -a
Linux x 2.6.12-9-686 #1 Mon Oct 10 13:25:32 BST 2005 i686 GNU/Linux





This results in a tab "\t" in place of null.

2452570.708333:9.7:null:0.00
2452570.718750:9.6:null:0.00
2452570.729167:9.6:null:0.00
2452570.739583:9.4:null:0.00



--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays