[sqlite] Avoiding table scans with complex queries

[Paul Vercellotti]

Hi there,

I've got a very slow query that seems to be oh-so-close to running quickly, and 
it seems to come down to handling of OR operators.  When the query has one OR 
criteria, it uses the correct indexes, but when there are two, it does a table 
scan, which in this case brings the query time from milliseconds up to minutes 
in length when the database is large (~3GB).   I don't understand why it thinks 
it needs a table scan, and would love some suggestions.  This is with SQLite 
3.7.16.2.

It's a hierarchical data layout (as for a filesystem tree), and uses FTS4 
tables.

Here's a simplified schema (sorry I couldn't simplify it further):

CREATE TABLE files( key INTEGER DEFAULT 0 primary key, name TEXT COLLATE 
NOCASE, type INTEGER, parent INTEGER );
CREATE INDEX files_parent_idx_idx ON files (parent);

CREATE TABLE search_hierarchy( parent INTEGER REFERENCES files(key), child 
INTEGER REFERENCES files(key) );
CREATE INDEX search_hierarchy_parent_idx ON search_hierarchy (parent);
CREATE INDEX search_hierarchy_child_idx ON search_hierarchy (child);
CREATE VIRTUAL TABLE filename_fts USING fts4( file_name_fts );
CREATE VIRTUAL TABLE file_comment_metadata USING fts4( fileComment );

And the query in question:

SELECT files.key FROM files, search_hierarchy, filename_fts, 
file_comment_metadata
WHERE search_hierarchy.child = files.parent AND filename_fts.rowid = files.key 
AND file_comment_metadata.rowid = files.key AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND 

( (filename_fts.file_name_fts MATCH '"X*"') OR 
(file_comment_metadata.fileComment MATCH '"X*"') ) LIMIT 0, 1000;


This gets run as 

0|0|1|SCAN TABLE search_hierarchy (~100 rows) <
0|1|0|SEARCH TABLE files USING COVERING INDEX files_parent_idx_idx (parent=?) 
(~5 rows)
0|2|2|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 1: (~0 rows)
0|3|3|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 1: (~0 rows)

It's that first SCAN TABLE search_hierarchy that is killing it (~3 minutes for 
this query).  It seems to be the OR's on the last two lines that seem to throw 
it for a loop.

If I change it to the following, by removing the first OR:
SELECT files.key FROM files, search_hierarchy, filename_fts, 
file_comment_metadata
WHERE search_hierarchy.child = files.parent AND filename_fts.rowid = files.key 
AND file_comment_metadata.rowid = files.key AND
( search_hierarchy.parent = 12  ) AND 

( (filename_fts.file_name_fts MATCH '"X*"') OR 
(file_comment_metadata.fileComment MATCH '"X*"') ) LIMIT 0, 1000;


It runs very fast (75ms) as
0|0|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_parent_idx 
(parent=?) (~10 rows)
0|1|0|SEARCH TABLE files USING COVERING INDEX files_parent_idx_idx (parent=?) 
(~10 rows)
0|2|2|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 1: (~0 rows)
0|3|3|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 1: (~0 rows)

Or if change it to:

SELECT files.key FROM files, search_hierarchy, filename_fts, 
file_comment_metadata
WHERE search_hierarchy.child = files.parent AND filename_fts.rowid = files.key 
AND file_comment_metadata.rowid = files.key AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND 

( (filename_fts.file_name_fts MATCH '"X*"') ) LIMIT 0, 1000;


it runs very quickly (~70ms) as well, explained as 
0|0|2|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
0|1|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|3|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 1: (~0 rows)
0|3|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_child_idx 
(child=?) (~5 rows)

Are there any suggestions to eliminate the table scan in the original query, 
since it seems quite capable of efficiently handling each of the OR expressions 
on their own.  (I could do a UNION of multiple versions of the whole query with 
each OR section separated out, but that's not terribly satisfying.)   


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

[sqlite] Android large file support patch

[Pepijn Van Eeckhoudt]

Hi all,

I've ran into some issues running sqlite on Android when handling files
larger than 4Gb. Support for this counts on glibc style large file support
where off_t and off64_t are identical. In bionic (Android C runtime) this
is not the case. off_t is always 32-bit; for 64-bit offsets off64_t and the
corresponding 64 bit variants of functions must be used (ftruncate64,
pread64, pwrite64, ...).

The attached patch modifies os_unix.c to count less on off_t being a 64-bit
value. Could someone review this and if approved integrate this upstream?

Sincerely,

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

[sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Ali Dirik]

Dear Friends,

I want to develop a application that runs Windows CE or Mobile devices.
I want to use the Sqlite database.
May I find same sample source codes?

Best Regards

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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

Hello Ali,

I've done years ago, it's pretty much standard use. What do you want to do ?


On 31 May 2013 10:19, Ali Dirik  wrote:

> Dear Friends,
>
> I want to develop a application that runs Windows CE or Mobile devices.
> I want to use the Sqlite database.
> May I find same sample source codes?
>
> Best Regards
>
> Ali D
> i
> rik
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Ali Dirik]

Hello Noel Frankinet,

