Chapter 8. Accessing Databases

Storing and accessing data.

Storing and accessing data in a database is an important part of programming. This is something which is often used in web and other developments.

When developing an application, whether it is a web application, a desktop application, or even a mobile application, we are dealing with a lot of data. Indeed, that's mostly why applications are around—dealing with data.

Using databases can help to make it easier to deal with data. They may help store and retrieve data, and moreover, they can help filter the data you are going to retrieve.

In this chapter, we will:

• Learn how to connect to MySQL databases

• Learn how to connect to SQLite databases

• See how to query databases

• See how to map haXe objects to databases

So now, if you're ready, let's go!

Connecting to databases

haXe offers you two connectors in its standard library. Those two connectors can be used when targeting the PHP or Neko platforms.

Unfortunately, at the time of writing, the standard library does not provide any way to connect to databases when targeting C++.

So, on PHP and Neko, you will be able to connect to MySQL and SQLite databases. Note that these two databases systems work by following two different schemes:

1. MySQL is based on a client/server model where your application is the client and connects to the server using a login and a password.

2. SQLite databases are stored in a file on your filesystem. Therefore, you will have to make sure that your user has the correct access rights on the file storing your database. SQLite databases are really good when you don't want to depend on installing and running a server. This is particularly interesting when you want to create some locally running applications.

php.db.Connection and neko.db.Connection

Whether we connect to a...

The SPOD

SPOD stands for Simple Persistence Objects Database. It is a functionality of the library to allow one to define objects in haXe and save and retrieve them easily from a database.

In fact, you will need to map haXe classes to tables in your database and you will be able to insert new records, delete them, update them, and search them.

Setting a SPOD object

To create objects that you will be able to use as SPOD objects, you have to create a class that extends neko.db.Object or php.db.Object.

Simply create the fields that exist in your table in the class as you would normally do:

class User extends neko.db.Object
{
   public var id : Int;
   public var userName : String;
   public var password : String;
}

We will also need to define a manager; it will be in charge of managing many operations for objects of this type. There is a standard one that you can use:

class User extends neko.db.Object
{
   public var id : Int;
   public var userName : String;
   public var password : String;
   
...

A concrete example of SPOD use

Let's see a concrete example of how to use that.

Set up a database in your MySQL server with a table named Users.

In this table, add the following three fields:

1. id: This should be the Primary Key and have auto_increment, so that we don't have to worry about generating the Primary Keys.

2. username: This should be a text; we will use it to store the user's username.

3. password: This should be a text too; we will use it to store the user's password hash.

Setting the object

Now, let's create a User class reflecting those fields and useable as SPOD:

class User extends neko.db.Object
{
   public var id : Int;
   public var username : String;
   public var password : String;

