' ***********************************************************
' PROGRAM:  sql-mozart.bac
' PURPOSE:  demo of BaCon sqlite3 commands
' AUTHOR:       vovchik (Puppy Linux forum)     
' DEPENDS:  bash, bacon, slqite3
' PLATFORM: Puppy Linux (actually, any *nix)
' DATE:     14-05-2010
' VERSION:  0.1a
' ***********************************************************

' *****************
' INCLUDES
' *****************

INCLUDE "sqlite3.bac"

' *****************
' END INCLUDES
' *****************


' *****************
' DECLARATIONS
' *****************

GLOBAL MYSTR$[10] TYPE STRING

' *****************
' END DECLARATIONS
' *****************


' *****************
' SUBROUTINES
' *****************

'---------------
SUB PREPARE_DATA()
'---------------
    ' fill aray with data
    LOCAL STR1$
    STR1$ = "INSERT INTO Works(Koechel, Title, Date, Place) VALUES "
    MYSTR$[0] = CONCAT$(STR1$,"(24, '8 Variations in G on Laat ons Juichen', 'January 1766', 'The Hague');")
    MYSTR$[1] = CONCAT$(STR1$,"(25, 'Variations in D on Willem van Nassau', 'February 1766', 'Amsterdam');")
    MYSTR$[2] = CONCAT$(STR1$,"(26, 'Sonata in E-flat for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[3] = CONCAT$(STR1$,"(27, 'Sonata in G for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[4] = CONCAT$(STR1$,"(28, 'Sonata in C for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[5] = CONCAT$(STR1$,"(29, 'Sonata in D for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[6] = CONCAT$(STR1$,"(30, 'Sonata in F for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[7] = CONCAT$(STR1$,"(31, 'Sonata in B-flat for Violin and Keyboard', 'February 1766', 'The Hague');")
    MYSTR$[8] = CONCAT$(STR1$,"(32, 'Gallimathias Musicum (Quodlibet)', 'March 1766', 'The Hague');")
    MYSTR$[9] = CONCAT$(STR1$,"(33, 'Kyrie in F', 'June 1766', 'Paris');")
END SUB

'---------------
SUB CREATE_DB(STRING DBName$)
'---------------
    ' open sql database
    mydb = DB_OPEN(DBName$)
    ' create table
    DB_SQL(mydb, "CREATE TABLE Works(Koechel INTEGER, Title TEXT, Date TEXT, Place TEXT);")
END SUB

'---------------
SUB CREATE_RECORDS()
'---------------
        LOCAL i
    ' write data to sql database
    FOR i = 0 TO 9
        DB_SQL(mydb, MYSTR$[i])
    NEXT i
    DB_SQL(mydb, "COMMIT;")
END SUB

'---------------
SUB READ_RECORDS()
'---------------
    LOCAL i, j TYPE NUMBER
    LOCAL FieldName$ TYPE STRING
    ' read data from sql database and print to terminal
    res = DB_SQL(mydb, "SELECT * FROM Works;")
    IF res IS 0 THEN
        PRINT
        SPLIT CHOP$(DB_RESULT$) BY NL$ TO MYDATA$ SIZE RECORDS
        FOR i = 1 TO RECORDS - 1
            SPLIT CHOP$(MYDATA$[i]) BY CHR$(9) TO MYFIELDS$ SIZE FIELDS
            PRINT "Record no.: ", RIGHT$(CONCAT$("00",STR$(i)),2)
            FOR j = 0 TO FIELDS - 1
                SELECT j
                    CASE 0
                        FieldName$ = "Koechel Verzeichnis No.: "
                    CASE 1
                        FieldName$ = "Name of Composition: "
                    CASE 2
                        FieldName$ = "Composed during (date): "
                    CASE 3
                        FieldName$ = "Composed at (location): "
                END SELECT
                PRINT TAB$(1),LEFT$(CONCAT$(FieldName$,SPC$(10)),30), MYFIELDS$[j]
            NEXT j
            PRINT "-----"
        NEXT i
    ELSE
        PRINT DB_ERROR$
    ENDIF
    PRINT
END SUB

'---------------
SUB CLOSE_DB
'---------------
    ' clean up (close and delete database file)
    res = DB_CLOSE(mydb)
    DELETE FILE "mozart.sdb"
END SUB

' *****************
' END SUBROUTINES
' *****************


' *****************
' MAIN
' *****************

PREPARE_DATA
CREATE_DB("mozart.sdb")
CREATE_RECORDS
READ_RECORDS
CLOSE_DB
END

'---------------
' END MAIN
'---------------