MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for September, 2014

Using MySQL Workbench

with 2 comments

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:

cat Data/cit225/mysql/lab1/apply_mysql_lab1.sql

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:

\. /home/student/Data/cit225/mysql/lab1/apply_mysql_lab1.sql

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:

show tables;

It displays:

+---------------------+
| 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.

mysql-workbench

Here are the instructions for the lab with MySQL Workbench:

FedoraMySQLWorkbenchHome

  1. 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.

MySQL_Lab3_01

  1. After clicking the the symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.

MySQL_Lab3_02

  1. Click the Test Connection button to see if it works.

MySQL_Lab3_03

  1. 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.

MySQL_Lab3_04

  1. Click the OK button to continue.

MySQL_Lab3_05

  1. Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.

MySQL_Lab3_03

  1. 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.

MySQL_Lab3_06

  1. The MySQL Workbench launches in the default view.

MySQL_Lab3_07

  1. Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.

MySQL_Lab3_08

  1. 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.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database.

MySQL_Lab3_10

  1. This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.

MySQL_Lab3_11

  1. 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.

MySQL_Lab3_12

  1. 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.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.

MySQL_Lab3_14

  1. 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.

MySQL_Lab3_15

  1. 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.

MySQL_Lab3_15

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_16

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_17

  1. 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.

MySQL_Lab3_18

  1. This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.

MySQL_Lab3_19

  1. 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.

MySQL_Lab3_20

  1. 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.

MySQL_Lab3_21

  1. 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.

MySQL_Lab3_22

  1. 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.

Written by maclochlainn

September 25th, 2014 at 5:10 pm

Fedora PostgreSQL Install

without comments

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.

Written by maclochlainn

September 24th, 2014 at 2:43 am

Add Color to VIM?

without comments

In Fedora 20, documents have no color coding when you edit them with vi or vim. That’s because Fedora installs vim-minimal by default. You can check what’s running with the following command at a shell prompt in the terminal:

rpm -qa | grep vim

It should print the following to console:

vim-minimal-7.4.179-1.fc20.x86_64

You can download and install vim with the enhanced version by using the following syntax:

sudo yum install vim-enhanced
Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package vim-enhanced.x86_64 2:7.4.417-1.fc20 will be installed
--> Processing Dependency: vim-common = 2:7.4.417-1.fc20 for package: 2:vim-enhanced-7.4.417-1.fc20.x86_64
--> Processing Dependency: libgpm.so.2()(64bit) for package: 2:vim-enhanced-7.4.417-1.fc20.x86_64
--> Running transaction check
---> Package gpm-libs.x86_64 0:1.20.7-3.fc20 will be installed
---> Package vim-common.x86_64 2:7.4.417-1.fc20 will be installed
--> Processing Dependency: vim-filesystem for package: 2:vim-common-7.4.417-1.fc20.x86_64
--> Running transaction check
---> Package vim-filesystem.x86_64 2:7.4.417-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package              Arch         Version                  Repository     Size
================================================================================
Installing:
 vim-enhanced         x86_64       2:7.4.417-1.fc20         updates       1.0 M
Installing for dependencies:
 gpm-libs             x86_64       1.20.7-3.fc20            fedora         32 k
 vim-common           x86_64       2:7.4.417-1.fc20         updates       5.9 M
 vim-filesystem       x86_64       2:7.4.417-1.fc20         updates        11 k
 
Transaction Summary
================================================================================
Install  1 Package (+3 Dependent packages)
 
Total download size: 7.0 M
Installed size: 23 M
Is this ok [y/d/N]: y
Downloading packages:
(1/4): gpm-libs-1.20.7-3.fc20.x86_64.rpm                    |  32 kB  00:00     
(2/4): vim-enhanced-7.4.417-1.fc20.x86_64.rpm               | 1.0 MB  00:03     
(3/4): vim-filesystem-7.4.417-1.fc20.x86_64.rpm             |  11 kB  00:03     
(4/4): vim-common-7.4.417-1.fc20.x86_64.rpm                 | 5.9 MB  00:05     
--------------------------------------------------------------------------------
Total                                              1.3 MB/s | 7.0 MB  00:05     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 2:vim-filesystem-7.4.417-1.fc20.x86_64                       1/4 
  Installing : 2:vim-common-7.4.417-1.fc20.x86_64                           2/4 
  Installing : gpm-libs-1.20.7-3.fc20.x86_64                                3/4 
  Installing : 2:vim-enhanced-7.4.417-1.fc20.x86_64                         4/4 
  Verifying  : gpm-libs-1.20.7-3.fc20.x86_64                                1/4 
  Verifying  : 2:vim-common-7.4.417-1.fc20.x86_64                           2/4 
  Verifying  : 2:vim-enhanced-7.4.417-1.fc20.x86_64                         3/4 
  Verifying  : 2:vim-filesystem-7.4.417-1.fc20.x86_64                       4/4 
 
