Creating a Flex AIR text editor Part 59

In this tutorial we will add the ability to remove snippet categories.

When a category is deleted, all its contents also are to be deleted. Moreover, all the categories below the one thats deleted need to have their categoryPosition value updated.

First of all add a click listener for the bDeleteCategory button. Set the event handler to a function called deleteCategory().

<s:Button id="bDeleteCategory" label="Delete category with its contents" enabled="false" click="deleteCategory();" />

Now we fill this function with content. The first thing were going to do is declare 3 variables - currentID, currentPos and totalCategoriesBelow. On declaration, set the first two variables to the id and categoryPosition values of the trees selected item.

private function deleteCategory():void {
var currentID:int = snippetManageTree.selectedItem.@id;
var currentPos:int = snippetManageTree.selectedItem.@categoryPosition;
var totalCategoriesBelow:int;

}

Now we need to delete the category from the database. This is easy, since we all we need to do is call one statement and specify the id of the item:

// Delete category from database

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "DELETE FROM categories WHERE id=" + currentID;
stat.execute();

Now we delete all the snippets belonging to this category, also from the database.

// Delete all snippets belonging to this category

var stat2:SQLStatement = new SQLStatement();
stat2.sqlConnection = snippetConnection;
stat2.text = "DELETE FROM snippets WHERE categoryID=" + currentID;
stat2.execute();

Next, we can remove the category from the xml.

// Delete from the xml

delete treeData.category[currentPos];

Now we need to loop through the categories that are below this one and update their categoryPosition. This is done using a loop similar to the one from the previous tutorial:

// Increase categoryPosition of all categories below
totalCategoriesBelow = treeData.category.length() - currentPos;

for (var i:int = 0; i < totalCategoriesBelow; i++) {
treeData.category[currentPos + i].@categoryPosition = currentPos + i;

var stat3:SQLStatement = new SQLStatement();
stat3.sqlConnection = categoryConnection;
stat3.text = "UPDATE categories SET categoryPosition=@categoryPosition WHERE categoryPosition=" + (currentPos + i);
stat3.parameters["@categoryPosition"] = currentPos + i - 1;
stat3.execute();
}

Finally we disable all selection enabled things:

// Disable all selection enabled components

bSaveSnippet.enabled = false;
bDeleteSnippet.enabled = false;
bSaveCategory.enabled = false;
bDeleteCategory.enabled = false;
upCategory.enabled = false;
downCategory.enabled = false;
upSnippet.enabled = false;
downSnippet.enabled = false;

The full function code:

private function deleteCategory():void {
var currentID:int = snippetManageTree.selectedItem.@id;
var currentPos:int = snippetManageTree.selectedItem.@categoryPosition;
var totalCategoriesBelow:int;

// Delete category from database

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "DELETE FROM categories WHERE id=" + currentID;
stat.execute();

// Delete all snippets belonging to this category

var stat2:SQLStatement = new SQLStatement();
stat2.sqlConnection = snippetConnection;
stat2.text = "DELETE FROM snippets WHERE categoryID=" + currentID;
stat2.execute();

// Delete from the xml

delete treeData.category[currentPos];

// Increase categoryPosition of all categories below
totalCategoriesBelow = treeData.category.length() - currentPos;

for (var i:int = 0; i < totalCategoriesBelow; i++) {
treeData.category[currentPos + i].@categoryPosition = currentPos + i;

var stat3:SQLStatement = new SQLStatement();
stat3.sqlConnection = categoryConnection;
stat3.text = "UPDATE categories SET categoryPosition=@categoryPosition WHERE categoryPosition=" + (currentPos + i);
stat3.parameters["@categoryPosition"] = currentPos + i - 1;
stat3.execute();
}

// Disable all selection enabled components

bSaveSnippet.enabled = false;
bDeleteSnippet.enabled = false;
bSaveCategory.enabled = false;
bDeleteCategory.enabled = false;
upCategory.enabled = false;
downCategory.enabled = false;
upSnippet.enabled = false;
downSnippet.enabled = false;
}

And now SnippetWindow.mxml code:

<?xml version="1.0" encoding="utf-8"?>
<mx:Window xmlns:fx="http://ns.adobe.com/mxml/2009" 
xmlns:s="library://ns.adobe.com/flex/spark" 
xmlns:mx="library://ns.adobe.com/flex/mx"
xmlns:custom="*"
title="Snippet management" type="utility" width="500" height="730"
creationComplete="init();" showStatusBar="false" alwaysInFront="true" resizable="false" backgroundColor="#dddddd">

<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLStatement;
import flash.events.Event;
import flash.net.Responder;
import mx.controls.Alert;

[Bindable]
public var treeData:XMLList;
private var snippetConnection:SQLConnection;
private var categoryConnection:SQLConnection;
[Bindable]
private var categories:Array = [];

private function init():void{
this.addEventListener(Event.CLOSING, onClose);
}

private function onClose(evt:Event):void {
this.visible = false;
evt.preventDefault();
}

public function setValues(xmlData:XMLList, conn1:SQLConnection, conn2:SQLConnection, createNewOnStart:Boolean = false, newText:String = ""):void {
snippetConnection = conn1;
categoryConnection = conn2;
treeData = xmlData;
newTextInput.text = newText;
newNameInput.text = "New snippet name here";
if (newText=="") {
newTextInput.text = "New snippet text here";
}
updateCategoryCombo();
}

private function addSnippet(name:String, text:String):void{
if (name != "" && text != "") {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = snippetConnection;
stat.text = "INSERT INTO snippets (snippetName, snippetText, categoryID, snippetPosition) VALUES (@snippetName, @snippetText, @categoryID, @snippetPosition);"
stat.parameters["@snippetName"] = name;
stat.parameters["@snippetText"] = text;
stat.parameters["@categoryID"] = categoryCombo.selectedItem.ind;
stat.parameters["@snippetPosition"] = totalItemsIn(categoryCombo.selectedItem.ind);
stat.execute(-1, new Responder(addSnippetXML));
}else {
Alert.show("The name and text of the snippet must not be blank!","Oops!");
}
}

private function addSnippetXML(evt:SQLResult):void {
if (treeData.category.length() == 0 && treeData..snippet.length() == 0) {
treeData = new XMLList(<root></root>);
}
var aSnippet:XML = <snippet/>;
aSnippet.@id = evt.lastInsertRowID;
aSnippet.@label = newNameInput.text;
aSnippet.@categoryID = categoryCombo.selectedItem.ind;
aSnippet.@snippetPosition = totalItemsIn(categoryCombo.selectedItem.ind);
aSnippet.@isBranch = false;
if(aSnippet.@categoryID!=-1){
treeData.category.(@id == categoryCombo.selectedItem.ind).appendChild(aSnippet);
}else {
treeData[0].appendChild(aSnippet);
}
}

private function addCategory(name:String):void {
if (name != "") {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "INSERT INTO categories (categoryName, categoryPosition) VALUES (@categoryName, @categoryPosition);"
stat.parameters["@categoryName"] = name;
stat.parameters["@categoryPosition"] = treeData.category.length();
stat.execute(-1, new Responder(addCategoryXML));
}else {
Alert.show("The name of the category must not be blank!","Oops!");
}
}

private function addCategoryXML(evt:SQLResult):void {
if (treeData.category.length() == 0 && treeData..snippet.length() == 0) {
treeData = new XMLList(<root></root>);
}
var aCategory:XML = <category/>;
aCategory.@id = evt.lastInsertRowID;
aCategory.@label = newCategoryInput.text;
aCategory.@categoryPosition = treeData.category.length();
aCategory.@isBranch = true;
if(treeData.category.length()>0){
var prevLastCategory:XML = treeData.category[treeData.category.length()-1];
treeData[0].insertChildAfter(prevLastCategory, aCategory);
} else
if(treeData[0].snippet.length()>0){
var firstSnippet:XML = treeData[0].snippet[0];
treeData[0].insertChildBefore(firstSnippet, aCategory);
} else {
treeData[0].appendChild(aCategory);
}
snippetManageTree.selectedItem = aCategory;
selectTree();
updateCategoryCombo();
}

private function updateCategoryCombo():void{
categories = [];
categories.push( { label:"No category", ind: -1 } );
for each (var cat:XML in treeData.category) {
categories.push({label:cat.@label, ind:cat.@id});
}
categoryCombo.selectedIndex = 0;
}

private function totalItemsIn(id:int):int {
var toRet:int;
if(id>=0){
toRet = treeData.category.(@id == id).children().length();
}
if (id == -1) {
toRet = treeData.snippet.length();
}
return toRet;
}

private function selectTree():void {
bSaveSnippet.enabled = false;
bDeleteSnippet.enabled = false;
bSaveCategory.enabled = false;
bDeleteCategory.enabled = false;
upCategory.enabled = false;
downCategory.enabled = false;
upSnippet.enabled = false;
downSnippet.enabled = false;

if (snippetManageTree.selectedItem.@isBranch == true) {
bSaveCategory.enabled = true;
bDeleteCategory.enabled = true;
upCategory.enabled = canCategoryUp();
downCategory.enabled = canCategoryDown();
newCategoryInput.text = snippetManageTree.selectedItem.@label;
} else {
bSaveSnippet.enabled = true;
bDeleteSnippet.enabled = true;
upSnippet.enabled = canSnippetUp();
downSnippet.enabled = canSnippetDown();
categoryCombo.selectedIndex = treeData.category.(@id == snippetManageTree.selectedItem.@categoryID).@categoryPosition + 1;
if (snippetManageTree.selectedItem.@categoryID == -1) {
categoryCombo.selectedIndex = 0;
}
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = snippetConnection;
stat.text = "SELECT snippetText FROM snippets WHERE id=" + snippetManageTree.selectedItem.@id;
stat.execute( -1, new Responder(loadSnippetText));
}
}

private function loadSnippetText(evt:SQLResult):void{
newNameInput.text = snippetManageTree.selectedItem.@label;
newTextInput.text = evt.data[0].snippetText;
}

private function canCategoryUp():Boolean {
var toRet:Boolean = true;
if (snippetManageTree.selectedItem.@categoryPosition == 0) {
toRet = false;
}
return toRet;
}

private function canCategoryDown():Boolean {
var toRet:Boolean = true;
if (snippetManageTree.selectedItem.@categoryPosition == (treeData.category.length()-1)) {
toRet = false;
}
return toRet;
}

private function canSnippetUp():Boolean {
var toRet:Boolean = true;
if (snippetManageTree.selectedItem.@snippetPosition == 0) {
toRet = false;
}
return toRet;
}

private function canSnippetDown():Boolean {
var toRet:Boolean = true;
if (snippetManageTree.selectedItem.@categoryID != -1 && snippetManageTree.selectedItem.@snippetPosition == (treeData.category.(@id == snippetManageTree.selectedItem.@categoryID).children().length()-1)) {
toRet = false;
}
if (snippetManageTree.selectedItem.@snippetPosition == (treeData.snippet.length()-1)) {
toRet = false;
}
return toRet;
}

private function categoryMove(dir:String):void{
var currentPos:int = snippetManageTree.selectedItem.@categoryPosition;
var currentId:int = snippetManageTree.selectedItem.@id;
var newPos:int = (dir=="up")?(currentPos - 1):(currentPos + 1);

// Databases:

// Set the above categorys position to current one
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "UPDATE categories SET categoryPosition=@currentPos WHERE categoryPosition=@newPos"
stat.parameters["@newPos"] = newPos;
stat.parameters["@currentPos"] = currentPos;
stat.execute();

// Set the current categorys position to the one above
var stat2:SQLStatement = new SQLStatement();
stat2.sqlConnection = categoryConnection;
stat2.text = "UPDATE categories SET categoryPosition=@newPos WHERE id=@currentId"
stat2.parameters["@newPos"] = newPos;
stat2.parameters["@currentId"] = currentId;
stat2.execute();

// XML:

var copy:XML = treeData.category[currentPos].copy();
delete treeData.category[currentPos];
if(dir=="up"){
treeData.insertChildBefore(treeData.category[newPos], copy);
}else {
treeData.insertChildAfter(treeData.category[currentPos], copy);
}
copy.@categoryPosition = newPos;
treeData.category[currentPos].@categoryPosition = currentPos;
snippetManageTree.selectedItem = copy;
selectTree();
}

