We work with SQLite in AIR applications

Hello connoisseurs (and not only) Flash, Flex and AIR.

Today I will tell you how to work with a local database (SQLite) in AIR applications.
')
To work, we need the Flex Builder, some, the SQLite editor (I use the application for the FireFox SQLite Manager) and a little patience.


We need the editor to view the changes in the database file, since AIR does not have its own.

To implement our plans, we will need the following libraries:

import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .

1. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
2. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
3. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
4. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
6. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
7. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .
9. import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .

import flash.data.SQLConnection; // () import flash.data.SQLStatement; // import flash.data.SQLResult; // import flash.data.SQLMode; // , , (, ) import flash.events.SQLErrorEvent; // import flash.events.SQLEvent; // (OPEN, UPDATE ..) import flash.filesystem. File ; // * This source code was highlighted with Source Code Highlighter .

Like other environments working with databases, SQLite AIR can create its own files, and it can work with ready-made database files.

Consider the option of creating a database file from scratch.

First we need to create the database file itself (testDB.sqlite) for this we use the following construction:

1. var dbFile: File = File .desktopDirectory.resolvePath ( "testDB.sqlite" ); // Specify the path to the database file (In our case, this is the desktop)
3. var DBConnection: SQLConnection = new SQLConnection (); // Connect to the base
4. DBConnection.addEventListener (SQLErrorEvent.ERROR, DBError); // Add an event handler that occurs during the connection
5. DBConnection.addEventListener (SQLEvent.OPEN, DBOpen); // Add an event handler for a successful connection
6. DBConnection.open (dbFile); // Actually initialize the opening of the database
8. / **
9. * This function handles connection errors.
10. * /
11. private function DBError (e: SQLErrorEvent): void
12. {
13. trace ( "Error message:" , e.error.message);
14. trace ( "Details:" , e.error.details);
15. }
17. / **
18. * This function handles a successful connection.
19. * /
20. private function DBOpen (e: SQLEvent): void
21. {
22. trace ( "The database was created successfully" );
23. }

* This source code was highlighted with Source Code Highlighter .

We already have the database file, it remains only to fill it with tables. Creating tables can be tied to a database creation success event (DBOpen). Next, we will do it, but for now let's figure out what our database will consist of (using the example of a database that will store birthdays). It will consist of two tables:

1. groups - groups which will include people, n. "Friends", "Work", "Family". The table will have two fields (id, name);
2. and actually peoples with fields (id, lname, fname, ffname, date, group_id)

Now we will create the tables described above, for this we need a variable that will contain a query to the database:

1. var GroupsTable: String = "CREATE TABLE IF NOT EXISTS groups (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)" ;

* This source code was highlighted with Source Code Highlighter .

And the object to manage the requests (this code can be inserted into the DBOpen function):

1. var statement: SQLStatement = new SQLStatement (); // Create an object
2. statement.sqlConnection = DBConnection; // Specify the base in relation to which we will execute the query
3. statement.text = GroupsTable; // Specify the text of the request
4. statement.addEventListener (SQLErrorEvent.ERROR, DBError); // Add an event handler that occurs during the connection
5. statement.addEventListener (SQLEvent.RESULT, TableResult); // Add an event handler that occurs when the table is created successfully
6. statement.execute (); // Initialize request processing
8. / **
9. * This function handles successful table creation.
10. * /
11. private function TableResult (e: SQLEvent): void
12. {
13. trace ( "Table created" );
14. }

* This source code was highlighted with Source Code Highlighter .

Now we create the second table, by analogy:

1. var PeoplesTable: String = "CREATE TABLE peoples (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, lname TEXT, fname TEXT, ffname TEXT, date TEXT, group_id INTEGER)" ;
3. var statement: SQLStatement = new SQLStatement (); // Create an object
4. statement.sqlConnection = DBConnection; // Specify the base in relation to which we will execute the query
5. statement.text = PeoplesTable; // Specify the text of the request
6. statement.addEventListener (SQLErrorEvent.ERROR, DBError); // Add an event handler that occurs during the connection
7. statement.addEventListener (SQLEvent.RESULT, TableResult); // Add an event handler that occurs when the table is created successfully
8. statement.execute (); // Initialize request processing

