<page xmlns="http://projectmallard.org/1.0/" xmlns:its="http://www.w3.org/2005/11/its" type="topic" id="record-collection.js" xml:lang="pt-BR">

  <info>

  <title type="text">Record collection (JavaScript)</title>

    <link type="guide" xref="js#examples"/>

    <desc>Create a small database application for ordering your music collection</desc>

    <revision pkgversion="0.1" version="0.1" date="2011-02-22" status="review"/>

    <credit type="author">

      <name>GNOME Documentation Project</name>

      <email its:translate="no">gnome-doc-list@gnome.org</email>

    </credit>

    <credit type="author">

      <name>Johannes Schmid</name>

      <email its:translate="no">jhs@gnome.org</email>

    </credit>

    <credit type="editor">

      <name>Marta Maria Casetti</name>

      <email its:translate="no">mmcasettii@gmail.com</email>

      <years>2013</years>

    </credit>

    <mal:credit xmlns:mal="http://projectmallard.org/1.0/" type="translator copyright">

      <mal:name>Rafael Ferreira</mal:name>

      <mal:email>rafael.f.f1@gmail.com</mal:email>

      <mal:years>2013</mal:years>

    </mal:credit>

  </info>

<title>Record collection</title>

<synopsis>

  
In this tutorial, you will learn:

  <list>

    <item>
How to connect to a database using libgda
</item>

    <item>
How to insert and browse records in a database table
</item>

  </list>

</synopsis>

<section id="intro">

  <title>Introduction</title>

    This demo uses the Javascript language. We are going to demonstrate how to connect and use a database from a GTK program, by using the GDA (GNOME Data Access) library. Thus you also need this library installed.

    GNOME Data Access (GDA) is library whose purpose is to provide universal access to different kinds and types of data sources. This goes from traditional relational database systems, to any imaginable kind of data source such as a mail server, a LDAP directory, etc. For more information, and for a full API and documentation, visit the <link href="http://library.gnome.org/devel/libgda/stable/">GDA website</link>.

    Although a big part of the code is related to user interface (GUI), we are going to focus our tutorial on the database parts (we might mention other parts we think are relevant though). To know more about Javascript programs in GNOME, see the <link xref="image-viewer.js">Image Viewer program</link> tutorial.

</section>

<section id="anjuta">

  <title>Create a project in Anjuta</title>

  
Before you start coding, you'll need to set up a new project in Anjuta. This will create all of the files you need to build and run the code later on. It's also useful for keeping everything together.

  <steps>

    <item>

    
Start Anjuta and click <guiseq><gui>File</gui><gui>New</gui><gui>Project</gui></guiseq> to open the project wizard.

    </item>

    <item>

    
Choose <gui>Generic Javascript</gui> from the <gui>JS</gui> tab, click <gui>Forward</gui>, and fill-out your details on the next few pages. Use <file>record-collection</file> as project name and directory.

   	</item>

    <item>

    
Click <gui>Finished</gui> and the project will be created for you. Open <file>src/main.js</file> from the <gui>Project</gui> or <gui>File</gui> tabs. It contains very basic example code.

    </item>

  </steps>

</section>

<section id="structure">

  <title>Program Structure</title>

  <media type="image" mime="image/png" src="media/record-collection.png"/>

  
This demo is a simple GTK application (with a single window) capable of inserting records into a database table as well as browsing all records of the table. The table has two fields: id, an integer, and name, a varchar. The first section (on the top) of the application allows you to insert a record into the table. The last section (bottom) allows you to see all the records of that table. Its content is refreshed every time a new record is inserted and on the application startup.

</section>

<section id="start">

  <title>Starting the fun</title>

  
Let's start by examining the skeleton of the program:

const GLib = imports.gi.GLib;

const Gtk = imports.gi.Gtk;

const Gda = imports.gi.Gda;

const Lang = imports.lang;

function Demo () {

  this._init ();

}

Demo.prototype = {

  _init: function () {

    this.setupWindow ();

    this.setupDatabase ();

    this.selectData ();

  }

}

Gtk.init (null, null);

var demo = new Demo ();

Gtk.main ();]]>

  <list>

    <item>
Lines 1‒4: Initial imports. Pay special attention to line 3, which tells Javascript to import the GDA library, our focus in this tutorial.
</item>

    <item>
Lines 6‒17: Define our Demo class. Pay special attention to lines 13‒15, where we call 3 methods which will do the whole job. They will be detailed below.
</item>

    <item>
Lines 19‒23: Start the application.
</item>

  </list>

</section>

<section id="design">

  <title>Designing the application</title>

  
