Re: [sqlite] adding/dropping foreign key to existing table

[Simon Slavin]

On 23 Mar 2011, at 2:55am, BareFeetWare wrote:

> begin immediate;
> create temp table "My Table Backup" as select * from "My Table";
> drop table "My Table";
> create table "My Table" ();
> insert into "My Table" select * from "My Table Backup";
> drop table "My Table Backup";
> commit;
> 
> Unfortunately SQLite doesn't automatically rollback a transaction if the 
> create or drop statements fail. So you have to detect any errors and, if so, 
> issue a rollback (instead of commit).

If I may suggest an additional check, compare the number of rows in the 
original table and the replacement table.  If they don't match, something went 
wrong.

Last month I spent two days rescuing data on a problem which would have been 
caught by that test.  Fortunately I was being paid for it, but I had better 
things to do with the time.

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

Re: [sqlite] adding/dropping foreign key to existing table

[BareFeetWare]

On 23/03/2011, at 1:17 PM, Sam Carleton wrote:

> I don't see any examples on http://www.sqlite.org/foreignkeys.html how to 
> either add or drop a foreign key to an existing table.  What might that 
> syntax look like exactly?

You have to drop the old table and create a new one with the changed foreign 
keys. If you have data in the table, you'll want to back it up fist, like this:

begin immediate;
create temp table "My Table Backup" as select * from "My Table";
drop table "My Table";
create table "My Table" ();
insert into "My Table" select * from "My Table Backup";
drop table "My Table Backup";
commit;

Unfortunately SQLite doesn't automatically rollback a transaction if the create 
or drop statements fail. So you have to detect any errors and, if so, issue a 
rollback (instead of commit).

> Also, from a performance perspective, is there an advantage to using a
> foreign key in SQLite verses just an index? (aka, is it worth my time to add
> the key to begin with, I understand there are draw backs).

An index and a foreign key serve different purposes. What are you trying to 
achieve. Post the relevant parts of your schema.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

[sqlite] adding/dropping foreign key to existing table

[Sam Carleton]

I don't see any examples on http://www.sqlite.org/foreignkeys.html how to
either add or drop a foreign key to an existing table.  What might that
syntax look like exactly?

Also, from a performance perspective, is there an advantage to using a
foreign key in SQLite verses just an index? (aka, is it worth my time to add
the key to begin with, I understand there are draw backs).

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

[sqlite] System.Data.Sqlite.Linq

[Adam Bright]

Hello all,
 
Anyone know where I can download the latest System.Data.Sqlite.Linq.dll?  I 
tried compiling it from what I found at System.Data.Sqlite.org, but it gave me 
errors because the SQL Generation folder was blank.  I also tried SourceForge, 
but the Setup file said "A network error occurred while trying to read from the 
file " perhaps because I'm on 64 bit.
 
Thanks in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table if not exists and insert seed data

[BareFeetWare]

On 22/03/2011, at 9:04 AM, Erich93063 wrote:

> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???

Others have suggested solutions that require you to step back and forth from 
SQL to application code (except Max's solution), but you can do this in pure 
SQL. Something like:

begin immediate;
create temp table if not exists "Variables" (Name text unique collate nocase, 
"Value");
insert or replace into "Variables" select 'MyTable exists', 1 in (select Name 
from SQLite_Master where type = 'table');
create table if not exists "My Table" (ID integer primary key not null, Name 
text unique); -- or whatever your definition
insert into "My Table" (Name) select 'First row' where (select "Value" from 
"Variables" where Name = 'MyTable exists');
insert into "My Table" (Name) select 'Second row' where (select "Value" from 
"Variables" where Name = 'MyTable exists');
insert into "My Table" (Name) select 'Third row' where (select "Value" from 
"Variables" where Name = 'MyTable exists');
commit;


Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Nico Williams]

You can store any big-number representation you like as TEXT or BLOB
values.  The main issue is that you'll lose syntactic sugar: SQLite3
won't be able to treat those as numeric values, therefore it won't be
able to compare numerically nor use arithmetic with such values.  You
can get some of that back with user-defined functions, but not
automatic conversions.  Just pick a decent bignum library,
canonicalize bignums before binding such values to any statements, and
add user-defined functions and collations via which to invoke the
bignum library from SQL.

To do better than this would probably require significant surgery on
SQLite3.  (Though it might not be a bad idea anyways, but who would do
it?)

If you can manage to live with integers and use those to represent
floating point values, then that's by far your best option.  (The
typical example on this list is money.  For something like U.S.
dollars you'd store numbers as integer counts of tenths of a cent, so
that $2.599 becomes 2599, allowing you to count over $9,000 trillion,
which will be enough for a while, but is already on the low side.)
For scientific, mathematical, or other purposes where you really need
huge numbers, you may want to pursue the bignum shoehorn approach.

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

Re: [sqlite] SQLite IDE's

[Sam Carleton]

On Tue, Mar 22, 2011 at 4:02 PM, Ben  wrote:
> You don't mention which platform you're on, but for OS X there's a good 
> comparison table of SQLite editors here:
>
> http://www.barefeetware.com/sqlite/compare/?ml

Ben,

I have a Mac, but I am currently targetting Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite IDE's

[Sam Carleton]

On Tue, Mar 22, 2011 at 3:26 PM, Jonathan Allin
 wrote:
> Would the diff have to do more than compare (in some nice graphical way) the
> two sqlite_master tables?

What I am looking for is this:  I have one version of the DB out in
the field, I have made changed to it in development, I want to see
exactly what has changed.  Ideally I would like to see something basic
like list of tables/views/triggers/indexes that have changed, then a
more detail one to show if the contents are actually different.

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

Re: [sqlite] SQLite IDE's

[Ben]

You don't mention which platform you're on, but for OS X there's a good 
comparison table of SQLite editors here:

http://www.barefeetware.com/sqlite/compare/?ml

- Ben


On 22 Mar 2011, at 18:46, Sam Carleton wrote:

> I am looking for a good SQLite IDE, SQLite Maestro looks like a good
> candidate with most all the features I need.  The price is good, too.
> The one feature I don't see is a tool that can do a diff on the DDL of
> two SQLite db's.  Does anyone know of any other SQLite IDE's that have
> that ability?
> 
> Sam
> ___
> 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 IDE's

[Jonathan Allin]

Would the diff have to do more than compare (in some nice graphical way) the
two sqlite_master tables?

 

From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille
Sent: 22 March 2011 19:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite IDE's

 

 

On Mar 22, 2011, at 7:46 PM, Sam Carleton wrote:

> The one feature I don't see is a tool that can do a diff on the DDL of two
SQLite db's.

Ah, yes, something along the lines of Oracle's
DBMS_METADATA_DIFF.COMPARE_ALTER [1] would be handy.

Alternatively, what about a simple DIFF(1) between the DDLs?

[1]
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_metadiff.ht
m

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

  _  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3522 - Release Date: 03/22/11

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

Re: [sqlite] SQLite IDE's

[Petite Abeille]

On Mar 22, 2011, at 7:46 PM, Sam Carleton wrote:

> The one feature I don't see is a tool that can do a diff on the DDL of two 
> SQLite db's.

Ah, yes, something along the lines of Oracle's DBMS_METADATA_DIFF.COMPARE_ALTER 
[1] would be handy.

Alternatively, what about a simple DIFF(1) between the DDLs?

[1] 
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_metadiff.htm

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

[sqlite] SQLite IDE's

[Sam Carleton]

I am looking for a good SQLite IDE, SQLite Maestro looks like a good
candidate with most all the features I need.  The price is good, too.
The one feature I don't see is a tool that can do a diff on the DDL of
two SQLite db's.  Does anyone know of any other SQLite IDE's that have
that ability?

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

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

[Dan Kennedy]

On 03/23/2011 01:07 AM, Jay A. Kreibich wrote:
> On Tue, Mar 22, 2011 at 06:25:04PM +0700, Dan Kennedy scratched on the wall:
>
>> SQLite assumes that the result of each expression in the WHERE
>> clause depends only on its inputs. If the input arguments are
>> the same, the output should be do. Since random() has no inputs,
>> SQLite figures that it must always return the same value.
>
>To what degree?  And expression like "...WHERE 20<= (random()%100)"
>has no "inputs" other than constants, but is still evaluated once per
>row.  Or is it just raw functions and column references, and not the
>expression as a whole?


I think once you are trying to predict how many times or exactly
when a user function will be called for a given SQL statement you
are technically into the realms of undefined behaviour.

And again, technically, SQLite assumes that the value returned by
a user-defined function are a function of its inputs. Once instance
of where this assumption is used is with virtual tables. If you do:

   SELECT * FROM vtab WHERE col = userfunction();

and the xBestIndex() method says it can handle "col = ?" but does
not set the corresponding "aConstraintUsage[x].omit" flag, SQLite
will evaluate userfunction() once to pass to the xFilter
method, and then again for each row visited by the virtual table
cursor. If the result of userfunction() is not
stable, the query could return difficult to explain results.

I think there might be other such examples too. Left joins. Where
clauses that include OR operators. That sort of thing.

That said, we're aware of the way random() and user-functions with
side-effects are often used. I don't think it's something that
would get changed capriciously.

Dan.



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

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

[Jay A. Kreibich]

On Tue, Mar 22, 2011 at 06:25:04PM +0700, Dan Kennedy scratched on the wall:

> SQLite assumes that the result of each expression in the WHERE
> clause depends only on its inputs. If the input arguments are
> the same, the output should be do. Since random() has no inputs,
> SQLite figures that it must always return the same value.

  To what degree?  And expression like "...WHERE 20 <= (random()%100)"
  has no "inputs" other than constants, but is still evaluated once per
  row.  Or is it just raw functions and column references, and not the
  expression as a whole?

  Either way, it would seem a function with no inputs should always be
  considered non-constant.  Unless someone writes a function that boils
  down to func(){return VALUE;}, it is very likely the function
  references some external value or state, and is unlikely to return
  the same value.

  I realize that most systems will only evaluates random() once, even
  in a larger expression, but I've always found it nice that SQLite did
  not in expressions like the one I gave.  It makes it much easier to
  sample data.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table if not exists and insert seed data

[A Gilmore]

On 11-03-22 10:40 AM, Simon Slavin wrote:
>
> Or just do a 'SELECT id FROM whatever LIMIT 1'.  If you get any error, it 
> doesn't exist, so create it and fill it.
>
> Or look in sqlite_master for an entry for the TABLE.
>
> Simon.

Could also use INSERT OR IGNORE statements for the seed data if the rows 
would conflict.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Simon Slavin]

On 22 Mar 2011, at 1:12pm, LacaK wrote:

>> You still don't say what you're planning on doing with these number...just 
>> displaying them?
> 
> Yes may be ...
> I am working on modification of database component for accessing SQLite3 
> databases for FreePascal project.
> We map declared column's types to native freepascal internal field types.

Here are two options which will let you get the contents back to the original 
precision:

A) Store the values as BLOBs.
B) Store the value as TEXT, but add a non-digit to the beginning of each number 
value, for example

X24395734857634756.92384729847239842398423964294298473927

Both methods will prevent SQLite from trying to see the value as a number.  Oh 
and since nobody seems to have pointed it out yet, SQLite doesn't have a 
NUMERIC or a DECIMAL column type.  The types can be found here:

http://www.sqlite.org/datatype3.html

Putting INTEGER and REAL together gives you NUMERIC, but there's no way to 
declare a column of that type, just a value.  The page actually rehearses your 
problem, showing when strings containing numeric values can be converted to a 
number.

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

