[solved]  finding inactive indices / understanding DLL
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-25 19:30

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 CREATE
TABLE TBKENNZAHLEN(  ID_WARE smallint NOT NULL,  COMM char(2),......  
CONSTRAINT PK_ID_WARE PRIMARY KEY (ID_WARE)  USING INDEX
IDX_PK_ID_KENNZAHLEN,  CONSTRAINT UNQ1_WARE UNIQUE (COMM));
I cannot remember how and why I made the table in this way.
I would not do it again like this. I was young.... ;-)
Unfortunately I used this table very, very often in many SQL
statements and in my code.
So every change means several days of work, what I want to avoid.

Today I know:
The only thing important is, that the field COMM is unique.

the line
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   USING
INDEX IDX_PK_ID_KENNZAHLEN,
does not say anything to me.

So my question:
Do you know, what this very line means and what do do with it?
Who has not read the other threads: My problem are 3 inactive indices.

----------------------------------------------------------------------
rvk · 2024-01-26 14:36

Quote from: Nicole on January 25, 2024, 08:30:31 pmthe line
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   USING
INDEX IDX_PK_ID_KENNZAHLEN,
does not say anything to me.

Wow, I never used or seen that USING INDEX either in a CREATE TABLE 

https://firebirdsql.org/refdocs/langrefupd20-alter-
table.html#langrefupd20-at-using-index

It states that it is to provide a user-defined for the automatically
created index.
And indeed, the primary key is named PD_ID_WARE while the index for it
is called IDX_PK_ID_KENNZAHLEN.
It's only useful if you want ALL your indexes to have a name like IDX_
in front of it.
The primary key would normally have PK_ and if you would omit the
USING INDEX you would also get a PK_ID_WARE index (which was never a
problem for me).

Quote from: Nicole on January 25, 2024, 08:30:31 pmWho has not read
the other threads: My problem are 3 inactive indices.
Did you fix ALL the 3's in your indexes table? Did you activate ALL
the indexes and constraints?
There were A LOT more than 3 that were inactive in your screenshot
!!!!

And if you did, did you try a backup/recovery cycle yet on the
database just to see if everything is correct?

If you didn't do above things, the database is still slow and it's
just a mater of time it happens again.

----------------------------------------------------------------------
Nicole · 2024-01-26 19:11

I made a new database with the fields of the old table and all values
for inactive indices are zero now.

Unfortunately I do not know, how to get my old data into the new
database.
Once upon a time there was something running of Firebird, which
hindered me to restore the backup into the main database.
This thing I switched off to do so. And now I have no idea, how to get
it to run again.

I bet, if this very thing runs, the old and the new database would
speak to each other.
At the moment I see an error message "unknown table" if I try to
address both my databases.

----------------------------------------------------------------------
rvk · 2024-01-26 19:21

Quote from: Nicole on January 26, 2024, 08:11:40 pmAt the moment I see
an error message "unknown table" if I try to address both my
databases.
You can't put both databases in one SQL. Period.

You either need to create an app which has two tibdatabase connections
and do a select on one and insert on the other.

But because this is a one time action you could just extract all the
insert from one database and execute them all at the same time in the
new database (just a copy paste action in FlameRobin).

Do any of your tables have BLOBs. Or is it all integer, decimals and
char fields?