private function snippetMove(dir:String):void{
var currentPos:int = snippetManageTree.selectedItem.@snippetPosition;
var currentId:int = snippetManageTree.selectedItem.@id;
var newPos:int = (dir == "up")?(currentPos - 1):(currentPos + 1);
var currentCategoryID:int = snippetManageTree.selectedItem.@categoryID;
var currentCategoryPos:int = NaN;

if (currentCategoryID != -1) {
currentCategoryPos = treeData.category.(@id == currentCategoryID).@categoryPosition;
}

// Databases:

// Set the above categorys position to current one
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = snippetConnection;
stat.text = "UPDATE snippets SET snippetPosition=@currentPos WHERE snippetPosition=@newPos"
stat.parameters["@newPos"] = newPos;
stat.parameters["@currentPos"] = currentPos;
stat.execute();

// Set the current categorys position to the one above
var stat2:SQLStatement = new SQLStatement();
stat2.sqlConnection = snippetConnection;
stat2.text = "UPDATE snippets SET snippetPosition=@newPos WHERE id=@currentId"
stat2.parameters["@newPos"] = newPos;
stat2.parameters["@currentId"] = currentId;
stat2.execute();

// XML:

var copy:XML = new XML();

if(currentCategoryID != -1){
copy = treeData.category[currentCategoryPos].snippet[currentPos].copy();
delete treeData.category[currentCategoryPos].snippet[currentPos];
if(dir=="up"){
treeData.category[currentCategoryPos].insertChildBefore(treeData.category[currentCategoryPos].snippet[newPos], copy);
}else {
treeData.category[currentCategoryPos].insertChildAfter(treeData.category[currentCategoryPos].snippet[currentPos], copy);
}
copy.@snippetPosition = newPos;
treeData.category[currentCategoryPos].snippet[currentPos].@snippetPosition = currentPos;
}

if(currentCategoryID == -1){
copy = treeData.snippet[currentPos].copy();
delete treeData.snippet[currentPos];
if(dir=="up"){
treeData.insertChildBefore(treeData.snippet[newPos], copy);
}else {
treeData.insertChildAfter(treeData.snippet[currentPos], copy);
}
copy.@snippetPosition = newPos;
treeData.snippet[currentPos].@snippetPosition = currentPos;
}

snippetManageTree.selectedItem = copy;
selectTree();
}

private function saveCategory():void {
var currentID:int = snippetManageTree.selectedItem.@id;

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "UPDATE categories SET categoryName=@categoryName WHERE id=" + currentID;
stat.parameters["@categoryName"] = newCategoryInput.text;
stat.execute();

treeData.category[snippetManageTree.selectedItem.@categoryPosition].@label = newCategoryInput.text;
updateCategoryCombo();
}

private function saveSnippet():void {
var currentID:int = snippetManageTree.selectedItem.@id;

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = snippetConnection;
stat.text = "UPDATE snippets SET snippetName=@snippetName, snippetText=@snippetText WHERE id=" + currentID;
stat.parameters["@snippetName"] = newNameInput.text;
stat.parameters["@snippetText"] = newTextInput.text;
stat.execute();

var currentPos:int = snippetManageTree.selectedItem.@snippetPosition;
var currentCategoryID:int = snippetManageTree.selectedItem.@categoryID;
var currentCategoryPos:int;

if (currentCategoryID != -1) {
currentCategoryPos = treeData.category.(@id == currentCategoryID).@categoryPosition;
treeData.category[currentCategoryPos].snippet[currentPos].@label = newNameInput.text;
}else {
treeData.snippet[currentPos].@label = newNameInput.text;
}

}

private function deleteSnippet():void {
var currentPos:int = snippetManageTree.selectedItem.@snippetPosition;
var currentId:int = snippetManageTree.selectedItem.@id;
var currentCategoryID:int = snippetManageTree.selectedItem.@categoryID;
var currentCategoryPos:int = NaN;

if (currentCategoryID != -1) {
currentCategoryPos = treeData.category.(@id == currentCategoryID).@categoryPosition;
}
var totalSnippetsBelow:int;

// Delete from the SQL database
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = snippetConnection;
stat.text = "DELETE FROM snippets WHERE id=" + currentId;
stat.execute();

// If in a category:
if (currentCategoryID != -1) {

// Delete from the XML
delete treeData.category[currentCategoryPos].snippet[currentPos];

// Increase snippetPosition of all snippets below
totalSnippetsBelow = treeData.category[currentCategoryPos].children().length() - currentPos;

for (var i:int = 0; i < totalSnippetsBelow; i++) {
treeData.category[currentCategoryPos].snippet[currentPos + i].@snippetPosition = currentPos + i;

var snipStat1:SQLStatement = new SQLStatement();
snipStat1.sqlConnection = snippetConnection;
snipStat1.text = "UPDATE snippets SET snippetPosition=@snippetPosition WHERE snippetPosition=" + (currentPos + i);
snipStat1.parameters["@snippetPosition"] = currentPos + i - 1;
snipStat1.execute();
}

}

// If in a root tags:
if (currentCategoryID == -1) {

// Delete from the XML
delete treeData.snippet[currentPos];

// Increase snippetPosition of all snippets below
totalSnippetsBelow = treeData.snippet.length() - currentPos;

for (var u:int = 0; u < totalSnippetsBelow; u++) {
treeData.snippet[currentPos + u].@snippetPosition = currentPos + u;

var snipStat2:SQLStatement = new SQLStatement();
snipStat2.sqlConnection = snippetConnection;
snipStat2.text = "UPDATE snippets SET snippetPosition=@snippetPosition WHERE snippetPosition=" + (currentPos + u);
snipStat2.parameters["@snippetPosition"] = currentPos + u - 1;
snipStat2.execute();
}

}

// Disable all selection enabled components

bSaveSnippet.enabled = false;
bDeleteSnippet.enabled = false;
bSaveCategory.enabled = false;
bDeleteCategory.enabled = false;
upCategory.enabled = false;
downCategory.enabled = false;
upSnippet.enabled = false;
downSnippet.enabled = false;
}

private function deleteCategory():void {
var currentID:int = snippetManageTree.selectedItem.@id;
var currentPos:int = snippetManageTree.selectedItem.@categoryPosition;
var totalCategoriesBelow:int;

// Delete category from database

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = categoryConnection;
stat.text = "DELETE FROM categories WHERE id=" + currentID;
stat.execute();

// Delete all snippets belonging to this category

var stat2:SQLStatement = new SQLStatement();
stat2.sqlConnection = snippetConnection;
stat2.text = "DELETE FROM snippets WHERE categoryID=" + currentID;
stat2.execute();

// Delete from the xml

delete treeData.category[currentPos];

// Increase categoryPosition of all categories below
totalCategoriesBelow = treeData.category.length() - currentPos;

for (var i:int = 0; i < totalCategoriesBelow; i++) {
treeData.category[currentPos + i].@categoryPosition = currentPos + i;

var stat3:SQLStatement = new SQLStatement();
stat3.sqlConnection = categoryConnection;
stat3.text = "UPDATE categories SET categoryPosition=@categoryPosition WHERE categoryPosition=" + (currentPos + i);
stat3.parameters["@categoryPosition"] = currentPos + i - 1;
stat3.execute();
}

// Disable all selection enabled components

bSaveSnippet.enabled = false;
bDeleteSnippet.enabled = false;
bSaveCategory.enabled = false;
bDeleteCategory.enabled = false;
upCategory.enabled = false;
downCategory.enabled = false;
upSnippet.enabled = false;
downSnippet.enabled = false;
}
]]>
</fx:Script>

<s:VGroup paddingLeft="5" paddingRight="5" paddingTop="5" paddingBottom="5">
<s:Label text="Snippet creation and management: " />
<s:HGroup>
<s:TextInput id="newNameInput" text="New snippet name" width="240" />
<mx:ComboBox id="categoryCombo" width="240" dataProvider="{categories}" />
</s:HGroup>
<s:TextArea id="newTextInput" width="490" height="200" text="Snippet text" />
<s:HGroup>
<s:Button id="bAddSnippet" label="Add snippet" click="addSnippet(newNameInput.text, newTextInput.text);" />
<s:Button id="bSaveSnippet" label="Save changes" enabled="false" />
<s:Button id="bDeleteSnippet" label="Delete snippet"  enabled="false" click="deleteSnippet();" />
<custom:IconButton id="upSnippet" icon="@Embed(../lib/arrow_up.png)" toolTip="Move up" enabled="false" click="snippetMove(up);" />
<custom:IconButton id="downSnippet" icon="@Embed(../lib/arrow_down.png)" toolTip="Move down" enabled="false" click="snippetMove(down);" />
</s:HGroup>

<s:Label text="" />

<s:Label text="Category creation and management: " />
<s:TextInput id="newCategoryInput" text="New category name" width="490" />
<s:HGroup>
<s:Button id="bAddCategory" label="Add category" click="addCategory(newCategoryInput.text);"/>
<s:Button id="bSaveCategory" label="Save changes" enabled="false" click="saveCategory();" />
<s:Button id="bDeleteCategory" label="Delete category with its contents" enabled="false" click="deleteCategory();" />
<custom:IconButton id="upCategory" icon="@Embed(../lib/arrow_up.png)" toolTip="Move up" enabled="false" click="categoryMove(up);" />
<custom:IconButton id="downCategory" icon="@Embed(../lib/arrow_down.png)" toolTip="Move down" enabled="false" click="categoryMove(down);" />
</s:HGroup>

<s:Label text="" />

<mx:Tree id="snippetManageTree" dataProvider="{treeData}" width="490" height="300" showRoot="false" labelField="@label" itemClick="selectTree();" />
</s:VGroup>

</mx:Window>

Thanks for reading!

Read More

Creating a Flex AIR Screenshot app Part 20

In this tutorial we will keep improving our crop selection functionality.

Right now, we can only select by dragging from the initial position (the coordinates that we pressed our mouse at) to bottom right. The code doesnt allow us to drag in any other direction, thanks to a conditional Ive put in the previous tutorial.

I did that, because if we create the selection into any other direction, we wont be able to drag it properly anymore. This is because the code we have right now assumes that the top left corner of the selection box coordinates are drawingRect[0] and drawingRect[1]. Instead of rewriting that code, we can just make sure that no matter what direction the selection is created, the top left corner will always be that point with those coordinates.

We can do that by adding 4 conditionals to the drawMove() function inside the if(drawing) conditional. They might look confusing at first glance, but they are pretty straight forward and there isnt much to explain from what is already written in the code. Ill just say that we will achieve our goal if we check which direction the user drags, and according to that value, set the 4 values of drawingRect accordingly, where 0 and 1 elements are always the x and y of the whole selection and represent the top left point in the rectangle.

if (drawing) {
// bottom - right
if (evt.target.mouseX > drawPreviousX  && evt.target.mouseY > drawPreviousY) {
drawingRect[0] = drawPreviousX;
drawingRect[1] = drawPreviousY;
drawingRect[2] = evt.target.mouseX - drawPreviousX;
drawingRect[3] = evt.target.mouseY - drawPreviousY;
drawRectangle();
}
// bottom - left
if (evt.target.mouseX < drawPreviousX  && evt.target.mouseY > drawPreviousY) {
drawingRect[0] = evt.target.mouseX;
drawingRect[1] = drawPreviousY;
drawingRect[2] = drawPreviousX - evt.target.mouseX;
drawingRect[3] = evt.target.mouseY - drawPreviousY;
drawRectangle();
}
// top - right
if (evt.target.mouseX > drawPreviousX  && evt.target.mouseY < drawPreviousY) {
drawingRect[0] = drawPreviousX;
drawingRect[1] = evt.target.mouseY;
drawingRect[2] = evt.target.mouseX - drawPreviousX;
drawingRect[3] = drawPreviousY - evt.target.mouseY;
drawRectangle();
}
// top - left
if (evt.target.mouseX < drawPreviousX  && evt.target.mouseY < drawPreviousY) {
drawingRect[0] = evt.target.mouseX;
drawingRect[1] = evt.target.mouseY;
drawingRect[2] = drawPreviousX - evt.target.mouseX;
drawingRect[3] = drawPreviousY - evt.target.mouseY;
drawRectangle();
}
}

