DLLC

[JRS]

This is the CSV2SQL example posted earlier in a SUB. If you don't pass the database:table  name argument it will default to using the CSV file name and csv_import as the table name.

Note: I was getting weird filenames being generated with the sqlite::open() function. I  appended a NULL to the end and the problem went away. I need to look closer at AIR's ext. module code.

Update: I took a peek at the SQLite extension module code and noticed AIR used the SB String type rather than a Zstring argument definition. It now works fine without the extra NULL appended. The updated SQLite ext. module will be in the 2.2 release.

Code: Script BASIC

1. IMPORT sqlite.bas
2.  
3. SUB CSV2SQL(csvfn, fmtstr, dbtbl, quo)
4.   OPEN csvfn FOR INPUT AS #1
5.   IF dbtbl = "" THEN
6.     dbfn = LEFT(csvfn,INSTR(csvfn,".")) & "db"
7.     tblfn = "csv_import"
8.   ELSE
9.     SPLIT dbtbl BY ":" TO dbfn, tblfn
10.     dbfn &= ".db"
11.   END IF    
12.   db = sqlite::open(dbfn & CHR(0))
13.   LINE INPUT #1, hdr
14.   hdr = CHOMP(hdr)
15.   SPLITA hdr BY "," TO col
16.   SPLITA fmtstr BY "" TO typ
17.   lastcol = UBOUND(col)
18.   sql = "CREATE TABLE " & tblfn & "("
19.   FOR x = 0 TO lastcol
20.     tmp = ""
21.     IF typ[x] = "S" THEN
22.       tstr = " TEXT"
23.     ELSE IF typ[x] = "I" THEN
24.       tstr = " INTEGER"
25.     ELSE IF typ[x] = "R" THEN
26.       tstr = " REAL"
27.     END IF
28.     tmp &= col[x] & tstr
29.     IF x <> lastcol THEN tmp &= ", "
30.     sql &= tmp
31.   NEXT
32.   sql &= ");"
33.   sqlite::execute(db, sql)
34.   sqlite::execute(db, "BEGIN TRANSACTION")
35.   WHILE NOT EOF(1)
36.     sql = "INSERT INTO " & tblfn & " VALUES ("
37.     LINE INPUT #1, csvln
38.     csvln = CHOMP(csvln)
39.     SPLITAQ csvln BY "," QUOTE "" TO col
40.     FOR x = 0 TO lastcol
41.       IF typ[x] = "S" THEN
42.         tmp = "'" & col[x] & "'"
43.       ELSE
44.         tmp = col[x]
45.       END IF
46.       IF x <> lastcol THEN tmp &= ", "
47.       sql &= tmp
48.     NEXT
49.     sql &= ");"
50.     sqlite::execute(db, sql)
51.   WEND
52.   sqlite::execute(db, "END TRANSACTION")
53.  
54.   sqlite::close(db)
55.   CLOSE(1)
56. END SUB
57.  
58.  
59. ' CSV2SQL "SacramentocrimeJanuary2006.csv", "SSISISIRR", "sac16:crime", ""
60. CSV2SQL "SacramentocrimeJanuary2006.csv", "SSISISIRR", "", ""
61.  

[JRS]

I'm still testing...

How things going with your O2 data processing library?

I'm pretty happy with the two new T.bas additions. It would be helpful if others would give the new SB routines a try on CSV files you may want to convert. (Works on Windows, Linux and Android native)

.

[Charles Pegge]

Hi John,

We have text and numeric sorting, ascending and descending.

Also selectable multiple columns.

Pls correct me if I'm wrong but I don't think there is a way of passing variadic data via a module wrapper function. So dllcall has to be invoked directly for variadic procedures.

[JRS]

All SB extension module functions are variadic based. Your right, there is no way to dynamically service a variadic defined function on the fly. (that I know) I had to emulate it in many of the IUP SB interface calls. If I remember correctly, this is how IUP is doing Vbox in their source. I just copied the code for most part.

