Recent

Author Topic: Firebird - field uniqueness and continuity- HELP ME  (Read 14435 times)

abmservice

  • Jr. Member
  • **
  • Posts: 65
Firebird - field uniqueness and continuity- HELP ME
« on: April 21, 2010, 10:17:11 am »
in the database have a field numbering FIREBIRD (financial documents). Field but must retain the uniqueness and continuity of numbering. How to Solve It - Firebird server-side. Database work for many customers. Please forward suggestions and examples

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #1 on: April 21, 2010, 10:31:03 am »
„Autoinc“ functionality in Firebird is achieved by setting up a trigger and a generator, as explained here: http://www.firebirdfaq.org/faq29

If you use FlameRobin, you can do it quickly this way: In the main tree view, expand the table with this field, then right-click on the field's node and chose ShowProperties. In FieldProperties dialog check both "create new generator" and "create trigger".

abmservice

  • Jr. Member
  • **
  • Posts: 65
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #2 on: April 21, 2010, 11:10:37 am »
autoinc know. What if I add a record and I will remove it for a moment or a transaction is not approved. then already have the jump in numbers. Any suggestions?

abmservice

  • Jr. Member
  • **
  • Posts: 65
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #3 on: April 21, 2010, 11:18:52 am »
I checked that if for some reason does not approve this transaction autoinc generator will increase by 1
select max (fields) from table 1??

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #4 on: April 21, 2010, 11:43:24 am »
Ah, now I understand, you don't want gaps in numbering...
You can't get it if your database serves many concurent connections, as it is not known when two or more transactions starts how many will be actually commited, so gaps can happen.

I admit that I don't know how to do it. I can't help you, sorry.

I am even afraid that there is no solution, otherwise the database developers would provide a way to do it... And, as far as I know, such functionality does not exist in any RDBMS I know about.

What is the problem with gaps? What do you need to achieve? Maybe it can be achived some other way. If you need the current record count, you should use count(*) -- I know it can be time consuming in large tables...

abmservice

  • Jr. Member
  • **
  • Posts: 65
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #5 on: April 21, 2010, 12:59:10 pm »
As for the numbering of documents-invoices where there may be a break in the numbering - maybe some other solution?

JD

  • Hero Member
  • *****
  • Posts: 1755
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #6 on: April 21, 2010, 02:01:25 pm »
autoinc know. What if I add a record and I will remove it for a moment or a transaction is not approved. then already have the jump in numbers. Any suggestions?

METHOD A:
Autoincrementing using a trigger & a generator is quite safe & is recommended for concurrent transactions.

What you want to achieve can be done by changing the last used value of the generator if you really want continuous numbers BUT IT IS NOT RECOMMENDED & IS VERY DANGEROUS. Another way of doing it is to recreate the table from scratch using an SQL script with records having continuous numbers & then resetting the generator to the last used number.

However I fail to see the advantage of doing this because if the table has heavy transaction traffic, you will actually slow down transaction processing & at worst may just corrupt the database entirely. I  DON'T ADVISE THAT YOU DO THIS. It is best to let Firebird handle the assigning of numbers to records.

METHOD B:
You can create a table which contains the last used number. If your transaction in the main table is completed, retrieve the last used number from the number table, increase it by 1 AND then update both the main table record & the number table value. This can be done using triggers & stored procedures.

You'll find examples of this type of usage in the old Paradox apps for Delphi 7.

The risk here is you may still have exceptions where two main table records have the same number because the updating was not done fast enough.

So my final advice is:
use a trigger & a generator to handle the numbering & let Firebird handle it as it sees fit. You'll save yourself HUGE headaches in future believe me.  :D

« Last Edit: April 21, 2010, 02:05:29 pm by JD »
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

