Project

General

Profile

metacat / test / tools / about_sql_scripts.txt @ 4418

1
INTRODUCTION:
2
This file describes how to use the sql scripts in the /metacat/test/tools directory
3
to create different versions of the metacat database schema.  This is useful for testing
4
the metacat auto-upgrade functionality.  
5

    
6
These scripts were extracted from the ddl for metacat using code tags.  The scripts 
7
were retrieved back to version 1.4.  For earlier scripts, you will need to go to the 
8
SVN repository.
9

    
10
Note that database scripts currently only support postgresql and oracle databases.
11

    
12
PURPOSE AND NAMING CONVENTIONS:
13
You will find three major categories of sql scripts in this directory.  Here we list
14
their usage and naming conventions. 
15

    
16
1) xmltables scripts - these are used to create a database schema for a particluar 
17
version of metacat.  The naming format is:
18

    
19
		<version_number>_xmltables-<database_type>.sql
20
		
21
2) loaddtdschema scripts - these are use to populate eml schema information into 
22
the database.  The nameing format is:
23

    
24
		<version_number>_loaddtdschema.sql
25
		
26
Note that there is no qualification for database type.  Currently, all database types
27
use the same eml schema scripts.  If this should change in the future, we would need
28
to add the database type qualification.
29

    
30
3) cleanall scripts -  these clear out all data and metacat schema information.  This 
31
should remove all tables, sequences and triggers for the given version.  The naming 
32
format is:
33

    
34
		<version_number>_cleanall-<database_type>.sql
35
		
36
HOW TO RUN SCRIPTS:
37

    
38
If you are testing against a postgresql database, you should start with an empty
39
database.  You will run the xmltables script first and then the loaddtdschema script.
40
Change to the postgres user and run the script by typing:
41

    
42
		psql -f <file_name> <database_name>
43
		
44
So for instance, if you have an empty postgres database named "metacat" and you wanted
45
to install the 1.4 metacat schema, you would type:
46

    
47
		sudo su - postgres
48
		psql -f 1.4_xmltables-postgres.sql
49
		psql -f 1.4_loaddtdschema.sql
50

    
51
If you are testing against an oracle database, you should log into the database 
52
using sqlplus.  A typical scenario is that you have downloaded and installed the 
53
development XE version of oracle.  You may need to 'locate sqlplus' to find the sqlplus
54
program.  
55

    
56
the command to log into sqlplus looks like:
57
		
58
		sqlplus <db_user_name>@<db_sid>
59
		
60
Once you are at the sqlplus command line, you can run a sript by referencing it with a
61
"@" and the script name.  So if you have an empty oracle XE development license database 
62
with a "metacat" user, that you want to install with the 1.4 metacat schema, you would 
63
run the scripts by typing:
64

    
65
		sqlplus metacat@XE
66
		SQL> @1.4_xmltables-oracle.sql
67
		SQL> @1.4_loaddtdschema.sql
68
		
69

    
70
CLEANING THE DATABASE:
71
Whatever version your database schema is at, you can get it back to an empty state
72
by running the script named:
73

    
74
		<version_number>_cleanall-<database_type>.sql
75
		
76
Follow the same steps shown above to run the script depending on which database 
77
you have installed.
78

    
79

    
80

    
81

    
82

    
83

    
84

    
85

    
86

    
87

    
88

    
89