extending SQL

[Chris Holbrook]

I use SQlite more than native PB data structures, because it allows complex queries with little coding effort - I have a lot of mileage with SQL. A few years ago I started driving forms and listviews (report style) directly from SQL queries, adding the extra information inside column aliases, then preprocessing the adulterated SQL to extract the extra info, which gave the listbox column width, column title, and alignment, and located textboxes in the form, and so on.  To cut a long story short, once conditionality is added to the extra information, whole applications can be coded in a single query - which has become a tree of queries. But the more I add to this framework, the more thousands of lines of code I have to maintain.

I wonder if there is an existing scripting language which might do this better?

[John Spikowski]

I wonder if there is an existing scripting language which might do this better?

If you're feeling a little dangerous, you could always give ScriptBasic a try. (Window/Linux/Android)

Code Select Expand


import sqlite.bas

db = sqlite::open("sqlite3.db")

sqlite::execute(db,"create table demo (someval integer, sometxt text);")
sqlite::execute(db,"insert into demo values (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")

stmt = sqlite::query(db,"SELECT * FROM demo")

while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
  if sqlite::fetchhash(stmt,column) then
    print column{"someval"},"\t-\t",column{"sometxt"},"\n"
  end if
wend

sqlite::close(db)


jrs@laptop:~/sb/sb22/test$ scriba testsqlite3.sb
123   -   hello
234   -   cruel
345   -   world
jrs@laptop:~/sb/sb22/test$

FYI The Sqlite3 ScriptBasic extension module has no dependencies. Very handy on the Android platform where SQLite is under the control of the Java VM.

[Chris Holbrook]

John, a few quick questions, does Scriptbasic support Collections? Does it include an XML parsing library? Is there a single GUI component with is well supported on Windows and ~UX platforms which can be used successfully with it? Thanks, Chris

[John Spikowski]

ScriptBasic supports two fetch methods. The example I posted uses fetchhash() which returns the row in an associative array with the column names you assigned on creation as the hash key. (single table only)  FetchArray() on the other hand returns the resulting row in an element array as the query may have been with joins involving multiple tables.

Does it include an XML parsing library?

No but I have written a XML parser in SB in about 10 lines of code. (see below) The T (Tools) SB extension module has a function to turn an array (associative, element or a combination of both) into a XML string describing its structure. I haven't looked if there is an add-on library for SQLite that would support a XML interface. ScriptBasic also has an extension module for Mini-XML Armando wrote.

mxml.sb

Code Select Expand


IMPORT t.bas

xml = t::LoadString("shiporder.xml")

SPLITA xml BY ">" TO a

FOR x = 0 TO UBOUND(a)
  IF LEFT(TRIM(a[x]),1) = "<" THEN GOTO IT
  p = INSTR(a[x],"<")
  PRINT MID(a[x],p+2) & " = " & LEFT(a[x],p-1),"\n"
IT:
NEXT


shiporder.xml

Code Select Expand


<?xml version="1.0" encoding="ISO-8859-1"?>

<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
  <orderperson>John Smith</orderperson>
  <shipto>
    <name>Ola Nordmann</name>
    <address>Langgt 23</address>
    <city>4000 Stavanger</city>
    <country>Norway</country>
  </shipto>
  <item>
    <title>Empire Burlesque</title>
    <note>Special Edition</note>
    <quantity>1</quantity>
    <price>10.90</price>
  </item>
  <item>
    <title>Hide your heart</title>
    <quantity>1</quantity>
    <price>9.90</price>
  </item>
</shiporder>


Results

jrs@laptop:~/sb/sb22/test$ scriba mxml.sb
orderperson = John Smith
name = Ola Nordmann
address = Langgt 23
city = 4000 Stavanger
country = Norway
title = Empire Burlesque
note = Special Edition
quantity = 1
price = 10.90
title = Hide your heart
quantity = 1
price = 9.90



=
jrs@laptop:~/sb/sb22/test$





I use phpMyAdmin for most of my GUI MySQL DB definition work. I work with Amazon EC2 (headless) servers and a HTTP GUI interface works out well. When in a console I use the Sqlite console utility.

Update

http://www.sqlitemanager.org/

[Chris Holbrook]

