SQLite Forum

Timeline
Login

20 forum posts by user KevinYouren

2021-10-19
23:24 Reply: can I insert an 8GB file into sqlite? (artifact: 20ef934fda user: KevinYouren)

Andrew,

is there any more progress with your requirement?

The largest user type file I have is panlex_lite.zip which is 2.7G,

and it contains a 7.2 GB sqlite database.

2021-10-18
08:06 Reply: Column names and aliases in expressions (artifact: 2b07d62528 user: KevinYouren)

Could you possibly give the SQL and table definitions for your example, please?

2021-08-28
01:49 Post: Loading a large file into a sqlite database (artifact: 034d6228dd user: KevinYouren)
If you have a need analyze a really large file,
the following .import technique may be useful to you.

Note, I don't use a .mode sub-command, as my data is not in CSV format.

A 2.5GB Linux text file with about 67 million lines, 
loaded into a 3GB SQLITE db table in about 80 seconds.

The sqlite3 terminal command works quite well with this database.

The GUI database viewer I use hangs when I try to load the database.



my_directory$ ls -lai lsof_with_trace.trace
2475135568 Aug 27 11:58 lsof_with_trace.trace
my_directory$ rm lsof_with_trace_short_blob.sqlite
my_directory$ date;
Sat 28 Aug 2021 10:46:57 AEST

my_directory$ sqlite3 lsof_with_trace_short_blob.sqlite
SQLite version 3.37.0 2021-08-03 23:23:23
Enter ".help" for usage hints.
sqlite> create table trace_blob(trace_line BLOB);
sqlite> .schema
CREATE TABLE trace_blob(trace_line BLOB);
sqlite> .import "lsof_with_trace.trace" trace_blob
sqlite> select count(*) from trace_blob;
66920485
sqlite> .quit
my_directory$ date;
Sat 28 Aug 2021 10:48:14 AEST

my_directory$ ls -lai lsof_with_trace.trace
2475135568 Aug 27 11:58 lsof_with_trace.trace
my_directory$ ls -lai lsof_with_trace_short_blob.sqlite
3044913152 Aug 28 10:48 lsof_with_trace_short_blob.sqlite
2021-08-14
21:00 Reply: AVG funcion problem (artifact: 3ccac150db user: KevinYouren)
For this query, more information is required.

I downloaded your sample data, and had to add some ); characters to tidy up the sql.

Also, a table definition is missing:

Error: no such table: shopper_orders.
2021-05-26
21:17 Reply: can I insert an 8GB file into sqlite? (artifact: abc2bfa746 user: KevinYouren)
Andrew,

I suggest you search and read the Sqlite website for SQLAR, for SQLite Archive Files.

It will very nicely store photos, etc.

The schema is:

sqlite> .schema
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,
  mode INT,
  mtime INT,
  sz INT,
  data BLOB
);

As it is an Sqlite database, you can quite happily add any extra tables you want. 

For example, a table containing the names your camera uses for stored photos together with a description you can relate to. Similar to the idea of writing on the back of paper/cardboard photos. "Grandma with grandkids", etc.

Storing stuff over 1Gb is possible, by splitting the file into smaller junks.

That is, a bit of programming required for splitting and recombining.

sqlite> select name, mode, mtime, sz from sqlar;
fred.part1|33204|1621820988|913975706
fred.part2|33204|1621822838|912242597
fred.part3|33204|1621827849|909762547
fred.part4|33204|1621832248|907433793
fred.part5|33204|1621835715|205364403


I used a similar technique in the 90s, transferring files between Melbourne (Australia) and San Ramon (California) nightly. Megabytes in those days.
2021-05-23
21:18 Post: Suggestion on how to read the forums for latest items (artifact: bd090d75b1 user: KevinYouren)

If you are interested in reading the forums in a similar way to reading the latest entries in a newsfeed ("Just In", etc), try these links.

Also makes it easier to do a search of the last few days.

For Sqlite & Fossil, I get the latest 100:

https://sqlite.org/forum/timeline?ss=v&y=f&n=100&vfx&nsm
https://fossil-scm.org/forum/timeline?ss=v&y=f&n=100&vfx&nsm

The Pikchr forum isn't changed often, I get the latest 50:

https://pikchr.org/home/timeline?ss=v&y=f&n=50&vfx&nsm

2021-04-08
04:40 Reply: Impact analysis for "larger-databases" change (artifact: 5fc05f511e user: KevinYouren)
Thanks for the forum entry.

I found the list of programs already.

What I am asking is: 
 "How is the list of programs that require change(s) produced?"
01:37 Post: Impact analysis for "larger-databases" change (artifact: b9f269a42e user: KevinYouren)
My apologies if this type of question has been asked before, or if it should be asked in the Fossil forum.

If possible, could you tell about the following change in July last year.

"Allow for page numbers as large as 4294967294 (0xfffffffe) which means database files as large as 281 TB. (check-in: 166e82dd user: drh tags: trunk)"

How did you know which programs and which lines needed changing?
2021-02-19
19:18 Reply: Table and index in separate files (artifact: 68e681d8f6 user: KevinYouren)

Yes, checking and occasionally rebuilding indexes was common.

Moving from disks that spin around, and hence have rotational delay, to solid state drives is probably the "newest" innovation. I read an article in "Popular Mechanics", in 1967 or 68.

My SQLITE apps are about 3 times faster on the removable devices I bought last year.

In 1990, for an IMS database, I suggested moving the indexes to separate disks, but my manager started shaking and trembling, so I dropped the suggestion.

2021-01-11
09:30 Delete reply: Nit newly exposed in "How to Compile" (artifact: 1aff5e803f user: KevinYouren)
Deleted
09:29 Reply: Nit newly exposed in "How to Compile" (artifact: c7414bc67b user: KevinYouren)