Thank you for your answer.
I using a hand-held terminal (
http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with windows
mobile operating system.
We use this device for counting.
There is already an application that works with SQLCE (Microsoft SQL
Compact Edition)
I want to use SQLite instead of SQL CE.
I found a sample source code on the Internet (
http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
)
I've tried it, but did not succeed source code.
Do you have any idea about this?

Best Regards
Ali Dirik


2013/5/31 Ali Dirik 

> Dear Friends,
>
> I want to develop a application that runs Windows CE or Mobile devices.
> I want to use the Sqlite database.
> May I find same sample source codes?
>
> Best Regards
> Ali D
> i
> rik
>



-- 
İyi Çalışmalar
Ali DİRİK
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[fnoyanisi]

This is the first shot from google
http://sqlite-wince.sourceforge.net/index.html

If you'd like to use sqlitecin your app, I do not think it will be an issue for 
any playform once you have the proper C compiler. You may try to statically 
compiling your application.

On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:

> Hello Noel Frankinet,
> 
> Thank you for your answer.
> I using a hand-held terminal (
> http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with windows
> mobile operating system.
> We use this device for counting.
> There is already an application that works with SQLCE (Microsoft SQL
> Compact Edition)
> I want to use SQLite instead of SQL CE.
> I found a sample source code on the Internet (
> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
> )
> I've tried it, but did not succeed source code.
> Do you have any idea about this?
> 
> Best Regards
> Ali Dirik
> 
> 
> 2013/5/31 Ali Dirik 
> 
>> Dear Friends,
>> 
>> I want to develop a application that runs Windows CE or Mobile devices.
>> I want to use the Sqlite database.
>> May I find same sample source codes?
>> 
>> Best Regards
>> Ali D
>> i
>> rik
> 
> 
> 
> -- 
> İyi Çalışmalar
> Ali DİRİK
> ___
> 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 Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

I've used C++, do you want to use anything else. It's of course easier in
C++ since sqlite is in C. You link statically, nothing to install.


On 31 May 2013 12:21,  wrote:

> This is the first shot from google
> http://sqlite-wince.sourceforge.net/index.html
>
> If you'd like to use sqlitecin your app, I do not think it will be an
> issue for any playform once you have the proper C compiler. You may try to
> statically compiling your application.
>
> On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
>
> > Hello Noel Frankinet,
> >
> > Thank you for your answer.
> > I using a hand-held terminal (
> > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with
> windows
> > mobile operating system.
> > We use this device for counting.
> > There is already an application that works with SQLCE (Microsoft SQL
> > Compact Edition)
> > I want to use SQLite instead of SQL CE.
> > I found a sample source code on the Internet (
> >
> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
> > )
> > I've tried it, but did not succeed source code.
> > Do you have any idea about this?
> >
> > Best Regards
> > Ali Dirik
> >
> >
> > 2013/5/31 Ali Dirik 
> >
> >> Dear Friends,
> >>
> >> I want to develop a application that runs Windows CE or Mobile devices.
> >> I want to use the Sqlite database.
> >> May I find same sample source codes?
> >>
> >> Best Regards
> >> Ali D
> >> i
> >> rik
> >
> >
> >
> > --
> > İyi Çalışmalar
> > Ali DİRİK
> > ___
> > 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
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding table scans with complex queries

[Clemens Ladisch]

Paul Vercellotti wrote:
> I've got a very slow query that seems to be oh-so-close to running
> quickly, and it seems to come down to handling of OR operators.
> When the query has one OR criteria, it uses the correct indexes, but
> when there are two, it does a table scan, which in this case brings
> the query time from milliseconds up to minutes in length when the
> database is large (~3GB).   I don't understand why it thinks it needs
> a table scan

The _estimated_ cost for executing the ORs separately is higher than
that for single table scan.

> 0|0|1|SCAN TABLE search_hierarchy (~100 rows)

You did not run ANALYZE.


> Are there any suggestions to eliminate the table scan in the original
> query, since it seems quite capable of efficiently handling each of
> the OR expressions on their own.

Try to put the FTS matches into a subquery (this is usually done to
avoid inefficient joins with the FTS tables):

EXPLAIN QUERY PLAN
SELECT files.key FROM files, search_hierarchy
WHERE search_hierarchy.child = files.parent AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND
(files.key IN (SELECT rowid FROM filename_fts WHERE file_name_fts MATCH '"X*"' 
UNION
   SELECT rowid FROM file_comment_metadata WHERE fileComment MATCH 
'"X*"'));

0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
2|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
3|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|1|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_child_idx 
(child=?) (~5 rows)

> (I could do a UNION of multiple versions of the whole query with each
> OR section separated out, but that's not terribly satisfying.)

Replacing this UNION with OR does not appear to help (I guess the other
OR interferes too much):

EXPLAIN QUERY PLAN
SELECT files.key FROM files, search_hierarchy
WHERE search_hierarchy.child = files.parent AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND
(files.key IN (SELECT rowid FROM filename_fts WHERE file_name_fts MATCH '"X*"') 
OR
 files.key IN (SELECT rowid FROM file_comment_metadata WHERE fileComment MATCH 
'"X*"'));

0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 3
3|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|EXECUTE LIST SUBQUERY 4
4|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
0|1|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_child_idx 
(child=?) (~5 rows)


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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Ali Dirik]

Thanks for answers.

I use c# (VS2008)
I am looking for c# sample source code.


2013/5/31 Noel Frankinet 

> I've used C++, do you want to use anything else. It's of course easier in
> C++ since sqlite is in C. You link statically, nothing to install.
>
>
> On 31 May 2013 12:21,  wrote:
>
> > This is the first shot from google
> > http://sqlite-wince.sourceforge.net/index.html
> >
> > If you'd like to use sqlitecin your app, I do not think it will be an
> > issue for any playform once you have the proper C compiler. You may try
> to
> > statically compiling your application.
> >
> > On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
> >
> > > Hello Noel Frankinet,
> > >
> > > Thank you for your answer.
> > > I using a hand-held terminal (
> > > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with
> > windows
> > > mobile operating system.
> > > We use this device for counting.
> > > There is already an application that works with SQLCE (Microsoft SQL
> > > Compact Edition)
> > > I want to use SQLite instead of SQL CE.
> > > I found a sample source code on the Internet (
> > >
> >
> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
> > > )
> > > I've tried it, but did not succeed source code.
> > > Do you have any idea about this?
> > >
> > > Best Regards
> > > Ali Dirik
> > >
> > >
> > > 2013/5/31 Ali Dirik 
> > >
> > >> Dear Friends,
> > >>
> > >> I want to develop a application that runs Windows CE or Mobile
> devices.
> > >> I want to use the Sqlite database.
> > >> May I find same sample source codes?
> > >>
> > >> Best Regards
> > >> Ali D
> > >> i
> > >> rik
> > >
> > >
> > >
> > > --
> > > İyi Çalışmalar
> > > Ali DİRİK
> > > ___
> > > 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
> >
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
İyi Çalışmalar
Ali DİRİK
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[fnoyanisi]

Once more, googling gives some handy stuff

http://code.google.com/p/csharp-sqlite

On 31/05/2013, at 8:46 PM, Ali Dirik  wrote:

> Thanks for answers.
> 
> I use c# (VS2008)
> I am looking for c# sample source code.
> 
> 
> 2013/5/31 Noel Frankinet 
> 
>> I've used C++, do you want to use anything else. It's of course easier in
>> C++ since sqlite is in C. You link statically, nothing to install.
>> 
>> 
>> On 31 May 2013 12:21,  wrote:
>> 
>>> This is the first shot from google
>>> http://sqlite-wince.sourceforge.net/index.html
>>> 
>>> If you'd like to use sqlitecin your app, I do not think it will be an
>>> issue for any playform once you have the proper C compiler. You may try
>> to
>>> statically compiling your application.
>>> 
>>> On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
>>> 
 Hello Noel Frankinet,
 
 Thank you for your answer.
 I using a hand-held terminal (
 http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with
>>> windows
 mobile operating system.
 We use this device for counting.
 There is already an application that works with SQLCE (Microsoft SQL
 Compact Edition)
 I want to use SQLite instead of SQL CE.
 I found a sample source code on the Internet (
>> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
 )
 I've tried it, but did not succeed source code.
 Do you have any idea about this?
 
 Best Regards
 Ali Dirik
 
 
 2013/5/31 Ali Dirik 
 
> Dear Friends,
> 
> I want to develop a application that runs Windows CE or Mobile
>> devices.
> I want to use the Sqlite database.
> May I find same sample source codes?
> 
> Best Regards
> Ali D
> i
> rik
 
 
 
 --
 İyi Çalışmalar
 Ali DİRİK
 ___
 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
>> 
>> 
>> 
>> --
>> Noël Frankinet
>> Strategis sprl
>> 0478/90.92.54
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> -- 
> İyi Çalışmalar
> Ali DİRİK
> ___
> 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] Avoiding table scans with complex queries

[Simon Slavin]

On 31 May 2013, at 8:21am, Paul Vercellotti  wrote:

> If I change it to the following, by removing the first OR:
> SELECT files.key FROM files, search_hierarchy, filename_fts, 
> file_comment_metadata
> WHERE search_hierarchy.child = files.parent AND filename_fts.rowid = 
> files.key AND file_comment_metadata.rowid = files.key AND
> ( search_hierarchy.parent = 12  ) AND 
> 
> ( (filename_fts.file_name_fts MATCH '"X*"') OR 
> (file_comment_metadata.fileComment MATCH '"X*"') ) LIMIT 0, 1000;
> 
> 
> It runs very fast (75ms)

As a temporary fix, perform both of these very fast queries, then use UNION to 
combine the answers.

PS: Also, as Clemens wrote, you should perform ANALYZE on your database once it 
has reasonable data in it.

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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

ah, sorry, I will never understand why everybody wants to add a virtual
machine on an already feeble machine. Then you will have to deal wil a
extra layer of complexity ( you need a assembly that will translate from
managed code to unmanaged). You will probably have to compile it yourself,
since its probably an arm CPU. Good luck !


On 31 May 2013 13:16, Ali Dirik  wrote:

> Thanks for answers.
>
> I use c# (VS2008)
> I am looking for c# sample source code.
>
>
> 2013/5/31 Noel Frankinet 
>
> > I've used C++, do you want to use anything else. It's of course easier in
> > C++ since sqlite is in C. You link statically, nothing to install.
> >
> >
> > On 31 May 2013 12:21,  wrote:
> >
> > > This is the first shot from google
> > > http://sqlite-wince.sourceforge.net/index.html
> > >
> > > If you'd like to use sqlitecin your app, I do not think it will be an
> > > issue for any playform once you have the proper C compiler. You may try
> > to
> > > statically compiling your application.
> > >
> > > On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
> > >
> > > > Hello Noel Frankinet,
> > > >
> > > > Thank you for your answer.
> > > > I using a hand-held terminal (
> > > > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703) with
> > > windows
> > > > mobile operating system.
> > > > We use this device for counting.
> > > > There is already an application that works with SQLCE (Microsoft SQL
> > > > Compact Edition)
> > > > I want to use SQLite instead of SQL CE.
> > > > I found a sample source code on the Internet (
> > > >
> > >
> >
> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
> > > > )
> > > > I've tried it, but did not succeed source code.
> > > > Do you have any idea about this?
> > > >
> > > > Best Regards
> > > > Ali Dirik
> > > >
> > > >
> > > > 2013/5/31 Ali Dirik 
> > > >
> > > >> Dear Friends,
> > > >>
> > > >> I want to develop a application that runs Windows CE or Mobile
> > devices.
> > > >> I want to use the Sqlite database.
> > > >> May I find same sample source codes?
> > > >>
> > > >> Best Regards
> > > >> Ali D
> > > >> i
> > > >> rik
> > > >
> > > >
> > > >
> > > > --
> > > > İyi Çalışmalar
> > > > Ali DİRİK
> > > > ___
> > > > 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
> > >
> >
> >
> >
> > --
> > Noël Frankinet
> > Strategis sprl
> > 0478/90.92.54
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> İyi Çalışmalar
> Ali DİRİK
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

oh, yes everything in c# then, let's see the performance on an arm cpu !


On 31 May 2013 13:23, Noel Frankinet  wrote:

> ah, sorry, I will never understand why everybody wants to add a virtual
> machine on an already feeble machine. Then you will have to deal wil a
> extra layer of complexity ( you need a assembly that will translate from
> managed code to unmanaged). You will probably have to compile it yourself,
> since its probably an arm CPU. Good luck !
>
>
> On 31 May 2013 13:16, Ali Dirik  wrote:
>
>> Thanks for answers.
>>
>> I use c# (VS2008)
>> I am looking for c# sample source code.
>>
>>
>> 2013/5/31 Noel Frankinet 
>>
>> > I've used C++, do you want to use anything else. It's of course easier
>> in
>> > C++ since sqlite is in C. You link statically, nothing to install.
>> >
>> >
>> > On 31 May 2013 12:21,  wrote:
>> >
>> > > This is the first shot from google
>> > > http://sqlite-wince.sourceforge.net/index.html
>> > >
>> > > If you'd like to use sqlitecin your app, I do not think it will be an
>> > > issue for any playform once you have the proper C compiler. You may
>> try
>> > to
>> > > statically compiling your application.
>> > >
>> > > On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
>> > >
>> > > > Hello Noel Frankinet,
>> > > >
>> > > > Thank you for your answer.
>> > > > I using a hand-held terminal (
>> > > > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703)
>> with
>> > > windows
>> > > > mobile operating system.
>> > > > We use this device for counting.
>> > > > There is already an application that works with SQLCE (Microsoft SQL
>> > > > Compact Edition)
>> > > > I want to use SQLite instead of SQL CE.
>> > > > I found a sample source code on the Internet (
>> > > >
>> > >
>> >
>> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
>> > > > )
>> > > > I've tried it, but did not succeed source code.
>> > > > Do you have any idea about this?
>> > > >
>> > > > Best Regards
>> > > > Ali Dirik
>> > > >
>> > > >
>> > > > 2013/5/31 Ali Dirik 
>> > > >
>> > > >> Dear Friends,
>> > > >>
>> > > >> I want to develop a application that runs Windows CE or Mobile
>> > devices.
>> > > >> I want to use the Sqlite database.
>> > > >> May I find same sample source codes?
>> > > >>
>> > > >> Best Regards
>> > > >> Ali D
>> > > >> i
>> > > >> rik
>> > > >
>> > > >
>> > > >
>> > > > --
>> > > > İyi Çalışmalar
>> > > > Ali DİRİK
>> > > > ___
>> > > > 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
>> > >
>> >
>> >
>> >
>> > --
>> > Noël Frankinet
>> > Strategis sprl
>> > 0478/90.92.54
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> İyi Çalışmalar
>> Ali DİRİK
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

and it's already on year behind the official c sqlite. I wouldn't go that
route


On 31 May 2013 13:26, Noel Frankinet  wrote:

> oh, yes everything in c# then, let's see the performance on an arm cpu !
>
>
> On 31 May 2013 13:23, Noel Frankinet  wrote:
>
>> ah, sorry, I will never understand why everybody wants to add a virtual
>> machine on an already feeble machine. Then you will have to deal wil a
>> extra layer of complexity ( you need a assembly that will translate from
>> managed code to unmanaged). You will probably have to compile it yourself,
>> since its probably an arm CPU. Good luck !
>>
>>
>> On 31 May 2013 13:16, Ali Dirik  wrote:
>>
>>> Thanks for answers.
>>>
>>> I use c# (VS2008)
>>> I am looking for c# sample source code.
>>>
>>>
>>> 2013/5/31 Noel Frankinet 
>>>
>>> > I've used C++, do you want to use anything else. It's of course easier
>>> in
>>> > C++ since sqlite is in C. You link statically, nothing to install.
>>> >
>>> >
>>> > On 31 May 2013 12:21,  wrote:
>>> >
>>> > > This is the first shot from google
>>> > > http://sqlite-wince.sourceforge.net/index.html
>>> > >
>>> > > If you'd like to use sqlitecin your app, I do not think it will be an
>>> > > issue for any playform once you have the proper C compiler. You may
>>> try
>>> > to
>>> > > statically compiling your application.
>>> > >
>>> > > On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
>>> > >
>>> > > > Hello Noel Frankinet,
>>> > > >
>>> > > > Thank you for your answer.
>>> > > > I using a hand-held terminal (
>>> > > > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703)
>>> with
>>> > > windows
>>> > > > mobile operating system.
>>> > > > We use this device for counting.
>>> > > > There is already an application that works with SQLCE (Microsoft
>>> SQL
>>> > > > Compact Edition)
>>> > > > I want to use SQLite instead of SQL CE.
>>> > > > I found a sample source code on the Internet (
>>> > > >
>>> > >
>>> >
>>> http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
>>> > > > )
>>> > > > I've tried it, but did not succeed source code.
>>> > > > Do you have any idea about this?
>>> > > >
>>> > > > Best Regards
>>> > > > Ali Dirik
>>> > > >
>>> > > >
>>> > > > 2013/5/31 Ali Dirik 
>>> > > >
>>> > > >> Dear Friends,
>>> > > >>
>>> > > >> I want to develop a application that runs Windows CE or Mobile
>>> > devices.
>>> > > >> I want to use the Sqlite database.
>>> > > >> May I find same sample source codes?
>>> > > >>
>>> > > >> Best Regards
>>> > > >> Ali D
>>> > > >> i
>>> > > >> rik
>>> > > >
>>> > > >
>>> > > >
>>> > > > --
>>> > > > İyi Çalışmalar
>>> > > > Ali DİRİK
>>> > > > ___
>>> > > > 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
>>> > >
>>> >
>>> >
>>> >
>>> > --
>>> > Noël Frankinet
>>> > Strategis sprl
>>> > 0478/90.92.54
>>> > ___
>>> > sqlite-users mailing list
>>> > sqlite-users@sqlite.org
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>>
>>>
>>>
>>> --
>>> İyi Çalışmalar
>>> Ali DİRİK
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Noël Frankinet
>> Strategis sprl
>> 0478/90.92.54
>>
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android large file support patch

[Dan Kennedy]

On 05/31/2013 03:01 PM, Pepijn Van Eeckhoudt wrote:

Hi all,

I've ran into some issues running sqlite on Android when handling files
larger than 4Gb. Support for this counts on glibc style large file support
where off_t and off64_t are identical. In bionic (Android C runtime) this
is not the case. off_t is always 32-bit; for 64-bit offsets off64_t and the
corresponding 64 bit variants of functions must be used (ftruncate64,
pread64, pwrite64, ...).

The attached patch modifies os_unix.c to count less on off_t being a 64-bit
value. Could someone review this and if approved integrate this upstream?


The mailing list strips attachments unfortunately. Can you
upload the patch somewhere?

Thanks,
Dan.

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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

one last consideration, on android and ios (about the same CPU power), they
use sqlite native, nota java translation.
Is you application requiring c# in any way ?


On 31 May 2013 13:28, Noel Frankinet  wrote:

> and it's already on year behind the official c sqlite. I wouldn't go that
> route
>
>
> On 31 May 2013 13:26, Noel Frankinet  wrote:
>
>> oh, yes everything in c# then, let's see the performance on an arm cpu !
>>
>>
>> On 31 May 2013 13:23, Noel Frankinet  wrote:
>>
>>> ah, sorry, I will never understand why everybody wants to add a virtual
>>> machine on an already feeble machine. Then you will have to deal wil a
>>> extra layer of complexity ( you need a assembly that will translate from
>>> managed code to unmanaged). You will probably have to compile it yourself,
>>> since its probably an arm CPU. Good luck !
>>>
>>>
>>> On 31 May 2013 13:16, Ali Dirik  wrote:
>>>
 Thanks for answers.

 I use c# (VS2008)
 I am looking for c# sample source code.


 2013/5/31 Noel Frankinet 

 > I've used C++, do you want to use anything else. It's of course
 easier in
 > C++ since sqlite is in C. You link statically, nothing to install.
 >
 >
 > On 31 May 2013 12:21,  wrote:
 >
 > > This is the first shot from google
 > > http://sqlite-wince.sourceforge.net/index.html
 > >
 > > If you'd like to use sqlitecin your app, I do not think it will be
 an
 > > issue for any playform once you have the proper C compiler. You may
 try
 > to
 > > statically compiling your application.
 > >
 > > On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
 > >
 > > > Hello Noel Frankinet,
 > > >
 > > > Thank you for your answer.
 > > > I using a hand-held terminal (
 > > > http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703)
 with
 > > windows
 > > > mobile operating system.
 > > > We use this device for counting.
 > > > There is already an application that works with SQLCE (Microsoft
 SQL
 > > > Compact Edition)
 > > > I want to use SQLite instead of SQL CE.
 > > > I found a sample source code on the Internet (
 > > >
 > >
 >
 http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
 > > > )
 > > > I've tried it, but did not succeed source code.
 > > > Do you have any idea about this?
 > > >
 > > > Best Regards
 > > > Ali Dirik
 > > >
 > > >
 > > > 2013/5/31 Ali Dirik 
 > > >
 > > >> Dear Friends,
 > > >>
 > > >> I want to develop a application that runs Windows CE or Mobile
 > devices.
 > > >> I want to use the Sqlite database.
 > > >> May I find same sample source codes?
 > > >>
 > > >> Best Regards
 > > >> Ali D
 > > >> i
 > > >> rik
 > > >
 > > >
 > > >
 > > > --
 > > > İyi Çalışmalar
 > > > Ali DİRİK
 > > > ___
 > > > 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
 > >
 >
 >
 >
 > --
 > Noël Frankinet
 > Strategis sprl
 > 0478/90.92.54
 > ___
 > sqlite-users mailing list
 > sqlite-users@sqlite.org
 > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
 >



 --
 İyi Çalışmalar
 Ali DİRİK
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>>
>>>
>>> --
>>> Noël Frankinet
>>> Strategis sprl
>>> 0478/90.92.54
>>>
>>
>>
>>
>> --
>> Noël Frankinet
>> Strategis sprl
>> 0478/90.92.54
>>
>
>
>
> --
> Noël Frankinet
> Strategis sprl
> 0478/90.92.54
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite init file from PHP

[Lior Hazan]

Dear All,

If I'm using Command Line , I can use the next command

sqlite.exe -init FILENAME file.db
This let me init the SQLite with some parameters and it works great

I wonder how can I do that using PHP?

Thanks in advance

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

Re: [sqlite] SQLite init file from PHP

[Simon Slavin]

On 30 May 2013, at 2:11pm, Lior Hazan  wrote:

> sqlite.exe -init FILENAME file.db
> This let me init the SQLite with some parameters and it works great
> 
> I wonder how can I do that using PHP?

Can I ask what sort of thing you want to set with your parameters ?

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

Re: [sqlite] SQLite init file from PHP

[fnoyanisi]

Use

http://php.net/manual/en/function.system.php

Or

http://www.php.net/manual/en/function.exec.php

On 30/05/2013, at 10:41 PM, Lior Hazan  wrote:

> Dear All,
> 
> If I'm using Command Line , I can use the next command
> 
> sqlite.exe -init FILENAME file.db
> This let me init the SQLite with some parameters and it works great
> 
> I wonder how can I do that using PHP?
> 
> Thanks in advance
> 
> Lior
> ___
> 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 Sample Source Codes For Windows CE/Mobile

[Bernd Lehmkuhl]

This is not correct. It's up definitely up to date:

https://system.data.sqlite.org/downloads/1.0.86.0/sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.86.0.zip

Available at:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

"This binary package contains all the binaries for the PocketPC version 
of the System.Data.SQLite 1.0.86.0 (3.7.17) package. The .NET Compact 
Framework 3.5 is required."




Am 31.05.2013 13:28, schrieb Noel Frankinet:

and it's already on year behind the official c sqlite. I wouldn't go that
route


On 31 May 2013 13:26, Noel Frankinet  wrote:


oh, yes everything in c# then, let's see the performance on an arm cpu !


On 31 May 2013 13:23, Noel Frankinet  wrote:


ah, sorry, I will never understand why everybody wants to add a virtual
machine on an already feeble machine. Then you will have to deal wil a
extra layer of complexity ( you need a assembly that will translate from
managed code to unmanaged). You will probably have to compile it yourself,
since its probably an arm CPU. Good luck !


On 31 May 2013 13:16, Ali Dirik  wrote:


Thanks for answers.

I use c# (VS2008)
I am looking for c# sample source code.


2013/5/31 Noel Frankinet 


I've used C++, do you want to use anything else. It's of course easier

in

C++ since sqlite is in C. You link statically, nothing to install.


On 31 May 2013 12:21,  wrote:


This is the first shot from google
http://sqlite-wince.sourceforge.net/index.html

If you'd like to use sqlitecin your app, I do not think it will be an
issue for any playform once you have the proper C compiler. You may

try

to

statically compiling your application.

On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:


Hello Noel Frankinet,

Thank you for your answer.
I using a hand-held terminal (
http://www.ute.com/products_info.php?pc1=1&pc2=3&rbu=0&pid=703)

with

windows

mobile operating system.
We use this device for counting.
There is already an application that works with SQLCE (Microsoft

SQL

Compact Edition)
I want to use SQLite instead of SQL CE.
I found a sample source code on the Internet (






http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application

)
I've tried it, but did not succeed source code.
Do you have any idea about this?

Best Regards
Ali Dirik


2013/5/31 Ali Dirik 


Dear Friends,

I want to develop a application that runs Windows CE or Mobile

devices.

I want to use the Sqlite database.
May I find same sample source codes?

Best Regards
Ali D
i
rik




--
İyi Çalışmalar
Ali DİRİK
___
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





--
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
İyi Çalışmalar
Ali DİRİK
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
Noël Frankinet
Strategis sprl
0478/90.92.54





--
Noël Frankinet
Strategis sprl
0478/90.92.54







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

Re: [sqlite] Sqlite Sample Source Codes For Windows CE/Mobile

[Noel Frankinet]

it's different (not the sqlite engine in c# but just the interface). The
interface is up to date, and maintained with the core engine.
But the binary is for x86 windows.


On 31 May 2013 14:30, Bernd Lehmkuhl  wrote:

> This is not correct. It's up definitely up to date:
>
> https://system.data.sqlite.**org/downloads/1.0.86.0/sqlite-**
> netFx35-binary-PocketPC-ARM-**2008-1.0.86.0.zip
>
> Available at:
> https://system.data.sqlite.**org/index.html/doc/trunk/www/**downloads.wiki
>
> "This binary package contains all the binaries for the PocketPC version of
> the System.Data.SQLite 1.0.86.0 (3.7.17) package. The .NET Compact
> Framework 3.5 is required."
>
>
>
> Am 31.05.2013 13:28, schrieb Noel Frankinet:
>
>  and it's already on year behind the official c sqlite. I wouldn't go that
>> route
>>
>>
>> On 31 May 2013 13:26, Noel Frankinet  wrote:
>>
>>  oh, yes everything in c# then, let's see the performance on an arm cpu !
>>>
>>>
>>> On 31 May 2013 13:23, Noel Frankinet  wrote:
>>>
>>>  ah, sorry, I will never understand why everybody wants to add a virtual
 machine on an already feeble machine. Then you will have to deal wil a
 extra layer of complexity ( you need a assembly that will translate from
 managed code to unmanaged). You will probably have to compile it
 yourself,
 since its probably an arm CPU. Good luck !


 On 31 May 2013 13:16, Ali Dirik  wrote:

  Thanks for answers.
>
> I use c# (VS2008)
> I am looking for c# sample source code.
>
>
> 2013/5/31 Noel Frankinet 
>
>  I've used C++, do you want to use anything else. It's of course easier
>>
> in
>
>> C++ since sqlite is in C. You link statically, nothing to install.
>>
>>
>> On 31 May 2013 12:21,  wrote:
>>
>>  This is the first shot from google
>>> http://sqlite-wince.**sourceforge.net/index.html
>>>
>>> If you'd like to use sqlitecin your app, I do not think it will be an
>>> issue for any playform once you have the proper C compiler. You may
>>>
>> try
>
>> to
>>
>>> statically compiling your application.
>>>
>>> On 31/05/2013, at 7:42 PM, Ali Dirik  wrote:
>>>
>>>  Hello Noel Frankinet,

 Thank you for your answer.
 I using a hand-held terminal (
 http://www.ute.com/products_**info.php?pc1=1&pc2=3&rbu=0&**pid=703
 )

>>> with
>
>> windows
>>>
 mobile operating system.
 We use this device for counting.
 There is already an application that works with SQLCE (Microsoft

>>> SQL
>
>> Compact Edition)
 I want to use SQLite instead of SQL CE.
 I found a sample source code on the Internet (


>>>
>>  http://www.codeproject.com/**Articles/22165/Using-SQLite-**
> in-your-C-Application
>
>> )
 I've tried it, but did not succeed source code.
 Do you have any idea about this?

 Best Regards
 Ali Dirik


 2013/5/31 Ali Dirik 

  Dear Friends,
>
> I want to develop a application that runs Windows CE or Mobile
>
 devices.
>>
>>> I want to use the Sqlite database.
> May I find same sample source codes?
>
> Best Regards
> Ali D
> i
> rik
>



 --
 İyi Çalışmalar
 Ali DİRİK
 __**_
 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
>>>
>>>
>>
>>
>> --
>> Noël Frankinet
>> Strategis sprl
>> 0478/90.92.54
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>>
>
>
> --
> İyi Çalışmalar
> Ali DİRİK
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8

[sqlite] Import skip first line

[Bart Smissaert]

Importing a. csv file via the. import command of sqlite3.exe.
As the first line holds the field names I want to skip that.
There is no problem achieving this in code, but could I do this
purely via sqlite3 commands?

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

[sqlite] Android large file support patch

[Pepijn Van Eeckhoudt]

Sorry for the non-threaded reply. My subscription was set to digest mode...

Patch is available at
https://dl.dropboxusercontent.com/u/105584447/android_largefile.patch

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

Re: [sqlite] .net Sqlite lib reading date fields not working?

[eschneider FE]

I was just using the ;DateTimeKind= DateTimeKind.Utc
But I just tested without any date settings and it works also. 

I have not tried your new method.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Thursday, May 30, 2013 9:45 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?


eschneider FE 
>
> Solved it.
>

Great.  I'm curious, which connection string properties did you end up
using?

>
> You cannot use  parameter DbType.DateTime2 and must use DbType.DateTime
for
> the lib to format the date upon insert.
>

Right, DbType.DateTime2 is not recognized by System.Data.SQLite.

--
Joe Mistachkin

___
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] Concatenating literals with column values

[Dave Wellman]

Hi,

 

Running the following SQL does not seem to give consistent results.

 

If I concatenate a literal with a column containing a numeric value then I
get expected results. (answers B and E below)

 

If however I concatenate the literal with a numeric literal added to the
column, I just get the numeric literal as my result.  (answer D below)

 

These two do not seem consistent with each other.

 

(A) (B) (C)  (D)
(E)

select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
,'STEPID'||5 

from seqnumber;

 

A   B   CD E

5|STEPID5|10|5|STEPID5

 

 

It is the result D that is not what I'm expecting and does not seem
consistent with the other results. I'm expecting a result of STEPID10.

 

The StepID column is defined as INTEGER and contains the value 5.

 

I am using 3.7.16.2.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] Concatenating literals with column values

[Richard Hipp]

On Fri, May 31, 2013 at 11:38 AM, Dave Wellman
wrote:

> Running the following SQL does not seem to give consistent results.
> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5
>
>
Maybe for column D you intended to say:   'STEPID'||(stepid+5)

-- 
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] Concatenating literals with column values

[Dave Wellman]

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: 31 May 2013 16:46
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concatenating literals with column values

On Fri, May 31, 2013 at 11:38 AM, Dave Wellman
wrote:

> Running the following SQL does not seem to give consistent results.
> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5
>
>
Maybe for column D you intended to say:   'STEPID'||(stepid+5)

-- 
D. Richard Hipp
d...@sqlite.org
___
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] Concatenating literals with column values

[Simon Slavin]

On 31 May 2013, at 4:38pm, "Dave Wellman"  wrote:

> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5 
> 
> from seqnumber;
> 
> 
> 
> A   B   CD E
> 
> 5|STEPID5|10|5|STEPID5

Interesting:

SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE myTable (stepint INTEGER, steptext TEXT);
sqlite> INSERT INTO myTable VALUES (5,'5');
sqlite> SELECT * FROM myTable;
sqlite> SELECT 'hello mum'||stepint+5,'hello mum'||steptext+5 FROM myTable;
5|5
sqlite> SELECT ('hello mum'||stepint)+5,('hello mum'||steptext)+5 FROM myTable;
5|5
sqlite> SELECT 'hello mum'||(stepint+5),'hello mum'||(steptext+5) FROM myTable;
hello mum10|hello mum10
sqlite> SELECT 'hello mum'+stepint,'hello mum'+steptext FROM myTable;
5|5
sqlite> SELECT 'hello mum'+'hello mum' FROM myTable;
0

Okay, so the '+' operator, when either of the operators is text, considers the 
text to evaluate to 0.  Not sure what I expected.

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

Re: [sqlite] Concatenating literals with column values

[Michael Black]

Your statement doesn't even work in MySQLas || is a logical operator there.
And Oracle complains about your original query:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||stepid+5,'STEPID'||5 from seqtable; 
select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5,'STEPID'||5
from seqtable
   *
ERROR at line 1:
ORA-01722: invalid number

And Oracle works with parentheses just like SQLite does:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||(stepid+5),'STEPID'||5 from seqtable; 

STEPID 'STEPID'||STEPID STEPID+5
-- -- --
'STEPID'||(STEPID+5)   'STEPID
-- ---
 5 STEPID510
STEPID10   STEPID5


So I'm not sure what "other" databases you're talking about.

What you're seeing is operator precedence.  || has the highest precedence so
you need the parentheses to override that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman
Sent: Friday, May 31, 2013 11:08 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave



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

Re: [sqlite] Concatenating literals with column values

[Clemens Ladisch]

Dave Wellman wrote:
> Why do I need the "()" around my calculation?

 says:
| When casting a TEXT value to INTEGER, the longest possible prefix of
| the value that can be interpreted as an integer number is extracted
| from the TEXT value and the remainder ignored. Any leading spaces in
| the TEXT value when converting from TEXT to INTEGER are ignored. If
| there is no prefix that can be interpreted as an integer number, the
| result of the conversion is 0.

So the result of 'forty-two'+0 is 0.


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

[sqlite] numeric string quotation

[Spora]

I have table:

CREATE TABLE test (
  alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in Mysql, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?


--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
Ami l'arte e vuoi arredare casa con stile? Su MisterCupido.com puoi acquistare 
le RIPRODUZIONI DEI QUADRI di: Van Gogh, Monet, Klimt, Modigliani, Cezanne, 
Hayez, Michelangelo, Raffaello, ecc
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12386&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation

[Richard Hipp]

On Fri, May 31, 2013 at 12:52 PM, Spora  wrote:

> I have table:
>
> CREATE TABLE test (
>   alfa char(5) NOT NULL default '',
> );
>
> when i .dump i obtain:
>
> INSERT INTO test VALUES(01000);
>

I get

INSERT INTO text VALUES('01000');

I tried this with older versions of SQLite too, going back to 3.3.5 circa
2006.  So I don't know how you are getting the results you are reporting.
-- 
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] numeric string quotation

[Larry Brasfield]

I have table:

CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in Mysql, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?


Why are you asking about an issue with Mysql in a SQLite list?

Assuming this (almost) happened with SQLite, (and assuming there was not 
really a trailing comma on the last column spec in the create 
statement), the literal in your insert statement becomes the same 
integer during parsing whether there is a leading '0' or not.  It is at 
execution time that the column type affinity causes that (same) integer 
to become a character sequence.


While it is unclear what problem you intend to solve, if you wish to 
insert the string literal '01000', write it as a string literal in your 
insert statement.


--
Larry Brasfield

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

Re: [sqlite] Concatenating literals with column values

[Dave Wellman]

Resend from correct account.


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: David Wellman [mailto:david.well...@ward-analytics.com] 
Sent: 31 May 2013 18:05
To: 'General Discussion of SQLite Database'
Subject: RE: [sqlite] Concatenating literals with column values

Hi Michael,

Ok, you caught me out. I actually only tried it in one (Teradata) which is
the dbms that I've worked with most over the last few years. That is
certainly not completely 'standard' in terms of what syntax it supports-
which is probably true of most dbms's - but I didn't think this area would
be different.

Anyhow, I now have sql that gives me the answer that I'm expecting.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR Registered company number: 3917021 Registered in
England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black
Sent: 31 May 2013 17:37
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Your statement doesn't even work in MySQLas || is a logical operator there.
And Oracle complains about your original query:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||stepid+5,'STEPID'||5 from seqtable; select stepid
,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5,'STEPID'||5 from seqtable
   * ERROR at line 1:
ORA-01722: invalid number

And Oracle works with parentheses just like SQLite does:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||(stepid+5),'STEPID'||5 from seqtable; 

STEPID 'STEPID'||STEPID STEPID+5
-- -- --
'STEPID'||(STEPID+5)   'STEPID
-- ---
 5 STEPID510
STEPID10   STEPID5


So I'm not sure what "other" databases you're talking about.

What you're seeing is operator precedence.  || has the highest precedence so
you need the parentheses to override that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman
Sent: Friday, May 31, 2013 11:08 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave



___
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] Concatenating literals with column values

