Lazarus

Free Pascal => Database => Topic started by: devEric69 on July 23, 2021, 12:52:50 pm

Title: [Solved] Firebird 3 \ IBX : how to restart native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 12:52:50 pm
Hello,

I have a firebird \ IBX (maybe) question: I've migrated a firebird 2.5 database towards 3.
In the process, I got rid of the SQL objects like generators, sequences, and triggers in the beforeInsert of each tables, i.e. i now use auto-incremented fields. It works well.

Now, as a result of my trials, I am using auto-incremented fields (ID) that are worth 45, 16, ... I would like to have a "clean" demo database, with only 3 records in each table, and that the next ID is 4 (for info., the SQL sequences + generators objects were allowed to be reinitialized, reset). So I've deleted all the records in order to leave only the first 3 records in each table. Unfortunately, when i ask to create new records, I have new IDs that are 46, 17, ... and not 4, 4, 4, ...

I've made a backup of the database with only 3 records in each table, then restored the database from this *.gbk: it doesn't change anything, i.e. the following IDs are still 47, 18, ...

So, does anyone know how to do this: reset auto-incremented IDs fields, or compress \ compact (as close to the "MS-Access way" - IIRC; i think so, but I'm not sure anymore - if possible) auto-incremented IDs fields, or remake a new database without records where each new record would have an ID=1 (at worst)? Which method is easier to use (with firebird tools), or to implement in Lazarus (with IBX)?

Regards.
Title: Re: Firebird \ IBX : how compress \ compact native auto-incremented IDs fields?
Post by: egsuh on July 23, 2021, 01:29:46 pm
I do not know how to set id values.
To create new clean tables, you can download DDL of all the definitions --- including custom defined generators, and run them --- not sure whether this would renew the auto-increment ids, but I guess it will.
Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 03:32:32 pm
Yes, this's what i suspect: export everything (DDL + data) in SQL script, change the keys manually and recreate the database via the script. So there is nothing magical about it.

Otherwise, i display the key-IDs columns to be able to sort by group in the association tables (by foreign key groups). But as the "holes" of numbering bother me (this is just a "mania", without much interest), i will just add a functional option: the possibility of coloring the columns ID and their fonts in grey, so that they cannot be read explicitly.

Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: dsiders on July 23, 2021, 04:02:29 pm
...
So, does anyone know how to do this: reset auto-incremented IDs fields, or compress \ compact (as close to the "MS-Access way", if possible) auto-incremented IDs fields, or remake a new database without records where each new record would have an ID=1 (at worst)? Which method is easier to use (with firebird tools), or to implement in Lazarus (with IBX)?

You can use DDL to change the identity columns:

Code: MySQL  [Select][+][-]
  1. ALTER TABLE ... ALTER COLUMN ... (RESTART WITH <n>)

It's up to you to make sure the new start value doesn't already exist in the table.

If you want a clean table, just re-create it and load the data again. It's not going to stay that way though, so obsessing over the "holes" is pointless.

If by compress / compact you mean changing the identity values for existing rows... that would be one more reason why I won't use MS Access. ;)

Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 04:27:42 pm
Thanks for the tip @dsiders: i've internal knowledge gaps (DDL - SQL, inner services, ...) specific to Firebird.
:-[
Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 04:59:11 pm
For info., found on Firebird's site: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-ddl-identity (https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-ddl-identity) .
Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 05:17:57 pm
nevertheless, this DDL statement ( "ALTER TABLE ... ALTER COLUMN ... (RESTART WITH <n>);" ) does nothing on the native IDentity fields, without external SQL generator. I will create an issue \ bug report at Firebird's gitHub, to see \ ask if this is normal.
Title: Re: Firebird \ IBX : how compress, compact native auto-incremented IDs fields?
Post by: devEric69 on July 23, 2021, 06:16:36 pm
Well, finally, it works very well with Firebird's native auto-incremented fields. The DDL syntax has no parenthesis ( https://github.com/FirebirdSQL/firebird/issues/6906 (https://github.com/FirebirdSQL/firebird/issues/6906) ). For example, if there are only 3 records left in a very emptied table, and i want the next num#-ID to be 4 (more seriously, this can be useful to reset things at the beginning of the year, for example, like resetting the ID-counter of an INVOICES table to 1, ...):

Code: MySQL  [Select][+][-]
  1. ALTER TABLE MY_TABLE ALTER COLUMN MY_IDENTITY_COLUMN_ID RESTART WITH 3;
Title: Re: [Solved] Firebird 3 \ IBX : how restart native auto-incremented IDs fields?
Post by: egsuh on July 24, 2021, 03:21:58 am
Quote
export everything (DDL + data) in SQL script, change the keys manually and recreate the database via the script.

I thought creating database first with DDL without data, and insert data without the key field. Will this work? It's not easy to move data from one database to another anyway.
TinyPortal © 2005-2018