recursief aanpassen van records
Ik werkte in het verleden met tabellen via een prefix. Nu zou ik deze tabellen willen samenvoegen met een id van herkomst of iets dergelijks.
Hoe pak ik dit het beste aan?
Sowieso de kleinste tabellen toevoegen aan de grootste lijkt me het beste. maar de gelinkte id's moeten wel volgen natuurlijk.
Ik kan natuurlijk zelf een conversie doorvoeren via "record per record" en de links verbeteren maar werken via insert from lijkt me toch sneller en efficienter.
Code (php)
1
2
3
4
5
2
3
4
5
#bron w3schools
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
Jan
Gewijzigd op 29/08/2021 19:17:29 door Jan R
Algemeen gesproken is alles in SQL een tabel. Als je twee tabellen samenvoegt tot 1 resultaat met bijvoorbeeld een LEFT JOIN, dan is de uitkomst een resultaattabel. Die kan je weer opslaan in een nieuwe tabel met een INSERT INTO tabel (col1, col2, ...) SELECT ...
Als je ID's recursief zijn maak je gebruik van een recursieve common table expression.
Je ziet dat de tabellen beginnen met trn_ een andere groep begint met tr2_
De id's van alle tabellen beginnen logisch met 1.
Dus in trn_groep, totaal 3 records, heb je id 1 maar ook in tr2_groep. Dus als ik deze samenvoeg wordt het id in trn_groep 4 welkde slechts 1 records heeft.
gevolg alles met verwijzing groepid uit tr2_ moet aangepast worden van 1 naar 4 tijdens de importatie
Dit loopt verder uit met de toernooien ±50 en 3 records welke weer moeten overgenomen worden in de partijen, 3570+96 records, en de spelers, 469+26 records.
In totaal 13 tabellen met hun records
Er zijn geen relaties gelegd via:
Code (php)
1
ALTER TABLE trn_toernooien ADD CONSTRAINT FK_2 FOREIGN KEY (groep) REFERENCES trn_groepen (id);
Die worden slechts in de query's gevraagd.
Jan
Gewijzigd op 30/08/2021 15:44:25 door Jan R
Waarom zijn er geen FK-relaties gelegd, heeft dat te maken met de storage engine ?
Als ik in jouw schoenen stond, en de storage engine mag InnoDB (standaard) zijn, dan zou ik
1.) FK-relaties aanleggen. Dan worden bij een update in meerdere tabellen tegelijk de ID's bijgewerkt, bijvoorbeeld zo:
Code (php)
1
2
2
ALTER TABLE groep ADD CONSTRAINT trn_toernooien_groep_id_fkey
REFERENCES trn_toernooien (id) ON DELETE RESTRICT ON UPDATE CASCADE;
REFERENCES trn_toernooien (id) ON DELETE RESTRICT ON UPDATE CASCADE;
Zie: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
Als je dan het ID zou veranderen, in trn_groepen, dan verandert de ID mee in de tabel trn_toernooien:
Code (php)
1
UPDATE trn_groepen SET id = 1 WHERE ... LIMIT 1;
De kolom id van de tabel trn_groepen zou idealiter een PRIMARY KEY zijn.
2.) De id's van de soortgelijke tabellen op elkaar afstemmen, bijvoorbeeld:
Code (php)
1
UPDATE trn_groepen SET id = id + (SELECT MAX(id) FROM tr2_groepen);
3.) Nieuwe tabellen aanmaken met dezelfde structuur en relaties als de soortgelijke tabellen
4.) De nieuwe tabellen vullen
Code (php)
1
2
3
4
2
3
4
INSERT INTO groepen
SELECT * FROM trn_groepen
UNION ALL SELECT * FROM tr2_groepen
UNION ALL SELECT * FROM ...
SELECT * FROM trn_groepen
UNION ALL SELECT * FROM tr2_groepen
UNION ALL SELECT * FROM ...
5.) De oude tabellen verwijderen
Stel, de grootste tabel bevat minder dan 20.000 records. Je kunt dan 2000 toevoegen voor de ID's in de kleinere tabel:
- 1 wordt 20001
- 2 wordt 20002
- 3 wordt 20003
enzovoort
Ik zou dat zelf even doen met zoeken en vervangen in een export van de SQL-data, zodat je ziet wat je doet, maar je kunt de operatie ook uitvoeren met de stringfunctie CONCAT() in SQL.
resumeren
1. FK relaties aanmaken
2. update table ... set id=id + max_van_andere_tabel voor elke tabel
3. tabellen samenvoegen
PS ik maak geen fk relaties omdat mijn restore-script hier problemen mee heeft. Eerst worden de tabellen verwijderd dan opnieuw aangemaakt en dan opnieuw gevuld.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE IF EXISTS dvd_films;
CREATE TABLE dvd_films (
id int(11) NOT NULL AUTO_INCREMENT,
titel varchar(50) NOT NULL,
subtitels text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=242 DEFAULT CHARSET=utf8;
INSERT INTO dvd_films VALUES("1", "3 FAMILY MOVIES", "");
# en dan nog 241 records natuurlijk
Bij het verwijderen lukt het niet omdat de andere, benodigde, tabel nog bestaat.
Jan
CREATE TABLE dvd_films (
id int(11) NOT NULL AUTO_INCREMENT,
titel varchar(50) NOT NULL,
subtitels text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=242 DEFAULT CHARSET=utf8;
INSERT INTO dvd_films VALUES("1", "3 FAMILY MOVIES", "");
# en dan nog 241 records natuurlijk
Bij het verwijderen lukt het niet omdat de andere, benodigde, tabel nog bestaat.
Jan
Code (php)
1
2
2
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
zowel bij verwijderen als inserten van records in willekeurige volgorde.
Al lijkt me een restore iets wat je normaal op een hele database doet en niet per losse tabel.
Omdat one.com geen backups kent moet ik me behelpen met een script.
Script loopt door de db een maakt een restorescript aan
inhoud restorescript(sql bestand)
1 verwijder tabel
2 maak de tabel opnieuw met de indexen
3 voeg records toe
4 ga naar volgende tabel en keer terug naar punt 1 indien er nog tabellen zijn.
Jan
Het gaat zoals meestal weer over schaken:) maar soms heeft iemand geen tegenstander. In dat geval wordt naar gelang de reden -2,-1 of 0 gebruikt. Deze tegenstanders bestaan niet. Ze worden hardcoded opgevangen als vrijwillig niet spelen, geen tegenstander en afwezig.
Behalve ze in elk toernooi aanmaken is er eventueel een oplossing hiervoor?
Code (php)
1
2
2
ALTER TABLE ldr_partijen ADD CONSTRAINT Fk_143 FOREIGN KEY (wit) REFERENCES ldr_spelers (id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ldr_partijen ADD CONSTRAINT Fk_144 FOREIGN KEY (zwart) REFERENCES ldr_spelers (id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ldr_partijen ADD CONSTRAINT Fk_144 FOREIGN KEY (zwart) REFERENCES ldr_spelers (id) ON DELETE CASCADE ON UPDATE CASCADE;
De 1° lukt dus maar de 2° niet omdat zwart -2 tot 0 kan zijn welke niet bestaan in de database.
Jan
Gewijzigd op 03/09/2021 11:53:29 door Jan R
Aannemende dat 0 de niet-bestaande waarde is, kan je kiezen om 0 toe te voegen in de andere tabel, of 0 te vervangen voor NULL.
Dat is een uitgedachte output om een database mee te restoren. (of alleen de data, of alleen de tabellen/view, evt in- of exclusief procedures etc).
Dat werkt altijd beter dan een eigen script, waarbij je zoals hier tegen FK restricties aanloopt. Die FK-check regel zul je dan ook vaak in de output van mysqldump zien staan.
Ivo P op 03/09/2021 22:06:29:
kun je geen mysqldump gebruiken?
nee :(
Ad Fundum op 03/09/2021 20:23:48:
Als er geen record bestaat waar naar verwezen kan worden, is het gebruikelijk om in de kolom met de FK-contraint een NULL te zetten. Dat is geen probleem voor de FK-constraint, die controleert alleen maar of de waarde waar naar verwezen wordt bestaat, als er überhaupt een waarde is opgegeven.
Aannemende dat 0 de niet-bestaande waarde is, kan je kiezen om 0 toe te voegen in de andere tabel, of 0 te vervangen voor NULL.
Aannemende dat 0 de niet-bestaande waarde is, kan je kiezen om 0 toe te voegen in de andere tabel, of 0 te vervangen voor NULL.
Spijtig genoeg is er maar 1 null.
Ik heb 0,-1 en -2
Gewijzigd op 04/09/2021 15:09:47 door Jan R
Mocht je het moeten veranderen, dan zou het beter zijn om een tabel te maken, bijvoorbeeld
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- PostgreSQL is even het enige dat ik paraat heb
CREATE SEQUENCE "tegenstander_id_seq";
GRANT USAGE ON SEQUENCE TO PUBLIC;
CREATE TABLE "tegenstander" (
"id" bigint NOT NULL DEFAULT nextval('tegenstander_id_seq'::regclass),
"naam" text NOT NULL DEFAULT E'',
PRIMARY KEY ("id")
) WITH (OIDS=FALSE);
COMMENT ON TABLE "tegenstander" IS 'Aanwezigheid tegenstander';
COMMENT ON COLUMN "id" IS 'ID';
COMMENT ON COLUMN "naam" IS 'Naam';
INSERT INTO "tegenstander" ("id", "naam") VALUES
(-2, 'vrijwillig niet spelen'),
(-1, 'geen tegenstander'),
( 0, 'afwezig');
ALTER TABLE "<mijn tabel>" ADD FOREIGN KEY "<naam fk>"
REFERENCES TO "tegenstander MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT;
GRANT ALL ON "tegenstander" TO PUBLIC;
CREATE SEQUENCE "tegenstander_id_seq";
GRANT USAGE ON SEQUENCE TO PUBLIC;
CREATE TABLE "tegenstander" (
"id" bigint NOT NULL DEFAULT nextval('tegenstander_id_seq'::regclass),
"naam" text NOT NULL DEFAULT E'',
PRIMARY KEY ("id")
) WITH (OIDS=FALSE);
COMMENT ON TABLE "tegenstander" IS 'Aanwezigheid tegenstander';
COMMENT ON COLUMN "id" IS 'ID';
COMMENT ON COLUMN "naam" IS 'Naam';
INSERT INTO "tegenstander" ("id", "naam") VALUES
(-2, 'vrijwillig niet spelen'),
(-1, 'geen tegenstander'),
( 0, 'afwezig');
ALTER TABLE "<mijn tabel>" ADD FOREIGN KEY "<naam fk>"
REFERENCES TO "tegenstander MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT;
GRANT ALL ON "tegenstander" TO PUBLIC;
Echter iets bizar nu
Code (php)
1
2
3
4
5
6
2
3
4
5
6
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_031 FOREIGN KEY (kleurder) REFERENCES strips_kleurders (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_032 FOREIGN KEY (schrijver) REFERENCES strips_schrijvers (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_033 FOREIGN KEY (tekenaar) REFERENCES strips_tekenaars (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_034 FOREIGN KEY (uitgever) REFERENCES strips_uitgeverij (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_strips ADD CONSTRAINT FK_035 FOREIGN KEY (reeksnr) REFERENCES strips_reeksen (reeksnr) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_subtitels ADD CONSTRAINT FK_036 FOREIGN KEY (id) REFERENCES strips_strips (uniek) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_032 FOREIGN KEY (schrijver) REFERENCES strips_schrijvers (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_033 FOREIGN KEY (tekenaar) REFERENCES strips_tekenaars (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_reeksen ADD CONSTRAINT FK_034 FOREIGN KEY (uitgever) REFERENCES strips_uitgeverij (nummer) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_strips ADD CONSTRAINT FK_035 FOREIGN KEY (reeksnr) REFERENCES strips_reeksen (reeksnr) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE strips_subtitels ADD CONSTRAINT FK_036 FOREIGN KEY (id) REFERENCES strips_strips (uniek) ON DELETE CASCADE ON UPDATE CASCADE;
Ik voeg deze commando's in via phpMySql. Geen foutmelding maar toch staan ze niet in de lijst met constraints.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'janr_be'
ORDER BY
KEY_COLUMN_USAGE.CONSTRAINT_NAME ASC
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'janr_be'
ORDER BY
KEY_COLUMN_USAGE.CONSTRAINT_NAME ASC
Van de 103 nu lukken enkel deze 6 niet. Sommige anderen gaven ook wel fouten maar dat lag aan de database/mij :)
Gewijzigd op 04/09/2021 15:15:23 door Jan R
Je kunt het schema ook nog vermelden bij het aanmaken van de constraints:
Code (php)
1
2
3
4
2
3
4
ALTER TABLE janr_be.strips_reeksen
ADD CONSTRAINT janr_be.FK_031 FOREIGN KEY (kleurder)
REFERENCES janr_be.strips_kleurders (nummer)
ON DELETE CASCADE ON UPDATE CASCADE;
ADD CONSTRAINT janr_be.FK_031 FOREIGN KEY (kleurder)
REFERENCES janr_be.strips_kleurders (nummer)
ON DELETE CASCADE ON UPDATE CASCADE;
Toevoeging op 05/09/2021 14:05:57:
Gevonden!
tabellen stonden als MyIsam ipv InnoDB
De tabellen werden aangemaakt vanuit een Windows desktop programma en hier gebeurde dus een foutje.
Eindelijk weer een stapje dichterbij.
Jan R op 05/09/2021 09:54:40:
Gevonden!
tabellen stonden als MyIsam ipv InnoDB
tabellen stonden als MyIsam ipv InnoDB
Dat is één van die dingen om geen MySQL te willen gebruiken, maar een echte database server zoals PostgreSQL. Nu je toch bezig bent is dit misschien een mooi moment om over te stappen als de hosting het toe laat. Je zult er geen spijt van krijgen.
MRG_MyISAM
CSV
MEMORY
MyISAM
Aria
InnoDB
SEQUENCE
Omdat MyIsam als standaard stond heb ik in het begin dat gebruikt en daarna overgestapt naar InnoDB. Iets installeren zit er niet bij. Is enkel hosting!
Server: janr.be.mysql
Servertype: MariaDB
Serverversie: 10.3.30-MariaDB-1:10.3.30+maria~focal - mariadb.org binary distribution
Protocolversie: 10
Gebruiker: xxxxxxxx
Karakterset van server: UTF-8 Unicode (utf8)
Maar ik dacht dat het vroeger mysql was nu mariaDB
Jan