[Dave Wellman]

Hi Clemens,

Thanks for the pointer although I'm doing this the other way around. I'm
casting an INTEGER to a TEXT value.

I think Michael is probably on the right track here, this is to do with
operator precedence.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: 31 May 2013 17:43
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concatenating literals with column values

Dave Wellman wrote:
> Why do I need the "()" around my calculation?

 says:
| When casting a TEXT value to INTEGER, the longest possible prefix of 
| the value that can be interpreted as an integer number is extracted 
| from the TEXT value and the remainder ignored. Any leading spaces in 
| the TEXT value when converting from TEXT to INTEGER are ignored. If 
| there is no prefix that can be interpreted as an integer number, the 
| result of the conversion is 0.

So the result of 'forty-two'+0 is 0.


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] Avoiding table scans with complex queries

[Paul Vercellotti]

Clemens Ladisch wrote:

>Try to put the FTS matches into a subquery (this is usually done to
>avoid inefficient joins with the FTS tables):
>
>EXPLAIN QUERY PLAN
>SELECT files.key FROM files, search_hierarchy
>WHERE search_hierarchy.child = files.parent AND
>( search_hierarchy.parent = 12 OR files.parent = 12 ) AND
>(files.key IN (SELECT rowid FROM filename_fts WHERE file_name_fts MATCH '"X*"' 
>UNION
>             SELECT rowid FROM file_comment_metadata WHERE fileComment MATCH 
> '"X*"'));


