Friday, October 7, 2011

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

In this tutorial we will work on the Backup feature - make it possible to save, store and load backup entries.

Here's how the mechanism works - all the backup entries are stored in a separate database, called backups. It will have these columns/attributes: date, entries and data. The date one will hold a string value of the date that the backup was made on, the entries property is an integer that holds the number of total entries in the backup entry. The data property holds a BLOB value of the data object for this backup.

First we need to create this new database. Create a new SQLConnection object, we will call it connection2:

private var connection2:SQLConnection;

We'll also need an ArrayCollection to store the data we will extract from this database. We store it in a variable that's similar to the existing listData, backupData:

[Bindable]
private var backupData:ArrayCollection = new ArrayCollection();

We can now apply this backupData variable to the dataProvider of our datagrid in the backup window:

<mx:DataGrid id="backupGrid" width="480" height="170" dataProvider="{backupData}">

Now we can get to actually creating the database. In this init() function, add this:

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

You can see that it is very similar to what we already have for the database.db database. The onOpenBackup() function is also very similar to onOpen():

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);
}

Notice that we don't set a responder in the last function. This is because we only want to read from the database when we need it - and that is when the backup window is opened.

Go to the openBackup() function, and add a SQL command that selects all the data from the database and has updateBackups() function as responder:

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

The updateBackups function is simple:

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

We need to add functionality to the load button on our backup window. First make it so that it is only enabled when an item is selected in the data grid, and make it call doLoadBackup() on click:

<s:Button enabled="{backupGrid.selectedItems.length>0}" label="Load selected backup file" click="doLoadBackup()"/>

The doLoadBackup() function needs to delete everything from the existing database first.

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

And then fill the contact database with new entries from the backup:

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();
}

Notice that the object we are getting information from is an Object. I mentioned this before, when saying that it is stored as a BLOB data in the 'data' property in the backups database. The BLOB type means that the data is stored as it is, in our case - an Object.

Now we need to add functionality for the Create backup button!

We have an existing createBackup() function, let's populate it with code. First we need to select all data from the existing contact database:

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));
}

Then we insert all this data into the backups database:

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");
}

It is all done! The loading, storing and saving works. The only thing we still need to do is the limit feature. Here is 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);
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");
}

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);
}

]]>
</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}" /></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>

We'll continue in the next tutorial.

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 14
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