Re: [sqlite] Create table if not exists and insert seed data

[Simon Slavin]

On 22 Mar 2011, at 1:38pm, Pavel Ivanov wrote:

> You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If
> it succeeds then you populate table with data (remember to do that in
> the same transaction where you created the table). If CREATE TABLE
> fails then you don't insert your data.

Or just do a 'SELECT id FROM whatever LIMIT 1'.  If you get any error, it 
doesn't exist, so create it and fill it.

Or look in sqlite_master for an entry for the TABLE.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

On 3/22/2011 12:50 PM, Jonathan Allin wrote:
> Igor,
>
> Is there another way of looking at the problem by considering how Java and
> other libraries handle big integers and big decimals?

They have data types for them, and the library to support them.

> Can you store the numeric value across sufficient cells necessary to achieve
> the required precision?

Who is "you" in this picture? If you mean "SQL engine", there are plenty 
that can do this, but SQLite is not one of them (hence "lite"). If you 
mean "application programmer", then sure, you can invent some 
representation for your big numbers (or use a library that provides 
one), and store them in the database as text or blobs.
-- 
Igor Tandetnik

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Jonathan Allin]

Igor, 

 

Is there another way of looking at the problem by considering how Java and
other libraries handle big integers and big decimals?

 

Can you store the numeric value across sufficient cells necessary to achieve
the required precision?

 

¬Jonathan

 

From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Konrad J Hambrick
Sent: 22 March 2011 15:25
To: General Discussion of SQLite Database
Subject: Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

 

 

LacaK wrote, On 03/22/2011 08:53 AM:
>>> / Problem will be solved if SQLite will store such values as text ... so


Laco --

Problem will be solved when you teach SQLite to store such values as text.

This library might help your project:

http://speleotrove.com/decimal/

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

  _  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3521 - Release Date: 03/21/11

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Konrad J Hambrick]

LacaK wrote, On 03/22/2011 08:53 AM:
>>> / Problem will be solved if SQLite will store such values as text ... so


Laco --

Problem will be solved when you teach SQLite to store such values as text.

This library might help your project:

http://speleotrove.com/decimal/

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

On 3/22/2011 9:53 AM, LacaK wrote:
> Base idea is store as TEXT when :
> 1. column value is supplied as TEXT (only in case sqlite3_bind_text)
> 2. conversion to REAL or INTEGER leads to loose of precision (digits)
>
> I do not know details how to implement it ;-)
> May be, 1. strip out leading and trailing spaces and zeroes 2. and then
> analyze string if contains only digits and decimal separator

Which of the following should be left as text:

'1e+003'
'10e2'
'1000'
'+.01e05'

-- 
Igor Tandetnik

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

Re: [sqlite] Create table if not exists and insert seed data

[Drake Wilson]

Quoth Philip Graham Willoughby , on 
2011-03-22 10:18:08 +:
> Yes, I had this problem - if sqlite3_open_v2 had an equivalent to
> O_EXCL it would make this a lot easier: you would only try to run
> your schema/prepopulating SQL if the exclusive open worked. If it
> failed you would retry a non-exclusive open and then assume the
> database was initialised. If such a flag is added it would be
> helpful for it to implicitly get an exclusive lock on the database
> it creates so that no-one else can try any queries before the schema
> is there.

If you control the database schema, user_version is a convenient place
to put such markers, albeit not a foolproof one; you can set it to a
magic number after initializing the DB structure, then query it on
open.  The main failure mode is if someone hands you a completely
unrelated database that already has schema elements in it that collide
with yours.

Querying page_count or doing « SELECT COUNT(*) FROM sqlite_master »
may also allow you to determine whether you have just created a
database, though it's also not foolproof since there's no interface
guarantee linking the two.

You probably want to do a BEGIN EXCLUSIVE before loading the schema in
most cases.  (The EXCLUSIVE may not strictly be necessary, but I find
it makes things clearer.)

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

/ Problem will be solved if SQLite will store such values as text ... so

/>>/ will behave like this:
/>>/ 1. is supplied value in TEXT (sqlite3_bind_text)
/>>/ 2. if yes then try convert this text value into INTEGER or REAL
/>>/ 3. convert back to text and compare with original value
/>>/ 4. if equal then store it as INTEGER or REAL, if not then store it as is
/>>/ as supplied in (1).
/

Does this mean that, say, '042' or '42.00' are stored as text?


no


Do you think that would be desirable?


no, of course

Base idea is store as TEXT when :
1. column value is supplied as TEXT (only in case sqlite3_bind_text)
2. conversion to REAL or INTEGER leads to loose of precision (digits)

I do not know details how to implement it ;-)
May be, 
1. strip out leading and trailing spaces and zeroes 
2. and then analyze string if contains only digits and decimal separator

3. count number of digits and if > than max precision for REAL or INTEGER then 
do not convert, but store as is

-Laco.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

LacaK  wrote:
>> Problem will be solved if SQLite will store such values as text ... so
>> will behave like this:
>> 1. is supplied value in TEXT (sqlite3_bind_text)
>> 2. if yes then try convert this text value into INTEGER or REAL
>> 3. convert back to text and compare with original value
>> 4. if equal then store it as INTEGER or REAL, if not then store it as is
>> as supplied in (1).

Does this mean that, say, '042' or '42.00' are stored as text? Do you think 
that would be desirable?
-- 
Igor Tandetnik

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

Re: [sqlite] Create table if not exists and insert seed data

[Pavel Ivanov]

You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If
it succeeds then you populate table with data (remember to do that in
the same transaction where you created the table). If CREATE TABLE
fails then you don't insert your data.


Pavel

On Mon, Mar 21, 2011 at 6:04 PM, Erich93063  wrote:
> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???
>
> THANKS
> ___
> 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] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

If you already have an arbitrary precision number, just encode it to 
text, save it in sqlite and then decode on the way out.


