Facebook Twitter Gplus LinkedIn RSS
Home Local Database Database Transactions

Database Transactions

Database transactions allow you to query the local database, letting you do a range of things such as creating tables and inserting data. Here I will teach you most of the transactions that I find useful and necessary to know.

First off, to open a transaction with a database, we must first type in:

db.transaction(function(tx){
//your code here
});

where the ‘db’ variable is the name of your database opening variable, and tx is changeable(it’s just a variable). This will initiate the local database, allowing us to place all sorts of queries into it. Once you open a transaction with the database, you can start to execute many Sqls. You can place them one after another, within other functions, or however else you see fit. To execute Sqls with the transaction, use the following syntax:

db.transaction(function(tx){
tx.executeSql(query, []);
});

From here on, the decision of what to do is yours. I like to set queries as a variable(usually called query for me), and modify the query based on my functions from there. For example, if I wanted to insert a row of data into my table, I would do something like this:

var query = 'INSERT INTO table(column_one, column_two) VALUES ('hi', 'bye');';
db.transaction(function(tx){
tx.executeSql(query, []);
});

This will insert hi into column_one of the table named table, and bye into column_two of table. Although it is possible to arbitrarily insert values like above, it is often better practice to use parametrized variables with the following syntax:

var query = 'INSERT INTO table(column_one, column_two) VALUES (?, ?);';
db.transaction(function(tx){
tx.executeSql(query, ['hi', 'bye']);
});

This code will perform the same function as the previously mentioned one, but instead takes in parametrized values. This can be useful, especially when downloading data and placing them into the local database. Think of what would happen if your downloaded data contained single or double quotes, you would get syntax errors on the attempt to insert a specific row into the database, thus losing that data.

Now this all sounds fun, but in order to store any data into the local database, we first have to create tables. To do this, set the variable of your query to either of these to perform table transactions:

//create a table
var query = 'CREATE TABLE IF NOT EXISTS cars(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, model TEXT, year INTEGER);';
//drop a table
var queryDrop = 'DROP TABLE IF EXISTS cars;';

Now we’ve introduced a lot here, but let’s go through it slowly. The first query will create the table cars, and assign it three columns, one for an id, one for the model, and one for the year. Now the additions to the id may seem weird, but they’re pretty helpful. The ‘NOT NULL’ tag lets the database know that the value in this column is never null, and the ‘PRIMARY KEY’ and ‘AUTOINCREMENT’ help it establish a sort of local id, where it starts at 1 for the first car, and then will increase by 1 each time another row is added. When you use this, you will see that the web Sql automatically creates a table called sqlite_sequence, where it stores the current count values for all local ids of all tables in a key value pair, with the key being the name of the table, and the value being the current count. There are many different data types that you can associate a column with, but you can experiment that on you’re own on the SQLite website.

Now that we have our table, let’s start adding, editing, deleting, and selecting data from it. First we’ll start with inserting.

var query = 'INSERT into cars(model, year) VALUES(?, ?);';
db.transaction(function(tx){
tx.executeSql(query, ['Honda Civic', '2011']);
});

This will, as expected, insert a row into our cars table with the data specified in the transaction execution. Notice, however, that we did not define an id during our transaction. This is because those special tags mentioned earlier will save us from this hassle, and create local ids that automatically increment for us. To no surprise, this Honda Civic should have an id of 1 in our table.

Next is editing. We can update a row in our table through the following syntax:

var query = 'UPDATE cars SET year=? WHERE model=?;';
db.transaction(function(tx){
tx.executeSql(query, ['2008', 'Honda Civic']);
});

We found out that our car is actually from the year 2008, and through this query we are able to update our table to reflect that. We are also able to delete entries in our table with the following syntax:

var query = 'DELETE FROM cars WHERE year=?;';
db.transaction(function(tx){
tx.executeSql(query, ['2008']);
});

With this transaction, we’re able to get rid of the car in our entry since we think it’s too old to be worth keeping. Last but not least, you can pass in a query to select certain rows in a table with the following:

var query = 'SELECT * FROM cars WHERE year=?;';
db.transaction(function(tx){
tx.executeSql(query, ['2011']);
});

This will return all the lines in the table (hence the * symbol) where the year column has a value of 2011, which for us means all the cars from the year 2011.

All of the query examples listed above are only syntax that you need to follow regarding placement of tables, columns, and data. You are at any time free to construct your query variable however you see necessary. For example, if I wish to update a line in a table based on inputted parameters, I could write a more general function like this:

function addCar(table, column, value){
var query = 'INSERT into ' + table + '(' + column + ') VALUES(?);';
db.transaction(function(tx){
tx.executeSql(query, [value]);
});
//execute the function somewhere
addCar('cars', 'model', 'Honda Civic');

You can even get creative and allow your function to take in an object to manipulate and place into your database. There are actually even more ways to query your local database, such as renaming(or in the case of Sqlite syntax, ALTERING) tables, or even using additional options in your queries (e.g. ‘INSERT OR IGNORE’ will insert a line only if there is not another row that has the value you are trying to insert in its own unique column field. If you didn’t know about the UNIQUE option, it’s a way to make a specific column when creating a table never have the same data in any row! This is handy when you know you will always have unique IDs for something, and don’t want to accidentally reinsert an entry and have double the data). The possibilities are endless! Remember, it’s up to you as the developer to determine how your coding will work. You can read up on more of these extra cases on the SQLite website.

For now, this is enough to get a basic app rolling. Let’s move on to database versioning, something that could be extremely useful whenever you feel that you need to add a new column to a table, but need a way for the user to delete and update his local database. Next ->

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>