/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
                    fishySQL.as
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
package {
    import flash.display.Sprite;
    import flash.display.StageAlign;
    import flash.display.StageScaleMode;
    import flash.events.MouseEvent;

    import qnx.ui.buttons.LabelButton;
    import qnx.ui.listClasses.ListSelectionMode;
    import qnx.ui.listClasses.TileList;
    import qnx.ui.text.Label;
    import qnx.ui.text.TextInput;

    [SWF(width="1024", height="600", backgroundColor="#424242", frameRate="30")]
    public class fishySQL extends Sprite {
        private var sqlData:SQLFunctions;

        private var inputUSERNAME:TextInput;
        private var inputUSERPASSWORD:TextInput;
        private var inputUSERDESCRIPTION:TextInput;

        private var userList:TileList;

        public function fishySQL() {
            super();

            // support autoOrients
            stage.align = StageAlign.TOP_LEFT;
            stage.scaleMode = StageScaleMode.NO_SCALE;

            sqlData = new SQLFunctions();
            theInputs();
        }
        private function theInputs():void {
            inputUSERNAME = new TextInput();
            inputUSERNAME.prompt = "User Name";
            inputUSERNAME.setPosition(65, 60);
            addChild(inputUSERNAME);

            inputUSERPASSWORD = new TextInput();
            inputUSERPASSWORD.prompt = "User Password";
            inputUSERPASSWORD.setPosition(65, 100);
            addChild(inputUSERPASSWORD);

            inputUSERDESCRIPTION = new TextInput();
            inputUSERDESCRIPTION.prompt = "User Description";
            inputUSERDESCRIPTION.setPosition(65, 140);
            addChild(inputUSERDESCRIPTION);

            var submitData:LabelButton = new LabelButton();
            submitData.label = "Save";
            submitData.setPosition(600, 60);
            submitData.addEventListener(MouseEvent.CLICK, writeData);
            addChild(submitData);

            var delData:LabelButton = new LabelButton();
            delData.label = "Delete";
            delData.setPosition(600, 110);
            delData.addEventListener(MouseEvent.CLICK, deleteData);
            addChild(delData);

            userList = new TileList();
            userList.dataProvider = sqlData.getTableData();
            userList.setPosition(65, 200);
            userList.setSize(894, 400);
            userList.columnCount = 3;
            userList.columnWidth = 300;
            userList.rowCount = sqlData.getCount();
            userList.rowHeight = 45;
            userList.cellPadding = 1;
            userList.selectionMode = ListSelectionMode.SINGLE;
            addChild(userList);
        }
        private function writeData(e:MouseEvent):void {
            sqlData.writeData(inputUSERNAME.text, inputUSERPASSWORD.text, inputUSERDESCRIPTION.text);
            inputUSERNAME.text = "";
            inputUSERPASSWORD.text = "";
            inputUSERDESCRIPTION.text = "";
        }
        private function deleteData(e:MouseEvent):void {
            sqlData.removeRecord(userList.selectedIndex);
        }
    }
}

/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
                    SQLFunctions.as
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
package {
    import flash.data.SQLConnection;
    import flash.data.SQLResult;
    import flash.data.SQLStatement;
    import flash.filesystem.File;

    import qnx.ui.data.DataProvider;

    public class SQLFunctions {
        private var dbName:String = "fishySQL.db";
        private var dbFile:File = File.applicationStorageDirectory.resolvePath(dbName);
        private var dbConn:SQLConnection;
        private var dbSTMT:SQLStatement;
        private var sqlResult:SQLResult;

        private var databaseDP:DataProvider;

        private var totalRows:int;

        public function SQLFunctions() {
            if (!dbFile.exists) {
                createDB()
            }
        }
        public function createDB():void {
            createConnection();
            trace("FIRST RUN!")
            dbSTMT.text = "CREATE TABLE IF NOT EXISTS USERTABLE (USER_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "USERNAME TEXT, " +
                "USERPASSWORD TEXT, " +
                "USERDESCRIPTION TEXT" +
                ")";
            dbSTMT.execute();
            dbSTMT.text = "INSERT INTO USERTABLE (" +
                "USERNAME, " +
                "USERPASSWORD, " +
                "USERDESCRIPTION" +
                ") VALUES (?, ?, ?)";
            dbSTMT.parameters[0] = "John Doe";
            dbSTMT.parameters[1] = "p455w0rd";
            dbSTMT.parameters[2] = "PW IS BAD!";
            dbSTMT.execute();
            closeConnection();
        }
        public function createConnection():void {
            dbConn = new SQLConnection();
            dbConn.open(dbFile);
            dbSTMT = new SQLStatement();
            dbSTMT.sqlConnection = dbConn;
        }
        public function closeConnection():void {
            dbConn.close();
        }
        public function getTableData():DataProvider {
            createConnection();
            dbSTMT.text = "SELECT * FROM USERTABLE";
            dbSTMT.execute();
            sqlResult = dbSTMT.getResult();
            totalRows = sqlResult.data.length;
            databaseDP = new DataProvider();
            for each(var obj:Object in sqlResult.data){
                if(obj != null){
                    databaseDP.addItem({label:obj["USERNAME"]});
                    databaseDP.addItem({label:obj["USERPASSWORD"]});
                    databaseDP.addItem({label:obj["USERDESCRIPTION"]});
                }
            }
            return databaseDP;
        }
        public function getCount():int {
            return totalRows;
        }
        public function writeData(inputUN:String, inputUP:String, inputUD:String):void {
            createConnection();
            dbSTMT.text = "INSERT INTO USERTABLE (" +
                "USERNAME, " +
                "USERPASSWORD, " +
                "USERDESCRIPTION" +
                ") VALUES (?, ?, ?)";
            dbSTMT.parameters[0] = inputUN;
            dbSTMT.parameters[1] = inputUP;
            dbSTMT.parameters[2] = inputUD;
            dbSTMT.execute();
            closeConnection();
        }
        public function removeRecord(delID:int):void {
            trace("I GOT HERE!");
            createConnection();
            trace(dbSTMT.text = "DELETE FROM USERTABLE WHERE USER_ID = " + delID + "");
            dbSTMT.execute();
            closeConnection();

        }
    }
}