rails symphonies

SQLite is good for development and testing, and depending on your needs production even. By default Typo uses SQLite for production. The greatest advantage is that it’s light. (like duh), and very easy to deploy.

Note: There are two versions of SQLite, version 2 and 3. They produce databases which are incompatible with each other.

Version 2

Enter into the Terminal, and as root execute:
yum install gcc ruby-devel sqlite2-devel
gem install sqlite-ruby

If you see this, you want the ruby version, so select 1 (enter it in).

Select which gem to install for your platform (i486-linux)
 1. sqlite-ruby 2.2.3 (ruby)
 2. sqlite-ruby 2.2.3 (mswin32)
 3. sqlite-ruby 2.2.2 (ruby)
 4. sqlite-ruby 2.2.2 (mswin32)
 5. Skip this gem
 6. Cancel installation
>

Edit the file RAILS_ROOT/config/database.yml. For now we are using the development environment. The environment you are deploying your database should look like so.

development:
  adapter: sqlite
  dbfile: db/development.sql

Version 3

Enter into the Terminal, and as root execute:
yum install gcc ruby-devel sqlite-devel
gem install sqlite3-ruby

If you see this, you want the ruby version, so select 2 (enter it in).

Select which gem to install for your platform (i486-linux)
 1. sqlite3-ruby 1.2.1 (mswin32)
 2. sqlite3-ruby 1.2.1 (ruby)
 3. sqlite3-ruby 1.2.0 (mswin32)
 4. sqlite3-ruby 1.2.0 (ruby)
 5. Skip this gem
 6. Cancel installation
>

Edit the file RAILS_ROOT/config/database.yml. For now we are using the development environment. The environment you are deploying your database should look like so.

development:
  adapter: sqlite3
  dbfile: db/development.sql

Conclusion

There you have it, your database is now set up!

So this time we will take a break off Rails, and go straight into the actually using Ruby alone. No abstraction.

Ruby has a simple abstraction layer (Ruby DBI), but lets skip that for now. We don’t want abstraction.

Installing MySQL

Well first you need MySQL installed. I have written a guide on how to get it installed, and some of the default security issues. Its on a page about using MySQL with Rails, but the installation is pretty much independent.

There are two ways to install MySQL in Ubuntu Feisty Fawn. The straight forward way is to install via the apt-get package, the second, the traditional RubyGems way.

Installing MySQL using apt-get

This shouldn’t be so tough.

sudo apt-get install libmysql-ruby

Nah. Not tough at all.

Installing MySQL using RubyGems

First lets ensure that our Ruby development library, and the MySQL client libraries are installed.

sudo apt-get install ruby-1.8dev libmysqlclient15-dev build-essentials

So let’s pull down the MySQL libraries via RubyGems

sudo gem install mysql

Note: If you see the following, choose for your platform. In this case I’d select number 3 for ruby.

Select which gem to install for your platform (i486-linux)
 1. mysql 2.7.3 (mswin32)
 2. mysql 2.7.1 (mswin32)
 3. mysql 2.7 (ruby)
 4. mysql 2.6 (ruby)
 5. Skip this gem
 6. Cancel installation
> 

Documentation

There is documentation for the MySQL library online . So you can follow that.

Getting started with MySQL

Now if you ever want to use MySQL in your ruby application. You can insert the following at the top, and your good to go.

So let’s make a connection to MySQL.

In fact, if it fails to make a connection, it will raise a Mysql::Error.

Mysql::Error: Access denied for user 'root'@'localhost' (using password: YES)

So if you really want to play safe, you can wrap a exception handling block around it.

Or you could just let the raised exception halt the program entirely. But that’s not cool.

Simple Queries

So let’s begin with some simple queries!

For starters, I wrap my code in a exception handling block, to ensure that no matter what happens in the code. The database connection is closed.

The returned results if of the class Mysql::Result, so there are various methods to use in how the results are interpreted. Check the documentation and scroll down to Mysql::Result. If you are familiar with other MySQL connectors, this should be a breeze.

In this instance, I used the each_hash method, so that each row is treated as a hash. (If you come from the PHP world, this is similar to mysql_fetch_assoc)
The each_hash method is an iterator, and in the style of Ruby iterators, makes it very easy to iterate over an return result set.

Prepared Statements

The Mysql Ruby connector also supports prepared statements.

Conclusion

There you have it, a quick run down of using Mysql with Ruby. Hopefully it should get you started.

MySQL is a rock solid database, used all around the world, for all kinds of situations. Its use escalated with the usage of PHP and together became known as the LAMP (Linux Apache MySQL and PHP) stack.

Warning: By default, root access to MySQL is open. We will deal with this later.

Installation

Enter into the Terminal:

sudo apt-get install mysql-server

Edit the file RAILS_ROOT/config/database.yml. For now we are using the development environment. The environment you are deploying your database should look like so:

development:
  adapter: mysql
  database: railsapp_development
  username: root
  password:
  host: localhost

Note: Modify to fit your needs

If you don’t have a database created yet, execute:

mysqladmin -u root create railsapp_development

Note: Where railsapp_development should be the same as the database field we configured.

And there you have it! Your Rails application will now work with MySQL.

Continue if you want to learn more about MySQL.

Locking down MySQL

By default 3 accounts are associated to the ‘root’ user. Each one without a password. Definitely not safe.

So we’ll remove 2 of those accounts, and give a password to the remaining one.

Lets enter MySQL:

mysql -u root

use mysql;
DELETE FROM user WHERE User = 'root' && Host != 'localhost';
UPDATE user SET Password=PASSWORD('new-password') WHERE User='root';
FLUSH PRIVILEGES;

Note: Make sure to specify the password in place of ‘new-password’!

Now in order to access MySQL via root, you’ll have to use:

mysql -u root -p

And you should be prompted for your password.

Note: People tend to append your password after the -p flag. Do not do this. It will make the password visible via ps.

Even man mysql states:

Specifying a password on the command line should be considered insecure. See Section 7.6, “Keeping Your Password Secure”.

If you immediately want to access your railsapp_development database, you can just specify it like so.

mysql -u root -p railsapp_development

Easy MySQL Administration

Coming from a PHP background, I am familiar with phpMyAdmin, but there are other solutions out there. The problem with phpMyAdmin is that it requires the installation of both Apache and PHP, which opens more ports on your computer, and adds uneeded extra software on your computer.

Another solution is to use mysql-admin (sudo apt-get install mysql-admin ), though I haven’t tried using it.

phpMyAdmin can be installed via:

sudo apt-get install phpMyAdmin

Conclusion

There you have it, your database is now set up!