Undiscovered Country 2 Agenda How to get in How to find your way Whats possible without losing support Whats not possible without losing ID: 798993
Download The PPT/PDF document "Your MySQL Database: The" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Your
MySQL Database:
The
Undiscovered
Country
Slide22
Agenda
How
to
get
in?
How
to
find
your
way
?
What's
possible
without
losing
support
?
What's
not
possible
without
losing
support
?
Slide33
Agenda
How
to
get
in?
How
to
find
your
way
?
What's
possible
without
losing
support
?
What's
not ...
and
yet
sometimes
needs
to
be
done
?
A
c
losed
o
pen
i
nterface
?
Two
Enhancement
r
equests
Slide44
1
How
to
get
in?
Slide55
Interactively
The
MySQL
user
of
an SFX
instance
usually
bears the same name as the corresponding UNIX user.The respective MySQL password is usually just set to the user name by the SFX installer.Preferably, you should log in as user of a local SFX instance (e.g. as "sfxlcl41" like in the screenshot above)!
sfxlcl41>
mysql
–usfxlcl41 –p
Enter password:
Welcome to the MySQL monitor. […]
16:35 (none)>
Slide66
Interactively
(
continued
)
After
successful
login
you need to choose a database to work with.In case of doubt: database = user = passwordAll users have read permission on all databases.Write permission is granted to sfxglb41 on all databases, and to each of the other users on "their
"
own
database only.
16:35 (
none
)>
use
sfxlcl41;
Reading table information […]
Database changed
16:37 sfxlcl41>
Slide77
W
rapper
script
MySQL
user
unknown
,
password
forgotten? – Don't worry!Module Manager::Connection is all you really need:Build your SQL statement,insert it into a Perl wrapper script that establishes a database connection by using module
Manager::Connection
,
and
run
the
wrapper
script
. (Redirect
the
output
into
a
file
in
case
you
expect
a
longer
list
of
results
.)
Slide8Would
Perl::DBI work as well?In principle yes, but establishing the database connection by yourself meansyour script contains the MySQL
user as
plain
text
your
script
contains
the MySQL password as plain textcharacter encoding usually needs to be set explicitely by submitting a SET NAMES 'utf8'; as first commandAll this is taken care of by Manager::Connection !8
Slide99
2
How
to
find
your
way
?
Slide1010
show tables
lists
all
tables
of
the
currently
chosen database:16:37 sfxlcl41> show tables;+------------------------------------------+| 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)
Slide1111
show
tables
as
sfxglb41
yields
a
completely
different
list of tables:16:39 sfxglb41> show tables;+------------------------------------------+| Tables_in_sfxglb41 |+------------------------------------------+: :| KB_OBJECT_PORTFOLIOS |: :| KB_TARGET_SERVICES |: :
+------------------------------------------+
78
rows
in
set
(0.01 sec)
Slide1212
Database architecture in SFX 3
sfxglb3
sfxlcl3
Slide1313
SFX 4: Less is more!Key change of architecture: database of a local instance no longer contains a full
copy of the
global KB, ...
...
but
only
the
activation
status and any local fields (e.g. thresholds, parse params)Result: Less redundantly used disk space allows formore content in the global KB (e-books, author names, etc.)faster revision updatesmore frequent revision updates
Slide1414
Database architecture in SFX 4
sfxglb41
sfxlcl41
LCL_*_INVENTORY
LCL_*_LINKING_INFO
KB_*
,
e
.
g
.
KB_TARGETS
Slide1515
desc(ribe)
describes
the
columns
of
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)
Slide1616
desc
(
ribe
)
as
sfxlcl41
one
local
counterpart of 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)
Slide1717
3
What's
possible
without
losing
support
?
Slide1818
select
SELECT
statements
return
the
current
contents of the specified database fields.They do not change these contents at all!The specified fields may correspond to columns FROM as many different tables as needed.Selection conditions (WHERE ...) are always conditions about the contents of the fields.By means
of
Boolean
operators (AND, OR
,
NOT
, resp.)
conditions
may
be
combined
or
negated
.
Slide1919
select
(
continued
)
The
result
of
a SELECT
statement is always a set of rows: „<number> rows in set (<seconds> sec)“.This set of rows may be GROUPed BY the values of certain fields ...... oder it may be LIMITed to a subset defined by
size
or
a certain range
of
row
numbers
.
Slide20select
example #1How many object portfolios are active?2016:45 sfxlcl41>
>
select
> count(*)
> from
> LCL_OBJECT_PORTFOLIO_INVENTORY
> where
> ACTIVATION_STATUS = 'ACTIVE';
Slide21Can
this be true???For instance sfxlcl41 on sfx.bib-bvb.de the query stated above returned on Feb 8, 2013:142,974– that's quite a lot for an
instance which we
use
as
sort
of
a prototype for newly added library instances!21
Slide22select-Beispiel 2
Now, how many object portfolios are really active?22select
count
(*)
from
LCL_OBJECT_PORTFOLIO_INVENTORY
as
pi
,
LCL_SERVICE_INVENTORY as si, LCL_TARGET_INVENTORY as ti, sfxglb41.KB_OBJECT_PORTFOLIOS as opwhere
pi.ACTIVATION_STATUS
= 'ACTIVE'
and
si.ACTIVATION_STATUS
= 'ACTIVE'
and
ti.ACTIVATION_STATUS
= 'ACTIVE'
and
si.TARGET_ID
=
ti.TARGET_ID
and
op.TARGET_ID
=
ti.TARGET_ID
and
op.TARGET_SERVICE_ID
=
si.TARGET_SERVICE_ID
and
op.OP_ID
=
pi.OP_ID
;
Slide23Every
answer rises new questionsFor instance sfxlcl41 on sfx.bib-bvb.de the query stated above returned on Feb 8, 2013:17,100– well, that's far
more plausible!
But:
Who
activated
so
many
OPs
without
activating the associated targets and services? 23
Slide2424
desc
(
ribe
)
as
sfxlcl41
one
local
counterpart of 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)
Slide25What
the AdminCenter doesn't tellThe MySQL level often reveals information that is not accessible from the SFX AdminCenter!Example: How many
e-book targets
are
there
?
A SELECT
statement
consisting of 29 lines (formatted as in the two examples above) determines the currently valid answer.Due to a weekly triggered Perl script that pimps the result set with some HTML markup the publicly available overview stays up-to-date – at least as long as we have applied the latest r
evision
update
25
Slide2626
4
What's
not
possible
without
losing
support
... and yet sometimes needs to be done?
Slide2727
Read? YO! – Write? NO!
Warning
:
Writing
directly
into
SFX MySQL
tables
will
forfeit your entitlement to technical support!At least that's official Ex Libris policy (AFAIK).In practice: If you have manipulated local or even global tables of the SFX database you can not rely on the helping hands of Ex Libris when fighting problems clearly caused by that manipulation – and be
sure
that they'll know
what
has
caused
your
problems
!
Slide2828
insert
adds
new
rows
to
MySQL
tables
may compromise the consistency of the various links to objects stored in other tables is definitely not recommended
Slide2929
update
changes
already
existing
rows
in a MySQL
table
should be used only if you know what you're doing ...... or if you are invited to by SFX support.And, yes, the latter may happen once in a while!
Slide30Everything
proxied?For how many object portfolios is proxying enabled?30
select
t.TARGET_NAME
,
count
(
pl.OP_ID
)
as countfrom sfxglb41.KB_TARGETS as t, sfxglb41.KB_OBJECT_PORTFOLIOS as op, LCL_OBJECT_PORTFOLIO_LINKING_INFO as
pl
where
pl.PROXY_ENABLED
= 1
and
pl.OP_ID
=
op.OP_ID
and
op.TARGET_ID
=
t.TARGET_ID
group
by
t.TARGET_NAME
;
Slide31A
migration bugFor none of the instances on sfx.bib-bvb.de a proxy is configured at all. Nevertheless on Nov 22, 2012, the query stated above returned a total amount
of
1,408
affected
targets
758,920
affected
object portfoliosSFX support recognized this as a "known issue" which occurred during the first bunch of database migrations as part of the upgrade from v3 to v4 and recommended: 31
Slide32Do 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.”32update LCL_OBJECT_PORTFOLIO_LINKING_INFO
set
PROXY_ENABLED = 0;
Slide3333
Thanks a lot, Ex Libris!
sfxglb41
sfxlcl41
LCL_*_INVENTORY
LCL_*_LINKING_INFO
KB_*
,
e
.
g
.
KB_TARGETS
Slide3434
5
A Closed
Open Interface?
Slide3535
When
DataLoader
cannot
help
...
DataLoader
allows
for
mass changes of the most commonly customized fields of object portfolios.Doing so creates many localizations ...... only a few of which (e.g., local thresholds) can be reverted / removed by a complementary mass change.Unless you've enabled AUTO UPDATE on service level!But you
cannot
do so
by
means of DataLoader
Slide36...
you need to help yourselfHow does DataLoader operate on the MySQL database?How does the SFX AdminCenter operate on the MySQL database? (e.g., when
you edit an
o
bject
portfolio
)
Answers
provided
in the readable SFX source code.By copy & learn it was quite simple to develop a Perl script that enables AUTO UPDATE for a target service given by ID – with absolutely no click at all!36
Slide3737
DBLayer::*
SFX 4
comes
with
a
completely
new
interface between the MySQL database and the Perl programmes.Object-oriented modules like DBLayer::TargetService allow for database manipulations just by calling the appropriate methods – with no SQL statements at all!Since SFX source code itself makes use of this interface it presumably is quite stable
already
but,
unfortunately,as
good
as
undocumented
and
officially
not
supported
if
used
in
customer
scripts
.
Slide3838
6
Two
Enhancement
Requests
Slide39ER #1
What's possible for the file system ("Unix File Manager") should be possible for the database as well!
39
Slide40ER #2
Open Platform Strategyfor SFX now!40
Slide41What
do you think? Dr. Mathias Kratzer Bayerische Staatsbibliothek / Bavarian Library Network E-Mail: kratzer@bsb-muenchen.de 41