Code: C

1. /*
2. Ihandle* IupVbox(Ihandle *child, ...);  <iup.h>
3.  
4. Creates a void container for composing elements vertically. It is a box that
5. arranges the elements it contains from top to bottom.
6. */
7. besFUNCTION(PuiVbox)
8.   VARIABLE Argument;
9.   Ihandle *ih;
10.   unsigned long i;
11.   char *child;
12.  
13.   ih = IupVbox(NULL);
14.  
15.   for( i=1 ; i <= (unsigned)besARGNR ; i++ ){
16.     Argument = besARGUMENT(i);
17.     besDEREFERENCE(Argument);
18.     memcpy(&child, STRINGVALUE(Argument), sizeof(child));
19.     IupAppend(ih, child);
20.     }
21.  
22.   besRETURN_POINTER(ih);
23. besEND
24.  

[JRS]

Charles,

I modified one of my old IUP examples to show the variadic emulation of appending child controls to a IupVbox container.

Code: Script BASIC

1. ' Script BASIC Rapid-Q form conversion
2.  
3. IMPORT iup.bas
4.  
5. ' SBIUP-Q INIT
6.  
7. Iup::Open()
8. Iup::SetGlobal("DEFAULTFONT", "Sans, 7.5")
9.  
10. ' CREATE FORM
11.  
12. Form = Iup::Create("dialog")
13.        Iup::SetAttributes(Form, "RASTERSIZE=320x240, TITLE=\"Form1\"")
14.  
15. '    vbx     = Iup::Create("vbox")
16. '              Iup::Append(Form, vbx)
17.  
18.      Label1  = Iup::Create("label")
19.                Iup::SetAttributes(Label1, "TITLE=\"Customer\", RASTERSIZE=55x13, FLOATING=YES, POSITION=\"19,19\"")
20. '              Iup::Append(vbx, Label1)
21.  
22.      Edit1   = Iup::Create("text")
23.                Iup::SetAttributes(Edit1, "RASTERSIZE=121x21, FLOATING=YES, POSITION=\"72,16\"")
24. '              Iup::Append(vbx, Edit1)
25.  
26.      Button1 = Iup::Create("button")
27.                Iup::SetAttributes(Button1, "TITLE=\"&Quit\", RASTERSIZE=75x25, FLOATING=YES, POSITION=\"107,164\"")
28. '              Iup::Append(vbx, Button1)
29.  
30. ' Code to show variadic emulation
31. vbx = Iup::Vbox(Label1, Edit1, Button1)
32. Iup::Append(Form, vbx)
33.  
34.                
35. ' SET CALLBACKS
36.  
37. Iup::SetCallback(Form, "CLOSE_CB", ADDRESS(Win_exit()))
38.  
39.  
40. ' CALLBACKS FUNCTIONS
41.  
42. SUB Win_exit
43.   Iup::ExitLoop = TRUE
44. END SUB
45.  
46. ' MAIN
47.  
48. ' Iup::Show(Iup::LayoutDialog(Form))
49. Iup::Show(Form)
50. Iup::MainLoop
51. Iup::Close
52.  

[JRS]

I'm thinking of doing one last function to format a row from a SQLite return. Most of the work is already done and I should have something posted soon. FmtSQLRow

It turns out all I had to do is check if the passed data line was an array.

Code: Script BASIC

