Pro­gram­ming: Go

This is­sue, we’ll in­te­grate an SQL database with our video shar­ing server to en­able key­word search­ing

Linux User & Developer - - Contents -

In part 3, we in­te­grate an SQL database with our video-shar­ing server to en­able key­word search­ing

Over the last two is­sues, we’ve been build­ing up a web server that en­ables peo­ple to up­load videos with ti­tles. The only slight lim­i­ta­tion is the abil­ity to browse videos. The cur­rent im­ple­men­ta­tion puts a long list of all the up­loaded videos on to the home page, but this is clearly go­ing to be im­prac­ti­cal if the site be­comes pop­u­lar enough. A bet­ter ap­proach is to im­ple­ment some kind of search, so that peo­ple can more eas­ily find the videos they’re in­ter­ested in.

It’s not hard to think of ways we could do this us­ing our ex­ist­ing in­fra­struc­ture: for ex­am­ple, by sav­ing a list of key­words into our video­data.txt files, and per­haps main­tain­ing some form of in­dex in or­der to help speed up search us­ing the file sys­tem.

A bet­ter ap­proach, how­ever, is to re­alise that our cur­rent setup is inad­e­quate for what we want it to do, and that we should use a tool de­signed for stor­ing and re­triev­ing data in this way: an SQL database.

If you’re join­ing the se­ries this is­sue, you can find a com­plete set of code on the coverdisc and the FileSilo. We will use this code as the start­ing point for this ar­ti­cle.

Set up the database

The first step is to set up the database. If you have a MySQL im­ple­men­ta­tion in­stalled al­ready, you can use that. Oth­er­wise, we will be us­ing Mari­aDB, a free soft­ware

fork of MySQL. Once it’s in­stalled, you should run the fol­low­ing com­mand to per­form some in­tial setup:

# mysql_in­stal­l_db --user=mysql --basedir=usr --datadir=/var/lib/mysql

Af­ter this, start the Mari­aDB ser­vice with ser­vice mysql start or sys­tem­ctl start mari­adb de­pend­ing on your distri­bu­tion. You can now bring up the Mari­aDB prompt by run­ning the fol­low­ing com­mand in a com­mand win­dow:

$ mysql -u root -p

You should be prompted to en­ter the pass­word for the root SQL user. If you’ve just in­stalled Mari­aDB, a blank pass­word should work; you can change this later. To start with, we’ll cre­ate a database called lu­tube and a user, also called lu­tube, who will be re­spon­si­ble for ad­min­is­ter­ing it:

> CRE­ATE DATABASE 'lu­tube';

> CRE­ATE USER 'lu­tube'@'lo­cal­host'; > GRANT ALL PRIV­I­LEGES ON 'lu­tube'.* TO 'lu­tube'@'lo­cal­host';

Then run \q to close this in­stance. Re-run Mari­aDB us­ing the lu­tube user by run­ning the com­mand mysql -u lu­tube. Run the com­mand USE lu­tube; to switch to the database we have created. First, we will cre­ate a ta­ble to store in­for­ma­tion about all the videos on the site:

> CRE­ATE TA­BLE Videos (

> ID var­char(100) NOT NULL PRI­MARY KEY, > Ti­tle var­char(200)

> );

This cre­ates a ta­ble with two col­umns, ID and Ti­tle, which we should be fa­mil­iar with al­ready. Keep this win­dow open so we can see how the database changes as our pro­gram op­er­ates on it.

In­te­grate the database with Go

Go pro­gram­mers can in­ter­act with SQL data­bases us­ing the database/sql pack­age. How­ever, since this pack­age is in­tended to work as gen­er­ally as pos­si­ble, it does not pro­vide any code for deal­ing with in­di­vid­ual database im­ple­men­ta­tions. We’ll down­load the Go MySQL driver, since Mari­aDB is a MySQL im­ple­men­ta­tion. We can do this by run­ning the fol­low­ing com­mand:

$ go get

In or­der to use this pack­age, we need to add the fol­low­ing two im­ports into the im­port state­ment at the top of lu­tube.go:

"database/sql" _ ""

