Submitted by: José María Sánchez (jmsanchez)

Relate to DNET208
Is duplicated by DNET404

Hi all,
I am working on a project to create a front-end application for a Firebird database with Microsoft Lightswitch. I am using Firebird Server 2.5, Firebird NETProvider 2.7.5 and Firebird DDEX Provider 2.0.5. Some days ago I found a problem that has puzzled me for several days. I think that the issue is due to a problem in the NETProvider, but it also could be related to the information that the DDEX provider sends to NETProvider for generating the SQL queries.
I report it because I think it may be a bug and maybe could help others to solve a similar problem.

Simple SQL queries for screens displaying data from a single table seem to generate correctly, but I found a problem in complex JOIN queries for screens that display information from different related tables using Foreign Keys.

In my Firebird database I have several related tables. I include some of them as an example.

/* Original table name is "memos" */
CREATE TABLE "memos" (
"id_memo" INTEGER CONSTRAINT "IDPK_id_memo" NOT NULL PRIMARY KEY /* "id_memo" */ ,
"id_text_grup" INTEGER NOT NULL /* "id_text_grup" */
);

/* Original table name is "memos_data" */
CREATE TABLE "memos_data" (
"id_memo_data" INTEGER CONSTRAINT "IDPK_id_memo_data" NOT NULL PRIMARY KEY /* "id_memo_data" */ ,
"id_memo" INTEGER NOT NULL /* "id_memo" */ ,
"id_idioma" INTEGER NOT NULL /* "id_idioma" */ ,
"text" BLOB SUB_TYPE 1 /* "text" */
);

/* Original table name is "texts_grups" */
CREATE TABLE "texts_grups" (
"id_text_grup" INTEGER CONSTRAINT "IDPK_id_text_grup" NOT NULL PRIMARY KEY /* "id_text_grup" */ ,
"nom_grup" VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8 /* "nom_grup" */
);

/* Original table name is "idiomes" */
CREATE TABLE "idiomes" (
"id_idioma" INTEGER CONSTRAINT "IDPK_id_idioma" NOT NULL PRIMARY KEY /* "id_idioma" */ ,
"tag" CHAR(3) CHARACTER SET UTF8 COLLATE UTF8 /* "tag" */ ,
"descrip" VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8 /* "descrip" */
);

ALTER TABLE "memos" ADD CONSTRAINT "FK_memos_id_text_grup" FOREIGN KEY ("id_text_grup") REFERENCES "texts_grups"("id_text_grup") ON UPDATE CASCADE;
ALTER TABLE "memos_data" ADD CONSTRAINT "FK_memos_data_id_idioma" FOREIGN KEY ("id_idioma") REFERENCES "idiomes"("id_idioma") ON UPDATE CASCADE;
ALTER TABLE "memos_data" ADD CONSTRAINT "FK_memos_data_id_memo" FOREIGN KEY ("id_memo") REFERENCES "memos"("id_memo") ON UPDATE CASCADE;

The NETProvider returns the following SQL error code to my Lightswitch application.

FirebirdSql.Data.FirebirdClient Information: 0 : Command:
SELECT
"A"."Extent1"."id_memo" AS "id_memo",
"A"."Extent1"."id_text_grup" AS "id_text_grup",
"A"."Extent2"."id_text_grup" AS "id_text_grup1",
"A"."Extent2"."nom_grup" AS "nom_grup"
FROM ( SELECT FIRST (45) SKIP (0) "C"."id_memo" AS "id_memo", "C"."id_text_grup" AS "id_text_grup1", "D"."id_text_grup" AS "id_text_grup2", "D"."nom_grup" AS "nom_grup"
FROM "memos" AS "C"
INNER JOIN "texts_grups" AS "D" ON "C"."id_text_grup" = "D"."id_text_grup"
ORDER BY "C"."id_memo" ASC
) AS "A"
FirebirdSql.Data.FirebirdClient Information: 0 : Parameters:

[Microsoft.LightSwitch.DataService][Application:Error][LightSwitchApplication.SodecaCatalogDatasource:memos_All] An exception has occurred: Microsoft.LightSwitch.DataServiceOperationException: Se produjo un error al ejecutar la definición del comando. Vea la excepción interna para obtener detalles. ---> System.Data.EntityCommandExecutionException: Se produjo un error al ejecutar la definición del comando. Vea la excepción interna para obtener detalles. ---> FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 14
. ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 14