Now we can create it in any direction and it will work just fine. We are also able to drag the created selection around.

One little problem remains, that might be really annoying and irritating - its the fact that when we drag our selection around and near the edges, it often gets "stuck", because sometimes the mouse goes a few pixels too far in a direction, which would make the selection cross the borders. Right now, if the program sees that by moving the selection it will not fit in the frame, it just doesnt move it at all. Lets change it a bit - make it actually move the selection to the very edge of the area. This will make it seem much more user-friendly and clean.

To do that, go to the drawMove() function and in the if(dragging) function, remove the long conditional checking for intersection with borders. Just set the coordinates to newX and newY. Then, call a function called validSelectionPositionCheck().

if (dragging) {
var newX:int = evt.target.mouseX - dragOffsetX;
var newY:int = evt.target.mouseY - dragOffsetY;
drawingRect[0] = newX;
drawingRect[1] = newY;
validSelectionPositionCheck();
drawRectangle();
}

This function checks if the position of the selection crosses the borders. If it does, move it to very edge, but inside.

private function validSelectionPositionCheck():void {
if (drawingRect[0] < 0) {
drawingRect[0] = 0;
}
if (drawingRect[0] > drawArea.width - drawingRect[2]) {
drawingRect[0] = drawArea.width - drawingRect[2];
}
if (drawingRect[1] < 0) {
drawingRect[1] = 0;
}
if (drawingRect[1] > drawArea.height - drawingRect[3]) {
drawingRect[1] = drawArea.height - drawingRect[3];
}
}

Thats all for today.

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:custom="*"
                       xmlns:mx="library://ns.adobe.com/flex/mx" showStatusBar="false"
   width="550" height="600" creationComplete="init();">
   
   
<fx:Declarations>
<mx:ArrayCollection id="headerTitles">
<fx:Object step="Step one:" description="load a web page." />
<fx:Object step="Loading..." description="please wait." />
<fx:Object step="Step two:" description="set your export preferences." />
<fx:Object step="Step two:" description="select the area you wish to crop." />
<fx:Object step="Step three:" description="set your export preferences for the cropped image." />
<fx:Object step="Exporting:" description="please wait." />
</mx:ArrayCollection>
</fx:Declarations>

<fx:Style>
@namespace s "library://ns.adobe.com/flex/spark";
@namespace mx "library://ns.adobe.com/flex/mx";

.descriptionText{
fontSize: 24;
color: #fff;
}

.descriptionText2{
fontSize: 16;
color: #fff;
}

.settingText{
fontSize: 16;
color: #fff;
}

#headStep{
fontSize: 30;
fontWeight: bold;
color: #ffffbb;
}

#headDesc{
fontSize: 30;
color: #ffffff;
}
</fx:Style>

<fx:Script>
<![CDATA[
import flash.display.Bitmap;
import flash.display.BitmapData;
import flash.events.Event;
import flash.events.MouseEvent;
import flash.events.TimerEvent;
import flash.filesystem.File;
import flash.filesystem.FileStream;
import flash.filters.GlowFilter;
import flash.geom.Point;
import flash.geom.Rectangle;
import flash.net.URLRequest;
import flash.utils.ByteArray;
import flash.utils.Timer;
import mx.controls.HTML;
import mx.core.FlexHTMLLoader;
import mx.events.FlexNativeWindowBoundsEvent;
import mx.controls.Alert;
import mx.events.ResizeEvent;
import mx.graphics.codec.IImageEncoder;
import mx.graphics.codec.JPEGEncoder;
import mx.graphics.codec.PNGEncoder;
import mx.graphics.ImageSnapshot;
import spark.components.Scroller;
import spark.primitives.BitmapImage;
import flash.filesystem.FileMode;
import mx.managers.PopUpManager;

[Bindable]
private var urlString:String;
private var tempHTML:HTML = new HTML();

private var preferences:SharedObject = SharedObject.getLocal("kirshotPreferences");
[Bindable]
private var pref_screensizes:Array;
[Bindable]
private var pref_format:String;
[Bindable]
private var pref_quality:int;
[Bindable]
private var pref_folder:Boolean;
[Bindable]
private var pref_destination:String;
[Bindable]
private var exportText:String;
private var screenGo:String;
private var drawing:Boolean = false;
private var dragging:Boolean = false;
private var inDragArea:Boolean = false;
private var drawingRect:Array = [];
private var dragOffsetX:int = 0;
private var dragOffsetY:int = 0;
private var drawPreviousX:int = 0;
private var drawPreviousY:int = 0;

[Embed(source="../lib/cursor_crop.png")]
private var cropCursor:Class;

[Embed(source="../lib/cursor_move.png")]
private var dragCursor:Class;

private var screenSettings:Array;

private function init():void {
//preferences.data.firsttime = null;

// Set preferences if loaded for the first time
if (preferences.data.firsttime == null) {
preferences.data.firsttime = true;
preferences.data.screensizes = [ 
{ checked:true },
{ checked:true, w:1280, h:1024 },
{ checked:true, w:1280, h:800 },
{ checked:true, w:1024, h:768 },
{ checked:false, w:"", h:"" },
{ checked:false, w:"", h:"" },
{ checked:false, w:"", h:"" } ];
preferences.data.format = "JPEG";
preferences.data.quality = 100;
preferences.data.folder = true;
preferences.data.destination = File.documentsDirectory.nativePath;
preferences.flush();
}

// Set preferences loaded from local storage
pref_screensizes = preferences.data.screensizes;
pref_format = preferences.data.format;
pref_quality = preferences.data.quality;
pref_folder = preferences.data.folder;
pref_destination = preferences.data.destination;

addElement(tempHTML);
removeElement(tempHTML);
}

private function doBrowse():void{
var file:File = new File();
file.addEventListener(Event.SELECT, browseSelect);
file.browseForOpen("Load a webpage");

function browseSelect(evt:Event):void {
urlInput.text = file.nativePath;
}
}

private function goScreenshot(screen:String):void {
screenGo = screen;
stack.selectedChild = screenshotloading;
urlString = urlInput.text;

addElement(tempHTML);
tempHTML.htmlLoader.useCache = false;
tempHTML.horizontalScrollPolicy = "off";
tempHTML.verticalScrollPolicy = "off";
tempHTML.visible = false;
tempHTML.addEventListener(Event.COMPLETE, onTempLoad);
tempHTML.htmlLoader.load(new URLRequest(urlString));
}

private function onTempLoad(evt:Event):void {
tempHTML.removeEventListener(Event.COMPLETE, onTempLoad);
if(screenGo=="screenshot"){
stack.selectedChild = screenshotsettings;
}
if (screenGo == "crop") {
if(tempHTML.contentWidth <= 4096 && tempHTML.contentHeight <= 4096){
var t:Timer = new Timer(1000, 1);
t.addEventListener(TimerEvent.TIMER_COMPLETE, tComplete);
t.start();
tempHTML.width = tempHTML.contentWidth;
tempHTML.height = tempHTML.contentHeight;
function tComplete(evt:TimerEvent):void {
stack.selectedChild = crop;
}
}else {
Alert.show("Dimensions of a screenshot cannot exceed 4096 pixels.", "Sorry...");
stack.selectedChild = loadpage;
}
}
}

private function cancelLoading():void {
tempHTML.removeEventListener(Event.COMPLETE, onTempLoad);
tempHTML.cancelLoad();
stack.selectedChild = loadpage;
removeElement(tempHTML);
}

private function screenshotBack():void {
saveScreenshotSettings();
stack.selectedChild = loadpage;
removeElement(tempHTML);
}

private function changeState():void {
if (stack.selectedChild == loadpage) {
contentBox.setStyle("horizontalAlign", "center");
urlInput.text = urlString;
tempHTML.width = 1;
tempHTML.height = 1;
tempHTML.removeEventListener(Event.COMPLETE, onTempLoad);
tempHTML.htmlLoader.loadString("<html></html>");
}
if (stack.selectedChild == crop) {
maximize();
contentBox.setStyle("horizontalAlign", "left");
var bd:BitmapData = new BitmapData(tempHTML.width, tempHTML.height, false);
bd.draw(tempHTML);
var b:Bitmap = new Bitmap(bd, "auto", true);
cropHTML.source = b;
drawArea.addEventListener(MouseEvent.MOUSE_DOWN, drawDown);
addEventListener(MouseEvent.MOUSE_UP, drawUp);
drawArea.addEventListener(MouseEvent.MOUSE_MOVE, drawMove);
displayWidth.filters = [new GlowFilter(0xffffff, 1, 3, 3, 50)];
displayHeight.filters = [new GlowFilter(0xffffff, 1, 3, 3, 50)];
drawArea.addEventListener(MouseEvent.MOUSE_OVER, drawOver);
drawArea.addEventListener(MouseEvent.MOUSE_OUT, drawOut);
displayHeight.text = "";
displayWidth.text = "";
cropDraw.graphics.clear();
}
if (stack.selectedChild == screenshotsettings) {
screenSettings = [set2, set3, set4, set5, set6, set7];
contSize.text = "Full size (" + tempHTML.contentWidth + "x" + tempHTML.contentHeight + ")";
loadScreenshotSettings();
}
}

private function loadScreenshotSettings():void {
set1checkbox.selected = pref_screensizes[0].checked;

for (var i:int = 0; i < screenSettings.length; i++) {
screenSettings[i].checked = pref_screensizes[i + 1].checked;
screenSettings[i].w = pref_screensizes[i + 1].w;
screenSettings[i].h = pref_screensizes[i + 1].h;
}

if (pref_format == "JPEG") {
screenRadioJPEG.selected = true;
} else {
screenRadioPNG.selected = true;
}
}

private function saveScreenshotSettings():void {
pref_screensizes[0].checked = set1checkbox.selected;

for (var i:int = 0; i < screenSettings.length; i++) {
pref_screensizes[i + 1].checked = screenSettings[i].checked;
pref_screensizes[i + 1].w = screenSettings[i].w;
pref_screensizes[i + 1].h = screenSettings[i].h;
}

if (screenRadioJPEG.selected == true) {
pref_format == "JPEG";
} else {
pref_format == "PNG";
}

preferences.data.screensizes = pref_screensizes;
preferences.data.format = pref_format;
preferences.data.quality = pref_quality;
preferences.data.folder = pref_folder;
preferences.data.destination = pref_destination;
preferences.flush();
}

private function formatChange(newformat:String):void {
pref_format = newformat;
}

private function startExportScreenshot():void {
var canExport:Boolean = true;

for (var i:int = 0; i < screenSettings.length; i++) {
if (screenSettings[i].checked && ((screenSettings[i].w == "" || screenSettings[i].w == 0) || (screenSettings[i].h == "" || screenSettings[i].h == 0))) {
canExport = false;
}
}

if (canExport) {
if ((pref_folder && folderField.text != "") || !pref_folder) {
saveScreenshotSettings();
exportScreen();
}else {
Alert.show("Folder name should not be blank!", "Oops...");
}
}else {
Alert.show("One or more selected screen sizes are not entered or are invalid!", "Oops...");
}
}

private function screenshotDestination():void {
var newDestination:File = new File(pref_destination);
newDestination.browseForDirectory("Select directory");
newDestination.addEventListener(Event.SELECT, destinationSelect);

function destinationSelect(evt:Event):void {
pref_destination = newDestination.nativePath;
}
}

private function exportScreen():void {
var encoder:IImageEncoder;
var bd:BitmapData;
var byteArray:ByteArray;
var folderName:String = (pref_folder)?(folderField.text):("");
var fileName:String;
var file:File;
var fileStream:FileStream;
var screensToExport:Array = [];
stack.selectedChild = export;

if (pref_format == "JPEG") {
encoder = new JPEGEncoder(pref_quality);
}
if (pref_format == "PNG") {
encoder = new PNGEncoder();
}

// add full-size screen to array if checked
if (pref_screensizes[0].checked) {
screensToExport = [ { w:tempHTML.contentWidth, h: tempHTML.contentHeight, full:true} ];
}

// add the rest screens to array if checked
for (var i:int = 0; i < screenSettings.length; i++) {
if (pref_screensizes[i + 1].checked) {
screensToExport.push( { w: pref_screensizes[i + 1].w, h:pref_screensizes[i + 1].h } );
}
}

// if nothing is checked, go to first page and stop code
if (screensToExport.length == 0) {
removeElement(tempHTML);
stack.selectedChild = loadpage;
return;
}

// create a timer that repeats itself as many times as many items there are in the array
var timer:Timer = new Timer(2000, screensToExport.length);
timer.addEventListener(TimerEvent.TIMER, onTimer);
// set sizes to the first size of the array
if (screensToExport[0].full) {
tempHTML.horizontalScrollPolicy = "off";
tempHTML.verticalScrollPolicy = "off";
}else {
tempHTML.horizontalScrollPolicy = "auto";
tempHTML.verticalScrollPolicy = "auto";
}
if(screensToExport[0].h <= 4096 && screensToExport[0].w <= 4096){
tempHTML.height = screensToExport[0].h;
tempHTML.width = screensToExport[0].w;
}
updateExportText(screensToExport[0].w, screensToExport[0].h, 1, screensToExport.length);
timer.start();

function onTimer(evt:TimerEvent):void {
// do export for the current size
if(screensToExport[timer.currentCount-1].h <= 4096 && screensToExport[timer.currentCount-1].w <= 4096){
timer.stop();
doExport();
timer.start();
}else {
Alert.show("Cannot export " + screensToExport[timer.currentCount-1].w + "x" + screensToExport[timer.currentCount-1].h + " - dimensions of a screenshot cannot extend 4096.","Sorry");
}
// change the size if this was not the last size in the array
if (timer.currentCount != screensToExport.length) {
tempHTML.horizontalScrollPolicy = "auto";
tempHTML.verticalScrollPolicy = "auto";
if(screensToExport[timer.currentCount].h <= 4096 && screensToExport[timer.currentCount].w <= 4096){
tempHTML.height = screensToExport[timer.currentCount].h;
tempHTML.width = screensToExport[timer.currentCount].w;
}
updateExportText(screensToExport[timer.currentCount].w, screensToExport[timer.currentCount].h, timer.currentCount+1, screensToExport.length);
}else {
// if it was the last size in the array, return to first page
timer.stop();
removeElement(tempHTML);
stack.selectedChild = loadpage;
}
}

function doExport():void {
bd = new BitmapData(tempHTML.width, tempHTML.height, false);
bd.draw(tempHTML, null, null, null, null, true);
byteArray = encoder.encode(bd);
fileName = pref_destination + File.separator + folderName + File.separator + tempHTML.width + "x" + tempHTML.height + "." + pref_format;
file = new File(fileName);
fileStream = new FileStream();
fileStream.open(file, FileMode.WRITE);
fileStream.writeBytes(byteArray);
fileStream.close();
}

function updateExportText(w:int, h:int, current:int, total:int):void {
exportText = "Exporting " + w + "x" + h + "." + pref_format + " (" + current + "/" + total + ")";
}
}

private function drawDown(evt:MouseEvent):void {
if(!inDragArea){
drawing = true;
drawingRect = [evt.target.mouseX, evt.target.mouseY, 0, 0];
drawPreviousX = evt.target.mouseX;
drawPreviousY = evt.target.mouseY;
drawRectangle();
}
if (inDragArea) {
dragging = true;
dragOffsetX = evt.target.mouseX - drawingRect[0];
dragOffsetY = evt.target.mouseY - drawingRect[1];
}
}

private function drawUp(evt:MouseEvent):void{
drawing = false;
dragging = false;
}

private function drawMove(evt:MouseEvent):void{
if (drawing) {
// bottom - right
if (evt.target.mouseX > drawPreviousX  && evt.target.mouseY > drawPreviousY) {
drawingRect[0] = drawPreviousX;
drawingRect[1] = drawPreviousY;
drawingRect[2] = evt.target.mouseX - drawPreviousX;
drawingRect[3] = evt.target.mouseY - drawPreviousY;
drawRectangle();
}
// bottom - left
if (evt.target.mouseX < drawPreviousX  && evt.target.mouseY > drawPreviousY) {
drawingRect[0] = evt.target.mouseX;
drawingRect[1] = drawPreviousY;
drawingRect[2] = drawPreviousX - evt.target.mouseX;
drawingRect[3] = evt.target.mouseY - drawPreviousY;
drawRectangle();
}
// top - right
if (evt.target.mouseX > drawPreviousX  && evt.target.mouseY < drawPreviousY) {
drawingRect[0] = drawPreviousX;
drawingRect[1] = evt.target.mouseY;
drawingRect[2] = evt.target.mouseX - drawPreviousX;
drawingRect[3] = drawPreviousY - evt.target.mouseY;
drawRectangle();
}
// top - left
if (evt.target.mouseX < drawPreviousX  && evt.target.mouseY < drawPreviousY) {
drawingRect[0] = evt.target.mouseX;
drawingRect[1] = evt.target.mouseY;
drawingRect[2] = drawPreviousX - evt.target.mouseX;
drawingRect[3] = drawPreviousY - evt.target.mouseY;
drawRectangle();
}
}

if (dragging) {
var newX:int = evt.target.mouseX - dragOffsetX;
var newY:int = evt.target.mouseY - dragOffsetY;
drawingRect[0] = newX;
drawingRect[1] = newY;
validSelectionPositionCheck();
drawRectangle();
}

if (!drawing && !dragging){
cursorManager.removeAllCursors();

if (evt.target.mouseX > drawingRect[0] && evt.target.mouseX < drawingRect[0] + drawingRect[2] && evt.target.mouseY > drawingRect[1] && evt.target.mouseY < drawingRect[1] + drawingRect[3]) {
cursorManager.setCursor(dragCursor, 2, -9, -9);
inDragArea = true;
}else {
cursorManager.setCursor(cropCursor, 2, -9, -9);
inDragArea = false;
}
}
}

private function drawRectangle():void {
displayWidth.x = drawingRect[0];
if (displayWidth.x + displayWidth.width > cropDraw.width) {
displayWidth.x -= displayWidth.width;
}
displayWidth.y = drawingRect[1] - 12;
if (displayWidth.y < 0) {
displayWidth.y += displayWidth.height;
}
displayWidth.text = Math.abs(drawingRect[2]) + "px";
displayHeight.x = drawingRect[0] + drawingRect[2] + 4;
if (displayHeight.x + displayHeight.width > cropDraw.width) {
displayHeight.x -= (displayHeight.width + 4);
}
displayHeight.y = drawingRect[1] + drawingRect[3] - 12;
if (displayHeight.y < 0) {
displayHeight.y += displayHeight.height;
}
displayHeight.text = Math.abs(drawingRect[3]) + "px";
cropDraw.graphics.clear();
cropDraw.graphics.lineStyle(1, 0xff0000);
cropDraw.graphics.beginFill(0x00ff00, 0.15);
cropDraw.graphics.drawRect(drawingRect[0], drawingRect[1], drawingRect[2], drawingRect[3]);
cropDraw.graphics.endFill();
}

private function drawOver(evt:MouseEvent):void {
if (dragging) {
cursorManager.setCursor(dragCursor, 2, -9, -9);
}else {
cursorManager.setCursor(cropCursor, 2, -9, -9);
}
}

private function drawOut(evt:MouseEvent):void {
cursorManager.removeAllCursors();
}

private function validSelectionPositionCheck():void {
if (drawingRect[0] < 0) {
drawingRect[0] = 0;
}
if (drawingRect[0] > drawArea.width - drawingRect[2]) {
drawingRect[0] = drawArea.width - drawingRect[2];
}
if (drawingRect[1] < 0) {
drawingRect[1] = 0;
}
if (drawingRect[1] > drawArea.height - drawingRect[3]) {
drawingRect[1] = drawArea.height - drawingRect[3];
}
}
]]>
</fx:Script>
   
