The Simple Persistent Objects Database Library (SPOD) is part of the standard Haxe distribution. It needs only two classes neko.db.Object and neko.db.Manager. This Tutorial will explain how to use this library.

But first, let's explain what SPOD is doing. With SPOD, you can define some Classes that will map to your database tables. You can then manipulate tables like objects, by simply modifying the table fields and calling a method to update the datas or delete the entry. For most of the standard stuff, you only need to provide some basic declarations and you don't have to write one single SQL statement. You can later extend SPOD by adding your own SQL requests for some application-specific stuff.

Creating a SPOD

First, let's create a simple table User using the following MySQL statement :

CREATE TABLE User (
    id INT NOT NULL auto_increment,
    name VARCHAR(32) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY  (id)
) ENGINE=InnoDB;

Or the equivalent for SQLite :

CREATE TABLE User (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INT NOT NULL
)

This is a very simple table with 3 fields : id for the user identifier, name for his name, and age for his age. We choose the MySQL InnoDB engine for Transactional support but this is optional.

We can write the corresponding SPOD Object in User.hx :

class User extends neko.db.Object {
    public var id : Int;
    public var name : String;
    public var age : Int;
    
    public static var manager = new neko.db.Manager<User>(User);
}

First we declare all the fields of the table, and we add the corresponding Haxe types. That can be Int, Float, String, Bool, Date as specified in the Accessing to MySQL Database Tutorial. Please note that there is currently no automatic check that the fields declared this way exists in the database and have the correct type. It's up to the programmer to keep its tables and classes up-to-date.

By default, the table linked to the class is the class name. This behavior can be changed by redefining the TABLE_NAME static variable. Be careful to *always* declare all static variables before the manager static is initialized :

class User extends neko.db.Object {
    ...
    static var TABLE_NAME = "User";
    static var manager = ...
}

The table primary key is also considered to be by default an integer named id. You can change this default behavior also as we will see later.

Using the SPOD

Now that the class is created, we can write a simple application/website, that connects to a database, query a given user or create it if it doesn't exists, then increment the user age for every request :

class App {

    static function main() {        
        // initialize the connection
        var useMysql = false;
        var cnx : neko.db.Connection;
        if( !useMysql )
            cnx = neko.db.Sqlite.open("mydatabase_file.db");
        else {
            cnx = neko.db.Mysql.connect({ 
                host : "localhost",
                port : 3306,
                database : "MyDatabase",
                user : "root",
                pass : "",
                socket : null
            });
        }

        neko.db.Manager.cnx = cnx;
        neko.db.Manager.initialize();
        // run the application
        execute();
        // close the connection and do some cleanup
        neko.db.Manager.cleanup();
        cnx.close();
    }

    static function execute() {
        var u = User.manager.get(5); // get user #5
        if( u == null ) {
            u = new User();
            u.id = 5; // force the id to 5
            u.name = "John";
            u.age = 1;
            u.insert();
        } else {
            u.age++;
            u.update();
        }
        trace(u.name+" is "+u.age+" years old");
    }
}

Compile the following example and execute it. It will display the following trace for every time you execute it :

John is 1 years old
John is 2 years old
John is 3 years old
...

We can slightly modify the sample to delete the user if he reaches some given age :

    static function execute() {
        ....
        if( u.age > 10 ) {
            trace("deleting "+u.name);
            u.delete();
        }
    }

Adding object Methods

You can easily add your own methods to the class User. They will be available on all instances you retrieve. Here's an example :

class User extends neko.db.Object {
    ...
    public function getDescription() {
        return name+" has id "+id+" and is "+age+" years old";
    }
}

You can use theses methods from your application as soon as they are declared public :

class App {
    ...
    static function execute() {
        var u = User.manager.get(5);
        if( u == null )
            trace("No User#5");
        else
            trace(u.getDescription());
    }
}

The SPOD Object

A SPOD Object is an instance of User or another class extending the neko.db.Object class. All objects are retrieved by their identifier(s). When the identifier is a single integer, you can use the neko.db.Manager.get method. See the SPOD Manager documentation below if you want to use multiple-identifiers tables.

The following methods are available from any SPOD Object :

• insert() : insert a newly created object into the database. All fields that have been defined in the object will be used to create the INSERT statement. If some table fields have not been defined in the object, they will still be null after insertion, so you might need to synchronize the object before using them. If the table identifier has not been defined, it is retrieved using the LAST_INSERT_ID() command.
• update() : update in the database the object fields that have been modified
• delete() : remove the object from the database
• sync() : synchronize the object fields by retrieving the current database datas
• toString() : returns the name of the table with the object identifier(s)

The SPOD Manager

The SPOD Manager is the place where all the SQL logic is performed. It provides a lot of API methods, some used when accessing the Manager from the application, some used in order to extend the Manager when defined your own manager.

A Manager is created by using the *type* of the SPOD Object as type parameter and the *class* as constructor parameter (that will be used to retrieve some metadatas) :

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

By convention, the manager is always defined as a static field of the SPOD Object class.

The following methods are dedicated to retrieving SPOD Objects :

• get(id : Int) : retrieve a SPOD Object based on a single integer identifier
• getWithKeys(keys : {}) : retrieve a SPOD Object based on its multiple keys (see below)
• count() : Int : the number of rows for this table
• search(fields : {}, lock : Bool) : return a list of SPOD Objects matching the search patterns :

var ul : List<User> = User.manager.search({ age : 10, name : "John" },true);
trace(ul.length+" users found");

Other methods are available in order to extend the Manager by defining your own.

Extending the SPOD Manager