Let's take a look at the setupWindow method. It is responsible for creating the User Interface (UI). As UI is not our focus, we will explain only the relevant parts.

  setupWindow: function () {

    this.window = new Gtk.Window ({title: "Data Access Demo", height_request: 350});

    this.window.connect ("delete-event", function () {

      Gtk.main_quit();

      return true;

      });

    // main box

    var main_box = new Gtk.Box ({orientation: Gtk.Orientation.VERTICAL, spacing: 5});

    this.window.add (main_box);

    // first label

    var info1 = new Gtk.Label ({label: "Insert a record", xalign: 0, use_markup: true});

    main_box.pack_start (info1, false, false, 5);

    // "insert a record" horizontal box

    var insert_box = new Gtk.Box ({orientation: Gtk.Orientation.HORIZONTAL, spacing: 5});

    main_box.pack_start (insert_box, false, false, 5);

    // ID field

    insert_box.pack_start (new Gtk.Label ({label: "ID:"}), false, false, 5);

    this.id_entry = new Gtk.Entry ();

    insert_box.pack_start (this.id_entry, false, false, 5);

    // Name field

    insert_box.pack_start (new Gtk.Label ({label: "Name:"}), false, false, 5);

    this.name_entry = new Gtk.Entry ({activates_default: true});

    insert_box.pack_start (this.name_entry, true, true, 5);

    // Insert button

    var insert_button = new Gtk.Button ({label: "Insert", can_default: true});

    insert_button.connect ("clicked", Lang.bind (this, this._insertClicked));

    insert_box.pack_start (insert_button, false, false, 5);

    insert_button.grab_default ();

    // Browse textview

    var info2 = new Gtk.Label ({label: "Browse the table", xalign: 0, use_markup: true});

    main_box.pack_start (info2, false, false, 5);

    this.text = new Gtk.TextView ({editable: false});

    var sw = new Gtk.ScrolledWindow ({shadow_type:Gtk.ShadowType.IN});

    sw.add (this.text);

    main_box.pack_start (sw, true, true, 5);

    this.count_label = new Gtk.Label ({label: "", xalign: 0, use_markup: true});

    main_box.pack_start (this.count_label, false, false, 0);

    this.window.show_all ();

  },]]>

  <list>

    <item>
Lines 22 and 27: Create the 2 entries (for the two fields) in which users will type something to get inserted in the database.
</item>

    <item>
Lines 31‒34: Create the Insert button. We connect its clicked signal to the _insertClicked private method of the class. This method is detailed below.
</item>

    <item>
Line 39: Create the widget (TextView) where we will show the contents of the table.
</item>

    <item>
Line 44: Create the label where we will show the number of records in the table. Initially it's empty, it will be updated later.
</item>

  </list>

</section>

<section id="connect">

  <title>Connecting to and initializing the database</title>

     The code which makes the connection to the database is in the setupDatabase method below:

  setupDatabase: function () {

    this.connection = new Gda.Connection ({provider: Gda.Config.get_provider("SQLite"),

                                          cnc_string:"DB_DIR=" + GLib.get_home_dir () + ";DB_NAME=gnome_demo"});

    this.connection.open ();

    try {

      var dm = this.connection.execute_select_command ("select * from demo");

    } catch (e) {

      this.connection.execute_non_select_command ("create table demo (id integer, name varchar(100))");

    }

  },]]>

  <list>

    <item>

      
Lines 2‒3: Create the GDA's Connection object. We must supply to its constructor some properties:

      <list>

        <item>

          
provider: One of GDA's supported providers. GDA supports SQLite, MySQL, PostgreSQL, Oracle and many others. For demo purposes we will use a SQLite database, as it comes installed by default in most distributions and it is simple to use (it just uses a file as a database).

        </item>

        <item>

          
cnc_string: The connection string. It may change from provider to provider. The syntax for SQLite is: DB_DIR=PATH;DB_NAME=FILENAME. In this demo we are accessing a database called gnome_demo in the user home dir (note the call to GLib's get_home_dir function).

        </item>

      </list>

      <note>

        
If the provider is not supported by GDA, or if the connection string is missing some element, line 2 will raise an exception. So, in real life we should handle it with JavaScript's statement try...catch.

      </note>

    </item>

    <item>
Line 4: Open the connection. In the SQLite provider, if the database does not exist, it will be created in this step.
</item>

    <item>

      
Lines 6‒10: Try to do a simple select to check if the table exists (line 7). If it does not exist (because the database was just created), this command will raise an exception, which is handled by the try...catch block. If it is the case, we run the create table statement (line 9).

      
In order to run the SQL commands above we are using the GDA connection methods execute_select_command and execute_non_select_command. They are simple to use, and just require two arguments: The Connection object and the SQL command to be parsed.

    </item>

  </list>

  
At this point we have the database set up, and are ready to use it.

</section>

<section id="select">

  <title>Selecting</title>

     After connecting to the database, our demo's constructor calls the selectData method. It is responsible for getting all the records in the table and showing them on the TextView widget. Let's take a look at it:

  selectData: function () {

    var dm = this.connection.execute_select_command  ("select * from demo order by 1, 2");

    var iter = dm.create_iter ();

    var text = "";

    while (iter.move_next ()) {

      var id_field = Gda.value_stringify (iter.get_value_at (0));

      var name_field = Gda.value_stringify (iter.get_value_at (1));

      text += id_field + "\t=>\t" + name_field + '\n';

    }

    this.text.buffer.text = text;

    this.count_label.label = "" + dm.get_n_rows () + " record(s)";

  },]]>

  <list>

    <item>