   public static var manager = new neko.db.Manager<User>(User);
}

Note

Note: We are going to use the standard Manager.

Remember how we named our table Users and see how our class is named User? This makes perfect sense and it respects/accounts for what people are used to seeing. Still, we will need...

The Manager

We have already seen how to initialize the manager, now, let's have a quick look at what it has to offer.

The all function

This function returns a list of all records present in the table. It takes an optional lock parameter that, if true, indicates that records that are going to be retrieved should have a lock put on them.

The count function

This function allows you to get the number of records that are present in the table. It can take an optional anonymous object as a parameter in order to filter records. By doing this, you can, for example, pass this object {sex : "M"} if you want to get only records corresponding to a male person.

The delete function

The delete function allows you to delete objects. Just like the count function, it can take an anonymous object to filter records that will be concerned.

The get function

The get function is certainly one of the most important functions; it allows you to retrieve an object from the database based on its Primary Key. It can also get...

Handling relations

Most of the time, you have modeled your database with relations inside it.

The haXe SPOD is able to handle one-to-one and many-to-one relations.

This is done by implementing the static function RELATIONS that takes no parameter. It has to return an array of anonymous objects made of the fields prop, key, and manager.

The prop field will be a string that is the name of a property by which you will get and set the object. This property has to be a dynamic property.

The key field is also a string that is the name of the field (in the database) that stores the foreign key.

So, let's take our User example and a sponsorship program to it. A user can sponsor another one.

class User extends neko.db.Object
{
   public var id : Int;
   public var username : String;
   public var password : String;
   public var sponsorId : Int; //Foreign Key
   //The property
   public var sponsor(dynamic, dynamic) : User;
   //Our function
   static function RELATIONS()
   {
      return [{prop: "sponsor...

Creating a blogging system

Now that we have seen how we can query databases and how we can use the SPOD system, we are going to try implementing something that looks like a very simple blogging system.

We will have the following four functionalities:

1. Adding a new post.

2. Listing posts.

3. Adding a user.

4. Listing users.

In order to choose what actions we want to execute, we will use the GET parameter "action". We will also pass other parameters needed by the action through GET parameters.

Time for action – Creating the Users table

Firstly, we are going to create a table named Users which will hold information about users. We will need the following three fields:

1. username: Will hold the user's login.

2. password: Will hold the user's password hashed.

3. id: The Primary Key.

The following is the SQL query you can use to create this table:

CREATE TABLE 'Users' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'username' varchar(255) NOT NULL,
  'password' varchar(255) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

What just happened?

This will create the table and the ID will also have auto_increment set on it.

Time for action – Creating the User class

Now, we will create the User class that maps to the Users table as follows:

package hxBlog;

#ifneko
import neko.db.Object;
import neko.db.Manager;
#end

#ifphp
import php.db.Object;
import php.db.Manager;
#end



class User extends Object
{
   public var id : Int;
   public var username : String;
   public var password : String;
   
   static var TABLE_NAME = "Users";
   public static var manager = new Manager<User>(User);
   
   public function setPassword(password : String)
   {
      this.password = haxe.Md5.encode(password);
   }
}

What just happened?

There are several things to note here:

• We are redefining the table to use by setting the TABLE_NAME static variable to Users

• We have created a simple function to set the password to the hash of the password

• We are performing some imports depending on the platform, so that our application can run in both Neko and PHP

Time for action – Creating the Posts table

We will now create a table named Posts which will hold all our blog posts.

This table will have the following five fields:

1. id: The ID of the post

2. title: The post's title

3. body: The post's text

4. fk_author: This will contain the ID of the author

5. postedOn: The date when the post was published

The following is the SQL query that you can use to create this table:

CREATE TABLE 'Posts' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'title' text NOT NULL,
  'body' longtext NOT NULL,
  'fk_author' int(11) NOT NULL,
  'postedOn' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Our table will be set correctly with this query.

Time for action – Creating the Post class

We will now create the Post class mapping to the Posts table.

We will need to redefine the table it is mapped to.

We will also need to define its relation to the User table.

package hxBlog;

#ifneko
import neko.db.Object;
import neko.db.Manager;
#end

#ifphp
import php.db.Object;
import php.db.Manager;
#end
class Post extends Object
{
   public var id : Int;
   public var title : String;
   public var body : String;
   public var postedOn : Date;
   public var fk_author : Int;
   
   public var author(dynamic, dynamic) : hxBlog.User;
   
   static function RELATIONS()
   {
      return [{prop: "author", key: "fk_author", manager: User.manager}];
   }
   
   static var TABLE_NAME = "Posts";
   public static var manager = new Manager<Post>(Post);
}

What just happened?

As you can see, we have our imports at the top of the class to ensure that this will work on Neko and PHP.

We also have the fk_author field definition and most importantly, we have...

Time for action – Creating the connection to the database

For our main function, we are going to do it systematically.

At first, let's create the connection to the database:

#ifneko
import neko.db.Connection;
import neko.db.Mysql;
import neko.db.Manager;
import neko.Web;
import neko.Lib;
#end

#ifphp
import php.db.Connection;
import php.db.Mysql;
import php.db.Manager;
import php.Web;
import php.Lib;
#end

class HxBlog
{
   public static function main(): Void
   {
      var connection : Connection;
      
      var options =   { 
                     user : "root",
                     pass : "root",
                     socket : null,
                     host : "127.0.0.1",
                     port : 8889,
                     database : "hxBlog"
            };
      connection = Mysql.connect(options);
      //Tell manager to use this connection.
      Manager.cnx = connection;
      Manager.initialize();

      //Do things
      
      connection.close();
   }
}

What just happened?

This...

Time for action – Listing posts

Now, let's create a function to list posts. This one is pretty easy:

   public static function listPosts()
   {
      Lib.println("Listing posts:<br/>");
      for(p in hxBlog.Post.manager.all(false))
      {
         Lib.print(p.title+"(posted on"+p.postedOn.toString() + "by"+p.author.username+")<br/>");
         Lib.print(p.body+"<br/>");
      }
   }

As you can see, it simply prints information from every post.

Also, note that you can use the User object corresponding to the author directly. This is one of the most interesting parts of the SPOD system.

Therefore, our main class now looks like this:

#ifneko
import neko.db.Connection;
import neko.db.Mysql;
import neko.db.Manager;
import neko.Web;
import neko.Lib;
#end

#ifphp
import php.db.Connection;
import php.db.Mysql;
import php.db.Manager;
import php.Web;
import php.Lib;
#end

class HxBlog
{
   public static function main(): Void
   {
      var connection : Connection;
      
 
     var...

Time for action – Listing users

Now, we are going to create a function to list all users.

This one is even simpler than listing posts because it has no relations.

   public static function listUsers()
   {
      Lib.print("Users:<br/>");
      for(u in hxBlog.User.manager.all(false))
      {
         Lib.print(u.username+"<br/>");
      }
   }

Our class now looks like the following:

#ifneko
import neko.db.Connection;
import neko.db.Mysql;
import neko.db.Manager;
import neko.Web;
import neko.Lib;
#end

#ifphp
import php.db.Connection;
import php.db.Mysql;
import php.db.Manager;
import php.Web;
import php.Lib;
#end

class HxBlog
{
   public static function main(): Void
   {
      var connection : Connection;
      
      var options =   { 
                     user : "root",
                     pass : "root",
                     socket : null,
                     host : "127.0.0.1",
                     port : 8889,
                     database : "hxBlog"
            };
      connection...

Time for action – Adding a user

Now, let's create the function to add a user. It will take the login and the password as parameters and will return the created object as follows:

   public static function createUser(login : String, password : String) : hxBlog.User
   {
      var u = new hxBlog.User();
      u.username = login;
      u.setPassword(password);
      u.insert();
      return u;
   }

Time for action – Adding a post

The function to create a post will have a little twist because it will have to set the author of the post. It will take, among other information, the name of the author. We will then retrieve the author's object by using the manager's search function.

   public static function createPost(authorLogin : String, title : String, body : String) : hxBlog.Post
   {
      var author = hxBlog.User.manager.search({username : authorLogin}, false).first();
      var p = new hxBlog.Post();
      p.author = author;
      p.title = title;
      p.body = body;
      p.postedOn = Date.now();
      p.insert();
      return p;
   }

Time for action – Branching it with the main function

Now, let's just add some code to act depending on the action GET parameters and get all parameters as needed by the action and pass them to our functions:

  //Depending on the action GET parameter to do things:
  switch(Web.getParams().get("action"))
  {
     case "listPosts":
        listPosts();
     case "addUser":
        createUser(Web.getParams().get("login"), Web.getParams().get("password"));
     case "listUsers":
        listUsers();
     case "addPost":
        createPost(Web.getParams().get("author"), Web.getParams().get("title"), Web.getParams().get("body"));
     default:
        listPosts();
  }

Therefore, our complete class is as follows:

#ifneko
import neko.db.Connection;
import neko.db.Mysql;
import neko.db.Manager;
import neko.Web;
import neko.Lib;
#end

#ifphp
import php.db.Connection;
import php.db.Mysql;
import php.db.Manager;
import php.Web;
import php.Lib;
#end

class HxBlog
{
  public static function main(): Void...

Summary

So, in this chapter, we have learned quite a lot of things about how to manipulate data from databases with haXe.

Specifically, we covered how to connect to MySQL and SQLite databases, how to query those databases with the low-level API, and how to use the SPOD library in order to store, retrieve, modify, and delete objects from the database.

Now that we know how to use databases, we will learn how we can do some templating. That may prove to be useful to display our objects in a more elegant way. So, are you ready? Let's go on!