Re: [sqlite] New bug: PRAGMA cache_size requires write lock on DB

2013-07-23 Thread Dan Kennedy 

On 07/23/2013 01:38 AM, Philip Goetz wrote:

I have a Perl program that we have run successfully every day for
almost the past 2 years, but which crashes today with the error
message:

FATAL ERR: Can't do PRAGMA cache_size = 100: attempt to write a
readonly database

The SQLite database in question is readonly (group and world have only
read permission), and always has been, and the code has always used
PRAGMA cache_size = 100 on it immediately after opening its
readonly connection.

Running Perl 5.16.1 built for x86_64-linux-thread-multi on CentOS 6.
Have DBD::SQLite v. 1.39, Bundle::DBI 1.627.
The line of code that triggers the crash is

$dbh->do($cmd) or die "FATAL ERR: Can't do $cmd: $DBI::errstr";

where $cmd = "PRAGMA cache_size = 100"

Is there a way to set the default cache size for this sqlite db?

Is there a journal file in the file system?

If it is the first statement executed, "PRAGMA cache_size" will try
to read the schema from the database. If there is a hot-journal in
the file system, then it will need to be rolled back first. A
read-only connection can't do this.

Dan.





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

Re: [sqlite] Multiple autoinc columns?

2013-07-23 Thread Igor Tandetnik 

On 7/24/2013 12:34 AM, Kai Peters wrote:

Is it possible to have two (or more) autoincrement columns per table?


No, not automatically. With some work, you could simulate it using AFTER 
INSERT trigger.

--
Igor Tandetnik

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

[sqlite] Multiple autoinc columns?

2013-07-23 Thread Kai Peters 
Is it possible to have two (or more) autoincrement columns per table?
___
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] New bug: PRAGMA cache_size requires write lock on DB

2013-07-23 Thread Richard Hipp 
On Mon, Jul 22, 2013 at 2:38 PM, Philip Goetz  wrote:

> I have a Perl program that we have run successfully every day for
> almost the past 2 years, but which crashes today with the error
> message:
>
> FATAL ERR: Can't do PRAGMA cache_size = 100: attempt to write a
> readonly database
>

Unable to recreate the problem here with 3.7.17 or 3.8.0, unix or windows.



>
> The SQLite database in question is readonly (group and world have only
> read permission), and always has been, and the code has always used
> PRAGMA cache_size = 100 on it immediately after opening its
> readonly connection.
>
> Running Perl 5.16.1 built for x86_64-linux-thread-multi on CentOS 6.
> Have DBD::SQLite v. 1.39, Bundle::DBI 1.627.
> The line of code that triggers the crash is
>
> $dbh->do($cmd) or die "FATAL ERR: Can't do $cmd: $DBI::errstr";
>
> where $cmd = "PRAGMA cache_size = 100"
>
> Is there a way to set the default cache size for this sqlite db?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
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] Sqlite locking issue with ATTACH'ed databases

2013-07-23 Thread Loren Keagle 
I've narrowed this locking issue down to a very simply test case.  It seems as 
though having the same file attached multiple times with different names 
prevents exclusive or immediate transactions from acquiring a lock.  Deferred 
transactions still seem to work fine.  Try the following code:

#define SQLITE_CHECK(s) do { int localret = (s); if (localret != SQLITE_OK) 
exit(__LINE__); } while (false);

int main (void)
{
::DeleteFile(L"Test.sqlite");
::DeleteFile(L"TestSub.sqlite");

// Open master database
sqlite3* db = NULL;
SQLITE_CHECK(sqlite3_open_v2("Test.sqlite", , SQLITE_OPEN_FULLMUTEX 
| SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
// Attach write database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as 
write1;", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "CREATE TABLE write1.TestTable (id 
INTEGER PRIMARY KEY, IntColumn INTEGER);", NULL, NULL, NULL));
// Attach read database
SQLITE_CHECK(sqlite3_exec(db, "ATTACH DATABASE 'TestSub.sqlite' as 
read1;", NULL, NULL, NULL));

 Detach read db
//SQLITE_CHECK(sqlite3_exec(db, "DETACH DATABASE read1;", NULL, NULL, 
NULL));

// Exclusive transactions fail
SQLITE_CHECK(sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION;", NULL, 
NULL, NULL));
// Immediate transactions fail
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN IMMEDIATE TRANSACTION;", NULL, 
NULL, NULL));
// Deferred transactions are fine
//SQLITE_CHECK(sqlite3_exec(db, "BEGIN DEFERRED TRANSACTION;", NULL, 
NULL, NULL));

