SQLite Extender: describe

[stanl]

Jim;


Your extender is terrific. Maybe it is me, but the .chm file that accompanies your .dll is empty when I open it. I can see a dbschema() method but not much more.


Anyway. I looked back and found I had begun an SQLite describe script based on the ODBC driver. For fun, I thought about trying it out with the .NET 'System.Data.SQLite.dll'.  The attached is crude, but illustrates

   
• SQLite can be loaded in memory
• SQLite has a PRAGMA element that is pretty cool.

Not sure if a more full describe capability is even worth considering for an extender. I would be pleased to help test any possibilities in that area.

Code (WINBATCH) Select Expand



gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()
dll = folder:'System.Data.SQLite.dll'
If ! FileExist(dll) Then Terminate(@TRUE,"Cannot Continue",dll:@LF:"Required .NET dll is missing")
types="SQLite Files|*.sqlite;*.db|"
db=AskFilename("Select SQLite DB", folder, types, "", 101)
If !  isSqLite(db) Then Terminate(@TRUE,"Cannot Continue",db:@LF:"is not a recognized SQLite database")
ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')
oSQLite = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;mode=memory&cache=shared")
oSQLite.Open
oCmd = ObjectClrNew("System.Data.SQLite.SQLiteCommand",oSQLite)
tables=Pragma_Display(oCmd,"Select * from sqlite_master where type='table';")
For i = 1 To ItemCount(tables,@TAB)-1
   cSQL = "SELECT sql FROM sqlite_master WHERE name = '":ItemExtract(i,tables,@TAB):"';"
   oCmd.CommandText = cSQL
   oCmd.ExecuteNonQuery()
   rdr = oCmd.ExecuteReader()
   cDescribe=""
   While rdr.Read()
      cDescribe=cDescribe:rdr.GetString(0)
   EndWhile
   rdr.Close()
   Message("SQLite Database: ":db,"Table SQL: ":ItemExtract(i,tables,@TAB):@LF:cDescribe)
Next
Exit
:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit


:CANCEL
If cncl==0 Then goto start
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine




#DefineFunction isSqLite(cDB)
valid="53514C69746520666F726D6174203300"
retval=0
fs=FileSize(cDB)
binbuf = BinaryAlloc(fs+100)
If binbuf == 0
   Return(retval)
Else
   BinaryRead(binbuf, cDB)
   ret=BinaryPeekHex( binbuf, 0, 16)
   If ret==valid Then retval=1
EndIf
BinaryFree(binbuf)
Return(retval)
#EndFunction


#DefineFunction Pragma_Display(oCmd,sql)
oCmd.CommandText = sql
oCmd.ExecuteNonQuery();
rdr = oCmd.ExecuteReader()
result=""
While rdr.Read()
   result=result:rdr.GetString(1):@TAB
EndWhile
rdr.Close()
Return result
#EndFunction
Return


[JTaylor]

Go to the File Properties for the Help file and Unblock it.

Jim

[JTaylor]

What would you like to see included?   You can use, at least some, of the pragma options with GetString() and GetRows().

Jim

Jim;


Your extender is terrific. Maybe it is me, but the .chm file that accompanies your .dll is empty when I open it. I can see a dbschema() method but not much more.


Anyway. I looked back and found I had begun an SQLite describe script based on the ODBC driver. For fun, I thought about trying it out with the .NET 'System.Data.SQLite.dll'.  The attached is crude, but illustrates

   
• SQLite can be loaded in memory
• SQLite has a PRAGMA element that is pretty cool.

Not sure if a more full describe capability is even worth considering for an extender. I would be pleased to help test any possibilities in that area.

Code (WINBATCH) Select Expand



gosub udfs
IntControl(73,1,0,0,0)
folder = dirscript()
dll = folder:'System.Data.SQLite.dll'
If ! FileExist(dll) Then Terminate(@TRUE,"Cannot Continue",dll:@LF:"Required .NET dll is missing")
types="SQLite Files|*.sqlite;*.db|"
db=AskFilename("Select SQLite DB", folder, types, "", 101)
If !  isSqLite(db) Then Terminate(@TRUE,"Cannot Continue",db:@LF:"is not a recognized SQLite database")
ObjectClrOption("Appbase", DirScript())
ObjectClrOption('use','System.Data.SQLite')
oSQLite = ObjectClrNew("System.Data.SQLite.SQLiteConnection","Data Source=%db%;mode=memory&cache=shared")
oSQLite.Open
oCmd = ObjectClrNew("System.Data.SQLite.SQLiteCommand",oSQLite)
tables=Pragma_Display(oCmd,"Select * from sqlite_master where type='table';")
For i = 1 To ItemCount(tables,@TAB)-1
   cSQL = "SELECT sql FROM sqlite_master WHERE name = '":ItemExtract(i,tables,@TAB):"';"
   oCmd.CommandText = cSQL
   oCmd.ExecuteNonQuery()
   rdr = oCmd.ExecuteReader()
   cDescribe=""
   While rdr.Read()
      cDescribe=cDescribe:rdr.GetString(0)
   EndWhile
   rdr.Close()
   Message("SQLite Database: ":db,"Table SQL: ":ItemExtract(i,tables,@TAB):@LF:cDescribe)
