recursief aanpassen van records

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Jan R

Jan R

29/08/2021 19:16:59
Quote Anchor link
Hi

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)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
#bron w3schools
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
 
PHP hulp

PHP hulp

28/03/2024 12:38:13
 
Ad Fundum

Ad Fundum

30/08/2021 08:15:30
Quote Anchor link
Beetje vage beschrijving, misschien kan je er een voorbeeld bij geven?

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.
 
Jan R

Jan R

30/08/2021 15:21:03
Quote Anchor link
Hier een foto van een deel van de relaties
Afbeelding

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)
PHP script in nieuw venster Selecteer het PHP script
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
 
Ad Fundum

Ad Fundum

31/08/2021 09:07:41
Quote Anchor link
Aan de screenshot te zien gebruik je MySQL/MariaDB ?
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)
PHP script in nieuw venster Selecteer het PHP script
1
2
ALTER TABLE groep ADD CONSTRAINT trn_toernooien_groep_id_fkey
  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)
PHP script in nieuw venster Selecteer het PHP script
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)
PHP script in nieuw venster Selecteer het PHP script
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)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
INSERT INTO groepen
SELECT * FROM trn_groepen
UNION ALL SELECT * FROM tr2_groepen
UNION ALL SELECT * FROM ...


5.) De oude tabellen verwijderen
 
Ward van der Put
Moderator

Ward van der Put

31/08/2021 09:12:47
Quote Anchor link
Als de tabellen die je wilt samenvoegen dezelfde structuur hebben, kun je het waarschijnlijk het gemakkelijkst en veiligst oplossen met een numerieke prefix.

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.
 
Jan R

Jan R

31/08/2021 12:05:51
Quote Anchor link
Beiden bedankt. ik hoopte op minder werk :) maar dacht al dat dit de beste oplossing zou zijn.
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)
PHP script in nieuw venster Selecteer het PHP script
1
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
 
Ivo P

Ivo P

31/08/2021 15:12:40
Quote Anchor link
in zo'n geval is het verstandiger om de check tijdelijk uit te zetten in plaats van helemaal niet te gebruiken:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
SET FOREIGN_KEY_CHECKS=0;
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.
 
Jan R

Jan R

01/09/2021 14:06:04
Quote Anchor link
Die kende ik nog niet. Bedankt!

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
 
Jan R

Jan R

03/09/2021 11:52:15
Quote Anchor link
Ik ben dus vollop bezig met de CONSTRAINT's aan te maken. Ik heb er al 48. Ik heb er echter ook 1tje die niet verplicht een link heeft.
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)
PHP script in nieuw venster Selecteer het PHP script
1
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;

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
 
Ad Fundum

Ad Fundum

03/09/2021 20:23:48
Quote Anchor link
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.
 
Ivo P

Ivo P

03/09/2021 22:06:29
Quote Anchor link
kun je geen mysqldump gebruiken?

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.
 
Jan R

Jan R

04/09/2021 07:12:34
Quote Anchor link
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.

Spijtig genoeg is er maar 1 null.
Ik heb 0,-1 en -2
Gewijzigd op 04/09/2021 15:09:47 door Jan R
 
Ad Fundum

Ad Fundum

04/09/2021 09:25:32
Quote Anchor link
Als die redenen in alle tabellen hetzelfde zijn hoef je daar niets aan te veranderen, of mis ik iets?
Mocht je het moeten veranderen, dan zou het beter zijn om een tabel te maken, bijvoorbeeld
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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;
Gewijzigd op 04/09/2021 09:27:17 door Ad Fundum
 
Jan R

Jan R

04/09/2021 15:14:27
Quote Anchor link
Ik heb deze gewoon toegevoegd. dus dat probleem opgelost.
Echter iets bizar nu
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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;

Ik voeg deze commando's in via phpMySql. Geen foutmelding maar toch staan ze niet in de lijst met constraints.
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

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
 
Ad Fundum

Ad Fundum

04/09/2021 22:12:54
Quote Anchor link
Misschien was het schema verkeerd?
Je kunt het schema ook nog vermelden bij het aanmaken van de constraints:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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;
 
Jan R

Jan R

05/09/2021 09:54:40
Quote Anchor link
Als het schema verkeerd is zou ik toch een foutmelding krijgen?

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.
 
Ad Fundum

Ad Fundum

05/09/2021 14:48:02
Quote Anchor link
Jan R op 05/09/2021 09:54:40:
Gevonden!
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.
 
Jan R

Jan R

06/09/2021 07:27:18
Quote Anchor link
Op mijn hoosting heb ik deze opties
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
 
Ad Fundum

Ad Fundum

06/09/2021 08:52:09
Quote Anchor link
Tenzij je nog veel zou willen ontwikkelen, dan kan het lonend zijn om over te stappen naar hosting met PostgreSQL, anders zou ik het gewoon zo laten, met alleen InnoDB als storage engine.
Gewijzigd op 06/09/2021 08:52:50 door Ad Fundum
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.