Thanks for the suggestions - Unfortunately this form (using a subquery for the 
FTS) also takes minutes, proportional to the number of results returned by FTS 
query.   In the example case, there are about 100 records returned by the 
FTS result.   The form that uses joins is nice in that it seems to be able to 
stop working on the FTS lookups once the LIMIT conditions are met.

Seems UNIONing repetitions of whole query for each OR section is offering the 
best option so far.  I'll keep trying things. 

Cheers,
Paul



 From: Clemens Ladisch 
To: sqlite-users@sqlite.org 
Sent: Friday, May 31, 2013 4:11 AM
Subject: Re: [sqlite] Avoiding table scans with complex queries
 

Paul Vercellotti wrote:
> I've got a very slow query that seems to be oh-so-close to running
> quickly, and it seems to come down to handling of OR operators.
> When the query has one OR criteria, it uses the correct indexes, but
> when there are two, it does a table scan, which in this case brings
> the query time from milliseconds up to minutes in length when the
> database is large (~3GB).   I don't understand why it thinks it needs
> a table scan

The _estimated_ cost for executing the ORs separately is higher than
that for single table scan.

> 0|0|1|SCAN TABLE search_hierarchy (~100 rows)

You did not run ANALYZE.


> Are there any suggestions to eliminate the table scan in the original
> query, since it seems quite capable of efficiently handling each of
> the OR expressions on their own.