1. ' result = FormatLine(in_str/array, fmt_str, quo_char, num_spc) Note: num_spc = -1 uses TAB
2.  
3. IMPORT sqlite.bas
4.  
5. FUNCTION FormatLine(ln,fmtstr,qc,nsp)
6.   IF ISARRAY(ln) THEN
7.     col = ln
8.   ELSE
9.     SPLITAQ ln BY "," QUOTE qc TO col
10.   END IF
11.      
12.   SPLITA fmtstr BY "|" TO fmtcmd
13.   rs = ""
14.   FOR x = 0 to UBOUND(col)
15.     SPLITA fmtcmd[x] BY ":" TO fmt
16.     IF fmt[0] = "L" THEN
17.       tmp = LEFT(col[x] & STRING(fmt[1]," "),fmt[1])
18.       GOSUB Margin
19.     ELSE IF fmt[0] = "R" THEN
20.       IF fmt[2] <> undef THEN
21.         tmp = FORMAT(fmt[2],col[x])
22.       ELSE
23.         tmp = col[x]
24.       END IF
25.       tmp = RIGHT(STRING(fmt[1]," ") & tmp, fmt[1])
26.       GOSUB Margin
27.     ELSE IF fmt[0] = "C" THEN
28.       pad = fmt[1] - LEN(col[x])
29.       pboth = pad \ 2
30.       prt = pad % 2
31.       tmp = STRING(pboth," ") & col[x] & STRING(pboth," ") & STRING(prt," ")
32.       GOSUB Margin
33.     END IF
34.   NEXT
35.   GOTO Done
36.  
37.   Margin:
38.   IF nsp = -1 THEN
39.     tmp &= "\t"
40.   ELSE
41.     tmp &= STRING(nsp," ")
42.   END IF
43.   rs &= tmp  
44.   RETURN
45.  
46.   Done:
47.   FormatLine = rs
48. END FUNCTION
49.  
50. db = sqlite::Open("sac16.db")
51. stmt = sqlite::Query(db,"SELECT * FROM crime LIMIT 1")
52. sqlite::Row(stmt)
53. sqlite::FetchArray(stmt,columns)
54. fmtstr = "L:15|L:30|R:4|L:4|R:6|L:35|L:6|R:10:%~-##0.0000~|R:10:%~-##0.0000~"
55. PRINT FormatLine(columns,fmtstr,"",2),"\n"
56. sqlite::Close(db)
57.  

Output

Code: [Select]

jrs@laptop:~/sb/sb22/test$ scriba fmtsqlrow.sb
1/1/06 0:00      3108 OCCIDENTAL DR                 3  3C      1115  10851(A)VC TAKE VEH W/O OWNER        2404       38.5504   -121.3914 
jrs@laptop:~/sb/sb22/test$


[Charles Pegge]

Hi John,

I think string passing might be a better alternative to using variadics.

If you take table-processing far enough - then some form of SQL begins to emerge, in order to specify all the options. I wonder if there is a sweet spot for managing simple databases with lightweight code - say 20k of source code.

[JRS]

Charles,

SQLite is hard to beat. The SB extension module static links the SQLite library in so there is zero dependencies. It's important on Android as there is no native Andorid Linux SQLite support. (only Java VM) You may want to look at SQLite for O2 rather than recreating the wheel in code. IMHO

Could SQLite be static linked into Oxygen.dll? (expanding the O2 keyword set) I believe SQLite can keep it's DB in memory rather than using a file. Maybe a wrapper include would be better.

John

.

[JRS]

Charles,

I haven't lost interest in our DLLC library project and was hoping to get Rob involved with what we have cooking. (TinyScheme, SDL_gfx, SQLite3 and DLLC) Our BASIC children hooked on METHods. 

I'm trying to wrap up what is and isn't going to make it in the Script BASIC 2.2 release which I hope to have out by the first of the year.

John

[JRS]

Charles,

It seems rather easy to use :memory: as the data base rather than a file.

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory.

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:". In other words, instead of passing the name of a real disk file into one of the sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() functions, pass in the string ":memory:". For example:

Code: C

1.     rc = sqlite3_open(":memory:", &db);
2.  

When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

The first run was using the :memory: feature and the second run using a normal file. Both were reading from a .csv disk file.

Output

jrs@laptop:~/sb/sb22/test$ time scriba csv2sql.sb

real   0m0.426s
user   0m0.327s
sys   0m0.000s
jrs@laptop:~/sb/sb22/test$ time scriba csv2sql.sb

