Re: [sqlite] Is REAL the best universal data type?

2013-07-24 Thread Petite Abeille 

On Jul 23, 2013, at 9:32 PM, Max Vlasov  wrote:

> On Tue, Jul 23, 2013 at 10:09 PM, Petite Abeille
> wrote:
> 
>> 
>> On Jul 23, 2013, at 9:52 AM, Max Vlasov  wrote:
>> 
>>> Basically it's several tables implementing Object-Propery-Value metaphor
>> 
>> Hurray! The Entity–attribute–value (EAV) anti-pattern!
>> 
>> 
> pattern, anti-pattern... I think the main principle should be "whatever
> works" :)

I guess it depends what you mean by "work"… :D

> As for my experience, this was started when I wanted to extract the
> information from the CIA Factbook data. 

So… for example… how would one get, say, the top 3 countries by population 
growth year-on-year in Asia in 2010? Or in the last 5 years?

Or, say, population of one country in 2010 vs. 2011? And calculate the 
difference between the two?

Or, given a country, all its bordering countries? Sorted by border length?

Or, …, well, …, any query really...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Simon Slavin 

On 24 Jul 2013, at 12:04am, j.merr...@enlyton.com wrote:

> I do not know if SQLite has the storage behavior that e.g. PostgreSQL and MS 
> SQL Server have, which is that null values take up absolutely no space.

SQLite uses a one-byte 'type' indicator, followed by some bytes for the value.  
The 'type' tells SQLite how many bytes to expect for the value.  The NULL type 
indicates that zero bytes follow.  So it uses one byte per NULL value.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread j . merrill 
On 07/23/2013 02:52 PM, Max Vlasov wrote:
> I've created a kind of triple storage base with Sqlite db as the container.
> Basically it's several tables implementing Object-Propery-Value metaphor.
> There's only one field for data so thinking about generality I assumed that
> the type for the data field should be TEXT of nothing since most of other
> types (let's forget about blob for a moment) can be stored (and easily
> visualized) with this field. But there are also indexes involved and here
> comes the problem. If I insert natural numbers in some sub-sequence I will
> get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can
> accept any data in any field, so I can change the type to INTEGER and enjoy
> numbered order when there are numbers were added (1, 2, 10, 20). On the
> other side, when we look at real numbers, the problem would still exist. So
> paradoxically probably the best type for universal field container is REAL
> (or NUMERIC) since it will accept data of any type, but has advantage of
> best sorting if reals or integers are involved.
>
> Is this correct or I am missing something?

You could consider having more than one column in the "value" table -- one for 
each data type that you want to have behave the way you want to. (When using 
more traditional SQL implementations that require the data to be "right", this 
lets you have "strongly typed" data -- you put dates into a date[time] column, 
integers in an integer column, etc.)

I do not know if SQLite has the storage behavior that e.g. PostgreSQL and MS 
SQL Server have, which is that null values take up absolutely no space. If 
SQLite behaves that way as well, you could put each value in the the proper 
column for its datatype and not pay any storage penalty for having the extra 
columns.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Max Vlasov 
On Tue, Jul 23, 2013 at 10:09 PM, Petite Abeille
wrote:

>
> On Jul 23, 2013, at 9:52 AM, Max Vlasov  wrote:
>
> > Basically it's several tables implementing Object-Propery-Value metaphor
>
> Hurray! The Entity–attribute–value (EAV) anti-pattern!
>
>
pattern, anti-pattern... I think the main principle should be "whatever
works" :)
As for my experience, this was started when I wanted to extract the
information from the CIA Factbook data. And this was 170 columns for more
than 150 countries. Making it triple storage with the excellent sqlite
support allowed very flexible data and queries, for example showing the
data about single country as a long table with two columns: property
name/property value, not mentioning other comparison queries, vertical or
horizontal depending on the goal. Queries become bigger, I admit, usually
this involves extra join, but they are still manageable

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Petite Abeille 

On Jul 23, 2013, at 9:52 AM, Max Vlasov  wrote:

> Basically it's several tables implementing Object-Propery-Value metaphor

Hurray! The Entity–attribute–value (EAV) anti-pattern!

"… an EAV based approach is an anti-pattern which can lead to longer 
development times, poor use of database resources and more complex queries when 
compared to a relationally-modelled data schema…"

http://en.wikipedia.org/wiki/Entity–attribute–value_model

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Dan Kennedy 

On 07/23/2013 04:53 PM, Max Vlasov wrote:

On Tue, Jul 23, 2013 at 1:38 PM, Dan Kennedy  wrote:


On 07/23/2013 02:52 PM, Max Vlasov wrote:


   So
par adoxically probably the best type for universal field container is
REAL
(or NUMERIC) since it will accept data of any type, but has advantage of
best sorting if reals or integers are involved.

Is this correct or I am missing something?


The only difference between "INTEGER" and "REAL" is that real
values are converted to integers if this is possible without
loss of data. In other respects they are the same. Both try
to convert text values to numbers on insert.


So when building indexes, it doesn't matter whether it's REAL or INTEGER,
the comparison will include both real and fractional parts of values if
they exist, right?


That's correct. Sorting order is not affected.

Dan.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Clemens Ladisch 
Max Vlasov wrote:
> On Tue, Jul 23, 2013 at 1:32 PM, Clemens Ladisch  wrote:
>> But REAL will sort the strings '1', '10', '2' wrong.
>
> What do you mean by "wrong"?
>
> 1, 2, 10, something
> that's what I wanted

