Saturday, October 8, 2011

Creating a contact book using Flex, AIR and SQLite database: Part 14

In this tutorial we will start working on the backup limiting functionality.

The first thing we need to do is make the existing backupLimit variable and the numericStepper (which the user can use to set the limit) two-way data binded. This basically means that whenever backupLimit variable is changed, the value in numericStepper is changed too, and when the value in numericStepper is changed, it is also changed for backupLimit.

To apply dual binding, instead of just setting NumericStepper's value to {backupLimit}, set it to @{backupLimit}. It's that simple.

<s:NumericStepper id="backupStepper" minimum="1" maximum="10" value="@{backupLimit}" change="checkBackupLimit();" />

You can also see that I set the 'change' property of the stepper to a checkBackupLimit() function. This will be the function that will actually delete the oldest backup(s) when needed. This is not the only time when we call this function. Call the same function in the end of your doBackup() function:

private function doBackup(evt:SQLResult):void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "INSERT INTO backups (date, entries, data) VALUES (@date, @entries, @data)";
stat.parameters["@date"] = new Date().toDateString();
stat.parameters["@entries"] = evt.data.length;
stat.parameters["@data"] = evt.data;
stat.execute( -1);
Alert.show("Backup file (" + new Date().toDateString() + ") successfully created!", "Database backup");
checkBackupLimit();
}

Now the check will be done when a) the user changes the limit and b) the user creates a new backup.

Let's get started with this checkBackupLimit function. The first thing we want to do is select all the id's from backups.

private function checkBackupLimit():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "SELECT id FROM backups";
stat.execute( -1, new Responder(countBackups));
}

The responder is a countBackups function. Here, you need to be careful and pay attention. This is the function that will work with what information we have - the array of IDs of backups - and it will need to decide, should it or should it not delete any backups. If it should - how many, and which ones. Remember, that the oldest backups have the smallest IDs. This means we can search the ID array for the smallest value and remove that. If we have multiple backups to delete (for example, if limit is 3, and we have 5 backups in total - the code will have to remove the oldest 2 backups), the code will need to find the lowest ID in the array, remove the backup with that ID, then (!) remove this id from the array (we deleted it already, we need to make sure the code won't try to delete it again) and perform the check for the minimal value again to delete the next oldest backup. Repeat this process as many times as needed.

All these words in code:

private function countBackups(evt:SQLResult):void {
if (evt.data.length > backupLimit) {
var ids:Array = evt.data;
var times:int = evt.data.length - backupLimit;
for (var i:int = 0; i < times; i++) {
var min:int = getMin(ids);
deleteBackup(min);
deleteValFromIDarray(ids, min);
}
}
doUpdateBackups();
}

private function getMin(arr:Array):int {
var min:int = arr[0].id;
for (var mi:int = 0; mi<arr.length; mi++)
{
if (arr[mi].id < min)
{
min = arr[mi].id;
}
}
return min;
}

private function deleteValFromIDarray(arr:Array, val:int):void {
for (var i:int = 0; i < arr.length; i++) {
if (arr[i].id == val) {
arr.splice(i, 1); 
break; 
}
}
}

private function deleteBackup(theid:int):void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "DELETE FROM backups WHERE id=" + theid;
stat.execute( -1);
}

The full code:

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
                       xmlns:s="library://ns.adobe.com/flex/spark"
                       xmlns:mx="library://ns.adobe.com/flex/mx" 
   width="850" height="560"
   creationComplete="init();"
   title="Flex contact book"
   >
   
<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.events.Event;
import flash.events.SQLEvent;
import flash.data.SQLMode;
import flash.filesystem.File;
import flash.filesystem.FileStream;
import flash.geom.Rectangle;
import flash.net.FileFilter;
import flash.net.Responder;
import flash.data.SQLStatement;
import flash.printing.PrintJob;
import mx.collections.ArrayCollection;
import mx.collections.Sort;
import mx.controls.advancedDataGridClasses.AdvancedDataGridColumn;
import mx.controls.Alert;
import flash.data.SQLResult;
import mx.controls.dataGridClasses.DataGridColumn;
import mx.printing.FlexPrintJob;
import mx.printing.PrintDataGrid;
import mx.validators.Validator;
import mx.core.FlexGlobals;
import flash.filesystem.FileMode;
import mx.managers.PopUpManager;

private var connection:SQLConnection;
private var connection2:SQLConnection;
[Bindable]
private var listData:ArrayCollection = new ArrayCollection();
[Bindable]
private var backupData:ArrayCollection = new ArrayCollection();
private var receivedXML:XML = new XML();
[Bindable]
private var backupLimit:int = 3;