Next
Exit
:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit


:CANCEL
If cncl==0 Then goto start
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine




#DefineFunction isSqLite(cDB)
valid="53514C69746520666F726D6174203300"
retval=0
fs=FileSize(cDB)
binbuf = BinaryAlloc(fs+100)
If binbuf == 0
   Return(retval)
Else
   BinaryRead(binbuf, cDB)
   ret=BinaryPeekHex( binbuf, 0, 16)
   If ret==valid Then retval=1
EndIf
BinaryFree(binbuf)
Return(retval)
#EndFunction


#DefineFunction Pragma_Display(oCmd,sql)
oCmd.CommandText = sql
oCmd.ExecuteNonQuery();
rdr = oCmd.ExecuteReader()
result=""
While rdr.Read()
   result=result:rdr.GetString(1):@TAB
EndWhile
rdr.Close()
Return result
#EndFunction
Return


[JTaylor]

I am looking into the in-memory option.   Thought it might just work but apparently not.


Jim

[JTaylor]

My brain started working...The in-memory option will not work with the extender because I do not keep the database open.    If I knew how to pass the object back and forth I could do that but it is my understanding that takes COM and, as I mentioned in my other post, I don't know what pieces are needed to make that work and no good way to find out.  Things like registry entries for CLSID and entry points and such.   Of course the documentation says I need them and offer no other options that I can see but I don't know if that is the case with the Extender.  One day I hope to figure it out but at a bit of an impasse at the moment with no clear way ahead.

Let me know on the other stuff in which you are interested and which doesn't already work.

Jim

[stanl]

I thought loading in memory was just a parameter of the connection string.  In your example .wbt you use "FullUri=file" - which does not work (or at least fails for me) using the .NET dll - so instead I use "Data Source=file". I found that


FullUri=file::memory:?cache=shared  << is likely to fail


Data Source=file;mode=memory&cache=shared  << works with .NET dll


Loading in memory is of course subject to memory. I didn't intend it as a request for the Extender.

[ChuckC]

My brain started working...The in-memory option will not work with the extender because I do not keep the database open.    If I knew how to pass the object back and forth I could do that but it is my understanding that takes COM...

In the Property Store extender that I wrote, I keep COM object in-memory across extender function calls.  Internally, I keep an array of COM object interface references that exists as long as the extender is loaded, and simply use the index values as the means of linking WIL code to the correct COM object instance.  Return the index as a "handle" type of integer value from an extender function and then have the WIL code pass that same integer value back in as a parameter on a subsequent extender function call.

If you are not using COM objects and simply have, say, a handle value or just a pointer to a class instance for a class that manages a SQLite database, then use the handle or pointer value as the data in the array and still use the array element indexes as the "glue".  Or, if you are feeling brave, simply convert the handle/pointer to an appropriately sized unsigned integer based on your 32/64 bit architecture and pass that value around as the "glue".

[JTaylor]

Okay.  I was unsure so responded as if that was the question.      Jut to follow up though since I already started down that path...It is and ":memory:" works with the Extender but the problem is I close the database in-between calls so the database goes away making it appear that it doesn't work.  I had the same experience as you with the uri option.   In any event, if I can find a way to keep it open it does work with the extender so that option would be available.

Jim

I thought loading in memory was just a parameter of the connection string.  In your example .wbt you use "FullUri=file" - which does not work (or at least fails for me) using the .NET dll - so instead I use "Data Source=file". I found that


FullUri=file::memory:?cache=shared  << is likely to fail


Data Source=file;mode=memory&cache=shared  << works with .NET dll


Loading in memory is of course subject to memory. I didn't intend it as a request for the Extender.

[JTaylor]

