Είναι γνωστό ότι το βράδυ έρχονται οι καλύτερες ιδέες σε όλους όσους ασχολούνται με την πληροφορική και ειδικά σε όσους από εμάς γράφουμε κώδικα. Τριάντα και χρόνια που κάνω αυτή δουλειά δεν έχω βρει τους λόγους που γίνεται αυτό.

Χθες το βράδυ όπως κάθε βράδυ έκανα την συνηθισμένη μου ημερήσια ανακεφαλαίωση στο SQLschool.gr και όπως κοίταζα την πρώτη σελίδα μου ήρθε μια σκέψη για να αυτοματοποιήσω τα sections της πρώτης σελίδας που δείχνουν τα sql nights και τα webcast videos ώστε αυτά χωρίς να κάνω κάτι να δείχνουν πάντα τα τελευταία.

Για όσους δεν το γνωρίζουν ή δεν με γνωρίζουν χρόνια θα πρέπει να επισημάνω ότι η σχέση με το κώδικα ξεκινάει από το 1985 και μέχρι και πριν αρκετά χρόνια έγγραφα καθημερινά κώδικα σχεδόν αποκλειστικά, πλέον δεν είναι αυτή καθημερινότητα μου. Επίσης να επισημάνω ότι ξεκίνησα να κάνω εκπαίδευση το 1999 στα dev tools της Microsoft και ήμουν από τους πρώτους που το 2000 δίδασκαν .net.

Για όσους πάλι δεν το γνωρίζουν το SQLschool.gr είναι όλο γραμμένο με τα χεράκια μου. Δεν έχει τίποτα έτοιμο, ακόμα και το CMS που υπάρχει πίσω από αυτό είναι δικής μου επινόησης και κατασκευής. Οι λόγοι που με οδήγησαν σε αυτό ήταν αρκετοί με βασικό λόγο ότι μπορώ ανά πάσα στιγμή να κάνω ότι θέλω όπως το θέλω χωρίς να περιορίζομαι από το οποιοδήποτε CMS. Αυτό έχει τα καλά του αλλά και τα κακά του καθώς το δικό μου CMS δεν έχει όλα τα καλούδια που έχουν όλα τα εμπορικά CMS και ότι αν θέλω να βάλω κάτι νέο θα πρέπει να βρω χρόνο να το φτιάξω αλλά δεν με απασχολεί πλέον αυτό, δεν έχω κάποιο αφεντικό πάνω από το κεφάλι μου.

Γιατί τα είπα όλα αυτά;

Η πρώτη σκέψη μου για να κάνω αυτό ήταν να ανοίξω το Visual Studio και να φτιάξω αυτό που ήθελα, αλλά βαριόμουνα (δεν το κρύβω). Κάθισα μερικά λεπτά και σκέφτηκα ποιος άλλος τεμπέλικος τρόπος θα έκανε αυτό που ήθελα. Τα όσα είπα παραπάνω βοήθησαν καθώς εύκολα μπορούσα να το κάνω αυτό με μια μικρή stored procedure.

Όλα τα videos που παρουσιάζονται μέσα στο SQLschool.gr είναι στο κανάλι μου στο youtube και δεν είναι κρυφό ότι είναι embedded, όπως δεν είναι κρυφό το πως το youtube τα υλοποιεί (iframe) και όλα είναι σε ένα πίνακα μέσα στο content html code που το CMS έχει, άρα ήταν εύκολο να κάνω ένα top(3) query ordered by date desc. Επίσης όλα στο SQLschool.gr είναι web controls που διαβάζουν περιεχόμενο από τη CMS database που έχω. ΒΑΜ ήξερα ακριβώς τι έπρεπε να κάνω και αυτό θα σας δείξω καθώς θεωρώ ότι θα δείτε αρκετά ωραία πράγματα που μπορείτε να κάνετε χωρίς πολύ κόπο.

Το να γράψω όπως είπα το query αυτό ήταν εύκολο καθώς το μόνο που ήθελα ήταν το htmlcode field

select top(3) htmlcode from dbo.Videos order by EntryDate desc;

Από αυτό έπρεπε να εντοπίσω την αρχή και το τέλος του youtube video link που είναι σε iframe. Άρα ήθελα ένα offsetstart και ένα offsetend για αυτό και επειδή ήθελα να κάνω ευανάγνωστο το κώδικα μου αλλά και ζωή μου ευκολότερη αποφάσισα να χρησιμοποιήσω την cross apply που να δημιουργεί ένα πίνακα για κάθε record με τις τιμές αυτών και μετά με μία ταπεινή substring να παίρνω το ζητούμενο.

select top(3) 
substring (htmlcode,oset.offsetstart,oset.offsetend-oset.offsetstart) as video_link
from dbo.Videos
cross apply (values (CHARINDEX('<iframe',HTMLCode),CHARINDEX('</iframe>',HTMLCode)+9)) as oset (offsetstart, offsetend)
order by EntryDate desc;

Αυτό μου επέστεφε τα δεδομένα μου ήθελα αλλά δεν με βόλευε καθώς έπρεπε να κτίσω ένα νέο html το οποίο και να κάνει update το record που του web control διαβάζει. Η εύκολη λύση είναι ο cursor αλλά δεν μου άρεσε καθώς έπρεπε να γράψω αρκετές γραμμές κώδικα και όπως είπα και παραπάνω βαριόμουν.

