[SOLVED] database SQL Microsoft Word Document5

Microsoft Word Document5

Exercise5
Inexercise4,youhopefullyrealizedhowcumbersomeitwas
writingyourownrawSQLqueriestointeractwithyourdata!
Forthisexercise,youllmoveawayfromwritingSQLqueries
directly,andinsteadinteractwithyourdatabaseviaanORM.

LearningOutcome
Bytheendofthisexercise,youshouldbeveryfamiliarwith
howtointeractwithadatabaseprogrammaticallyusingan
ORMinnode.js.

Prerequisites
Inthisassignment,youllbeusingtheSequelize.js(V3)library
tocreateandinteractwithyourDB(stillreading/writingtoan
SQLitedatabase).
Togetfamiliarwithhowtoprogrammaticallyinteractwiththe
SequelizeORM,checkoutthedemocodefromclass,and/or
checkoutthistutorial.
TheSequelizelibrarymakesheavyuseofPromises(the
solutiontocallbackhell).IfyoureunfamiliarwithPromises,I
suggestrunningthroughatutorialsuchasthisorthis.

Gettingstarted
Youshouldbuildontopofwhatyouhaveforexercise3(ifyou
haventcompletedexercise3,youllneedtodoenoughofitto
satisfythisexercisesrequirements,butdoesnotneedtosatisfy
therequirementsofexercise4oranypriorexercise).
Thereasonwhyyourebuildingoffexercise3andnot4is
becauseyourerepeatingwhatyoudidinexercise4,exceptin
adifferentway.
Youwillneedtoloadtheexternalnode.jsdependenciesfor
Sequelize.Runthefollowinginyourterminal/command
prompt:
npminstallsavesequelize

(thesaveflagwillautomaticallyaddsequelizeasa
dependencyinyourpackage.jsonfile).

Task
YourobjectivewillbeforallofyourexistingGETAPIsto
interactwithyourdatabaseviatheSequelizeORM,ratherthan
theSQLitelibraryfromthepreviousexercise.Ihighly
recommendgettingstartedbyusingthedemocodefromclass,
orbyfollowingthisexamplecode.
WhenyouupdateyourGETrequeststoworkwiththe
database,yourMusicAppshouldcontinuetoworkasnormal
anypartsoftheUIthatusetheseAPIs.
Creatingyourmodels
Youwillbesettingupyourmodelstomatchthesameschema
youhadfromexercise4.
Inthedemocodefromclass,therewasjustamodelforSongs.
YoullhavetocreateaPlaylistmodel.
Note:youshouldnotneedtodefineyouridamodelisgiven
anidasitsprimarykeybydefaultinSequelize.
Youllalsohavetosetupassociationswithyourmodels.Fora
ManyToManyrelationship(whichisthecaseherebecause
songscanbelongtomanyplaylists,andplaylistscancontain
manysongs),youllwanttousethe.belongsToManymethod
tosetupyourassociations.
Note:youdo*not*needtomanuallycreateaSongsPlaylists
model(unlikelastassignmentwhereyouhadtomanually
associatethedatawithatableyourself).Itwillautomatically
getcreatedforyouaslongasyousetupyourbelongsToMany
associations.(Hint:youllwanttodothissimilarlytohowit
wasdoneinthisexampleexceptusingbelongsToMany
insteadofbelongsTo.Theclassdemoisbasedoffthislinked
example).
TofurtherunderstandassociationsinSequelize,readthough
this.