Larry,

I checked my clone compile & link from a few days ago.

-lm & MATH_FUNCTIONS=1 already present. 'Tis somewhat verbose.

./libtool --mode=compile --tag=CC gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/src -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/rtree -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/icu -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/fts3 -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/async -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/session -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c libtool: compile: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/src -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/rtree -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/icu -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/fts3 -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/async -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/session -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o libtool: compile: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/src -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/rtree -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/icu -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/fts3 -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/async -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/session -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c -o sqlite3.o >/dev/null 2>&1 ./libtool --mode=link gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/src -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/rtree -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/icu -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/fts3 -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/async -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/session -I/media/kevin/KCY_Samsung_T5/KCYPrograms/myclone_of_sqlite/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_HAVE_ZLIB=1 -no-undefined -o libsqlite3.la sqlite3.lo -lm -ldl -lz -lpthread -rpath "/usr/local/lib" -version-info "8:6:8"

Here are the sizes, in case you are curious.

523286 -rwxr-xr-x 1 root root 7660448 Jan 4 18:40 sqlite3 kevin@KCYDell:/usr/local/bin$ ls -la sqlite -rwxr-xr-x 1 root root 7660448 Jan 4 18:40 sqlite3 kevin@KCYDell:/usr/local/bin$ cd /usr/local/lib kevin@KCYDell:/usr/local/lib$ ls -la sqlite -rw-r--r-- 1 root root 12059292 Jan 4 18:40 libsqlite3.a -rwxr-xr-x 1 root root 963 Jan 4 18:40 libsqlite3.la lrwxrwxrwx 1 root root 19 Jan 4 18:40 libsqlite3.so -> libsqlite3.so.0.8.6 lrwxrwxrwx 1 root root 19 Jan 4 18:40 libsqlite3.so.0 -> libsqlite3.so.0.8.6 -rwxr-xr-x 1 root root 4994856 Jan 4 18:40 libsqlite3.so.0.8.6

regs, Kev

2021-01-10
23:39 Reply: Nit newly exposed in "How to Compile" (artifact: a11752a353 user: KevinYouren)

Thanks, Larry.

I'll add the (PRAGMA??) -DSQLITE_ENABLE_MATH_FUNCTIONS=1 to my compile and link, as well.

I used to download the amalgamation from https://www.sqlite.com/download.html, unpack, and compile and link, and then copy to /usr/local/bin/sqlite3.

Now, I do a 'fossil pull' into my clone of sqlite, followed by:

./configure make

I'll get there. Linux is easy, for me.

2021-01-09
19:52 Reply: Nit newly exposed in "How to Compile" (artifact: fc8cd9dad0 user: KevinYouren)

Larry, this an interesting flowon from Dan's update of Full Text Search, which occurred a while ago.

Add "-lm" wherever -DSQLITE_ENABLE_FTS appears?

I also had to manually add the link to the maths library.

So, can the documentation be more closely linked to the "code"?

19:12 Reply: Nit newly exposed in "How to Compile" (artifact: 87d68c7df6 user: KevinYouren)

Larry, this an interesting flowon from Dan's update of Full Text Search, which occurred a while ago.

Add "-lm" wherever -DSQLITE_ENABLE_FTS appears?

I also had to manually add the link to the maths library.

So, can the documentation be more closely linked to the "code"?

2020-11-24
00:04 Reply: sha3 (sha3sum) (artifact: 374e0e394e user: KevinYouren)

Thanks, Keith, much appreciated.

2020-11-22
23:12 Reply: sha3 (sha3sum) (artifact: 372009e674 user: KevinYouren)

Keith, I like your detailed display, thanks.

This is the second time this year this query has been asked.

md5sum is a single value, whereas sha3 has a set.

I suggest changing sha3 to sha3-256, at least in the Download page, and perhaps the other pages that appear with Search.

2020-10-12
22:28 Reply: Most efficient way to tell if any database content has changed (artifact: e4dd574a6a user: KevinYouren)

Would a simple directory listing suffice?

It is independent of file size.

I mainly use Unix terminals, so:

ls -lai cfd_20201009_0146.sqlite

gives: 6553669 -rw-r--r-- 1 root root 200912896 Oct 9 12:53 cfd_20201009_0146.sqlite

Windows has similar and Android too. (with a bit of effort)

2020-10-10
01:27 Reply: Performance Issue: How can I increase a performance? (artifact: d3cfd28877 user: KevinYouren)

Artur,

I suggest you need more information about the number of list_id's, item_id's and modified's.

Sometimes people use the term CARDINALITY for these counts.

So, start with list_id, which is one form of a UUID. The UUID consists of the digits 0 thru 9 and the letters a thru f or A thru F. The hyphen, '-', is merely for human readability.

select count(*) from (select distinct list_id from item) ;

Repeat for "modified" and "item_ids".

2020-09-13
06:46 Reply: SHA-3 hash (artifact: 430d718dd8 user: KevinYouren)

I suggest having a read of :

https://www.di-mgt.com.au/sha_testvectors.html

This has some standard test examples to test the hash algorithm and software.

The line for the input abc is: SHA-3-256 3a985da74fe225b2045c172d6bd390bd855f086e3e9d525b46bfe24511431532

So, if your Windows program, such as quickhash, produces this same result for the 3 character input abc, then it is acceptable. Make sure you input is EXACTLY 3 characters. No spaces, tabs,line feeds or carriage returns.

It may take a few tries to get the right parameters - as you can see from the above sha_testvectors site there are at least 9 combinations.

2020-03-13
20:51 Reply: Welcome (artifact: 19cd77ca47 user: KevinYouren)

Richard,

thank you, much appreciated.

regs,

Kev