Web Base SQL schema design

This amazing online SQL design (ERD) tool called wwwsqldesigner, is a great solution for those of us who get too troubled by designing simple apps database layouts. (NOTE: you should always hire a professional DBA)

Checkout the Homepage on GoogleCode, and Live demo.

Rails Migrations - Running a Single Migration

We all know the good old

rake db:migrate

And we all know the Rails 2.x

rake db:migrate:rollback

to go back in time and fix what we did.
Now, in Rails 2.x you can specify a single migration by specifing one of the each

rake db:migrate:up VERSION=<migration_timestamp> # Runs the self.up
rake db:migrate:down VERSION=<migration_timestamp> # Runs the self.down

Note that migrations are meant to be concurrent and that's the most basic purpose in which they are there for, executing an out of line migration may cause you problems in the future, so try to stay as clean as possible when dealing with migrations

ActiveRecord.js: a Relational js ActiveRecord implementation

 

ActiveRecord.js is the first released component of ActiveJS a JavaScript framework initiative sponsored by Aptana. This project is still in it's early stages and we welcome your thoughts and ideas on the future of JavaScript application development. Since this is a GitHub hosted project, if you would like to directly contribute we recommend you fork the project and initiate a pull request to merge your changes

The ActiveRecordjs Homepage

Using ruby on rails with Oracle Legacy.

Great slide on SlideShare.
introduces the ruby-plsql gem, which makes it easier to preform database functions and stored procedures from ruby/rails applications (and supports bind variables).
i personally wrote a module of my own to handle this need (which supports bind variables as well).
Check out the slide and pay attention to the oracle_enhanced_adapter now available.
The slide is over here at SlideShare.

Ruby on Rails and Oracle on Mac Os Leopard

Overview

The nightmare is over.
Just until the latest Oracle libraries update (finally released a X86 library pack for mac) it was nesecerry to use 2 versions of ruby, a universal and a ppc version. Sadly, when running PPC, the benchmarking were terrible and it had some very annoying freezes and other stuff that would simple make you want to jump off the roof.
BUT! (:) ) times had changed, Oracle (as mentioned) released an X86 Intel compatible library pack for MacOs users and therefore ended my misery,
Woohoo! That makes the entire process of connection Ruby on Rails and oracle on Leopard about as 100 times less complicated than before, so I’ve posted it here to let everyone enjoy.
I assumes that you’re using Rails 2.0 or greater (Why not really?).

IMPORTART!!!

If you already connected Oracle and Ruby on Rails using the old way, please preform the "Cleanup" step first.

Oracle Libraries

The new Intel Mac versions are available from the Oracle downloads site. Install them in /Library/Oracle/.
You can do side-by-side installations in folders with whatever names you want, since apps find them by using the $ORACLE_HOME environment variable (and it’s friends). I’ve got mine in /Library/Oracle/instantclient_10_2.
Also make sure that you’ve got the files required to run sqlplus and the sdk along with the basic. You can drop those in the same directory.

Symlink the libraries

In the directory where you’ve installed the instant client, run this:

ln -s libclntsh.dylib.10.1 libclntsh.dylib

Set the environment variables correctly

You’ll probably want to put these lines in your /etc/profile , but they also must be run from the command line to take effect (you can also "source /etc/profile"):

export ORACLE_HOME=/Library/Oracle/instantclient_10_2 <= Change to your library!
export TNS_ADMIN=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME

Oracle! giddie up!

First you'll need to install the Active Record Oracle adapter,

sudo gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org

which is how ActiveRecord deals with Oracle.
It doesn’t, however, install the Ruby oci8 driver, which is how Ruby talks to Oracle (yeah, annoying).

Important!!!

Have you installed the Oracle Instant Client SDK ?
good.


Get the lastest the oci8 library. Download it and unpack the file in the finder: it should unzip into ~/Downloads/ruby-oci8-x.x.x.

Now we can finish configuring the environment before we compile the library.