// Write to the database via the write1 table
SQLITE_CHECK(sqlite3_exec(db, "INSERT INTO write1.TestTable (IntColumn) 
VALUES (1);", NULL, NULL, NULL));
SQLITE_CHECK(sqlite3_exec(db, "COMMIT TRANSACTION;", NULL, NULL, NULL));
return 0;
}

Currently my workaround is to conditionally detach the read database if it's 
pointed to the same file index as the write database.  I'd like to know whether 
this is intended behavior.  There doesn't seem to be any limitations in the 
documentation about attaching the same file multiple times.  It seems like it 
should be a bug, because it works for deferred transactions.

Can someone knowledgeable in the ATTACH/DETACH behavior comment on this?



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Loren Keagle
> Sent: Monday, July 22, 2013 2:38 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 67, Issue 18
>
> > "The sqlite3_reset() function is called to reset a prepared statement
> > object back to its initial state, ready to be re-executed. Any SQL
> > statement variables that had values bound to them using the
> > sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
> > to reset the bindings."
> >
> > To close a prepared statement you need to use finalize.
> >
> > "The sqlite3_finalize() function is called to delete a prepared
> > statement. If the most recent evaluation of the statement encountered
> > no errors or if the statement is never been evaluated, then
> > sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
> > statement S failed, then sqlite3_finalize(S) returns the appropriate
> > error code or extended error code."
> >
> > You have one prepared statement open and then try to start a transaction.
> > This gives you a busy error.
>
> I'm sorry, but this doesn't fit with my experience with sqlite at all.  I 
> create
> transactions all the time while having unfinalized prepared statements.  As
> long as the statements are reset, they should not be active, and a "BEGIN
> EXCLUSIVE" does not return SQLITE_BUSY.
>
> This issue seems to be related to having the same file attached with multiple
> database names.  See my earlier response with sample code that
> demonstrates the problem.
>
> One experiment I have not yet tried is to finalize and re-prepare any existing
> statements that were prepared against the first attached file, after attaching
> the same file a second time.  If that works, then I would still consider it a 
> bug
> in sqlite, as the locking subsystem should still behave consistently with no
> active statements.
>
> ~Loren
>
>
>
> > On 07/17/2013 06:56 PM, Du?an Paulovi? wrote:
> > > If you remove a busy check, does it output any statements?
> > > Do you have any custom functions/operations running so they could
> > > block sqlite in creating new statement?
> > >
> > >
> > > 2013/7/17 Loren Keagle 
> > >
> > >> Hi everyone,
> > >>
> > >> I have an interesting locking problem that I'm wondering if someone
> > >> can help with some insight.
> > >>
> > >> I have a master database with some metadata, and several
> > >> sub-databases to store logging events. I have one reader object and
> > >> one writer object that attach to the sub-databases and

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

[sqlite] Compile option documentation conflict

2013-07-23 Thread Ben 
There is a minor conflict in the online documentation.

This page: http://www.sqlite.org/fts3.html#section_2
States that: "There is not a separate SQLITE_ENABLE_FTS4 compile-time option" 
and that enabling FTS3 also includes FTS4 support.

This page: http://www.sqlite.org/compile.html
States that the option "SQLITE_ENABLE_FTS3" enables FTS3 support (without 
mentioning FTS4).
It also lists the option "SQLITE_ENABLE_FTS4" with a description stating that 
this enables FTS3 and 4.


For myself at least, adding SQLITE_ENABLE_FTS3 seems to enable both just fine.

Regards,

Ben Barnett
___
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] sqlite on ESXI hypervisor

2013-07-23 Thread Rob Willett 
Hi,

Apologies for jumping in here.