<s:VGroup width="100%" height="100%" gap="0">
<mx:HBox backgroundColor="#333333" height="46" width="100%" paddingTop="10" paddingLeft="10">
<s:Label id="headStep" text="{headerTitles.getItemAt(stack.selectedIndex).step}" />
<s:Label id="headDesc" text="{headerTitles.getItemAt(stack.selectedIndex).description}" />
</mx:HBox>
<mx:Box backgroundColor="#666666" width="100%" height="100%" id="contentBox" horizontalAlign="center">
<mx:ViewStack id="stack" change="changeState();">
<s:NavigatorContent id="loadpage">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">Enter the link to the page:</s:Label>
<s:HGroup>
<s:TextInput width="250" id="urlInput" text="http://" /><s:Button label="Browse local..." click="doBrowse();" />
</s:HGroup>
<s:HGroup>
<custom:ImageButton img="@Embed(../lib/b_screenshot.png)" over="@Embed(../lib/b_screenshot_over.png)" toolTip="Take screenshots" click="goScreenshot(screenshot);" buttonMode="true" enabled="{urlInput.text!=}" />
<custom:ImageButton img="@Embed(../lib/b_cut.png)" over="@Embed(../lib/b_cut_over.png)" toolTip="Crop area" click="goScreenshot(crop);" buttonMode="true" enabled="{urlInput.text!=}" />
</s:HGroup>
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="screenshotloading">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">The page is being loaded...</s:Label>
<s:Button label="Cancel" click="cancelLoading();" />
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="screenshotsettings">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText2">Select screenshot screen sizes:</s:Label>
<s:SkinnableContainer backgroundColor="#999999" width="310" height="18" >
<s:CheckBox toolTip="Use this screen size" x="4" id="set1checkbox" />
<s:Label id="contSize" styleName="settingText" x="22" y="3" />
</s:SkinnableContainer>
<custom:ScreenSetting id="set2" />
<custom:ScreenSetting id="set3" />
<custom:ScreenSetting id="set4" />
<custom:ScreenSetting id="set5" />
<custom:ScreenSetting id="set6" />
<custom:ScreenSetting id="set7" />

<s:Label/>

<s:Label styleName="descriptionText2">Export as:</s:Label>
<s:HGroup>
<s:RadioButton id="screenRadioJPEG" label="JPEG" groupName="screenshotFormat" change="formatChange(JPEG);" styleName="descriptionText2" /> 
<s:RadioButton id="screenRadioPNG" label="PNG" groupName="screenshotFormat" change="formatChange(PNG);" styleName="descriptionText2" /> 
</s:HGroup>
<s:Label styleName="descriptionText2">Quality:</s:Label>
<s:HSlider id="screenQualitySlider" width="310" minimum="1" maximum="100" liveDragging="true" enabled="{pref_format==JPEG}" value="@{pref_quality}" />

<s:Label/>

<s:Label styleName="descriptionText2">Export destination:</s:Label>
<s:HGroup width="310">
<s:TextInput editable="false" width="100%" toolTip="Destination" text="{pref_destination}" />
<s:Button label="Browse" click="screenshotDestination();" />
</s:HGroup>
<s:CheckBox id="folderCheckbox" label="Create new folder with exported images" styleName="descriptionText2" selected="@{pref_folder}" />
<s:TextInput id="folderField" width="100%" toolTip="Folder name" maxChars="200" enabled="{folderCheckbox.selected}" restrict="a-zA-Z0-9._-=+" />
<s:HGroup>
<s:Button label="Back" click="screenshotBack();" />
<s:Button label="Export" click="startExportScreenshot();" />
</s:HGroup>
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="crop">
<s:VGroup width="{contentBox.width}" height="100%" gap="0" horizontalAlign="center">
<mx:HBox backgroundColor="#999999" width="100%" height="24" verticalScrollPolicy="off" verticalAlign="middle" paddingLeft="2">
<s:Button label="Back" click="{stack.selectedChild = loadpage;}" />
<s:Button label="Export selection" enabled="false" />
</mx:HBox>
<s:Scroller width="{(contentBox.width>cropHTML.width+15)?(cropHTML.width+15):(contentBox.width)}" height="{contentBox.height-24}" id="scrollHTML">
<s:Group>
<s:BitmapImage id="cropHTML" />
<s:SpriteVisualElement id="cropDraw" width="{cropHTML.width}" height="{cropHTML.height}" />
<mx:Box width="{cropHTML.width}" height="{cropHTML.height}" alpha="0" id="drawArea" backgroundColor="#000000"/>
<s:Label id="displayWidth" color="#000000" mouseEnabled="false" />
<s:Label id="displayHeight" color="#000000" mouseEnabled="false" />
</s:Group>
</s:Scroller>
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="cropsettings">