abmservice

  • Jr. Member
  • **
  • Posts: 65
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #7 on: April 21, 2010, 06:52:02 pm »
Problem solved, at least I think so:)
I wrote a trigger:)
I used the select max (number) from table and acts as I delete the record and add it again later I have continuity. the master key is a gap in the numbering so probably will work:)
Well, I practice:) If you will be ok I'll put the code for the other may be helpful:)f course if anyone will want to:)

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #8 on: April 21, 2010, 07:26:53 pm »
Problem solved, at least I think so:)
I wrote a trigger:)
I used the select max (number) from table and acts as I delete the record and add it again later I have continuity. the master key is a gap in the numbering so probably will work:)
Well, I practice:) If you will be ok I'll put the code for the other may be helpful:)f course if anyone will want to:)

I'm not sure if it is good aproach. If you start transaction, get max(number), then another user starts another transaction, gets the same max(number) and after these two transactions are commited, you end up with duplicate entry.

I would rather listen to JD's advice... There are reasons why, after all these years of rapid RDBMS development, no RDBMS has built-in mechanism for this. If they could implement it safely, they would surely have done it.

Anyway, if you want to use max(number) for this, create descending index on this field, or you can get perfomance issues as your table grows (read why: http://www.firebirdfaq.org/faq205).

zeljko

  • Hero Member
  • *****
  • Posts: 1050
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #9 on: April 21, 2010, 07:59:46 pm »
I would rather listen to JD's advice... There are reasons why, after all these years of rapid RDBMS development, no RDBMS has built-in mechanism for this. If they could implement it safely, they would surely have done it.

What's purpose of serialized transactions then ? 

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #10 on: April 21, 2010, 08:30:18 pm »
I would rather listen to JD's advice... There are reasons why, after all these years of rapid RDBMS development, no RDBMS has built-in mechanism for this. If they could implement it safely, they would surely have done it.

What's purpose of serialized transactions then ? 

I guess this is what these are for! :)
:-[

JD

  • Hero Member
  • *****
  • Posts: 1755
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #11 on: April 21, 2010, 10:35:19 pm »
I would rather listen to JD's advice... There are reasons why, after all these years of rapid RDBMS development, no RDBMS has built-in mechanism for this. If they could implement it safely, they would surely have done it.

What's purpose of serialized transactions then ?  

Pray elucidate Zeljko......  :D

What's your take on this subject?
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

abmservice

  • Jr. Member
  • **
  • Posts: 65
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #12 on: April 22, 2010, 04:18:52 am »
Well before I wrote a trigger for looking at various forums and such a solution is suggested.
I did it this way:
Quote

SET SQL DIALECT 3;

CREATE GENERATOR GEN_DOKUMENTY_ID;

SET TERM ^ ;
CREATE OR ALTER TRIGGER DOKUMENTY_BI FOR DOKUMENTY
ACTIVE BEFORE INSERT POSITION 0
as
declare variable nr INTEGER:
begin
if (new.licznik is null) then
begin
select max(licznik) from dokumenty
into :nr;
if (nr is null)then
begin
nr=0;
end
end
    new.licznik = (nr+1);
end
^
SET TERM ; ^

whether there is a chance to work?
I'll add that on this box set up a unique index
« Last Edit: April 22, 2010, 04:26:28 am by abmservice »

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #13 on: April 22, 2010, 07:56:10 am »
It will surely work if concurent transactions doesn't try to make an entry. So, see if you can forbid them -- I guess serialized transactions that Željko mentioned should be safe. I don't know if you can enforce them and how to do it.

And, do create descending index on "licznik", for the reason I mention earlier:
Anyway, if you want to use max(number) for this, create descending index on this field, or you can get perfomance issues as your table grows (read why: http://www.firebirdfaq.org/faq205).

Zoran

  • Hero Member
  • *****
  • Posts: 1429
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird - field uniqueness and continuity- HELP ME
« Reply #14 on: April 22, 2010, 10:24:31 am »
Also note that if any row in a table, other than the one that is entered last, is deleted, you will have gaps again.
So, you still can never be sure that there are no gaps, if you allow fisical row removal in this table.