Line 2: The SELECT command. We are using the GDA connection's execute_select_command method for that. It returns a DataModel object, which is later used to retrieve the rows.
</item>

    <item>
Line 3: Create an Iter object, which is used to iterate over the DataModel's records.
</item>

    <item>
Line 7: Loop through all the records, fetching them with the help of the Iter object. At this point, the iter variable contains the actual, retrieved data. Its move_next method returns false when it reaches the last record.
</item>

    <item>

      
Lines 8‒9: We do two things in each line:

      <list>

        <item>
Use Iter's method get_value_at, which requires only one argument: the column number to retrieve, starting at 0. As our SELECT command returns only two columns, we are retrieving columns 0 and 1.
</item>

        <item>
The method get_value_at returns the field in GLib's GValue format. A simple way to convert this format to a string is by using GDA's global function value_stringify. That's what we are doing here, and we store the results in the variables id_field and name_field.
</item>

      </list>

    </item>

    <item>
Line 11: Concatenate the two fields to make one text line, separated by "=>", and store it in the text variable.
</item>

    <item>
Line 14: After the loop is finished, we have all the records formatted in the text variable. In this line we just set the contents of the TextView with that variable.
</item>

    <item>
Line 15: Display the number of records in the table, making use of the DataModel's get_n_rows method.
</item>

  </list>

</section>

<section id="insert">

  <title>Inserting</title>

     OK, we know how to connect to a database and how to select rows from a table. Now it's time to do an INSERT on the table. Do you remember above, in the method setupWindow we connected the <gui>Insert</gui> button's clicked signal to the method _insertClicked? Let's see the implementation of this method.

  _insertClicked: function () {

    if (!this._validateFields ())

      return;

    // Gda.execute_non_select_command (this.connection,

    //   "insert into demo values ('" + this.id_entry.text + "', '" + this.name_entry.text + "')");

    var b = new Gda.SqlBuilder ({stmt_type:Gda.SqlStatementType.INSERT});

    b.set_table ("demo");

    b.add_field_value_as_gvalue ("id", this.id_entry.text);

    b.add_field_value_as_gvalue ("name", this.name_entry.text);

    var stmt = b.get_statement ();

    this.connection.statement_execute_non_select (stmt, null);

    this._clearFields ();

    this.selectData ();

  },]]>

    We have learned how to use the GDA connection's methods execute_select_command and execute_non_select_command to quickly execute SQL commands on the database. GDA allows one to build a SQL statement indirectly, by using its SqlBuilder object. What are the benefits of this? GDA will generate the SQL statement dynamically, and it will be valid for the connection provider used (it will use the same SQL dialect the provider uses). Let's study the code:

  <list>

    <item>
Lines 2‒3: Check if the user filled all the fields. The code for the private method _validateFields is really simple and you can read it in the full demo source code.
</item>

    <item>
Line 5: The faster way of doing the INSERT. It's commented out as we want to show how to use the SqlBuilder object to build a SQL statement portable across databases.
</item>

    <item>
Line 7: Create the SqlBuilder object. We must pass the type of statement we are going to build. It can be SELECT, UPDATE, INSERT or DELETE.
</item>

    <item>
Line 8: Set the name of the table on which the built statement will operate (it will generate INSERT INTO demo)
</item>

    <item>
Lines 9‒10: Set the fields and its values that will be part of the statement. The first argument is the field name (as in the table). The second one is the value for that field.
</item>

    <item>
Line 11: Get the dynamically generated Statement object, which represents a SQL statement.
</item>

    <item>
Line 12: Finally, execute the SQL statement (INSERT).
</item>

    <item>
Line 14: Clear the id and name fields on the screen.  The code for the private method _clearFields is really simple and you can read it in the full demo source code.
</item>

    <item>
Line 15: Refresh the view on the screen by doing another SELECT.
</item>

  </list>

  <note>
You can also make use of parameters while building the statement. By using the SqlBuilder objects and parameters you are less subject to attacks like SQL injection. Check the <link href="http://library.gnome.org/devel/libgda/stable/">GDA documentation</link> for more information about parameters.
</note>

</section>

<section id="run">

  <title>Run the application</title>

  
All of the code you need should now be in place, so try running the code. You now have a database for your record collection!

</section>

<section id="impl">

 <title>Reference Implementation</title>

 
If you run into problems with the tutorial, compare your code with this <link href="record-collection/record-collection.js">reference code</link>.

</section>

</page>