</s:NavigatorContent>

<s:NavigatorContent id="export">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText" text="{exportText}" />
</s:VGroup>
</s:NavigatorContent>
</mx:ViewStack>
</mx:Box>
</s:VGroup>

</s:WindowedApplication>

Thanks for reading!

Read More

Creating a Flex AIR Screenshot app Part 7

In this tutorial we are going to start adding functionality to the screenshot feature.

The screenshot feature is our programs main feature, and the crop feature will later work based on the functions well make during creating this screenshot functionality, so it is wise to start with it before we go any deeper into the crop functionality. This doesnt mean that everything weve done for the crop stage window is for nothing - no, on the contrary, we will use it in the future, when we return to coding it.

Anyway, lets start now. Go to the loadpage NavigatorContent container and swap the image buttons (the Screenshot button should stand first, since it really is the main attraction of the program). Add a click event listener for the screenshot button, set the handler to goScreenshot() function:

<s:NavigatorContent id="loadpage">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">Enter the link to the page:</s:Label>
<s:HGroup>
<s:TextInput width="250" id="urlInput" text="http://" /><s:Button label="Browse local..." click="doBrowse();" />
</s:HGroup>
<s:HGroup>
<custom:ImageButton img="@Embed(../lib/b_screenshot.png)" over="@Embed(../lib/b_screenshot_over.png)" toolTip="Take screenshot" click="goScreenshot();" buttonMode="true" enabled="{urlInput.text!=}" />
<custom:ImageButton img="@Embed(../lib/b_cut.png)" over="@Embed(../lib/b_cut_over.png)" toolTip="Crop area" click="goCrop();" buttonMode="true" enabled="{urlInput.text!=}" />
</s:HGroup>
</s:VGroup>
</s:NavigatorContent>

When the user clicks on this button, according to our scheme - the user should be taken to export settings. However, there is a process to be made before that - and that is loading the actual site to later take picture of it. To do that, well need to create another state for our application - so create a new NavigatorContent with an id of "screenshotloading". It should contain text saying that the user should wait until the page is loaded, and a button to cancel loading. Set the buttons click event handler to cancelLoading().

<s:NavigatorContent id="loadpage">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">Enter the link to the page:</s:Label>
<s:HGroup>
<s:TextInput width="250" id="urlInput" text="http://" /><s:Button label="Browse local..." click="doBrowse();" />
</s:HGroup>
<s:HGroup>
<custom:ImageButton img="@Embed(../lib/b_screenshot.png)" over="@Embed(../lib/b_screenshot_over.png)" toolTip="Take screenshot" click="goScreenshot();" buttonMode="true" enabled="{urlInput.text!=}" />
<custom:ImageButton img="@Embed(../lib/b_cut.png)" over="@Embed(../lib/b_cut_over.png)" toolTip="Crop area" click="goCrop();" buttonMode="true" enabled="{urlInput.text!=}" />
</s:HGroup>
</s:VGroup>
</s:NavigatorContent>

Add this NavigatorContent after loadpage one. This way it is second in the stack.

Go to declarations and add a new object for the headings for this stack item, place it as the second item:

<fx:Declarations>
<mx:ArrayCollection id="headerTitles">
<fx:Object step="Step one:" description="load a web page." />
<fx:Object step="Loading..." description="please wait." />
<fx:Object step="Step two:" description="set your export preferences." />
<fx:Object step="Step two:" description="select the area you wish to crop." />
<fx:Object step="Step three:" description="set your export preferences for the cropped image." />
<fx:Object step="Final step:" description="please wait while your images are being expored." />
</mx:ArrayCollection>
</fx:Declarations>

The loading process is like this - we create a temporary HTML object somewhere (without displaying it to the user) and load the content inside of it. Listen to its COMPLETE event, when that is dispatched - go to next view stack item.

First declare this HTML object:

private var tempHTML:HTML = new HTML();

Create a goScreenshot() function. Inside of it, set the stacks selectedChild to screenshotloading page, set urlString to the input text fields text value. Add the HTML object using addElement() method, set its visibility to false, add an event listener and load the page.

private function goScreenshot():void {
stack.selectedChild = screenshotloading;
urlString = urlInput.text;

addElement(tempHTML);
tempHTML.visible = false;
tempHTML.addEventListener(Event.COMPLETE, onTempLoad);
tempHTML.htmlLoader.load(new URLRequest(urlString));
}

In the onTempLoad() function, set selectedChild of the stack to screenshotsettings:

private function onTempLoad(evt:Event):void {
stack.selectedChild = screenshotsettings;
}

When the user cancels the loading, cancelLoading() is called. Inside of it, remove the event listener for the COMPLETE event, cancel loading usign cancelLoad() method and return to loadpage. Remove tempHTML using removeElement:

private function cancelLoading():void {
tempHTML.removeEventListener(Event.COMPLETE, onTempLoad);
tempHTML.cancelLoad();
stack.selectedChild = loadpage;
removeElement(tempHTML);
}

And were done.

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:custom="*"
                       xmlns:mx="library://ns.adobe.com/flex/mx" showStatusBar="false"
   width="550" height="300" creationComplete="init();">
   
   
<fx:Declarations>
<mx:ArrayCollection id="headerTitles">
<fx:Object step="Step one:" description="load a web page." />
<fx:Object step="Loading..." description="please wait." />
<fx:Object step="Step two:" description="set your export preferences." />
<fx:Object step="Step two:" description="select the area you wish to crop." />
<fx:Object step="Step three:" description="set your export preferences for the cropped image." />
<fx:Object step="Final step:" description="please wait while your images are being expored." />
</mx:ArrayCollection>
</fx:Declarations>

<fx:Style>
@namespace s "library://ns.adobe.com/flex/spark";
@namespace mx "library://ns.adobe.com/flex/mx";

.descriptionText{
fontSize: 24;
color: #fff;
}

#headStep{
fontSize: 30;
fontWeight: bold;
color: #ffffbb;
}

#headDesc{
fontSize: 30;
color: #ffffff;
}
</fx:Style>

<fx:Script>
<![CDATA[
import flash.display.Bitmap;
import flash.display.BitmapData;
import flash.events.Event;
import flash.events.MouseEvent;
import flash.filesystem.File;
import flash.geom.Rectangle;
import flash.net.URLRequest;
import mx.controls.HTML;
import mx.core.FlexHTMLLoader;
import mx.events.FlexNativeWindowBoundsEvent;
import mx.controls.Alert;
import mx.graphics.ImageSnapshot;
import spark.primitives.BitmapImage;

[Bindable]
private var urlString:String;
[Bindable]
private var canSelect:Boolean = true;
private var tempHTML:HTML = new HTML();

private function init():void {
addEventListener(FlexNativeWindowBoundsEvent.WINDOW_RESIZE, onResize);
}

private function doBrowse():void{
var file:File = new File();
file.addEventListener(Event.SELECT, browseSelect);
file.browseForOpen("Load a webpage");

function browseSelect(evt:Event):void {
urlInput.text = file.nativePath;
}
}

private function goCrop():void {
stack.selectedChild = crop;
urlString = urlInput.text;
}

private function goScreenshot():void {
stack.selectedChild = screenshotloading;
urlString = urlInput.text;

addElement(tempHTML);
tempHTML.visible = false;
tempHTML.addEventListener(Event.COMPLETE, onTempLoad);
tempHTML.htmlLoader.load(new URLRequest(urlString));
}

private function onTempLoad(evt:Event):void {
stack.selectedChild = screenshotsettings;
}

private function cancelLoading():void {
tempHTML.removeEventListener(Event.COMPLETE, onTempLoad);
tempHTML.cancelLoad();
stack.selectedChild = loadpage;
removeElement(tempHTML);
}

private function changeState():void {
if (stack.selectedChild == loadpage) {
contentBox.setStyle("horizontalAlign", "center");
urlInput.text = urlString;
}
if (stack.selectedChild == crop) {
maximize();
canSelect = true;
contentBox.setStyle("horizontalAlign", "left");
cropHTML.htmlLoader.load(new URLRequest(urlString));
cropHTML.width = contentBox.width;
cropHTML.height = contentBox.height - 24;
cropStatus.text = "Loading";
cropStatus.setStyle("color", "#ffff00");
cropHTML.addEventListener(Event.COMPLETE, onCropLoad);
}
}

private function onCropLoad(evt:Event):void {
cropStatus.text = "Loaded";
cropStatus.setStyle("color", "#ffffff");
}

private function onResize(evt:Event):void {
if (stack.selectedChild == crop) {
cropHTML.width = contentBox.width;
cropHTML.height = contentBox.height - 24;
}
}
]]>
</fx:Script>
   
<s:VGroup width="100%" height="100%" gap="0">
<mx:HBox backgroundColor="#333333" height="46" width="100%" paddingTop="10" paddingLeft="10">
<s:Label id="headStep" text="{headerTitles.getItemAt(stack.selectedIndex).step}" />
<s:Label id="headDesc" text="{headerTitles.getItemAt(stack.selectedIndex).description}" />
</mx:HBox>
<mx:Box backgroundColor="#666666" width="100%" height="100%" id="contentBox" horizontalAlign="center">
<mx:ViewStack id="stack" change="changeState();">
<s:NavigatorContent id="loadpage">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">Enter the link to the page:</s:Label>
<s:HGroup>
<s:TextInput width="250" id="urlInput" text="http://" /><s:Button label="Browse local..." click="doBrowse();" />
</s:HGroup>
<s:HGroup>
<custom:ImageButton img="@Embed(../lib/b_screenshot.png)" over="@Embed(../lib/b_screenshot_over.png)" toolTip="Take screenshot" click="goScreenshot();" buttonMode="true" enabled="{urlInput.text!=}" />
<custom:ImageButton img="@Embed(../lib/b_cut.png)" over="@Embed(../lib/b_cut_over.png)" toolTip="Crop area" click="goCrop();" buttonMode="true" enabled="{urlInput.text!=}" />
</s:HGroup>
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="screenshotloading">
<s:VGroup width="100%" horizontalAlign="center" paddingTop="20">
<s:Label styleName="descriptionText">The page is being loaded...</s:Label>
<s:Button label="Cancel" click="cancelLoading();" />
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="screenshotsettings">

</s:NavigatorContent>

<s:NavigatorContent id="crop">
<s:VGroup width="100%" height="100%" gap="0">
<mx:HBox backgroundColor="#999999" width="100%" height="24" verticalScrollPolicy="off" verticalAlign="middle" paddingLeft="2">
<s:Button label="Back" click="{cropHTML.htmlLoader.loadString(); stack.selectedChild = loadpage;}" />
<s:Button label="Export selection" enabled="false" />
<s:Label id="cropStatus" />
<s:Label text="{urlString}" />
</mx:HBox>
<mx:HTML id="cropHTML" horizontalScrollPolicy="on" verticalScrollPolicy="on" />
</s:VGroup>
</s:NavigatorContent>

<s:NavigatorContent id="cropsettings">

</s:NavigatorContent>

<s:NavigatorContent id="export">

</s:NavigatorContent>
</mx:ViewStack>
</mx:Box>
</s:VGroup>

</s:WindowedApplication>

Thanks for reading!

Read More

KirSQLite Flex AIR Database Manager Part 31

In this tutorial we will finish our "Copy table" feature.

Right now, we have the pop up window working properly - all we have to do is fill the doCopyTable() function with code that actually copies the table.

First of all, we need to declare two variables - one is an Array "columns". We set its value using getSchemColumns() function and well use it to get the full column declaration string from currently selected table. The second variable is destDatabase - a String that contains the value of the id name of the destination database.

var columns:Array = getSchemaColumns();
var destDatabase:String = copyDestinationDatabase.selectedItem.did;

Then we can create an SQLStatement object and enter a query that creates a new table with the specified name and in specified database using the information we have. Declare its columns using String(columns):

var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "CREATE TABLE " + destDatabase + "." + copyTableName.text + " (" + String(columns) + ")";
cstat.execute( -1, new Responder(copySuccess, copyError));
lastStatement(cstat.text);