I was interested in your question as I have some development going on which 
will eventually go into production on a VMWare ESXI server, which could be 
either Linux or Windows.

When you say you are running sqlite3 on esxi5 do you mean that you are running 
sqlite3 on a host system on top of Esxi 5 (e.g. Linux or Windows Server) or 
actually in the hypervisor itself? 

I know you say "esxi 5.0 (VMware hypervisor)" in your original email but wanted 
to check the details. 

Thanks,

Rob.

On 23 Jul 2013, at 15:04, Clemens Ladisch  wrote:

> 1 1 wrote:
>> I've tried to run the latest version of sqlite3 on esxi 5.0 (VMware
>> hypervisor), but unsuccessfully. Strace shows "fcntl function not
>> implemended".
> 
> Apparently, VMFS does not implement file locking.
> 
> Try using the unix-dotfile VFS, or unix-none if you can guarantee that
> the database will never be accessed concurrently.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Access Permission on Table

2013-07-23 Thread techi eth 
I am getting request from different process for different SQlite3 operation
to be performed. Here I want to restrict Update/Alter/Drop operation to
creator of the table but read can be done by all.

I am using sqlite3_exec() for query execution. In those scenarios can I
directly map set_authorization function.

Appreciate if I will get link to example.

Cheers-

Techi


On Mon, Jul 22, 2013 at 2:53 PM, Simon Davies
wrote:

> On 22 July 2013 10:02, techi eth  wrote:
> > Is their any statement I can build using SQLite3, which will provide me
> > access permission over table.
> >
> > Let us say I have 3 tables (table 1, table 2, table 3). Here I am looking
> > Read permission for one->many but Write can be done by creator of table
> > only. If other will try to access then they couldn't be able to do &
> error
> > will be thrown.
>
> Perhaps http://www.sqlite.org/c3ref/set_authorizer.html helps...
>
> >
> > Cheers-
> >
> > Techi
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on ESXI hypervisor

2013-07-23 Thread Clemens Ladisch 
1 1 wrote:
> I've tried to run the latest version of sqlite3 on esxi 5.0 (VMware
> hypervisor), but unsuccessfully. Strace shows "fcntl function not
> implemended".

Apparently, VMFS does not implement file locking.

Try using the unix-dotfile VFS, or unix-none if you can guarantee that
the database will never be accessed concurrently.


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

Re: [sqlite] Memory Access

2013-07-23 Thread Simon Slavin 

On 23 Jul 2013, at 12:26pm, techi eth  wrote:

> I would like to check how we can restrict SQLite3 for more
> updating/dropping (I think this two will restrict flash life) based on
> memory life.
> 
> If I say my flash will support ‘N’ Thousand access over the life time.

You are trying to minimise block writing.  As always, you have several 
compromises in mind.  Most of them would be controlled by PRAGMAs so look here:



I recommend

PRAGMA auto_vacuum = NONE
PRAGMA automatic_index = OFF

and maybe

PRAGMA journal_mode = OFF
PRAGMA synchronous = 0 | OFF

The first two represent compromises between space used and the number of writes 
to the database file.  They decrease the number of writes but allow the 
database to use more space.

The second two represent compromises between data integrity and the number of 
writes to the database file.  They decrease the number of writes but increase 
the chance that if your application (or the entire device) crashes, the data 
left in the database will be older.

There's also a discussion to be had if you decide you do want to use a Journal. 
 I am not sure which journalling mode would involve the fewest writes to 
storage.

You should note that SQLite is not particularly bad at doing lots of writes.  
Both iPhone and Android phones, for example, constantly write log files to 
storage.  If your device memory is reaching its cycle limit, SQLite may not be 
the cause of your problem.

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

[sqlite] New bug: PRAGMA cache_size requires write lock on DB

2013-07-23 Thread Philip Goetz 
I have a Perl program that we have run successfully every day for
almost the past 2 years, but which crashes today with the error
message:

FATAL ERR: Can't do PRAGMA cache_size = 100: attempt to write a
readonly database

The SQLite database in question is readonly (group and world have only
read permission), and always has been, and the code has always used
PRAGMA cache_size = 100 on it immediately after opening its
readonly connection.

