410 likes | 562 Views
Your MySQL Database: The Undiscovered Country. Agenda. How to get in? How to find your way ? What's possible without losing support ? What's not possible without losing support ?. Agenda. How to get in? How to find your way ? What's possible without losing support ?
E N D
Agenda • Howtoget in? • Howto find yourway? • What'spossiblewithoutlosingsupport? • What's not possiblewithoutlosingsupport?
Agenda • Howtoget in? • Howto find yourway? • What'spossiblewithoutlosingsupport? • What's not ... andyetsometimesneedstobedone? • A closedopen interface? • Two Enhancementrequests
Interactively sfxlcl41> mysql –usfxlcl41 –p Enter password: Welcome to the MySQL monitor. […] 16:35 (none)> • The MySQL userof an SFX instanceusuallybearsthe same nameasthecorresponding UNIX user. • The respectiveMySQL passwordisusually just settotheusernamebythe SFX installer. • Preferably, youshould log in asuserof a local SFX instance (e.g. as "sfxlcl41" like in thescreenshotabove)!
Interactively(continued) 16:35 (none)> use sfxlcl41; Reading table information […] Database changed 16:37 sfxlcl41> • After successfulloginyouneedtochoose a databasetoworkwith. • In caseofdoubt: database = user = password • All usershavereadpermission on all databases. • Write permissionisgrantedto sfxglb41 on all databases,andtoeachoftheotherusers on "their" owndatabaseonly.
Wrapper script MySQL userunknown, passwordforgotten? – Don'tworry! Module Manager::Connectionis all youreallyneed: • Buildyour SQL statement, • insertitinto a Perl wrapper scriptthatestablishes a databaseconnectionbyusingmoduleManager::Connection, and • runthe wrapper script. (Redirect theoutputinto a file in caseyouexpect a longerlistofresults.)
Would Perl::DBI workaswell? In principleyes, butestablishingthedatabaseconnectionbyyourselfmeans • yourscriptcontainsthe MySQL userasplaintext • yourscriptcontainsthe MySQL passwordasplaintext • characterencodingusuallyneedstobesetexplicitelybysubmitting a SET NAMES 'utf8';asfirstcommand All thisistakencareofbyManager::Connection!
show tables lists all tablesofthecurrentlychosendatabase: 16:37 sfxlcl41> showtables; +------------------------------------------+ | Tables_in_sfxlcl41 | +------------------------------------------+ : : | LCL_OBJECT_PORTFOLIO_INVENTORY | | LCL_OBJECT_PORTFOLIO_LINKING_INFO | | LCL_SERVICE_INVENTORY | | LCL_SERVICE_LINKING_INFO | : : +------------------------------------------+ 48 rows in set (0.00 sec)
showtablesas sfxglb41 yields a completely different listoftables: 16:39 sfxglb41> showtables; +------------------------------------------+ | Tables_in_sfxglb41 | +------------------------------------------+ : : | KB_OBJECT_PORTFOLIOS | : : | KB_TARGET_SERVICES | : : +------------------------------------------+ 78 rows in set (0.01 sec)
Database architecture in SFX 3 sfxglb3 sfxlcl3
SFX 4: Lessismore! • Key changeofarchitecture: databaseof a localinstancenolongercontains afullcopyofthe global KB, ... • ... but onlytheactivationstatusandanylocalfields (e.g. thresholds, parse params) • Result: Lessredundantlyuseddiskspaceallowsfor • morecontent in the global KB (e-books, authornames, etc.) • fasterrevisionupdates • morefrequentrevisionupdates
Database architecture in SFX 4 sfxglb41 sfxlcl41 KB_*,e. g.KB_TARGETS LCL_*_LINKING_INFO LCL_*_INVENTORY
desc(ribe) describesthecolumnsof a table: 16:40 sfxglb41> desc KB_OBJECT_PORTFOLIOS; +------------------------+------------------------+- | Field | Type |… +------------------------+------------------------+- | OP_ID | bigint(20) unsigned |… | TARGET_ID | bigint(20) unsigned |… | TARGET_SERVICE_ID | bigint(20) unsigned |… | OBJECT_ID | bigint(20) unsigned |… | TARGET_PARSER_PROGRAM | varchar(50) |… : : : +------------------------+------------------------+- 23 rows in set (0.01 sec)
desc(ribe) as sfxlcl41 onelocalcounterpartof KB_OBJECT_PORTFOLIOS: 16:41 sfxlcl41> desc LCL_OBJECT_PORTFOLIO_INVENTORY; +---------------------+---------------------------+- | Field | Type |… +---------------------+---------------------------+- | INTERNAL_ID | bigint(20) unsigned |… | OP_ID | bigint(20) unsigned |… | ACTIVATION_STATUS | enum('ACTIVE','INACTIVE') |… | DEACTIVATION_REASON | varchar(255) |… | OWNER | varchar(100) |… : : : +---------------------+---------------------------+- 10 rows in set (0.01 sec)
select • SELECTstatementsreturnthecurrentcontentsofthespecifieddatabasefields. • They do not changethesecontentsat all! • The specifiedfieldsmaycorrespondtocolumnsFROMasmany different tablesasneeded. • Selectionconditions(WHERE...) arealwaysconditionsaboutthecontentsofthefields. • Bymeansof Boolean operators (AND, OR, NOT, resp.) conditionsmaybecombinedornegated.
select(continued) • The resultof a SELECT statementisalways a setofrows: „<number>rows in set (<seconds>sec)“. • This setofrowsmaybeGROUPedBY thevaluesofcertainfields ... • ... oder itmaybeLIMITedto a subsetdefinedbysizeor a certainrangeofrownumbers.
selectexample #1 Howmanyobjectportfoliosareactive? 16:45 sfxlcl41> > select > count(*) > from > LCL_OBJECT_PORTFOLIO_INVENTORY > where > ACTIVATION_STATUS = 'ACTIVE';
Can thisbetrue??? Forinstance sfxlcl41 on sfx.bib-bvb.de thequerystatedabovereturned on Feb 8, 2013: 142,974 – that'squite a lotfor an instancewhichweuseassortof a prototype fornewlyaddedlibraryinstances!
select-Beispiel 2 Now, howmanyobjectportfoliosarereallyactive? select count(*) from LCL_OBJECT_PORTFOLIO_INVENTORY aspi, LCL_SERVICE_INVENTORY as si, LCL_TARGET_INVENTORY asti, sfxglb41.KB_OBJECT_PORTFOLIOS asop where pi.ACTIVATION_STATUS = 'ACTIVE' andsi.ACTIVATION_STATUS = 'ACTIVE' andti.ACTIVATION_STATUS = 'ACTIVE' andsi.TARGET_ID = ti.TARGET_ID andop.TARGET_ID = ti.TARGET_ID andop.TARGET_SERVICE_ID = si.TARGET_SERVICE_ID andop.OP_ID = pi.OP_ID;
Every answerrisesnewquestions Forinstance sfxlcl41 on sfx.bib-bvb.de thequerystatedabovereturned on Feb 8, 2013: 17,100 – well, that'sfarmore plausible! But: Who activated so many OPs withoutactivatingtheassociatedtargetsandservices?
desc(ribe) as sfxlcl41 onelocalcounterpartof KB_OBJECT_PORTFOLIOS: 16:41 sfxlcl41> desc LCL_OBJECT_PORTFOLIO_INVENTORY; +---------------------+---------------------------+- | Field | Type |… +---------------------+---------------------------+- | INTERNAL_ID | bigint(20) unsigned |… | OP_ID | bigint(20) unsigned |… | ACTIVATION_STATUS | enum('ACTIVE','INACTIVE') |… | DEACTIVATION_REASON | varchar(255) |… | OWNER | varchar(100) |… : : : +---------------------+---------------------------+- 10 rows in set (0.01 sec)
WhattheAdminCenterdoesn'ttell • The MySQL leveloftenrevealsinformationthatis not accessiblefromthe SFX AdminCenter! • Example: Howmanye-booktargetsarethere? • A SELECT statementconsistingof 29 lines (formattedas in thetwoexamplesabove) determinesthecurrently valid answer. • Due to a weeklytriggered Perl scriptthatpimpstheresultsetwithsome HTML markupthepubliclyavailableoverviewstaysup-to-date – at least aslongaswehaveappliedthelatestrevision update
4What's not possiblewithoutlosingsupport ... andyetsometimesneedstobedone?
Read? YO! – Write? NO! • Warning: Writing directlyinto SFX MySQL tables will forfeityourentitlementtotechnicalsupport! • At least that'sofficial Ex Libris policy (AFAIK). • In practice: Ifyouhavemanipulatedlocaloreven global tablesofthe SFX databaseyoucan not rely on thehelpinghandsof Ex Libris whenfightingproblemsclearlycausedbythatmanipulation – andbesurethatthey'llknowwhathascausedyourproblems!
insert • addsnewrowsto MySQL tables • maycompromisetheconsistencyofthevarious links toobjectsstored in othertables • isdefinitely not recommended
update • changesalreadyexistingrows in a MySQL table • shouldbeusedonlyifyouknowwhatyou'redoing ... • ... orifyouareinvitedtoby SFX support. And, yes, thelattermay happen once in a while!
Everythingproxied? Forhowmanyobjectportfoliosisproxyingenabled? select t.TARGET_NAME, count( pl.OP_ID ) ascount from sfxglb41.KB_TARGETS as t, sfxglb41.KB_OBJECT_PORTFOLIOS asop, LCL_OBJECT_PORTFOLIO_LINKING_INFO aspl where pl.PROXY_ENABLED = 1 andpl.OP_ID = op.OP_ID andop.TARGET_ID = t.TARGET_ID groupby t.TARGET_NAME;
Amigrationbug Fornoneoftheinstances on sfx.bib-bvb.de a proxyisconfiguredat all. Nevertheless on Nov 22, 2012, thequerystatedabovereturned a total amountof 1,408 affectedtargets 758,920 affectedobjectportfolios SFX supportrecognizedthisas a "knownissue" whichoccurredduringthefirstbunchofdatabasemigrationsaspartofthe upgrade from v3 to v4 andrecommended:
Do it yourself! “If you would like to turn off the proxy setting for all portfolios please set the PROXY_ENABLED field to 0 for all records in the LCL_OBJECT_PORTFOLIO_LINKING_INFO table using If you want to deactivate the setting only for certain targets this is obviously a lot more complicated and will need to be done manually.” update LCL_OBJECT_PORTFOLIO_LINKING_INFO set PROXY_ENABLED = 0;
Thanks a lot, Ex Libris! sfxglb41 sfxlcl41 KB_*,e. g.KB_TARGETS LCL_*_LINKING_INFO LCL_*_INVENTORY
When DataLoader cannothelp ... • DataLoader allowsformasschangesofthemostcommonlycustomizedfieldsofobjectportfolios. • Doing so createsmanylocalizations ... • ... only a fewofwhich (e.g., localthresholds) canbereverted / removedby a complementarymasschange. • Unlessyou'veenabledAUTO UPDATE on servicelevel! • But youcannot do so bymeansof DataLoader
... youneedtohelpyourself • Howdoes DataLoader operate on the MySQL database? • Howdoesthe SFX AdminCenteroperate on the MySQL database? (e.g., whenyouedit an objectportfolio) • Answersprovided in thereadable SFX sourcecode. • Bycopy & learnit was quite simple todevelop a Perl scriptthatenables AUTO UPDATE for a targetservicegivenby ID – withabsolutelynoclickat all!
DBLayer::* • SFX 4 comeswith a completelynewinterfacebetweenthe MySQL databaseandthe Perl programmes. • Object-orientedmoduleslikeDBLayer::TargetServiceallowfordatabasemanipulations just bycallingtheappropriatemethods – withno SQL statementsat all! • Since SFX sourcecodeitselfmakesuseofthisinterfaceitpresumablyisquitestablealready but, unfortunately, • asgoodasundocumentedand • officiallynot supportedifusedin customerscripts.
ER #1 What'spossibleforthefilesystem ("Unix File Manager") shouldbepossibleforthedatabaseaswell!
ER #2 Open PlatformStrategy for SFX now!
What do youthink? Dr. Mathias Kratzer Bayerische Staatsbibliothek / Bavarian Library Network E-Mail: kratzer@bsb-muenchen.de