* This source code was highlighted with Source Code Highlighter .

Coped. The base with tables is created and ready to work. It remains only to fill it with data and learn how to extract them.
Consider an example of adding information to a table:

1. var InsertIntoPeoples: String = "INSERT INTO peoples (lname, fname, ffname, date, group_id) VALUES (@ column0, @ column1, @ column2, @ column3, @ column4)" ;
3. var LNameArray: Array = [ "Ivanov" , "Sidorov" , "Kozlov" , "Ignatenko" , "Borschev" , "Kolchenko" , "Buzyakin" ];
4. var FNameArray: Array = [ "Ivan" , "Nikolai" , "Artem" , "Igor" , "Sergey" , "Boris" , "Alexey" ];
5. var FFNameArray: Array = [ "Alekseevich" , "Borisovich" , "Igorevich" , "Artemovich" , "Nikolaevich" , "Ivanovich" , "Sergeevich" ];
7. var statement2: SQLStatement = new SQLStatement ();
8. statement2.sqlConnection = DBConnection;
9. statement2.text = InsertIntoPeoples;
10. statement2.addEventListener (SQLErrorEvent.ERROR, DBError);
11. statement2.addEventListener (SQLEvent.RESULT, InsertResult);
13. for ( var j: Number = 0; j <100; j ++)
14. {
15. statement2.parameters [ "@ column0" ] = LNameArray [ Math .round ( Math .random () * 6)];
16. statement2.parameters [ "@ column1" ] = FNameArray [ Math. around ( Math .random () * 6)];
17. statement2.parameters [ "@ column2" ] = FFNameArray [ Math. around ( Math .random () * 6)];
18. statement2.parameters [ "@ column3" ] = ( Math .round ( Math .random () * 30) + 1) + ':' + ( Math .round ( Math .random () * 11) + 1) + ': ' + ( Math. Around ( Math .random () * 2009) + 1);
19. statement2.parameters [ "@ column4" ] = Math .round ( Math .random () * 3);
20. statement2.execute ();
21. }
23. / **
24. * This function handles the successful addition of data to the table.
25. * /
26. private function InsertResult (e: SQLEvent): void
27. {
28. trace ( "Add to table successfully" );
29. }

* This source code was highlighted with Source Code Highlighter .

In this example, the INSERT construct uses the "@ column0" links, which are needed to simplify adding data using a loop. This is much more convenient than using a string with variables that you have to override before each addition.

Now let's try to extract the data from the table:

1. var q: String = "SELECT * FROM groups ORDER BY name" ;
3. var getGroupStat: SQLStatement = new SQLStatement ();
4. getGroupStat.sqlConnection = DBConnection;
5. getGroupStat.text = q;
6. getGroupStat.addEventListener (SQLErrorEvent.ERROR, DBError);
7. getGroupStat.addEventListener (SQLEvent.RESULT, GetGroupResult);
8. getGroupStat.execute ();
10. / **
11. * Handles the result of the GetGroup () function.
12. * /
13. private function GetGroupResult (e: SQLEvent): void
14. {
15. var result: SQLResult = e.target.getResult ();
17. var GroupArray: ArrayCollection = new ArrayCollection ();
19. if (result.data)
20. for ( var i: Number = 0; i <result.data.length; i ++)
21. GroupArray.addItem (result.data [i]);
22. }

* This source code was highlighted with Source Code Highlighter .

In the GetGroupResult () function, the result variable is assigned the result of a SELECT query; it is an array of variables of type Object:

1. [[ object Object], [ object Object], [ object Object], [ object Object]]

* This source code was highlighted with Source Code Highlighter .

It is best used by assigning a variable of type ArrayCollection, for convenient access to data by name (GroupArray [i] .name).
For deleting and updating data, constructions similar to those that we have reviewed differ only in string database queries.

At the end of the lesson, I would like to note that if your AIR application uses some kind of data storage, it is better not to find the SQLite file, since working with it is much faster than with a TXT file or even XML.

I attach the source to the lesson (project for Flex Builder 3.2).

PS Can someone tell me a good code highlighting for AS3?
Prompted quickhighlighter.com , thanks kutu