Haxe SPOD: Specifying a unique field

Haxe has a very nice library called SPOD Macros which allows us to manipulate database tables as strictly-typed objects instead of dealing with the plain-text SQL commands and the clumsy result sets/arrays of untyped row objects.

The Problem

As at Haxe 3.0.1, SPOD does not provide a compile-time solution (I mean metadata like @:table and @:relation) to specify a field to be unique. One of the solution is to first create the table in Haxe, then use a database management tool such as phpMyAdmin to configure the column to be unique. However, I would like to do this in my Haxe code, after all we are programmers!

The Solution

Manager.cnx = PDO.open("mysql:host=localhost;dbname=my_db;charset=utf8", "username", "password", { "1002" : "SET NAMES 'UTF8'" } );

TableCreate.create(YourDbObjectClass.manager); //note: YourDbObjectClass extends sys.db.Object

Manager.cnx.request("ALTER TABLE " + YourDbObjectClass.manager.dbInfos().name + " ADD UNIQUE (field_name)");

try
{
    //insert or update some records by calling insert() or update()
}
catch(ex:String)
{
    //an exception will be thrown if the inserted/updated value already exists
    trace(ex);
}
  • Line 1: Initialize the connection object, here I am targeting php (read alternative solution below for neko) and using a mysql database as backend.
  • Line 3: Create a table in the usual SPOD way
  • Line 5: Run a plain SQL to add the “unique” constraint to the column. After that we can use phpMyAdmin to verify that the column is actually configured as unique.
    Haxe SPOD: phpMyAdmin shows that the 'unique' constraint is activated
  • Line 7-15: Insert/update values in a try block. When a duplicate record is inserted/updated, an exception will be thrown, which can be handled by a catch block. The actual error message (in php) would be something like this:
Error while executing INSERT INTO table_name (unique_field,another_field) VALUES ('some duplicated value', 1) (Duplicate entry 'some duplicated value' for key 'unique_field')

Alternative Solution

An alternative solution to this problem would be the dbadmin developed by Nicolas Cannasse, the major developer of Haxe. According to this post, currently dbadmin is tied to neko, but it should not be difficult to port it to php.

Leave a Reply

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