Win an Ipad
  Blog   Database

14.7.2012

Database

This blog was written on request for more information about the database on our forum. If you would like to see other topics or if there is anything else that could help you to make an awesome app do not worry and write us on our forum.

Moscrif uses SQLite as its database system. The main advantage of the SQLite is that it is not a separate process that is accessed from the client application, but an integral part of the application. It does not require any servers or other additional services.

In Moscrif, the databases are managed by Database class. To enable using the Database class in the project, one needs to add sqlite library in dialog in project preferences->application window (there is an error in current version of Moscrif; and it is necessary to manually rewrite sql to sqlite. We are working to fix it).

You can create a database by right clicking on the project's name and then selecting New file->SQLite database file. The database files are automatically open using Moscrif’s database editor, which allows to edit the database.

The database can be opened by open function. The sql comands are executed by exec function and the database is closed by close function. The next code shows basic operations with database. At first, it is needed to create one table called "names" with four columns (id, name, surname and age) and a few lines. This table was made by Moscrif’s SQLite editor by command:

CREATE TABLE names( id NUMERIC AUTO_INCREMENT PRIMARY_KEY,name TEXT,surname TEXT,age NUMERIC)

And then lines were ad by:

INSERT INTO names VALUES (null, 'John', 'Smith', 25);
INSERT INTO names VALUES (null, 'George', 'Washington', 23);
INSERT INTO names VALUES (null, 'Bill', 'McKlinton', 32);

Database looks like in the next Picture:

The return value of exec function is Dataset object which contains all information about the result (all lines, columns and values). First, we add function rowAsObject to Dataset object. This function splits the one row into object which contains members with the same names as the table columns with corresponding values. It is not usually important to know how the rowAsObject function works inside. It is enough to know what it does and how to use it. You can copy it from this blog.

Example: the object made from the first line has members: id : 1; name : John; surname : Smith and age : 25.

// Add new function to Dataset class. It returns object constructed from current row as { field1: value1, field2: value2, etc. }
function Dataset.rowAsObject()
{
    var names = this.names;
    // make names array
    if (!names) {
        names = [];
        for (var n = 0; n < this.length; ++n)
            names.push(symbol(this.name(n)));
        this.names = names;
    }
    var obj = {}; var n = 0;
    // fill the ibject with cresponding values
    for (var v in this)
        obj[names[n++]] = v;
    // return object
    return obj;
}

When the Dataset has function rowAsObject included the values from database are selected.


// load database file
var database = Database.open("app://database.db"); // select all lines
var result = database.exec("SELECT * FROM names"); // release sources reserved for database
database.close();

The result oh having these three lines of code is that the variable contains the whole table. The next function of Dataset object allows moving to the next row in the table. This feature is used to show the whole table to the list element.

var row;
// if result exists
while (result) {
    // create row object
    row = result.rowAsObject();
    // show values from the line in a list
    app.list.add(new ListItem({text : "name: " + row.name + " surname: " + row.surname + "; age: " + row.age}));
    // move to next line. If it does not exists break thy cycle
    if (!result.next())
        break;
}

You can find all source codes and fully working project in attached zip archive.

Write a Comment (0)
Subject
Please complete this mandatory field.
HTML Tags Not Allowed!
Comment
Please complete this mandatory field.
Author

Author latest blogs