real   0m0.627s
user   0m0.325s
sys   0m0.008s
jrs@laptop:~/sb/sb22/test$

[Charles Pegge]

Hi John,

I see that SQLITE weighs in at around 700K. I'm after something much lighter, for managing simple data tables, that will work well with other inc/ utilities. The question is how much fuctionality can be obtained for 20kb?

[JRS]

Fat people are happy people.  (I've been told. I'm 175 lbs / 6' 1" and have been so since I was 17 years old)

I'll take convenience, portability and ease of use over ball busting teeny weeny code any day. 

I guess I would have to be an ASM programmer to realize your 700KB concerns.

@ALL - You can find a SQLite3 example done in O2 in the examples/data processing folder of the O2 zip.

BTW SB < 700KB

Now that I figuured out how to get the speed I was looking for with SQLite, I plan to use it more instead of SB arrays when a sort option is required.

[JRS]

WhiteDB is a lightweight database library operating fully in main memory.
Disk is used only for dumping/restoring database and logging.

Data is persistantly kept in the shared memory area: it is available simultaneously
to all processes and is kept intact even if no processes are currently using the
database.

WhiteDB has no server process. Data is read and written directly from/to memory,
no sockets are used between WhiteDB and the application using WhiteDB.

WhiteDB keeps data as N-tuples: each database record is a tuple of N elements.
Each element (record field) may have an arbitrary type amongst the types provided
by WhiteDB. Each record field contains exactly one integer (4 bytes or 8 bytes).
Datatypes which cannot be fit into one integer are allocated separately
and the record field contains an (encoded) pointer to the real data.

WhiteDB is written in pure C in a portable manner and should compile and function
without additional porting at least  under Linux (gcc) and Windows
(native Windows C compiler cl). It has Python and experimental Java bindings.

WhiteDB Project Site

Here is the results of the speed test I ran that came with the distribution. Sorry Charles, still a 700KB engine.  Being lighting fast might be enough to ignore it's weight problem.

Record pointers: the foundation of a graph database

Searching for a related record through an index using the record id (the standard SQL way) is neither the fastest nor the simplest way of going through complex data structures.

It is much faster and easier to store direct pointers to records.

speed15.c builds a database of 10 million records and stores a pointer to the previously created record to the field 3 of each record. Essentially, all the 10 million records will form a long chain from the last record back to the first. Additionally, we store a pointer to the last record into field 2 of the very first record, to directly access the last record later.

The building test takes 0.66 seconds.

speed16.c traverses through the whole chain of backward pointers and counts the 10 million records in a list.

The traversal test takes 0.15 seconds: in other words, you can traverse almost 100 million records in a linked list in a second.

jrs@laptop:~/whitedb-0.7.3/Examples/speed$ gcc speed15.c -o speed15 -O2 -lwgdb
jrs@laptop:~/whitedb-0.7.3/Examples/speed$ time ./speed15
wg memory error: creating shared memory segment: Specified segment size too large or too small.
wg memory error: create_shared_memory failed.
db creation failed
jrs@laptop:~/whitedb-0.7.3/Examples/speed$ sudo sysctl kernel.shmmax=1000000000
kernel.shmmax = 1000000000
jrs@laptop:~/whitedb-0.7.3/Examples/speed$ time ./speed15
i 10000000

real   0m1.154s
user   0m0.660s
sys   0m0.492s
jrs@laptop:~/whitedb-0.7.3/Examples/speed$ gcc speed16.c -o speed16 -O2 -lwgdb
jrs@laptop:~/whitedb-0.7.3/Examples/speed$ time ./speed16
i 10000000

real   0m0.281s
user   0m0.194s
sys   0m0.087s
jrs@laptop:~/whitedb-0.7.3/Examples/speed$


FYI: I'm staying with SQLite3. I like SQL access syntax. It self documents as you code.