Thanks for those. It looks as if ScriptBasic has the building blocks, but no convincing advantage, which is what would be needed to prise me away from PowerBASIC for current work. Also I don't think that doing the GUI in HTML would produce an acceptable result, viz, keystroke compatible with the current stuff, developed in MSDOS days.

The GUI which I am using (to write what looks and to some extent is coded like a character mode app) is PowerBASIC'c GRAPHIC one, a wrapper for GDI. I don't know how to migrate that to ~UX, except by rewriting the UI for a different graphics library, one which I can count on being available both on Windows and ~UX for the next ten years.

[Patrice Terrier]

Chris--

OpenGL is the way to go.

You can work in 2D mode with it, and that would work on all OS.

[Chris Holbrook]

Thanks Patrice, I shall give it a spin.

[John Spikowski]

It looks as if ScriptBasic has the building blocks, but no convincing advantage, which is what would be needed to prise me away from PowerBASIC for current work.

Could you help me understand your comment better by posting an example of this ScriptBasic code in PowerBASIC?

Code Select Expand


import sqlite.bas

db = sqlite::open("sqlite3.db")

sqlite::execute(db,"create table demo (someval integer, sometxt text);")
sqlite::execute(db,"insert into demo values (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")

stmt = sqlite::query(db,"SELECT * FROM demo")

while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
  if sqlite::fetchhash(stmt,column) then
    print column{"someval"},"\t-\t",column{"sometxt"},"\n"
  end if
wend

sqlite::close(db)


[José Roca]

Something similar:

Code Select Expand


' ########################################################################################
' Microsoft Windows
' File: CSQLITE_Step.bas
' Contents: CSQLite class example
' Connects to a database and reads records.
' Copyright (c) 2012 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CSQLite.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pSQL AS ISQLite
   pSQL = CLASS "CSQLite"
   IF ISNOTHING(pSQL) THEN EXIT FUNCTION

   ' // Create a connection object
   LOCAL pDbc AS ISQLiteConnection
   pDbc = pSQL.Connection
   IF ISNOTHING(pDbc) THEN EXIT FUNCTION

   TRY
      ' // Delete our test database if it exists
      IF ISFILE(EXE.PATH$ & "Test.sdb") THEN KILL EXE.PATH$ & "Test.sdb"
      ' // Create a new database
      pDbc.OpenDatabase(EXE.PATH$ & "Test.sdb")
      ' // Create a table
      pDbc.Exec("CREATE TABLE t (xyz text)")
      ' // Insert rows
      pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')")
      pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')")
'      ' // Prepare a query
      LOCAL pStmt AS ISQLiteStatement
      pStmt = pDbc.Prepare("SELECT * FROM t")
      ? "Column count:" & STR$(pStmt.ColumnCount)
      ' // Read the column names and values
      LOCAL i AS LONG
      DO
         ' // Fetch rows of the result set
         IF pStmt.Step = %SQLITE_DONE THEN EXIT DO
         ' // Read the columns and values
         FOR i = 0 TO pStmt.ColumnCount- 1
            ? pStmt.ColumnName(i)
'            ? pStmt.ColumnText(i)
            ? pStmt.ColumnText("xyz")
         NEXT
      LOOP
   CATCH
     ' // Display error information
      ? pSql.OleErrorInfo
   END TRY

   ' // Cleanup
   pStmt = NOTHING   ' // Deletes the prepared statement
   pDbc = NOTHING    ' // Closes the database
   pSQL = NOTHING

   #IF %DEF(%PB_CC32)
      WAITKEY$
   #ENDIF

END FUNCTION
' ========================================================================================


[John Spikowski]

Thanks José for the PB code example. I'm not sure how this solves Chris's original request of a cross platform SQLite scripting solution with a GUI admin facility. The SB code I posted runs untouched on Windows, Linux, OSX and Android. The GUI (browser based) SQLite administrative tool also runs on these platforms with no new learning curve. With Chrome and FireFox OS just around the corner, shying away from browser solutions could be fatal.

@Chris - Can you explain again how PB solved your original request?

[Chris Holbrook]

@Chris - Can you explain again how PB solved your original request?