private function init():void {

var dbFile:File = File.applicationStorageDirectory.resolvePath("database.db");
connection = new SQLConnection();
connection.addEventListener(SQLEvent.OPEN, onOpen);
connection.openAsync(dbFile, SQLMode.CREATE);

var db2File:File = File.applicationStorageDirectory.resolvePath("backups.db");
connection2 = new SQLConnection();
connection2.addEventListener(SQLEvent.OPEN, onOpenBackup);
connection2.openAsync(db2File, SQLMode.CREATE);
}

private function onOpen(evt:SQLEvent):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, phone TEXT, email TEXT)";
stat.execute(-1, new Responder(selectItems));
}

private function onOpenBackup(evt:SQLEvent):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "CREATE TABLE IF NOT EXISTS backups (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, entries INTEGER, data BLOB)";
stat.execute( -1);
}

private function selectItems(evt:SQLResult):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function onSelected(evt:SQLResult):void {
var tempSort:Sort;
if (listData!=null) {
tempSort = listData.sort;
}
listData = new ArrayCollection(evt.data);
listData.sort = tempSort;
listData.refresh();
}

private function createNew():void {
if(fieldsCheck()){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone, email) VALUES (@fname, @lname, @phone, @email)";
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute( -1, new Responder(selectItems));
}
}

private function onChange():void {
b_save.enabled = true;
b_delete.enabled = true;
t_fname.text = contactList.selectedItems[0].fname;
t_lname.text = contactList.selectedItems[0].lname;
t_phone.text = contactList.selectedItems[0].phone;
t_email.text = contactList.selectedItems[0].email;
if (contactList.selectedItems.length > 1) {
b_save.enabled = false;
}
}

private function onDelete():void {
for (var i:int = 0; i < contactList.selectedItems.length; i++){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts WHERE id=" + contactList.selectedItems[i].id;
stat.execute( -1, new Responder(selectItems));
}
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}

private function onSave():void {
if (fieldsCheck()) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "UPDATE contacts SET fname=@fname, lname=@lname, phone=@phone, email=@email WHERE id=" + contactList.selectedItems[0].id;
stat.parameters["@fname"] = t_fname.text;
stat.parameters["@lname"] = t_lname.text;
stat.parameters["@phone"] = t_phone.text;
stat.parameters["@email"] = t_email.text;
stat.execute( -1, new Responder(selectItems));
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}
}

private function clearFields():void {
t_fname.text = t_lname.text = t_phone.text = t_email.text = "";
}

private function fieldsCheck():Boolean {
var validatorErrors:Array = Validator.validateAll(nameValidators);
if (validatorErrors.length > 0) {
Alert.show("You have to fill in first name, last name and at least 1 other contact field!", "Oops!");
return false;
}else if (valid_phone.validate(t_phone.text).type != "valid" && valid_email.validate(t_email.text).type != "valid") {
Alert.show("You have to fill in at least 1 contact field, other name first name and last name! (You need to have at least 3 fields filled in, including first name and last name.)", "Oops!");
return false;
}else{
return true;
}
}

private function doSearch():void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts WHERE fname LIKE @search OR lname LIKE @search OR phone LIKE @search OR email LIKE @search ORDER BY id;";
stat.parameters["@search"] = "%" + t_search.text + "%";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function showAll():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(onSelected));
b_save.enabled = false;
b_delete.enabled = false;
}

private function printData(dataP:Object):void{
var pJob:FlexPrintJob = new FlexPrintJob();
var pGrid:PrintDataGrid = new PrintDataGrid();

var col1:DataGridColumn = new DataGridColumn();
col1.dataField = "fname";
col1.headerText = "First name";
var col2:DataGridColumn = new DataGridColumn();
col2.dataField = "lname";
col2.headerText = "Last name";
var col3:DataGridColumn = new DataGridColumn();
col3.dataField = "phone";
col3.headerText = "Phone";
var col4:DataGridColumn = new DataGridColumn();
col4.dataField = "email";
col4.headerText = "Email";

if (pJob.start()) {
addElement(pGrid);

pGrid.width = pJob.pageWidth;
pGrid.height = pJob.pageHeight;

pGrid.dataProvider = dataP;
pGrid.columns = [col1, col2, col3, col4];

pJob.addObject(pGrid);

pJob.send();

removeElement(pGrid);
}
}

private function exportXML():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(doExport));
}

private function doExport(evt:SQLResult):void {
var xmlStr:String = "";
xmlStr += "<?xml version='1.0' encoding='UTF-8'?>\n";
xmlStr += "<contacts>\n"
for (var i:int = 0; i < evt.data.length; i++) {
xmlStr += "<contact fname='" + evt.data[i].fname + "' lname='" + evt.data[i].lname + "' phone='" + evt.data[i].phone + "' email='" + evt.data[i].email + "'/>\n"
}
xmlStr += "</contacts>\n"

var myFile:File = new File();
myFile.browseForSave("Save XML file:");
myFile.addEventListener(Event.SELECT, fileSave);

function fileSave(evt:Event):void {
myFile.nativePath+=".xml"
var stream:FileStream = new FileStream();
stream.open(myFile, FileMode.WRITE);
stream.writeUTFBytes(xmlStr);
stream.close();
}
}