That query is not correct. It should be something like:
SELECT
"A"."id_memo" AS "id_memo",
"A"."id_text_grup1" AS "id_text_grup",
"A"."id_text_grup2" AS "id_text_grup1",
"A"."nom_grup" AS "nom_grup"
FROM ( SELECT FIRST (45) SKIP (0) "C"."id_memo" AS "id_memo", "C"."id_text_grup" AS "id_text_grup1", "D"."id_text_grup" AS "id_text_grup2", "D"."nom_grup" AS "nom_grup"
FROM "memos" AS "C"
INNER JOIN "texts_grups" AS "D" ON "C"."id_text_grup" = "D"."id_text_grup"
ORDER BY "C"."id_memo" ASC
) AS "A"

So to get a better idea of what was going on, I downloaded the trunk source code, studied it for some days and found a possible solution to this problem.
I am not really sure if this is the best way to solve the issue but now all is working as expected.

The generation process for this query is something like:
1) the inner query is generated JOINING tables "C" and "D"
2) next the provider generates an intermediate query for the result of the previous JOIN as "B"
3) finally the global query is generated. As it requires no more information and to simplify the final query, "B" is reused and renamed to "A"

Debugging the process I saw that the information for the JoinSymbol required to generate "A" was somehow lost in the process. That produces this issue because the provider does not have all the internal join information ("C" and "D") needed to create the external query for "A".

I changed the following and seem to have gotten it to work:

NETProvider\source\FirebirdSql\Data\Entity\SqlGenerator.cs

SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol)
{
fromSymbol = null;

// Finalize the old statement
if \(finalizeOldStatement && oldStatement\.Select\.IsEmpty\)
\{
	List<Symbol\> columns = AddDefaultColumns\(oldStatement\);

	// Thid could not have been called from a join node\.
	Debug\.Assert\(oldStatement\.FromExtents\.Count == 1\);

	// if the oldStatement has a join as its input, \.\.\.
	// clone the join symbol, so that we "reuse" the
	// join symbol\.  Normally, we create a new symbol \- see the next block
	// of code\.
	JoinSymbol oldJoinSymbol = oldStatement\.FromExtents\[0\] as JoinSymbol;
	if \(oldJoinSymbol \!= null\)
	\{
		// Note: oldStatement\.FromExtents will not do, since it might
		// just be an alias of joinSymbol, and we want an actual JoinSymbol\.
		JoinSymbol newJoinSymbol = new JoinSymbol\(inputVarName, inputVarType, oldJoinSymbol\.ExtentList\);
		// This indicates that the oldStatement is a blocking scope
		// i\.e\. it hides/renames extent columns
		newJoinSymbol\.IsNestedJoin = true;
		newJoinSymbol\.ColumnList = columns;
		newJoinSymbol\.FlattenedExtentList = oldJoinSymbol\.FlattenedExtentList;

		fromSymbol = newJoinSymbol;
	\}
\}

//JMS\-START: THESE ARE MY LINES
if \(fromSymbol == null\)
\{
    // if the oldStatement has a join as its input, \.\.\.
    // clone the join symbol, so that we "reuse" the
    // join symbol\.  Normally, we create a new symbol \- see the next block
    // of code\.
    JoinSymbol oldJoinSymbol = oldStatement\.FromExtents\[0\] as JoinSymbol;
    if \(oldJoinSymbol \!= null\)
    \{
        // Note: oldStatement\.FromExtents will not do, since it might
        // just be an alias of joinSymbol, and we want an actual JoinSymbol\.
        JoinSymbol newJoinSymbol = new JoinSymbol\(inputVarName, inputVarType, oldJoinSymbol\.ExtentList\);
        // This indicates that the oldStatement is a blocking scope
        // i\.e\. it hides/renames extent columns
        newJoinSymbol\.IsNestedJoin = true;
        newJoinSymbol\.ColumnList = null;
        newJoinSymbol\.FlattenedExtentList = oldJoinSymbol\.FlattenedExtentList;

        fromSymbol = newJoinSymbol;
    \}
\}
//JMS\-END: THESE ARE MY LINES

if \(fromSymbol == null\)
\{
    // This is just a simple extent/SqlSelectStatement,
    // and we can get the column list from the type\.
    fromSymbol = new Symbol\(inputVarName, inputVarType\);
\}

// Observe that the following looks like the body of Visit\(ExtentExpression\)\.
SqlSelectStatement selectStatement = new SqlSelectStatement\(\);
selectStatement\.From\.Append\("\( "\);
selectStatement\.From\.Append\(oldStatement\);
selectStatement\.From\.AppendLine\(\);
selectStatement\.From\.Append\("\) "\);


return selectStatement;

}

Maybe this is not the best solution to solve the problem, but I include it as a possible solution.

Thank you very much for this wonderful project Jiri.