[sqlite] Moving from Sqlite 3.5.9 to 3.9.1

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 11:15pm, Jim Morris wrote: > First, I was able to drop in the 3.9.2 sqlite.c and sqlite.h files, build and > run the app without compiler, linker or other apparent errors. Great job by > dev team! This is, in fact, the advised way to use SQLite. Library files save disk sp

[sqlite] how to empty a table with sqliteEstudio-2.0.26

2015-11-02 Thread Héctor Fiandor
Dear fellows, I have obtained the sqliteEstudio-2.0.26 and I need to empty a table. Is possible with this program? If not, how to do? Thanks in advance, Yours, Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread Simon Slavin
> On 2 Nov 2015, at 7:46pm, Cory Nelson wrote: > > Serializing in user space is going to prevent pipelining, so it's > definitely not the greatest idea. Command queuing is a great reason to > have parallel I/Os and can make a big difference depending on your > storage type. SSDs can read multipl

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

2015-11-02 Thread Philip Warner
On 2/11/2015 1:49 AM, Gerald Bauer wrote: >I've started a new project, that is, /factbook.sql [1] that offers > an SQL schema for the World Factbook and also includes a pre-built > single-file SQLite database, that is, factbook.db [2] for download. Have you seen the XML/mysql versoin: ht

[sqlite] documentation: autoindex vs automatic index

2015-11-02 Thread Török Edwin
Hi, The SQLite documentation does a good job explaining what an 'automatic index' is [1], so when someone looks at 'explain query plan' output and sees something like this they get worried: SEARCH TABLE fmeta USING INDEX sqlite_autoindex_fmeta_1 (file_id=?) The link above[1] uses #autoindex, th

[sqlite] Moving from Sqlite 3.5.9 to 3.9.1

2015-11-02 Thread Jim Morris
First, I was able to drop in the 3.9.2 sqlite.c and sqlite.h files, build and run the app without compiler, linker or other apparent errors. Great job by dev team! Our Windows Mobile 6.x app has been using SQLite 3.5.9, threading mode=1. I added a background thread, with its own connection to

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

2015-11-02 Thread Gerald Bauer
Hello, Thanks. Great advice. The SQLite schema code gets generated from the ActiveRecord (Ruby) source [1] e.g.: create_table :facts do |t| t.string :code, null: false # country code e.g. au t.string :name, null: false # country name e.g. Austria t.integer :area

[sqlite] Question about Style

2015-11-02 Thread Stephen Chrzanowski
I've tackled this problem a few ways; *Method 1* The one I usually go with (Old habits die hard, and I'm freak'n lazy when it comes to one or two statements in the whole application) is just put the SQL string right into the code. That way it doesn't 'get lost', I don't have to rely on external s

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread Cory Nelson
On Sun, Nov 1, 2015 at 10:25 PM, Simon Slavin wrote: > > On 2 Nov 2015, at 3:48am, sanhua.zh wrote: > >> I thought it might be storage contention, too. >> BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do >> read for disk only. > > Even reading needs the attention of the d

[sqlite] FTS5 explicitly set delimiter

2015-11-02 Thread Dan Kennedy
On 11/02/2015 01:55 AM, chromedout64 at yahoo.com wrote: > Thanks, I figured that this might be the case. What is the best way to > specify all characters except whitespace as part of a CREATE VIRTUAL TABLE > statement? Should you simply list each of the literal ascii characters such > as tokenc

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
I thought it might be storage contention, too. BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do read for disk only. And I check the source code of SQLite, it uses unix file lock implement mutex. Will unix file lock keep one reading at one time ? If not, it might be other r

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
I change my code to make it clear. - (double)now { struct timeval time; gettimeofday(time, NULL); double ms = time.tv_sec+time.tv_usec/100.0; return ms; } - (void)test { ... double before = [self now]; sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL); double after

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

2015-11-02 Thread sanhua.zh
I don?t test the returned values because it?s just a test case. And ?sqliteLog' function will tell me if it failed. I compare the same thing in ?DELETE? mode just now. 1-thread result, 2015-11-02 10:53:21.179 TestSQLite[1647:586781] 0: 1446432801.075409, info: NSThread: 0x15de373a0{number = 2,

[sqlite] Question about Style

2015-11-02 Thread James K. Lowden
On Fri, 30 Oct 2015 14:08:16 -0400 Ramar Collins wrote: > I'm using C and a small library to get the majority of the work > done. My question is, do you have any suggestions or know where to > find more lore on how to nicely embed SQL in a program like this? I store my SQL in separate files,

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread Scott Robison
On Sun, Nov 1, 2015 at 9:48 PM, Stephen Chrzanowski wrote: > @sanhua> If you can, just for testing, use the SQLite backup mechanism to > copy the database to memory, then run your transactions off the memory > version. This will get rid of disk IO with the exclusion of the OS > swapping to memor

[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

2015-11-02 Thread sanhua.zh
it seems that this mail list will filter my code. I re-send it now. It?s written by Objective-C and C, but it?s quite simple to understand. void sqliteLog(void* userInfo, int retCode, const char* text) { if (retCode != SQLITE_OK) { NSLog(@"SQLITE FAILED errCode=%d, errMsg=%s", retCode, text

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 7:45am, Gerald Bauer wrote: > I prefer the "simpler" non-generic way e.g. spelling out all fields e.g.: > > CREATE TABLE "facts"( > "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > "code" varchar(255) NOT NULL, A couple of quick comments: First, there are not variabl

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

2015-11-02 Thread Michael Falconer
Gerald, thanks for this, it looks most interesting on both levels. i.e. The data release and your project work. Thank you for the information and your project. On 2 November 2015 at 01:49, Gerald Bauer wrote: > Hello, > > I've started a new project, that is, /factbook.sql [1] that offers > an

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

2015-11-02 Thread Gerald Bauer
Hello, Thanks for your kind words and the links. About: > Have you seen the XML/mysql versoin: Yes, this is great and seems to be the "last" reader/project (of many others) that still is maintained and might get updated. The CIA changed the structure of the online (live) pages in A

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 3:48am, sanhua.zh wrote: > I thought it might be storage contention, too. > BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do read > for disk only. Even reading needs the attention of the disk. You tell the disk what you want to read and it has to fin

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 3:12am, sanhua.zh wrote: > I change my code to make it clear. That is must more easy for me to understand. I can think of no other explanation for your problem than storage contention. Multiple threads allow lots of processing at the same time but they do not allow four th

[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 2:45am, Simon Slavin wrote: > There's nothing wrong with your code. Well actually there is (you don't test > the values returned by all the SQLite calls) but that will not affect what > we're discussing. Okay, I see how you do this now: you use the log trap to do it. I don

[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

2015-11-02 Thread Simon Slavin
On 2 Nov 2015, at 2:19am, sanhua.zh wrote: > it seems that this mail list will filter my code. I re-send it now. > It?s written by Objective-C and C, but it?s quite simple to understand. There's nothing wrong with your code. Well actually there is (you don't test the values returned by all th