Thanks.   I am doing a similar thing (just not with COM objects) but between this and what Tony posted it turned on another light switch for me.   I thought once a call was made and finished in the Extender the link to things were broken.  Sort of like a function call (not a perfect reference).    If I understood Tony though regarding the arrays I can retrieve the array reference in the Extender from WIL and if I make changes those changes will reflect when I return to WIL without having write the Array back out.

Just to clarify so I don't chase the wrong rabbit...  When you say "Internally, I keep an array of COM object interface references that exists as long as the extender is loaded", are you passing this back to WIL via DLLVarHandler to keep it alive like I am currently doing or is there a way keep values available within the Extender code between function calls?

Thanks.

Jim

In the Property Store extender that I wrote, I keep COM object in-memory across extender function calls.  Internally, I keep an array of COM object interface references that exists as long as the extender is loaded, and simply use the index values as the means of linking WIL code to the correct COM object instance.  Return the index as a "handle" type of integer value from an extender function and then have the WIL code pass that same integer value back in as a parameter on a subsequent extender function call.

If you are not using COM objects and simply have, say, a handle value or just a pointer to a class instance for a class that manages a SQLite database, then use the handle or pointer value as the data in the array and still use the array element indexes as the "glue".  Or, if you are feeling brave, simply convert the handle/pointer to an appropriately sized unsigned integer based on your 32/64 bit architecture and pass that value around as the "glue".

[JTaylor]

Looking at our previous correspondence it looks like you are writing it out to WIL to keep it alive but confirmation would be good.  Thanks again.

Jim

[ChuckC]

Just to clarify so I don't chase the wrong rabbit...  When you say "Internally, I keep an array of COM object interface references that exists as long as the extender is loaded", are you passing this back to WIL via DLLVarHandler to keep it alive like I am currently doing or is there a way keep values available within the Extender code between function calls?

There is no need to use DLLVarHandler to pass anything back to WIL between extender function calls.  What is critical is the C/C++ "scope" in which the reference to the open SQLite DB resides.  If you implement your extender function as a single function that opens the DB, performs transactions and then closes the DB, then you're never going to be able to preserve an open DB reference across extender function calls.

Instead, what you need to do is split things into 3 or more extender functions.  One opens the SQLite DB and returns a "handle" or "index" to the database instance that is open.  Also, implement a "close" function that takes a "handle" or "index" and properly releases all in-memory resources associated with it.  The other extender functions take the "handle" or "index" value as an input parameter and then make use of it to find the in-memory SQLite DB instance to interact with it.

I'm not familiar with the SQLite API, but, in general, there has to be something where you all a function or instantiate a class to open up a database.  When you do that, you either get something back from the function that is the "handle" to the database, possibly an opaque pointer to something, or you simply have a pointer to a class instance.  In any case, when you perform this "open" operation, store your "handle" or pointer value in a variable that has module scope [C] or is a class data member of your extender class [C++] depending on which language your extender is implemented in.  That way, when the extender function returns control to WIL and all of the function's local variables go out of scope, your "handle" or pointer to the database remains intact.

I strongly favor creating an array of "handles" or class instance pointers vs. passing back an integer value representing the actual "handle" or pointer value itself.  Validation of an "index" value for that array is simpler, and, it avoids receiving a garbage value that would cause an exception if you tried to de-reference it to an actual class instance or otherwise passed it on to a SQLite API function.

There's nothing magical about this technique.  Simply pay attention to the scope of the variable that you use to keep track of the open databases.  Using this design would allow the extender to have a variable number of databases open simultaneously and to interact with them in a piecemeal manner via multiple extender function calls as opposed to having to do the open/db-operation/close in a single function call.

[JTaylor]

Okay.  Again, goes back to my misunderstanding of how the Extender works.   I thought everything went away each time.    If things persist, that makes a HUGE difference. 

THANK YOU!!!

Jim

Just to clarify so I don't chase the wrong rabbit...  When you say "Internally, I keep an array of COM object interface references that exists as long as the extender is loaded", are you passing this back to WIL via DLLVarHandler to keep it alive like I am currently doing or is there a way keep values available within the Extender code between function calls?

There is no need to use DLLVarHandler to pass anything back to WIL between extender function calls.  What is critical is the C/C++ "scope" in which the reference to the open SQLite DB resides.  If you implement your extender function as a single function that opens the DB, performs transactions and then closes the DB, then you're never going to be able to preserve an open DB reference across extender function calls.

Instead, what you need to do is split things into 3 or more extender functions.  One opens the SQLite DB and returns a "handle" or "index" to the database instance that is open.  Also, implement a "close" function that takes a "handle" or "index" and properly releases all in-memory resources associated with it.  The other extender functions take the "handle" or "index" value as an input parameter and then make use of it to find the in-memory SQLite DB instance to interact with it.