Yes it is possible, but such values (and databases) will not be readable 
by other database connectors

(like for example in PHP etc.)
Problem will be solved if SQLite will store such values as text ... so 
will behave like this:

1. is supplied value in TEXT (sqlite3_bind_text)
2. if yes then try convert this text value into INTEGER or REAL
3. convert back to text and compare with original value
4. if equal then store it as INTEGER or REAL, if not then store it as is 
as supplied in (1).


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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Pavel Ivanov]

> Is there way how to store numeric values, which are out of REAL range ?

SQLite has no way of storing numbers other than REAL or INTEGER. If
you want the exact number to be stored your only option is to store it
as TEXT (and don't work with it as a number on SQL level).


Pavel

On Tue, Mar 22, 2011 at 2:51 AM, LacaK  wrote:
> Hi,
> I have table like this:
> CREATE TABLE tab1 (
>  a INTEGER,
>  c DECIMAL(30,7),
>  ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when storing
> values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
> ___
> 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] storing big numbers into NUMERIC, DECIMAL columns

[TR Shaw]

On Mar 22, 2011, at 9:12 AM, LacaK wrote:

>> You still don't say what you're planning on doing with these number...just 
>> displaying them?
> 
> Yes may be ...
> I am working on modification of database component for accessing SQLite3 
> databases for FreePascal project.
> We map declared column's types to native freepascal internal field types.
> So DECIMAL and NUMERIC is mapped to TFmtBCDFieldType (which is able to hold 
> up to 64 digits)
> We read column value using sqlite3_column_text and then convert string 
> representation into TBCD (which is internal structure for "arbitrary" 
> precision numbers)
> But problem arrives when we want write back values.
> We use sqlite3_bind_text and as I wrote a this point we loose precision 
> (because SQLite3 forces conversion to floating point values).

Laco

If you already have an arbitrary precision number, just encode it to text, save 
it in sqlite and then decode on the way out.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

You still don't say what you're planning on doing with these number...just 
displaying them?


Yes may be ...
I am working on modification of database component for accessing SQLite3 
databases for FreePascal project.
We map declared column's types to native freepascal internal field types.
So DECIMAL and NUMERIC is mapped to TFmtBCDFieldType (which is able to hold up 
to 64 digits)
We read column value using sqlite3_column_text and then convert string representation 
into TBCD (which is internal structure for "arbitrary" precision numbers)
But problem arrives when we want write back values.
We use sqlite3_bind_text and as I wrote a this point we loose precision 
(because SQLite3 forces conversion to floating point values).

TIA
-Laco.

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

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

[Max Vlasov]

On Tue, Mar 22, 2011 at 2:25 PM, Dan Kennedy  wrote:

> On 03/22/2011 04:26 PM, Max Vlasov wrote:
> > Hi,
> >
> > recently I finally started experimenting with virtual tables and there's
> at
> > least one thing I can not understand.
> >
> > As I see xBestIndex/xFilter were developed to allow fast searching if the
> > implementation is able to do this. But there's also sql language that
> allows
> > very exotic queries. Some of them may be recognized by the
> implementation,
> > some not. If the former, one just can rely on sqlite double checking and
> > just do full scan. But there are also cases when it looks like
> recognition
> > is not possible. For example
> >
> > SELECT * FROM vtest where id>  random()
> >
> > in this case xBestIndex just assumes some constant as the expression, so
> the
> > one who implements just can't detect probably unresolved query and thinks
> > that it can search quickly (binary search, for example). The call to
> xFilter
> > just passes first random value and sqlite will never call it again for
> the
> > same enumeration. So xFilter thinks this is the constant value used in
> the
> > query and jumps to the first correct row row never planning to jump back.
> > But this is actually a misleading action since in real world sqlite calls
> > random on every row and the rows bypassed are actually important and can
> be
> > evaluated to true. I mentioned random(), but there may be other cases,
> for
> > example when other fields are part of expressions.
>
> SQLite assumes that the result of each expression in the WHERE
> clause depends only on its inputs. If the input arguments are
> the same, the output should be do. Since random() has no inputs,
> SQLite figures that it must always return the same value.
>
> You can see a similar effect with:
>
>   CREATE TABLE t1(a PRIMARY KEY, b);
>   SELECT * FROM t1 WHERE a > random();  -- random() evaluated once.
>   SELECT * FROM t1 WHERE +a > random(); -- random() evaluated many times
>


Dan, thanks, I double-checked your information and (ironically) I see that
the problem is with "the double check" :)

As I see now, sqlite does a great job that probably won't require any
additional steps for the problem I posted. So if the expression is not
"simple" in the terms I used, it just won't supply any constraint to
xBestIndex so automatically forcing full-scan. But if the double-check is
on, sqlite seems like actually checks random() for every result row and this
actually can give non-correct result.

Although I can not confirm the assumption with the numbers, but I also
checked this hypothesis with another "dynamic" expression using milliseconds

SELECT * FROM vtest WHERE (id = cast(strftime('%f','now')*1000 as integer))

and for a comparatively large dataset the value passed in xFilter is always
different to one returned if I just use full scan and double-checking (for
example 15719 vs 18984).

So it seems like virtual tables double checker always evaluates the
expression used for every row. One can live with that just by disabling
double-checking or not using such dynamics at all. I'm not sure whether such
a minor thing should be fixed in the core.

Thanks

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

LacaK  wrote:
>> So once if I define column as DECIMAL,NUMERIC then there is no chance store 
>> in such column numeric values out of range of 64bit
>> integers or 64bit floating point values, right ?

Well, no chance to store them losslessly, preserving the precision.

Where does this precision come from in the first place? How do you represent 
these numbers in your program? Where do they come from? It's highly unlikely 
that you can measure any real-world signal this accurately.
-- 
Igor Tandetnik

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Black, Michael (IS)]

