Sunday, October 9, 2011

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

In this tutorial we will do 2 things to our contact book application - make the program remember the backup limit that the user has set, and fix special character issue when importing and exporting XML files.

The first thing we're going to do is make sure that the backupLimit variable saves locally. This will be done using a SharedObject variable. I called it preferences.

private var preferences:SharedObject = SharedObject.getLocal("contactPreferences");

In the init() function, we need to set the backupLimit variable to the blimit property of the shared object data. If this property doesn't exist, then ignore.

if (preferences.data.blimit != null) {
backupLimit = preferences.data.blimit;
}

You can also save other variable preferences this way.

We've got the loading working, now we need to get to the saving. Find the function that is called every time the numericStepper's value is changed - checkBackupLimit(). Here we will add 2 lines that will handle saving the variable - just set the value to the data.blimit property of the shared object and then call its flush() method to save the data.

preferences.data.blimit = backupLimit;
preferences.flush();

It is done.

The next thing I will cover in this tutorial is fixing the reserved character issue when working with XML files. The thing is, our code right now allows the user enter whatever they want into the inpu fields - which will be saved to the XML file on export. This means that the user can also enter characters like ', ", <, > and & - the reserved XML characters.

It is obvious why this can be a problem - imagine the user using the closing tag symbol. The XML will read it as a closing of a tag, which will lead to errors.

A way to bypass this is created 2 functions - one for encoding and one for decoding the special characters into something that will not cause problems in the file. Encode the data before sending it to the XML file, decode it after reading it.

Here are the two functions:

private function symbolEncode(str:String):String{
str = str.replace(/"/g, '&quot;');
str = str.replace(/'/g, '&apos;');
str = str.replace(/\</g, '&lt;');
str = str.replace(/\>/g, '&gt;');
str = str.replace(/&/g, &'&');
return str;
}

private function symbolDecode(str:String):String{
str = str.replace(/&quot;/g, '"');
str = str.replace(/&apos;/g, "'");
str = str.replace(/&lt;/g, '<');
str = str.replace(/&gt;/g, '>');
str = str.replace(/&amp;/g, '&');
return str;
}

Now we can use these functions to change the values before sending them to the XML writing function and after reading them.

The functions that we would use them in are handleXML:

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

And 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='" + symbolEncode(evt.data[i].fname) + "' lname='" + symbolEncode(evt.data[i].lname) + "' phone='" + symbolEncode(evt.data[i].phone) + "' email='" + symbolEncode(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();
}
}

Here's 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;
import flash.net.SharedObject;

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 var preferences:SharedObject = SharedObject.getLocal("contactPreferences");

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

if (preferences.data.blimit != null) {
backupLimit = preferences.data.blimit;
}
}

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='" + symbolEncode(evt.data[i].fname) + "' lname='" + symbolEncode(evt.data[i].lname) + "' phone='" + symbolEncode(evt.data[i].phone) + "' email='" + symbolEncode(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(symbolDecode(cont.@fname), symbolDecode(cont.@lname), symbolDecode(cont.@phone), symbolDecode(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 {
preferences.data.blimit = backupLimit;
preferences.flush();
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);
}

private function symbolEncode(str:String):String{
str = str.replace(/"/g, '&quot;');
str = str.replace(/'/g, '&apos;');
str = str.replace(/\</g, '&lt;');
str = str.replace(/\>/g, '&gt;');
str = str.replace(/&/g, &'&');
return str;
}

private function symbolDecode(str:String):String{
str = str.replace(/&quot;/g, '"');
str = str.replace(/&apos;/g, "'");
str = str.replace(/&lt;/g, '<');
str = str.replace(/&gt;/g, '>');
str = str.replace(/&amp;/g, '&');
return str;
}

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

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 14
Creating a contact book using Flex, AIR and SQLite database: Part 16

No comments:

Post a Comment