SQLite semi Embedded database functionality in AutoIt

[blink314]

Actually, quick question: are views kept over sessions? If I create a view will it be viewable when you close and open the database again?

Kevin

[blink314]

Ok, guictrlsetstyle works on some controls and not on others. It turns radio controls into buttons. Group controls also do not respond well.

Kevin

[ptrex]

Sorry I didn' s say that they would work on all of them.

I suggested only to add them at these lines

PS please add on line 152, 159 and 310 the command "guictrlsetstyle(-1,$ws_visible)" to solve the GUI problems.

This is the Table name Label, the New Field ListView and the SQL Input box.

If you could add only those and than upload the changed version I can start looking for the rest of the GUI problems.

PS can you tell what the procedure is to add a 'new field'. I tested this but could not get this to work.

Till later

[blink314]

I already tried addding guictrlsetstyle to the listview and it made it disappear. Also the radio buttons are messed up... actually once you open another gui window, most all the controls go wacky.

New Field creates a new field for a CREATE TABLE statement. Click the "New Field" button. In the text box at the bottom type the field name, "::", and then pick what options you want. You should be able to specify options that arent on the list but you will have to type these manually. Click preview to see a preview of what the program thinks you are trying to say.

For Example:

Field1::0,8

Makes a new field called Field1 that has a datatype of TEXT and a default value of CURRENT_DATE.

Once you have added all the fields you want, make sure you have a table name selected and then click on the Create Table button.

Kevin

Edited November 16, 2005 by blink314

[ptrex]

all the controls go wacky

When did this start happening. I can' t recall that there was a problem with the controls in the early versions you posted.

Any idea ?

[blink314]

If you put in the gui styles Visible, Clibsiblings, and OverlappedWindow the gui looks fine. Until you try and write to the Excel Object. Then the entire gui flickers like mad. If you add ClipChildren the excel object works like a charm but the rest of the gui screws up.

I need the Excel GUI to work without locking things since it takes a fair bit of time for the data to load. You cant make the user wait while it loads.

Kevin

[ptrex]

@blink314

Can you copy the code in mu post #2 and run it.

I have there an example database and export to the Excel COM and no flickering.

Let me know if this is OK.

[blink314]

That wont work. You lock the GUI before inserting data. I moved the export to xl button off the tab, took out the gui lock statements and it flickers like crazy. With 2000 lines of data (nWind.db) the data loads in half a minute or more. YOu cant lock the gui and have the user sit there with a blank screen while it all loads. Just isnt practical!

I dont know why those styles make the controls show up badly.

Kevin

[ptrex]

@blink314

Flickering solved.

Please make adjustments here

Func TreeDBTableClick()

Dim $GetFieldNames

Dim $Data1 = ""

GUISetState(@SW_LOCK)

The "GUISetState(@SW_LOCK)" should be moved up starting just under the DIM statements.

And the other should go here, just before the EndFunc

GUISetState(@SW_UNLOCK)

EndFunc ;==>TreeDBTableClick

Now the flicering is stopped, but you will have the problem of 100% CPU.

Maybe there is a solution for that as well.

But before proceding. Can you make the recommended corrections first and than upload that version. So that we can stay in SYNC.

Thanks

[blink314]

Yes, flickering is stopped but now the excel control does not update until all the data is in it. Go to the nwind database and click on the OrderDetails table. It takes a good half minute or more to load. I dont want to sit there with a blank form (or a message saying "processing") while it loads all the data.

Kevin

[ptrex]

@Blink314

I know that you don' t want to wait for it to load. Me neither.

There are 2 options :

Go back to the LV as it was in the beginning (and like in my example) or rewrite the procedure.

The CPU goes to 100 % when loading the data.

This is a not a problem of the Control, but there is a problem in how you wrote the procedure to load the data.

At the moment it read each record from the table and then it writes to Excel Object.

Then the next one and so on.

A better way to solve to CPU related problem is to write first all the records to an Temp ARRAY.

And then fill the EXCEL com in 1 block from the array.

