[solved] beginners question about dropping and generators
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-25 11:54

As the helpful guys here know, I have a problem with my database.
And I let my FlameRobin write the DDL and found such lines:


Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
/****************** SEQUENCES ********************/ ...........CREATE
SEQUENCE GEN_TBSCAN_ID ;............
what is this?
There was a table tbscan, but I dropped it.

Then I checked generators in the tree view from FlameRobin.
Some generators would serve tables, which are dropped long ago.

Can I just drop the generators?
Must I do anything else?
Will this "create sequence" disappear from it?
* sorry, I have that little idea about databases. *

----------------------------------------------------------------------
Zvoni · 2024-01-25 15:59

In a nutshell: such a Sequence (or in Firebird a Generator) is what
provides you your "Auto-ID" in case you use surrogate Primary Keys
(see other Discusssions) when you add a new Record in a table

That said: If i read it right, those are valid for Firebird 2.5 and
earlier

Firebird3 and later use something like

create table t1 (
   id integer generated by default as identity primary key
)

So if you have moved your DB to FireBird3 or later, AND have adjusted
your Create Table-Statements to use above syntax, you can drop the
sequences-thingies AND its corresponding Trigger(s)

----------------------------------------------------------------------
rvk · 2024-01-25 16:58

Only disadvantage about "generated by default as identity primary key"
is that I can't generate my own ID.
You NEED to do INSERT RETURNING ID everywhere and use generated ID to
make child records if you add them in one go.
(Now I generate a new number via GEN_ID and use that and because it's
outside a transaction it will always work correctly)

But if you program with the RETURNING ID in mind, then it can be
useful.

Quote from: Nicole on January 25, 2024, 12:54:10 pmThere was a table
tbscan, but I dropped it.Then you probably also dropped any TRIGGER
that was attached to it?
And if you did, the SEQUENCE can also be removed.

Quote from: Nicole on January 25, 2024, 12:54:10 pmThen I checked
generators in the tree view from FlameRobin.
Some generators would serve tables, which are dropped long ago.
That's why it's smart to make a copy of your DLL (via FlameRobin) and
keep that up to date.
You can search it easily and find all the weird connections.

Also... you had some snippets like this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i GRANT
DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBKONTRAKTE TO  SYSDBA
WITH GRANT OPTION GRANTED BY SYSDBA;You wouldn't normally use that
because SYSDBA is already owner of the complete database and has all
rights. You would only need that for non SYSDBA users (if you have
any, which you probably don't).

I have a copy of the DLL in a text file which is added as resource in
my program. When a new users wants a new database I can just execute
that DLL and have a clean empty database. After that you can add
default records (also maybe via textfile) or pump data from an older
database to the newer one.

(When pumping data you need to make sure you do the order correctly.
First the master tables with no foreign keys, then the tables from any
foreign keys and finally all the child tables.)