Monday, April 25, 2011

Working with SQLite in AIR: Part 2

Today we will start learning the SQL language.

As I said in the previous tutorial, to write, read, update, rewrite, etc. SQLite database tables, we need to use the SQL language. Right now our goal is to get familiar with the basic SQL commands to later be able to implement them in our AS3 code and work with SQLite databases.

Let's get started. You can't store any data if you don't have a table in your database, so the first command we're going to learn is called "CREATE TABLE", followed by the name of your table and then the columns, separated by commas.

For example, to create a table with 4 columns - ID, First name, Last name and Age, we can use this command:

CREATE TABLE contacts (id, fname, lname, age)

SQLite uses storage classes. This is similar to the typed variables in AS3 - we can make our variable an integer, a string, an array, and so on. This is useful in SQLite because then it will only use the needed resources for the said class (for example, it will know that the age column is always an integer) and will be more friendly with memory this way. We are working with databases, so we always want to keep our used resources to minimum.

Anyway, here are the storage classes and their purposes:

NULL - only for null values
INTEGER - round numbers, positive or negative
REAL - a floating point value
TEXT - a string value
BLOB - a piece of data that is stored exactly as it was input

To maximise compatibility between SQLite and other database engines, SQLite supports "type affinity" on columns. A type affinity is a prefered type of the values in a column. Any columns can store any type of data, but if we specify a prefered one - it will strive to use that (if there is a possibility). For example, if we have a numeric value "123" and put it in a column with type affinity "TEXT", it will turn the number 123 to string value.

Here are the possible affinity types:

TEXT - the engine tries to turn the data into string
NUMERIC - the engine tries to turn the data into numbers
INTEGER - the engine tries to turn the data into integers, the difference between INTEGER and NUMERIC types is that the engine will try to turn any floating point values we might have into round numbers
REAL - all numeric values are turned into floating point values
NONE - the engine doesn't try to convert the values, this is the default type affinity.

To apply affinity types to columns, you can do that during the creation of the table:

CREATE TABLE contacts (id INTEGER, fname TEXT, lname TEXT, age INTEGER)

Its obvious that we can only create a table if it doesn't exist yet. We can check if the table exists by using "IF NOT EXISTS" expression in the "CREATE" operator:

CREATE TABLE IF NOT EXISTS contacts (id INTEGER, fname TEXT, lname TEXT, age INTEGER)

SQLite supports many types of possible limitations/requirements we can apply to columns. One of them is "PRIMARY KEY", which requires each value in the column to be unique. If the type of the column is an integer, we can also add the keyword "AUTOINCREMENT" to the operator. This way, each value in the column will be an unique number and when we add a new line to the table, the value is automatically updated by setting its value to the previous value + 1.

CREATE TABLE IF NOT EXISTS contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT, age INTEGER)

Creating tables is more complicated than deleting them. To delete a table, all you have to do is use the "DROP TABLE" operator and specify the name of your table:

DROP TABLE contacts

We can check if it exists before deleting it:

DROP TABLE IF EXISTS contacts

And that's all for now.

Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 7
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11
Working with SQLite in AIR: Part 12

3 comments:

Anonymous said...

Thank you so much for these!

sean steezy said...

Hey, your tutorials on this subject have been absolutely crucial for me. I have one silly question though, and since I'm not smart in database stuff, maybe you can help? In your contact list examples, how would I get the database to understand if someone's name already exists and then instead of creating a new entry, it would update the persons details where their name = the name they entered? It looks like setting fName to a primary key maybe... I'm lost... thanks for taking the time to publish all this great info tho!

Kirill Poletaev said...

The REPLACE command is what you are looking for!

http://www.sqlite.org/lang_replace.html

Use the INSERT OR RELACE query - it basically tries to insert a new row, but if the row already exists (an item with such ID already exists, or another value in the row is not unique when it should be) it just overwrites the existing row with new values.

So, first make sure you set your name column UNIQUE, then you can use the INSERT OR REPLACE command.

Hope this helps.

Post a Comment