[blink314]

How do you write the array to the excel object all at once?

Kevin

Nevermind found an example. I'll try it see what happens.

Edited November 16, 2005 by blink314

[ptrex]

@blnik314

Added the Bugs and add features list in post #1.

Seems that the add features is longer than the bugs list, which is GOOD !!

Don' t forget to upload you latest version so I can proceed (including the changes I mentioned before).

Thanks

[blink314]

Ok here is a status update on the bugs/features:

Bugs:

- Excel COM table loading is fixed

- Excel column resizing in SQL view is fixed

Features:

- Storing VIEWS and TRIGGERS is kind of already implemented. Every command that changes the database is stored in the z_SQL_Log table. Do you mean something way different than this? VIEWS and TRIGGERS should be included in this... let me know if they are not.

- Add more datatypes? You can add them in if you know them... just type in the type in the Insert Field dialog (eg: FieldName1::FLOAT,9 would give a FLOAT datatype with a DEFAULT CURRENT DATE). I dont have numbers for them because... a ten based number system only has ten one digit numbers (the numbers are there for speed for the most used types). All collate functions work this way too. If they dont let me know.

- Analyse function... in the GUI you mean?

- Import from Excel is well underway and almost working.

- The whole field manipulation can be done from the SQL window... SQLite does not internally support a lot of field manipulation. I will get around to this some time, but it involves creating a temp table and moving data back and forth. Not impossible, but something that's not high on the priorities.

- Rollback is something I've thought about... and for the present decided against implementing. I will look into it again in the future, once I've gotten a little more experience under my belt.

- GUI for functions and expressions?? WHat's this for?

Remember, the goal of the SQL section is that an experienced person can enter in whatever they wish. I will not be putting everything in the GUI. The GUI is mainly so you can create a database quickly and get a handle on what's inside and where. If I try and fit everything in the GUI it will become cumbersome (more than it already is).

Kevin

Database_1.au3

Edited November 16, 2005 by blink314

[ptrex]

Jezus, you are fast !!

I ran your last version, and indead the problems are solved with the XLS com loading.

Kevin, the feature list is not something I want to put all on your sholders !!

It's just a checklist for me (and you). If you decide to freeze your version, I could eventually add features of the list to it. So don' t feel pushed to do or add things you don' t need.

The important thing is that I keep an overview of what I have in my mind.

Add more datatypes? You can add them in if you know them... just type in the type in the Insert Field dialog

I was not aware of this. So maybe more documentation should be added to the to list

Analyse function

This refers to the SQLite ANALYZE (sorry for my misspelling) The ANALYZE command gathers statistics about indices and stores them in a special tables. For more info see SQLite Syntax.

Import from Excel is well underway and almost working.

Perfect

Rollback

Seems very interesting. In case someone does something stupid, like DROP TABLE or DELETE RECORDS.

Functions/Expressions

This is something for the long run. See SQLite Syntax click on Expressions.

http://www.sqlite.org/lang.html

I will not be putting everything in the GUI

If you leave it I will try to take it a little further and see how far we get.

[blink314]

Concerning being "fast," part of the reason is that right now I am working on this at work. It's a slow period and since this will be used to store information at work and because my coworkers are interested in possibly using databases this counts as work. It's also fast because I have numerous places I could use this program... at work and at home. And I havent even started the frontend.

Documentation?? I'm following in the footsteps of that software giant we all know and love. Incidentally I also am following in the footsteps of the Open Source community (couldnt resist the jab!). Yeah, documentation will have to be generated at some point.

But, on the plus side I have the excel import working on small xls files. I just tried our 45,000 line 20 column behemoth and it crashed so we'll se what happens!

Kevin

Database_1.au3

Edited November 16, 2005 by blink314

[randallc]

Hi,

I haven't checked your import yet; did you use import or paste?

If you are importing from arrays, you have no choice but to "paste" the pre-arranged array; but from listView you could write to a csv first

But if you already have exported to csv, better than arrays is; [Arrays will slow things immensely with large files]

