Archive for the ‘DBA’ Category
Using a Sparse Index
My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set? You can solve the problem by passing an ADT (Attribute Data Type) or UDT (User Defined Type) variable into a subquery of a cursor. The subquery leverages the TABLE function to translate the ADT or UDT into SQL result set, which is equivalent to a comma-delimited list of values.
You can also solve this problem with Native Dynamic SQL (NDS). However, the person who posed the question didn’t want to use NDS to build out a variable length list of comma-delimited numbers.
You need to create three object types for this example. They are:
- a list of numbers
- a record structure, declared as an object type without methods
- a list of the record structure
These are the SQL commands to create the required data types:
CREATE OR REPLACE TYPE list_ids IS TABLE OF NUMBER; / |
CREATE OR REPLACE TYPE item_struct IS OBJECT ( item_id NUMBER , item_title VARCHAR2(80) , release_date DATE ); / |
CREATE OR REPLACE TYPE item_struct_list IS TABLE OF item_struct; / |
Next, you create a nonsynchronous function. It takes a sparsely populated list of values that map to the surrogate key of the column, which is typically the table’s primary key column. It returns a collection of the item_struct object type. This type of function is an object-table function.
The code follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | CREATE OR REPLACE FUNCTION nonsynchronous ( pv_list_ids LIST_IDS ) RETURN item_struct_list IS /* Declare a record data structure list. */ lv_struct_list ITEM_STRUCT_LIST := item_struct_list(); /* Declare a sparsely indexed list of film items. */ CURSOR get_items ( cv_list_ids LIST_IDS ) IS SELECT item_id AS item_id , item_title || CASE WHEN item_subtitle IS NOT NULL THEN ': '|| item_subtitle END AS item_title , release_date AS release_date FROM item WHERE item_id IN (SELECT * FROM TABLE(cv_list_ids)) ORDER BY item_id; BEGIN /* Lood through the sparsely populated list of numbers. */ FOR i IN get_items(pv_list_ids) LOOP lv_struct_list.EXTEND; lv_struct_list(lv_struct_list.COUNT) := item_struct( item_id => i.item_id , item_title => i.item_title , release_date => i.release_date ); END LOOP; /* Return the record structure list. */ RETURN lv_struct_list; END; / |
The foregoing nonsynchronous function uses a nested query that transforms to a result set on lines 18 and 19. In the execution block of the program, it uses a call to the item_struct structure to capture and assign row values to an element of the lv_struct_list variable.
You can now test the nonsynchronous function with the following query:
COL item_id FORMAT 9999 HEADING "Item|ID #" COL item_title FORMAT A40 HEADING "Item Title" COL release_date FORMAT A11 HEADING "Release|Date" SELECT * FROM TABLE(nonsynchronous(list_ids(1002, 1013, 1007))); |
The query returns the record set as an ordered list in the result set, like:
Item Release ID # Item Title Date ----- ---------------------------------------- ----------- 1002 Star Wars I: Phantom Menace 04-MAY-99 1007 RoboCop 24-JUL-03 1013 The DaVinci Code 19-MAY-06 |
I hope this answers the question about how to get results sets with sparsely populated ID values.
Bash Arrays & MySQL
Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.
It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.
Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.
Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.
If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.
Working with bash Arrays ↓
A basic example of working with an array in bash shell is the following list1.sh script:
1 2 3 4 5 6 7 8 9 10 11 12 13 | #!/usr/bin/bash # Print script name. echo $0 #!/usr/bin/bash # Define an array. declare -a cmd=("one" "two" "three") # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Line 8 declares the cmd array by assigning three strings. Line 12 returns the elements of the array to the ${i} variable, which lets you manage them one at a time. You use the chmod command to make the list1.sh executable, like this:
chmod 755 list1.sh |
Then, you can run it like this from the present working directory (pwd):
./list1.sh |
It should print:
one two three |
The list2.sh example changes the cmd array declaration from list1.sh. It declares the cmd array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one" cmd[1]="two" cmd[2]="three" # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Lines 7 through 9 assign values to the elements of the cmd array. You would chmod the file, and run the file as qualified above for the list1.sh script.
The last pre-implementation example requires that you create three demonstration scripts, the one.sh, two.sh, and three.sh scripts. You should put them in the same directory as the list3.sh script.
The demonstration scripts should all have the same code, like this:
1 2 3 4 | #!/usr/bin/bash # Print script name. echo $0 |
Line 4 returns command line parameter $0 or ${0}, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).
The list3.sh script should contain the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash <code> # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one.sh" cmd[1]="two.sh" cmd[2]="three.sh" # Call the array elements. for i in ${cmd[*]}; do `pwd`/${i} done |
When you run the list3.sh script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:
/home/student/Code/bash/one.sh /home/student/Code/bash/two.sh /home/student/Code/bash/three.sh |
The list3.sh script provides the present working directory (pwd) and the one.sh, two.sh, and three.sh scripts return only their executable name. For example, if you ran one.sh with the following syntax:
./one.sh |
It returns
./one.sh |
Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.
Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:
1 2 3 4 5 6 7 8 9 10 | #!/usr/bin/bash # This says hello to the argument while managing no argument. if [[ ${#} = 1 ]]; then echo 'The '${0}' program says: "Hello '${1}'!"' elif [[ ${#} > 1 ]]; then echo 'The '${0}' program wants to know if you have more than one name?' else echo 'The '${0}' program wants to know if you have a name?' fi |
If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.
You will need the three batch SQL files first, so here they are:
Setup SQL Files ↓
The actor.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an actor table. DROP TABLE IF EXISTS actor; -- Create an actor table. CREATE TABLE actor ( actor_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , actor_name VARCHAR(30) NOT NULL ); -- Insert two rows. INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth'); INSERT INTO actor (actor_name) VALUES ('Chris Pine'); INSERT INTO actor (actor_name) VALUES ('Chris Pratt'); |
The film.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop a film table. DROP TABLE IF EXISTS film; -- Create a film table. CREATE TABLE film ( film_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , film_name VARCHAR(30) NOT NULL ); -- Insert rows. INSERT INTO film (film_name) VALUES ('Thor'); INSERT INTO film (film_name) VALUES ('Thor: The Dark World'); INSERT INTO film (film_name) VALUES ('Star Trek'); INSERT INTO film (film_name) VALUES ('Star Trek into Darkness'); INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy'); |
The movie.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an movie table. DROP TABLE IF EXISTS movie; -- Create an movie table. CREATE TABLE movie ( movie_id int unsigned PRIMARY KEY AUTO_INCREMENT , actor_id int unsigned NOT NULL , film_id int unsigned NOT NULL , CONSTRAINT actor_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) , CONSTRAINT film_fk FOREIGN KEY (film_id) REFERENCES film(film_id)); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor: The Dark World')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek into Darkness')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pratt') ,(SELECT film_id FROM film WHERE film_name = 'Guardians of the Galaxy')); |
The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #!/usr/bin/bash # Assign user and password username="${1}" password="${2}" database="${3}" directory="${4}" # List the parameter values passed. echo "Username: " ${username} echo "Password: " ${password} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.
You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.
./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql" |
With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:
Username: student Password: student Database: sampledb Directory: /home/student/Code/bash/mysql Tables_in_sampledb ---------------------------------------- actor film movie Actors in Films ---------------------------------------- Chris Hemsworth in Thor Chris Hemsworth in Thor: The Dark World Chris Pine in Star Trek Chris Pine in Star Trek into Darkness Chris Pine in Guardians of the Galaxy |
If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:
Username: Password: Database: Directory: ./list_mysql.sh: line 25: /actor.sql: No such file or directory ./list_mysql.sh: line 25: /film.sql: No such file or directory ./list_mysql.sh: line 25: /movie.sql: No such file or directory |
The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | #!/usr/bin/bash # Assign user and password username="${1}" database="${2}" directory="${3}" # List the parameter values passed. echo "Username: " ${username} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.
Fedora PostgreSQL Install
Somebody asked how to put PostgreSQL on my Fedora image with Oracle Database 11g and MySQL. It’s fairly simple. You can check for the current download at yum.postgresql.org and then download it like this as the root user:
yum localinstall http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/pgdg-fedora93-9.3-1.noarch.rpm |
You should see the following output when the download is successful, don’t forget to type y to complete the download:
Loaded plugins: langpacks, refresh-packagekit pgdg-fedora93-9.3-1.noarch.rpm | 5.1 kB 00:00 Examining /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm: pgdg-fedora93-9.3-1.noarch Marking /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-fedora93.noarch 0:9.3-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: pgdg-fedora93 noarch 9.3-1 /pgdg-fedora93-9.3-1.noarch 2.1 k Transaction Summary ================================================================================ Install 1 Package Total size: 2.1 k Installed size: 2.1 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : pgdg-fedora93-9.3-1.noarch 1/1 Verifying : pgdg-fedora93-9.3-1.noarch 1/1 Installed: pgdg-fedora93.noarch 0:9.3-1 Complete! |
After downloading the packages, you install with the following command:
yum install postgresql93-server |
You should see the following output when the installation is successful, don’t forget to type y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit pgdg93 | 3.6 kB 00:00 (1/2): pgdg93/20/x86_64/group_gz | 332 B 00:00 (2/2): pgdg93/20/x86_64/primary_db | 84 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.f20 will be installed --> Processing Dependency: postgresql93-libs(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: postgresql93(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: postgresql93 = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64 --> Running transaction check ---> Package postgresql93.x86_64 0:9.3.5-1PGDG.f20 will be installed ---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: postgresql93-server x86_64 9.3.5-1PGDG.f20 pgdg93 3.6 M Installing for dependencies: postgresql93 x86_64 9.3.5-1PGDG.f20 pgdg93 1.0 M postgresql93-libs x86_64 9.3.5-1PGDG.f20 pgdg93 203 k Transaction Summary ================================================================================ Install 1 Package (+2 Dependent packages) Total download size: 4.8 M Installed size: 22 M Is this ok [y/d/N]: y Downloading packages: (1/3): postgresql93-libs-9.3.5-1PGDG.f20.x86_64.rpm | 203 kB 00:00 (2/3): postgresql93-9.3.5-1PGDG.f20.x86_64.rpm | 1.0 MB 00:01 (3/3): postgresql93-server-9.3.5-1PGDG.f20.x86_64.rpm | 3.6 MB 00:02 -------------------------------------------------------------------------------- Total 1.6 MB/s | 4.8 MB 00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : postgresql93-libs-9.3.5-1PGDG.f20.x86_64 1/3 Installing : postgresql93-9.3.5-1PGDG.f20.x86_64 2/3 Installing : postgresql93-server-9.3.5-1PGDG.f20.x86_64 3/3 Verifying : postgresql93-server-9.3.5-1PGDG.f20.x86_64 1/3 Verifying : postgresql93-9.3.5-1PGDG.f20.x86_64 2/3 Verifying : postgresql93-libs-9.3.5-1PGDG.f20.x86_64 3/3 Installed: postgresql93-server.x86_64 0:9.3.5-1PGDG.f20 Dependency Installed: postgresql93.x86_64 0:9.3.5-1PGDG.f20 postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20 Complete! |
You can confirm the installation with the following command:
rpm -qa | grep postgres |
It returns:
postgresql93-9.3.5-1PGDG.f20.x86_64 postgresql93-server-9.3.5-1PGDG.f20.x86_64 postgresql93-libs-9.3.5-1PGDG.f20.x86_64 |
You’ve now installed PostgreSQL but did you really? If you’re asking that questions you have other questions. Let me try to answer them quickly, here:
You have installed PostgreSQL and created a postgres user. postgres is the owner of the PostgreSQL database. You can connect to the database as the postgres user without credentials because that’s where you administer the database. However, you can’t connect using ssh as the postgres user. You must use sudo to assume the root user’s privileges and then use the su command to become the postgres user.
If you just completed the installation, you are the root user. You can verify that with a call to the whoami utility:
whoami |
It should return:
root |
You connect as the postgres user with the su utility like this:
su - postgres |
If you rerun the whoami command now, you should see:
postgres |
You can start the PostgreSQL command-line utility (psql), like this:
psql |
At the postgres (or psql) prompt, you can interactively confirm the setup of a database installation:
postgres=# SELECT setting as "Data Location" postgres-# FROM pg_settings postgres-# WHERE name = 'data_directory'; |
It should return the following:
Data Location ------------------------- /var/lib/pgsql/9.3/data (1 row) |
At this point, you should refer to this other blog post that shows you how to setup a new Database or Schema in PostgreSQL. You can find basic Postgres help files in this other blog post. As always, I hope this helps those timid about adding new software.
PostgreSQL New Database
How time flies, last March I explained how to install and configure PostgreSQL on Windows. It was my intent to start posting more content on PostgreSQL but I was distracted by another writing commitment on Oracle Database 12c PL/SQL Advanced Programming Techniques, which should be available in November. It tempted me away from PostgreSQL because I got to write about how to use Java inside Oracle Database 12c, which was fun. Having completed that, I’m back on task. Here’s the second entry on PostgreSQL. It shows you howto create your own database, database administrator role, user, and how to connect with psql CLI (Command Line Interface) as the new user.
- Create a user-defined
video_dbtablespace for your database. This requires that you know where the physical files where created when you installed PostgreSQL. You can discover the directory with the following query:
SELECT setting AS "Data Location" FROM pg_settings WHERE name = 'data_directory'; |
Data Location -------------------------------------- C:/Program Files/PostgreSQL/9.3/data (1 row) |
You create the video_db tablespace with the following syntax:
CREATE TABLESPACE video_db OWNER postgres LOCATION 'C:\Program Files\PostgreSQL\9.3\data'; |
You can check the presence of the video_db tablespace after creating it with the following query:
SELECT * FROM pg_tablespace; |
It should print:
spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | video_db | 10 | | (3 rows) |
It’s important to note for those new to PostgreSQL that the pg_global and pg_default tablespaces are creating when initializing the database. The pg_global holds shared tables and the pg_default holds everything else.
- Create a database that uses your user-defined
video_dbtablespace with the following two commands:
CREATE DATABASE videodb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = video_db LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1; COMMENT ON DATABASE videodb IS 'VideoDB'; |
- Create a database role, grant the super user privileges to the role, and create a user with the role. You can do that with the following three commands:
CREATE ROLE dba WITH SUPERUSER; GRANT ALL PRIVILEGES ON DATABASE videodb TO dba; CREATE USER video WITH ROLE dba PASSWORD 'video'; |
- Connect to the new
videodbdatabase with thepsqlCLI as thevideouser. You can do that with the following OS command:
psql -d videodb -U video |
- Once connected as the new
videouser, you can use a system information function to determine the current database:
SELECT current_database(); |
It should display:
current_database ------------------ videodb (1 row) |
There are many privilege options, and you should choose wisely which ones you use. As always, I hope this answers questions for other users.
Drop Types Recursively
As covered in my new Oracle Database 12c PL/SQL Programming book (publisher’s satisfied), you can evolve object types. That means you can change a base object type and the change cascades through dependents. Somebody asked how to remove an object type chain without appending the FORCE clause.
It’s quite easy if you understand writing a recursive function in PL/SQL, as done here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | -- Create a recursive function. CREATE OR REPLACE FUNCTION drop_dependents (pv_base_object_type VARCHAR2) RETURN NUMBER IS /* Declare a return data type. */ lv_retval NUMBER := 0; /* Declare item type. */ lv_type_name VARCHAR2(30); lv_object_name VARCHAR2(30); /* The first part of the cursor finds the dependent type names of complex object types, and the second part of the cursor finds the dependent collection types. Effectively the set operator finds two distinct branches because you may use any base type as an element of a complex object or of a collection. */ CURSOR base_type (cv_base_type VARCHAR2) IS SELECT uta.type_name , NULL AS object_name FROM user_type_attrs uta INNER JOIN user_types ut ON uta.attr_type_name = ut.type_name WHERE ut.type_name = cv_base_type UNION ALL SELECT uct.type_name , NULL AS object_name FROM user_types ut INNER JOIN user_coll_types uct ON ut.type_name = uct.elem_type_name WHERE uct.elem_type_name = cv_base_type UNION ALL SELECT CASE WHEN package_name IS NULL THEN uo.object_type ELSE 'PACKAGE' END AS type_name , CASE WHEN package_name IS NULL THEN ua.object_name ELSE ua.package_name END AS object_name FROM user_arguments ua LEFT JOIN user_objects uo ON ua.package_name = uo.object_name OR ua.object_name = uo.object_name WHERE type_name = cv_base_type; BEGIN /* Open a parameterized cursor. */ OPEN base_type(pv_base_object_type); /* Loop through return records. */ LOOP /* Fetch records. */ FETCH base_type INTO lv_type_name , lv_object_name; /* Drop type without dependents, or drop leaf node dependent. */ IF base_type%NOTFOUND THEN /* Drop functions when they include an object type or object type dependent as a formal parameter type or return type. Drop procedures when they include an object type or object type dependent. Drop procedures when any function or procedure uses an object type or object type dependent. */ IF lv_type_name IN ('FUNCTION','PACKAGE','PROCEDURE') THEN /* Drop the base type when no dependents are found. */ EXECUTE IMMEDIATE 'DROP '||lv_type_name||' '||lv_object_name; ELSE /* Drop the base type when no dependents are found. */ EXECUTE IMMEDIATE 'DROP TYPE '||pv_base_object_type; END IF; /* Set exit state to one or true. */ lv_retval := 1; /* Exit the loop. */ EXIT; ELSE /* A type must exclude function, package, and procedure; and the object name must be null before you recurse to another level. */ IF lv_type_name NOT IN ('FUNCTION','PACKAGE','PROCEDURE') AND lv_object_name IS NOT NULL THEN /* Drop base type when no dependents are found. */ lv_retval := drop_dependents(lv_type_name); END IF; END IF; END LOOP; /* Close open cursor. */ CLOSE base_type; /* Return 0 for false. */ RETURN lv_retval; END; / |
Somebody asked me to provide a test case of a hierarchy of object types to support the drop_dependents function. So, here’s the test case code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | -- Create object type. CREATE OR REPLACE TYPE item_object IS OBJECT ( item_name VARCHAR2(30) , item_subname VARCHAR2(30)); / -- Create object type. CREATE OR REPLACE TYPE identified_object IS OBJECT ( identified_id NUMBER , identified_object item_object); / -- Create object collection. CREATE OR REPLACE TYPE item_table IS TABLE OF item_object; / -- Create object collection. CREATE OR REPLACE TYPE item_varray IS VARRAY(5) OF item_object; / -- Create object type. CREATE OR REPLACE TYPE item_async_table IS OBJECT ( item_name VARCHAR2(30) , item_collection item_table); / -- Create object type. CREATE OR REPLACE TYPE item_async_varray IS OBJECT ( item_name VARCHAR2(30) , item_collection item_varray); / -- Create object collection. CREATE OR REPLACE TYPE item_list IS TABLE OF item_async_table; / -- Create object collection. CREATE OR REPLACE TYPE item_array IS VARRAY(10) OF item_async_varray; / -- Create package specification. CREATE OR REPLACE PACKAGE item_package AS /* A published function of the package. */ FUNCTION initialize_object ( id NUMBER , name NUMBER ) RETURN ITEM_OBJECT; END item_package; / -- Create a schema function. CREATE OR REPLACE FUNCTION get_item_object ( pv_id NUMBER , pv_name NUMBER ) RETURN ITEM_OBJECT IS /* Declare a local variable. */ lv_item_object ITEM_OBJECT; BEGIN /* Initialize the object type. */ lv_item_object := item_object(pv_id, pv_name); /* Return the dat type. */ RETURN lv_item_object; END; / |
If you call the function with the base type, it’ll drop the most dependent object type first, and the base object type last. The rest are dropped in their order of dependency. You can call a drop_dependents function with a base type, like ITEM_OBJECT, by using the following syntax:
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON SIZE UNLIMITED BEGIN IF drop_dependents('ITEM_OBJECT') = 1 THEN dbms_output.put_line('Objects dropped.'); END IF; END; / |
Hope this helps those looking to drop a chain of object types in an Oracle database.
Subscribe via RSS