I'm not familiar with the SQLite API, but, in general, there has to be something where you all a function or instantiate a class to open up a database.  When you do that, you either get something back from the function that is the "handle" to the database, possibly an opaque pointer to something, or you simply have a pointer to a class instance.  In any case, when you perform this "open" operation, store your "handle" or pointer value in a variable that has module scope [C] or is a class data member of your extender class [C++] depending on which language your extender is implemented in.  That way, when the extender function returns control to WIL and all of the function's local variables go out of scope, your "handle" or pointer to the database remains intact.

I strongly favor creating an array of "handles" or class instance pointers vs. passing back an integer value representing the actual "handle" or pointer value itself.  Validation of an "index" value for that array is simpler, and, it avoids receiving a garbage value that would cause an exception if you tried to de-reference it to an actual class instance or otherwise passed it on to a SQLite API function.

There's nothing magical about this technique.  Simply pay attention to the scope of the variable that you use to keep track of the open databases.  Using this design would allow the extender to have a variable number of databases open simultaneously and to interact with them in a piecemeal manner via multiple extender function calls as opposed to having to do the open/db-operation/close in a single function call.

[JTaylor]

Well that makes me feel like an idiot   :)     Just tested to make sure I was doing what you are talking about to make sure I understood.    Wish I had known that little bit of info many hours ago.

Thanks again.   This will simplify things A LOT!!!   Oh well, learned a lot about DllVarHandler() and some other things along the way so not a wasted trip.

Jim

There is no need to use DLLVarHandler to pass anything back to WIL between extender function calls.  What is critical is the C/C++ "scope" in which the reference to the open SQLite DB resides.  If you implement your extender function as a single function that opens the DB, performs transactions and then closes the DB, then you're never going to be able to preserve an open DB reference across extender function calls.

Instead, what you need to do is split things into 3 or more extender functions.  One opens the SQLite DB and returns a "handle" or "index" to the database instance that is open.  Also, implement a "close" function that takes a "handle" or "index" and properly releases all in-memory resources associated with it.  The other extender functions take the "handle" or "index" value as an input parameter and then make use of it to find the in-memory SQLite DB instance to interact with it.

I'm not familiar with the SQLite API, but, in general, there has to be something where you all a function or instantiate a class to open up a database.  When you do that, you either get something back from the function that is the "handle" to the database, possibly an opaque pointer to something, or you simply have a pointer to a class instance.  In any case, when you perform this "open" operation, store your "handle" or pointer value in a variable that has module scope [C] or is a class data member of your extender class [C++] depending on which language your extender is implemented in.  That way, when the extender function returns control to WIL and all of the function's local variables go out of scope, your "handle" or pointer to the database remains intact.

I strongly favor creating an array of "handles" or class instance pointers vs. passing back an integer value representing the actual "handle" or pointer value itself.  Validation of an "index" value for that array is simpler, and, it avoids receiving a garbage value that would cause an exception if you tried to de-reference it to an actual class instance or otherwise passed it on to a SQLite API function.

There's nothing magical about this technique.  Simply pay attention to the scope of the variable that you use to keep track of the open databases.  Using this design would allow the extender to have a variable number of databases open simultaneously and to interact with them in a piecemeal manner via multiple extender function calls as opposed to having to do the open/db-operation/close in a single function call.

[td]

Many WIL extenders follow the design pattern Chuck eloquently described.  The WinInet, Wlan, FAF, and WinSock extenders are prime examples but there are others.  Some of the newer extenders take advantage of one or more C++ Standard Library algorithms to efficiently store and retrieve voluminous information associated with various handles.  Some even go way over the top and use a bit of finite mathematics to generate handles that are nonsequential integers but guaranteed to be unique no matter how many handles the user creates. 

[ChuckC]

Well that makes me feel like an idiot   :)     Just tested to make sure I was doing what you are talking about to make sure I understood.    Wish I had known that little bit of info many hours ago.

Thanks again.   This will simplify things A LOT!!!   Oh well, learned a lot about DllVarHandler() and some other things along the way so not a wasted trip.

Jim

To borrow a saying that my college Partial Differential Equations professor used frequently, "It is inherently obvious to the most casual observer..."

A lot functions are named with verb & noun components in their names.  Frequently, when there are pairs of operations that can be performed, you see verb pairs like add/remove, open/close, begin/end, start/stop, etc...