Do not for­get the un­der­score, which means that we

are im­port­ing the pack­age for its side ef­fects only. Go in­ter­acts with the database through an ob­ject of type sql.DB. To avoid hav­ing to pass this around a lot, let’s make it a global vari­able by putting the fol­low­ing dec­la­ra­tion out­side the scope of any func­tion.

var videoDatabase *sql.DB

Now let’s write some func­tions for set­ting up this database, and for clos­ing it again. Sam­ple func­tions are shown in Fig­ure 1. The string "mysql" that we pass into sql.Open refers to the par­tic­u­lar SQL driver pack­age that we have cho­sen.

We now call these func­tions at the very start of our main func­tion:

openVideoDatabase("lu­tube@/lu­tube") de­fer closeVideoDatabase()

The string lu­tube@/lu­tube refers to the database we created ear­lier. You should deal with the er­ror value re­turned from openVideoDatabase, per­haps by pass­ing it to panic if it is not nil and im­me­di­ately re­turn­ing.

Work with SQL state­ments

In or­der to get to grips with the Go-SQL in­te­gra­tion, we’ll write a short tool to mi­grate our ex­ist­ing videos to the new SQL sys­tem. Since this will be a sep­a­rate pro­gram, we’ll need to put the com­mon func­tions into a sep­a­rate file. It makes sense to have a new file to hold all the func­tions used for stor­ing and read­ing videos, so cre­ate a new file called videos.go, and move into it the two new func­tions open/closeVideoDatabase that we have just writ­ten, plus the func­tions load­Video, saveVideo and getA­vail­ableVideos that were al­ready there. Also move the videoDatabase global vari­able and the def­i­ni­tion of the Video struct into videos.go. Put pack­age main at the top of the file, as in lu­tube.go.

This will mess up the pack­age im­ports a bit: we still need to write an im­port state­ment for videos.go, and

To get to grips with Go-SQL in­te­gra­tion, we’ll write a short tool to mi­grate our ex­ist­ing videos

there are some im­ports in lu­tube.go that are only used in the func­tions we have moved across, so these need to be re­moved. The eas­i­est way to see what needs to go and what can stay is to try to com­pile the orig­i­nal pro­gram us­ing the com­mand:

$ go build lu­tube.go videos.go

and to look at the er­ror mes­sages. You should also move the im­port of the SQL driver mysql from lu­tube.go into videos.go.

The first step for our mi­gra­tion tool is to add an­other func­tion in videos.go called saveVideoToDatabase, which will take a video and store its ID and ti­tle in the Videos ta­ble that we created ear­lier. First write the func­tion header:

func saveVideoToDatabase(video *Video) er­ror


// body goes here


In or­der to com­mu­ni­cate with the database, we use the Exec, Query and QueryRow func­tions from the sql pack­age. We use Exec when we want to ex­e­cute an SQL state­ment and don’t ex­pect to get any in­for­ma­tion back, QueryRow when we ex­pect to get back a row of data and Query when we ex­pect to get mul­ti­ple rows.

Since the saveVideoToDatabase func­tion does not need to get any in­for­ma­tion out of the database, we use Exec. We write the body of the func­tion as fol­lows.

_, err := videoDatabase.Exec("INSERT INTO Videos VAL­UES (?, ?)", video.Id, video.Ti­tle)

re­turn err

The string that we pass into Exec is an SQL state­ment for in­sert­ing val­ues into a ta­ble, and the two ques­tion marks are place­hold­ers that will be filled with the val­ues video.Id and video.Ti­tle that we have passed into the func­tion. If you are un­fa­mil­iar with SQL, don’t worry – we’ll be pro­vid­ing all the com­mands that you need and ex­plain­ing them as we go.

Use the database

All we need to do now is to write the mi­gra­tion tool. For this, we cre­ate a new file called mi­grate.go, and copy in the main func­tion from Fig­ure 2, as well as the pack­age main dec­la­ra­tion at the top. The main func­tion uses the func­tions we’ve writ­ten to it­er­ate over the videos on the file sys­tem, sav­ing them all to the new database. Com­pile the pro­gram with go build mi­grate.go videos.go and run it with ./mi­grate.

