' ***********************************************************
' 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()
'---------------
        ' 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
'---------------