Database indelen en up-to-date houden
Graag wil ik jullie advies bij indelen en up-to-date houden van een database.
Ik heb allemaal video's in een tabel staan (800.000 items), deze zijn verdeeld in categorieën. Maar ze kunnen in meerdere categorieën staan!
Wanneer je op de video pagina bent dienen de categorieën waar de video in staat weergegeven te worden en dient er dus aan de hand van het "video_id" dit opgehaald worden.
Er is natuurlijk een overzicht van alle categorieën en uiteraard kan er door categorieën gebladerd worden en dienen de video's tevoorschijn te komen van de betreffende categorie.
Op het moment staan alle categorieën in het video tabel bij elkaar, bijvoorbeeld:
ID 1
Cat Games;Tech;Algemeen
Hierdoor kan ik op de video pagina gemakkelijk de categorieën weergeven. Maar hierdoor heb ik geen tabel met alle categorieën erin. Zo "indexeer" (zo'n query met nog wat php gespeel duurt even namelijk) ik nu alle categorieën en plaats deze in een ander tabel waarmee ik een pagina kan maken met een overzicht van alle categorieën.
Zodra op een categorie pagina wordt geopend gebruik ik een "like query" om de betreffende video's te laden:
Code (php)
1
SELECT * FROM videos WHERE Cat LIKE %Tech%
Dit in 800.000 items werkt gigantisch traag. Graag ook advies over eventuele indexes in deze situatie.
Graag jullie advies hoe dit in te delen en uit te lezen. En qua performance lekker snel te maken.
Als tweede vraag: Hoe een grote database up-to-date houden?
Wat ik nu elke keer doe is alles verwijderen en opnieuw toevoegen, hierdoor hebben helaas alle videos elke keer een ander id. Wel het meest snelle lijkt mij.
Andere optie is natuurlijk loop'en door alle items (de nieuwe items cq de xml) en:
Is deze video al aanwezig?
Nee -> Toevoegen
Ja -> Gegevens hetzelfde?
Nee -> Updaten
Dan hebben we de nieuwe en geupdate items onder controle maar hebben we wel erg veel query's elke keer?
Dan, om de oude items te verwijderen kan ik bijv. bij elk item een "update datum" meegeven (dan moet ik wel bij elke update alle items updaten). Op het moment dat die datum achterloopt verwijderen.
Daarbij komt nog een keer dit alles om de categorieën tabel up-to-date te houden.
Graag jullie advies!
Groet!
Gewijzigd op 07/11/2012 17:58:04 door Roy -
Als je een juiste query-opbouw hebt / juiste controlestructuur is het toevoegen/wijzigen van 1 record sneller dan alles verwijderen en toevoegen.
Als jij een kast met ordners hebt en je moet 1 a4-tje toevoegen, trek je toch ook niet alles uit de kast om dan daarna alles terug te zetten? Als je een logisch kastindeling hebt met duidelijke, eenduidige namen, dan kun je het heel snel op de juiste plaats opbergen.
Verwijderen: in de meest eenvoudige vorm een delete-query met een WHERE constructie (op id)
Naar mijn idee denk je veel te moeilijk ;-)
Dan een tabel waarin staat per game_id en een cat_id.
En dan kunnen er prima 10 categorieën bij 1 game horen.
LIKE niet gebruiken, alleen =!
Gewijzigd op 07/11/2012 20:05:26 door Aad B
Gewijzigd op 07/11/2012 20:49:50 door Ger van Steenderen
Als je één boek wil toevoegen in de boekenkast, zet je hem er gewoon bij. Maar als je in de kast wat boeken hebt staan en deze zelfde boeken weer krijgt met een aantal nieuwe, is het toch makkelijker om alles gewoon eruit te gooien en de nieuwe erin te plaatsen? In plaats van te zoeken welke je al hebt en welke nieuw zijn.
En verwijderen door simpelweg met een where op id klinkt makkelijk, maar dan moet ik wel de id's weten welke niet meer aanwezig zijn ;)
@Eddy Erkelens:
Weet niet of ik je helemaal begrijp maar volgens mij is het op die manier wat statisch? Er zijn namelijk geen vaste categorieën.
@Aad B:
Index op cat scheelt niets in de huidige situatie. En als ik een like met enkel een % aan het einde doe worden de categorieën welke in het midden staan niet gezien (bijv. Games;Tech;Algemeen). Als ik daar Tech% doe krijg ik niets, toch?
@Ger van Steenderen:
Daar zat ik gisteren ook nog aan te denken inderdaad. (inmiddels gebruikt als voorbeeld onderop)
@Iedereen:
Ik zal later nog even een duidelijke uitleg maken met de nodige voorbeelden erbij.
Toevoeging op 08/11/2012 13:00:15:
Bij deze probeer ik het zo duidelijk mogelijk uit te leggen.
Ik krijg elke dag meerdere XML's aangeleverd met als voorbeeld daarin:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<item>
<id>1</id>
<title>Naam</title>
<info>Omschrijving</info>
<cats>
<cat>Games</cat>
<cat>Tech</cat>
<cat>Algemeen</cat>
</cats>
</item>
<id>1</id>
<title>Naam</title>
<info>Omschrijving</info>
<cats>
<cat>Games</cat>
<cat>Tech</cat>
<cat>Algemeen</cat>
</cats>
</item>
Alle XML's bij elkaar gaat het om zo'n 800.000 items.
Quote:
Nu moet deze data opgeslagen worden in een database en volledig up-to-date blijven. Nieuwe items moeten dus toegevoegd worden, mocht er iets gewijzigd zijn moet dit bijgewerkt worden en mocht er een item niet meer genoemd worden, verwijderd.
Er komen 3 soorten pagina's:
1) Overzicht Hier worden alle categorieën weergegeven
2) Categorie Hier worden alle video's weergegeven uit de gekozen categorie
3) Item Hier wordt het item weergeven met alle nodige informatie (onder andere in welke categorieën te vinden)
Hoe de database hiervoor opbouwen?
Nog ergens indexes plaatsen? Gezien de grote hoeveelheid items?
Tevens ben ik wat aan het experimenteren met relaties (foreign keys), hier toepasbaar?
tabel items
item_id middels auto_increment en supplier_id is gelijk aan het id uit de xml
Code (php)
1
2
3
2
3
item_id,supplier_id,title,info
-------------------------
1,1,Naam,Omschrijving
-------------------------
1,1,Naam,Omschrijving
tabel items_cats
Code (php)
1
2
3
4
5
2
3
4
5
id,item_id,cat_id
-----------------
1,1,1
2,1,2
3,1,3
-----------------
1,1,1
2,1,2
3,1,3
tabel cats
Code (php)
1
2
3
4
5
2
3
4
5
cat_id,cat_name
-----------
1,Games
2,Tech
3,Algemeen
-----------
1,Games
2,Tech
3,Algemeen
En hoe dit verwerken?
Ik "loop" middels een "foreach" door de XML heen, wat ga ik vervolgens doen om het eerder in de quote genoemde mogelijk te maken?
Gewijzigd op 08/11/2012 19:30:42 door Roy -
Aannemende dat je dat wel weet zou je bijvoorbeeld de volgende volgorde toe kunnen passen:
- bestand komt van? Dit zal je supplier_id gaan vormen
- kijk eerst welke categorien er in het bestand zitten, zijn er nieuwe dan moet je deze eerst toevoegen aan de tabel cats. Sla vervolgens alle benodigde cat_id's op in een array, inclusief de nieuwe toegevoegde
- nu kun je de tabel items vullen met de leverancier, titel en de info. Sla het nieuwe id van dit item op in een variable.
- als laatste kun je je tabel item_cats vullen met behulp van je array met categorien en je variable met het item_id
Quote:
item_id middels auto_increment en supplier_id is gelijk aan het id uit de xml
Ik zal er nummers bij zetten:
1) Dus ik "loop" eerst door de XML heen (per item), vervolgens een "sub-loop" per categorie. Daarbij bouw ik een array op met alle genoemde categorieën. Aan het einde van alle "loops" gooi ik de array in array_unique zodat ik geen duplicaten heb. "Loop" ik door die array heen en ga ik per categorie in de tabel cats kijken of deze aanwezig is. Zo niet voeg ik deze toe. Vervolgens haal ik alle categorieën op uit de database en zet ik deze in een array zodat ik de cat_name bij het juiste cat_id heb en categorieën die wellicht via een andere XML zijn toegevoegd.
2) En hoe ga ik ervoor zorgen dat oude cq niet meer aanwezige categorieën verwijderd worden? Deze worden wellicht wel nog genoemd in een andere XML?
3) Om de tabel items te vullen ga ik dus weer door de XML "loopen" en bouw ik opnieuw (maar deze keer beginnen met een lege array) een array van categorieën op middels de "sub-loop" (heb het item_id nodig namelijk bij het vullen van de items_cats tabel, dus die moet ik eerst weten). Na het voltooien van de "sub-loop" ga ik kijken aan de hand van het XML id of deze als supplier_id aanwezig is in het items tabel en wanneer aanwezig haal ik de gegevens hiervan op. Vervolgens vergelijk ik de aanwezige waardes (title en info) met de waardes in de XML. Wanneer anders updaten we dit. Mocht het item niet aanwezig zijn voegen we deze natuurlijk toe. Hierop volgend "loop" ik door de opgebouwde array met categorieën voor dit item en ga ik weer kijken of deze aanwezig zijn in het items_cats tabel en anders voeg ik deze toe. Middels het item_id die we eerder opgehaald hebben bij het bewerken of anders middels "insert_id" en het cat_id vanuit de opgebouwde array
4) Ook hier de vraag weer, hoe ervoor zorgen dat de niet meer aanwezige items verwijderd worden? Daarbij de bijbehorende gegevens in het items_cats tabel.
Zoiets? Is dit te moeilijk gedacht?
Enkel ter illustratie wat ik hierboven beschreef. Zitten aardige fouten in, I know!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<?php
//XML laden
$xml = simplexml_load_file('feed.xml');
//Loopen
foreach($xml as $item){
//Foreach cat
foreach($cats as $cat){
//Array met alle categorieën
$cats[] = $cat;
}
}
//Duplicaten verwijderen
$cats = array_unique($cats);
//Loopen
foreach($cats as $cat){
//Aanwezig?
$query = mysql_query("SELECT * FROM cats WHERE cat_name=$cat");
//Niet aanwezig?
if(!mysql_num_rows($query)){
mysql_query("INSERT INTO cats (cat_name) VALUES ($cat)");
}
}
//Alle cats ophalen
$query = mysql_query("SELECT * FROM cats");
$hoofdcats = mysql_fetch_array($query);
//Loopen
foreach($xml as $item){
//Starten met lege array
$cats = array();
//Foreach cat
foreach($cats as $cat){
$cats[] = $cat;
}
//Item aanwezig?
$query = mysql_query("SELECT * FROM items WHERE supplier_id=$item->id");
if(mysql_num_rows($query)){
$gegevens = mysql_fetch_assoc($query);
//Gegevens anders?
if($gegevens[info] != $cat->info OR $gegevens[title] != $cat->title){
//Updaten
mysql_query("UPDATE items SET info=$cat->info, title=$cat->title WHERE item_id=$cat->id");
}
$item_id = $gegevens[id];
} else {
//Toevoegen
mysql_query("INSERT INTO items (supplier_id,title,info) VALUES ($cat->id,$cat->title,$cat->info)");
$item_id = mysql_insert_id();
}
//Loop cats
foreach($cats as $cat){
//Aanwezig?
$query = mysql_query("SELECT * FROM items_cats WHERE item_id=$item_id AND cat_id=$hoofdcat[$cat]");
if(!mysql_num_rows($query)){
mysql_query("INSEERT INTO items_cats (item_id,cat_id) VALUES ($item_id,$hoofdcat[$cat])");
}
}
}
?>
//XML laden
$xml = simplexml_load_file('feed.xml');
//Loopen
foreach($xml as $item){
//Foreach cat
foreach($cats as $cat){
//Array met alle categorieën
$cats[] = $cat;
}
}
//Duplicaten verwijderen
$cats = array_unique($cats);
//Loopen
foreach($cats as $cat){
//Aanwezig?
$query = mysql_query("SELECT * FROM cats WHERE cat_name=$cat");
//Niet aanwezig?
if(!mysql_num_rows($query)){
mysql_query("INSERT INTO cats (cat_name) VALUES ($cat)");
}
}
//Alle cats ophalen
$query = mysql_query("SELECT * FROM cats");
$hoofdcats = mysql_fetch_array($query);
//Loopen
foreach($xml as $item){
//Starten met lege array
$cats = array();
//Foreach cat
foreach($cats as $cat){
$cats[] = $cat;
}
//Item aanwezig?
$query = mysql_query("SELECT * FROM items WHERE supplier_id=$item->id");
if(mysql_num_rows($query)){
$gegevens = mysql_fetch_assoc($query);
//Gegevens anders?
if($gegevens[info] != $cat->info OR $gegevens[title] != $cat->title){
//Updaten
mysql_query("UPDATE items SET info=$cat->info, title=$cat->title WHERE item_id=$cat->id");
}
$item_id = $gegevens[id];
} else {
//Toevoegen
mysql_query("INSERT INTO items (supplier_id,title,info) VALUES ($cat->id,$cat->title,$cat->info)");
$item_id = mysql_insert_id();
}
//Loop cats
foreach($cats as $cat){
//Aanwezig?
$query = mysql_query("SELECT * FROM items_cats WHERE item_id=$item_id AND cat_id=$hoofdcat[$cat]");
if(!mysql_num_rows($query)){
mysql_query("INSEERT INTO items_cats (item_id,cat_id) VALUES ($item_id,$hoofdcat[$cat])");
}
}
}
?>
Gewijzigd op 09/11/2012 16:32:52 door Roy -
Ik ben niet zeker, maar ik denk dat helemaal legen en opnieuw vullen dan wel het snelste is. Je hoeft dan geen checks uit te voeren op items en categorien die verwijderd moeten worden.
Hoe belangrijk is het dat het id van het item hetzelfde blijft?
Toevoeging op 09/11/2012 16:27:32:
Je zou voor het behouden van de id's een tabel items_oud kunnen maken. Elke keer als je opnieuw invoert zet je eerst de items over naar deze tabel. Hier heb je alleen de titel en het id_oud voor nodig.
Aan je tabel items voeg je een kolom item_id_oud toe. Als alles opnieuw is geimporteerd zou je een check kunnen doen op basis van de titel en zou de oude id's in je tabel items opnemen. Let wel dat als het item niet voor komt in de tabel items_oud je dan als item_id_oud het nieuwe id neemt.
Item id van leverancier "Pietje bel": P1
Item id van leverancier "Klaas": K1
Dan de item id's aanhouden als uniek id.
Er zijn wat 'trucjes' die je in SQL kunt doen om wat dingen te vereenvoudigen. Kijk eens naar deze query:
Code (php)
1
2
3
2
3
INSERT INTO items (item_id, supplier_id, title, info)
VALUES (1,1,'title1','info1'), (2,1,'title2','info2')
ON DUPLICATE KEY UPDATE
VALUES (1,1,'title1','info1'), (2,1,'title2','info2')
ON DUPLICATE KEY UPDATE
Op deze manier kan je in één query meerdere rijen tegelijk inserten, als de combi item_id, supplier_id bestaat worden de gegevens geupdate met de insert waardes.
Als je de item_id's in een array bewaard kan je ook met 1 query de te verwijderen items uit de tabel deleten:
Code (php)
1
2
3
4
2
3
4
<?php
$sql = "DELETE FROM items WHERE supplier_id=1 AND item_id NOT IN (" .
implode(',', $item_ids) . ")";
?>
$sql = "DELETE FROM items WHERE supplier_id=1 AND item_id NOT IN (" .
implode(',', $item_ids) . ")";
?>
Over de categorieën zou ik me niet te druk maken, als ze niet bestaan voeg je ze toe, en als ze bij een leverancier wel kunnen voorkomen en een andere niet laat je ze gewoom alrijd in de tabel staan.
Gewijzigd op 09/11/2012 18:05:23 door Ger van Steenderen
En inderdaad: je kan altijd (heel eenvoudig) een categorie toevoegen, als nodig.
Maar categorieën zijn juist wel vaste dingen; het is immers een noemer om delen te groeperen.
Wat betreft je eerste voorbeeld, erg handig! Maar... het item_id wordt bepaald middels auto increment en weet ik dus niet. Is het dan evengoed toepasbaar? Of is het uberhaupt zonder het gebruiken van minstens één primary key te gebruiken?
Ofwel, werkt dit ook? (enkel als supplier_id een primary key is denk ik?)
Code (php)
1
2
3
2
3
INSERT INTO items (supplier_id, title, info)
VALUES (1,'title1','info1'), (1,'title2','info2')
ON DUPLICATE KEY UPDATE
VALUES (1,'title1','info1'), (1,'title2','info2')
ON DUPLICATE KEY UPDATE
en dit? (lijkt me niet gezien het juist om "on duplicate key" gaat en er zo geen key is)
Code (php)
1
2
3
2
3
INSERT INTO items (title, info)
VALUES ('title1','info1'), ('title2','info2')
ON DUPLICATE KEY UPDATE
VALUES ('title1','info1'), ('title2','info2')
ON DUPLICATE KEY UPDATE
Je verwijderen tip vindt ik geniaal!
En categorieën ga ik even over nadenken, gezien er op die wijze niet alleen categorieën zijn maar ook tags. Categorieën zijn dan wel aardig statisch maar de tags zijn heel wisselend.
Gewijzigd op 09/11/2012 21:00:20 door Roy -
Een index-key hoeft toch niet per se numeriek te zijn?
Ik dacht in eerste instantie dat het item_id in de tabel het item_id uit de XML was, maar daar maak jij dus supplier_id van. Ik dacht dat dit dus het id van de leverancier was. In principe kan je dat nog steeds toepassen, want er staat nergens dat een primary key een auto_increment kolom moet zijn.
Update!
Inmiddels wat gespeeld met tot nu toe het volgende resultaat:
Database structuur
- video
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `videos` (
`video_id` int(15) NOT NULL,
`supplier_id` int(2) NOT NULL,
`title` varchar(255) NOT NULL,
`duration` varchar(10) NOT NULL,
`date` date NOT NULL,
`embed` text NOT NULL,
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`video_id` int(15) NOT NULL,
`supplier_id` int(2) NOT NULL,
`title` varchar(255) NOT NULL,
`duration` varchar(10) NOT NULL,
`date` date NOT NULL,
`embed` text NOT NULL,
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- suppliers
Code (php)
1
2
3
4
5
6
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `suppliers` (
`supplier_id` int(2) NOT NULL AUTO_INCREMENT,
`supplier_name` varchar(255) NOT NULL,
`supplier_feed` varchar(255) NOT NULL,
PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`supplier_id` int(2) NOT NULL AUTO_INCREMENT,
`supplier_name` varchar(255) NOT NULL,
`supplier_feed` varchar(255) NOT NULL,
PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- categories
Code (php)
1
2
3
4
5
6
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `categories` (
`category_id` int(10) NOT NULL AUTO_INCREMENT,
`category_name` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`),
UNIQUE KEY `category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`category_id` int(10) NOT NULL AUTO_INCREMENT,
`category_name` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`),
UNIQUE KEY `category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- video_cat
Code (php)
1
2
3
4
2
3
4
CREATE TABLE IF NOT EXISTS `video_cat` (
`video_id` int(15) NOT NULL,
`cat_id` int(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`video_id` int(15) NOT NULL,
`cat_id` int(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- tags
Code (php)
1
2
3
4
5
6
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `tags` (
`tag_id` int(10) NOT NULL AUTO_INCREMENT,
`tag_name` varchar(255) NOT NULL,
PRIMARY KEY (`tag_id`),
UNIQUE KEY `tag_name` (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`tag_id` int(10) NOT NULL AUTO_INCREMENT,
`tag_name` varchar(255) NOT NULL,
PRIMARY KEY (`tag_id`),
UNIQUE KEY `tag_name` (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- video_tag
Code (php)
1
2
3
4
2
3
4
CREATE TABLE IF NOT EXISTS `video_tag` (
`video_id` int(15) NOT NULL,
`tag_id` int(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`video_id` int(15) NOT NULL,
`tag_id` int(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Code
Ter herinnering toch nog even: het gaat hier om 800.000 videos die verwerkt worden. Elke video kan in meerdere categorieën staan en kan meerdere tags hebben!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
<?php
//Set supplier
$supplier_id = 1;
$supplier_name = 'naam';
$supplier_feed = 'http://website.nl/feed.xml';
//Download XML
file_put_contents('feeds/'.$supplier_name.'.xml', file_get_contents($supplier_feed));
//Load XML
$xml = simplexml_load_file('feeds/'.$supplier_name.'.xml');
//Start loop to get all cats and tags
foreach($xml as $video)
{
//Categories
foreach($video->categories as $cat)
{
$category = ucfirst(strtolower($cat->category));
if(!empty($category))
{
$categories[] = $category;
}
}
//Tags
foreach($video->tags as $tag)
{
$tag = ucfirst(strtolower($tag->tag));
if(!empty($tag))
{
$tags[] = $tag;
}
}
}
//Strip duplicates and sort cats and tags
$categories = array_unique($categories);
sort($categories);
$tags = array_unique($tags);
sort($tags);
//Reconnect
$this->db->reconnect();
//Loop trough all cats and tags and insert if not exist
foreach($categories as $category){
$this->db->query("INSERT IGNORE INTO categories (category_name) VALUES (".$this->db->escape($category).")");
}
foreach($tags as $tag){
$this->db->query("INSERT IGNORE INTO tags (tag_name) VALUES (".$this->db->escape($tag).")");
}
//Get all cats and tags with the name as key
$categories = array();
$query = $this->db->select('category_id,category_name')->get('categories');
foreach($query->result_array() as $cat){
$categories[$cat['category_name']] = $cat['category_id'];
}
$tags = array();
$query = $this->db->select('tag_id,tag_name')->get('tags');
foreach($query->result_array() as $cat){
$tags[$cat['tag_name']] = $cat['tag_id'];
}
//Start loop for the videos
foreach($xml as $video)
{
//Set video_id
$video_id = (int)$video->id.$supplier_id;
//Insert or update video
$this->db->query("INSERT INTO videos (
video_id,
supplier_id,
title,
duration,
date,
embed
) VALUES (
".$this->db->escape($video_id).",
".$this->db->escape($supplier_id).",
".$this->db->escape((string)$video->title).",
".$this->db->escape((string)$video->duration).",
".$this->db->escape(substr((string)$video->date_added,0,10)).",
".$this->db->escape((string)$video->embed)."
) ON DUPLICATE KEY UPDATE
title = ".$this->db->escape((string)$video->title).",
duration = ".$this->db->escape((string)$video->duration).",
date = ".$this->db->escape(substr((string)$video->date_added,0,10)).",
embed = ".$this->db->escape((string)$video->embed)."
");
//Delete cats and tags by video_id
$this->db->delete('video_cat', array('video_id' => $video_id));
$this->db->delete('video_tag', array('video_id' => $video_id));
//Categories
foreach($video->categories as $cat)
{
$cat = ucfirst(strtolower($cat->category));
if(!empty($cat) AND array_key_exists($cat,$categories)){
$this->db->insert('video_cat',array('video_id' => $video_id,'cat_id' => $categories[$cat]));
}
}
//Tags
foreach($video->tags as $tag)
{
$tag = ucfirst(strtolower($tag->tag));
if(!empty($tag) AND array_key_exists($tag,$tags)){
$this->db->insert('video_tag',array('video_id' => $video_id,'tag_id' => $tags[$tag]));
}
}
$all_videos[] = $video_id;
}
//Delete all who are not longer present
$this->db->query("DELETE FROM videos WHERE supplier_id=".$this->db->escape($supplier_id)." AND video_id NOT IN (".implode(',',$all_videos).")");
//Delete file
unlink('feeds/'.$supplier_name.'.xml');
//Echo script execution time
echo $this->benchmark->elapsed_time();
?>
//Set supplier
$supplier_id = 1;
$supplier_name = 'naam';
$supplier_feed = 'http://website.nl/feed.xml';
//Download XML
file_put_contents('feeds/'.$supplier_name.'.xml', file_get_contents($supplier_feed));
//Load XML
$xml = simplexml_load_file('feeds/'.$supplier_name.'.xml');
//Start loop to get all cats and tags
foreach($xml as $video)
{
//Categories
foreach($video->categories as $cat)
{
$category = ucfirst(strtolower($cat->category));
if(!empty($category))
{
$categories[] = $category;
}
}
//Tags
foreach($video->tags as $tag)
{
$tag = ucfirst(strtolower($tag->tag));
if(!empty($tag))
{
$tags[] = $tag;
}
}
}
//Strip duplicates and sort cats and tags
$categories = array_unique($categories);
sort($categories);
$tags = array_unique($tags);
sort($tags);
//Reconnect
$this->db->reconnect();
//Loop trough all cats and tags and insert if not exist
foreach($categories as $category){
$this->db->query("INSERT IGNORE INTO categories (category_name) VALUES (".$this->db->escape($category).")");
}
foreach($tags as $tag){
$this->db->query("INSERT IGNORE INTO tags (tag_name) VALUES (".$this->db->escape($tag).")");
}
//Get all cats and tags with the name as key
$categories = array();
$query = $this->db->select('category_id,category_name')->get('categories');
foreach($query->result_array() as $cat){
$categories[$cat['category_name']] = $cat['category_id'];
}
$tags = array();
$query = $this->db->select('tag_id,tag_name')->get('tags');
foreach($query->result_array() as $cat){
$tags[$cat['tag_name']] = $cat['tag_id'];
}
//Start loop for the videos
foreach($xml as $video)
{
//Set video_id
$video_id = (int)$video->id.$supplier_id;
//Insert or update video
$this->db->query("INSERT INTO videos (
video_id,
supplier_id,
title,
duration,
date,
embed
) VALUES (
".$this->db->escape($video_id).",
".$this->db->escape($supplier_id).",
".$this->db->escape((string)$video->title).",
".$this->db->escape((string)$video->duration).",
".$this->db->escape(substr((string)$video->date_added,0,10)).",
".$this->db->escape((string)$video->embed)."
) ON DUPLICATE KEY UPDATE
title = ".$this->db->escape((string)$video->title).",
duration = ".$this->db->escape((string)$video->duration).",
date = ".$this->db->escape(substr((string)$video->date_added,0,10)).",
embed = ".$this->db->escape((string)$video->embed)."
");
//Delete cats and tags by video_id
$this->db->delete('video_cat', array('video_id' => $video_id));
$this->db->delete('video_tag', array('video_id' => $video_id));
//Categories
foreach($video->categories as $cat)
{
$cat = ucfirst(strtolower($cat->category));
if(!empty($cat) AND array_key_exists($cat,$categories)){
$this->db->insert('video_cat',array('video_id' => $video_id,'cat_id' => $categories[$cat]));
}
}
//Tags
foreach($video->tags as $tag)
{
$tag = ucfirst(strtolower($tag->tag));
if(!empty($tag) AND array_key_exists($tag,$tags)){
$this->db->insert('video_tag',array('video_id' => $video_id,'tag_id' => $tags[$tag]));
}
}
$all_videos[] = $video_id;
}
//Delete all who are not longer present
$this->db->query("DELETE FROM videos WHERE supplier_id=".$this->db->escape($supplier_id)." AND video_id NOT IN (".implode(',',$all_videos).")");
//Delete file
unlink('feeds/'.$supplier_name.'.xml');
//Echo script execution time
echo $this->benchmark->elapsed_time();
?>
Opmerkingen
- Sommige functies zullen wellicht niet meteen bekend zijn maar hier wordt het CodeIgniter framework gebruikt waar deze functies vandaan komen.
- Bovenop in de code zien we dat de "supplier" ingesteld wordt. Straks komt alles in nog een foreach om dit per "supplier" uit te voeren middels de gegevens in het "suppliers" tabel.
Vragen
- Aan- of opmerkingen op de database structuur?
- Aan- of opmerkingen op code?
- Dit runnen gaat gigantisch langzaam (hij draait al een aantal uren!). Voornamelijk bij de laatste loop door de XML heen waarbij de video_cat en video_tag tabellen gevuld worden. Tips voor performance? Query bijvoorbeeld opbouwen en pas helemaal aan het einde uitvoeren, wordt wel een gigantisch lange query dan. Of opsparen en om de 100 videos uitvoeren of iets in die richting?
- Oude cq niet meer gebruikte categorieën en tags worden nog niet verwijderd (uit categories, tags, video_cat en video_tag tabellen), hier tips voor?
- Hoe het geheugen gebruik terugbrengen? Met memory_limit op 1536M is het nog niet genoeg, dan heb ik de volgende regels zelfs "gecomment" zodat ze niet uitgevoerd worden: 8, 107, 116, 128
Gewijzigd op 10/11/2012 18:52:48 door Roy -
Je performance is zo belabberd, omdat je je tig query's uitvoert. Als ik even snel door je code kijk, kan je dit ook bereiken met ca 6 queries. Een multiple row insert is hier uitstekend geschikt voor (en duizend maal sneller dan prepared statements). Ook bv Workbench gebruikt dit bij dumps.
Overigens hoef je niet bij ON DUPLICATE KEY UPDATE de update waardes niet in te geven, dit gebeurt automatisch
Gewijzigd op 10/11/2012 18:44:37 door Ger van Steenderen
Zal de auto increment velden uit de koppel tabellen verwijderen (ook in mijn vorige bericht gedaan om het up-to-date te houden).
Vanuit mijn vragen:
Quote:
Query bijvoorbeeld opbouwen en pas helemaal aan het einde uitvoeren, wordt wel een gigantisch lange query dan. Of opsparen en om de 100 videos uitvoeren of iets in die richting?
Wat is beter? 800.000 items "opsparen" en dus in plaats van steeds uit te voeren aan de query toevoegen en na alle loops pas echt uitvoeren? Of om de (x)xxx aantal videos alvast uitvoeren?
Wat duplicate key betreft krijg ik een error als ik de update waardes er niet achter zet:
Code (php)
1
Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 15
Dan ziet het er zo uit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
//Insert or update video
$this->db->query("INSERT INTO videos (
video_id,
supplier_id,
title,
duration,
date,
embed
) VALUES (
".$this->db->escape($video_id).",
".$this->db->escape($supplier_id).",
".$this->db->escape((string)$video->title).",
".$this->db->escape((string)$video->duration).",
".$this->db->escape(substr((string)$video->date_added,0,10)).",
".$this->db->escape((string)$video->embed)."
) ON DUPLICATE KEY UPDATE");
?>
//Insert or update video
$this->db->query("INSERT INTO videos (
video_id,
supplier_id,
title,
duration,
date,
embed
) VALUES (
".$this->db->escape($video_id).",
".$this->db->escape($supplier_id).",
".$this->db->escape((string)$video->title).",
".$this->db->escape((string)$video->duration).",
".$this->db->escape(substr((string)$video->date_added,0,10)).",
".$this->db->escape((string)$video->embed)."
) ON DUPLICATE KEY UPDATE");
?>
Op de zelfde manier dat een kolom geen select mag heten
Of het verstandig is...