Codegeneration for stored procedures / Support in Sql2005 Database adapter / Introduce db capability support

[akrohn]

In the last weeks I worked in implementing codegeneration for stored procedures. This is splitted into the base part and the database adapter specific part. The first adapter to support this is the Sql2005 Database adapter.

DB ADAPTER CAPABILITY SUPPORT
Because not every database adapter supports this I implemented the functionality to check the capabilities of a database adapter. So the codegeneration can act differently depending on supported capability.

In QDatabaseBase is a new array $blnCapabilitesArray and some functions to check/set a capability. The Constructor sets the minimal capabilities (TableSupport? => true, StoredProcedureSupport? => false). This then can be overriden by the specific database adapter. So it should be backward compatible! But if anyone could check out with mysql and other datbases if it works, I would be glad.

CODEGENERATION FOR STORED PROCEDURES
This is splitted into the base part (QDatabaseBase, QDatabaseCodeGen) and the database adapter specific part (QSqlServer2005Database).

The implementation supports multiple resultsets, output paramters and return values.

The stored procedure class files will be generated in "MODEL_GEN/stored_procedures". The creation of this new directory is not included in this patch (Tortoise does not generate it). So a maintainer with write access should do this.

The class paths will be in the new file "MODEL_GEN/_stored_procedure_class_paths.inc.php". This is also not included in this patch. So a maintainer with write access should do this.

The new files QStoredProcedure.class.php and QStoredProcedureParameter.class.php have to be under "includes/qcubed/_core/codegen/". A maintainer with write access should do this.

CODEGEN TEMPLATE
The template for the stored procedure files has to be under "includes/qcubed/_core/codegen/templates/".

1. db_stored_procedure_main.tpl
There has to be a new directory "db_stored_procedure" and under this a new directory "class_gen". There goes this file with the name "_main.tpl". A maintainer with write access should do this.

CLASS PATH TEMPLATE
The template file has to be under "includes/qcubed/_core/codegen/templates/".

2. aggregate_db_stored_procedure_main.tpl
There has to be a new directory "aggregate_db_stored_procedure" and under this a new directory "class_paths". There goes this file with the name "_main.tpl". A maintainer with write access should do this.

This should be everything it needs and I hope it has no effect for database adapter that don't support stored procedures at this time. Please test it.

The implementation for other adapters should be possible with this infrastructure. At least I had this in mind when designing this.

Hope this brings QCubed to another step of code generation and others find this useful.

[VexedPanda]

Preliminarily, this sounds great, but I'm unclear on the specifics about what it does.
Could you build an example for this? What functionality do the generated classes have? What benefits does this bring?

[akrohn]

Here is an example:

We have the following test stored procedure defined:

ALTER PROCEDURE dbo.uspTest
	@konfig_id INT
	, @wert NVARCHAR(500) OUTPUT
	, @beschreibung NVARCHAR(500) OUTPUT
	, @bool BIT OUTPUT
	, @float DECIMAL(10,2) OUTPUT
	, @zeitpunkt DATETIME = '03.03.2010 10:00:00'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @ret INT = 0;
	
	IF (@konfig_id IS NULL)
	BEGIN
        SET @ret = dbo.ufsGetErrorMsgId('PROC_PFLICHTFELD');
        RETURN @ret;
	END
	

	SELECT k.*, @zeitpunkt AS zeitpunkt
	FROM dbo.Konfig AS k
	WHERE k.konfig_id = @konfig_id;

	SELECT k.*, @zeitpunkt AS zeitpunkt
	FROM dbo.Konfig AS k;

	SELECT @wert = k.wert
	    , @beschreibung = k.beschreibung
	FROM Konfig AS k
	WHERE k.konfig_id = @konfig_id;
	
	SELECT @bool = 1
	    , @float = 456.34

    RETURN @ret;
END

This procedure expects 1 input parameter (konfig_id) and 1 optional (@zeitpunkt) input paramter, returns 2 resultsets and has 4 output parameters. The return value is mostly used for returning error codes.

In QCubed a class named "UspTest?" will be generated and can be used this way:

<?php
require_once('../qcubed.inc.php');
require('../includes/configuration/header.inc.php');

$objDb = QApplication::$Database[1];
$objSpTest = new UspTest($objDb);
// Set the input parameter
$dttNow = new QDateTime("now");
$objSpTest->Parameters()->KonfigId = 3;
$objSpTest->Parameters()->Zeitpunkt = $dttNow;


// Call the procedure
$intReturnValue = $objSpTest->Call();
_p('<b>Return value='.$intReturnValue.'</b><br>', false);

// List resultsets
$objResultset = $objSpTest->GetNextResult();
while (!is_null($objResultset)) {
        _p('<b>Resultset</b><br>', false);
        _p('<ul>', false);
        while ($objRowArray = $objResultset->FetchArray()) {
                _p('<li>' . $objRowArray['konfig_id'], false);
                _p(' | ' . $objRowArray['konstante'], false);
                _p(' | ' . $objRowArray['wert'], false);
                _p(' | ' . $objRowArray['beschreibung'], false);
                _p(' | ' . $objRowArray['zeitpunkt']->format('d.m.Y'), false);
                _p('</li>', false);
        }
        _p('</ul>', false);
        $objResultset = $objSpTest->GetNextResult();
}

// Outputparameters
_p('<b>Outputparameter Array</b><br>', false);
$mixOutputParameterArray = $objSpTest->GetOutputParameterArray();
if ($mixOutputParameterArray === false) {
        _p('<b>There are still resultsets available. Can't get output parameters!</b><br>', false);
}
else {
        _p('<ul>', false);
        foreach ($mixOutputParameterArray as $strParamName => $mixParamValue) {
                _p('<li>' . $strParamName . '=' . $mixParamValue . '</li>', false);
        }
        _p('</ul>', false);
}

_p('<b>Outputparameter spezifisch</b><br>', false);
$strParamName = 'Beschreibung';
$mixParamValue = $objSpTest->GetOutputParameter($strParamName);
if ($mixParamValue === false) {
        _p('<b>Error getting output parameter!</b><br>', false);
}
else {
        _p($strParamName . '=' . $mixParamValue . '<br>', false);
}

require('../includes/configuration/footer.inc.php');
?>

[VexedPanda]

Ok, that looks pretty useful.
What would it take to migrate this to the mysql adapters?

[akrohn]

Extending the MySQL adapter should be possible by looking a little into it. (http://dev.mysql.com/doc/refman/5.0/en/stored-routines-metadata.html , http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html).

It would consist of the implementation of GetStoredProcedures?(), GetParametersForStoredProcedure?() for the codegeneration part and the specific classes for QMySqlDatabaseStoredProcedureBase, QMySqlDatabaseStoredProcedureParameters for the runtime part.

[alex94040]

Akrohn - this looks super, super-useful. I would love to see this in the core, even if we don't have MySQL support right away.

BTW, if someone has time to add support for MySQL for this, a lot of people will love them :) :)