You can see I added a responder with copySuccess and copyError handler functions. The copyError() function simply displays the error:

function copyError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}

The copySuccess() function copies all the data from selected table to the new table. Use columnNames() function to get the names of the columns. Dont forget to add a Responder:

function copySuccess(evt:SQLResult):void {
// copy all the data
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + destDatabase + "." + copyTableName.text + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
istat.execute( -1, new Responder(copyInsertSuccess, copyInsertError));
lastStatement(istat.text);
}

The copyInsertSuccess() function calls loadDataSchema() on the destination database to refresh its contents. Unselect the selected table in tableTree and clear the tableData ArrayCollection. Then close the window.

function copyInsertSuccess(evt:SQLResult):void {
// update xml
loadDataSchema(destDatabase);
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
// close window
closeCopyTableWindow();
}

In case of error (copyInsertError()), we need to delete the newly created table:

function copyInsertError(evt:SQLError):void {
// delete new table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + destDatabase + "." + copyTableName.text;
dstat.execute();
Alert.show("ERROR: " + evt.details, "Error");
}

Full function:

private function doCopyTable():void {
// create table
var columns:Array = getSchemaColumns();
var destDatabase:String = copyDestinationDatabase.selectedItem.did;
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "CREATE TABLE " + destDatabase + "." + copyTableName.text + " (" + String(columns) + ")";
cstat.execute( -1, new Responder(copySuccess, copyError));
lastStatement(cstat.text);
function copySuccess(evt:SQLResult):void {
// copy all the data
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + destDatabase + "." + copyTableName.text + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
istat.execute( -1, new Responder(copyInsertSuccess, copyInsertError));
lastStatement(istat.text);
}
function copyError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
function copyInsertSuccess(evt:SQLResult):void {
// update xml
loadDataSchema(destDatabase);
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
// close window
closeCopyTableWindow();
}
function copyInsertError(evt:SQLError):void {
// delete new table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + destDatabase + "." + copyTableName.text;
dstat.execute();
Alert.show("ERROR: " + evt.details, "Error");
}
}

Now lets fix an unrelated bug. Go to dropTable() function and add a line in dropTableSuccess() after calling loadDataSchema() that empties the tableData ArrayCollection. It should be empty since nothing is selected in the tree.

private function dropTable():void {
Alert.show("Are you sure you want to completely delete this table?", "Drop table?", Alert.YES | Alert.NO, null, dropConfirm);
function dropConfirm(evt:CloseEvent):void {
if (evt.detail == Alert.YES) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
lastStatement(stat.text);
stat.execute( -1, new Responder(dropTableSuccess, dropTableError));
}
}
function dropTableSuccess(evt:SQLResult):void {
loadDataSchema(selectedDatabase);
tableData = new ArrayCollection([]);
}
function dropTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

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" showStatusBar="false">
   
<s:menu>
<mx:FlexNativeMenu dataProvider="{windowMenu}" showRoot="false" labelField="@label" keyEquivalentField="@key" itemClick="menuSelect(event);" />
</s:menu>

<fx:Declarations>
<fx:XML id="windowMenu">
<root>
<menuitem label="Database">
<menuitem id="newdb" label="New" key="n" controlKey="true" />
<menuitem id="opendb" label="Open" key="o" controlKey="true" />
<menuitem id="savedb" label="Save a copy" key="s" controlKey="true" enabled="{tableTree.selectedItems.length>0}"/>
</menuitem>
<menuitem label="Table">
<menuitem id="newtable" label="Add table" key="t" controlKey="true" enabled="{tableTree.selectedItems.length>0}"/>
<menuitem id="droptable" label="Drop table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="renametable" label="Rename table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="copytable" label="Copy table" controlKey="true" enabled="{isTableSelected}"/>
</menuitem>
</root>
</fx:XML>
<fx:XMLList id="dbData">
</fx:XMLList>
<mx:ArrayCollection id="tableData">
</mx:ArrayCollection>
<mx:ArrayCollection id="columnData">
</mx:ArrayCollection>
<mx:ArrayCollection id="resultData">
</mx:ArrayCollection>
<mx:ArrayCollection id="databaseData">
</mx:ArrayCollection>
<mx:ArrayCollection id="conflictTypes">
<fx:String>---</fx:String>
<fx:String>ABORT</fx:String>
<fx:String>FAIL</fx:String>
<fx:String>IGNORE</fx:String>
<fx:String>ROLLBACK</fx:String>
<fx:String>REPLACE</fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="dataTypes">
<fx:String>NONE</fx:String>
<fx:String>INTEGER</fx:String>
<fx:String>TEXT</fx:String>
<fx:String>REAL</fx:String>
<fx:String>NUMERIC</fx:String>
</mx:ArrayCollection>
<mx:AdvancedDataGridColumn id="checkboxColumn" headerText=" " width="30" sortable="false" draggable="false" resizable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:CheckBox selected="@{data.sel}" />
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
<mx:TitleWindow id="newTableWindow" title="Create new table" close="closeNewTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="newTableName" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Key column: </s:Label>
<s:TextInput id="keyName" />
</s:HGroup>
<s:Button click="createNewTable();" label="Create" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="newRecordWindow" title="Add new record" close="closeNewRecordWindow();" showCloseButton="true">
<s:VGroup width="100%" height="100%">
<mx:AdvancedDataGrid id="recordDataGrid" width="100%" height="50" editable="true"/>
<s:Button click="addNewRecord();" label="Add record" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="historyWindow" title="SQL History" close="closeHistoryWindow();" showCloseButton="true">
<mx:Box width="100%" height="100%" paddingLeft="10" paddingRight="10" paddingTop="10" paddingBottom="10">
<s:TextArea id="historyText" width="100%" height="100%" editable="false" />
</mx:Box>
</mx:TitleWindow>
<mx:TitleWindow id="renameTableWindow" title="Rename table" close="closeRenameTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="renameTableName" />
</s:HGroup>
<s:Button click="doRenameTable();" label="Rename" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="copyTableWindow" title="Copy table" close="closeCopyTableWindow();" showCloseButton="true">
<s:VGroup paddingTop="8">
<s:Label id="selectedCopyTable" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="copyDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New table name: </s:Label>
<s:TextInput id="copyTableName" />
</s:HGroup>
<s:Button click="doCopyTable();" label="Copy" width="100%" />
</s:VGroup>
</mx:TitleWindow>
</fx:Declarations>

<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLSchema;
import flash.data.SQLSchemaResult;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.Event;
import flash.events.MouseEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import flash.net.FileFilter;
import flash.net.FileReference;
import flash.net.Responder;
import flash.ui.ContextMenu;
import flash.ui.ContextMenuItem;
import mx.collections.ArrayCollection;
import mx.controls.advancedDataGridClasses.AdvancedDataGridColumn;
import mx.controls.Alert;
import mx.events.CloseEvent;
import mx.events.FlexNativeMenuEvent;
import mx.managers.PopUpManager;

private var connection:SQLConnection = new SQLConnection();
[Bindable]
private var selectedDatabase:String = "";
[Bindable]
private var isTableSelected:Boolean = false;
private var sqlHistory:Array = [];
private var recordColumnNames:Array = [];
private var recordColumnNamesFull:Array = [];
[Bindable]
private var fullSelectedTable:String = "";

private function selectAllChange(evt:Event):void {
var i:int;
if (evt.currentTarget.selected) {
for (i = 0; i < tableData.length; i++) {
tableData[i].sel = true;
}
} else
if (!evt.currentTarget.selected) {
for (i = 0; i < tableData.length; i++) {
tableData[i].sel = false;
}
}
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}

private function menuSelect(evt:FlexNativeMenuEvent):void {
(evt.item.@id == "newdb")?(newDatabase()):(void);
(evt.item.@id == "opendb")?(openDatabase()):(void);
(evt.item.@id == "newtable")?(newTable()):(void);
(evt.item.@id == "droptable")?(dropTable()):(void);
(evt.item.@id == "savedb")?(saveCopy()):(void);
(evt.item.@id == "renametable")?(renameTable()):(void);
(evt.item.@id == "copytable")?(copyTable()):(void);
}

private function newDatabase():void {
var file:File = File.desktopDirectory.resolvePath("Untitled");
file.addEventListener(Event.SELECT, newSelect);
file.browseForSave("Choose where to save the database");
var newDB:XML;
var statement:SQLStatement = new SQLStatement();
function newSelect(evt:Event):void {
if (file.exists) {
Alert.show("File already exists, cannot overwrite.", "Nope");
return;
}
file.nativePath += ".db";
var n:String = parseDatabase(file);
loadDataSchema(n);
}
}

private function openDatabase():void {
var file:File = new File();
file.browseForOpen("Open database", [new FileFilter("Databases", "*.db"), new FileFilter("All files", "*")]);
file.addEventListener(Event.SELECT, openSelect);

function openSelect(evt:Event):void {
var n:String = parseDatabase(file, true);
loadDataSchema(n);
}
}

private function saveCopy():void {
var databasePath:String;
if (selectedDatabase == "main") databasePath = dbData.db[0].@path;
if (selectedDatabase != "main") {
var newNum:int = Number(selectedDatabase.replace("db", ""));
var newInd:int;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (dbData.db[i].@numid == newNum) {
newInd = i;
break;
}
}
databasePath = dbData.db[newInd].@path;
}
var file:File = new File(databasePath);
file.browseForSave("Save copy of database");
file.addEventListener(Event.SELECT, onCopySelect);
function onCopySelect(evt:Event):void {
if(notAlreadyOpen(file)){
var initFile:File = new File(databasePath);
initFile.copyTo(file, true);
}else {
Alert.show("Cannot overwrite a file that is currently open.", "Nope");
}
}
}

private function loadDataSchema(name:String):void {
if (name != "") {
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;
// Adding tables:
var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
dataNode.appendChild(newTable);
}
}
function schemaError(evt:SQLError):void {
// Alert.show("Database is empty");
}
isTableSelected = false;
}
}

