unit udbconpg;
interface

uses
  Classes,
  {$IFDEF upgsql}
  upgsql,
  {$ELSE}
  pqconnection, sqldb, DB,
  {$ENDIF}
  SysUtils;


{$IFDEF upgsql}
type
  TSQLQuery = upgsql.TPgSqlRes;
{$ELSE}
type
  TSQLQuery = sqldb.TSQLQuery;
{$ENDIF}


{$IFDEF upgsql}
type
  TDBPQCon = class(TPQConnection)
    constructor Create(archi_credencial: string);
    constructor Create_hpbuc(
      host: string;
      port: cardinal;
      base: string;
      usuario: string;
      clave: string );
    procedure Free; override;
  end;
{$ELSE}
type
  TDBPQCon = class(TPQConnection)
   public
    ModoDemo: boolean;
    constructor Create(archi_credencial: string);
    constructor Create_hpbuc(
      host: string;
      port: cardinal;
      base: string;
      usuario: string;
      clave: string );
    procedure Free;
    function query(const sql: string): TSQLQuery;
    function exec(const sql: string): integer;
    function func(const sql: string; defVal: string = ''): string;
    function funcI(const sqlstr: string; defval: integer = -1): integer;
    function funcF(const sqlstr: string; defval: double = -1): double;
    function funcS(const sqlstr: string; defval: string = ''): string;
    function funcD(const sqlstr: string; defval: TDateTime = 0.0): TDateTime;
    function nextval(const tabla: string; campo: string='nid'; auto_crear: boolean=
      false): integer;
  private
    dbtran: TSQLTransaction;
  end;
    {$ENDIF}

  TDBCon = TDBPQCon;

{$IFDEF DBPQCon_Auto}
var
  db: TDBPQCon;
{$ENDIF}

implementation

{$IFNDEF upgsql}


function TDBPQCon.query(const sql: string): TSQLQuery;
var
  q: TSQLQuery;
begin
  q := TSQLQuery.Create(nil);
  q.DataBase := self;
  q.SQL.Text := sql;
  q.Open;
  Result := q;
end;

function TDBPQCon.func(const sql: string; defVal: string = ''): string;
var
  q: TSQLQuery;
begin
  q := query(sql);
  if q.EOF then
    Result := defVal
  else
    Result := q.Fields[0].AsString;
  q.Free;
end;

function TDBPQCon.exec(const sql: string): integer;
var
  q: TSQLQuery;
  dbtran:TSQLTransaction;
begin
  if ModoDemo then
  begin
    result:= 0;
    exit;
  end;
  dbtran := TSQLTransaction.Create(nil);
  dbtran.DataBase := self;
  q := TSQLQuery.Create(nil);
  q.Transaction:= dbtran;
  q.DataBase := self;
  q.SQL.Text := sql;
  q.ExecSQL;
  Result := q.RowsAffected;
  dbtran.Commit;
  q.Free;
  dbtran.Free;
end;

function TDBPQCon.funcI(const sqlstr: string; defval: integer = -1): integer;
var
  s: string;
begin
  s := func(sqlstr, IntToStr(defval));
  Result := StrToInt(s);
end;

function TDBPQCon.funcF(const sqlstr: string; defval: double = -1): double;
var
  s: string;
begin
  s := func(sqlstr, FloatToStr(defval));
  Result := StrToFloat(s);
end;

function TDBPQCon.funcS(const sqlstr: string; defval: string = ''): string;
begin
  Result := func(sqlstr, defval);
end;

function TDBPQCon.funcD(const sqlstr: string; defval: TDateTime = 0.0): TDateTime;
var
  s: string;
begin
  s := func(sqlstr, FormatDateTime('yyyy-mm-dd hh:nn:ss', defval));
  Result := StrToFloat(s);
end;


function TDBPQCon.nextval(const tabla: string; campo: string = 'nid'; auto_crear: boolean = false ): integer;
var
  s: string;
begin
  s:= func( 'SELECT nextval( '''+tabla + '_' + campo + '_seq'+''' );' );
  if (s = '') and auto_crear then
  begin
    exec( 'CREATE SEQUENCE '+tabla + '_' + campo + '_seq'+' START 1;' );
    s:= func( 'SELECT nextval( '''+tabla + '_' + campo + '_seq'+''' );' );
  end;
  if s = '' then
    raise Exception.Create( 'Falló nextval en secuencia: '+tabla + '_' + campo + '_seq' );
  result:= StrToInt( s );
end;



{$ENDIF}

constructor TDBPQCon.Create_hpbuc(
  host: string;
  port: cardinal;
  base: string;
  usuario: string;
  clave: string );
begin
{$IFDEF upgsql}
inherited Create(host, IntToStr(port), '', '', base, usuario, clave);
if not testOk then
  raise Exception.Create('Falló conexión a la db: ' + base +
    ' Error: '+self.UltimoError )
else
  AutoRiseException := True;
{$ELSE}
inherited Create(nil);
ModoDemo:= false;
HostName := host;
// self.Port:= 5432;
DatabaseName := base;
UserName := usuario;
Password := clave;
dbtran := TSQLTransaction.Create(nil);
dbtran.DataBase := self;
Connected := True;
{$ENDIF}

end;

constructor TDBPQCon.Create(archi_credencial: string);
var
  f: textfile;
  host, base, usuario, clave: string;
  port: cardinal;

begin
  if not fileexists(archi_credencial) then
  begin
    Self := nil;
    exit;
  end;

  assignfile(f, archi_credencial);
  reset(f);
  readln(f, host);
  readln(f, base);
  readln(f, usuario);
  readln(f, clave);
  closefile(f);
  port:= 5432;

  {$IFDEF CONNECT_LOCALHOST_1111}
  // para que esto funcione, primero hay que hacer un tunel
  // ssh -L 1111:localhost:5432 usaurio_remoto@servidor_remoto
  host:= 'localhost';
  port:= 1111;
  {$ENDIF}

  Create_hpbuc( host, port, base, usuario, clave )
end;

procedure TDBPQCon.Free;
begin
{$IFNDEF upgsql}
  if dbtran <> nil then
    dbtran.Active := False;
  connected := False;
  if dbtran <> nil then
    dbtran.Free;
{$ENDIF}
  inherited Free;
end;


initialization
{$IFDEF DBPQCon_Auto}
db:= TDBPQCon.Create( 'condb.dat' );
{$ENDIF}
finalization
{$IFDEF DBPQCon_Auto}
 if db <> nil then db.Free;
{$ENDIF}
end.