Think about AddExtender()... there's no corresponding RemoveExtender() function, although the WIL Extender Interface defines one interaction related to WIL itself shutting down and notifying the extender that it should release all resources that it allocated on behalf of itself or s script and has not previously released.

The implication is that once an extender is loaded into memory, it can't be unloaded.  And, it follows, that as long as the extender's DLL remains loaded in memory, then all memory that it allocates from the heap remains allocated until it is explicitly de-allocated.

Once you understand that, then a lot of possibilities open up w/respect to retaining state information across extender function calls.

[JTaylor]

Yes.  I have been emulating that behavior utilizing DllVarHandler() and fairly successfully but this, obviously, makes things so much easier.   This might be a good note for the top of the SDK in VERY BIG letters.   I am off to redo my last release.   This should also allow me to use a Vector so I don't have to do hokey stuff to get the row count.

Thanks again Tony and Chuck for shining a bit more light into this dark, dense thing I call a mind.

Jim

To borrow a saying that my college Partial Differential Equations professor used frequently, "It is inherently obvious to the most casual observer..."

A lot functions are named with verb & noun components in their names.  Frequently, when there are pairs of operations that can be performed, you see verb pairs like add/remove, open/close, begin/end, start/stop, etc...

Think about AddExtender()... there's no corresponding RemoveExtender() function, although the WIL Extender Interface defines one interaction related to WIL itself shutting down and notifying the extender that it should release all resources that it allocated on behalf of itself or s script and has not previously released.

The implication is that once an extender is loaded into memory, it can't be unloaded.  And, it follows, that as long as the extender's DLL remains loaded in memory, then all memory that it allocates from the heap remains allocated until it is explicitly de-allocated.

Once you understand that, then a lot of possibilities open up w/respect to retaining state information across extender function calls.

[td]

Note that extenders can be unloaded using IntControl 99.  Also, most extenders on the download page don't use the handle design pattern because they simply don't need to retain information between function calls.

[JTaylor]

Understood.   My CommControl extender was that way as well.   Although I am going to go back and take another look to see if there are any improvements I can make after the things I have recently learned in addition to bringing it up to the current SDK Format.   Thanks again.

Jim

Note that extenders can be unloaded using IntControl 99.  Also, most extenders on the download page don't use the handle design pattern because they simply don't need to retain information between function calls.

[ChuckC]

Note that extenders can be unloaded using IntControl 99.  Also, most extenders on the download page don't use the handle design pattern because they simply don't need to retain information between function calls.

I stand corrected on that point...

When did that particularly nifty IntControl() request quietly enter into existence?

And, does it use the same WIL Extender Interface mechanism that is used to notify an extender that WIL itself is shutting down?  Or is there a newer mechanism that must be used to properly release resources when IntControl(99, ...) is used to unload an extender?

[td]

IntControl 99 has been around since WinBatch version 2017A. It uses the same mechanism that is used during WIL termination.  That way the extender has every opportunity to clean up before it is unloaded from memory.  Of course, this adds emphasis to the extender being written to perform proper extender termination because the WinBatch/WBS/Popmenu... process is still executing.

[stanl]

Excellent Rabbit hole Jim :o

[JTaylor]

I started to apologize earlier for, apparently, hijacking your thread.   It has jumped my understanding of how the Extender works way ahead though so not overly sorry  :)

Jim

Excellent Rabbit hole Jim :o

[stanl]

I started to apologize earlier for, apparently, hijacking your thread.   It has jumped my understanding of how the Extender works way ahead though so not overly sorry  :)

Not my thread. Lucky enough to still be employed and have to get back to re-inventing easy use WB scripting into C#/Powershell.

[ChuckC]

Not my thread. Lucky enough to still be employed and have to get back to re-inventing easy use WB scripting into C#/Powershell.

I live deep in the world of c# these days, especially as used to extend the capabilities of PowerShell with a slew of dynamic / on-the-fly compilation of c# v8 code into in-memory assemblies as the backend support for a variety of PowerShell's "advanced functions" in script-based modules or in full-blown module assemblies.

If you find yourself stuck with the more intricate details of the intersection of c# & PowerShell, don't hesitate to hit me up with questions.

[stanl]

If you find yourself stuck with the more intricate details of the intersection of c# & PowerShell, don't hesitate to hit me up with questions.

I have used WB for simple dialog buttons to run PS via CLR and ObjectClrNew("System.Management.Automation.PowerShell"). Most recent involved executing PS code generated by WinSCP to load or retrieve files from a number of different SFTP sites. Could replace the WB dialog here: https://poshgui.com/Editor


but we are hi-jacking the thread at this point......