Try to put the FTS matches into a subquery (this is usually done to
avoid inefficient joins with the FTS tables):

EXPLAIN QUERY PLAN
SELECT files.key FROM files, search_hierarchy
WHERE search_hierarchy.child = files.parent AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND
(files.key IN (SELECT rowid FROM filename_fts WHERE file_name_fts MATCH '"X*"' 
UNION
               SELECT rowid FROM file_comment_metadata WHERE fileComment MATCH 
'"X*"'));

0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
2|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
3|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|1|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_child_idx 
(child=?) (~5 rows)

> (I could do a UNION of multiple versions of the whole query with each
> OR section separated out, but that's not terribly satisfying.)

Replacing this UNION with OR does not appear to help (I guess the other
OR interferes too much):

EXPLAIN QUERY PLAN
SELECT files.key FROM files, search_hierarchy
WHERE search_hierarchy.child = files.parent AND
( search_hierarchy.parent = 12 OR files.parent = 12 ) AND
(files.key IN (SELECT rowid FROM filename_fts WHERE file_name_fts MATCH '"X*"') 
OR
files.key IN (SELECT rowid FROM file_comment_metadata WHERE fileComment MATCH 
'"X*"'));

0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 3
3|0|0|SCAN TABLE filename_fts VIRTUAL TABLE INDEX 2: (~0 rows)
0|0|0|EXECUTE LIST SUBQUERY 4
4|0|0|SCAN TABLE file_comment_metadata VIRTUAL TABLE INDEX 2: (~0 rows)
0|1|1|SEARCH TABLE search_hierarchy USING INDEX search_hierarchy_child_idx 
(child=?) (~5 rows)


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] Import skip first line