You should be able to check that this has worked by go­ing back to your Mari­aDB win­dow and typ­ing the com­mand SE­LECT * FROM Videos;. If ev­ery­thing has worked, you should see a ta­ble as in Fig­ure 3. This means the videos have mi­grated suc­cess­fully to the database.

We need to mod­ify the be­hav­iour of the server so that it uses the new database, rather than the file sys­tem­based setup we had be­fore. To do this, we will need to re­write a num­ber of the func­tions in videos.go (a side ef­fect of this is that our mi­grate tool will no longer work, but that’s fine).

Let’s start with the load­Video func­tion. First, delete the ex­ist­ing con­tents of the func­tion: we’re go­ing to re­write it com­pletely. load­Video takes in a pa­ram­e­ter, id, for the ID of the video to load. The first step is to run an SQL state­ment that will re­turn us the cor­re­spond­ing row of the ta­ble:

row := videoDatabase.QueryRow("SE­LECT * FROM Videos WHERE ID = ?", id)

Next, we cre­ate a new Video ob­ject and use this row to pop­u­late it:

var video Video err := row.Scan(&video.Id, &video.Ti­tle)

Deal with the er­ror in the usual way. Lastly, we can re­turn (a pointer to) the video, com­plet­ing the func­tion:

re­turn &video, nil

When rewrit­ing the saveVideo func­tion, we want to keep

the first few lines, which deal with sav­ing the video file to the file sys­tem. But we should delete the sub­se­quent lines, which save the video’s ti­tle into the video­data.txt file. In­stead, we want to save the video to the database. Luck­ily, we’ve al­ready writ­ten a func­tion to do this, so we can just write:

err = saveVideoToDatabase(video) re­turn err

The last func­tion that you need to change should be getA­vail­ableVideos. Pre­vi­ously, we im­ple­mented this func­tion by search­ing for di­rec­to­ries in the videos folder, but it is more ro­bust to get them from the database. Once again, clear out the ex­ist­ing con­tents of the func­tion – we’re go­ing to re­write it com­pletely.

First, we use the Query func­tion to send an SQL state­ment that will re­turn the list of all the rows in the Videos ta­ble:

rows, err := videoDatabase.Query("SE­LECT * FROM Videos")

Deal with the er­ror as you see fit. This query will re­turn the list of all the IDs of videos in the ta­ble.

getA­vail­ableVideos should re­turn a slice of point­ers to Videos. First, we cre­ate an empty slice (as in the orig­i­nal im­ple­men­ta­tion of this func­tion):

avail­ableVideos := make([]*Video, 0)

We are now go­ing to it­er­ate over the ob­ject rows, us­ing a for loop. For each it­er­a­tion of this loop – that is, for each row of the ta­ble – we cre­ate a new Video ob­ject and use the row of the ta­ble to fill in its fields, be­fore adding the video to the list:

for rows.Next() { var video Video err := rows.Scan(&video.Id, &video.Ti­tle) avail­ableVideos = ap­pend(avail­ableVideos, &video)


Last, we re­turn the new list of avail­able videos:

re­turn avail­ableVideos, nil

Re­com­pile the code with go build lu­tube.go videos. go. If you have done ev­ery­thing right, you should get no er­rors, apart from a mes­sage telling you you need to re­move the im­port io/ioutil from videos.go. Re­move this im­port and then re­com­pile the pro­gram.

Run ./lu­tube, then fire up http://lo­cal­host:8080 in a browser and check that you are still able to play and up­load videos, and that up­loaded videos ap­pear on the home page when you reload it. If ev­ery­thing is work­ing as be­fore, then we can move on to the next step. Oth­er­wise, check your work­ing and any er­ror-han­dling code you may have in­tro­duced.

We want to be able to search for videos by key­words: each video will have a num­ber of key­words as­so­ci­ated with it, and we should be able to search for them and be pre­sented with a list of all the match­ing videos.

Key­word search

The first step is to cre­ate a new ta­ble in our lu­tube database. In­side your Mari­aDB com­mand win­dow, run the com­mand shown in Fig­ure 4 to cre­ate the ta­ble.

This ta­ble will have one row for ev­ery key­word from each video on the server. In par­tic­u­lar, each video will have mul­ti­ple rows in the ta­ble cor­re­spond­ing to it, one for each video key­word, and each key­word will oc­cur in mul­ti­ple rows of the ta­ble, once for each video where it ap­pears. The ID field is a con­ve­nient counter used to uniquely iden­tify rows of the ta­ble, and the FOR­EIGN KEY spec­i­fi­ca­tion en­sures that the VideoID field is in­deed the ID of a video liv­ing in the Videos ta­ble.

We haven’t writ­ten any func­tion­al­ity to add key­words to videos through the web in­ter­face, so spend some time adding them through the com­mand-line MySQL in­ter­face in­stead. For ex­am­ple:

> INSERT INTO Key­words (Word, VideoID) > -> VAL­UES

> -> ('ele­phant', 'zoo'),

> -> ('flamin­gos', 'zoo'),

> -> ('carousel', '383618752');

Use SE­LECT * FROM Key­words to look at your hand­i­work when you have fin­ished.

Now we can start im­ple­ment­ing the search. First, we write the func­tion (in videos.go) that’s go­ing to take the

We need to mod­ify the be­hav­iour of the server so that it uses the new database, rather than a file sys­tem-based setup

list of key­words and con­vert it into search re­sults. The func­tion will take in a list of key­words and re­turn a slice of point­ers to ap­pro­pri­ate Video ob­jects:

func getMatch­ingVideos(key­words []string) ([]*Video, er­ror) {



The first thing this func­tion needs to do is to cre­ate a new slice to hold the videos:

videos := make([]*Video, 0)

Next, it should build up the SQL query that will get us the search re­sults. One slightly com­pli­cated bit is that the num­ber of key­words is vari­able, so we need to add some code to add a vari­able num­ber of ques­tion marks into the query! See the main im­age on the first page for an ex­am­ple (lines 100 to 107). In this bit of code, we’ve used back­ticks `...` rather than in­verted com­mas "..." for string de­lim­iters, which al­lows us to split a string across mul­ti­ple lines. We need to im­port the "strings" li­brary in or­der to use strings.Re­peat, which we have used to re­peat the string ,? for ev­ery key­word from the sec­ond to the last, giv­ing us a string like ?,?,?,?.

One other thing we need to do is to use cast­ing to con­vert our list of strings into a list of in­ter­face{} ob­jects, since the Query func­tion ex­pects ar­gu­ments of this type:

in­ter­faceKey­words := make([]in­ter­face{}, len(key­words)) for i := range key­words {

in­ter­faceKey­words[i] = in­ter­face{} (key­words[i])


We can now call the Query func­tion to get the rows of the ta­ble cor­re­spond­ing to videos that match the key­words:

rows, err := videoDatabase.Query(sqlQuery, in­ter­faceKey­words...)

The el­lip­sis ... af­ter key­words is be­cause Query ex­pects a vari­able num­ber of ar­gu­ments, whereas we have our key­words stored in an ar­ray or slice.

Af­ter deal­ing with the er­ror, we use a for loop to pop­u­late the videos slice. See Fig­ure 7 for an ex­am­ple, with er­ror-han­dling stripped out. We can re­turn it with re­turn videos, nil.

Up­grade the web in­ter­face

The next step is to in­te­grate all this with the web in­ter­face. Open the HTML tem­plate home.html and add a short form on it for car­ry­ing out searches:

<form id="search-form" ac­tion="/search/" method="post">

<in­put type="text" name="key­words" />

<in­put type="sub­mit" value="Search Lu­Tube"/> </form>

We will need to cre­ate a new HTML tem­plate called search_re­sults.html, which will print out a list of

match­ing videos. The code on this tem­plate is very sim­i­lar to the code that prints out the list of all the videos on the home page; take a look at Fig­ure 8 for an ex­am­ple im­ple­men­ta­tion.

Last, we need to han­dle the search it­self. The form we just added sends its re­quest to the /search/ URL, so we should write a new han­dler func­tion called searchHan­dler in­side lu­tube.go to deal with that:

An easy way to add key­words for a video au­to­mat­i­cally is to ex­tract them from the video’s ti­tle at up­load time

func searchHan­dler(writer http.Re­spon­seWriter, re­quest *http.Re­quest) {

// ...


First, this func­tion should parse the form in or­der to be able to read the data:

err := re­quest.ParseForm()

Af­ter deal­ing with the er­ror value, we can then get the key­words that were typed on to the form:

key­wordList := re­quest.For­mValue("key­words") key­words := strings.Split(key­wordList, " ")

We have used strings.Split to split up the string that the user typed into in­di­vid­ual words. This means that we have to im­port the "strings" li­brary into lu­tube.go.

Next, we pass these key­words into our getMatch­ingVideos func­tion, us­ing the re­turned list of Videos to ren­der the search_re­sults tem­plate. Strip­ping out er­ror han­dling, we end up with code that looks some­thing like this:

Above Go’s use­ful HTML tem­plate li­brary makes it easy to dis­play a list of search re­sults that link to the ap­pro­pri­ate videos, like this

match­ingVideos, _ := getMatch­ingVideos(key­words)

ren­derTem­plate(writer, "search_re­sults", match­ingVideos)

Lastly, we need to reg­is­ter this han­dler to the search ac­tion by adding a new line of code to the main func­tion:

http.Han­dleFunc("/search/", searchHan­dler)

You can add proper er­ror han­dling through one of our spe­cial ‘han­dler’ func­tions from the last is­sue if you feel com­fort­able with that.

Fin­ish off

There’s just one last thing to do: cur­rently, we are only able to add key­words to the video li­brary by in­ter­act­ing di­rectly with the Mari­aDB con­sole. It would be nice if we could insert key­words au­to­mat­i­cally when up­load­ing videos. The eas­i­est way to do this is to split up the video ti­tle into words and add them to the database, which we can do by adding the code in Fig­ure 9 into the up­load­Han­dler func­tion, just be­fore the call to http. Re­di­rect right at the end.

Com­pile with go build lu­tube.go videos.go and fire up http://lo­cal­host:8080 in a web browser. If your code is all work­ing, you should now be able to search for videos us­ing the key­words stored in our SQL database.

Hope­fully this se­ries has given you an in­sight into how Go works as a lan­guage, as well as pro­vid­ing one so­lu­tion (out of many, many ap­proaches) to build­ing a work­ing video server. If you go even fur­ther and add ex­tras, let us know – we’d love to see the re­sults!

Above Go in­cludes a com­pre­hen­sive li­brary for com­mu­ni­cat­ing with data­bases us­ing the SQL lan­guage. Here, we have mixed in some Go code with the SQL in or­der to pro­gram­mat­i­cally build up the query

Above The global vari­ablevideoDatabase will be the ob­ject through which we per­form all com­mu­ni­ca­tion with the database

Above With the func­tions we have writ­ten, it’s pos­si­ble to write a tool to mi­grate our ex­ist­ing video li­brary with only a few lines of code

Above Mari­aDB’s com­mand line in­ter­face makes it easy to view the con­tents of database ta­bles or queries

Above The FOR­EIGN KEY con­straint is a use­ful san­ity check to make sure that the VideoID col­umn only con­tains valid video IDs, so we don’t get tripped up

Above Out­put from the state­ment in Fig­ure 5. We can usethe OR­DER BY key­word to rank rows by the num­ber of matches if we want to

Above We can add tem­po­rary ex­tra col­umns to a ta­ble that are com­puted from ex­ist­ing col­umns, such as the Matches col­umn shown here

We have to cre­ate a dummy vari­able, matches, to store the num­ber of match­ing key­words. You might like to save this vari­able and dis­play it on the re­sults screen

Above The eas­i­est way to pop­u­late the Key­words ta­ble is to insert all the words from the ti­tle when the video is up­loaded

Newspapers in English

Newspapers from UK

© PressReader. All rights reserved.