|
SQL Scripts
and Templates (UNIX)
Contents
Introduction
Scripts
mysql and msql Scripts
mysql_rb/msql_rb Scripts
Templates
Introduction
This topic describes
the SQL scripts and templates
available to assist UNIX users to create and maintain the two Access component
databases: accessdb for controlling access
to WebZ and rbdb for controlling
access to Record Builder.
Document
Conventions
- <WebZ_root>
refers to the location of your installed WebZ environment.
- sql_dir
is the your SQL server's top-level
directory:
- <WebZ_root>/mysql
for MySQL (beginning with SiteSearch 4.1.2)
- <WebZ_root>/msql
for mSQL (SiteSearch 4.1.1 and earlier versions)
Scripts
Beginning with
SiteSearch 4.1.2, the <WebZ_root>/scripts directory contains
two MySQL scripts for database administration for the Access component:
mysql and mysql_rb. These scripts use standard MySQL commands to perform
database maintenance on accessdb and rbdb, respectively. In SiteSearch
4.1.0 and 4.1.1, the <WebZ_root>/scripts directory contains
two similar mSQL scripts: msql and msql_rb, which use standard mSQL commands.
Each script can
perform several actions, depending on the command line option you enter
when you invoke the script.
| Note: |
Run these
scripts from the <WebZ_root>/scripts directory.
|
mysql
and msql Scripts
Except for mysql
start and msql start, these commands apply to the accessdb
database for WebZ
|
Command
Line
|
Action
|
|
SiteSearch
4.1.2
(MySQL)
|
SiteSearch
4.1.0/4.1.1
(mSQL)
|
| mysql
start |
msql
start
|
Starts MySQL
or mSQL, respectively..
| Note: |
Use
these command for starting MySQL or mSQL, respectively.
|
|
| mysql
create |
msql
create
|
Creates the
accessdb database in this
directory:
- Beginning
with SiteSearch 4.1.2 (MySQL): <WebZ_root>/mysql/data/accessdb
- SiteSearch
4.1.1 and earlier (mSQL): <WebZ_root>/msql/msqldb/accessdb
|
| mysql
load |
msql
load |
Creates table
structure and adds sample data to accessdb, using the input data
file <WebZ_root>/sql_dir/accessdb.dump.
- Beginning
with SiteSearch 4.1.2 (MySQL): Saves a record of the start
and end time of the database load in the file <WebZ_root>/mysql/accessdb.out.
- SiteSearch
4.1.1 and earlier (mSQL): Saves a record of the tables created
and the data added in the file <WebZ_root>/msql/accessdb.out.
See accessdb
Database for information about this database's schema and the
sample data provided.
|
| mysql
import <filename1>, <filename2>, ... <filename_n> |
msql
import <filename1>, <filename2>, ... <filename_n>
(beginning with SiteSearch 4.1.1) |
Adds data
from one or more comma-delimited files (.csv) to table(s) in accessdb.
The name of each input file must be the same as the name of its
target table; that is, to add data to the authos table, name the
input file authos.csv. Each input file must reside in <WebZ_root>/sql_dir.
| Example: |
mysql
import authos.csv inst.csv |
|
mysql
export <tablename1>, <tablename2>, <tablename_n>
|
msql
export <tablename1>, <tablename2>, <tablename_n>
(beginning with SiteSearch 4.1.1) |
Exports
data from one or more tables in accessdb to an output file in comma-delimited
(.csv) format. It writes the output file to <WebZ_root>/sql_dir/<tablename>.csv.
If you do not specify one or more table names, it creates a file
for each table.
| Examples: |
mysql
export dbs |
(exports
only the dbs table as dbs.csv) |
| |
mysql
export |
(exports
all tables as authos.csv, dbs.csv, etc.) |
|
|
Important
(SiteSearch 4.1.1 only):
To use the
msql import or msql export commands, edit the msql script
as follows:
(1) Add
these two lines to the top of the file:
# What version of SiteSearch is installed
SSVERSION=4_1_1
(2) Below
the "do not edit" line, modify the CLASSPATH line as follows
(the characters to add appear in dark
blue bold text):
CLASSPATH="$JAVAHOME/lib/classes.zip: \
$ROOTDIR/classes/lib/SS$SSVERSION.jar: \
$ROOTDIR/classes:$JDBCPATH"
(The backslashes
above are included for readability only. You do not need to add
them to the CLASSPATH line.)
|
| mysql
dump |
msql
dump |
Dumps
the contents of the accessdb database to the output file <WebZ_root>/sql_dir/accessdb.dump.Dyyyymmdd.Thhmmss,
where yyyymmdd is the current year, month, and day, and hhmmss
is the current time in hours (on a 24-hour clock), minutes, and seconds. |
| mysql
drop |
msql
drop |
Deletes
the accessdb database and the directory in which it resides. |
| mysql
stop |
msql stop |
Stops
MySQL or mSQL, respectively. |
| Advanced
Users: |
If you want
to use an input file other than <WebZ_root>/sql_dir/accessdb.dump
(version 4.1.2), edit these lines at the top of the <WebZ_root>/scripts/mysql
file (version 4.1.2) or <WebZ_root>/scripts/msql file
(version 4.1.0/4.1.1) as follows:
mysql
(beginning with SiteSearch 4.1.2) |
|
DUMPFILE=$MYSQLHOME/accessdb.dump
LOGFILE=$MYSQLHOME/accessdb.out |
|
msql
(SiteSearch 4.1.0/4.1.1)
|
|
DUMPFILE=$MSQLHOME/accessdb.dump
LOGFILE=$MSQLHOME/accessdb.out |
Change accessdb.dump
and accessdb.out to the name of your input file and the name of
the file that captures the results of the database dump. Then place
the input file in the <WebZ_root>/sql_dir directory.
|
Return
to Contents
mysql_rb
and msql_rb Scripts
These commands apply
to the rbdb database for Record
Builder.
|
Command
Line
|
Action
|
|
SiteSearch
4.1.2
MySQL
|
SiteSearch
4.1.0/4.1.1
mSQL
|
| mysql_rb
start |
msql_rb
start
|
Starts MySQL
or mSQL, respectively.
| Notes: |
(1)
Only use this command to start MySQL or mSQL when you are
performing database maintenance on the rbdb database.
|
| (2) Do
not issue this command if MySQL or mSQL if is already running.
You receive a "port in use" error message if you do. |
|
| mysql_rb
create |
msql_rb
create
|
Creates the
rbdb database in this directory:
- Beginning
with SiteSearch 4.1.2 (MySQL): <WebZ_root>/mysql/data/rbdb
- SiteSearch
4.1.1 and earlier (mSQL): <WebZ_root>/msql/msqldb/rbdb
|
| mysql_rb
load |
msql_rb
load |
Creates table
structure and adds sample data to rbdb, using the input data file
<WebZ_root>/sql_dir/rbdb.dump.
- Beginning
with SiteSearch 4.1.2 (MySQL): Saves a record of the start
and end time of the database load in the file <WebZ_root>/mysql/rbdb.out.
- SiteSearch
4.1.1 and earlier (mSQL): Saves a record of the tables created
and the data added in the file <WebZ_root>/msql/rbdb.out.
See rbdb
Database for information about this database's schema and the
sample data provided.
|
| mysql_rb
dump |
msql_rb
dump |
Dumps
the contents of the rbdb database to the output file <WebZ_root>/sql_dir/rbdb.dump.Dyyyymmdd.Thhmmss,
where yyyymmdd is the current year, month, and day, and hhmmss
is the current time in hours (on a 24-hour clock), minutes, and seconds. |
| mysql_rb
drop |
msql_rb
drop |
Deletes
the rbdb database and the directory in which it resides. |
mysql_rb
import
<filename1>, <filename2>, ... <filename_n>
|
N/A |
Adds data
from one or more comma-delimited files (.csv) to table(s) in accessdb.
The name of each input file must be the same as the name of its
target table; that is, to add data to the authos table, name the
input file authos.csv. Each input file must reside in <WebZ_root>/mysql.
| Example: |
mysql_rb
import authos.csv dbs.csv |
|
mysql_rb
export
mysql export <tablename1>, <tablename2>, <tablename_n>
|
N/A |
Exports
data from one or more tables in accessdb to an output file in comma-delimited
(.csv) format. It writes the output file to <WebZ_root>/mysql<tablename>.csv.
If you do not specify one or more table names, it creates a file
for each table.
| Examples: |
mysql_rb
export dbs |
(exports
only the dbs table as dbs.csv) |
| |
mysql_rb
export |
(exports
all tables as authos.csv, members.csv, etc.) |
|
| Advanced
Users: |
If you want
to use an input file other than <WebZ_root>/sql_dir/rbdb.dump,
edit these lines at the top of the <WebZ_root>/scripts/msql_rb
file (beginning with version 4.1.2) or <WebZ_root>/scripts/msql_rb
file (version 4.1.0/4.1.1) , as follows:
mysql_rb
(beginning with SiteSearch 4.1.2) |
|
DUMPFILE=$MYSQLHOME/rbdb.dump
LOGFILE=$MYSQLHOME/rbdb.out |
msql_rb
(SiteSearch 4.1.0/4.1.1)
|
|
DUMPFILE=$MSQLHOME/rbdb.dump
LOGFILE=$MSQLHOME/rbdb.out |
Change rbdb.dump
and rbdb.out to the name of your input file and the name of the
file that captures the results of the database dump.Then place the
input file in the <WebZ_root>/sql_dir directory.
|
Return
to Contents
Templates
In SiteSearch
4.1.1 and earlier versions, the <WebZ_root>/msql/templates
directory contains template files to assist you to create the accessdb
database and then add data to each table. The following table lists
each template file and describes its purpose. Read the comments in each
template file before you attempt to use it.
Do not use the
sample accessdb database that you created for configuring
the Access component to work with WebZ and testing
this configuration for storing your patron access data. Create a instance
of accessdb to store authorization data about your WebZ patrons.
| Note: |
If you change
the structure of a table from its original definition (as
described in accessdb Database), change the INSERT statement in
the appropriate template accordingly. |
| |
|
File
|
Purpose
|
|
tables.txt
|
Create the
six database tables that comprise the accessdb database: authos,
dbs, inst, resource, userdb, and userresource. See accessdb
Database for information about the structure of each of these
tables.
|
|
authos.txt
|
Loads data
into the authos table.
|
| dbs.txt |
Loads data
into the dbs table.
|
| inst.txt |
Loads
data into the inst table. |
| resources.txt |
Loads
data into the resource
table. |
| userdb.txt |
Loads
data into the userdb table. |
| userresource.txt |
Loads
data into the userresource
table. |
See the mSQL documentation
in <WebZ_root>/msql/doc/manual.html or or at the Hughes
Technologies Web site for information about inserting data into an
existing table, deleting data from an existing table, or replacing data
in an existing table.
Return
to Contents
See Also
Access
Component Overview
Configuring the Access Component for WebZ
(UNIX)
Configuring the Access Component for
Record Builder (UNIX)
accessdb Database
rbdb Database
The updateSQL.pl Utility
Operating the Web System (UNIX)
|