If the new database is utf8 and the old iso you need to keep an eye on
the records with special characters. But via copy paste they might get
translated properly (bit I'm not sure).

You could also try the fbcopy I mentioned earlier.

But just doing the copy paste of the INSERTs might be easiest.

----------------------------------------------------------------------
Nicole · 2024-01-27 08:17

no BLOBs.
Yesterday I rewrote all my triggers and generators and put them into
the new DB as SQL. Worked fine.
I was surprised how many strange things I found on re-reading the
printed SQL. (e.g. one huuuge field with a BingInt which just needs
Integer.)

And only one field - I keep an eye on - which will change character
types. I cross fingers for it. It will not spoil the function of my
app, but disturb my eye if the letters look strange. I mostly wrote
English text in in, so the problem will hopefully be not that huge.

This "copy" you mentioned, I opened it in a tab and on the end of the
day I hope to find it again. I have opened so many tabs in the
meanwhile solving questions of my migration here not mentioned.

What the guys told me as syntax insert between databases: * from
db1.mytable select * from db.mytabe
(correct syntax is saved)
will only working from inside Laz?

Not from FlameRobin?
I can open there both dbs fine.
No, - interaction did not work up to now.

I will need to migrate by SQL. Too many details changed in my tables
for a stupid copy.

PS: Found the FBcopy-Tab, this looks promising.

----------------------------------------------------------------------
rvk · 2024-01-27 15:48

I'm not sure if opening all those topics is wise... They are all about
the same subject essentially.

Quote from: Nicole on January 27, 2024, 09:17:23 amI will need to
migrate by SQL. Too many details changed in my tables for a stupid
copy.
If the tables itself changed you could have a hard time with ready
made copy programs like fbcopy.

Your best bet would be then to just extract the inserts and change
them in an editor (notepad++) with search and replace. After that you
can copy the first few lines into a Execute SQL window in FlameRobin
to see if it works. If it works you can do the rest.

You do need to learn about the SQL window in FlameRobin and have
understanding of the transaction buttons there.
Open a table with "Browse data"
Press menu Grid > Fetch all data
Press Ctrl+A to select all
Press menu copy as Insert statements
Paste them in notepad++ and see if they are all there

Take the first few
Right click a database and choose execute sql statement
Paste the first few lines in
Press F4 or the button Execute statements
If there is no error DON'T forget to press F5 or the button Commit
transaction

If you have another view open for SELECT * FROM TABLE you need to
realize that view is in its own transaction.
That view doesn't see ANYTHING from other transaction until you commit
or cancel the transaction in that view.
So if you have a SELECT open and do an INSERT in another view, you
need to press the Cancel or Commit (green V or red X) of that SELECT
screen and press Run again before you can see the changes made in the
other screen. (I hope I explained this clearly enough.)

I'll check in a bit if you can declare an external datasource in
FlameRobin. I'm not sure if it's possible, but if it is, you could do
the SELECT INTO from one table to another. But you need to check if
that also goes correctly if you have different character sets (special
characters).

----------------------------------------------------------------------
rvk · 2024-01-27 17:55

I tried the ON EXTERNAL DATA SOURCE option of Firebird in FlameRobin
but it's really limited.

I can do something like this and that works:
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 SET TERM
#;EXECUTE BLOCK RETURNS (LINK BIGINT) ASBEGIN  FOR EXECUTE STATEMENT
'select LINKNUMMER from RELATIE'       ON EXTERNAL DATA SOURCE
'localhost:c:\dblocation\test.fdb' AS USER 'sysdba' PASSWORD
'masterkey'  INTO :link  DO SUSPEND;END
But you can't directly SELECT all fields automatically into a table in
the local database.
You would need to SELECT all fields in the correct variabele and after
that command, INSERT those fields in your local table.
And that for every table you want to copy.

It would be much easier to do with the actual INSERT statements or via
code in Lazarus with two DB connections.

BTW. You said that you tried it with two connection and it didn't
work.
(I'm not sure what topic because there are so many now)
That should have worked so if you want to go that route you need to
elaborate with code and exact error messages (I don't think you did
that there).

----------------------------------------------------------------------
Zvoni · 2024-01-29 08:09

Quote from: Nicole on January 25, 2024, 08:30:31 pmCode: MySQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBKENNZAHLEN(  ID_WARE smallint NOT NULL,  COMM char(2),......  
CONSTRAINT PK_ID_WARE PRIMARY KEY (ID_WARE)  USING INDEX
IDX_PK_ID_KENNZAHLEN,  CONSTRAINT UNQ1_WARE UNIQUE (COMM));
I cannot remember how and why I made the table in this way.
I would not do it again like this. I was young.... ;-)
Unfortunately I used this table very, very often in many SQL
statements and in my code.
So every change means several days of work, what I want to avoid.

Today I know:
The only thing important is, that the field COMM is unique.

the line
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   USING
INDEX IDX_PK_ID_KENNZAHLEN,
does not say anything to me.

So my question:
Do you know, what this very line means and what do do with it?
Who has not read the other threads: My problem are 3 inactive indices.
It's probably confusing because you wrote it on two lines
Your line 7 (where you define the primary Key), there is no closing
comma at that line, meaning the following line belongs to it.

If you write it in one line it becomes clearer
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  CONSTRAINT
PK_ID_WARE PRIMARY KEY (ID_WARE) USING INDEX IDX_PK_ID_KENNZAHLEN,
Translated: "Create a Constraint named PK_ID_WARE for the Primary Key
on Field ID_WARE and CREATE the Index IDX_PK_ID_KENNZAHLEN to enforce
that Constraint"
https://firebirdsql.org/refdocs/langrefupd15-create-table.html
QuoteDescription: A USING INDEX subclause can be placed at the end of
a primary, unique or foreign key definition. Its purpose is to

    provide a user-defined name for the automatically created index
that enforces the constraint, and

    optionally define the index to be ascending or descending (the
default being ascending).

NotaBene: It makes no Sense to create a Constraint on a Primary Key,
except if you use "natural" Primary Keys.
If you use Auto-ID Primary Keys then you don't need a Constraint

----------------------------------------------------------------------
rvk · 2024-01-29 08:57

Quote from: Zvoni on January 29, 2024, 09:09:35 amNotaBene: It makes
no Sense to create a Constraint on a Primary Key, except if you use
"natural" Primary Keys.
If you use Auto-ID Primary Keys then you don't need a Constraint
Maybe that's because when you ADD a PRIMARY KEY after the creation of
the table you do it with an
ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_KEYFIELD PRIMARY KEY
(KEYFIELD)^
Even if KEYFIELD is a generated key.

(So maybe the CONSTAINT PK PRIMARY was later added from outside the
CREATE to inside, where it could have been added to the KEY line.)

I have it like that (split) because I also have an import phase where
I need to add missing records for certain data (for foreign keus)
before adding the constraints and primary keys.

BTW. It doesn't matter if you put the primary key at the end like that
with CONSTAINT. The effect is the same. There is NO extra CONSTAINT
added. Just the CONSTAINT for primary key (which is exactly the same
as if PRIMARY KEY was added after the primary key field).

----------------------------------------------------------------------
Zvoni · 2024-01-29 09:00

Quote from: rvk on January 29, 2024, 09:57:33 amQuote from: Zvoni on
January 29, 2024, 09:09:35 amNotaBene: It makes no Sense to create a
Constraint on a Primary Key, except if you use "natural" Primary Keys.
If you use Auto-ID Primary Keys then you don't need a Constraint
Maybe that's because when you ADD a PRIMARY KEY after the creation of
the table you do it with an
ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_KEYFIELD PRIMARY KEY
(KEYFIELD)^
Even if KEYFIELD is a generated key.

(So maybe the CONSTAINT PK PRIMARY was later added from outside the
CREATE to inside, where it could have been added to the KEY line.)

I have it like that (split) because I also have an import phase where
I need to add missing records for certain data (for foreign keus)
before adding the constraints and primary keys.

BTW. It doesn't matter if you put the primary key at the end like that
with CONSTAINT. The effect is the same. There is NO extra CONSTAINT
added. Just the CONSTAINT for primary key (which is exactly the same
as if PRIMARY KEY was added after the primary key field).
Ahh....OK..... That confused me (i'm not familiar with Firebird),
since if you declare a Field as a PK, i know that there is at least a
UNIQUE-Constraint hidden somewhere

----------------------------------------------------------------------
rvk · 2024-01-29 09:09

Quote from: Zvoni on January 29, 2024, 10:00:38 amAhh....OK..... That
confused me (i'm not familiar with Firebird), since if you declare a
Field as a PK, i know that there is at least a UNIQUE-Constraint
hidden somewhere
You don't need to declare the PK directly after the field. In Firebird
this can be done both after the field, at the end in the CREATE TABLE
and even outside the CREATE in an ALTER TABLE. And it creates the PK
as a (UNIQUE) CONSTRAINT.

On this page there is also the following example (with the pk
constraint at the end):
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 CREATE
TABLE people (  id INT NOT NULL,  nickname VARCHAR(12) NOT NULL, 
country CHAR(4),  ..  ..  CONSTRAINT pk_people PRIMARY KEY (id), 
CONSTRAINT uk_nickname UNIQUE (nickname) USING INDEX
ix_nick)https://firebirdsql.org/refdocs/langrefupd20-create-table.html

That page also stated (with the example of constraint after the
field):
QuoteThe first example creates a primary key constraint PK_CUST using
an index named IX_CUSTNO:

Code: [Select]create table customers (
  custno int not null constraint pk_cust primary key using index
ix_custno,
  ...So you see that they call it "primary key constraint" (stating
that primary key is a constraint).
But I've never needed the use of "using index" because the naming is
all done automatically anyway.

QuoteFive types of constraints can be specified. They are:
Primary key (PRIMARY KEY)Unique key (UNIQUE)Foreign key
(REFERENCES)CHECK constraint (CHECK)NOT NULL constraint (NOT NULL)