Creatingyourdatabase
Inyourpreviousassignment,yourdatabasewascreatedby
runningtheCREATETABLESQLquery.Thistime,youwont
interactwithyourdatabasedirectlythedatabaseandits
tableswillbecreatedforyouautomaticallybasedonthe
modelsyoudefine.
Whenyourmodelsareallsetup,runningnode
populateDb.jsshouldcreateaSQLitedatabasedsaved
music.dbdatabasewithyourSong,Playlist,andSongsPlaylists
table,andthedatashouldbepopulatedbywhatisin
songs.jsonandplaylists.json.(inthesamplecode,thedatabase
anddatabasefileusedbySequelizeisspecifiedin
models/index.js).
Youllneedtocreateyoursongsandplaylistsvia
models.Song.createandmodels.Playlist.create.Youll
needtouseplaylistInstance.addSongtoaddsongsto
yourplaylists.
Note:youdo*not*havetouseyourdatabasefromexercise4.
Youllbestartingfreshwithcreatinganewdatabaseusingthe
SequelizeORM.GettinganORMworkingwithanexisting
databaseisabitofapain.Butifyouareinterestedin
understandinghowtodoit,seethistutorial.
UpdatingyourAPIs
GET/API/SONGS
InsteadofloadingtheJSONfromdisk,youshouldbecrafting
theresponseyourselfbypullingthedatafromthedatabase
usingyourORMandcreatingaJSONobjectfromitthat
matchestheformalfromthepreviousassignment.
ThisshouldreturntheexactsameJSONresponseastheone
fromexercise3.
GET/API/PLAYLISTS
SameasabovethisshouldreturntheexactsameJSON
responseastheonefromexercise3.
Remember:thePlaylistmodelitselfdoesnotcontainthe

listsofsongs.Forthat,youllneedtouse
playlistInstance.getSongs()(oranotherORMmethod
forgettingsongsjustmakesuretousetheORMandnot
directSQLcalls!).
POST/API/PLAYLISTS
DontworryaboutupdatingthePOSTrequestinthisexercise.
Thiswillcomeinafutureexercise.
Yes,thatmeansyourMusicAppwillnotbeabletoaddnew
playlists,oraddnewsongstoexistingplaylistsbecauseyour
POSTswillnotinteractwithyourdatabase.Wewillgettothat
inalaterexercise.

RequirementsChecklist
nodepopulateDb.jscreatesaSQLitedatabasecalled

music.db,populatedbythedatafromsongs.jsonand
playlists.jsonfromexercise3,usingonlyORMmethodsto
createtheentriesinthedatabases(norawSQLqueries
allowed).(3marks)

YourGET/api/playlistspullsdatafromthedatabase
viaORMmethodcalls(norawSQLqueriesallowed),and
returnsthesamedataasitdidinexercise3(matchingthe
contentinplaylists.json).(3marks)

YourGET/api/songspullsdatafromthedatabasevia
ORMmethodcalls(norawSQLqueriesallowed),and
returnsthesamedataasitdidinexercise3(matchingthe
contentinsongs.json).(3marks)

YourMusicAppworkswiththeseupdatedGETAPIs
(meaningsongs,playlists,andsongsinplaylistsare
successfullyloadedintoyourUImatchingthemocksfrom
thepreviousexercises).(1mark)

Total:10marks.

Importantnotes
Yournode.jsappmuststartbyrunningnpminstall

&&nodepopulateDb.js&&npmstart.Using

differentfilenameswillresultinalossof3marks.Ifthis
commanddoesnotrunyourapplicationsuccessfully,we
willnotattempttofigureoutwhy,whichwillresultina
gradeofzero.Makesureyoudeclareyournode.js
dependenciesproperlyinyourpackage.jsonfile!Your
programmustalsoworkwithNode.js6.X.Specifyexactly
whatversionofnodeyouusedinaREADME.mdfile.

Youmayuseexpress.js(node.jsbackendframework)in
thisexercise.Usingexpress.jscanhelpcleanupyour
codebasesignificantly,soIdhighlyrecommend
incorporatingitintoyourmusicapp!

Youdonothavetoworryaboutresponseefficiency.By
that,Imeandontworryifyournotusingstreamsto
respondtorequestssendingbufferedresponsesisfine
fornow(althoughIhighlyencourageyoutocheckout
streams!).

TheuseofrawSQLqueriesinthisexercisewill
resultyouinagradeof0.Thegoalofthisexerciseistolearn
aboutORMs!