[Larry Brasfield]

Bart Smissaert wrote:

Importing a. csv file via the. import command of sqlite3.exe.
As the first line holds the field names I want to skip that.
There is no problem achieving this in code, but could I do this
purely via sqlite3 commands?


You could do it easily with a temporary table having an and additional 
autoincrement column, but the verbage to then select that table into the 
one where you want no header line would be code, IMO.


I get this functionality with a modified version of the SQLite shell. 
When headers are on, it also expects to see them upon .import and does 
the right thing, which I take to be interpreting them as column names.


--
Larry Brasfield

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

Re: [sqlite] numeric string quotation

[Michael Black]

I think you showed us the insert you do and not the .dump result.
You need to ensure your inserted values are single-quoted.

sqlite> create table test(alfa char(5) not null default '');
sqlite> insert into test values(01000);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seqnumber(stepid);
INSERT INTO "seqnumber" VALUES(5);
CREATE TABLE test(alfa char(5) not null default '');
INSERT INTO "test" VALUES('1000');
COMMIT;
sqlite> insert into test values('01000');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seqnumber(stepid);
INSERT INTO "seqnumber" VALUES(5);
CREATE TABLE test(alfa char(5) not null default '');
INSERT INTO "test" VALUES('1000');
INSERT INTO "test" VALUES('01000');
COMMIT;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora
Sent: Friday, May 31, 2013 11:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] numeric string quotation

