Re: [sqlite] VFS implementation for SoC
Thank you. It appears that the POSIX function fsync of the SoC has some problem syncing to the local spiffs, but the whole thing works for an external SD filesystem. Regards Arun On Fri, 12 Apr 2019 18:58:36 +0530 Richard Hipp wrote > On 4/12/19, Arun - Siara Logics (cc) wrote: > > Hi, > > > > Please ignore the earlier emails. The extended error code when trying > > CREATE TABLE is 1034 and when trying INSERT is 266. > > You can now visit https://www.sqlite.org/rescode.html and search for > "1034" and "266" to find out what those errors mean. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS implementation for SoC
Hi,
Please ignore the earlier emails. The extended error code when trying CREATE
TABLE is 1034 and when trying INSERT is 266.
I have given below the correct log generated during INSERT. Thanks.
Regards
Arun
Enter file name:
/spiffs/test.db
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
Opened database successfully
Welcome to SQLite console!!
---
Database file: /spiffs/test.db
1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file
Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES (shox96_0_2c('This wont get inserted'))
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Open
/spiffs/test.db-journal
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error
On Fri, 12 Apr 2019 17:47:21 +0530 Arun - Siara Logics (cc)
wrote
> I also tried INSERT on an existing database. This time the extended error
> is 266. I am giving below the log.
> Also, there are two warnings printed during open:
> (21) API call with invalid database connection pointer
> (21) misuse at line 152855 of [fb90e7189a]
>
> Regards
> Arun
>
> Enter file name:
> /spiffs/test.db
> (21) API call with invalid database connection pointer
> (21) misuse at line 152855 of [fb90e7189a]
> fn: FullPathNamefn:Fullpathname:Success
> fn: Open
> /spiffs/test.db
> fn:Open:Success
> fn: Read
> fn: FlushBuffer
> fn:FlushBuffer:Success
> Opened database successfully
>
> Welcome to SQLite console!!
> ---
>
> Database file: /spiffs/test.db
>
> 1. Open database
> 2. Execute SQL
> 3. Execute Multiple SQL
> 4. Close database
> 5. List folder contents
> 6. Rename file
> 7. Delete file
>
> Enter choice: 2
> Enter SQL (max 500 characters):
> INSERT INTO test VALUES ('This wont get inserted')
> fn: Access
> fn:Access:Success
> fn: FileSize
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn:FileSize:Success
> fn: Read
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn: Read
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn: Read
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn: Open
> /spiffs/test.db-journal
> Create mode
> fn:Open:Success
> fn: Write
> fn:Write:Success
> fn: Write
> fn:Write:Success
> fn: Write
> fn:Write:Success
> fn: Write
> fn:Write:Success
> fn: Write
> fn:Write:Success
> fn: Write
> fn: FlushBuffer
> fn: DirectWrite:
> fn:DirectWrite:Success
> fn:FlushBuffer:Success
> fn:Write:Success
> fn: Write
> fn:Write:Success
> fn: Read
> fn: FlushBuffer
> fn: DirectWrite:
> fn:DirectWrite:Success
> fn:FlushBuffer:Success
> fn: FileSize
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn:FileSize:Success
> fn: FileSize
> fn: FlushBuffer
> fn:FlushBuffer:Success
> fn:FileSize:Su
Re: [sqlite] VFS implementation for SoC
I also tried INSERT on an existing database. This time the extended error is
266. I am giving below the log.
Also, there are two warnings printed during open:
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]
Regards
Arun
Enter file name:
/spiffs/test.db
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
Opened database successfully
Welcome to SQLite console!!
---
Database file: /spiffs/test.db
1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file
Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES ('This wont get inserted')
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Open
/spiffs/test.db-journal
Create mode
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error
Time taken:310381 us
On Fri, 12 Apr 2019 17:30:14 +0530 Arun - Siara Logics (cc)
wrote
> Hi, Thank you for the suggestion. The sqlite3_extended_errcode() is 1034
> disk I/O error.
> Regards
> Arun
>
> On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp
> wrote ----
> > On 4/12/19, Arun - Siara Logics (cc) wrote:
> > > fn:DirectWrite:Success
> > > fn:FlushBuffer:Success
> > > fn:Sync:Success
> > > SQL error: disk I/O error
> > >
> > > At the end, there are two files on disk: vfs_test.db (0 bytes) and
> > > vfs_test.db-journal (512 bytes). There is no problem reading a
> database.
> > > But when CREATE or INSERT is involved, it gives disk I/O error.
> > >
> > > Any idea why it is throwing disk I/O error, inspite of the previous sync
> > > success? Any suggestions on how I could figure it out?
> >
> > Please tell us the sqlite3_extended_errcode(). Also, consider
> > enabling the error and warning log
> > (https://www.sqlite.org/errlog.html)
> >
> >
> > >
> > > Regards
> > > Arun
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS implementation for SoC
Hi, Thank you for the suggestion. The sqlite3_extended_errcode() is 1034 disk I/O error. Regards Arun On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp wrote > On 4/12/19, Arun - Siara Logics (cc) wrote: > > fn:DirectWrite:Success > > fn:FlushBuffer:Success > > fn:Sync:Success > > SQL error: disk I/O error > > > > At the end, there are two files on disk: vfs_test.db (0 bytes) and > > vfs_test.db-journal (512 bytes). There is no problem reading a database. > > But when CREATE or INSERT is involved, it gives disk I/O error. > > > > Any idea why it is throwing disk I/O error, inspite of the previous sync > > success? Any suggestions on how I could figure it out? > > Please tell us the sqlite3_extended_errcode(). Also, consider > enabling the error and warning log > (https://www.sqlite.org/errlog.html) > > > > > > Regards > > Arun > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VFS implementation for SoC
I am trying to implement the demo VFS provided at
https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c for a System on Chip
(ESP32) that has its own inbuilt filesystem (spiffs),
No changes have been made to the above code except, I had to add following code
in the demoOpen method, just before call to "open" POSIX method:
if ( flags_OPEN_READWRITE || flags_OPEN_MAIN_JOURNAL ) {
struct stat st;
memset(, 0, sizeof(struct stat));
int rc = stat( zName, );
Serial.println(zName);
if (rc == -1) {
int fd = open(zName, (O_CREAT | O_EXCL), S_IRUSR | S_IWUSR);
close(fd);
//oflags |= (O_CREAT | O_RDWR);
Serial.println("Create mode");
}
}
since it was not creating the file and giving error at Open.
I have given below the log of VFS function calls when calling sqlite3_open()
and sqlite3_exec() for simple table creation CREATE TABLE t1 (c1):
Enter file name:
/spiffs/test_vfs.db
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test_vfs.db
Create mode
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
Opened database successfully
Enter SQL (max 500 characters):
CREATE TABLE t1 (c1)
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Open
/spiffs/test_vfs.db-journal
Create mode
fn:Open:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: Sync
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Sync:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Write
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: disk I/O error
At the end, there are two files on disk: vfs_test.db (0 bytes) and
vfs_test.db-journal (512 bytes). There is no problem reading a database. But
when CREATE or INSERT is involved, it gives disk I/O error.
Any idea why it is throwing disk I/O error, inspite of the previous sync
success? Any suggestions on how I could figure it out?
Regards
Arun
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (Info) Shox96 Compression as SQLite UDF
Shox96 is a compression technique for Short Strings. It can achieve upto 65% compression. This technique is available for compressing text columns in SQLite as loadable extension in the repository https://github.com/siara-cc/Shox96_Sqlite_UDF. Output screenshot: https://github.com/siara-cc/Shox96_Sqlite_UDF/blob/master/output.png?raw=true To find out more about Shox96 click: https://github.com/siara-cc/Shox96 To find out how Shox96 works click: https://github.com/siara-cc/Shox96/blob/master/Shox96_Article_0_2_0.pdf?raw=true P.S.: The compressor and decompressor are built for short strings using less memory suitable for constrained environments such as Arduino Uno and ESP8266. So may not be as fast as Zip or GZip. Related projects: Sqlite3 Library for ESP32 https://github.com/siara-cc/esp32_arduino_sqlite3_lib Sqlite3 Library for ESP8266 https://github.com/siara-cc/esp_arduino_sqlite3_lib Sqlite3 Library for ESP-IDF https://github.com/siara-cc/esp32-idf-sqlite3 Storing compressed Shox96 text content in Arduino Flash Memory https://github.com/siara-cc/Shox96_Arduino_Progmem_lib Shox96 Compression Library for Arduino https://github.com/siara-cc/Shox96_Arduino_lib ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a table from inside a scalar function
Hi Keith, Thank you. I think I have all inputs to go ahead. The function I have in mind is deterministic, that is, it is expected to return the same value for a given input whether its called once or infinite number of times. I think I can get it work with SQLite. Regards Arun On Mon, 18 Feb 2019 22:50:55 +0530 Keith Medcalf wrote > > Note that really in the latter case the correct attribute is SLO_CHNG which > indicates that the function is fully deterministic WITHIN a statement > execution but may be volatile BETWEEN statement executions. > > The DETERMINISTIC attribute means the opposite of the default volatile. The > function will ALWAYS FROM NOW THROUGH THE END OF THE UNIVERSE always and > without exception return the same value when presented with the same > parameters. > > SQLite3 really only cares about DETERMINISM within the current statment > execution context. Unless of course you use the output in something that > persists outside of the "right bloody nowness" of a statement execution -- > such as using the function in an index, for example. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf > >Sent: Monday, 18 February, 2019 10:08 > >To: SQLite mailing list > >Subject: Re: [sqlite] Reading a table from inside a scalar function > > > > > >SQLite does not maintain state between VDBE executions ... each > >execution is a context onto itself. Nor is maintain state between > >separate VDBE executions executing concurrently. That is to say that > >the default volatile, SLO_CHNG or DETERMINISTIC attributes apply only > >within the execution context of a single statement and not between > >statements or serial re-executions of the same statement. > > > >Ie, if you execute the statement: > > > >select cosine(34); > > > >then the results you get is TWO SEPARATE DETERMINISTIC RESULTS. The > >fact that the function returned some particular value on some > >particular execution of the statement is not maintained between > >executions. It is entirely possible that the function "cosine" is > >DETERMINISTIC with each single execution context yet returns > >different results when the statement is executed twice. > > > >--- > >The fact that there's a Highway to Hell but only a Stairway to Heaven > >says a lot about anticipated traffic volume. > > > > > >>-Original Message- > >>From: sqlite-users [mailto:sqlite-users- > >>boun...@mailinglists.sqlite.org] On Behalf Of Arun - Siara Logics > >>(cc) > >>Sent: Monday, 18 February, 2019 09:23 > >>To: SQLite mailing list > >>Subject: Re: [sqlite] Reading a table from inside a scalar function > >>Importance: High > >> > >>Thanks Dominique, Thanks Simon, > >>Do you mean to say SQLite might keep function results across > >queries? > >>My design would be more complicated, but it is something like this: > >>If my function uses first part of a text column in the row involved > >>and if I make sure all modifications are always appended to the > >cell, > >>then the function will always return the same value. So it can be > >>deterministic and would work even if SQLite caches function results > >>across queries. Am I correct? > >> > >> On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin > >> wrote > >> > On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) > >> wrote: > >> > > >> > > Thank you, for the detailed advice, info and the pointer. Is > >>there a faster way to query the table using row id, that is, skip > >the > >>query parsing and planner? > >> > > >> > No. For fastest queries, use "WHERE rowid = ", and > >list > >>the columns you're interested in specifically. Do not use "SELECT > >>*". > >> > > >> > > I still need the page cache feature and allow for concurrent > >>modification of the row involved, while ensuring determinism by > >>designing so. I guess sqlite3_exec() would take care of this, but > >is > >>there a faster way? > >> > > >> > If you're allowing for the table row you're reading to be > >>modified, then how will
Re: [sqlite] Reading a table from inside a scalar function
Thanks Dominique, Thanks Simon, Do you mean to say SQLite might keep function results across queries? My design would be more complicated, but it is something like this: If my function uses first part of a text column in the row involved and if I make sure all modifications are always appended to the cell, then the function will always return the same value. So it can be deterministic and would work even if SQLite caches function results across queries. Am I correct? On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin wrote > On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) wrote: > > > Thank you, for the detailed advice, info and the pointer. Is there a > > faster way to query the table using row id, that is, skip the query > > parsing and planner? > > No. For fastest queries, use "WHERE rowid = ", and list the > columns you're interested in specifically. Do not use "SELECT *". > > > I still need the page cache feature and allow for concurrent modification > > of the row involved, while ensuring determinism by designing so. I guess > > sqlite3_exec() would take care of this, but is there a faster way? > > If you're allowing for the table row you're reading to be modified, then how > will your function be deterministic ? Would changing values in that row not > lead to a change in the value returned by your function ? If not, why are > you looking up the row ? > > Note that if you mark that function as deterministic, you cannot rely on > SQLite calling your function at all. SQLite may reason "I called that > function, with those arguments, a few instructions ago, so I already know > what the result will be.". > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a table from inside a scalar function
Thank you, for the detailed advice, info and the pointer. Is there a faster way to query the table using row id, that is, skip the query parsing and planner? I still need the page cache feature and allow for concurrent modification of the row involved, while ensuring determinism by designing so. I guess sqlite3_exec() would take care of this, but is there a faster way? On Mon, 18 Feb 2019 16:55:23 +0530 Richard Hipp wrote > On 2/18/19, Arun - Siara Logics (cc) wrote: > > Hi, > > > > This is for an experimental feasibility study. Is it possible to read > > specific row(s) from a table within same db (using row ids) from inside a > > User defined Deterministic scalar function (C API)? > > > > If known, kindly point me to an existing open source implementation. > > https://www.sqlite.org/src/file/ext/misc/eval.c > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a table from inside a scalar function
> Yes it is possible, but then your UDF is unlikely to be *Deterministic*. By design, I intend to make sure that for any given input the function always returns the same value. If the dependent rows are missing or change over time, then it would be an error condition. What would be the recommended method ? Is there an API to read a table row using RowID? Or should I traverse the BTree pages using my own code? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reading a table from inside a scalar function
Hi, This is for an experimental feasibility study. Is it possible to read specific row(s) from a table within same db (using row ids) from inside a User defined Deterministic scalar function (C API)? If known, kindly point me to an existing open source implementation. Thanks Arun ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
I think this is a good option. Looks like I can save from registering UDFs everytime a connection is created. Thanks!! On Thu, 14 Feb 2019 18:59:09 +0530 Keith Medcalf wrote > > See also SQLITE_EXTRA_INIT > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Arun - Siara Logics > >(cc) > >Sent: Thursday, 14 February, 2019 06:14 > >To: SQLite mailing list > >Subject: Re: [sqlite] Global UDF using sqlite3_create_function()? > >Importance: High > > > >Thank you!! It works like a charm. Before your reply I added > >FUNCTION macros before sqlite3InsertBuiltinFuncs(). That worked too. > >But I guess using sqlite3_auto_extension() is elegant and I will go > >that way. > > > > > > On Thu, 14 Feb 2019 17:16:01 +0530 Richard Hipp > > wrote > > > On 2/14/19, Arun - Siara Logics (cc) wrote: > > > > > > > > The sqlite3_create_functions require the 'db' parameter to > >define a user > > > > defined function according to the doc: > > > > "The first parameter is the database connection to which the SQL > >function is > > > > to be added. If an application uses more than one database > >connection then > > > > application-defined SQL functions must be added to each database > >connection > > > > separately." > > > > > > > > Is it possible to define a global scalar function so that it > >does not need > > > > to be registered for every function? That way, I can define > >such a function > > > > and ship it with the binary and it can be used with any database > >using the > > > > binary. > > > > > > Use the sqlite3_auto_extension() interface > > > (https://www.sqlite.org/c3ref/auto_extension.html) to cause your > >UDFs > > > to be registered automatically with all new database connections. > > > > > > -- > > > D. Richard Hipp > > > d...@sqlite.org > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > > > > > > > > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
Thank you!! It works like a charm. Before your reply I added FUNCTION macros before sqlite3InsertBuiltinFuncs(). That worked too. But I guess using sqlite3_auto_extension() is elegant and I will go that way. On Thu, 14 Feb 2019 17:16:01 +0530 Richard Hipp wrote > On 2/14/19, Arun - Siara Logics (cc) wrote: > > > > The sqlite3_create_functions require the 'db' parameter to define a user > > defined function according to the doc: > > "The first parameter is the database connection to which the SQL function > > is > > to be added. If an application uses more than one database connection then > > application-defined SQL functions must be added to each database connection > > separately." > > > > Is it possible to define a global scalar function so that it does not need > > to be registered for every function? That way, I can define such a > > function > > and ship it with the binary and it can be used with any database using the > > binary. > > Use the sqlite3_auto_extension() interface > (https://www.sqlite.org/c3ref/auto_extension.html) to cause your UDFs > to be registered automatically with all new database connections. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Global UDF using sqlite3_create_function()?
Hi, The sqlite3_create_functions require the 'db' parameter to define a user defined function according to the doc: "The first parameter is the database connection to which the SQL function is to be added. If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately." Is it possible to define a global scalar function so that it does not need to be registered for every function? That way, I can define such a function and ship it with the binary and it can be used with any database using the binary. I see sqlite3InsertBuiltinFuncs() in sqlite3.c. Can it be used for this purpose? Regards Arun ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
I have modified the database and updated the repository at https://github.com/siara-cc/employee_db. Given below is the revised script. Regards Arun CREATE TABLE employees ( emp_id INTEGER NOT NULL, birth_date DATE NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, gender CHAR NOT NULL check(gender="M" or gender="F"), hire_date DATE NOT NULL, PRIMARY KEY (emp_id) ); CREATE TABLE departments ( dept_id INTEGER NOT NULL, dept_name TEXT NOT NULL, PRIMARY KEY (dept_id), CONSTRAINT dept_name_unique UNIQUE (dept_name) ); CREATE TABLE dept_manager ( dept_id INTEGER NOT NULL, emp_id INTEGER NOT NULL, from_dateDATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id)ON DELETE CASCADE, FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, dept_id) ); CREATE TABLE dept_emp ( emp_id INTEGER NOT NULL, dept_id INTEGER NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, dept_id) ); CREATE TABLE titles ( emp_id INTEGER NOT NULL, title TEXT NOT NULL, from_date DATE NOT NULL, to_date DATE, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, PRIMARY KEY (emp_id,title, from_date) ); CREATE TABLE salaries ( emp_id INTEGER NOT NULL, salary NUMBER NOT NULL, from_date DATENOT NULL, to_date DATENOT NULL, FOREIGN KEY (emp_id) REFERENCES employees (emp_id) ON DELETE CASCADE, PRIMARY KEY (emp_id, from_date) ); > On Thu, 20 Dec 2018 21:13:47 +0530 Chris Locke > wrote > > > Just because something doesn't have to be calculated, means that it has > > to be stored as text. > > > > Sorry - forgot a 'doesn't'. > > Just because something doesn't have to be calculated, doesn't mean that > it > > has to be stored as text. > > > > On Thu, Dec 20, 2018 at 3:42 PM Chris Locke > > wrote: > > > > > Just because something doesn't have to be calculated, means that it has > to > > > be stored as text. > > > Its usually recommended to set the column affinity to the type of data > > > you're storing. If you're storing a number (and a model number is a > > > numeric number) then it should be stored in a numeric field. If your > model > > > number has punctuation, then yes, a text field is required. > > > Its up to the application (although some would also argue the database) > to > > > validate data input, ie, ensure numeric data was inputted into a > numeric > > > field. > > > > > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden > > > > wrote: > > > > > >> On Wed, 19 Dec 2018 10:55:11 + > > >> Chris Locke wrote: > > >> > > >> > Fields with '_no' are read as 'number' and so should be a number. > > >> > OK, that doesn't always work for 'telephone_no' (they usually start > > >> > with a 0 > > >> > > >> Lots of numbers are labels that aren't meant to be calculated on. > Item > > >> number, part number, model number, serial number, order number. > > >> Anything that needs to be distinguished and isn't worth naming. > > >> > > >> It's never a good idea to store such numbers as numerical types. > > >> There's always a potential loss of information, be it the leading > zero > > >> or embedded '-' or multiple '.' characters. Unless the "number" is a > > >> quantity, for compuational purposes it's text. > > >> > > >> --jkl > > >> > > >> ___ > > >> sqlite-users mailing list > > >> sqlite-users@mailinglists.sqlite.org > > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >> > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
I simply did a bzip2 of the db file, uploaded it using "git add" and "git commit". I just downloaded it now from "View Raw" and the shasum of the downloaded file matches with what I uploaded. shasum -a 256 ~/Downloads/employees.db.bz2 dd0b87d339494d2ee2f21a66a3b253f58c1a5517e7705427382c0ff8c5a6ada1 /Users/arun/Downloads/employees.db.bz2 and bzip2 -t does not report any errors. I guess you probably got the link using "Copy link" and used a wget utility or something. Not sure why it does not work for you (Jungle Boogle). Regards Arun On Mon, 24 Dec 2018 05:45:44 +0530 Larry Brasfield wrote > Jungle Boogie wrote: > ➢ Anyone else have issues decompressing the file? $ bzip2 -d > employees.db.bz2 bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 > employees.db.bz2 bunzip2: employees.db.bz2 is not a bzip2 file. $ file > employees.db.bz2 employees.db.bz2: Non-ISO extended-ASCII HTML document text > $ sha256 employees.db.bz2 SHA256 (employees.db.bz2) = > 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 > > I browsed to the web page, link to which you quoted. At that page (on > GitHub) I clicked the “Clone or download” button, then clicked the “Download > ZIP” option, whereupon a .zip file could be downloaded. Within that .zip > archive, in a subdirectory, was a file which appeared as follows to the > ‘file’ utility: > > file employees.db > employees.db: SQLite 3.x database > > Better yet, sqlite3 v22 thinks it is a valid database. > > I don’t know what you did, but the evidence suggests you simply grabbed > whatever the server dished up under the http(s) protocol for the given link. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Hi Chris, James, Shawn, Thanks for the suggestions - I think they are quite relevant. I will modify and update soon. I kept the page size as 512 without rowid and dropped foreign keys so I could use it to demo SQLite on a low memory SoC ESP8266 (see https://github.com/siara-cc/esp_arduino_sqlite3_lib/). Since this db would be useful for a larger audience, I will keep separate copies. Regards Arun On Thu, 20 Dec 2018 21:13:47 +0530 Chris Locke wrote > > Just because something doesn't have to be calculated, means that it has > to be stored as text. > > Sorry - forgot a 'doesn't'. > Just because something doesn't have to be calculated, doesn't mean that it > has to be stored as text. > > On Thu, Dec 20, 2018 at 3:42 PM Chris Locke > wrote: > > > Just because something doesn't have to be calculated, means that it has to > > be stored as text. > > Its usually recommended to set the column affinity to the type of data > > you're storing. If you're storing a number (and a model number is a > > numeric number) then it should be stored in a numeric field. If your model > > number has punctuation, then yes, a text field is required. > > Its up to the application (although some would also argue the database) to > > validate data input, ie, ensure numeric data was inputted into a numeric > > field. > > > > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden > > wrote: > > > >> On Wed, 19 Dec 2018 10:55:11 + > >> Chris Locke wrote: > >> > >> > Fields with '_no' are read as 'number' and so should be a number. > >> > OK, that doesn't always work for 'telephone_no' (they usually start > >> > with a 0 > >> > >> Lots of numbers are labels that aren't meant to be calculated on. Item > >> number, part number, model number, serial number, order number. > >> Anything that needs to be distinguished and isn't worth naming. > >> > >> It's never a good idea to store such numbers as numerical types. > >> There's always a potential loss of information, be it the leading zero > >> or embedded '-' or multiple '.' characters. Unless the "number" is a > >> quantity, for compuational purposes it's text. > >> > >> --jkl > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
Hi Chris, I don't own the MySQL side of the db, but its easy for me to change anything on Sqlite side. To me the data looks decent for testing and creating applications for demo or learning. I am giving below the script and I will incorporate any other suggestions you may come up with: CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATENOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender CHAR(1) NOT NULL, hire_date DATENOT NULL, PRIMARY KEY (emp_no) ) without rowid; CREATE TABLE departments ( dept_no CHAR(4) NOT NULL, dept_name VARCHAR(40) NOT NULL, PRIMARY KEY (dept_no) ) without rowid; CREATE TABLE dept_manager ( dept_no CHAR(4) NOT NULL, emp_no INT NOT NULL, from_dateDATENOT NULL, to_date DATENOT NULL, PRIMARY KEY (emp_no, dept_no) ) without rowid; CREATE TABLE dept_emp ( emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL, from_date DATENOT NULL, to_date DATENOT NULL, PRIMARY KEY (emp_no,dept_no) ) without rowid; CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATENOT NULL, to_date DATE, PRIMARY KEY (emp_no,title, from_date) ) without rowid; CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATENOT NULL, to_date DATENOT NULL, PRIMARY KEY (emp_no, from_date) ) without rowid; CREATE INDEX emp_first_name on employees (first_name); CREATE INDEX emp_last_name on employees (last_name); Regards Arun On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke wrote > The scheme (for me) is like nails on a chalkboard. 'dept_no' but defined > as a 'CHAR', then 'emp_no' as an INT. > Fields with '_no' are read as 'number' and so should be a number. OK, that > doesn't always work for 'telephone_no' (they usually start with a 0 ... > well, they do in the UK where I am...) > But I digress.. > > > Chris > > > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) > wrote: > > > This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3 > > db file ported from mysql test_db found at > > https://github.com/datacharmer/test_db. It can be used to test your > > applications and database servers. To use this project, download > > employees.db.bz2, unzip and open using sqlite3 command line tool. > > > > The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens > > Corporate Research. The data is in XML format. > > http://timecenter.cs.aau.dk/software.htm > > > > Giuseppe Maxia made the relational schema and Patrick Crews exported the > > data in relational format. > > > > The database contains about 300,000 employee records with 2.8 million > > salary entries. The export data is 167 MB, which is not huge, but heavy > > enough to be non-trivial for testing. > > > > A picture of the schema can be found at: > > https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true > > > > Regards > > Arun - Siara Logics (cc) > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sample Employee database ported to SQLite from MySQL
This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3 db file ported from mysql test_db found at https://github.com/datacharmer/test_db. It can be used to test your applications and database servers. To use this project, download employees.db.bz2, unzip and open using sqlite3 command line tool. The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens Corporate Research. The data is in XML format. http://timecenter.cs.aau.dk/software.htm Giuseppe Maxia made the relational schema and Patrick Crews exported the data in relational format. The database contains about 300,000 employee records with 2.8 million salary entries. The export data is 167 MB, which is not huge, but heavy enough to be non-trivial for testing. A picture of the schema can be found at: https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true Regards Arun - Siara Logics (cc) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)
Without going into details of what your structure is, I suggest you look into "without rowid" option when creating the table. It reduces the overhead drastically. On Thu, 29 Nov 2018 18:59:26 +0530 Dominique Devienne wrote > On Wed, Nov 28, 2018 at 6:03 PM AJ M wrote: > > > [...] The data comes out to 10 billion rows of an 8 byte signed integer > > (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes > > 8 hours by > > itself. [...] query speed is fine as-is. [...] > > > > Hi AJ. Your message is quite intriguing, because you make it sound like > your row > is composed of a single 8-byte signed integer. Even multiplied by 1e10 > rows, that's only > 80GB ideally, so 200-300GB pre-indexing means a large 3x overhead in the > DB, which doesn't sound right. > > Also, a row composed of a single integer column is not that interesting at > first sight, and a SQL > DB does not seem appropriate for such a simple data "structure". What kind > of query would > you be running on that one signed integer? Surely you have other columns in > your DB? > What's the natural or primary key of those rows? > > So far you got answers on your specific question, but if we backed up a > little and got more context > on what you are trying to achieve at a higher level, your exact table(s) > structures and indexes, > and the kind of queries you are running? I'm sure you'd get a different > perspective on your > problem, which may even not be related to SQLite at all I kinda suspect. My > $0.02. --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite Page Explorer
Hi, Try my free and open source application to learn internal structure of SQLite databases. Explore the organisation of various objects such as schema, tables and indices. View hidden pages and data deleted. Available for Mac, WIndows and Linux. Applications: - Viewing internal organisation of Sqlite databases for software development, ethical hacking and troubleshooting - Studying the format of Sqlite databases for academic purposes - Forensic investigators may use it to view data from deleted pages GitHub page: https://github.com/siara-cc/sqlite3_page_explorer Mac App Store URL: https://itunes.apple.com/app/id1444019689?fbclid=IwAR1af-OcB0a4zqWG_BKsKvRAHSwKO-Cwpuy9sOglOpQ30q92GJoxH9I6TEo Regards Arun ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
