Install Mysql and Postgres in Mac OS through MacPorts

Here are two greats links on how to installing mysql and postgres on to your Mac OS.

1.Installing PostgreSQL on Leopard using MacPorts
2.Installing MySQL on Mac OS X Leopard using MacPorts

Connecting to various DBs by using ant’s sql command.

As you see from my previous blog, I set up oracle client to connect to the remote DB server to do the debug. If you just deal with one DB server, this should be fine, what if you need to test against various DB servers, like mysql, postgres, oracle and sqlserver, which is our riftsaw project need to be tested against, so instead of installing all of these clients, I am using the Apache Ant’s sql command.

I added a command to show all of tables in the db, below is the build.xml that I used.

 <target name="db.show.tables"
             depends="log.properties, copy.ojdbc"
             description="show tables in db">
   <sql driver="${driver}"
        url="${connection.url}"
        userid="${username}"
        password="${password}"
        onerror="continue"
        print="true">
     ${show.table.sql}
     <classpath>
       <fileset dir="drivers">
           <include name="*.jar"/>
       </fileset>
     </classpath>
   </sql>
   </target>

Different database vendor has its own syntax for showing tables, as I defined below.

 <condition property="show.table.sql" value="show tables;">
       <equals arg1="${database}" arg2="mysql" />
 </condition>

 <condition property="show.table.sql" value="select table_name from information_schema.tables where table_schema='public' and table_type='BASE TABLE';">
       <equals arg1="${database}" arg2="postgres" />
 </condition>

 <condition property="show.table.sql" value="select table_name from tabs;">
       <equals arg1="${database}" arg2="oracle" />
 </condition>

 <condition property="show.table.sql" value="select name from riftsaw..sysobjects where xtype = 'U';">
       <equals arg1="${database}" arg2="sqlserver" />
 </condition>

By using this approach, you don’t need to install those db clients, which could save you a lot of time.

Using Oracle Instant and Sqlplus in Fedora

In case you are working against Oracle database, and don’t want to install the whole Oracle DB mess, just want to use the sqlplus to connect to an existing db. Then see this post.

I installed oracle-instantclient11.2-basic-11.2.0.1.0-1.i386.rpm and oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.i386.rpm in my box. (Fedora 10)

Once you connect it successfully. simply run:

select table_name from tabs;

to show the tables from your connected database.

Create Database and user in Postgres and Mysql.

Since I worked in SOA area, haven’t touched the database for couple years, although in some projects, I need to test against various database (Mysql, Postgres, Oracle etc), but it is very basic stuff, like create database, users etc.

This post basically is a memo for me, I need to work with various DBs from time to time, and think it is best to record it in my blog, instead of looking into the manual again. ;-). Also, you could see this is a follow-up post for my previous postgres installation blog.

1. Postgres

1) connect Postgres

psql -h localhost -U postgres

2) add user

create user jeff with password ‘jeff’

3) create database

create database jeffdb

4) grant db to user

grant all privileges on database jeffdb to jeff

Log out with “\q” command

Log in jeffdb through user jeff:

psql -d jeffdb -U jeff

2. Mysql

1) connect mysql

mysql -u root -p ‘urpassword’

2) create database

create database jeffdb

3) allow user jeff to connect to the server from localhost using the password jeff

grant usage on *.* to jeff@localhost identified by ‘jeff’;

4)grant all privileges on the jeffdb database to this user

grant all privileges on jeffdb.* to jeff@localhost ;

Log out and log in through jeff user.

mysql -u jeff -p’jeff’ jeffdb

[Reference]
1. How to add postgres user and create db
2. Create mysql database and set privileges into a user