private function parseDatabase(file:File, needCheck:Boolean = false):String {
var ret:String = "";
if (!needCheck || file.exists) {
if(!needCheck || notAlreadyOpen(file)){
var newDB:XML;
if (dbData.db.length() == 0) {
connection.open(file);
dbData = new XMLList(<root></root>);
newDB = <db/>
newDB.@label = file.name + "(main)";
newDB.@name = file.name;
newDB.@numid = 1;
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "main";
}else
if (dbData.db.length() > 0) {
var newnum:int = dbData.db.length() + 1;
connection.attach("db"+newnum.toString(), file);
newDB = <db/>
newDB.@label = file.name + "(db" + newnum.toString() + ")";
newDB.@name = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "db" + newnum.toString();
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
return ret;
}

private function notAlreadyOpen(file:File):Boolean{ 
var r:Boolean = true;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (file.nativePath == dbData.db[i].@path) {
r = false;
}
}
return r;
}

private function tableSelect():void {
saveTableButton.emphasized = false;
fullSelectedTable = "";
columnData = new ArrayCollection([]);
if(col_name!=null){
col_name.text = "";
col_data.selectedIndex = 0;
col_key.selected = false;
col_auto.selected = false;
col_unique.selected = false;
col_null.selected = false;
col_default.text = "";
col_conflict.selectedIndex = 0;
}
if (tableTree.selectedItem.@isBranch) {
isTableSelected = false;
var dataname:String;
if (tableTree.selectedItem.@numid == 1) dataname = "main";
if (tableTree.selectedItem.@numid > 1) dataname = "db" + tableTree.selectedItem.@numid;
selectedDatabase = dataname;
}
if (tableTree.selectedItem.@isBranch == false) {
isTableSelected = true;
selectedDatabase = tableTree.selectedItem.@databaseName;
fullSelectedTable = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
tableData = new ArrayCollection([]);
var newColumns:Array = [checkboxColumn];
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
for (var i:int = 0; i < schema.tables[0].columns.length; i++) {
columnData.addItem({name:schema.tables[0].columns[i].name});
var aColumn:AdvancedDataGridColumn = new AdvancedDataGridColumn();
aColumn.headerText = schema.tables[0].columns[i].name;
aColumn.dataField = "db_" + schema.tables[0].columns[i].name;
if (schema.tables[0].columns[i].autoIncrement) aColumn.editable = false;
if (schema.tables[0].columns[i].primaryKey) tableTree.selectedItem.@primaryKeyColumn = schema.tables[0].columns[i].name;
newColumns.push(aColumn);
}
tableGrid.columns = newColumns;
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT * FROM " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
stat.execute(-1, new Responder(tableSuccess, tableError));
}
function tableSuccess(evt:SQLResult):void {
if (evt.data != null) {
for (var item:Object in evt.data) {
var obj:Object = new Object();
for (var value:Object in evt.data[item]) {
obj["db_"+value] = evt.data[item][value];
}
tableData.addItem(obj);
}
}
}
function tableError(evt:SQLError):void {
Alert.show("Unable to read table data.", "Error");
}
}

private function newTable():void {
PopUpManager.addPopUp(newTableWindow, this);
PopUpManager.centerPopUp(newTableWindow);
newTableWindow.title = "Create new table";
focusManager.setFocus(newTableName);
}

private function dropTable():void {
Alert.show("Are you sure you want to completely delete this table?", "Drop table?", Alert.YES | Alert.NO, null, dropConfirm);
function dropConfirm(evt:CloseEvent):void {
if (evt.detail == Alert.YES) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
lastStatement(stat.text);
stat.execute( -1, new Responder(dropTableSuccess, dropTableError));
}
}
function dropTableSuccess(evt:SQLResult):void {
loadDataSchema(selectedDatabase);
tableData = new ArrayCollection([]);
}
function dropTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

private function closeNewTableWindow():void{
PopUpManager.removePopUp(newTableWindow);
}

private function createNewTable():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS " + selectedDatabase + "." + newTableName.text + "(" + keyName.text + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)";
lastStatement(stat.text);
stat.execute( -1, new Responder(newTableSuccess, newTableError));
function newTableSuccess(evt:SQLResult):void {
closeNewTableWindow();
loadDataSchema(selectedDatabase);
}
function newTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

private function lastStatement(text:String):void {
statementText.text = text;
sqlHistory.push(text);
}

private function openHistory():void {
PopUpManager.addPopUp(historyWindow, this);
historyWindow.width = width - 100;
historyWindow.height = height - 100;
PopUpManager.centerPopUp(historyWindow);
historyText.text = "";
for (var i:int = sqlHistory.length - 1; i >= 0; i--) {
historyText.appendText(sqlHistory[i] + "
");
}
}

private function closeHistoryWindow():void {
PopUpManager.removePopUp(historyWindow);
}

private function saveTable():void {
connection.begin();
var keyColumnName:String = tableTree.selectedItem.@primaryKeyColumn;
// clear all "sel" and store them in a temp array
var tempSel:Array = [];
for (var s:int = 0; s < tableData.length; s++) {
if (tableData[s].sel) {
tempSel.push(true);
tableData[s].sel = false;
}else
if (!tableData[s].sel) {
tempSel.push(false);
}
}
// update each row
for (var i:int = 0; i < tableData.length; i++) {
var stat:SQLStatement = new SQLStatement();
var sqlStat:String = "UPDATE " + selectedDatabase + "." + tableTree.selectedItem.@label + " SET";
// add each attribute as parameter
for (var attribute:String in tableData[i]) {
// if column is not our CheckBox column or the key column
if (attribute != "mx_internal_uid" && attribute!=keyColumnName && attribute!="sel") {
// add value as parameter
stat.parameters["@" + attribute.substr(3)] = tableData[i][attribute];
sqlStat += " " + attribute.substr(3) + "=@" + attribute.substr(3) + ",";
}
}
// remove the last comma
sqlStat = sqlStat.substr(0, sqlStat.length - 1);
sqlStat += " WHERE " + keyColumnName + "=" + tableData[i]["db_"+keyColumnName];
stat.sqlConnection = connection;
stat.text = sqlStat;
lastStatement(stat.text);
stat.execute( -1, new Responder(saveSuccess, saveError));
}

function saveSuccess(evt:SQLResult):void {
}

function saveError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}

tableSelect();
for (var t:int = 0; t < tableData.length; t++) {
if (tempSel[t]) tableData[t].sel=true;
}
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
connection.commit();
}

private function deleteSelected():void {
connection.begin();
var keyColumnName:String = tableTree.selectedItem.@primaryKeyColumn;
for (var i:int = 0; i < tableData.length; i++) {
if (tableData[i].sel) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM " + selectedDatabase + "." + tableTree.selectedItem.@label + " WHERE " + keyColumnName + "=" + tableData[i]["db_" + keyColumnName];
lastStatement(stat.text);
stat.execute( -1, new Responder(deleteSuccess, deleteError));
}
}
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();

function deleteSuccess(evt:SQLResult):void {
}
function deleteError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
connection.commit();
}

private function newRecord():void {
PopUpManager.addPopUp(newRecordWindow, this);
newRecordWindow.width = width - 100;
newRecordWindow.height = 120;
PopUpManager.centerPopUp(newRecordWindow);

var columns:Array = getSchemaColumns();

var defaultItem:Object = new Object();
recordColumnNames = [];
recordColumnNamesFull = [];

var recordColumns:Array = [];
for (var i:int = columns.length-1; i >= 0; i--) {
if (columns[i].indexOf(" AUTOINCREMENT") == -1) {
recordColumnNames.push(columnData[i].name);
recordColumnNamesFull.push(columns[i]);
var advColumn:AdvancedDataGridColumn = new AdvancedDataGridColumn(columns[i]);
recordColumns.push(advColumn);
// find DEFAULT and extract it
var defaultMatch:String = "";
var defaultPattern:RegExp = /((DEFAULT)s((".+")|([0-9]+)))/i;
if (columns[i].match(defaultPattern)) {
defaultMatch = columns[i].match(defaultPattern)[0];
// delete "DEFAULT" from the match
defaultMatch = defaultMatch.substr(8);
// if any quotes are found, remove the first and last symbols
if (defaultMatch.indexOf(") != -1) {
defaultMatch = defaultMatch.substring(1, defaultMatch.length - 1);
}
}
defaultItem[columns[i]] = defaultMatch;
}
if (columns[i].indexOf(" AUTOINCREMENT") != -1) {
columns.splice(i, 1);
}
}

// if no fields to edit, insert blank
if (columns.length == 0) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO " + selectedDatabase + "." + tableTree.selectedItem.@label + " DEFAULT VALUES;";
lastStatement(stat.text);
stat.execute( -1, new Responder(newSuccess, newError));

function newSuccess(evt:SQLResult):void {
closeNewRecordWindow();
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}
function newError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
return;
}

recordDataGrid.columns = recordColumns;
recordDataGrid.dataProvider = new ArrayCollection([defaultItem]);
}

private function closeNewRecordWindow():void {
PopUpManager.removePopUp(newRecordWindow);
}

private function addNewRecord():void {
// placeholder code that inserts empty row:
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO " + selectedDatabase + "." + tableTree.selectedItem.@label + " (";
stat.text += String(recordColumnNames);
stat.text += ") VALUES (";
var values:Array = [];
for (var i:int = 0; i < recordColumnNames.length; i++) {
var val:String;
var dataValue:String = recordDataGrid.dataProvider[0][recordColumnNamesFull[i]];
if (isNaN(Number(dataValue))) val = " + dataValue + ";
if (!isNaN(Number(dataValue))) val = dataValue;
values.push(val);
}
stat.text += String(values);
stat.text += ");";
lastStatement(stat.text);
stat.execute( -1, new Responder(newSuccess, newError));

function newSuccess(evt:SQLResult):void {
closeNewRecordWindow();
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}
function newError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function columnSelect():void {
var columns:Array = getSchemaColumns();
// get the currently selected column
var currentColumn:String = columns[columnList.selectedIndex];
// delete the name of the column from this text
var currentParameters:String = currentColumn.substr(currentColumn.indexOf(columnList.selectedItem.name) + columnList.selectedItem.name.length + 1);
// find DEFAULT and extract it
var defaultMatch:String = "";
var defaultPattern:RegExp = /((DEFAULT)s((".+")|([0-9]+)))/i;
if (currentParameters.match(defaultPattern)) {
defaultMatch = currentParameters.match(defaultPattern)[0];
// delete it from currentParameters
currentParameters = currentParameters.replace(defaultMatch, "");
// delete "DEFAULT" from the match
defaultMatch = defaultMatch.substr(8);
// if any quotes are found, remove the first and last symbols
if (defaultMatch.indexOf(") != -1) {
defaultMatch = defaultMatch.substring(1, defaultMatch.length - 1);
}
}
// find ON CONFLICT and extract it
var conflictMatch:String = "";
var conflictPattern:RegExp = /((ON CONFLICT)s(ABORT|FAIL|IGNORE|ROLLBACK|REPLACE))/i;
if (currentParameters.match(conflictPattern)) {
conflictMatch = currentParameters.match(conflictPattern)[0];
// delete it from currentParameters
currentParameters = currentParameters.replace(conflictMatch, "");
// delete "ON CONFLICT" from the match
conflictMatch = conflictMatch.substr(12);
}
// apply values
col_name.text = columnList.selectedItem.name;
col_key.selected = (currentParameters.toUpperCase().indexOf("PRIMARY KEY") != -1)?(true):(false);
col_auto.selected = (currentParameters.toUpperCase().indexOf("AUTOINCREMENT") != -1)?(true):(false);
col_unique.selected = (currentParameters.toUpperCase().lastIndexOf("UNIQUE") != -1)?(true):(false);
col_null.selected = (currentParameters.toUpperCase().indexOf("NOT NULL") != -1)?(false):(true);
col_default.text = defaultMatch;
col_conflict.selectedIndex = 0;
if (conflictMatch.toUpperCase() == "ABORT") col_conflict.selectedIndex = 1;
if (conflictMatch.toUpperCase() == "FAIL") col_conflict.selectedIndex = 2;
if (conflictMatch.toUpperCase() == "IGNORE") col_conflict.selectedIndex = 3;
if (conflictMatch.toUpperCase() == "ROLLBACK") col_conflict.selectedIndex = 4;
if (conflictMatch.toUpperCase() == "REPLACE") col_conflict.selectedIndex = 5;

// read data type
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
col_data.textInput.text = schema.tables[0].columns[columnList.selectedIndex].dataType;

// enable or disable ON CONFLICT
checkConflict();
// unhighlight "Update selected"
col_b_update.emphasized = false;
}

private function checkConflict():void {
if (col_key.selected || !col_null.selected || col_unique.selected) {
col_conflict.enabled = true;
}else {
col_conflict.enabled = false;
}
}

private function formChange():void {
checkConflict();
if (columnList.selectedItems.length > 0) {
col_b_update.emphasized = true;
}
}

private function addColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;
if (col_name.text != "" && col_data.textInput.text != "" && (col_null.selected || col_default.text != "")) {
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var fullSQL:String = schema.tables[0].sql;
// extract the text inside the ( )
fullSQL = fullSQL.substring( fullSQL.indexOf("(") + 1 , fullSQL.lastIndexOf(")") );
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label + " (";
sqlText += fullSQL + ", ";
// add the new column
sqlText += col_name.text + " " + col_data.textInput.text + " ";
if (col_key.selected) sqlText += "PRIMARY KEY ";
if (col_key.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_key.selected && col_auto.selected) sqlText += "AUTOINCREMENT ";
if (!col_null.selected) sqlText += "NOT NULL ";
if (!col_null.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_unique.selected) sqlText += "UNIQUE ";
if (col_unique.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_default.text != "") {
sqlText += "DEFAULT ";
if (isNaN(Number(col_default.text))) sqlText += " + col_default.text + ";
if (!isNaN(Number(col_default.text))) sqlText += col_default.text;
}
sqlText += ");";
lastStatement(sqlText);

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;
bstat.text = "CREATE TABLE " + selectedDatabase + "." + backupName + " (" + fullSQL + ");";
bstat.execute();

// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " SELECT * FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute();

// Delete initial table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Create new table
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = sqlText;
stat.execute( -1, new Responder(newColumnSuccess, newColumnError));
}else {
Alert.show("Please fill all the required fields!", "Error");
}
function newColumnSuccess(evt:SQLResult):void {
// Insert previous values
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + selectedDatabase + "." + prevTableName + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + backupName;
istat.execute( -1, new Responder(newColumnInsertSuccess, newColumnInsertError));
function newColumnInsertSuccess(evt:SQLResult):void {
// Delete backup
var bdstat:SQLStatement = new SQLStatement();
bdstat.sqlConnection = connection;
bdstat.text = "DROP TABLE " + selectedDatabase + "." + backupName;
bdstat.execute();
tableSelect();
}
function newColumnInsertError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details + "

Restoring the database using backup...", "Error");
// Delete existing table
var destat:SQLStatement = new SQLStatement();
destat.sqlConnection = connection;
destat.text = "DROP TABLE " + selectedDatabase + "." + prevTableName;
destat.execute();
// Restore table using backup
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
}
function newColumnError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details + "

Restoring the database using backup...", "Error");
// Restore table
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
}

private function deleteColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;

var columns:Array = getSchemaColumns();
// get the currently selected index
var currentIndex:int = columnList.selectedIndex;

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + backupName + " (";
columns.splice(currentIndex, 1);
if (columns.length == 0) {
Alert.show("Cant make a table completely empty! Leave at least one column.", "Nope");
return;
}
sqlText += String(columns);
sqlText += ");";
bstat.text = sqlText;
lastStatement(sqlText);
bstat.execute();

// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " (" + columnNames(currentIndex) + ") SELECT " + columnNames(currentIndex) + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute();

// Drop existing table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Rename backup table to initial name
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName
rstat.execute();
tableSelect();
}

private function updateColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;

if (col_name.text != "" && col_data.textInput.text != "" && (col_null.selected || col_default.text != "")) {
var columns:Array = getSchemaColumns();
// get the currently selected index
var currentIndex:int = columnList.selectedIndex;

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;

// Compose updated column info
var newColumn:String = col_name.text + " " + col_data.textInput.text + " ";
if (col_key.selected) newColumn += "PRIMARY KEY ";
if (col_key.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_key.selected && col_auto.selected) newColumn += "AUTOINCREMENT ";
if (!col_null.selected) newColumn += "NOT NULL ";
if (!col_null.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_unique.selected) newColumn += "UNIQUE ";
if (col_unique.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_default.text != "") {
newColumn += "DEFAULT ";
if (isNaN(Number(col_default.text))) newColumn += " + col_default.text + ";
if (!isNaN(Number(col_default.text))) newColumn += col_default.text;
}

columns[currentIndex] = newColumn;

// Compose the table creation query
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + backupName + " (";
sqlText += String(columns);
sqlText += ");";
bstat.text = sqlText;
lastStatement(sqlText);
bstat.execute( -1, new Responder(updateColumnSuccess, updateColumnError));

function updateColumnSuccess(evt:SQLResult):void { 
// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " (" + columnNames(currentIndex) + ") SELECT " + columnNames(currentIndex) + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute(-1, new Responder(updateInsertColumnSuccess, updateInsertColumnError));

function updateInsertColumnSuccess(evt:SQLResult):void { 
// Drop existing table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Rename backup table to initial name
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
function updateInsertColumnError(evt:SQLError):void {
// Delete backup
var bdstat:SQLStatement = new SQLStatement();
bdstat.sqlConnection = connection;
bdstat.text = "DROP TABLE " + selectedDatabase + "." + backupName;
bdstat.execute();
tableSelect();
Alert.show("ERROR: " + evt.details, "Error");
}
}
function updateColumnError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}else {
Alert.show("Please fill all the required fields!", "Error");
}
}

private function tableIsUnique(name:String):Boolean {
var r:Boolean = true;
for (var i:int = 0; i < dbData..tb.length(); i++) {
if (dbData..tb[i].@label == name) {
r = false;
break;
}
}
return r;
}

private function columnNames(exception:int = -1):String {
var r:String = "";
var array:Array = [];
for (var i:int = 0; i < columnData.length; i++) {
if (i != exception) array.push(columnData[i].name);
}
r = String(array);
return r;
}

private function getSchemaColumns():Array{
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var fullSQL:String = schema.tables[0].sql;
// extract the text inside the ( )
fullSQL = fullSQL.substring( fullSQL.indexOf("(") + 1 , fullSQL.lastIndexOf(")") );
// split all columns into an array
var columns:Array = fullSQL.split(",");
return columns;
}

private function renameTable():void {
PopUpManager.addPopUp(renameTableWindow, this);
PopUpManager.centerPopUp(renameTableWindow);
renameTableName.text = tableTree.selectedItem.@label;
}

private function closeRenameTableWindow():void {
PopUpManager.removePopUp(renameTableWindow);
}

private function doRenameTable():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "ALTER TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label + " RENAME TO " + renameTableName.text;
lastStatement(stat.text);
stat.execute( -1, new Responder(renameSuccess, renameError));
function renameSuccess(evt:SQLResult):void {
closeRenameTableWindow();
tableTree.selectedItem.@label = renameTableName.text;
fullSelectedTable = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
}
function renameError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function queryExecute():void {
if(queryText.text!="" && dbData..db.length()>0){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = queryText.text;
stat.execute( -1, new Responder(querySuccess, queryError));
function querySuccess(evt:SQLResult):void {
var resultColumns:Array = [];
resultData = new ArrayCollection([]);
if (evt.data != null) {
// get the columns
for (var col:Object in evt.data[0]) {
var advCol:AdvancedDataGridColumn = new AdvancedDataGridColumn(String(col));
resultColumns.push(advCol);
}
// get the data
for (var i:int = 0; i < evt.data.length; i++) {
resultData.addItem(evt.data[i]);
}
}
queryResultGrid.columns = resultColumns;
}
function queryError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}
}

private function copyTable():void {
PopUpManager.addPopUp(copyTableWindow, this);
PopUpManager.centerPopUp(copyTableWindow);
selectedCopyTable.text = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
copyTableName.text = tableTree.selectedItem.@label;
databaseData = new ArrayCollection([]);
var selectedDbIndex:int = 0;
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbid:String;
if (dbData..db[i].@numid == 1) dbid = "main";
if (dbData..db[i].@numid != 1) dbid = "db" + dbData..db[i].@numid;
databaseData.addItem({name:dbData..db[i].@name, did:dbid});
if (dbid == tableTree.selectedItem.@databaseName) {
selectedDbIndex = i;
}
}
copyDestinationDatabase.selectedIndex = selectedDbIndex;
}

private function closeCopyTableWindow():void {
PopUpManager.removePopUp(copyTableWindow);
}

private function doCopyTable():void {
// create table
var columns:Array = getSchemaColumns();
var destDatabase:String = copyDestinationDatabase.selectedItem.did;
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "CREATE TABLE " + destDatabase + "." + copyTableName.text + " (" + String(columns) + ")";
cstat.execute( -1, new Responder(copySuccess, copyError));
lastStatement(cstat.text);
function copySuccess(evt:SQLResult):void {
// copy all the data
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + destDatabase + "." + copyTableName.text + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
istat.execute( -1, new Responder(copyInsertSuccess, copyInsertError));
lastStatement(istat.text);
}
function copyError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
function copyInsertSuccess(evt:SQLResult):void {
// update xml
loadDataSchema(destDatabase);
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
// close window
closeCopyTableWindow();
}
function copyInsertError(evt:SQLError):void {
// delete new table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + destDatabase + "." + copyTableName.text;
dstat.execute();
Alert.show("ERROR: " + evt.details, "Error");
}
}
]]>
</fx:Script>