Το πως ήθελα να ήταν αυτός ο κώδικας ήταν γνωστό ας πούμε όμως ότι ήταν ένα table όπου στο εσωτερικό έπρεπε να μπει το iframe, αλλά επειδή απεχθάνομαι το string concatenation βάζω στις αντίστοιχες θέσεις ψευδοπαραμέτρους με την λογική @1, @2 και με string replace κάνω μια χαρά την δουλεία μου.

<table>
<tr>
<td >@1</td>
<td>@2</td>
<td>@3</td>
</tr>
</table>

Το γεγονός ότι είχα τα δεδομένα σε γραμμές δεν με βοηθούσε καθώς για να αποφύγω το cursor έπρεπε να τα έχω σε variables αλλά για να τα έχω σε variableς έπρεπε να γυρίσω τις γραμμές σε κολώνες και αυτό το κάνει μια χαρά η pivot.

Υπήρχε ακόμα ένα πρόβλημα ότι θα έπρεπε να αλλάξω το width, height του iframe σε κάτι συγκεκριμένο αλλά αυτό λύθηκε εύκολα με την χρήση της STUFF

select top(3) 
stuff(substring (htmlcode,oset.offsetstart,oset.offsetend-oset.offsetstart),9,24,'width="320" height="200"') as video_link
from dbo.Videos
cross apply (values (CHARINDEX('<iframe',HTMLCode),CHARINDEX('</iframe>',HTMLCode)+9)) as oset (offsetstart, offsetend)
order by EntryDate desc;

Οπότε είχε μείνει να χρησιμοποιήσω την PIVOT όμως αυτή χρειάζεται κάτι που να είναι σαν οδηγός για να κάνει το pivot. Και αυτό ήταν εύκολο να λυθεί μένα τεχνικό πεδίο όπως παρακάτω

select top(3) 
'video'+cast (ROW_NUMBER() over(order by (select null)) as nchar(1)) as v,
stuff(substring (htmlcode,oset.offsetstart,oset.offsetend-oset.offsetstart),9,24,'width="320" height="200"') as video_link
from dbo.Videos
cross apply (values (CHARINDEX('<iframe',HTMLCode),CHARINDEX('</iframe>',HTMLCode)+9)) as oset (offsetstart, offsetend)
order by EntryDate desc;

Από εκεί και πέρα ο δρόμος ήταν εύκολος για την PIVOT

select [video1],[video2],[video3],[video4] 
from         
(
    select top(3) 
    'video'+cast (ROW_NUMBER() over(order by (select null)) as nchar(1)) as v,
    stuff(substring (htmlcode,oset.offsetstart,oset.offsetend-oset.offsetstart),9,24,'width="320" height="200"') as video_link
    from dbo.Videos
    cross apply (values (CHARINDEX('<iframe',HTMLCode),CHARINDEX('</iframe>',HTMLCode)+9)) as oset (offsetstart, offsetend)
    order by EntryDate desc
) as c
pivot (max(video_link) for v in ([video1],[video2],[video3],[video4])) as p

To μόνο που χρειάζονταν να γίνει ήταν να μπουν αυτά σε variables

declare @video1 nvarchar(4000),@video2 nvarchar(4000),@video3 nvarchar(4000);
select @video1 = video1, @video2 = video2, @video3 = video3
from         
(
    select top(3) 
    'video'+cast (ROW_NUMBER() over(order by (select null)) as nchar(1)) as v,
    stuff(substring (htmlcode,oset.offsetstart,oset.offsetend-oset.offsetstart),9,24,'width="320" height="200"') as video_link
    from [dbo].[WebCasts]
    cross apply (values (CHARINDEX('<iframe',HTMLCode),CHARINDEX('</iframe>',HTMLCode)+9)) as oset (offsetstart, offsetend)
    order by EntryDate desc
) as c
pivot (max(video_link) for v in ([video1],[video2],[video3],[video4])) as p

Το προτελευταίο βήμα ήταν να μπουν στο html που ήθελα και αυτό ήταν εύκολο με REPLACE

set @html = REPLACE(@html,'@1',@video1);
set @html = REPLACE(@html,'@2',@video2);
set @html = REPLACE(@html,'@3',@video3);

Το τελευταίο βήμα ήταν ένα απλό record update και όλο αυτό να γίνει μια stored procedure που έγινε schedule να τρέχει μια φορά την ημέρα.

Ο σκοπός του συγκεκριμένου άρθρου δεν είναι για να σας δείξω το πόσο καλός είμαι και το πόσο εύκολα λύνω προβλήματα. Δεν είναι σε καμία περίπτωση αυτό.

Αυτό που θέλω να δείξω, και θεώρησα ότι αυτό είναι ένα καλό παράδειγμα, είναι ότι η απλή σκέψη πάντα είναι καλύτερη.

Επίσης ήθελα να επισημάνω για ακόμα μια φορά την σπουδαιότητα της CROSS APPLY, την χρήση της STUFF, το πως μπορείς να χρησιμοποιήσεις την PIVOT χωρίς να έχεις aggregation αλλά και το πως απλά μπορείς να αυτοματοποιήσεις εργασίες που κάνουν την ζωή όλων μας καλύτερη.

//antonch