[firebird-support] How to detect broken database during backup ?

2019-12-06 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Hello,

I have an application server that automatically backup it's databases on
regular basis. As my application is deployed in the wild wild customers
world, databases can be striked from time to time (due to power loss,
hardware failures, ...) and, as far as I know, the firebird backup service
API don't return any status code of the database healthiness.

How can I detect "broken" databases during backup ? I'm using Firebird 3.0+

Many thanks, regards,

-- 
Pierre Yager
Crisalid

Re: [firebird-support] How to index this table

2019-11-18 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Hi Karol,

Thank you for your advices.

Adding "simple" indexes on "MAGASIN" and "CAISSE" makes query running more
slowly : 49s with indexes vs 44s without

When the table is indexed on (ANNULE, TYPE_MOUVEMENT, DATEHEURE) they are
not used at all.

Regards,

-- 
Pierre Yager


On Fri, Nov 15, 2019 at 7:26 PM Karol Bieniaszewski
liviusliv...@poczta.onet.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Hi
>
>
>
> Your problem is that you have (X or X) and (Y or Y)
>
> Firebird cannot use composite index here.
>
> Maybe it can somehow make something like (X1 and Y1) or (X1 and Y2) or …
> or (X1 and YN) or (X2 and Y1) or (X2 and Y2) …. (XN and YN)
>
> But it will be never optimal.
>
>
>
> Create separate indexes for magasin and caisse – then Firebird can use
> BITMAP OR + BITMAP AND
>
>
>
> Another optimisation will be instead of multiple OR put all values into
> temporary table and then join with it.
>
> Any other optimisations depend on value propagation in particular fields
> involved in the where clause.
>
>
>
> Regards,
>
> Karol Bieniaszewski
>
> 
>

[firebird-support] How to index this table

2019-11-15 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Hi,

I'm on Windows 7, running Firebird 3.0.4 :

I have this table hosting 1475813 records :

CREATE TABLE JOURNAL_CAISSE (
UID UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
NUMERO  INTEGER NOT NULL,
TYPE_MOUVEMENT  INTEGER NOT NULL,
DATEHEURE   TIMESTAMP DEFAULT 'NOW' NOT NULL,
MAGASIN UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
CAISSE  UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
PERIODE INTEGER NOT NULL,
ANNULE  BOOLEAN DEFAULT false NOT NULL,
QUANTITEINTEGER DEFAULT 1 NOT NULL,
TYPE_REGLEMENT  VARCHAR(30),
DEVISE  CHAR(12),
VALEUR  DECIMAL(10,4) DEFAULT 0.00 NOT NULL
);

With these constraints/indices :

ALTER TABLE JOURNAL_CAISSE
  ADD CONSTRAINT JOURNAL_CAISSE_PK
  PRIMARY KEY (UID);

ALTER TABLE JOURNAL_CAISSE
  ADD CONSTRAINT JOURNAL_CAISSE_PERIODE_FK
  FOREIGN KEY (PERIODE)
  REFERENCES PERIODES (ID)
  ON UPDATE CASCADE;

ALTER TABLE JOURNAL_CAISSE
  ADD CONSTRAINT JOURNAL_CAISSE_TYPES_FK
  FOREIGN KEY (TYPE_MOUVEMENT)
  REFERENCES TYPES_MVT_JOURNAL (TYPE_ID)
  ON UPDATE CASCADE;

CREATE INDEX JOURNAL_CAISSE_CAISSE ON JOURNAL_CAISSE (
  TYPE_MOUVEMENT, CAISSE, DATEHEURE
);

CREATE INDEX JOURNAL_CAISSE_IDX ON JOURNAL_CAISSE (
  PERIODE, MAGASIN, ZONE, CAISSE, NUMERO
);