Hmmm...the docs do say that...but how do you get that value back out?  
Retreiving it as text doesn't work.
You still don't say what you're planning on doing with these number...just 
displaying them?

I think the docs may be misleading...here is the comment in sqlite3.c
/*
** Try to convert a value into a numeric representation if we can
** do so without loss of information.  In other words, if the string
** looks like a number, convert it into a number.  If it does not
** look like a number, leave it alone.
*/

The "loss of information" simply means it still looks like a number...not that 
we lost any significant digits.  I think the docs should be clearer about that.
So storing them as text appears to be your only option.

The following just prints out the same truncated real number even though it's 
retrieved as text (and all by design).
You'll also find that a dump shows the same thing.
1.23456789123457e+17

#include 
#include 
#include 
#include 
#include 
#include 
#include "sqlite3.h"
int
main (int argc, char *argv[])
{
  sqlite3 *db = NULL;
  int ret = -1;
  char *sql0 = "drop table tab1";
  char *sql1 = "create table tab1(a integer,c real)";
  char *sql2 = "insert into tab1 values(1,'123456789123456789.123456')";
  char *sql3 = "select * from tab1";
  char *errmsg;
  sqlite3_stmt *p_stmt;
  ret = sqlite3_open ("prec.db", );
  if (ret != SQLITE_OK) {
fprintf (stderr, "open error\n");
exit (-1);
  }
  sqlite3_exec (db, sql0, NULL, NULL, );
  if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
  }
  sqlite3_exec (db, sql1, NULL, NULL, );
  if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
  }
  sqlite3_exec (db, sql2, NULL, NULL, );
  if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
  }
  ret = sqlite3_prepare_v2 (db, sql3, -1, _stmt, NULL);
  if (ret != SQLITE_OK) {
fprintf (stderr, "prepare error: %s\n", sqlite3_errmsg (db));
  }
  ret = sqlite3_step (p_stmt);
  if (ret == SQLITE_ROW) {
const unsigned char *myval = sqlite3_column_text (p_stmt, 1);
printf ("%s\n", myval);
  }
  sqlite3_finalize (p_stmt);
  sqlite3_close (db);
  return 0;
}


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of LacaK [la...@users.sourceforge.net]
Sent: Tuesday, March 22, 2011 6:25 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly"
converted to floating-point representation (REAL storage class), then
store it as text with TEXT storage class ... but this does not happen
(SQLite converts to floating-point and stores it and looses digits).

But on this page http://www.sqlite.org/datatype3.html is written:
"When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if
such conversion is lossless and reversible. For conversions between TEXT
and REAL storage classes, SQLite considers the conversion to be lossless
and reversible if the first 15 significant decimal digits of the number
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
not possible then the value is stored using the TEXT storage class*."

Laco.

