Support for postgres

Jul 26, 2009 at 2:26 PM

Hi,

I would like to add support for postgres in euss.

Is there anything that i should know before starting ? Is there any test unit that I could use ?

 

Thanks.

Developer
Aug 3, 2009 at 7:22 AM

Hi,

How's the progress on postgres support?

Coordinator
Aug 3, 2009 at 8:28 AM

Hi kyann,

You should have to make a Dialect and a Driver.

To make a dialect, you need to inherit from DBDialect.

To make a driver, just implement the IDriver interface.

You can reuse the unit test dedicated to the SqlMapperEngine (if I remember well, there are 63 unit tests for this engine).

Coordinator
Aug 3, 2009 at 11:25 AM

Even more, you should copy/paste either the MySQL, Sqlite or Access dialects and drivers in Euss. Take the which SQL statements is the most similar.

The unit tests which should succeed are in Common.cs. With that you have already 99% of the functionnalities. There is a test list already configured in Visual Studio, but I don't remember its name.

Aug 4, 2009 at 11:11 PM

13 passed, 57 failed, not so bad for a first test ;)

Aug 4, 2009 at 11:38 PM

61 passed, only 9 remaining ... I'm going to sleep and i'll continue another day !

Coordinator
Aug 5, 2009 at 7:53 AM

Very good ! When you have finish we will explain what to test next. Things like different other mappings, generic db storage, LINQ, and other object based layer. But all of them shall work if Common is complete.

Aug 8, 2009 at 1:50 PM
Edited Aug 8, 2009 at 1:52 PM

65/70 !

Je continue en français ce sera plus simple pour expliquer mon avancement ;)

Il y a un test qui ne passait pas à cause d'un bug dans npgsql, j'ai submit un patch : http://pgfoundry.org/tracker/index.php?func=detail&aid=1010683&group_id=1000140&atid=592

Après j'ai du modifier un test car postgres renvoi des int64 dans certains cas (lorsque l'on fait des calculs) et Assert.Equal test aussi le type retourné.

Pour le test "inheritancemultibranch" j'ai eu des problèmes liés aux requêtes d'UNION :

 

SELECT 'Square' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e6"."ShapeId" AS "pkShapeId", "e6"."Name" AS "Name", "e6"."Pos" AS "Position",
"e6"."Length" AS "Size", NULL AS "PositionEnd" , "e6"."Length" AS "Size1", NULL AS "Height"
FROM "Shape"  AS "e6"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e6"."FK_FigureId" )
WHERE (("e6"."Type" = 'Square' ) )
UNION ALL SELECT 'Shape' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e2"."ShapeId" AS "pkShapeId", "e2"."Name" AS "Name", "e2"."Pos" AS "Position",
    NULL AS "Size" , NULL AS "PositionEnd" , NULL AS "Size1" , NULL AS "Height"
FROM "Shape"  AS "e2"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e2"."FK_FigureId" )
WHERE (("e2"."Type" = 'Shape' ) )
UNION ALL SELECT 'Circle' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e4"."ShapeId" AS "pkShapeId", "e4"."Name" AS "Name", "e4"."Pos" AS "Position",
"e4"."Radius" AS "Size", NULL AS "PositionEnd" , "e4"."Radius" AS "Size1", NULL  AS "Height"
FROM "Shape"  AS "e4"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e4"."FK_FigureId" )
WHERE (("e4"."Type" = 'Circle' ) )
UNION ALL SELECT 'Line' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e5"."ShapeId" AS "pkShapeId", "e5"."Name" AS "Name", "e5"."Pos" AS "Position",
NULL  AS "Size" , "e5"."Size" AS "PositionEnd", NULL  AS "Size1" , NULL  AS "Height"
FROM "Shape"  AS "e5"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e5"."FK_FigureId" ) WHERE (("e5"."Type" = 'Line' ) )


Quand une colonne virtuelle à la valeur NULL alors postgres fait un fallback sur un type string pour cette colonne. Donc en fonction des selects qui sont
dans l'union on a la même colonne qui a deux types différents (text ou int).
Ca pose un problème pour postgres et donc la requete passe pas. Pour forcer un type sur le NULL il faut utiliser cette syntaxe : NULL::{type}. Ce qui donne :
SELECT 'Square' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e6"."ShapeId" AS "pkShapeId", "e6"."Name" AS "Name", "e6"."Pos" AS "Position",
"e6"."Length" AS "Size", NULL::integer  AS "PositionEnd" , "e6"."Length" AS "Size1", NULL::integer  AS "Height"
FROM "Shape"  AS "e6"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e6"."FK_FigureId" )
WHERE (("e6"."Type" = 'Square' ) )
UNION ALL SELECT 'Shape' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e2"."ShapeId" AS "pkShapeId", "e2"."Name" AS "Name", "e2"."Pos" AS "Position",
    NULL::integer  AS "Size" , NULL::integer  AS "PositionEnd" , NULL::integer  AS "Size1" , NULL::integer  AS "Height"
FROM "Shape"  AS "e2"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e2"."FK_FigureId" )
WHERE (("e2"."Type" = 'Shape' ) )
UNION ALL SELECT 'Circle' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e4"."ShapeId" AS "pkShapeId", "e4"."Name" AS "Name", "e4"."Pos" AS "Position",
"e4"."Radius" AS "Size", NULL::integer  AS "PositionEnd" , "e4"."Radius" AS "Size1", NULL::integer  AS "Height"
FROM "Shape"  AS "e4"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e4"."FK_FigureId" )
WHERE (("e4"."Type" = 'Circle' ) )
UNION ALL SELECT 'Line' AS "EntityType" , "e1"."ShapeId" AS "ParentId", "e5"."ShapeId" AS "pkShapeId", "e5"."Name" AS "Name", "e5"."Pos" AS "Position",
NULL  AS "Size" , "e5"."Size" AS "PositionEnd", NULL::integer  AS "Size1" , NULL::integer  AS "Height"
FROM "Shape"  AS "e5"
INNER JOIN "Shape"  AS "e1" ON ("e1"."ShapeId" = "e5"."FK_FigureId" ) WHERE (("e5"."Type" = 'Line' ) )

Donc j'ai fait un override dans PgSqlDialect de la fonction Visit(Constant constant) pour rajouter le type derrière le NULL. Pour ce faire j'ai du
utiliser le DbType de la constant qui dans ce cas était toujours set à AnsiString :(.
En fouillant un peu j'ai vu que cette constante était créé dans la fonction ConvertToSQL du fichier SqlMapperTransformer.
Et je me suis permis de rajouter un petit bout de code pour avoir le DbType correcte (ligne 695) :
string attrType = attName.Substring(0, attName.LastIndexOf(DOT));
// Get the mapping for this attribute, so we can set the right dbType
var modelAttr = _Mapping.Entities[attrType].Attributes[shortAttName];
childQuery.SelectList.Add(new Constant(DBNull.Value, modelAttr.DbType, attNameAlias));


Voila, maintenant il me reste un soucis lié au contrainte de clé étrangère, un problème sur le type money et le test de multithreading
aussi qui ne fonctionne pas. J'espere pouvoir régler ca ce week end si j'ai le temps :)

 

Aug 8, 2009 at 1:53 PM
Juste un mail d'encouragement pour cette avancée ...