Let's say you want to select all the users which are older than 20. For this specific request, you will have to write a bit of SQL, and at this time you need to extend the SPOD Manager :

class UserManager extends neko.db.Manager<User> {
    public function new() {
        super(User);
    }
    public function ageAbove( age : Int ) {
        return objects(select("age > "+age),true);
    }
}

And update the User class accordingly :

class User extends neko.db.Object {
    ....
    public static var manager = new UserManager();
}

Then from your application you can use the ageAbove manager method :

class App {
   ...
   static function execute() {
       var ul : List<User> = User.manager.ageAbove(20);
       trace(ul.length+" users found");
   }
}

• the select(condition) method will build a SELECT SQL request with the given condition
• the objects method will execute the SQL and returns a list of objects. The boolean flag is for objects locking and is set to true when used with select (it's for Transactions, as explained later).
• the object method can be used instead of objects to return a single result, or null if the request didn't return any result.

When using strings in SQL request, *don't forget* to use the quote method, if you don't want any SQL-injection security hole in your website :

class UserManager extends neko.db.Manager<User> {
    ...
    public function nameLike( name : String ) {
        return objects(select(" name LIKE "+quote(name)));
    }
}

Adding relations

In SQL, you can define relations between your tables. Such relations can also be defined in SPOD. First, let's add a field parentId to our Table User by executing the following SQL statement :

ALTER TABLE User ADD parentId INT;

By default, the parentId field is NULL. When defined, it will point to another User. Let's add a constraint on this field that will ensure the table consistency :

ALTER TABLE User ADD CONSTRAINT FOREIGN KEY (parentId) 
    REFERENCES User(id) ON DELETE SET NULL;

Now that the relation is defined on the Database side, you can define it in the SPOD Object :

class User extends neko.db.Object {
     static function RELATIONS() {
        return [{ prop : "parent", key : "parentId", manager : User.manager }];
     }
     public var id : Int;
     public var name : String;
     public var age : Int;
     private var parentId : Int;
     public var parent(dynamic,dynamic) : User;

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

When we compare to the previous declaration of User, we can see some changes :

• the parentId field has been added as private (that's a personal choice, public works also great)
• a dynamic property parent has been added, that will be used to access the parent User
• a RELATIONS static method has been added, which returns an array of relations for this table

A relation is defined by the following fields :

• prop : the dynamic property that is defined in the class
• key : the field/key used to reference the foreign table
• manager : the manager used to retrieve the corresponding SPOD Object

Using relations

Now that the relation is declared, we can use it transparently. In that example, we create a new parent user for the user 5 if it doesn't have one yet :

class App {
    ....
    static function execute() {
        var u = User.manager.get(5);
        if( u == null ) throw "User 5 not found";
        if( u.parent == null ) {
            trace("Adding parent");
            var p = new User();
            p.name = "Old Tom";
            p.age = 88;
            p.insert();
            u.parent = p;
            u.update();
        }
        trace(u.name+" has parent "+u.parent.name);
    }
}

The relation behavior is the following :

• when accessed for reading, the object is retrieved by the corresponding Manager and stored into the cache, so further accesses will not require any request
• when accessed for writing, both the cached object and the parentId are updated (that means that we can't directly write u.parent = new User(); since at this time the newly created user doesn't have an identifier yet.
• relations can only work for objects referenced by a single key

Object cache

Every object retrieved from the database is stored into a global object cache. This way, you can always ensure that if two SPOD objects of the same table have the same id, then the objects are the same and modifying one object will also modify the other one :

class App {
    ...
    static function execute() {
        var u1 = User.manager.get(5);
        var u2 = User.manager.get(5);
        u1.age = 0;
        u2.age = 33;
        trace(u1.age); // 33
    }
}

The object cache is emptied when neko.db.Manager.cleanup() is called.

Custom fields

Sometimes, you might want to add some fields in the SPOD Object that are not present in the database. For example let's say you always want to have the parentName field retrieved with your User. Since parentName is not part of the database fields, you need to add it to the PRIVATE_FIELDS array :

class User extends neko.db.Object {
     static var PRIVATE_FIELDS = ["parentName"];
     ...
     public var parentName : String;

     public static var manager = new UserManager();
}

Now you want the parent name to be retrieved for each User. For that, you need to override the UserManager.make method :

class UserManager extends neko.db.Manager<User> {

    public function new() {
        super(User);
    }

    override function make(o : User) {
        var p = o.parent;
        if( p != null )
            o.parentName = p.name;
    }
}

By overriding the method make, some application-specific code can be used after each object initialization. This code will be executed for all User objects retrieved from the database.

When the object is updated, we can also override the unmake method in order to perform some computation just before the SQL request is built :

class MyManager extends neko.db.Manager<MyClass> {
    ....
    override function unmake( o : MyClass ) {
        // will increment the version counter for each object update()
        o.version++; 
    }
}

Using multiple/not-integer keys

Here's an example on how to work with multiple keys or keys that are not integers. We need to define the TABLE_IDS static array that defines several keys being used for the SPOD Object :

class Session extends neko.db.Object {
    static var TABLE_IDS = ["sid","uid"];
    public var sid : String;
    public var uid : Int;

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

    public function new(sid,uid) {
        this.sid = sid;
        this.uid = uid;
    }

}

We can then use the getWithKeys SPOD Manager method :

class App {
    // ... 
    static function execute() {
         var s = Session.manager.getWithKeys({ sid : "XXXXX", uid : 10 });
         if( s == null ) {
             s = new Session("XXXXX",10);
             s.insert(); 
         }
    }
}