[sqlite] Moving from Sqlite 3.5.9 to 3.9.1
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 space. Disk space is ridiculously cheap. Simon.
[sqlite] how to empty a table with sqliteEstudio-2.0.26
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?
> 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 multiple NAND chips at once, HDDs can > start moving the head to the next location as soon as possible, etc. > Though, I'm not sure how applicable this is to iOS development. All true. The way you do it is to set up a process (or, if you already have threads, a thread) which does all the SQLite access. It queues up access requests and serves them. The thing is, this program (whatever it is) is running on an iOS device, which is probably a phone. The only thing that really needs multiple threads on a phone is a 3D game. Nothing that would access a huge SQLite database. Simon.
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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: http://jmatchparser.sourceforge.net/factbook/ it might be worth translating it into sqlite.
[sqlite] documentation: autoindex vs automatic index
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, the log code associated with it [3] is called SQLITE_WARNING_AUTOINDEX, so you can see why this is a bit confusing. The SQLite source code explains that 'sqlite3_autoindex_*' is the index created for PRIMARY KEY and UNIQUE constraints (i.e. a persistent index created at table creation time): ** If pName==0 it means that we are ** dealing with a primary key or UNIQUE constraint. We have to invent our ** own name. /* This routine has been called to create an automatic index as a ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or ** a PRIMARY KEY or UNIQUE clause following the column definitions. */ Indeed the column from the query plan is part of a PRIMARY KEY, and there was no warning logged via SQLITE3_CONFIG_LOG so there is nothing to worry about: CREATE TABLE fmeta (file_id INTEGER NOT NULL REFERENCES files(fid) ON DELETE CASCADE ON UPDATE CASCADE, key TEXT (256) NOT NULL, value BLOB (1024) NOT NULL, PRIMARY KEY(file_id, key)); I would suggest adding an explanation about sqlite3_autoindex_ to the documentation. For example add to [1]: -- example -- You can also use [EXPLAIN QUERY PLAN][2] to check for automatic index usage at query preparation time. -- example -- And add to [2]: -- example -- 1.5 Automatic indices SQLite may create automatic indices in certain situations [1]. You can use EXPLAIN QUERY PLAN to check for this, following the example from [1]: sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a=c; sele order from deta - 0 0 0 SCAN TABLE t1 0 1 1 SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (c=?) You can create a persistent index so that SQLite doesn't have to create a new index at every query: sqlite> CREATE INDEX my_persistent_index ON t2(c); sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a=c; sele order from deta - 0 0 0 SCAN TABLE t1 0 1 1 SEARCH TABLE t2 USING INDEX my_persistent_index (c=?) Or if your entries in the column are unique you can use a primary key or unique constraint with same effect: sqlite> CREATE TABLE t3(c PRIMARY KEY,d); sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t3 WHERE a=c; sele order from deta - 0 0 0 SCAN TABLE t1 0 1 1 SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (c=?) Note that sqlite_autoindex_* is the name given by SQLite to indexes created automatically as a result of a PRIMARY KEY or UNIQUE clause. -- example -- [1]: https://www.sqlite.org/optoverview.html#autoindex [2]: https://www.sqlite.org/eqp.html#autoindex [3]: https://www.sqlite.org/rescode.html#warning_autoindex -- Edwin T?r?k | Co-founder and Lead Developer Skylable open-source object storage: reliable, fast, secure http://www.skylable.com
[sqlite] Moving from Sqlite 3.5.9 to 3.9.1
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 sync new records, in Sqlite DB to server and got error SQLITE_BUSY (5). Set the busy timeout and still was seeing issues. Moving to 3.9.2 the busy errors went away. Always nice. Is there any concerns I should have other than a full regression?
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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 # e.g. 83,871 sq km t.integer :area_land # e.g. 82,445 sq km --use float - why? why not? t.integer :area_water # e.g. 1,426 sq km t.integer :population# e.g. 8,665,550 (July 2015 est.) t.float :population_growth# e.g. 0.55% (2015 est.) t.float :birth_rate# e.g. 9.41 births/1,000 population (2015 est.) t.float :death_rate # e.g. 9.42 deaths/1,000 population (2015 est.) t.float :migration_rate # e.g. 5.56 migrant(s)/1,000 population (2015 est.) t.timestamps end Good to see that "hand-coded" statements can get improved. Will try to clean-up the schema. Cheers. [1] github.com/worlddb/factbook/blob/master/lib/factbook/db/schema.rb
[sqlite] Question about Style
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 sources when trying to troubleshoot problems, and when I make a change directly in the source code and I'm on my merry way after a recompile, and the new code gets put into VC when I submit the change. *Method 2* Although I've not employed this method often, I'll keep a separate unit containing constant strings of all queries to be used for this application. Within the code, I'd use the querys constant name I've defined in the unit. However, I've found that it becomes a bit difficult to maintain, BUT, run time is fast as the compiler will essentially replace the constant name with the string and there is exactly zero look up time. If push came to shove, I could easily write an application that runs at pre-compile time that'd generate the unit based on that SQL code managers KEY=VALUE export, but, that'd be just one more thing to troubleshoot if something along the line failed. *Method 3* Another way is the same method that James mentions in that he uses external text files and builds them into his EXE. I've done this as well, and it works, although, handling simple resources like that is a bit annoying for me coding wise and organizational wise (Having several notepads or tabs in PSPad isn't ideal for me), especially when I might need tens to hundreds of individual queries. I could improve the code so it is less transparent, but, I've since moved onto this next method. *Method 4* The last, and most recent way that I work for hard-core applications dealing this particular problem is once I realized that I've used the same kind of SQL code and database structures (User Authentication mechanisms, or, tables with KEY=VALUE pairs, etc) across different projects, I wrote a program that manages a central repository of all my SQL table schemas and queries. On a new project, I tell this application where to either create, recreate, or add into a database file that will be used by the application the required table and view schemas, and then include sample or default data if required. It will also create another SQL database file that includes a table of KEY=VALUE of all queries that should be available to the program. At compile time, I build in the KEY=VALUE database into a resource file. At runtime, I create a globally accessible object that reads in this KEY=VALUE database and relevant table and puts the results into a tStringList, then my application refers to the object that contains the tStringList. When done, I drop the SQLite database from memory.[1] If, during development, I make changes to the KEY=VALUE table/database through my favorite SQLite3 database manager, I can have this application scan all KEY=VALUE databases it knows of, and import the changes and keep revisions available for other applications if needed. On export I can tell the application exactly which version of the query I want.[2] [1]=I haven't done tests to compare the speed of doing multiple SELECT queries to get a result versus just using a tStringList.Values['SQLStringName'] and get a result. In other words, I don't know if Pascal/Delphi tStringList.Values[] is quicker to do this kind of look up compared to creating a new table instance, generating the query, and getting the string result. As always, with C/C++/OC, YMMV. [2]=This application I wrote is seldom used because I'm not changing details all that much. It is basically used when I create new projects, do the exporting, and such, and then when I want to validate the existing KEY=VALUE pairs out 'in the field' in my development directory on my computer. On Fri, Oct 30, 2015 at 2:08 PM, Ramar Collins wrote: > Hi Guys, > > I'm working on a static site generator and want to use SQLite to store > metadata. 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? > > Here is an example: > char query[1024]; > char *val1, *val2, *qs = "INSERT INTO posts VALUES ( NULL, '%s', '%s' ); > /* initialize val1 and val2... */ > snprintf(query, 1023, qs, val1, val2); > /* open database, bind statement, etc. */ > sqlite3_exec(query); > > > The example is not nearly complete, but I'm almost certain there's a > cleaner way to seperate the SQL from the rest of the code. Before I go > come up with my own thing, I wanted to see if there perhaps some better > solutions already out there. > > Antonio R. Collins II > ramar.collins at gmail.com > http://ramarcollins.com > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org >
[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
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 disk. You tell the disk what you > want to read and it has to find that piece of disk, read it, and give the > result to you. It cannot answer four requests at once so while it's > answering one, the other threads have to wait. > > (Yes disk is cached. But that just moves the problem from the hard disk to > the piece of software which handles the cache. It cannot answer four > questions at once.) 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 multiple NAND chips at once, HDDs can start moving the head to the next location as soon as possible, etc. Though, I'm not sure how applicable this is to iOS development. -- Cory Nelson http://int64.org
[sqlite] FTS5 explicitly set delimiter
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 tokenchars '!@#$%' and so on. I think you'll have to do the above, yes. Dan.
[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
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 reasons making lower performance. ???:Simon Slavinslavins at bigfraud.org ???:SQLite mailing listsqlite-users at mailinglists.sqlite.org :2015?11?2?(??)?11:38 ??:Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing? On 2 Nov 2015, at 3:12am, sanhua.zh sanhua.zh at foxmail.com 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 things to talk to your disk at the same time. Your disk interface can service only one thread at a time. Your threads are having a contest to read and write the same part of hard disk, and only one can do this at one time. The other three do nothing while that one is keeping the disk busy. So making four threads -- or forty threads -- is not going to help. Sorry. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
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 = [self now];
sqlite3_close(handle);
double cost = after-before;
NSLog(@"cost %f begin %f after %f", cost, before, after);
}
1-thread result,
2015-11-02 11:10:04.670 TestSQLite[1663:591258] cost 0.105395 begin
1446433804.564858 after 1446433804.670253
4-threads result,
2015-11-02 11:10:41.484 TestSQLite[1666:591636] cost 0.150516 begin
1446433841.333576 after 1446433841.484092
2015-11-02 11:10:41.540 TestSQLite[1666:591635] cost 0.199100 begin
1446433841.341357 after 1446433841.540457
2015-11-02 11:10:41.540 TestSQLite[1666:591637] cost 0.188056 begin
1446433841.351582 after 1446433841.539638
2015-11-02 11:10:41.564 TestSQLite[1666:591638] cost 0.166090 begin
1446433841.397650 after 1446433841.563740
???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?11?2?(??)?10:47
??:Re: [sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
On 2 Nov 2015, at 2:45am, Simon Slavin slavins at bigfraud.org 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't know whether that works properly but it should not affect timing either
way. Simon. ___ sqlite-users
mailing list sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
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, name = (null)}
start
1: 1446432801.179614, cost 0.104205, info: NSThread: 0x15de373a0{number = 2,
name = (null)} end
4-threads result,
2015-11-02 10:52:26.908 TestSQLite[1644:586341]
0: 1446432746.741779, info: NSThread: 0x147d4c7b0{number = 5, name = (null)}
start
1: 1446432746.908137, cost 0.166358, info: NSThread: 0x147d4c7b0{number = 5,
name = (null)} end
2015-11-02 10:52:26.923 TestSQLite[1644:586346]
0: 1446432746.746527, info: NSThread: 0x147e05230{number = 4, name = (null)}
start
1: 1446432746.923394, cost 0.176867, info: NSThread: 0x147e05230{number = 4,
name = (null)} end
2015-11-02 10:52:26.952 TestSQLite[1644:586342]
0: 1446432746.783269, info: NSThread: 0x147e822c0{number = 2, name = (null)}
start
1: 1446432746.952698, cost 0.169429, info: NSThread: 0x147e822c0{number = 2,
name = (null)} end
2015-11-02 10:52:26.965 TestSQLite[1644:586343]
0: 1446432746.804017, info: NSThread: 0x147d65b30{number = 3, name = (null)}
start
1: 1446432746.965503, cost 0.161486, info: NSThread: 0x147d65b30{number = 3,
name = (null)} end
"4-threads" still take 60-70% slower.
???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?11?2?(??)?10:45
??:Re: [sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
On 2 Nov 2015, at 2:19am, sanhua.zh sanhua.zh at foxmail.com 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 the SQLite calls) but that will not affect what we're discussing. Please
compare times taken by your program when you use 'WAL' mode vs. using 'DELETE'
mode. Simon. ___ sqlite-users
mailing list sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about Style
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, one per statement. Very handy for
testing & development, too, btw.
At initialization, I read all the SQL into a list. In C it might be an
array of structures like
struct query_t { char *name; char *sql; } *queries;
where name is the filename and sql is the content of the file. With
just a little work and the help of bsearch(3), you can refer to your
queries by name and keep the literal SQL out of your code. As others
mentioned, it's better (and easier) to use prepared queries than
string-slinging for parameters.
If you don't want depend on separate files of SQL at runtime, write a
script to generate a .c file of a static array of query_t, as above.
HTH.
--jkl
[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
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 memory. This will tell you if you're running into some kind of > thread locking or if you're running into a disk IO issue. > > I have absolutely no clue about the back end of iOS (Other than it is linux > based) so I don't know what it offers for threaded operations, or if the > library you're using has threaded capabilities. > iOS is BSD based, not linux based. Not that they're hugely incompatible, but ... just FYI. -- Scott Robison
[sqlite] Why SQLite take lower performance in multi-threadSELECTing?
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);
}
}
@implementation TestCase {
NSString* _path;
NSUInteger _threadCount;
}
- (id)init
{
if (self = [super init]) {
NSString* document =
NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,
YES)[0];
_path = [document stringByAppendingPathComponent:@"test.sqlite"];
_threadCount = 4;
}
return self;
}
- (void)clear
{
//remove existing DB file
NSFileManager* fm = [NSFileManager defaultManager];
NSArray* paths = @[_path, [_path stringByAppendingString:@"-shm"], [_path
stringByAppendingString:@"-wal"], [_path stringByAppendingString:@"-journal"]];
for (NSString* path in paths) {
if ([fm fileExistsAtPath:path]) {
NSLog(@"file exists %@", path);
NSError* error;
if (![fm removeItemAtPath:path error:error]) {
NSLog(@"remove error %@", error);
exit(0);
}
}
}
}
- (void)prepare
{
[self clear];
sqlite3_config(SQLITE_CONFIG_LOG, sqliteLog, NULL);
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
sqlite3* handle;
sqlite3_open(_path.UTF8String, handle);
//pragma
sqlite3_exec(handle, "PRAGMA LOCKING_MODE=NORMAL;", NULL, NULL, NULL);
sqlite3_exec(handle, "PRAGMA JOURNAL_MODE=WAL;", NULL, NULL, NULL);
sqlite3_exec(handle, "PRAGMA SYNCHRONOUS=FULL;", NULL, NULL, NULL);
//create table
sqlite3_exec(handle, "CREATE TABLE testtable (id INT PRIMARY KEY);", NULL,
NULL, NULL);
//pre insert
sqlite3_exec(handle, "BEGIN", NULL, NULL, NULL);
sqlite3_stmt* stmt;
sqlite3_prepare_v2(handle, "INSERT INTO testtable (id) VALUES (?);", -1,
stmt, NULL);
for (int i = 0; i 100; i++) {
sqlite3_bind_int(stmt, 1, i);
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_exec(handle, "COMMIT", NULL, NULL, NULL);
sqlite3_finalize(stmt);
sqlite3_close(handle);
}
- (void)test
{
[self prepare];
NSRecursiveLock* lock = [[NSRecursiveLock alloc] init];
__block NSUInteger threadWait = _threadCount;
for (int i = 0; i _threadCount; i++) {
NSString* threadname = [NSString stringWithFormat:@"test_thread_%d", i];
dispatch_async(dispatch_queue_create(threadname.UTF8String,
DISPATCH_QUEUE_CONCURRENT), ^{
NSString* start = [NSString stringWithFormat:@"%@ start", [NSThread
currentThread]];
NSString* end = [NSString stringWithFormat:@"%@ end", [NSThread
currentThread]];
sqlite3* handle;
sqlite3_open(_path.UTF8String, handle);
//wait for all thread
[lock lock];
threadWait--;
[lock unlock];
while (threadWait);
//begin
[Ticker tickWithInfo:start];
sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL);
[Ticker stopWithInfo:end];
sqlite3_close(handle);
});
}
}
@end
???:Richard Hippdrh at sqlite.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?11?1?(??)?03:45
??:Re: [sqlite] Why SQLite take lower performance in multi-threadSELECTing?
On 10/30/15, sanhua.zh sanhua.zh at foxmail.com wrote: Hi, all I use SQLite
in iOS. I found that SQLite will take lower performancein multi-thread
SELECTing. Here is my test result, It costs 0.11s to select 100,
elements,in 1-thread SELECTing: But the sameIn 4-thread SELECTing, it costs
0.2s avg.This test run on iPhone 6s. You can see that 4-thread is take
almost twice slower than 1-thread. I know multi-thread might costs the system
resource, but this result is much slower than what I excepted. Is there some
race condition in SQLite or I write the wrong code ? You *might* be doing
something wrong. It is hard to say without seeing your code. But the 2x
slowdown might be entirely due to thread contention and mutexing. Remember that
the flash memory filesystem on iOS has finite bandwidth, and all the threads in
the world will not increase that bandwidth. The extra threads just cause extra
overhead which ends up slowing everything down. -- D. Richard Hipp drh at
sqlite.org ___ sqlite-users mailing
list sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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 variable-length text fields in SQLite. That field will be interpreted as TEXT and you cannot depend on truncation to 255 characters. I would advise you to change the above to "code" TEXT NOT NULL since this is the result you will get anyway. Second, although SQLite supports quoted column names, they're rather non-standard these days. SQLite can cope with double-quotes, MySQL can cope with ticks (back-apostrophes), MS SQL copes with brackets, etc.. Since every SQL engine copes well with unescaped names, e.g. code TEXT NOT NULL , and you don't use punctuation in your names, you might prefer to use that. Simon. (I am uncomfortably aware that one of my pieces of advice is to use a SQLite rule instead of a general standard, and the other is to use a general standard instead of a SQLite rule. I resort to Emerson: "A foolish consistency is the hobgoblin of little minds.")
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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 SQL schema for the World Factbook and also includes a pre-built > single-file SQLite database, that is, factbook.db [2] for download. > > What's the World Factbook? > > The World Factbook [3] published by the Central Intelligence Agency (CIA) > offers free 260+ country profiles in the public domain > (that is, no copyright(s), no rights reserved). > > Anyways, what's it good for? For example, to find the ten largest > countries by area, try: > > SELECT name, area FROM facts ORDER BY area DESC LIMIT 10; > > Resulting in: > > Russia | 17_098_242 > Canada | 9_984_670 > United States | 9_826_675 > China | 9_596_960 > Brazil | 8_515_770 > Australia | 7_741_220 > European Union | 4_324_782 > India | 3_287_263 > Argentina | 2_780_400 > Kazakhstan | 2_724_900 > >Or to find the ten largest countries by population, try: > > SELECT name, population FROM facts ORDER BY population DESC LIMIT 10; > >Resulting in: > >World | 7_256_490_011 >China | 1_367_485_388 >India | 1_251_695_584 >European Union | 513_949_445 >United States | 321_368_864 >Indonesia | 255_993_674 >Brazil | 204_259_812 >Pakistan | 199_085_847 >Nigeria| 181_562_056 >Bangladesh | 168_957_745 > >And so on. Note: Using the factbook command line tool and scripts > you can build yourself an up-to-date copy. > >Questions? Comments? Welcome. Enjoy. Cheers. > > [1] https://github.com/factbook/factbook.sql > [2] https://github.com/factbook/factbook.sql/releases > [3] https://www.cia.gov/library/publications/the-world-factbook > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer.
[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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 April 2015 and this project as far as I can tell can only "process" the download archives (not the live pages) and, thus, the latest update is from June, 2014 - the project is still waiting for a new download archive (formerly happened about once a year). About the schema: The project uses the "easy" and generic way for properties e.g.: CREATE TABLE `factbook_fields` ( `id` int(11) NOT NULL, `categoryid` int(11) NOT NULL, `name` varchar(255) COLLATE utf8_bin NOT NULL, `description` text COLLATE utf8_bin NOT NULL, `rankorder` int(11) NOT NULL, `unit` varchar(50) COLLATE utf8_bin NOT NULL, `dollars` tinyint(4) NOT NULL, PRIMARY KEY (`version`,`id`), ) 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, "name" varchar(255) NOT NULL, --- -- Geography "area" integer, "area_land" integer, "area_water" integer, -- People and Society "population"integer, "population_growth" float, "birth_rate"float, "death_rate"float, "migration_rate"float, ... ) Again thanks for the link and comments. Always great to see (and study) alternatives. Cheers.
[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
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 find that piece of disk, read it, and give the result to you. It cannot answer four requests at once so while it's answering one, the other threads have to wait. (Yes disk is cached. But that just moves the problem from the hard disk to the piece of software which handles the cache. It cannot answer four questions at once.) Simon.
[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
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 things to talk to your disk at the same time. Your disk interface can service only one thread at a time. Your threads are having a contest to read and write the same part of hard disk, and only one can do this at one time. The other three do nothing while that one is keeping the disk busy. So making four threads -- or forty threads -- is not going to help. Sorry. Simon.
[sqlite] Why SQLite take lower performance in multi-threadSELECTing?
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't know whether that works properly but it should not affect timing either way. Simon.
[sqlite] Why SQLite take lower performance in multi-threadSELECTing?
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 the SQLite calls) but that will not affect what we're discussing. Please compare times taken by your program when you use 'WAL' mode vs. using 'DELETE' mode. Simon.