private function importXML():void{
var myFile:File = new File();
myFile.browseForOpen("Import XML file", [new FileFilter("XML Files", "*.xml")]);
myFile.addEventListener(Event.SELECT, fileLoad);

function fileLoad(evt:Event):void{
if(myFile.extension=="xml"){
var stream:FileStream = new FileStream();
stream.open(myFile, FileMode.READ);
receivedXML = XML(stream.readUTFBytes(stream.bytesAvailable));
stream.close();

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts";
stat.execute( -1, new Responder(handleXML));
}else {
Alert.show("You can only import XML files!", "Error");
}
}
}

private function handleXML(evt:SQLResult):void {
for each(var cont:Object in receivedXML.contact) {
insertXML(cont.@fname, cont.@lname, cont.@phone, cont.@email);
}
showAll();
}

private function insertXML(fn:String, ln:String, ph:String, em:String):void{
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO contacts (fname, lname, phone, email) VALUES (@fname, @lname, @phone, @email)";
stat.parameters["@fname"] = fn;
stat.parameters["@lname"] = ln;
stat.parameters["@phone"] = ph;
stat.parameters["@email"] = em;
stat.execute( -1, new Responder(selectItems));
}

private function openBackup():void {
PopUpManager.addPopUp(backupWindow, this, true);
PopUpManager.centerPopUp(backupWindow);
doUpdateBackups();
}

private function doUpdateBackups():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "SELECT id, date, entries, data FROM backups"
stat.execute( -1, new Responder(updateBackups));
}

private function createBackup():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT id, fname, lname, phone, email FROM contacts ORDER BY id";
stat.execute( -1, new Responder(doBackup));
}

private function doBackup(evt:SQLResult):void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "INSERT INTO backups (date, entries, data) VALUES (@date, @entries, @data)";
stat.parameters["@date"] = new Date().toDateString();
stat.parameters["@entries"] = evt.data.length;
stat.parameters["@data"] = evt.data;
stat.execute( -1);
Alert.show("Backup file (" + new Date().toDateString() + ") successfully created!", "Database backup");
checkBackupLimit();
}

private function doLoadBackup():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM contacts";
stat.execute( -1, new Responder(insertBackup));
}

private function insertBackup(evt:SQLResult):void {
for each(var cont:Object in backupGrid.selectedItem.data) {
insertXML(cont.fname, cont.lname, cont.phone, cont.email);
}
showAll();
closeBackup();
}

private function closeBackup():void{
PopUpManager.removePopUp(backupWindow);
}

private function updateBackups(evt:SQLResult):void {
backupData = new ArrayCollection(evt.data);
}

private function checkBackupLimit():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "SELECT id FROM backups";
stat.execute( -1, new Responder(countBackups));
}

private function countBackups(evt:SQLResult):void {
if (evt.data.length > backupLimit) {
var ids:Array = evt.data;
var times:int = evt.data.length - backupLimit;
for (var i:int = 0; i < times; i++) {
var min:int = getMin(ids);
deleteBackup(min);
deleteValFromIDarray(ids, min);
}
}
doUpdateBackups();
}

private function getMin(arr:Array):int {
var min:int = arr[0].id;
for (var mi:int = 0; mi<arr.length; mi++)
{
if (arr[mi].id < min)
{
min = arr[mi].id;
}
}
return min;
}

private function deleteValFromIDarray(arr:Array, val:int):void {
for (var i:int = 0; i < arr.length; i++) {
if (arr[i].id == val) {
arr.splice(i, 1); 
break; 
}
}
}

private function deleteBackup(theid:int):void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection2;
stat.text = "DELETE FROM backups WHERE id=" + theid;
stat.execute( -1);
}


]]>
</fx:Script>

<fx:Declarations>
<s:TitleWindow id="backupWindow" width="500" height="370" close="closeBackup();" title="Database backup">
<s:VGroup x="10" y="10" width="480" height="350">
<s:Label width="480" text="Here you can load database backups that were previously saved. Once loaded, all existing contacts in the database will be overwritten with the data as it was when the backup was made."/>
<mx:DataGrid id="backupGrid" width="480" height="170" dataProvider="{backupData}">
<mx:columns>
<mx:DataGridColumn headerText="Creation date" dataField="date" />
<mx:DataGridColumn headerText="Amount of contacts" dataField="entries" />
</mx:columns>
</mx:DataGrid>
<s:Button enabled="{backupGrid.selectedItems.length>0}" label="Load selected backup file" click="doLoadBackup()"/>
<s:Label width="480" text="You can set a limit to backup entries that can be stored at the same time. If the limit is exceeded, the oldest entry is deleted, creating room for a new backup entry."/>
<mx:FormItem label="Backup limit"><s:NumericStepper id="backupStepper" minimum="1" maximum="10" value="@{backupLimit}" change="checkBackupLimit();" /></mx:FormItem>
</s:VGroup>
</s:TitleWindow>