> Hi,
> I have table like this:
> CREATE TABLE tab1 (
> a INTEGER,
> c DECIMAL(30,7),
> ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when
> storing values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

/ So only solution is use TEXT columns (with TEXT affinity) ? There is no

/>>/ way how to use DECIMAL columns (with NUMERIC affinity) ?
/>>/ My goal is store numeric values with big precision (as declared per
/>>/ column DECIMAL(30,7)).
/

SQLite happily ignores those numbers in parentheses. There is no arbitrary 
precision floating point data type in SQLite. You get to choose between text, 
64-bit integers, 64-bit IEEE doubles, and blobs.



/ I do not want any conversion to floating-point values ... because such

/>>/ conversion loses digits and is not reversible to original value.
/>>/ What I will expect is: If supplied value can not be "reversibly"
/>>/ converted to floating-point representation (REAL storage class), then
/>>/ store it as text with TEXT storage class
/

Use affinity of NONE (don't specify any type), and figure out in your program 
for each value whether to store as a floating point number or as text. Use 
sqlite3_bind_double or sqlite3_bind_text accordingly.



/ But on this page http://www.sqlite.org/datatype3.html is written:

/>>/ "When text data is inserted into a NUMERIC column, the storage class of
/>>/ the text is converted to INTEGER or REAL (in order of preference) if
/>>/ such conversion is lossless and reversible. For conversions between TEXT
/>>/ and REAL storage classes, SQLite considers the conversion to be lossless
/>>/ and reversible if the first 15 significant decimal digits of the number
/>>/ are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
/>>/ not possible then the value is stored using the TEXT storage class*."
/

In the examples you've shown, the first 15 significant digits are indeed 
preserved. SQLite appears to behave as documented. What again seems to be the 
problem?


Yes you are right , it seemes so.
So once if I define column as DECIMAL,NUMERIC then there is no chance store in 
such column numeric values out of range of 64bit integers or 64bit floating 
point values, right ?
My guess, hope was, that if I use sqlite3_bind_text with for example 
'123456789123456789.12345' then sqlite3 stores such value as string and do not 
convert them to floating point.
(equal as when I insert non numeric value for example 'abcd' then 'abcd' is 
stored)
Thanks for your assistance
Laco.



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

[sqlite] I made a sqlite widget

[Matt Young]

A Web Sql widget really, here is a screen shot link
http://bettereconomics.blogspot.com/2011/03/xml-commander.html

It is all XML htp get, web sql and xml in javascript, working on opera
browsers. The idea is to get the publisher simple access to the combination
of SQL in his document and ad hoc XML behind the scenes. Fits in a browser,
just a few hundred lines of code.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

LacaK  wrote:
>> So only solution is use TEXT columns (with TEXT affinity) ? There is no
>> way how to use DECIMAL columns (with NUMERIC affinity) ?
>> My goal is store numeric values with big precision (as declared per
>> column DECIMAL(30,7)).

SQLite happily ignores those numbers in parentheses. There is no arbitrary 
precision floating point data type in SQLite. You get to choose between text, 
64-bit integers, 64-bit IEEE doubles, and blobs.

>> I do not want any conversion to floating-point values ... because such
>> conversion loses digits and is not reversible to original value.
>> What I will expect is: If supplied value can not be "reversibly"
>> converted to floating-point representation (REAL storage class), then
>> store it as text with TEXT storage class

Use affinity of NONE (don't specify any type), and figure out in your program 
for each value whether to store as a floating point number or as text. Use 
sqlite3_bind_double or sqlite3_bind_text accordingly.

>> But on this page http://www.sqlite.org/datatype3.html is written:
>> "When text data is inserted into a NUMERIC column, the storage class of
>> the text is converted to INTEGER or REAL (in order of preference) if
>> such conversion is lossless and reversible. For conversions between TEXT
>> and REAL storage classes, SQLite considers the conversion to be lossless
>> and reversible if the first 15 significant decimal digits of the number
>> are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is
>> not possible then the value is stored using the TEXT storage class*."

In the examples you've shown, the first 15 significant digits are indeed 
preserved. SQLite appears to behave as documented. What again seems to be the 
problem?
-- 
Igor Tandetnik

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

Re: [sqlite] How to get Unicode Value of any Character in string/text

[Igor Tandetnik]

ashish yadav  wrote:
> I want to know Unicode vale of Character given in string ( Like chines ,
> Japanese etc) .
> 
> If there is any API which can help in this ?

Retrive the string as UTF-16 with sqite3_column_text16 (SQLite automatically 
converts between UTF-8 and UTF-16 as necessary). You'll get the string as a 
sequence of 16-bit unsigned integers, each representing a single Unicode 
codepoint, or else one half of a surrogate pair.
-- 
Igor Tandetnik

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi Michael,
thank you for response.
So only solution is use TEXT columns (with TEXT affinity) ? There is no 
way how to use DECIMAL columns (with NUMERIC affinity) ?
My goal is store numeric values with big precision (as declared per 
column DECIMAL(30,7)).
I do not want any conversion to floating-point values ... because such 
conversion loses digits and is not reversible to original value.
What I will expect is: If supplied value can not be "reversibly" 
converted to floating-point representation (REAL storage class), then 
store it as text with TEXT storage class ... but this does not happen 
(SQLite converts to floating-point and stores it and looses digits).


But on this page http://www.sqlite.org/datatype3.html is written:
"When text data is inserted into a NUMERIC column, the storage class of 
the text is converted to INTEGER or REAL (in order of preference) if 
such conversion is lossless and reversible. For conversions between TEXT 
and REAL storage classes, SQLite considers the conversion to be lossless 
and reversible if the first 15 significant decimal digits of the number 
are preserved. *If the lossless conversion of TEXT to INTEGER or REAL is 
not possible then the value is stored using the TEXT storage class*."


Laco.


Hi,
I have table like this:
CREATE TABLE tab1 (
a INTEGER,
c DECIMAL(30,7),
...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.



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

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

[Dan Kennedy]

On 03/22/2011 04:26 PM, Max Vlasov wrote:
> Hi,
>
> recently I finally started experimenting with virtual tables and there's at
> least one thing I can not understand.
>
> As I see xBestIndex/xFilter were developed to allow fast searching if the
> implementation is able to do this. But there's also sql language that allows
> very exotic queries. Some of them may be recognized by the implementation,
> some not. If the former, one just can rely on sqlite double checking and
> just do full scan. But there are also cases when it looks like recognition
> is not possible. For example
>
> SELECT * FROM vtest where id>  random()
>
> in this case xBestIndex just assumes some constant as the expression, so the
> one who implements just can't detect probably unresolved query and thinks
> that it can search quickly (binary search, for example). The call to xFilter
> just passes first random value and sqlite will never call it again for the
> same enumeration. So xFilter thinks this is the constant value used in the
> query and jumps to the first correct row row never planning to jump back.
> But this is actually a misleading action since in real world sqlite calls
> random on every row and the rows bypassed are actually important and can be
> evaluated to true. I mentioned random(), but there may be other cases, for
> example when other fields are part of expressions.

SQLite assumes that the result of each expression in the WHERE
clause depends only on its inputs. If the input arguments are
the same, the output should be do. Since random() has no inputs,
SQLite figures that it must always return the same value.

You can see a similar effect with:

   CREATE TABLE t1(a PRIMARY KEY, b);
   SELECT * FROM t1 WHERE a > random();  -- random() evaluated once.
   SELECT * FROM t1 WHERE +a > random(); -- random() evaluated many times
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database with persistent storage

[Bart Smissaert]

> through a version of dijkstra's routing algorithm

Just out of interest, what data is this working on?

RBS

On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri
 wrote:
> [Not at all expert in sqlite but here's a practical example of speed up
> using ":memory:" and perhaps a slightly different strategy for getting at
> the persistent data.]
>
> I use sqlite3 with Qt4 / C++ for an application which reads in an undirected
> graph and then chunks through a version of dijkstra's routing algorithm.  A
> colleague runs this on his machine and it takes all night on a large network
> running on a database on disk.  On my own machine which is more powerful it
> probably runs a lot faster but still takes a couple of hours plus.  Changing
> to an in memory database, reading data in and processing in memory brings
> the run time down to a couple of minutes.  So yes - running in memory can be
> much quicker.  At the end of the run I attach an on disk database and copy
> out the tables I need to save using "create table select" .
>
> A
>
> On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:
>
>> I know how to create a database that exists only in memory by using
>> the :memory: filename. This however, creates a new database every time
>> and it can not be saved.
>>
>> Is is possible to make SQLite load a database file into memory and
>> then save it back to the file again when the connection to the
>> database is closed?
>>
>> Would it improve speed?
>>
>> - paldepind
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Black, Michael (IS)]

I hope you know what you're doing with trying to preserve that much 
significance.  Ths first time you stick it in a double or long double variable 
you'll lose it.  You can use the HPAlib to get 32 digits 
http://www.nongnu.org/hpalib/


// Example showing digit loss -- doesn't matter double or long double -- at 
least under GCC 4.1.2
#include 
int main()
{
double d1=123456789123456789.123456;
long double d2=123456789123456789.123456;
printf("%f\n%Lf\n",d1,d2);
return 0;
}
123456789123456784.00
123456789123456784.00

But since you don't seem to understand the limits of floating point values I'm 
worried you're heading down a failing path.

But if what you want to do will really work just make the field text and then 
do whatever you're doing that preserves the significant digits.  It's a common 
misconception that significant digits is to the right of the decimal point but 
that's not true.  It means ALL the digits.

sqlite> CREATE TABLE tab1 (
   ...>   a INTEGER,
   ...>   c DECIMAL(30,7),
   ...> d TEXT);
insert into tab1 
values(1,123456789123456789.123456,'123456789123456789.123456');
1|123456789123456784|123456789123456789.123456


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of LacaK [la...@zoznam.sk]
Sent: Tuesday, March 22, 2011 1:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi,
I have table like this:
CREATE TABLE tab1 (
  a INTEGER,
  c DECIMAL(30,7),
  ...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
significant digits are not preserved)
Is there way how to store numeric values, which are out of REAL range ?

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

Re: [sqlite] Bulk loading large dataset; looking for speedups.

[Black, Michael (IS)]

But I thought he said he dropped the indexes (meaning they aren't there during 
inserts).
That should make sorting irrelevant.

3 Things.

#1 Test with :memory: database and see what the speed is.  That tells you if 
it's SQLite or disk I/O as the bottleneck.
#2 Try WAL mode "pragma journal_mode=WAL" -- sometimes helps a lot.
#3 Create your indexes after you're done with all the inserts.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, March 21, 2011 9:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bulk loading large dataset; looking for speedups.

On 22 Mar 2011, at 1:27am, Douglas Eck wrote:

> I fixed the slowness by sorting the sql statements in ascending key
> order and dumping to a new text file, then running sqlite on the new
> text file.  That *really* helped :-)

Neat.  Must remember that sorting improves speed that much.

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] Create table if not exists and insert seed data

[Max Vlasov]

On Tue, Mar 22, 2011 at 1:04 AM, Erich93063  wrote:

> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???
>
>

Maybe something like

CREATE TABLE IF NOT EXISTS [newTable] AS SELECT * FROM DataToPopulate

DataToPopulate can be a table from the db or temporary table created for
example when the program starts.

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

Re: [sqlite] Create table if not exists and insert seed data

[Philip Graham Willoughby]

On 21 Mar 2011, at 22:04, Erich93063 wrote:

> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???

Yes, I had this problem - if sqlite3_open_v2 had an equivalent to O_EXCL it 
would make this a lot easier: you would only try to run your 
schema/prepopulating SQL if the exclusive open worked. If it failed you would 
retry a non-exclusive open and then assume the database was initialised. If 
such a flag is added it would be helpful for it to implicitly get an exclusive 
lock on the database it creates so that no-one else can try any queries before 
the schema is there.

To answer your question: you can use a normal CREATE TABLE (without IF NOT 
EXISTS) and then only populate it if that worked - you will get an error 
because the table exists otherwise.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] How to get Unicode Value of any Character in string/text