Installed:
  vim-enhanced.x86_64 2:7.4.417-1.fc20                                          
 
Dependency Installed:
  gpm-libs.x86_64 0:1.20.7-3.fc20          vim-common.x86_64 2:7.4.417-1.fc20  
  vim-filesystem.x86_64 2:7.4.417-1.fc20  
 
Complete!

You can now type vim to edit files in color but vi will still be in black and white.

Written by maclochlainn

September 13th, 2014 at 2:06 am

Posted in Fedora,Linux,vim

MySQL Workbench on Fedora

with 7 comments

The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:

<shell> uname -r

My Fedora is at the following version:

3.14.8-200.fc20.x86_64

Then, you can install MySQL Workbench with yum, like this:

<shell> sudo yum install mysql-workbench

It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Running transaction check
---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed
---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed
---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed
---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed
---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed
---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                    Arch    Version        Repository              Size
================================================================================
Installing:
 mysql-workbench-community  x86_64  6.1.7-1.fc20   mysql-tools-community   24 M
Installing for dependencies:
 compat-lua-libs            x86_64  5.1.5-1.fc20   updates                158 k
 ctemplate                  x86_64  2.2-5.fc20     fedora                 174 k
 gtkmm24                    x86_64  2.24.4-2.fc20  fedora                 748 k
 libzip                     x86_64  0.11.2-1.fc20  updates                 59 k
 tinyxml                    x86_64  2.6.2-4.fc20   updates                 49 k
 vsqlite++                  x86_64  0.3.13-3.fc20  updates                 58 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 26 M
Installed size: 119 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm              | 158 kB  00:01     
(2/7): ctemplate-2.2-5.fc20.x86_64.rpm                      | 174 kB  00:01     
(3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm                      |  49 kB  00:00     
(4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm                     | 748 kB  00:01     
(5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm                   |  58 kB  00:00     
(6/7): libzip-0.11.2-1.fc20.x86_64.rpm                      |  59 kB  00:02     
(7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm    |  24 MB  00:08     
--------------------------------------------------------------------------------
Total                                              2.9 MB/s |  26 MB  00:08     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : gtkmm24-2.24.4-2.fc20.x86_64                                 1/7 
  Installing : libzip-0.11.2-1.fc20.x86_64                                  2/7 
  Installing : vsqlite++-0.3.13-3.fc20.x86_64                               3/7 
  Installing : ctemplate-2.2-5.fc20.x86_64                                  4/7 
  Installing : compat-lua-libs-5.1.5-1.fc20.x86_64                          5/7 
  Installing : tinyxml-2.6.2-4.fc20.x86_64                                  6/7 
  Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64                7/7 
Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type)
  Verifying  : tinyxml-2.6.2-4.fc20.x86_64                                  1/7 
  Verifying  : compat-lua-libs-5.1.5-1.fc20.x86_64                          2/7 
  Verifying  : ctemplate-2.2-5.fc20.x86_64                                  3/7 
  Verifying  : vsqlite++-0.3.13-3.fc20.x86_64                               4/7 
  Verifying  : mysql-workbench-community-6.1.7-1.fc20.x86_64                5/7 
  Verifying  : libzip-0.11.2-1.fc20.x86_64                                  6/7 
  Verifying  : gtkmm24-2.24.4-2.fc20.x86_64                                 7/7 
 
Installed:
  mysql-workbench-community.x86_64 0:6.1.7-1.fc20                               
 
Dependency Installed:
  compat-lua-libs.x86_64 0:5.1.5-1.fc20     ctemplate.x86_64 0:2.2-5.fc20       
  gtkmm24.x86_64 0:2.24.4-2.fc20            libzip.x86_64 0:0.11.2-1.fc20       
  tinyxml.x86_64 0:2.6.2-4.fc20             vsqlite++.x86_64 0:0.3.13-3.fc20    
 
Complete!

After successfully installing MySQL Workbench, you can launch it with the following command:

<shell> mysql-workbench

It should launch the following MySQL Workbench home page (click on it to see the full size image):

FedoraMySQLWorkbenchHome

Written by maclochlainn

September 13th, 2014 at 12:00 am

E-Business Customers

without comments

It seems the Oracle E-Business Suite stymies newbies. Naturally, it’s complex and requires some research before you try to develop any customization.

Having recently fielded a question about how to manage, search, create and update customers for a third party order entry system, I’m always surprised by how little people know about Oracle’s Trading Community Architecture (TCA). They likewise frequently don’t know that there is already a documented process, like the one defined in the Oracle Trading Community Architecture User Guide:

You also have the Oracle Trading Community Architecture Reference Guide, which provides more detailed information. I strongly recommend you read these before trying to Google a solution that may or may not meet your needs. Naturally, good PL/SQL coding skills make this type of activity straightforward. That’s why Oracle staff should master PL/SQL.

Written by maclochlainn

September 4th, 2014 at 11:25 am