I can't find a way to index this table properly so that this query can run
quickly (for now it takes around 45s :

select
  cast(2 as Integer) as TYPE_MVT,
  TYPE_REGLEMENT, DEVISE, null as BANQUE,
  sum(QUANTITE) as NOMBRE,
  sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3))
  and (ANNULE is false)
  and (
   (magasin=x'825469DD62BB4583B9F75C93E85FD4BF')
or (magasin=x'27917E1E9E99469ABB3B6B82075AD52A')
or (magasin=x'4AFB0F29F91644E8AC15B980FC4A17CF')
or (magasin=x'872CA352AD09459694F88FE51324CB35')
or (magasin=x'A2C3F9F5D1B1404293589068A8297EC0')
or (magasin=x'3050631347F04776BA6C08E10F164D76')
or (magasin=x'D277CB68C3154EACBC97B4BF95CC5B8F')
or (magasin=x'CA20DC918C9145FDA797043DCF477EA5')
or (magasin=x'79E0F6FE896A41F99D44D219A63CB511')
or (magasin=x'82717E0DA00D4DE2B82415A03F72846F')
or (magasin=x'189886B18FCD4998BEC33297ABF79E47')
or (magasin=x'376C73922868406CBD39540D47C7C1A7')
or (magasin=x'54C4FC9317724996989DC1C2BCA800CC')
or (magasin=x'C92CE52C9FFE48EA8B510888A0BD419C')
or (magasin=x'4B70E2D2232D47A6A74CE5C20ECDC941')
or (magasin=x'C893800B9A2E4E42BC34C47DEFF7135A')
or (magasin=x'87DA11B6C9724B47808B89E7338E9DFE')
  )
  and (
(caisse=x'0C949D9560234EE7B4824ABB93DA2386')
 or (caisse=x'0F84C9F9A1E54C9C83AA1CEC3D337E9A')
 or (caisse=x'19CFC91E81444B538640B4ED208D6AAC')
 or (caisse=x'1B80E288A79C4569BEFE46D1FBD116B4')
 or (caisse=x'22B06AC867AA4C87B813C78992105569')
 or (caisse=x'29261F9D1B784723B7B474F056CE140C')
 or (caisse=x'2AD2EEB3020641B28F53F03216169E36')
 or (caisse=x'2BEB18A58A6F4EE19DA256B7E5A2C35E')
 or (caisse=x'2D19A90D25AC4019B708D7A133A0FBE5')
 or (caisse=x'2D7735DB379948A6A023C343A04E049C')
 or (caisse=x'34402C4342474DBB8CB0446A09FEA15A')
 or (caisse=x'3825FBA5A4B348EE9F604B2A2C994061')
 or (caisse=x'396CF8284423444BA2509393BEDFD3B4')
 or (caisse=x'3A3B742C2D394F589AA5EABAD0733B84')
 or (caisse=x'3D53981F65E94100AE3779017C23F8A2')
 or (caisse=x'448C6D67543A45859D7BAD5778D30DD9')
 or (caisse=x'5996EAA0B442440C9C563BC4399F317C')
 or (caisse=x'5A98039B179643238250E1BEB73F8FD6')
 or (caisse=x'5F1357BFE2E64D309FC6889C07348A8A')
 or (caisse=x'6D33F33594864CF2B32487797AC5D8A8')
 or (caisse=x'6FF591B1D10C4A4C8C0904B629C42731')
 or (caisse=x'70ABF3DAC8774484A172CBC8B65A98B4')
 or (caisse=x'796C49B2D52B465A95D5B5D04EB43CEC')
 or (caisse=x'7B729EC0919643DF8197407535B27DAF')
 or (caisse=x'7F00F7E2FCD64946BF6C3C1DAE1D5716')
 or (caisse=x'8579CAD9D7DC4C61B172F10D4E748891')
 or (caisse=x'86F8AE15F55D40AF9E9C2D74B73C58FB')
 or (caisse=x'8A63C3B11F774DC3AD599DE880FE5770')
 or (caisse=x'8CFA2F21D96D49E3857E302542535C7A')
 or (caisse=x'90495B8778D6482FA11D4BCA97629FDD')
 or (caisse=x'909D44F58DF24077B51E3BB493E50C42')
 or (caisse=x'9166B3D7319741669AB7DD434645426D')
 or (caisse=x'936537DC96694885A6F7E661D3490C67')
 or (caisse=x'9374E2AF14D749A2A2DAE63918A51D05')
 or (caisse=x'985690F55F4244DAAED2063DB9A9EF08')
 or (caisse=x'9AD383E3E6E842668481AFB3069C3812')
 or (caisse=x'9CAA834580EA4B209DE4FBEC223AFE05')
 or (caisse=x'A1CB7A2BB3B6465DB167216298EB45FD')
 or (caisse=x'A42802A9027F4D8ABFDD4EBAEDC84A16')
 or (caisse=x'ABE44B53C5454F12AE94925DEBB084F5')
 or (caisse=x'AD068CF02DE54701AE2D572821B4F003')
 or (caisse=x'B1B535D246554BA28EAC2F0BA6C50A6A')
 or (caisse=x'B457957BFC55458C99B65C0471DC3CE6')
 or (caisse=x'BBC68C6092924E7FB107DAA026B96BE6')
 or (caisse=x'C29FE4826F014120BD451692052AA3ED')
 or

Re: [firebird-support] Re: Query crash in Firebird 3.0.3

2018-02-08 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Done : http://tracker.firebirdsql.org/browse/CORE-5743

On Thu, Feb 8, 2018 at 5:46 PM, hv...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> > Is it as expected ?
>
>   No, please register it at tracker.
>
> Regards,
> Vlad
>
> 
>

[firebird-support] Query crash in Firebird 3.0.3

2018-02-08 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Hello,

This query was known to work in Firebird 3.0.2 :

select
  2 as TYPE_MVT,
  TYPE_REGLEMENT,
  DEVISE,
  null as BANQUE
  sum(QUANTITE) as NOMBRE,
  sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3)) and (ANNULE is false)
  and (periode = ?)
group by 1,2,3,4

Now,, in Firebird 3.0.3 I get :


*Overflow occurred during data type conversion.conversion error from string
"2".*

I have to cast "2" as Integer to make it work again.

select
  cast(2 as Integer) as TYPE_MVT,
  TYPE_REGLEMENT,
  DEVISE,
  null as BANQUE
  sum(QUANTITE) as NOMBRE,
  sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3)) and (ANNULE is false)
  and (periode = ?)
group by 1,2,3,4

Is it as expected ?

Regards,

-- 
Pierre Yager
Crisalid

[firebird-support] Cannot grant user by code

2017-10-02 Thread 'Pierre Y.' pierr...@gmail.com [firebird-support] 
Hi,

I want to add readonly user creation/granting after database initialisation.

I create the database using a bunch of SQL scripts. After the last script
runs, I create a user using "create user MYUSER password 'myPassword'" the
I grant this user read only (select, references) access to every table of
the database using EXECUTE BLOCK :

https://gist.github.com/zedalaye/6300b679e56349c40b973d35bb4c91ee

On Transaction Commit, I get this error :

add record error violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on
table "PLG$USERS" Problematic key value is ("PLG$USER_NAME" = 'RO_USER')
Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements GDS Code: 336723987 - SQL
Code: -901 - Error Code: 19.

I use latest Firebird 3.0.2.32703, Delphi XE2 and UIB from Git Repository.
The database is created in a Linux instance hosted by a VM on the same
machine using inet4 connexion with SYSDBA user.

Many thanks,

-- Pierre Yager