<fx:Array id="nameValidators">
<mx:StringValidator source="{t_fname}" property="text" required="true" />
<mx:StringValidator source="{t_lname}" property="text" required="true" />
</fx:Array>
<mx:PhoneNumberValidator id="valid_phone" source="{t_phone}" property="text" required="true" />
<mx:EmailValidator id="valid_email" source="{t_email}" property="text" required="true" />
</fx:Declarations>


<s:HGroup width="500">
<mx:AdvancedDataGrid id="contactList" width="570" height="520" dataProvider="{listData}" change="onChange();" allowMultipleSelection="true">
<mx:columns>
<mx:AdvancedDataGridColumn dataField="fname" headerText="First name"/>
<mx:AdvancedDataGridColumn dataField="lname" headerText="Last name"/>
<mx:AdvancedDataGridColumn dataField="phone" headerText="Phone"/>
<mx:AdvancedDataGridColumn dataField="email" headerText="Email"/>
</mx:columns>
</mx:AdvancedDataGrid>
<s:VGroup horizontalAlign="center">
<mx:Form>
<mx:FormItem label="First name" required="true"><s:TextInput id="t_fname"/></mx:FormItem>
<mx:FormItem label="Last name" required="true"><s:TextInput id="t_lname"/></mx:FormItem>
<mx:FormItem label="Phone"><s:TextInput id="t_phone" restrict="0-9 " /></mx:FormItem>
<mx:FormItem label="Email"><s:TextInput id="t_email"/></mx:FormItem>
<mx:FormItem>
<s:Button id="b_new" label="Create new item" click="createNew();" />
<s:Button id="b_clear" label="Clear fields" click="clearFields();" />
<s:Button id="b_save" label="Save changes" enabled="false" click="onSave();" />
<s:Button id="b_delete" label="Delete selected" enabled="false" click="onDelete();" />
</mx:FormItem>
<mx:FormItem/>
<mx:FormItem label="Search"><s:TextInput id="t_search"/></mx:FormItem>
<mx:FormItem>
<s:HGroup>
<s:Button id="b_search" label="Search" click="doSearch();" />
<s:Button id="b_showall" label="Show all" click="showAll();" />
</s:HGroup>
</mx:FormItem>
<mx:FormItem/>
<mx:FormItem label="Printing">
<s:Button label="Print selected" enabled="{(contactList.selectedItems.length>0)?true:false}" click="printData(contactList.selectedItems);" />
<s:Button label="Print all" click="printData(contactList.dataProvider);" />
</mx:FormItem>
<mx:FormItem/>
<mx:FormItem label="Data transfer">
<s:Button label="Export contacts" click="exportXML();" />
<s:Button label="Import contacts" click="importXML();" />
<s:Button label="Create backup" click="createBackup();" />
<s:Button label="Load backup" click="openBackup();" />
</mx:FormItem>
</mx:Form>
</s:VGroup>
</s:HGroup>

</s:WindowedApplication>

There are still some little things left to do, such as manage the saving of the backupLimit variable, preventing errors, overwriting databases, etc. We will cover these features in the next tutorials.

Thanks for reading!

Related:

Creating a contact book using Flex, AIR and SQLite database: Part 1
Creating a contact book using Flex, AIR and SQLite database: Part 2
Creating a contact book using Flex, AIR and SQLite database: Part 3
Creating a contact book using Flex, AIR and SQLite database: Part 4
Creating a contact book using Flex, AIR and SQLite database: Part 5
Creating a contact book using Flex, AIR and SQLite database: Part 6
Creating a contact book using Flex, AIR and SQLite database: Part 7
Creating a contact book using Flex, AIR and SQLite database: Part 8
Creating a contact book using Flex, AIR and SQLite database: Part 9
Creating a contact book using Flex, AIR and SQLite database: Part 10
Creating a contact book using Flex, AIR and SQLite database: Part 11
Creating a contact book using Flex, AIR and SQLite database: Part 12
Creating a contact book using Flex, AIR and SQLite database: Part 13
Creating a contact book using Flex, AIR and SQLite database: Part 15
Creating a contact book using Flex, AIR and SQLite database: Part 16

No comments:

Post a Comment