cd ~/Downloads/ruby-oci8-x.x.x
export SQLPATH=$ORACLE_HOME
export RC_ARCHS=i386
ruby setup.rb config
make
sudo make install

oh joy, scrolling lines of doom will pass in front of you and hopefully you'll see no errors and burst into tears.

Show me!

At this point, we’re almost done. Let's see it working.

irb
require 'oci8'
==> true

or

irb
require 'rubygems'
==> []
require 'oci8'
==> true

Configure your database.yml

In your database.yml, use the following to make it work:

development:
adapter: oracle
database: your_instance_name
username: your_user_name
password: your_password

The database name = > comes straight out of your tnsnames.ora file. You don’t need to specify any other connection information in database.yml, since the tnsnames.ora file has everything you need.

if you are using the oracle Express edition, it should look something like that.

development:
  adapter: oci
  host: //db_hostname:db_port/xe <== Oracle port is usually 1521
  username: username
  password: password
  cursor_sharing: similar
  prefetch_rows: 100

note: last 2 lines are some tweaking for a better Oracle performance.

Cleanup: Fix the ruby_fat and ruby_ppc setup

if you have installed Oracle libraries using the old way, you'll be happy to remove the mess it made out of your ruby installation and happily, it’s quite simple.
Just remove the ruby_ppc files, and the symlinks to them (called ruby, and rename ruby_fat as ruby:

cd /usr/bin <== or wherever you put them.
sudo rm ruby
sudo rm ruby_ppc
sudo mv ruby_fat ruby

and

cd /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin
sudo rm ruby
sudo rm ruby_ppc
sudo mv ruby_fat ruby

Then, you should also remove the 2 management scripts:

sudo rm /usr/bin/ppc_ruby.sh
sudo rm /usr/bin/fat_ruby.sh

And that’s enough for the cleanup.

Tip: Installing Mysql Gem on Leopard

First, i want to make it clear that i am totally for using Oracle Express Edition, and not in Mysql, although you may need a real DBA, it is something you will want anyway, even with Mysql for my opinion.

Working with the Ruby-Based Mysql libraries is not recommended, it freezes with no prior warning in the middle of a transaction and not very stable at all for my opinion (i will update important notes from the comments if you have any other enlightenment about it).

anyway, since Leopard is running on the Intel based Macs, you'll need to specify the ARCH flag in the gem install command.

first, make sure you installed Mysql from the official package at mysql.com

then,


sudo env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config


Imporatant!!!! make sure you update your XCode libraries from the Leopard CDs.

Rails Tip: Annotate models

Ever happened to forget what columns does a Model has?
well, instead of Model.column_names, you can easily install the annotate_models plugin which adds the models schema info as a comment at the top of your model.rb file.

install from

script/plugin install http://repo.pragprog.com/svn/Public/plugins/annotate_models


and from the project root

rake annotate_models


Rails Tip: AR eager loading and :select

The AR::Base.find support the options of :include (for adding associations as JOINs in the select) and :select (which pinpoints the columns you wish to select.
For some reason, it is impossible to do them both toghther for example

Article.find(:all, :include => :author, :select => 'authors.name')

This line will raise an exception.
Luckily, there is the select_with_include gem which enables this feature!

first, install the gem

gem install select_with_include

next, require it in your environment configuration file (or in environment.rb)

require 'include_with_select'

Now, you can use the :select option, remembering to use the table name even if a column name is not unique.
There is a single fault back, in case you want to select all the table columns, you need to write them all, one by one. yeah, it sucks.

Rails and Oracle, new blog

Hi all,
I am happy to announce that Oracle On Rails, the too-much-anticipated blog from Ohad Asulin about working with Oracle and Rails, Ohad is one of my colleagues and is an excellent Oracle-Ninja, i strongly advice going up on this one (and generally move to Oracle for large scale applications),
you might really like to hear what this guy has to say.

Connection Options

i found a way to get the connection parameters (other than reading database.yml) of the current database connection.

1. def connection_hash  
2.   ActiveRecord::Base.connection.instance_variable_get(:@config_options)  
3. end