1. LibCurl 4 and SQLite 3 wrappers

LibCurl 4 and SQLite 3 wrappers for OpenEuphoria 4 as standalone libraries (all dependencies to other EU4 Standard libraries removed).

Jean-Marc

new topic     » topic index » view message » categorize

2. Re: LibCurl 4 and SQLite 3 wrappers

When using SQLite to store Euphoria objects, do I need to sprint() the data before storing it, or does the wrapper handle the conversion of Euphoria objects back and forth?

new topic     » goto parent     » topic index » view message » categorize

3. Re: LibCurl 4 and SQLite 3 wrappers

euphoric said...

When using SQLite to store Euphoria objects, do I need to sprint() the data before storing it, or does the wrapper handle the conversion of Euphoria objects back and forth?

It looks like sqlite_bind_blob() will store objects using compress() and sqlite_column_blob() will fetch them with decompress().

public procedure sqlite_bind_blob(atom db, atom stmt, integer param_index, object val) 
  integer ret 
  sequence val_string 
  atom val_addr    
     
  val_string = compress(val) 
  val_addr = allocate (length (val_string)) 
  poke (val_addr, val_string) 
       
  ret = c_func(xsqlite3_bind_blob,{stmt, param_index, val_addr, 
                  length (val_string), SQLITE_TRANSIENT}) 
   
  sqlite_last_err_no = ret 
  sqlite_last_err_desc = "" 
  if ret != SQLITE_OK then 
    sqlite_last_err_desc = sqlite_errmsg (db) 
    fatal (db, "sqlite_bind_blob()") 
  end if    
end procedure 
public function sqlite_column_blob(atom db, atom stmt, integer column_num) 
  atom addr, nBytes 
  sequence val_string 
       
  addr = c_func(xsqlite3_column_blob, {stmt, column_num - 1}) 
  if addr then 
    nBytes = c_func(xsqlite3_column_bytes, {stmt, column_num - 1}) 
    val_string = peek ({addr, nBytes}) 
    return decompress (val_string) 
  else 
    fatal(db, "sqlite_column_blob(): can't get data") 
    return -1 
  end if 
end function 

Curious, what benefit is there to storing serialized objects in the database? Wouldn't you be better served using text and number fields so you can query off them?

-Greg

new topic     » goto parent     » topic index » view message » categorize

4. Re: LibCurl 4 and SQLite 3 wrappers

ADDED: I'm on Windows 8, Euphoria 4.1 64-bit.

This doesn't seem right.

I'm getting this output running the test_sqlite3.ex.

deviceList: 
{ 
  { 
    "", 
    "OneOS", 
    "", 
    "Last_update", 
    "", 
    "Status", 
    "", 
    "Last_check", 
    "", 
    "Config", 
    "" 
  }, 
  { 
    "Orange Labs", 
    "", 
    "192.168.2.110", 
    "", 
    "admin", 
    "", 
    "admin", 
    "", 
    "LBB_132", 
    "", 
    "BOOT12-SEC-V3.4R3E37C" 
  } 
} 

The output of this test app doesn't seem right, either:

include std/pretty.e 
include std/console.e 
 
include sqlite3.e 
 
sequence res 
sequence sql 
 
sequence db_name = "mydb.sqlite" 
 
atom sql_db = sqlite_open(db_name, 0) 
 
if sql_db <= 0 then 
	puts(1, "Couldn't open SQLite database\n") 
else 
	puts(1,"Opened " & db_name) 
	 
	-- make sure 'categories' table exists 
	res = exec_sql_command(sql_db, "CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent INTEGER, name TEXT, data TEXT );") 
	puts(1,"\n\tcreated sql table categories") 
end if 
 
-- test the sql db 
sql = `INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.onlinesuperstore.com/browse/");` 
puts(1,"\n" & sql) 
res = exec_sql_command(sql_db,sql) 
puts(1,"\n") 
?res 
wait_key() 
res = exec_sql_command(sql_db,`SELECT * FROM categories`) 
pretty_print(1,res,{3}) 
wait_key() 

Opened mydb.sqlite 
    created sql table categories 
INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.o 
nlinesuperstore.com/browse/"); 
{} 
{ 
  { 
    "id", 
    "", 
    "parent", 
    "" 
  }, 
  { 
    "name", 
    "", 
    "data", 
    "" 
  } 
} 

What's going on?

new topic     » goto parent     » topic index » view message » categorize

5. Re: LibCurl 4 and SQLite 3 wrappers

euphoric said...

The output of this test app doesn't seem right, either:

include sqlite3.e 
res = exec_sql_command(sql_db,`SELECT * FROM categories`) 

What's going on?

I can't say much about that particular wrapper, but anyway, I quickly translated your code to phix and my pSQLite.e:

include pSQLite.e 
constant db_name = "mydb.sqlite"  
object res = delete_file(db_name) 
sqlite3 sql_db = sqlite3_open(db_name) 
  
-- NB: not properly tested; you may need to call [eg] sqlite3_set_fatal_id() before this sort of error handling becomes meaningful. 
if sql_db <= 0 then  
        puts(1, "Couldn't open SQLite database\n")  
else  
        puts(1,"Opened " & db_name)  
          
        -- make sure 'categories' table exists  
        res = sqlite3_exec(sql_db, "CREATE TABLE IF NOT EXISTS categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent INTEGER, name TEXT, data TEXT );")  
        puts(1,"\n\tcreated sql table categories")  
end if  
  
-- test the sql db  
string sql = `INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.onlinesuperstore.com/browse/");`  
puts(1,"\n" & sql)  
res = sqlite3_exec(sql_db,sql)  
puts(1,"\n")  
?res  
res = sqlite3_get_table(sql_db,`SELECT * FROM categories`)  
pp(res) 
{} = wait_key()  

output:

Opened mydb.sqlite 
        created sql table categories 
INSERT INTO categories (parent,name,data) VALUES (0,"departments","https://www.onlinesuperstore.com/browse/"); 
0 
{{`id`, `parent`, `name`, `data`}, 
 {`1`, `0`, `departments`, `https://www.onlinesuperstore.com/browse/`}} 

I wonder if the delete_file() would help you, by removing an out-of-date table definition? HTH.

PS: To answer your earlier question, in pSQLite.e, the sqlite3_bind_blob() routine uses serialize(val), and sqlite3_column_blob() uses deserialize() to retrieve it, which are my versions of compress() and decompress(). I should say that probably means blobs saved with OE are not readable by phix, and vice versa.

new topic     » goto parent     » topic index » view message » categorize

6. Re: LibCurl 4 and SQLite 3 wrappers

ghaberek said...

Curious, what benefit is there to storing serialized objects in the database? Wouldn't you be better served using text and number fields so you can query off them?

You can store exact atom values, rather than to-nearest-human-readable-string for one, which could be important for a table of thousands of readings.
You can also store images, perhaps not super-efficient but could be better than shipping hundreds or thousands of separate files.

new topic     » goto parent     » topic index » view message » categorize

7. Re: LibCurl 4 and SQLite 3 wrappers

petelomax said...

You can store exact atom values, rather than to-nearest-human-readable-string for one, which could be important for a table of thousands of readings.

I don't get it. Why can't you do this with the appropriate number field in SQL? MySQL for example supports FLOAT and DOUBLE columns.

new topic     » goto parent     » topic index » view message » categorize

8. Re: LibCurl 4 and SQLite 3 wrappers

petelomax said...

I wonder if the delete_file() would help you, by removing an out-of-date table definition? HTH.

Tried that, but it still fails. It seems there's an issue with the EuSQLite module I'm using.

Can anybody else duplicate this issue with Euphoria?

new topic     » goto parent     » topic index » view message » categorize

9. Re: LibCurl 4 and SQLite 3 wrappers

jimcbrown said...
petelomax said...

You can store exact atom values, rather than to-nearest-human-readable-string for one, which could be important for a table of thousands of readings.

I don't get it. Why can't you do this with the appropriate number field in SQL? MySQL for example supports FLOAT and DOUBLE columns.

You're not wrong, my bad! How about this: while bind_double() will do only one atom, bind_blob() can do several hundred or more in one go?

I have also just remembered a case where there were 72 meter readings per day, and needing 72 unique column names was quite the pita (a1..24, b1..24, and c1..24, or somesuch).

new topic     » goto parent     » topic index » view message » categorize

10. Re: LibCurl 4 and SQLite 3 wrappers

petelomax said...

You're not wrong, my bad! How about this: while bind_double() will do only one atom, bind_blob() can do several hundred or more in one go?

Ah! Makes perfect sense now.

petelomax said...

I have also just remembered a case where there were 72 meter readings per day, and needing 72 unique column names was quite the pita (a1..24, b1..24, and c1..24, or somesuch).

Sounds awful. bind_blob() sounds much better.

new topic     » goto parent     » topic index » view message » categorize

Search



Quick Links

User menu

Not signed in.

Misc Menu