Haxe PHP remoting server and PostgreSQL database

[PSu]

Hey Guys !

First, i'm sorry for my poor english.

I have to use a PHP remoting server with a PostgreSQL database. I found a haxe implementation of Postgres Wire Protocol (https://github.com/jdonaldson/postgrehx).

After implementing this, I had to patch Manager, RecordMacros and Object classes because the SQL built whith buildSQL function was not compatible with Postgre.  It is possible that my patched classes are not fully functional because i only worked on functions that concerned my personal needs.

Today, I can simply insert, update and delete SPOD Object on a PostreSQL Database using the same syntax (ex: select($foo == foo);) but my patched classes are no longer working with a MySQL database.

So I would like to know if you project to add a postgre implementation ? If I understood properly, in the past this was the case so if it's true why did you stop postgreSQL support ? Have you a proper method for Manager works with PostgreSQL language and MySQL language ?

Regards!

[Jason O'Neil]

Hi

This sounds really interesting.  Do you have the changes you made available anywhere? 

What would be great is to create a fork of the Haxe repo on Github, place your changes in as they are, and submit a pull request.  In the comments make a note that it breaks MySQL, but it will at least make it easy to look at what you've changed to get PostgreSQL to work, and maybe from there we can figure out a way to get it to work in all databases.

If you have time I'd appreciate it, I'd like to see PostgreSQL support with the DB macros.

Jason

--
To post to this group haxe...@googlegroups.com
http://groups.google.com/group/haxelang?hl=en
---
You received this message because you are subscribed to the Google Groups "Haxe" group.
For more options, visit https://groups.google.com/d/optout.

[theRemix]

Hi PSu,

Can you post your Manager, RecordMacros, and Object class changes?

I have some fixes for them, and i'd like to see what you did, and if it would help.

I posted a pull request for my changes, and i can include yours if i can fix compatibility with MySQL. 

https://github.com/HaxeFoundation/haxe/pull/3044

[PSu]

Hi!

I'm very busy for the moment. I'm happy to know that it can be interesting for some of you.

I will try to post it on github as soon as possible

[Justin Donaldson]

Thanks for digging into that issue.  

Let me know if you find some rough spots.  I'm still working on date issues, but most other basic types should work.

Hi!

I'm very busy for the moment. I'm happy to know that it can be interesting for some of you.

I will try to post it on github as soon as possible

--

[Yc]

Hi!

Sorry for this late response and my poor english,

PSu (my mate) don't make a pull request because this fix is very specific for our project (not fully functional, we suppose) .

But maybe, you will have a better solution to use SPOD for PHP server with a PostGreSQL database and keep compatibility with  a MySQL database.

I ask the same question that PSu : 

So I would like to know if you project to add a postgre implementation ? If I understood properly, in the past this was the case so if it's true why did you stop postgreSQL support ? Have you a proper method for Manager works with PostgreSQL language and MySQL language 

This is the fix who made PSu (my mate) for buildSQL function to be compatible with Postgre :

(Sorry for this ugly diff)

On Manager.hx :

Line 78 :

public function quoteTableName(tableName : String) : String

{

var postgreDB : Bool = (cnx.dbName() == "PostgreSQL");

return (postgreDB ? "\"" : "") + tableName + (postgreDB ? "\"" : "");

  }

public function all( ?lock: Bool ) : List<T> {

return unsafeObjects("SELECT * FROM " + quoteTableName(table_name),lock);

}

 Line 108 :

public function dynamicSearch( x : {}, ?lock : Bool ) : List<T> {

var s = new StringBuf();

s.add("SELECT * FROM ");

s.add(quoteTableName(table_name));

s.add(" WHERE ");

addCondition(s,x);

return unsafeObjects(s.toString(),lock);

}

line 169 : 

s.add("INSERT INTO ");

s.add(quoteTableName(table_name));

s.add(" (\"");

s.add(fields.join("\", \""));

s.add("\") VALUES (");

var first = true;

line 209

var s = new StringBuf();

s.add("UPDATE ");

s.add(quoteTableName(table_name));

s.add(" SET ");de here...

line 244

function doDelete( x : T ) {

var s = new StringBuf();

s.add("DELETE FROM ");

s.add(quoteTableName(table_name));

s.add(" WHERE ");

addKeys(s,x);

unsafeExecute(s.toString());

removeFromCache(x);

}

function doLock( i : T ) {

if( untyped i._lock )

return;

var s = new StringBuf();

s.add("SELECT * FROM ");

s.add(quoteTableName(table_name));

s.add(" WHERE ");

addKeys(s, i);

// will force sync

if( unsafeObject(s.toString(),true) != i )

throw "Could not lock object (was deleted ?); try restarting transaction";

}

line 416

public function unsafeGet( id : Dynamic, ?lock : Bool ) : T {

if( lock == null ) lock = true;

if( table_keys.length != 1 )

throw "Invalid number of keys";

if( id == null )

return null;

var x : Dynamic = getFromCacheKey(Std.string(id) + table_name);

if( x != null && (!lock || x._lock) )

return x;

var s = new StringBuf();

s.add("SELECT * FROM ");

s.add(quoteTableName(table_name));

s.add(" WHERE ");

s.add(quoteField(table_keys[0]));

s.add(" = ");

getCnx().addValue(s,id);

return unsafeObject(s.toString(), lock);

}

public function unsafeGetWithKeys( keys : { }, ?lock : Bool ) : T {

if( lock == null ) lock = true;

var x : Dynamic = getFromCacheKey(makeCacheKey(cast keys));

if( x != null && (!lock || x._lock) )

return x;

var s = new StringBuf();

s.add("SELECT * FROM ");

s.add(quoteTableName(table_name));

s.add(" WHERE ");

addKeys(s,keys);

return unsafeObject(s.toString(),lock);

}

On RecordMacros.hx 

line 410

//WARNING : It breaks MySQL Compatibility

function quoteField( f : String ) {

//var m : { private var KEYWORDS : haxe.ds.StringMap<Bool>; } = PATCHManager;

return /*m.KEYWORDS.exists(f.toLowerCase()) ? "`"+f+"`" :*/ f; 

}

[Jason O'Neil]

This could be helpful - would you submit it as a pull request so that it's easier to see what has happened and hopefully merge?