I have table:

CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in Mysql, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?
 
 
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
autenticato? GRATIS solo con Email.it http://www.email.it/f
 
 Sponsor:
 Ami l'arte e vuoi arredare casa con stile? Su MisterCupido.com puoi
acquistare le RIPRODUZIONI DEI QUADRI di: Van Gogh, Monet, Klimt,
Modigliani, Cezanne, Hayez, Michelangelo, Raffaello, ecc
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12386&d=31-5
___
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] numeric string quotation db2 to db3

[Spora]

I have sqlite2 table:

CREATE TABLE test (
  alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in sqlite3, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?

excuse previuos in MySql.


--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:

Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12824&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation db2 to db3

[Richard Hipp]

On Fri, May 31, 2013 at 1:32 PM, Spora  wrote:

> I have sqlite2 table:
>

In sqlite2, everything is a string.  So there is no solution to your
problem there.  Please consider using sqlite3 instead.

SQLite3 came out in 2004.  The last release of sqlite2 was in 2005.  The
last update to the sqlite2 sources was in 2007.



>
> CREATE TABLE test (
>   alfa char(5) NOT NULL default '',
> );
>
> when i .dump i obtain:
>
> INSERT INTO test VALUES(01000);
>
> but when i import in sqlite3, i obtain
>
> test = '1000'
> and not
> test = '01000'
>
> because .dump create:
> INSERT INTO test VALUES(01000);
> and not:
> INSERT INTO test VALUES('01000');
>
> how to solve?
>
> excuse previuos in MySql.
>
>
> --
> Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
> autenticato? GRATIS solo con Email.it http://www.email.it/f
>
> Sponsor:
>
> Clicca qui: 
> http://adv.email.it/cgi-bin/**foclick.cgi?mid=12824&d=31-5
> __**_
> 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] numeric string quotation db2 to db3

[Spora]

I undstand, but now i have to import a large db2 in db3.

Richard Hipp ha scritto quanto segue il 31/05/2013 19.44>



On Fri, May 31, 2013 at 1:32 PM, Spora mailto:sp...@email.it>> wrote:

I have sqlite2 table:


In sqlite2, everything is a string.  So there is no solution to your
problem there.  Please consider using sqlite3 instead.

SQLite3 came out in 2004.  The last release of sqlite2 was in 2005.  The
last update to the sqlite2 sources was in 2007.


CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in sqlite3, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?

excuse previuos in MySql.


_
sqlite-users mailing list
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-__bin/mailman/listinfo/sqlite-__users





--
D. Richard Hipp
d...@sqlite.org 



--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
Ami i PELUCHE? Acquistali online su MisterCupido.com! Tante offerte su: Disney, 
Simpson, Spongebob, Puffi, Sette Nani, Super Mario, Barbapapà, Teletubbies, 
Angry Birds, ecc
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12384&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation db2 to db3

[Richard Hipp]

On Fri, May 31, 2013 at 1:54 PM, Spora  wrote:

> I undstand, but now i have to import a large db2 in db3.
>


sqlite old.db .dump | sqlite3 new.db

The above will probably cause your '01000' to be convert to '1000'.  I
don't think that can be helped without hacking on the ".dump" code for
sqlite2.


-- 
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] numeric string quotation db2 to db3

[Spora]

