{ $Id: PDOSQLDelete.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 PDOSQLDelete;

{$I directives.inc}

interface

uses PDOClasses, sysUtils; //, PDOSysUtils;

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

{$IFDEF COMMON_SQL}
function assemble_multidelete_common (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
{$ENDIF}

{$IFDEF ENABLE_MYSQL}
function assemble_delete_mysql (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;

function assemble_multidelete_mysql (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
{$ENDIF}

{$IFDEF ENABLE_POSTGRESQL}
function assemble_delete_pgsql (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;

function assemble_multidelete_pgsql (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
{$ENDIF}

{$IFDEF ENABLE_FIREBIRD}
function assemble_delete_firebird_15 (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;

function assemble_delete_firebird_20 (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;
{$ENDIF}

implementation

{$IFDEF ENABLE_MYSQL}
function assemble_delete_mysql (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;
var
    str_condition: ansistring;
    str_order: ansistring;
    str_limit: ansistring;
begin
    case DeletionMethod of
        TRUNCATE:       Result := format ('TRUNCATE %s', [tablename]);
        DELETE_ALL:     Result := format ('DELETE FROM %s', [tablename]);
    else
        begin
            str_order     := '';
            str_limit     := '';
            str_condition := '';
            
            if limit <> 0 then
                begin
                    str_limit := format ('LIMIT %d', [limit]);
                    if order <> '' then str_order := 'ORDER BY ' + order;
                end;
            if condition <> '' then str_condition := 'WHERE ' + condition;

            Result := format ('DELETE FROM %s %s %s %s', [
                tablename,
                str_condition,
                str_order,
                str_limit
            ]);
        end;
    end;
end;

function assemble_multidelete_mysql (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
begin
    Result := assemble_multidelete_common (tables, table_joins, condition);
end;



{$ENDIF}

{$IFDEF ENABLE_POSTGRESQL}
function assemble_delete_pgsql (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;
var
    str_condition: ansistring;
    str_order: ansistring;
begin
    case DeletionMethod of
        TRUNCATE:       Result := format ('TRUNCATE %s', [tablename]);
        DELETE_ALL:     Result := format ('DELETE FROM %s', [tablename]);
    else
        begin
            str_condition := '';
            if condition <> '' then str_condition := 'WHERE ' + condition;

            if limit = 0 then
                Result := format ('DELETE FROM %s %s', [tablename, condition])
            else
                begin
                    str_order := '';
                    if order <> '' then str_order := 'ORDER BY ' + order;

                    Result := format ('DELETE FROM %s WHERE ctid = (SELECT ctid FROM %s %s %s LIMIT %d)', [
                                        tablename,
                                        tablename,
                                        str_condition,
                                        str_order,
                                        limit
                              ]);
                end;
        end;
    end;
end;

function assemble_multidelete_pgsql (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
begin
    Result := assemble_multidelete_common (tables, table_joins, condition);
end;
{$ENDIF}

{$IFDEF ENABLE_FIREBIRD}
function assemble_delete_firebird_20 (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;
var
    str_condition: ansistring;
    str_order: ansistring;
    str_limit: ansistring;
begin
    case DeletionMethod of
        TRUNCATE,
        DELETE_ALL:  Result := format ('DELETE FROM %s', [tablename]);
    else
        begin
            str_order     := '';
            str_limit     := '';
            str_condition := '';

            if limit <> 0 then
                begin
                    str_limit := format ('ROWS %d', [limit]);
                    if order <> '' then str_order := 'ORDER BY ' + order;
                end;
            if condition <> '' then str_condition := 'WHERE ' + condition;

            Result := format ('DELETE FROM %s %s %s %s', [
                tablename,
                str_condition,
                str_order,
                str_limit
            ]);
        end;
    end;
end;



function assemble_delete_firebird_15 (
    const DeletionMethod: TDeletionMethod;
    const tablename: Ansistring;
    const condition: Ansistring = '';
    const order: Ansistring = '';
    const limit: longword = 0): ansistring;
var
    str_condition: ansistring;
    str_order: ansistring;
begin
    case DeletionMethod of
        TRUNCATE,
        DELETE_ALL:  Result := format ('DELETE FROM %s', [tablename]);
    else
        begin
            str_condition := '';
            if condition <> '' then str_condition := 'WHERE ' + condition;

            if limit = 0 then
                Result := format ('DELETE FROM %s %s', [tablename, condition])
            else
                begin
                    str_order := '';
                    if order <> '' then str_order := 'ORDER BY ' + order;

                    Result := format ('DELETE FROM %s WHERE RDB$DB_KEY = (SELECT FIRST %d RDB$DB_KEY FROM %s %s %s)', [
                                        tablename,
                                        limit,
                                        tablename,
                                        str_condition,
                                        str_order
                              ]);
                end;
        end;
    end;
end;
{$ENDIF}


{$IFDEF COMMON_SQL}
function assemble_multidelete_common (
    const tables: Ansistring;
    const table_joins: Ansistring;
    const condition: ansistring = ''): ansistring;
var
    str_condition:   ansistring;
begin
    str_condition   := '';
    if condition <> '' then str_condition := 'WHERE ' + condition;

    Result := format ('DELETE FROM %s USING %s %s', [
                tables,
                table_joins,
                str_condition
    ]);
end;
{$ENDIF}

end.