|
Post by pierre on Apr 28, 2020 17:38:52 GMT -5
Oh, thank you Chris ! You are working incredibly fast !
It is half past midnight here. I 'll check it out tomorrow.
pierre
|
|
|
Post by Chris Iverson on Apr 29, 2020 1:27:51 GMT -5
It helps that most of the work is already done, and I'm mostly just poking at it until it compiles.
|
|
|
Post by ppro06 on Apr 29, 2020 4:33:32 GMT -5
Hello Everyone, Thank you all for your posts on this subject, its the gift that keeps on giving and I am learning a lot. I have tried the dump functions and they have worked fine in the wrapper. Following the fileio extension, I have been trying out the csv functions as an addition to fileio. print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "csv") print "SQL3CreateModule() - ";SQL3CreateModule(hDB, "file.csv") print "SQL3DeclarevTab()- "; SQL3DeclarevTab(hDB, "CREATE VIRTUAL TABLE temp.t1 USING csv(filename='"+ImporTable$+"');")
From my understanding of the sqlite documentation on virtual tables, those are the functions that have to be invoked to create virtual csv tables to import data. Will you take a look and point me in the right direction? Function SQL3CreateModule(hDB, ImporTable$)
CallDLL #sqlite3, "sqlite3_create_module",_ hDB as ulong,_ "csv" as ptr,_ csvtabCreate as ptr,_ ImporTable$ as ptr,_ SQL3CreateModule as long End Function
Function SQL3DeclarevTab(hDB, vTabschema$) CallDLL #sqlite3, "sqlite3_declare_vtab",_ hDB as ulong,_ vTabschema$+chr$(0) as ptr,_ SQL3DeclarevTab as long
End Function Attachments:csv.dll (85.78 KB)
|
|
|
Post by pierre on Apr 29, 2020 4:36:08 GMT -5
dbdump tested on a different database table with 8 columns and more than 900 records.
result successful.
pierre
|
|
|
Post by metro on Apr 29, 2020 4:45:32 GMT -5
dbdump tested on a different database table with 8 columns and more than 900 records.
result successful.
pierre
pierre, any chance of some of your code for us mere mortals to digest
thanks in advance
|
|
|
Post by ppro06 on Apr 29, 2020 6:55:34 GMT -5
pierre, any chance of some of your code for us mere mortals to digest
thanks in advance
Hello metro, This is what Chr is has helped us with so far. The dll attachments are in Chris' posts, if you check the thread along with portions of this code. gosub [SQL3PrepGlobals]
print "SQL3Init() - ";SQL3Init()
hDB = 0
print "SQL3Open() - ";SQL3Open("test.db", hDB)
print "SQL3EnableLoadExtension() - ";SQL3EnableLoadExtension(hDB, 1)
print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "fileio")
ret = SQL3ExecSingle(hDB, "CREATE TABLE test(a INT, b TEXT);")
print "SQL3ExecSingle() - ";ret
if ret = SQLITE.DONE then
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13, READFILE('testfile.txt'));")
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(12,'blah');")
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "INSERT INTO test(a, b) VALUES(13,'stuff');")
else
print SQL3ErrStr$(ret)
end if
'Attempting prepared statement with bound parameters
SQL$ = "INSERT INTO test(a, b) VALUES(?, ?);"
hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement)
if ret <> SQLITE.OK then
print "Bound prepared statement failed - ";ret;" - ";SQL3ErrStr$(ret)
goto [skipBind]
end if
ret = SQL3BindInt(hStatement, 1, 153)
if ret <> SQLITE.OK then
print "Bind int failed - ";ret;" - ";SQL3ErrStr$(ret)
goto [finalSkipBind]
end if
ret = SQL3BindText(hStatement, 2, "someoasidjrlkawergj")
if ret <> SQLITE.OK then
print "Bind text failed - ";ret;" - ";SQL3ErrStr$(ret)
goto [finalSkipBind]
end if
ret = SQL3Step(hStatement)
if ret <> SQLITE.DONE then
Print "Executing bound statement failed - ";ret;" - ";SQL3ErrStr$(ret)
end if
[finalSkipBind]
ret = SQL3Finalize(hStatement)
[skipBind]
SQL$ = "SELECT * FROM test;"
hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement)
if ret <> SQLLITE.OK then
Print "SQL3Prepare() failed - ";ret;" - ";SQL3ErrStr$(ret)
goto [skipSelect]
end if
print
print
do
ret = SQL3Step(hStatement)
if ret <> SQLITE.DONE and ret <> SQLITE.ROW then
print "SQL3Step() error - ";ret;" - ";SQL3ErrStr$(ret)
exit do
end if
numCols = SQL3DataCount(hStatement)
if numCols <> 0 then
for x = 0 to numCols - 1
print SQL3ColumnText$(hStatement, x),
next x
print
end if
loop while ret <> SQLITE.DONE
print
print
[DBDump]
schema$ = "main"
table$ = "test"
print "SQL3DBDump() - ";SQL3DBDump("testdb-dump.txt", hDB, schema$, table$)
print "SQL3DBDump() - ";SQL3DBDump("testdb-dump.txt", hDB, "", "")
print "SQL3Finalize() - ";SQL3Finalize(hStatement)
[skipSelect]
print "SQL3ExecSingle() - ";SQL3ExecSingle(hDB, "DROP TABLE test;")
print "SQL3Close() - ";SQL3Close(hDB)
print "SQL3Shutdown() - ";SQL3Shutdown()
end
'This function is provided as a shorthand for statements that can be executed
'in a single step, and don't return anything(CREATE, DROP, INSERT, etc)
Function SQL3ExecSingle(hDB, SQL$)
'Prepare/compile the statement
hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement)
if ret <> SQLITE.OK then
'Statement preparation failed. Error code may have more info.
goto [endFunc]
end if
'Execute the statement.
ret = SQL3Step(hStatement)
[endFunc]
dummy = SQL3Finalize(hStatement)
SQL3ExecSingle = ret
End Function
Function fputs(stuff, hFile)
if stuff = 0 then exit function
stuff$ = winstring(stuff)
print #dbdumpfile, stuff$;
End Function
Function SQL3DBDump(file$, hDB, schema$, table$)
callback FPUTSCB, fputs(ulong, long), void
open file$ for output as #dbdumpfile
open "dbdump" for dll as #dbdump
calldll #dbdump, "sqlite3_db_dump",_
hDB as ulong,_
schema$ as ptr,_
table$ as ptr,_
FPUTSCB as ulong,_
0 as long,_
SQL3DBDump as long
close #dbdump
close #dbdumpfile
End Function
Function SQL3Init()
open "sqlite3" for DLL as #sqlite3
CallDLL #sqlite3, "sqlite3_initialize",_
SQL3Init as long
End Function
Function SQL3EnableLoadExtension(hDB, onoff)
SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION = 1005
CallDLL #sqlite3, "sqlite3_db_config",_
hDB as ulong,_
SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION as long,_
onoff as long,_
SQL3EnableLoadExtension as long
End Function
Function SQL3LoadExtension(hDB, extFileName$)
CallDLL #sqlite3, "sqlite3_load_extension",_
hDB as ulong,_
extFileName$ as ptr,_
_NULL as long,_
_NULL as long,_
SQL3LoadExtension as long
End Function
Function SQL3Shutdown()
CallDLL #sqlite3, "sqlite3_shutdown",_
SQL3Shutdown as long
close #sqlite3
End Function
Function SQL3Open(fileName$, byref hDB)
struct a, b as ulong
CallDLL #sqlite3, "sqlite3_open",_
fileName$ as ptr,_
a as struct,_
SQL3Open as long
hDB = a.b.struct
End Function
Function SQL3Close(hDB)
CallDLL #sqlite3, "sqlite3_close",_
hDB as ulong,_
SQL3Close as long
End Function
Function SQL3Prepare(hDB, SQL$, byref hStatement)
sqlLen = len(SQL$)
struct a, b as ulong
CallDLL #sqlite3, "sqlite3_prepare_v2",_
hDB as ulong,_
SQL$ as ptr,_
sqlLen as long,_
a as struct,_
_NULL as long,_
SQL3Prepare as long
hStatement = a.b.struct
End Function
Function SQL3Finalize(hStatement)
CallDLL #sqlite3, "sqlite3_finalize",_
hStatement as ulong,_
SQL3Finalize as long
End Function
Function SQL3Step(hStatement)
CallDLL #sqlite3, "sqlite3_step",_
hStatement as ulong,_
SQL3Step as long
End Function
Function SQL3Reset(hStatement)
CallDLL #sqlite3, "sqlite3_reset",_
hStatement as ulong,_
SQL3Reset as long
End Function
Function SQL3ClearBindings(hStatement)
CallDLL #sqlite3, "sqlite3_clear_bindings",_
hStatement as ulong,_
SQL3ClearBindings as long
End Function
Function SQL3ErrStr$(errorCode)
CallDLL #sqlite3, "sqlite3_errstr",_
errorCode as long,_
ret as ulong
SQL3ErrStr$ = winstring(ret)
End Function
Function SQL3ColumnCount(hStatement)
CallDLL #sqlite3, "sqlite3_column_count",_
hStatement as ulong,_
SQL3ColumnCount as long
End Function
Function SQL3DataCount(hStatement)
CallDLL #sqlite3, "sqlite3_data_count",_
hStatement as ulong,_
SQL3DataCount as long
End Function
Function SQL3ColumnText$(hStatement, column)
CallDLL #sqlite3, "sqlite3_column_text",_
hStatement as ulong,_
column as long,_
ret as ulong
SQL3ColumnText$ = winstring(ret)
End Function
Function SQL3ColumnBytes(hStatement, column)
CallDLL #sqlite3, "sqlite3_column_bytes",_
hStatement as ulong,_
column as long,_
SQL3ColumnBytes as long
End Function
Function SQL3BindInt(hStatement, param, value)
CallDLL #sqlite3, "sqlite3_bind_int",_
hStatement as ulong,_
param as long,_
value as long,_
SQL3BindInt as long
End Function
Function SQL3BindText(hStatement, param, text$)
textLen = len(text$)
CallDLL #sqlite3, "sqlite3_bind_text",_
hStatement as ulong,_
param as long,_
text$ as ptr,_
textLen as long,_
SQLITE.STATIC as long,_
SQL3BindText as long
End Function
[SQL3PrepGlobals]
Global SQLITE.OK : SQLITE.OK = 0
Global SQLITE.ERROR : SQLITE.ERROR = 1
Global SQLITE.BUSY : SQLITE.BUSY = 5
Global SQLITE.LOCKED : SQLITE.LOCKED = 6
Global SQLITE.MISMATCH : SQLITE.MISMATCH = 20
Global SQLITE.MISUSE : SQLITE.MISUSE = 21
Global SQLITE.ROW : SQLITE.ROW = 100
Global SQLITE.DONE : SQLITE.DONE = 101
Global SQLITE.INTEGER : SQLITE.INTEGER = 1
Global SQLITE.FLOAT : SQLITE.FLOAT = 2
Global SQLITE.BLOB : SQLITE.BLOB = 3
Global SQLITE.NULL : SQLITE.NULL = 4
Global SQLITE.TEXT : SQLITE.TEXT = 5
Global SQLITE.STATIC : SQLITE.STATIC = 0
Global SQLITE.TRANSIENT : SQLITE.TRANSIENT = -1
Global SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION : SQLITE.DBCONFIG.ENABLE.LOAD.EXTENSION = 1005
return
|
|
|
Post by Chris Iverson on Apr 29, 2020 14:20:20 GMT -5
Hello Everyone, Thank you all for your posts on this subject, its the gift that keeps on giving and I am learning a lot. I have tried the dump functions and they have worked fine in the wrapper. Following the fileio extension, I have been trying out the csv functions as an addition to fileio. print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "csv") print "SQL3CreateModule() - ";SQL3CreateModule(hDB, "file.csv") print "SQL3DeclarevTab()- "; SQL3DeclarevTab(hDB, "CREATE VIRTUAL TABLE temp.t1 USING csv(filename='"+ImporTable$+"');")
From my understanding of the sqlite documentation on virtual tables, those are the functions that have to be invoked to create virtual csv tables to import data. Will you take a look and point me in the right direction? Function SQL3CreateModule(hDB, ImporTable$)
CallDLL #sqlite3, "sqlite3_create_module",_ hDB as ulong,_ "csv" as ptr,_ csvtabCreate as ptr,_ ImporTable$ as ptr,_ SQL3CreateModule as long End Function
Function SQL3DeclarevTab(hDB, vTabschema$) CallDLL #sqlite3, "sqlite3_declare_vtab",_ hDB as ulong,_ vTabschema$+chr$(0) as ptr,_ SQL3DeclarevTab as long
End Function You've almost got that correct. While those functions are what needs to be called by code implementing a custom table format, LB's not the one doing that. The CSV DLL is the one implementing the table format, and it calls those functions(like sqlite3_craete_module) for you, in the sqlite3_csv_init() function. So, the only thing that needs to be called is the CSV DLL's init function. So, we open up the DLL in LB, and call it, right? Nope. SQLite3's extension loader will take care of that for you. Instead, we call the SQLite that's already open, and tell it to load an extension for us(in this case, "csv"). SQLite itself will open the CSV DLL, and call the init() function. All of that is done for you by the extension loader, like it is for the fileio DLL. First, we make sure that extension loading is enabled(this is already done in my sample code): print "SQL3EnableLoadExtension() - ";SQL3EnableLoadExtension(hDB, 1) And then we ask SQLite to load the CSV extension: print "SQL3LoadExtension() - ";SQL3LoadExtension(hDB, "csv") Now, if you try that, you'll notice... that it comes back with a 1, SQLITE_ERROR, instead of SQLITE_OK. I think there's something missing in the DLL you compiled, but I'm not sure. An analysis tool shows a section is missing. I recompiled it myself(attached), and that version works. Once the extension is loaded, you can use SQL to load data from CSVs. Using this file as test(named test.csv): "stuff",12,235 "more",45,75 "another",934,24 And this query to import it: ret = SQL3ExecSingle(hDB, "CREATE VIRTUAL TABLE temp.csv USING csv(filename='test.csv');") print "SQL3ExecSingle() - ";ret I can then use the following query to read the information from the CSV file: SQL$ = "SELECT * FROM csv;" print "SQL Query - ";SQL$ hStatement = 0
ret = SQL3Prepare(hDB, SQL$, hStatement) The existing code for reading returned data then results in the following full program output: SQL3Init() - 0 SQL3Open() - 0 SQL3EnableLoadExtension() - 0 SQL3LoadExtension() - 0 SQL3LoadExtension() - 0 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 SQL3ExecSingle() - 101 stuff$ = someoas idjrlkawergj SQL Query - SELECT * FROM csv;
c0 c1 c2 stuff 12 235 more 45 75 another 934 24
SQL3Finalize() - 0 SQL3DBDump() - 0 SQL3ExecSingle() - 101 SQL3Close() - 0 SQL3Shutdown() - 0 As stated in the documentation for the DLL, the column names default to cX, where X is a zero-indexed column number. ** This file contains the implementation of an SQLite virtual table for ** reading CSV files. ** ** Usage: ** ** .load ./csv ** CREATE VIRTUAL TABLE temp.csv USING csv(filename=FILENAME); ** SELECT * FROM csv; ** ** The columns are named "c1", "c2", "c3", ... by default. Or the ** application can define its own CREATE TABLE statement using the ** schema= parameter, like this: ** ** CREATE VIRTUAL TABLE temp.csv2 USING csv( ** filename = "../http.log", ** schema = "CREATE TABLE x(date,ipaddr,url,referrer,userAgent)" ** ); ** ** Instead of specifying a file, the text of the CSV can be loaded using ** the data= parameter. ** ** If the columns=N parameter is supplied, then the CSV file is assumed to have ** N columns. If both the columns= and schema= parameters are omitted, then ** the number and names of the columns is determined by the first line of ** the CSV input. To name the columns, you can do this instead(broken onto multiple lines for readability): ret = SQL3ExecSingle(hDB, "CREATE VIRTUAL TABLE temp.csv USING csv(filename='test.csv'," + _ "schema = 'CREATE TABLE x(name,num1,num2)');") This will result in the following table output: name num1 num2 stuff 12 235 more 45 75 another 934 24 EDIT: Well, ok, for some reason, it's not letting me attach csv.dll, so I'll link it here: chrisiverson.net/sqlite3/csv.dll
|
|
|
Post by ppro06 on Apr 29, 2020 15:11:32 GMT -5
Thank you, that makes things so much easier.
|
|
|
Post by pierre on Apr 29, 2020 16:55:08 GMT -5
metro said:
I also belong to the 'mere mortals', compared to the utmost expertise Chris shows us...... You can use the testdb-dump on any database, it works very well. I have a small 'Blobtest' program. nothing really exciting . After running the program, you can compare the original png file and the one retrieved from the database. They are identical. It works for all kind of binary files bmp, png, jpg, pdf etc.
I assume you have already the 'test.txt' (from Chris) and the 'smiley.png' everyone probably has.
|
|
|
Post by metro on Apr 30, 2020 3:28:51 GMT -5
Thanks ppro06 and pierre, I'll get some time tonight to digest what you have both offered a BIG thanks to Chris for your ongoing commitment to this and LibertyBasic
|
|
|
Post by pierre on Apr 30, 2020 16:19:52 GMT -5
tested the 'csv' import extension. only 4 lines of code: - enable the extension - load the extension - create the virtual table and in the same process import the data from a file - copy the data to an existing table of the main database That is very interesting, but........we can only read the virtual table, not write to it and not export it. It seems that the SQLite C interface has no extension for exporting data to a csv file, while such a command does exist in the SQLite Command Line Shell. . That can of course be done - and has already be done - in LB, but with much more programming involved.
pierre
|
|
|
Post by Chris Iverson on Apr 30, 2020 17:23:34 GMT -5
Checking over the code, the command line shell does it manually, running the query and converting each returned result row into a CSV row, then outputting the row to a file. That's part of the command shell, not the SQLite interface(or an extension interface).
(For those looking at the C code, the relevant code is in SQLite source/src/shell.c.in. When running a SQL query, the function shell_callback() is called for each row of the result data. What that function does depends on the output mode it's in. If it's in CSV mode, it will call the output_csv() function for each column of data on each row. This function is what outputs it to the standard output handle.)
I imagine the same could be done in LB fairly simply.
|
|
|
Post by metro on May 1, 2020 2:27:35 GMT -5
You can use the testdb-dump on any database, it works very well.I have a small 'Blobtest' program. nothing really exciting . After running the program, you can compare the original png file and the one retrieved from the database. They are identical. It works for all kind of binary files bmp, png, jpg, pdf etc. I assume you have already the 'test.txt' (from Chris) and the 'smiley.png' everyone probably has.
Thanks pierre, I inserted a couple of PDF's and retrieved them. Now to code something useful for document storage with an index. To quote my 2 year old grandson................. maze(th)ing
|
|
|
Post by pierre on May 1, 2020 14:30:22 GMT -5
Checking over the code, the command line shell does it manually, running the query and converting each returned result row into a CSV row, then outputting the row to a file. That's part of the command shell, not the SQLite interface(or an extension interface). (For those looking at the C code, the relevant code is in SQLite source/src/shell.c.in. When running a SQL query, the function shell_callback() is called for each row of the result data. What that function does depends on the output mode it's in. If it's in CSV mode, it will call the output_csv() function for each column of data on each row. This function is what outputs it to the standard output handle.) I imagine the same could be done in LB fairly simply. Like this ? database inserts : 12 blah 13 stuff 419 test data 47 to 'quote' or 'not' to 'quote' input.csv loaded and copied into the main database: 19,Liberty BASIC 112,Power BASIC 661,PureBasic 24,FreeBASIC 77,blah blah generated output.csv : 12,blah 13,stuff 419,test data 47,to 'quote' or 'not' to 'quote' 19,Liberty BASIC 112,Power BASIC 661,PureBasic 24,FreeBASIC 77,blah blah
the test.txt is the file you already have, you may create the input.csv as stated above, or with the data you prefer.
pierre
|
|
|
Post by pierre on May 2, 2020 6:47:40 GMT -5
Using the READFILE command (see Chris's extension functions) may cause problems for simple text files. READFILE stores the data as BLOB. This is only visible in a db dump, the SELECT command will always show the plain text. Example: Enter 3 records: 61 test data ( without binding ) 47 test data ( with text binding ) 419 test data (= the file "test.txt" read by READFILE ) A search for the data 'test data' , without binding or with text binding will only find the first two values. A search for the same data with a (hypothetical) BLOB binding will only find the third value. Only a search with the LIKE clause (without the % sign) will find the 3 values. As a search for a BLOB value doesn't make much sense, this circumstance could easily be overseen, and a search could be unsuccessful or at least incomplete. Here is the database content:
and this is the database dump:
So, I would use READFILE for binary files only.
pierre
|
|