Archive for the ‘Database Design’ Category
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; /
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
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.
I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.
It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.
Here are the new instructions I’m adding and if you have any input leave it as a comment. 😉
You connect as the
student user, which puts you in the
/home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type
terminal in the search box. When you’re in the Terminal session, use the following command to verify that the
apply_mysql_lab1.sql file is correct:
It should display the following commands:
\. /home/student/Data/cit225/mysql/lib/cleanup.sql \. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql \. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql
You can run the
apply_mysql_lab1.sql to create the tables in the
studentdb database, and seed them with data. Assuming you’re in the same
/home/student directory, you connect to the
studentdb database with the following syntax:
mysql -ustudent -pstudent studentdb
or, more securely:
mysql -ustudent studentdb -p
Having connected to the
studentdb database, you can run the following command:
It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:
+---------------------+ | Tables_in_studentdb | +---------------------+ | address | | common_lookup | | contact | | contacts | | current_rental | | item | | member | | rental | | rental_item | | street_address | | system_user | | telephone | +---------------------+ 12 rows in set (0.00 sec)
After successfully creating and seeding the
studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.
Here are the instructions for the lab with MySQL Workbench:
- The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the ⊕ symbol to the right of the MySQL Workbench title.
- After clicking the the ⊕ symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.
- Click the Test Connection button to see if it works.
- When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.
- Click the OK button to continue.
- Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.
- When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.
- The MySQL Workbench launches in the default view.
- Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.
- This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.
- Enter the password and click the OK button to connect to the MySQL database.
- This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.
- This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.
- This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.
- Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.
- This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.
- This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.
- This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.
- The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.
- The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.
- The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.
- The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.
You can now save the MySQL Workbench file in the Lab 3 directory.
Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.
You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates
ALTER statements that will fail when a table holds a multiple column foreign key value. The
SELECT statement would look like this when capturing all foreign key values in a MySQL Server:
SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' ' ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' ' ,'FOREIGN KEY (',kcu.column_name,')',' ' ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' ,'(',kcu.referenced_column_name,');') AS script FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' ORDER BY tc.TABLE_NAME , kcu.column_name;
You would add a line in the
WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:
AND tc.table_schema = 'your_mysql_database' AND tc.table_name = 'your_table_name'
Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the
NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the
storedForeignKeys procedure finds the table’s foreign key constraints, and the
columnList function adds the column detail. The
command_list table stores the commands to restore foreign key constraints.
command_list table that stores the values is:
CREATE TABLE command_list ( command_list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sql_command VARCHAR(6) NOT NULL , sql_object VARCHAR(10) NOT NULL , sql_constraint VARCHAR(11) , sql_statement VARCHAR(768) NOT NULL);
This is the
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
CREATE PROCEDURE storeForeignKeys ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64)) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE sql_stmt VARCHAR(1024); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key table, it uses null replacement because the procedure supports null parameters. When you use null parameters, you get all foreign key values. */ DECLARE foreign_key_table CURSOR FOR SELECT tc.table_schema , tc.table_name , tc.constraint_name FROM information_schema.table_constraints tc WHERE tc.table_schema = IFNULL(lv_schema_name, tc.table_schema) AND tc.table_name = IFNULL(lv_table_name, tc.table_name) AND tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.table_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; /* Open a local cursor. */ OPEN foreign_key_table; cursor_foreign_key_table: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_table INTO lv_schema_name , lv_table_name , lv_constraint_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF; /* The nested calls to the columnList function returns the list of columns in the foreign key. Surrogate primary to foreign keys can be resolved with a simply query but natural primary to foreign key relationships require the list of columns involved in the primary and foreign key. The columnList function returns the list of foreign key columns in the dependent table and the list of referenced columns (or the primary key columns) in the independent table. */ SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' ' ,'ADD CONSTRAINT ',lv_constraint_name,' ' ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name)); /* Record the SQL statements. */ INSERT INTO command_list ( sql_command , sql_object , sql_constraint , sql_statement ) VALUES ('ALTER' ,'TABLE' ,'FOREIGN KEY' , sql_stmt ); END LOOP cursor_foreign_key_table; CLOSE foreign_key_table; END; $$
This is the
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
CREATE FUNCTION columnList ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64) , pv_constraint_name VARCHAR(64)) RETURNS VARCHAR(512) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE lv_column_count INT UNSIGNED; DECLARE lv_column_name VARCHAR(64); DECLARE lv_column_list VARCHAR(512); DECLARE lv_column_ref_list VARCHAR(64); DECLARE lv_referenced_table_schema VARCHAR(64); DECLARE lv_referenced_table_name VARCHAR(64); DECLARE lv_referenced_column_name VARCHAR(64); DECLARE lv_return_string VARCHAR(768); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key column. */ DECLARE foreign_key_column CURSOR FOR SELECT kcu.column_name , kcu.referenced_table_schema , kcu.referenced_table_name , kcu.referenced_column_name FROM information_schema.key_column_usage kcu WHERE kcu.referenced_table_schema = lv_schema_name AND kcu.table_name = lv_table_name AND kcu.constraint_name = lv_constraint_name ORDER BY kcu.column_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; SET lv_constraint_name := pv_constraint_name; /* Set the first column value. */ SET lv_column_count := 1; /* Open the nested cursor. */ OPEN foreign_key_column; cursor_foreign_key_column: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_column INTO lv_column_name , lv_referenced_table_schema , lv_referenced_table_name , lv_referenced_column_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF; /* Initialize the column list or add to it. */ IF lv_column_count = 1 THEN SET lv_column_list := lv_column_name; SET lv_column_ref_list := lv_referenced_column_name; /* Increment the counter value. */ SET lv_column_count := lv_column_count + 1; ELSE SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name); SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name); END IF; END LOOP cursor_foreign_key_column; CLOSE foreign_key_column; /* Set the return string to a list of columns. */ SET lv_return_string := CONCAT(lv_column_list,')',' ' ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' ,'(',lv_column_ref_list,');'); RETURN lv_return_string; END; $$
You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):
CREATE TABLE parent ( first_name VARCHAR(20) NOT NULL DEFAULT '' , last_name VARCHAR(20) NOT NULL DEFAULT '' , PRIMARY KEY (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE child ( child_name VARCHAR(20) NOT NULL , first_name VARCHAR(20) DEFAULT NULL , last_name VARCHAR(20) DEFAULT NULL , PRIMARY KEY (child_name) , KEY fk_parent(first_name, last_name) , CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name) REFERENCES parent (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
You call the
storeForeignKeys procedure for the
child table with this syntax:
CALL storeForeignKeys('studentdb', 'child');
You call the
storeForeignKeys procedure for all tables in a schema with this syntax:
CALL storeForeignKeys('studentdb', null);
While unlikely you’ll need this, the following calls the
storeForeignKeys procedure for all tables in all schemas:
CALL storeForeignKeys(null, null);
You can export the command sequence with the following command to a script file:
SELECT sql_statement INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM command_list;
While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.
Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.
For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:
Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:
You enter user defined types by entering a name value and choosing valid MySQL type value before clicking the Add button. When you’ve added your last user defined type, click the OK button instead of the Add button. The next screen shot shows how you can create Oracle Database 11g native data types, specifically the
VARCHAR2 data types.
Hopefully, this has shown that you can create User Defined Types let you use MySQL Workbench to create Oracle ERD models. Here’s an example of a table with Oracle’s
VARCHAR2 data types:
Yes, MySQL Workbench is a marvelous tool with wide potential for use to solve problems with MySQL and other databases.
One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.
It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.
Hope this helps those working with MySQL Workbench.
The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.
Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The
imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:
The imagedb ERD splits the foreign key references back to the
system_user table, which contains the individual user credentials. The
system_user table serves as the Access Control List (ACL) for the application.
Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.