<s:HGroup gap="0" width="100%" height="100%">
<s:VGroup width="200" height="100%" gap="0">
<s:HGroup width="200">
<s:Button label="New" click="newTable();" toolTip="New table" width="40" enabled="{tableTree.selectedItems.length>0}"/>
<s:Button label="Drop" click="dropTable();" toolTip="Drop table" width="40" enabled="{isTableSelected}" />
<s:Button label="Rename" click="renameTable();" toolTip="Rename table" width="40" enabled="{isTableSelected}" />
<s:Button label="Copy" click="copyTable();" toolTip="Copy table" width="40" enabled="{isTableSelected}" />
</s:HGroup>
<mx:Tree id="tableTree" width="100%" height="100%" dataProvider="{dbData}" showRoot="false" labelField="@label" itemClick="tableSelect();"/>
</s:VGroup>
<s:VGroup width="100%" height="100%" gap="0">
<mx:Box height="80" width="100%">
<s:VGroup paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10" width="100%" height="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label width="100%">Latest SQL statement:</s:Label>
<s:Button width="100" label="View history" click="openHistory();" />
</s:HGroup>
<s:TextArea id="statementText" editable="false" width="100%" height="30"/>
</s:VGroup>
</mx:Box>
<mx:TabNavigator width="100%" height="100%" paddingTop="0">
<s:NavigatorContent label="Table contents">
<s:VGroup width="100%" height="100%" gap="0">
<mx:HBox width="100%" height="30" paddingLeft="8" paddingTop="6">
<mx:CheckBox label="Select all" change="selectAllChange(event);" />
<s:Button label="Delete selected" enabled="{isTableSelected}" click="deleteSelected();" />
<s:Button id="saveTableButton" label="Save changes" click="saveTable();" enabled="{isTableSelected}"/>
<s:Button id="newRecordButton" label="Add a record" click="newRecord();" enabled="{isTableSelected}"/>
</mx:HBox>
<mx:AdvancedDataGrid id="tableGrid" width="100%" height="100%" dataProvider="{tableData}" editable="true" itemEditBegin="saveTableButton.emphasized=true;">
<mx:columns>
<mx:AdvancedDataGridColumn dataField="" headerText="Data" editable="false" />
</mx:columns>
</mx:AdvancedDataGrid>
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="Edit columns">
<s:HGroup width="100%" height="100%" >
<mx:List id="columnList" width="200" height="100%" dataProvider="{columnData}" labelField="name" change="columnSelect();" />
<s:VGroup height="100%" paddingTop="10">
<s:HGroup>
<s:Button id="col_b_add" label="Add column" enabled="{isTableSelected}" click="addColumn();" />
<s:Button id="col_b_update" label="Update selected" enabled="{columnList.selectedItems.length > 0}" click="updateColumn();" />
<s:Button id="col_b_delete" label="Delete selected" enabled="{columnList.selectedItems.length > 0}" click="deleteColumn();" />
</s:HGroup>
<mx:Form enabled="{isTableSelected}">
<mx:FormItem label="Name" required="true">
<s:TextInput id="col_name" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="Data type" required="true">
<s:ComboBox id="col_data" dataProvider="{dataTypes}" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="Primary Key">
<s:CheckBox id="col_key" change="formChange();" />
</mx:FormItem>
<mx:FormItem label="AutoIncrement">
<s:CheckBox id="col_auto" change="formChange();" enabled="{col_key.selected}" />
</mx:FormItem>
<mx:FormItem label="Unique">
<s:CheckBox id="col_unique" change="formChange();" />
</mx:FormItem>
<mx:FormItem label="Allow Null">
<s:CheckBox id="col_null" change="formChange();" selected="true" />
</mx:FormItem>
<mx:FormItem label="Default Value" required="{!col_null.selected}">
<s:TextArea id="col_default" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="On Conflict">
<mx:ComboBox id="col_conflict" dataProvider="{conflictTypes}" editable="false" change="formChange();"/>
</mx:FormItem>
</mx:Form>
</s:VGroup>
</s:HGroup>
</s:NavigatorContent>
<s:NavigatorContent label="Query">
<s:VGroup width="100%" height="100%" paddingLeft="10" paddingTop="10" paddingRight="10">
<s:Label text="{fullSelectedTable}" />
<s:Label>SQL Query:</s:Label>
<s:TextArea id="queryText" width="100%" height="160"/>
<s:Button label="Execute" width="100%" click="queryExecute();" />
<s:Label>Results:</s:Label>
<mx:AdvancedDataGrid id="queryResultGrid" width="100%" height="100%" dataProvider="{resultData}" />
</s:VGroup>
</s:NavigatorContent>
</mx:TabNavigator>
</s:VGroup>
</s:HGroup>

</s:WindowedApplication>

Thanks for reading!

Read More