But i see that sqlMaestro is able to do it.
How can he do?

Richard Hipp ha scritto quanto segue il 31/05/2013 19.44>



On Fri, May 31, 2013 at 1:32 PM, Spora mailto:sp...@email.it>> wrote:

I have sqlite2 table:


In sqlite2, everything is a string.  So there is no solution to your
problem there.  Please consider using sqlite3 instead.

SQLite3 came out in 2004.  The last release of sqlite2 was in 2005.  The
last update to the sqlite2 sources was in 2007.


CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in sqlite3, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?

excuse previuos in MySql.


--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e
SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:

Clicca qui:
http://adv.email.it/cgi-bin/__foclick.cgi?mid=12824&d=31-5

_
sqlite-users mailing list
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-__bin/mailman/listinfo/sqlite-__users





--
D. Richard Hipp
d...@sqlite.org 



--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
Crea la tua PEC con sconti fino al 20%: 2GB di spazio, sms di notifica, 
archivio di sicurezza.
* Prova adesso!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12826&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import skip first line

[Bart Smissaert]

That modified version sounds useful. Could you mail it?

RBS
On May 31, 2013 6:14 PM, "Larry Brasfield" 
wrote:

> Bart Smissaert wrote:
>
>> Importing a. csv file via the. import command of sqlite3.exe.
>> As the first line holds the field names I want to skip that.
>> There is no problem achieving this in code, but could I do this
>> purely via sqlite3 commands?
>>
>
> You could do it easily with a temporary table having an and additional
> autoincrement column, but the verbage to then select that table into the
> one where you want no header line would be code, IMO.
>
> I get this functionality with a modified version of the SQLite shell. When
> headers are on, it also expects to see them upon .import and does the right
> thing, which I take to be interpreting them as column names.
>
> --
> Larry Brasfield
>
> __**_
> 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] numeric string quotation db2 to db3

[Richard Hipp]

On Fri, May 31, 2013 at 2:01 PM, Spora  wrote:

> But i see that sqlMaestro is able to do it.
> How can he do?
>

I don't know.

-- 
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] numeric string quotation db2 to db3

[Michael Black]

So why don't you use SQLMaestro to import, then export it again.  I would
think that would quote it correctly.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora
Sent: Friday, May 31, 2013 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] numeric string quotation db2 to db3

But i see that sqlMaestro is able to do it.
How can he do?



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

Re: [sqlite] numeric string quotation db2 to db3

[Spora]

I need a scriptable solution.

Michael Black ha scritto quanto segue il 31/05/2013 20.08>

So why don't you use SQLMaestro to import, then export it again.  I would
think that would quote it correctly.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora
Sent: Friday, May 31, 2013 1:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] numeric string quotation db2 to db3

But i see that sqlMaestro is able to do it.
How can he do?







--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
PEC nel Registro delle Imprese entro il 30/6/2013, attivala ora e risparmi fino 
al 20%
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12825&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation db2 to db3

[Richard Hipp]

On Fri, May 31, 2013 at 2:18 PM, Spora  wrote:

> I need a scriptable solution.
>

Probably if you modify this line of code:

http://www.sqlite.org/src/artifact/46a1757c1?ln=395

so that the condition is always false and so that the else-clause is always
taken, then the ".dump" command will output everything as if it were a
string.  This should import fine into sqlite3.  Then from SQLite3 you can
export to whatever you want.

-- 
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] numeric string quotation db2 to db3

[Spora]

it is too complicated for me

Richard Hipp ha scritto quanto segue il 31/05/2013 20.29>



On Fri, May 31, 2013 at 2:18 PM, Spora mailto:sp...@email.it>> wrote:

I need a scriptable solution.


Probably if you modify this line of code:

http://www.sqlite.org/src/artifact/46a1757c1?ln=395

so that the condition is always false and so that the else-clause is
always taken, then the ".dump" command will output everything as if it
were a string.  This should import fine into sqlite3.  Then from SQLite3
you can export to whatever you want.

--
D. Richard Hipp
d...@sqlite.org 



--
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP 
autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
Offerta speciale PEC per ditte individuali e liberi professionisti, sconto del 
20% se attivi ora!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=12827&d=31-5
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation db2 to db3

[Richard Hipp]

On Fri, May 31, 2013 at 2:33 PM, Spora  wrote:

> it is too complicated for me
>

OK then, why don't you ".dump" into a file, then do a regular-expression
search and replace to quote all of the numbers.

-- 
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] Import skip first line

[Larry Brasfield]

Bart Smissaert wrote:

That modified version sounds useful. Could you mail it?


The source as modified is at
   http://www.filedropper.com/sqlite3modifiedshell
.

This includes a readline library that works on Win32.  The SQLite 
library source is omitted for space.  The shell incorporates the current 
release (v3.7.17) changes.  The file doall.btm is understood by JPSoft's 
TCC and the free light version, TCC/LE.  The makefile may not be 
current.  The modified shell is shell_3r7r17_mod.c .


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

Re: [sqlite] Import skip first line

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/05/13 05:34, Bart Smissaert wrote:
> Importing a. csv file via the. import command of sqlite3.exe. As the
> first line holds the field names I want to skip that. There is no
> problem achieving this in code, but could I do this purely via sqlite3
> commands?

You can also use the enhanced shell in APSW (Python wrapper).  You don't
need to use or write any Python and can just execute it for the import.

Of particular use is that it has a .autoimport command which automatically
sets up the correct column names.  It also deduces the type in each column
so for example phone numbers won't be mangled to integers, and dates will
be "fixed" automatically determining if they are US or correct format.  It
also automatically works out separators (eg csv, tabs, pipes).

http://apidoc.apsw.googlecode.com/hg/shell.html

sqlite> .help autoimport

.autoimport FILENAME ?TABLE?  Imports filename creating a table and
  automatically working out separators
  and data types (alternative to .import
  command)

The import command requires that you precisely pre-setup the table
and schema, and set the data separators (eg commas or tabs).  In
many cases this information can be automatically deduced from the
file contents which is what this command does.  There must be at
least two columns and two rows.

If the table is not specified then the basename of the file will be
used.

Additionally the type of the contents of each column is also deduced
- - for example if it is a number or date.  Empty values are turned
into nulls.  Dates are normalized into -MM-DD format and
DateTime are normalized into ISO8601 format to allow easy sorting
and searching.  4 digit years must be used to detect dates.  US
(swapped day and month) versus rest of the world is also detected
providing there is at least one value that resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only
treated as numbers if they do not have unnecessary leading zeroes or
plus signs.  This is to avoid treating phone numbers and similar
number like strings as integers.

This command can take quite some time on large files as they are
effectively imported twice.  The first time is to determine the
format and the types for each column while the second pass actually
imports the data.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlGpIoEACgkQmOOfHg372QRSqwCg3WTRwifRKC+tK55BmTbomVyo
PHsAnR8v79nKkpVZ7WYWydeTYxsHbZoE
=c95H
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Android large file support patch

[Dan Kennedy]

On 05/31/2013 09:28 PM, Pepijn Van Eeckhoudt wrote:

Sorry for the non-threaded reply. My subscription was set to digest mode...

Patch is available at
https://dl.dropboxusercontent.com/u/105584447/android_largefile.patch

Thanks for the patch.

When I compile on Android with it, I get linker errors for ftruncate64(),
pread64() and pwrite64(). Various posts on the internet suggest that
one needs to compile the required wrapper functions into the app itself.
Is this what you are doing?

Thanks,
Dan.






Pepijn
___
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