[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)
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] FTS5 explicitly set delimiter
On 11/01/2015 06:39 PM, chromedout64 at yahoo.com wrote: > Is there an easy way to set an FTS5 table so that the only > delimiter/separator is a space and everything else, including all > punctuation, is a token? Some searching revealed that there is an > undocumented feature as a part of FTS3/4 that allows the actual delimiter to > be specified as part of table creation, but I'm not sure if this exists for > FTS5. You could use the tokenchars option of the unicode61 or ascii tokenizers to specify that all characters except whitespace are token characters: https://www.sqlite.org/fts5.html#section_4_3_1 Dan.
[sqlite] FTS5 explicitly set delimiter
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. Or is it possible or would it be better to use some sort of hex representation of all of them understood by SQLite or another method?
[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
@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. On Sun, Nov 1, 2015 at 11: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.) > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] two memory leaks in shell
These were found by valgrind and verified by hand. I do not think they
are serious. I'm sorry for not including line numbers, but I doubt they
would be the same as in your actual source files anyway.
1. string returned from find_home_dir() not freed in process_sqliterc().
The find_home_dir() function returns a string allocated by malloc()
which the caller must free. But process_sqliterc() does not. This
function stores the string in a local variable called home_dir then uses
it to calculate another string with a full path. Excerpt:
home_dir = find_home_dir();
if( home_dir==0 ){
fprintf(stderr, "-- warning: cannot find home directory;"
" cannot read ~/.sqliterc\n");
return;
}
sqlite3_initialize();
zBuf = sqlite3_mprintf("%s/.sqliterc",home_dir);
sqliterc = zBuf;
It appears a good place to call free() would be at the end of this code
block, as home_dir is not used afterwards.
2. ShellState.zKey not freed
In the SEE shell, the shell accepts an encryption key using the -key and
-hexkey command-line arguments. Strings are allocated by
sqlite3_mprintf() and stored in the zKey field of the ShellState struct
as shown in the excerpt below.
}else if( strcmp(argv[i],"-key")==0 ){
data.zKey = sqlite3_mprintf("%s",cmdline_option_value(argc,argv,++i));
data.nKey = strlen(data.zKey);
}else if( strcmp(argv[i],"-hexkey")==0 ){
data.zKey = sqlite3_mprintf("%s",cmdline_option_value(argc,argv,++i));
data.nKey = shellHexToBin(data.zKey);
if( data.nKey<0 ){
fprintf(stderr, "%s: invalid key string\n", argv[0]);
exit(1);
}
The contract for sqlite3_mprintf is that the caller must free the result
using sqlite3_free(). But this never happens. A reasonable place to
perform the free would be at the end of main() where other cleanup is
also performed.
JKL
[sqlite] FTS5 explicitly set delimiter
Is there an easy way to set an FTS5 table so that the only delimiter/separator is a space and everything else, including all punctuation,?is a token? Some searching revealed that there is an undocumented?feature as a part of FTS3/4 that allows the actual delimiter to be specified as part of table creation, but?I'm not?sure if this exists for FTS5.?
[sqlite] The problem of inserting data too slow with index
On 10/30/15, aa wrote: >But recently I meet a problem about inserting data into table.It is > too slow whit index. >The first I create a table like this: > CREATE TABLE mac_tb (mac BIGINT PRIMARY KEY? > If I insert into mac_tb with mac ordey by num desc or asc , then the > speed is fast. > If I insert the mac randomly, then the speed will grow slower whit > the increasing of data. Don't forget that you should use transactions. Start a transaction with the BEGIN TRANSACTION statement, insert many rows (possibly all million you have, possibly just ten thousand), and then use the COMMIT TRANSACTION statement. If you do not explicitly start a transaction, then sqlite will execute each statement in a separate transaction, which could mean a transaction per row. This can be expensive, as described in "http://sqlite.org/faq.html"; . Further, if I understand correctly, if you do lots of inserts at the same time, then WAL mode might slow down the operation a bit. WAL mode is not the default, but someone might have enabled it in your database. Check with the PRAGMA journal_mode command that it is not enabled: if that command returns "wal", then WAL mode is active. Try to disable WAL mode if you're doing mass inserts. -- ambrus