Updated Excel com to include fast import facility without having to use macro or Excel import function.

This is as quick as importing via Data import in excel (eg 388Mb, 23000x200 rows/cols in 2 minutes; [3000Kb=3Mb/sec?]) [it is possible to do the import via Excel "Import function too from a file, but script is fiddly and I did not see the worth"]

Simply opens the "csv" file as object, then copies "used range", then pastes where asked

column number just upperleft cell name only

see how it works; (a.... function in Excel.com)

ExcelComthread post 40?

Randall

Edited November 16, 2005 by randallc

[blink314]

I am doing an import. I didnt use your udf because I wanted to bring the data in and write it to sqlite immediately. Looking through your functions there seemed to be a lot of overhead. I just used what I needed. When I left work 1400 records had been entered out of 40,000.

The import assumes a few things that I forgot to mention:

- the first line contains your column headings. These MUST be in the same format as the New Field dialog box in the GUI.

- In the first row there can be NO empty columns. An empty column signals the end of the table column-wise.

- Starting with row 2 all data is imported from column 1 to the first column with a null value.

- "" is treated as " " when inserting in the database for now.

- leading zeros are kept... by adding a ' in front of the 0.

Try and see...

Kevin

[piccaso]

seems like sqlite can be called directly from autoit.

a query and getting the errormsg is easy but i didnt find out how to read the result.

i'm bored from playing around for now, so heres a partialy working example.

expandcollapse popup

#include <array.au3>
$dll = DllOpen("sqlite3.dll")
If $dll == -1 Then
    ConsoleWrite("DLLOpen Error")
    Exit
EndIf
$r = DllCall($dll, "int", "sqlite3_open", "str", "my.db", "long_ptr", 0)
If @error > 0 Then
    ConsoleWrite("sqlite3_open Error: " & @error)
EndIf
; _ArrayDisplay($r,"sqlite3_open")
$hSql = $r[2]
$r = DllCall($dll, "int", "sqlite3_get_table", "ptr", $hSql, "str", "select '2'", "long_ptr", 0, "long_ptr", 0, "long_ptr", 0, "long_ptr", 0)
If @error > 0 Then
    ConsoleWrite("sqlite3_get_table Error: " & @error)
EndIf
; _ArrayDisplay($r,"sqlite3_get_table")
$s = DllStructCreate ("ptr", $r[3])
If @error > 0 Then
    ConsoleWrite("structcreate Error: " & @error & @CR)
EndIf
$rval = DllStructGetData ($s, 1)
If @error > 0 Then
    ConsoleWrite("structget Error: " & @error & @CR)
EndIf
$s1 = DllStructCreate ("char", $rval)
If @error > 0 Then
    ConsoleWrite("structcreate1 Error: " & @error & @CR)
EndIf
$rval1 = DllStructGetData ($s1, 1)
If @error > 0 Then
    ConsoleWrite("structget1 Error: " & @error & @CR)
EndIf
ConsoleWrite("result: '" & $rval1 & "'" & @CR);
$r = DllCall($dll, "int", "sqlite3_close", "ptr", $hSql)
If @error > 0 Then
    ConsoleWrite("sqlite3_close Error: " & @error)
EndIf
; _ArrayDisplay($r,"sqlite3_close")
DllClose($dll)

here is the source of sqlite3_get_table > http://www.srcdoc.com/sqlite_3.2.2/table_8c-source.html

a purebasic example of doing this > http://forums.purebasic.com/english/viewto...p?p=91178#91178

rapidq example > http://citymap.3322.org/?f=3886

Edited November 17, 2005 by piccaso

[blink314]

Latest word is that I have successfully imported over 37,000 records (~20 columns each) from the monster excel file. Loading into the listview is somewhat slow, but I have a few ideas for making this easier to tolerate. Besides, you use a database so you only have to see what you want to see anyway! There is a bug that prevents the Excel object from displaying anything over ~5000 lines, but I know what the problem is and it's already fixed... but its at work and will come tomorrow.

Kevin