V2 SQL Herkansing Bart van der Schoor #143137 14-nov-2005 Inleiding Mijn eerste opgeleverde stuk was onvoldoende. Dat was mijn Pro-Use concept, en vrij slordig aangepakt. Om te laten zien dat ik echt wel weet van het modeleren en gebruiken van gerelateerde databases pak ik het nu anders aan: ik laat een evolutie zien van een site die ik voor mijn werk als webmaster beheer. Het wordt een combinatie van wat het nu is, hoe het zo geworden is, en zoals ik het graag zou willen hebben. Ik heb al enige praktijk ervaring, en heb dus ook ervaren dat de ideale elegante en nette oplossing niet altijd de beste is (simpelweg omdat het dan niet werkt, of in dit geval ongewenste implicaties heeft). Het is welliswaar gedeeltelijk bestaand werk, maar ik hoop zo wel meer inzicht te laten zien. Ik zal eerst de site, en dan de entiteiten toelichten. Dan komt het ‘ideale’ model, zegmaar de start zoals het in dit geval goed zou moeten werken (met uitleg). Dan komen de fixes die de specifieke minpunten aanpakken (met motivering).
De site De site is www.ringtones.nl, een grote portal voor ringtones en andere mobiele content. Met deze site heb ik de meeste real-life ervaring, en hij is min of meer continu in ontwikkeling (on-and-off). Ik ben de enige webontwikkelaar voor deze site, en heb vanaf mijn MBO stage tot nu toe eraan gewerkt. De content komt van content-provider(s). In een eerdere versie werkten we met templates op de server van de content provider (eg: ik hoefde weinig te scripten). Sinds de laatse incarnatie (=totale herbouw) hebben we een eigen MySQL database, en werkt alles op een eigen webserver met PHP. De content komt elke nacht via een CSV feed binnen, en wordt in een eigen model opgeslagen. Onze content-provider heeft ook een XML feed, maar onze hosting draaide toen nog de oude PHP 4, zonder propere XML tools. Het basis datamodel werkt nu vrij goed (behalve de artiesten filter, maar daar kom ik op terug), en dus is het heel simpel om extra functies bij te bouwen. De daadwerkelijke afhandeling van de verkoop gaat via een popup naar de content provider. Die levert online en via feeds ook alle sales info voor het management (scheelt mij weer een hoop lastige scripts :). Als een verkoop is gedaan POST die server een aantal velden terug naar onze server, voor eigen stats en debugging (o.a. datum, ordercode, contentType, 06nr etc). Note: De basis entiteiten zijn Ringtone en Image, de unieke ID van de content provider is de orderCode, die is voor elk item (ongeacht type) uniek. Category wordt ook aangeleverd, maar Collections is een eigen toevoeging. Het voordeel van een eigen database is de vrijheid die je ermee krijgt (bijv de Collections is een eigen brouwsel op deze basis). Note 2: we hebben enorm veel traffic, dus performance is wel een issue. Soms kies ik dan voor redundancy om over minder tabellen te hoeven selecteren (wordt per geval in de text uitgelegd).
Basis entiteiten Ringtone - alle ringtone-type items (Realtones,Polyfonics,Monofone,etc) artist+title Wallpaper – alle image-type items (Wallpaper, Screensaver, Logos etc), naam+src ContentType – alle types content, met enkelvoud, meervoud en fake-directory naam ContentFamiliy – alle families, nu alleen nog ‘audio’ en ‘image’ (voor display routines) Category – category titels Collection – custom groepen: bijv. alle Sinterklaas items, van alle types bij elkaar Provider – look-up tabel, met elke content provider (voor de volledigheid, niet 100% geimplementeerd) OrderLog – eigen callback orderlog voor onze standaard provider, werk stand-alone maar met dezelfde orderCodes.
Basis versie
Dit is de basis zoals elegant zou zijn. •
• •
Omdat ringtone.orderCode en wallpaper.orderCode met een 0 kan beginnen is die kolom een VARCHAR. Dat is niet optimaal bij selecties, dus vandaar dat er nog steeds een INT auto increment primary key is. Bijna alles heeft een TINYINT display zodat je kan aangeven dat een item niet getoond wordt. Omdat een item in meerdere Collections kan zitten wordt dit gekoppeld via een koppeltabel. Category is een veld per item.
Problemen: Er zitten een paar haken en ogen aan dit model: •
•
•
•
Image en Ringtone zijn gekoppeld aan Collections met twee losse tabellen, en ze gebruiken ons eigen id-veld. Probleem: als om wat voor reden dan ook onze tabel wordt geleegd en weer gevuld kloppen de AI id’s niet meer, en zijn alle groepen corrupt. Je kan besluiten om dan maar nooit te legen, maar de ervaring leert dat de providers veel muteren, en het is dus aangeraden regelmatig alles te vernieuwen. Gelukkig kunnen we de orderCode gebruiken als unieke id. Lege categorien worden getoond, tenzij je bij elke lijst-generatie voor elke categorie gaat kijken of er ten minste 1 item (van 25.000+) in de rest van de selectie valt. Dat is langzaam, dus een cache is gewenst (en aangezien er een periodieke nachtelijke content update is kan dat prima daarna automatisch worden gedaan). De artiesten staan los in elk Ringtone item. Elegant zou zijn dit in ene losse tabel te doen, dan kan je eenvoudig alle items van 1 artiest ophalen, of de artiest’s naam centraal wijzigen. Het brakke datamodel van de provider geeft collisions met categorieën: de Category id’s worden voor verschillende types content herbruikt. (1 bij Realtone is ‘Dance’, maar bij Polyfone ‘Nederlands’.
Fixes: Collections
Hier is het collection deel veranderd: de twee koppel tabellen zijn vervangen door één: •
• •
Door de orderCode van de provider te gebruiken is onze eigen auto-increment id niet meer belangrijk binnen Collections, en kan je de content tabellen compleet refreshen zonder de groepen te verliezen (vandaar ook geen pijl naar Wallpaper en Ringtone, hoewel er wel een relatie is). Alle types content kunnen als Collection nu worden gegroepeerd in 1 tabel Het (redundant) contentType veld is handig voor stats en loops: om te kijken of er een groep inhoud van een bepaald type heeft hoeft de server niet de (nogal lange) content lijsten door te scannen, en is een cache niet nodig.
Fix: Artist Dit was nog een tegenvaller, en is nooit compleet opgelost. Het probleem was dat ik de artiesten in een losse tabel wil, met een relatie naar de items. Punt is alleen dat de content provider de artiest als een string-veld per content-item geeft, en niet met een id naar een gelinkte lijst. Een bijdehante scripter als ik paste dus de import routines aan: voor de INSERT van elk item wordt apart nog de artiest in een tabel gestopt (een UNIQUE veld), en in plaats daarvan de id ingevoegd bij het item. In theorie en testjes werkt dat prima. Het probleem is alleen de data zelf: hoe ga je om met alternatieve spellingen, typefouten etc? Ik kwam er achter bij de ‘Black Eye Peas’, die ook worden ingevoerd als ‘Black Eyed Peas’ (extra D). En natuurlijk ‘Jantje Smit’, die nu ‘Jan Smit’ heet, maar nog steeds dezelfde artiest is. Extra leuk zijn de combinaties van artiesten zoals bijv.: ‘Black Eyed Peas featuring Jay-Z’…. Blijkbaar hanteert de content provider dus zelf geen relatie. Je kan ervoor kiezen handmatig aan te passen, maar dat is te veel werk (het moet juist low-maintainance zijn). Ik heb met PHP’s Levenstein-distance en andere routines ge-expirirmenteerd, maar dat is of langzaam en onbetrouwbaar. We hebben nu besloten maar geen ‘meer van deze artiest’ te doen, of gewoon low-fi op tekst te zoeken (via PHP alle losse woorden uit de artiest plukken, en dan een SQL LIKE contructie). Het is lapwerk maar moet maar. Gelukkig is de doelgroep niet zo veeleisend, dus je komt er wel mee weg (was onze redenatie).
Fix: Category Collision Na een aantal artifacts in de bouwfase bleek een aanname (as usual, typisch leermoment) weer een probleem op te leveren. Op het moment van bouwen leek het dat de Categorie namen zoals de content provider de hanteert en levert uniek waren over alle content-types. In te praktijk bleek het niet te kloppen, en kwamen er rare mismatches tussen de text en id binnen links, en de inhoud van de daadwerkelijke selectie. Een simpele aanpassing is een extra content-type veld bij elke Category titel. Sommige categorie titels staan nu meerdere keren in de lijst (er staat 3x Dance bijv.), maar de id’s kloppen nu wel met de inhoud. (de aanpassing in het model is zo triviaal dat een extra schema onnodig is, maar het is verwerkt in volgende schema).
Fix: Lege categorieën
Het volgende probleem was de lege categorieën. Sommige categorie namen zijn al ingevoerd maar hebben nog geen content, of de content is verplaatst of verwijderd. Op het moment dat je de lijst categorieën selecteert (om een menu te maken) kan je kijken of er een item (van het content-type waarvoor je de lijst maakt) bestaat (en zichtbaar is) voordat je deze output. Maar omdat er 25000+ items in de Ringtone database staan kan dat wel uit de hand lopen met de zoektijd. De simpele oplossing is een cache tabel: na elke nachtelijke content update worden per content-type de oude records verwijderd uit FilledCats, en wordt er per categorie gekeken of er voor dat type tenminste één item zichtbaar is. Zo ja, dan wordt een record toegevoegd met het content-type en categorie id. Ook als er via de admin wijzigingen worden gedaan moet de cache update routine weer lopen (is dus eis aan de scripts) om er zeker van te zijn dat het up-to-date is.
Query’s 1: Basis: selecteer de top 50 realtones (contentType_id=3) SELECT ringtone.orderCode, ringtone.artist, ringtone.title FROM ringtone WHERE ringtone.contentType_id='3' AND ringtone.display='1' AND ringtone.provider='1' ORDER BY ringtone.ranking DESC LIMIT 0,50;
2: Selecteer alle categorieën met content van de realtones (contentType_id=3) SELECT category.id, category.title FROM category, filledCats WHERE filledCats.category_id = category.id AND category.contentType_id='3' AND filledCats.contentType_id='3' ORDER BY category.title ASC
3: Selecteer de top 50 realtones (op naam, via veld contentType_id.short) uit de Category 'Dance' (op naam, via category.title) SELECT ringtone.orderCode, ringtone.artist, ringtone.title FROM ringtone,contentType,category,filledCats WHERE ringtone.display='1' AND ringtone.provider='1' AND ringtone.contentType_id=contentType.id AND contentType.short LIKE 'realtone' AND category.contentType_id = contentType.id AND filledCats.contentType_id=contentType.id AND filledCats.category_id = category.id AND category.title LIKE 'Dance' AND ringtone.category_id = category.id ORDER BY ringtone.ranking DESC LIMIT 0,50;
4: Selecteer de top 50 realtones (contentType_id=3) uit een Collection (id=4) SELECT ringtone.orderCode, ringtone.artist, ringtone.title FROM ringtone,collectionContent WHERE ringtone.display='1' AND ringtone.provider='1' AND collectionContent.collection_id = '4' AND collectionContent.orderCode = ringtone.orderCode AND collectionContent.contentType_id='3' ORDER BY ringtone.ranking LIMIT 0,50;
Note: omdat ringtone.orderCode uniek is, hoeft ringtone.contentType_id niet gechecked te worden, deze komt al uit collectionContent
5: Selecteer de top 50 realtones (op naam, via veld contentType_id.short) uit de Collection 'Sinterklaas' (op naam, via collection.title) SELECT ringtone.orderCode, ringtone.artist, ringtone.title FROM ringtone,collectionContent,collection,contentType WHERE ringtone.display='1' AND ringtone.provider='1' AND collection.title LIKE 'Sinterklaas' AND collectionContent.collection_id = collection.id AND collectionContent.orderCode = ringtone.orderCode AND contentType_id.short LIKE 'realtone' AND collectionContent.contentType_id = contentType.id ORDER BY ringtone.ranking LIMIT 0,50;
Note: hoewel LEFT/RIGHT OUTER JOINS heel handig kunnen zijn vind ik ze erg slecht leesbaar bij meer dan 2 tabellen/voorwaarden. Natural joins zijn soms lange lijsten met voorwaardes, maar de simpele AND/OR combinaties geven geen haakjes 'brei', en zijn makkelijk te genereren via PHP (en door gewenning ook makkelijker te controleren). Ze passen ook beter bij mijn manier van denken: door alle voorwaarden los op te sommen en aan elkaar te plakken kan je vrij snel heel ingewikkelde selecties doen. De linker of rechter rest records van de OUTER JOIN heb ik tot nu toe nog niet nodig gehad. INNER JOIN gebruik ik niet, aangezien dat in een complexere syntax hetzelfde doet als Natural joins.
Extra’s De tabellen contentType en contentFamily en hun relaties zijn super belangrijk (het is de basis differentiatie) en voor elke pagina wordt vaak van contentType.short naar contentType.id gezocht (short gebruik ik voor virtuele directory’s). Daarom is het niet efficiënt om dat elke keer te queryën. Ik dump deze relatie daarom via PHP in een associatieve array (crossed) voor snel gebruik. Dat scheelt weer een JOIN: ongeveer het verschil tussen query 2 en 3, en tussen 4 en 5.