Let's avoid any mistunderstanding here. What I'm looking for is a scripting language which extends SQL in the direction of the UI. I have a feeling that it doesn't exist, possibly because it would have to assume that the scripting engine and the GUI were aware of each other and could communicate. I don't know how that might be done other than by writing code, which is what I have done so far using PowerBASIC.

As to how I code it, I have put an early example using the technique in the public domain http://www.powerbasic.com/support/pbforums/showthread.php?t=44995. Here is a method from it which shows where I was three years ago:

Code Select Expand

'-------------------------------------------------------------------------
            ' set up listview columns from an array of pointers to asciz text
            ' parameters are
            '     1) listview handle
            '     2) pointer to asciz ptr array each member of which points to a column name asciz string
            '        NB this is the format in which SQLite results are returned
            '     3) number of columns required
            '     4) number of rows in the result set = number of items in the list view
            '
            ' The SQL column names have been parasitized to include column formatting info.
            ' A separator character (defined as $mySQLseparator) is used to separate the
            ' column title itself from an alignment indicator and a column width in pixels.
            ' defaults are left alighnment and column width = title width.
            ' An example of syntax would be SELECT STUFF "Mystuff|L|200" FROM MYTABLE,
            ' resulting in a left-aligned column 200 pixels wide
            ' NB Windows rule is that the leftmost column must be left-aligned
            method LVStart
                local tLVC   as LV_COLUMN
                local tLVI   as LV_ITEM
                local s as string
                local i, l as long

                redim pSz(0 to ncols-1) as local asciz ptr at pResults
                ' Get ListView Handle
                l = ListView_GetExtendedListViewStyle(hLV)
                ListView_SetExtendedListViewStyle (hLV, l or %lvs_ex_gridlines or %lvs_ex_fullrowselect)
                tLVC.mask    = %LVCF_FMT or %LVCF_TEXT or %LVCF_SUBITEM or %LVCF_WIDTH
                tLVC.fmt     = %LVCFMT_LEFT
                ' Insert column headers
                for i = 0 to ncols -1
                    s = @pSz(i)
                    if parsecount(s, $mySQLseparator) > 1 then
                        select case as const$ parse$(s, $mySQLseparator, 2)
                            case "L": tLVC.fmt = 0
                            case "R": tLVC.fmt = 1
                            case "C": tLVC.fmt = 2
                        end select
                        s = parse$(s,$mySQLseparator,1)
                    end if
                    tLVC.pszText = strptr(s)
                    tLVC.iOrder = i
                    l = ListView_InsertColumn(hLV, i , tLVC)
                next
                ' set column widths
                for i = 0 to ncols -1
                   s = @pSz(i)
                   if parsecount(s,$mySQLseparator) > 2 then
                       ListView_SetColumnWidth(hLV, i, val(trim$(parse$(s,$mySQLseparator,3))))
                   else
                       ListView_SetColumnWidth(hLV, i, %LVSCW_AUTOSIZE_USEHEADER)
                   end if
                next
                listview_setitemcountex(hLV, nrows+1, %LVSICF_NOINVALIDATEALL )
            end method

Currently I generate the code to drive a listview and associated form from a SQL query. By including additional metadata in the SQL, drilldowns and child forms can also be done by the generated application.

As to how  would code your example in PB, my code would look very like Jose's, but my generated code uses the sqlite3_exec wrapper rather than prepare, step, etc. And, of course, I call my classes methods, not Jose's, but that is just an accident of history.

Having a well-defined and stable syntax, SQL provides opportunities for embellishment. The benefit to the coder is (IMO) that the embellished verion is much more weildy than either a separate, parallel structure for the extra information, or a container like XML for the two components.

[John Spikowski]

What I'm looking for is a scripting language which extends SQL in the direction of the UI.

Did you check out the IUP cross platform extension module for SB? Easy SQLite & GUI API sounds to me like what you were looking for.

FWIW  SB translates it's scripts to C source if a standalone application is needed with the option of static linking extension modules.

You don't have to leave the comfort of your PB compiler if you embed SB to do the no brainer work. (something PBCC GUIless folks have to contend with)

[José Roca]

PBCC has not DDT, but you can do GUI applications with it using straight SDK programming or using my CWindow class. In fact, I use PBCC more often than PBWin.

[John Spikowski]

Very cool, I didn't know that.

[John Spikowski]

Will the CWindows class work with PBCC 5?