So you actually want to sort strings as if they were numbers (if possible)?
In this case, you need NUMBER affinity.

For SQLite's sort order, see .


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Max Vlasov 
On Tue, Jul 23, 2013 at 1:38 PM, Dan Kennedy  wrote:

> On 07/23/2013 02:52 PM, Max Vlasov wrote:
>
>>   So
>> par adoxically probably the best type for universal field container is
>> REAL
>> (or NUMERIC) since it will accept data of any type, but has advantage of
>> best sorting if reals or integers are involved.
>>
>> Is this correct or I am missing something?
>>
>
> The only difference between "INTEGER" and "REAL" is that real
> values are converted to integers if this is possible without
> loss of data. In other respects they are the same. Both try
> to convert text values to numbers on insert.
>

So when building indexes, it doesn't matter whether it's REAL or INTEGER,
the comparison will include both real and fractional parts of values if
they exist, right?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Max Vlasov 
On Tue, Jul 23, 2013 at 1:32 PM, Clemens Ladisch  wrote:

>
>
> But REAL will sort the strings '1', '10', '2' wrong.
>

What do you mean by "wrong"?

The test

CREATE TABLE testtable (id integer primary key, value real);
insert into testtable (value) values ('1');
insert into testtable (value) values ('2');
insert into testtable (value) values ('10');
insert into testtable (value) values ('something');
CREATE INDEX [idx_Testable] ON [testtable] ([Value]);
SELECT * FROM testtable order by value;

will show

1, 2, 10, something
that's what I wanted (except for "something" being exception)

if I change create to
CREATE TABLE testtable (id integer primary key, value);

then the order will be
1, 10, 2, something.
(undesired result)

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Dan Kennedy 

On 07/23/2013 02:52 PM, Max Vlasov wrote:

I've created a kind of triple storage base with Sqlite db as the container.
Basically it's several tables implementing Object-Propery-Value metaphor.
There's only one field for data so thinking about generality I assumed that
the type for the data field should be TEXT of nothing since most of other
types (let's forget about blob for a moment) can be stored (and easily
visualized) with this field. But there are also indexes involved and here
comes the problem. If I insert natural numbers in some sub-sequence I will
get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can
accept any data in any field, so I can change the type to INTEGER and enjoy
numbered order when there are numbers were added (1, 2, 10, 20). On the
other side, when we look at real numbers, the problem would still exist. So
paradoxically probably the best type for universal field container is REAL
(or NUMERIC) since it will accept data of any type, but has advantage of
best sorting if reals or integers are involved.

Is this correct or I am missing something?


The only difference between "INTEGER" and "REAL" is that real
values are converted to integers if this is possible without
loss of data. In other respects they are the same. Both try
to convert text values to numbers on insert. For example:


  sqlite> CREATE TABLE t2(a INTEGER);
  sqlite> INSERT INTO t2 VALUES('one');
  sqlite> INSERT INTO t2 VALUES('1.5');
  sqlite> INSERT INTO t2 VALUES('2.0');
  sqlite> SELECT typeof(a), a FROM t2;
  text|one
  real|1.5
  integer|2


Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is REAL the best universal data type?

2013-07-23 Thread Clemens Ladisch 
Max Vlasov wrote:
> I've created a kind of triple storage base with Sqlite db as the container.
> Basically it's several tables implementing Object-Propery-Value metaphor.
> There's only one field for data so thinking about generality I assumed that
> the type for the data field should be TEXT of nothing since most of other
> types (let's forget about blob for a moment) can be stored (and easily
> visualized) with this field. But there are also indexes involved and here
> comes the problem. If I insert natural numbers in some sub-sequence I will
> get non-naturally sorted ones (1, 10, 2, 20).

You have found that you should not use TEXT affinity for this column.

> But we know that Sqlite can accept any data in any field, so I can change
> the type to INTEGER and enjoy numbered order when there are numbers were
> added (1, 2, 10, 20). On the other side, when we look at real numbers, the
> problem would still exist. So paradoxically probably the best type for
> universal field container is REAL (or NUMERIC) since it will accept data
> of any type,

In SQLite, tables and indexes will always accept values of any type.

> but has advantage of best sorting if reals or integers are involved.

But REAL will sort the strings '1', '10', '2' wrong.

Columns with TEXT/NUMERIC/INTEGER/REAL affinity might change some
values (if those values can be converted).

So the only correct affinity for your value column is NONE.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is REAL the best universal data type?

2013-07-23 Thread Max Vlasov 
I've created a kind of triple storage base with Sqlite db as the container.
Basically it's several tables implementing Object-Propery-Value metaphor.
There's only one field for data so thinking about generality I assumed that
the type for the data field should be TEXT of nothing since most of other
types (let's forget about blob for a moment) can be stored (and easily
visualized) with this field. But there are also indexes involved and here
comes the problem. If I insert natural numbers in some sub-sequence I will
get non-naturally sorted ones (1, 10, 2, 20). But we know that Sqlite can
accept any data in any field, so I can change the type to INTEGER and enjoy
numbered order when there are numbers were added (1, 2, 10, 20). On the
other side, when we look at real numbers, the problem would still exist. So
paradoxically probably the best type for universal field container is REAL
(or NUMERIC) since it will accept data of any type, but has advantage of
best sorting if reals or integers are involved.

Is this correct or I am missing something?

Thanks,

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users