[ashish yadav]

Hi Philip,

Can you please provide some example for Same ie
How to use iconv() to get Unicode of Character ?

Actually , i want to classify Japanese characters as either kanji or
Katakana or Hiragana .
so for that , i want to know Unicode of Character in string/text .
Base on Unicode value , i can say  if Character is  kanji or Katakana or
Hiragana ...

Thanks & Regards
 Ashish

On Tue, Mar 22, 2011 at 2:05 PM, Philip Graham Willoughby <
phil.willoug...@strawberrycat.com> wrote:

> On 22 Mar 2011, at 07:25, ashish yadav wrote:
>
> > Hi ,
> >
> > To be more specific that :
> > 1. Database is UTF-8.
> >  2. Programming Language is C or C++.
> >
> > Database may contain Chines / Japanese character of strings.
> >
> > So , if there is any way /APIs to know Unicode of Character  ?
>
> iconv()
>
> Best Regards,
>
> Phil Willoughby
> --
> Managing Director, StrawberryCat Limited
>
> StrawberryCat Limited is registered in England and Wales with Company No.
> 7234809.
>
> The registered office address of StrawberryCat Limited is:
>
> 107 Morgan Le Fay Drive
> Eastleigh
> SO53 4JH
>
> ___
> 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] In-memory database with persistent storage

[Amit Chaudhuri]

[Not at all expert in sqlite but here's a practical example of speed up
using ":memory:" and perhaps a slightly different strategy for getting at
the persistent data.]

I use sqlite3 with Qt4 / C++ for an application which reads in an undirected
graph and then chunks through a version of dijkstra's routing algorithm.  A
colleague runs this on his machine and it takes all night on a large network
running on a database on disk.  On my own machine which is more powerful it
probably runs a lot faster but still takes a couple of hours plus.  Changing
to an in memory database, reading data in and processing in memory brings
the run time down to a couple of minutes.  So yes - running in memory can be
much quicker.  At the end of the run I attach an on disk database and copy
out the tables I need to save using "create table select" .

A

On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:

> I know how to create a database that exists only in memory by using
> the :memory: filename. This however, creates a new database every time
> and it can not be saved.
>
> Is is possible to make SQLite load a database file into memory and
> then save it back to the file again when the connection to the
> database is closed?
>
> Would it improve speed?
>
> - paldepind
> ___
> 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

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi,
I have table like this:
CREATE TABLE tab1 (
 a INTEGER,
 c DECIMAL(30,7),
 ...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

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

Re: [sqlite] Newbie question

[K Peters]

Thanks, Igor & Michael - your help is much appreciated.

Cheers
Kai

On Mon, Mar 21, 2011 at 4:47 AM, Igor Tandetnik  wrote:
> Kai Peters  wrote:
>> given a table with two columns (SaleDate, SaleVolume) is it possible in one 
>> query to obtain
>> the following three column result set:
>>
>> SalesCurrentYear, SalesLastYEar, SalesAllyears
>
> select
>    sum(SaleVolume * (SaleDate >= StartOfCurYear)) SalesCurrentYear,
>    sum(SaleVolume * (StartOfLastYear <= SaleDate and SaleDate < 
> StartOfCurYear)) SalesLastYear,
>    sum(SaleVolume) SalesAllYears
> from Sales,
>    (select
>        date('now', 'start of year') StartOfCurYear,
>        date('now', 'start of year', '-1 years') StartOfLastYear);
>
> --
> Igor Tandetnik
>
> ___
> 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

[sqlite] Create table if not exists and insert seed data

[Erich93063]

I am trying to create a SQLite database if it doesn't exist, which I
know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
need to initially populate the database with seed data if it doesn't
exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
the table if it doesn't exist, but if I follow that up with insert
statements, those would ALWAYS get ran. I only want to enter the seed
data if the database does not exist. ???

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

Re: [sqlite] Determining deepest descendents of parent records from one table

[JPB]

Hi Enrico,

Thanks a lot for the help, it was very useful. I have a bit more
testing to do to make sure that I'm always getting the correct
records, but your methods seem to be working (and performing much
faster) so far!

The main "trick" that I didn't think of was selecting records from the
same table in a nested fashion (and of course having a separate table
with all of the relationships stored there).

Thanks again,
Jason

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

[sqlite] Best approach for xBestIndex/xFilter effectiveness

[Max Vlasov]

Hi,

recently I finally started experimenting with virtual tables and there's at
least one thing I can not understand.

As I see xBestIndex/xFilter were developed to allow fast searching if the
implementation is able to do this. But there's also sql language that allows
very exotic queries. Some of them may be recognized by the implementation,
some not. If the former, one just can rely on sqlite double checking and
just do full scan. But there are also cases when it looks like recognition
is not possible. For example

SELECT * FROM vtest where id > random()

in this case xBestIndex just assumes some constant as the expression, so the
one who implements just can't detect probably unresolved query and thinks
that it can search quickly (binary search, for example). The call to xFilter
just passes first random value and sqlite will never call it again for the
same enumeration. So xFilter thinks this is the constant value used in the
query and jumps to the first correct row row never planning to jump back.
But this is actually a misleading action since in real world sqlite calls
random on every row and the rows bypassed are actually important and can be
evaluated to true. I mentioned random(), but there may be other cases, for
example when other fields are part of expressions.

So, the main question: is it possible to detect simple expressions that can
be correctly resolved by quick searching? I know that I can always rely on
sqlite double-checking and always do full scan. But theoretically for large
datasets one should at least think about some optimization.

Thanks,

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

Re: [sqlite] How to get Unicode Value of any Character in string/text

[Philip Graham Willoughby]

On 22 Mar 2011, at 07:25, ashish yadav wrote:

> Hi ,
> 
> To be more specific that :
> 1. Database is UTF-8.
>  2. Programming Language is C or C++.
> 
> Database may contain Chines / Japanese character of strings.
> 
> So , if there is any way /APIs to know Unicode of Character  ?

iconv()

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] How to get Unicode Value of any Character in string/text

[ashish yadav]

Hi ,

To be more specific that :
 1. Database is UTF-8.
  2. Programming Language is C or C++.

Database may contain Chines / Japanese character of strings.

So , if there is any way /APIs to know Unicode of Character  ?

Thanks & Regards
 Ashish


On Tue, Mar 22, 2011 at 12:45 PM, ashish yadav wrote:

> Hi ,
>
> I want to know Unicode vale of Character given in string ( Like chines ,
> Japanese etc) .
>
> If there is any API which can help in this ?
>
> Thanks & Regards
>   Ashish
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to get Unicode Value of any Character in string/text

[ashish yadav]

Hi ,

I want to know Unicode vale of Character given in string ( Like chines ,
Japanese etc) .

If there is any API which can help in this ?

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

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Hi,
I have table like this:
CREATE TABLE tab1 (
a INTEGER,
c DECIMAL(30,7),
...
);

When I am trying insert values like:
INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');

values for c column are always rounded or cast to :
1.23456789012346e+19

If I understand correctly column c has NUMERIC affinity, but when 
storing values, they are stored using REAL storage class.
But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15 
significant digits are not preserved)

Is there way how to store numeric values, which are out of REAL range ?

TIA
-Laco.


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

Re: [sqlite] Multiple clients accessing one DB

[thilo]

On 2/9/2011 7:42 PM, Sam Carleton wrote:
> Currently I have two and sometimes three clients access the SQLite db, all
> on the same machine.
>
> * A C# program that doesn't ever stay connection all that long.
> * An Apache application that stays connected all the time.
> * A Qt application that stays connected when it is running.
>
> I am getting report that when the Qt application is running, the Apache
> application is crashing.  The only connection is the SQLite db.
If your configuration uses a separate user on the apache side and a
different user on your other applications,
check that file permissions actually allow everyone to access the DB.
(check the created journal/wal files for the right permissions and owners)
if you apache cannot open r/w the journal files (esp with WAL) because
the other user created the temp files,
it will fail.

thilo
> It is my understanding that SQLite is designed to allow multiple clients
> from the same computer to access the DB file at one time.  Assuming this to
> be true, what is the ideal flags when opening the file?
>
> The system does far more reading then writing.  I am currently using v3.6, I
> have not upgraded to v3.7 and WAL, would that also work to my advantage?
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Dipl. Ing. Thilo Jeremias
Zur Rabenwiese 14
27239 Twistringen
T: +49 15782492240
T: +49 4243941633

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