Firebird - field uniqueness and continuity- HELP ME lazarus / 16-Databases ---------------------------------------------------------------------- abmservice · 2010-04-21 08:17 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 · 2010-04-21 08:31 „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 · 2010-04-21 09:10 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 · 2010-04-21 09:18 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 · 2010-04-21 09:43 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 · 2010-04-21 10:59 As for the numbering of documents-invoices where there may be a break in the numbering - maybe some other solution? ---------------------------------------------------------------------- JD · 2010-04-21 12:01 Quote from: abmservice on April 21, 2010, 11:10:37 amautoinc 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. ---------------------------------------------------------------------- abmservice · 2010-04-21 16:52 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 · 2010-04-21 17:26 Quote from: abmservice on April 21, 2010, 06:52:02 pmProblem 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 · 2010-04-21 17:59 Quote from: Zoran on April 21, 2010, 07:26:53 pmI 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 · 2010-04-21 18:30 Quote from: zeljko on April 21, 2010, 07:59:46 pmQuote from: Zoran on April 21, 2010, 07:26:53 pmI 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 · 2010-04-21 20:35 Quote from: zeljko on April 21, 2010, 07:59:46 pmQuote from: Zoran on April 21, 2010, 07:26:53 pmI 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...... What's your take on this subject? ---------------------------------------------------------------------- abmservice · 2010-04-22 02:18 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 ---------------------------------------------------------------------- Zoran · 2010-04-22 05:56 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: Quote from: Zoran on April 21, 2010, 07:26:53 pmAnyway, 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 · 2010-04-22 08:24 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. ---------------------------------------------------------------------- abmservice · 2010-04-22 08:31 table has a primary key and the tables are related, this field is "licznik" is an additional number of the document ---------------------------------------------------------------------- abmservice · 2010-04-22 08:36 I'll do a test - will use the test data generator and at the same time, I'll put your details - we'll see what the reaction will be. I know that somehow it can do because there are many applications a client - server and documents are numbered ---------------------------------------------------------------------- Zoran · 2010-04-22 09:01 Quote from: abmservice on April 22, 2010, 10:31:59 amtable has a primary key and the tables are related, this field is "licznik" is an additional number of the document Yes, but you want it to be unique and without gaps, don't you? So it does not matter if it is PK or not. ---------------------------------------------------------------------- Zoran · 2010-04-22 09:09 Quote from: abmservice on April 22, 2010, 10:36:40 amI'll do a test - will use the test data generator and at the same time, I'll put your details - we'll see what the reaction will be. I know that somehow it can do because there are many applications a client - server and documents are numbered I beleive that most of those applications actually allow gaps in numbering, but they can do work for a long time without gaps to actually happen, esspecially if they don't serve many concurent connections working on same data. ---------------------------------------------------------------------- JD · 2010-04-22 11:06 @Zelkjo I'm still waiting to see your serialized transactions example. I've never tried it that way & I'm keen to see how it works. ---------------------------------------------------------------------- zeljko · 2010-04-22 16:34 Quote from: JD on April 22, 2010, 01:06:40 pm@Zelkjo I'm still waiting to see your serialized transactions example. I've never tried it that way & I'm keen to see how it works. @JD http://www.postgresql.org/docs/8.2/interactive/transaction- iso.html ---------------------------------------------------------------------- guidoaerts · 2010-04-22 18:32 taking a glance at this thread, I want to make this remark you can use a trigger and generator to have a unique identifier for your record, but not at the same time for your document. for your record, you will have to accept gaps. for your document, define another generator specific to your document, and coherent with your new record; that will garantuee you consistent documentnumbers grts, guido ---------------------------------------------------------------------- abmservice · 2010-04-23 15:39 I went in the basket of solutions: ( I read the suggestion test. Option 2 is a record in a transaction if it is approved by downloading the update number generator. ---------------------------------------------------------------------- fred_55 · 2010-04-26 07:41 You can read the article on the IBObjects - An Auditable Series of Numbers: http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries