{ $Id: PDOSQL.pas 32 2007-01-22 01:35:20Z jmarino $

############################################################################################

	Pascal Data Objects Library
	Copyright © 2006 John Marino, http://www.synsport.com
	Project site: http://pdo.sourceforge.net

############################################################################################

	This library is free software; you can redistribute it and/or
	modify it under the terms of the GNU Lesser General Public
	License as published by the Free Software Foundation; either
	version 2.1 of the License, or (at your option) any later version.

	This library is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
	Lesser General Public License for more details.

	You should have received a copy of the GNU Lesser General Public
	License along with this library; if not, write to the Free Software
	Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

############################################################################################}

unit PDOSQL;

{$I directives.inc}

interface

uses PDOClasses, sysUtils;

{$IFDEF ENABLE_MYSQL}
    {$DEFINE COMMON_SQL}
{$ELSE}
{$IFDEF ENABLE_SQLITE}
    {$DEFINE COMMON_SQL}
{$ELSE}
{$IFDEF ENABLE_POSTGRESQL}
    {$DEFINE COMMON_SQL}
{$ENDIF}  //end pgsql
{$ENDIF}  //end sqlite
{$ENDIF}  //end mysql

function assemble_vanilla         (Select: PDO_selectRecord): AnsiString;
{$IFDEF COMMON_SQL}
function assemble_select_mysql    (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_POSTGRESQL}
function assemble_select_pgsql    (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_SQLITE}
function assemble_select_sqlite   (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_DBLIB}
function assemble_select_mssql    (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_DB2}
function assemble_select_db2      (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_ORACLE}
function assemble_select_oracle   (Select: PDO_selectRecord): AnsiString;
{$ENDIF}
{$IFDEF ENABLE_FIREBIRD}
function assemble_select_firebird (Select: PDO_selectRecord): AnsiString;
{$ENDIF}

implementation

function assemble_vanilla (Select: PDO_selectRecord): AnsiString;
var
    condition: ansistring;
    orderby:   ansistring;
begin
    condition := '';
    orderby   := '';

    if (Select.sqlConditions <> '') then condition := 'WHERE '    + select.sqlConditions;
    if (Select.sqlOrderBy <> '')    then orderby   := 'ORDER BY ' + select.sqlOrderBy;

    Result := format ('SELECT %s FROM %s %s %s', [
                        Select.sqlColumns,
                        Select.sqlTables,
                        condition,
                        orderby
               ]);
end;

{$IFDEF ENABLE_POSTGRESQL}
{PostgreSQL is identical to MySQL in basic SELECT statement syntax}
function assemble_select_pgsql (Select: PDO_selectRecord): AnsiString;
begin
    Result := assemble_select_mysql(Select);
end;
{$ENDIF}

{$IFDEF ENABLE_SQLITE}
{SQLite is identical to MySQL in basic SELECT statement syntax}
function assemble_select_sqlite (Select: PDO_selectRecord): AnsiString;
begin
    Result := assemble_select_mysql(Select);
end;
{$ENDIF}

{$IFDEF COMMON_SQL}
function assemble_select_mysql (Select: PDO_selectRecord): AnsiString;
begin
    Result := assemble_vanilla (Select);

    if (Select.sqlOffset = 0) and (Select.sqlLimit > 0) then
        Result := Result + format(' LIMIT %d', [Select.sqlLimit]);

    if (Select.sqlOffset > 0) and (Select.sqlLimit > 0) then
        Result := Result +  format (' LIMIT %d OFFSET %d', [Select.sqlLimit, Select.sqlOffset]);
end;
{$ENDIF}

{$IFDEF ENABLE_DB2}
function assemble_select_db2    (Select: PDO_selectRecord): AnsiString;
begin
    if (Select.sqlOffset > 0) and (Select.sqlLimit > 0) then
        begin
            Result := format ('SELECT * ' +
                              'FROM (SELECT ROW_NUMBER() OVER (ORDER_BY %s) AS RN, %s FROM %s WHERE %s) ' +
                              'WHERE RN > %d AND RN <= %d', [
                                    Select.sqlOrderBy,
                                    Select.sqlColumns,
                                    Select.sqlTables,
                                    Select.sqlConditions,
                                    Select.sqlOffset,
                                    (Select.sqlOffset + Select.sqlLimit)
                     ]);
            exit;
        end;


    Result := assemble_vanilla (Select);
    if (Select.sqlOffset = 0) and (Select.sqlLimit > 0) then
        Result := Result + format(' FETCH FIRST %d ROWS ONLY', [Select.sqlLimit]);
end;
{$ENDIF}

{$IFDEF ENABLE_ORACLE}
function assemble_select_oracle (Select: PDO_selectRecord): AnsiString;
begin
    if (Select.sqlOffset = 0) and (Select.sqlLimit = 0) then
        begin
            Result := assemble_vanilla (Select);
            exit;
        end;

    Result := format ('SELECT * ' +
                      'FROM (SELECT ROW_NUMBER() OVER (ORDER_BY %s) AS RN, %s FROM %s WHERE %s) WHERE ', [
                            Select.sqlOrderBy,
                            Select.sqlColumns,
                            Select.sqlTables,
                            Select.sqlConditions
                ]);

    if (Select.sqlOffset = 0) and (Select.sqlLimit > 0) then
        Result := Result + format ('RN <= %d', [select.sqlLimit + select.sqlOffset]);

    if (Select.sqlOffset > 0) and (Select.sqlLimit > 0) then
        Result := Result + format ('RN > %d AND RN <= %d', [select.sqlOffset ,(select.sqlLimit + select.sqlOffset)]);
end;
{$ENDIF}

{$IFDEF ENABLE_FIREBIRD}
function assemble_select_firebird (Select: PDO_selectRecord): AnsiString;
var
    condition: ansistring;
    orderby:   ansistring;
begin
    Result := assemble_vanilla (Select);
    if (Select.sqlOffset = 0) and (Select.sqlLimit = 0) then exit;

    condition := '';
    orderby   := '';

    if (Select.sqlConditions <> '') then condition := 'WHERE '    + select.sqlConditions;
    if (Select.sqlOrderBy <> '')    then orderby   := 'ORDER BY ' + select.sqlOrderBy;

    if (Select.sqlOffset = 0) and (Select.sqlLimit > 0) then
        Result := format ('SELECT FIRST %d %s FROM %s %s %s', [
            Select.sqlLimit,
            Select.sqlColumns,
            Select.sqlTables,
            condition,
            orderby
        ]);

    if (Select.sqlOffset > 0) and (Select.sqlLimit > 0) then
        Result := format ('SELECT FIRST %d SKIP %d %s FROM %s %s %s', [
            Select.sqlLimit,
            Select.sqlOffset,
            Select.sqlColumns,
            Select.sqlTables,
            condition,
            orderby
        ]);
end;
{$ENDIF}

{$IFDEF ENABLE_DBLIB}
function assemble_select_mssql  (Select: PDO_selectRecord): AnsiString;
var
    condition:  ansistring;
    orderby:    ansistring;
    test:       ansistring;
    mirror:     ansistring;
    firstOrder: ansistring;
begin
    Result := assemble_vanilla (Select);
    if (Select.sqlOffset = 0) and (Select.sqlLimit = 0) then exit;
                  
    condition := '';
    orderby   := '';

    if (Select.sqlConditions <> '') then condition := 'WHERE '    + select.sqlConditions;
    if (Select.sqlOrderBy <> '')    then orderby   := 'ORDER BY ' + select.sqlOrderBy;

    if (Select.sqlOffset = 0) and (Select.sqlLimit > 0) then
        Result := format ('SELECT TOP %d %s FROM %s %s %s', [
            Select.sqlLimit,
            Select.sqlColumns,
            Select.sqlTables,
            condition,
            orderby
        ]);

     if (Select.sqlOffset > 0) and (Select.sqlLimit > 0) then
        Begin
            firstOrder := select.sqlOrderBy;
            test := uppercase(firstOrder);
            if (ansipos('ASC', test) < 0) and (ansipos('DESC', test) < 0) then
                firstOrder := firstOrder + ' ASC';

                mirror := StringReplace (firstOrder, 'ASC', 'NEW$DESC', [rfReplaceAll, rfIgnoreCase]);
                mirror := StringReplace (mirror, 'DESC', 'ASC', [rfReplaceAll, rfIgnoreCase]);
                mirror := StringReplace (mirror, 'NEW$DESC', 'DESC', [rfReplaceAll, rfIgnoreCase]);

                Result := format ('SELECT * FROM ' +
                                    '(SELECT TOP %d FROM ' +
                                        '(SELECT TOP %d %s FROM %s %s ORDER BY %s) AS L2 ' +
                                     'ORDER BY %s) AS L1 ' +
                                   'ORDER BY %s', [
                                        Select.sqlLimit,
                                        (Select.sqlLimit + Select.sqlOffset),
                                        Select.sqlColumns,
                                        Select.sqlTables,
                                        condition,
                                        orderby,
                                        mirror,
                                        orderby
                            ]);
        end
end;
{$ENDIF}





end.