Running Perl 5.16.1 built for x86_64-linux-thread-multi on CentOS 6.
Have DBD::SQLite v. 1.39, Bundle::DBI 1.627.
The line of code that triggers the crash is

$dbh->do($cmd) or die "FATAL ERR: Can't do $cmd: $DBI::errstr";

where $cmd = "PRAGMA cache_size = 100"

Is there a way to set the default cache size for this sqlite db?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite on ESXI hypervisor

2013-07-23 Thread 1 1 
Hello.

I've tried to run the latest version of sqlite3 on esxi 5.0 (VMware 
hypervisor), but unsuccessfully. Strace shows "fcntl function not implemended". 
Older versions return "disk i/o errors" or "database is locked" then create 
table request invoked. Do you have any ideas how to resolve this trouble?

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

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-23 Thread RSmith 


- You have another open operation or transaction which has not been
committed or finalized or in some way completed.


If I only have one connection, there can't be another uncommitted transaction?
Well there can be, which would cause a BUSY signal. Unless you've had success beginning the new transaction, in which case it can be 
the only open transaction. Do all prior end transaction operations return SQLITE_OK?



Bottom line is this: SQLite can't report a BUSY signal if it isn't actually
BUSY.  Being busy means it is waiting to finish some open DB operation
(which only you could have started) and you have either not yet finished it
or released it of its responsibility, so pay good attention to where you
release/close the queries.

I only have a handful of prepared statements, that are reused by
calling sqlite3_reset in between queries. This should be good, right?
I don't need to finalize them before last use?


Well yes, at least it works for me this way, unless someone else here knows of a quirk with resetting statements which I am unaware 
of. But, what I was trying to highlight,  is that one or more of these other statements HAS to be not reset or not finalized, or 
more simply, HAS to still have responsibility against them towards an open query. Do you check the return results of every 
sqlite3_reset() statement? Do they all return SQLITE_OK?  I once had an "If" statement that sidestepped a reset when an 
sqlite3_step() returned an error, so the error situation remained open, etc.


Bottom line: If only one connection exists, and every created query pointer (prepared statement) was successfully reset or finalized 
returning SQLITE_OK, then it is a physical impossibility for SQLite to return a busy signal (again, unless I am missing something). 
So you need to find the one that fails when reset/finalized.


Also - even if you have multiple connections, like say you are viewing the dataset with a Database management tool while coding, it 
should only ever fail when a table is actually locked, which should only ever happen at the very time you edit it from the other 
connection, and also only if you don't have a timeout pragma set. Setting a timeout and testing it without getting errors, then 
resetting it and getting errors again; is usually an easy test for me to ensure another connection is the culprit:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

 Last but not least, as most coders have experienced at some point - you may have an incorrectly sized buffer actually writing into 
wrong memory locations, usually a silly glitch like sizing a MCBS character array to the length rather than the bytesize or 
whatever, which means one inconspicuous bit of your code is corrupting the memory of another process. There are tools to check this 
with in most dev environments, and it's not a likely fault, but if you have tested everything and it is correct, and things 
happening start looking like "magic", then it's time for finding a memory bug.


Have a great day!

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

[sqlite] Memory Access

2013-07-23 Thread techi eth 
I would like to check how we can restrict SQLite3 for more
updating/dropping (I think this two will restrict flash life) based on
memory life.

If I say my flash will support ‘N’ Thousand access over the life time.

Cheers-

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

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-23 Thread Jesper Baekdahl 
Thank you all very much for your answers..

On Mon, Jul 22, 2013 at 9:07 AM, RSmith  wrote:

> - You have another open operation or transaction which has not been
> committed or finalized or in some way completed.

If I only have one connection, there can't be another uncommitted transaction?

> Bottom line is this: SQLite can't report a BUSY signal if it isn't actually
> BUSY.  Being busy means it is waiting to finish some open DB operation
> (which only you could have started) and you have either not yet finished it
> or released it of its responsibility, so pay good attention to where you
> release/close the queries.

I only have a handful of prepared statements, that are reused by
calling sqlite3_reset in between queries. This should be good, right?
I don't need to finalize them before last use?
___
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