Category Archives: SQL

Quick BASH Script to Dump & Compress a MySQL Database

A quick script I whipped up to dump my MySQL database.
Usage: sh backthatsqlup.sh

(be warned that it dumps ALL databases. This can get huge uncompressed)


#!/bin/sh
# Isam (Biodegradablegeek.com) public domain 12/28/2008
# Basic BASH script to dump and compress a MySQL dump

out=sequel_`date +'%m%d%Y_%M%S'`.sql
dest=/bx/

function e {
  echo -e "n** $1"
}

e "Dumping SQL file ($out). May take awhile..."
#echo "oh snap" > $out
sudo mysqldump -u root -p --all-databases > $out
if [ $? -ne 0 ]; then
  e "MySQL dump failed. Check that server is up and your username/pass"
  exit 7
fi

e "Uncompressed SQL file size"
du -hs $out

e "Compressing SQL file"
gz=$out.tar.gz
tar -zvvcf $gz $out
rt=$?

if [ $rt -ne 0 ]; then
  e "tar failed (error=$rt). Will NOT remove uncompressed SQL file"
else
  e "Removing uncompressed SQL file"
  rm -f $out
  out=$gz

  e "Compressed SQL file size"
  du -hs $out
fi

e "Moving shit to '$dest'"
sudo mv $out $dest

download BackThatSqlUp.sh

How To Use Fixtures to Populate Your Database in Rails

UPDATE: I’ve been using this method for awhile now: http://railspikes.com/2008/2/1/loading-seed-data

Seed data is data that the app is dependent on. It is data that has to exist if you were to wipe the database clean and reload your schema. Some examples would be a list of cities/states, a list of categories, or the initial ‘admin’ user account.

Most people looking at this thread want seed data rather than to populate their database with test/generated content. For the latter, you can go the route below or try Forgery

This is a response to the email I’ve been getting asking me how to use fixtures to load data into a database.

You want to create dummy entries in your Rails app, either for testing, for development, or for production, to make your site appear popular. Whatever the reason, populating your database can be done easily using fixtures.

While rake/fixtures/migrations can get a lot more complex, this will be a brief introductory example.

Initial App setup

$ rails characters
$ cd characters/

Edit config/database.yml – We only need a development database. So open up PHPMyAdmin or the MySQL command shell and:

mysql> CREATE database characters_development;
Query OK, 1 row affected (0.00 sec)

(I’m assuming you’re using MySQL. You can use anything; SQLite, Postgres, etc..)

Create a model and a table in the database (using a migration)

$ script/generate model Character
      exists  app/models/
      exists  test/unit/
      exists  test/fixtures/
      create  app/models/character.rb
      ....

$ vim db/migrate/001_create_characters.rb

Sexy migration:

class CreateCharacters < ActiveRecord::Migration
  def self.up
    create_table :characters do |t|
      t.string  :name, :alias, :motto
      t.timestamps
    end
  end

  def self.down
    drop_table :characters
  end
end

Now migrate development (default environment):

$ rake db:migrate

Create the characters fixture

$ vim test/fixtures/characters.yml

Continue reading How To Use Fixtures to Populate Your Database in Rails

Simple Way to Populate a Database in Rails

This is how I populate my database when I have a lot of data but can’t be bothered to write more than a quick throw-away hack. This doesn’t use fixtures, nor migrations (nothing wrong with them, I wuv migrations). Just a ruby file and the Rails console (this is optional actually).

I create a new rb file in lib/ (you can put your files in a sub-directory or anywhere ‘load’ can find them), then write the data I want inserted into the database in a new function in that file. I do so exactly as I would insert data in Rails. Model.create, Model.new, etc…

I then get into the Rails console (ruby script/console) and do load ‘file.rb’ and simply call the function. The database used will vary according to the environment you’re in (test, development, etc). Using ‘load’ every time you call your function(s) is preferred. Load will keep reloading the file (as opposed to ‘require’ which only reads a file once), staying up to date with edits you’re making to the file. For example, in the console:

>> load ‘funk.rb'; add_default_settings

(funk.rb is in /lib/ and add_default_settings is a function in that file)

As of 2.x, Rails now has rake db:migrate:reset and db:migrate:redo, which sends you down one migration and then back up to the current migration (or you can decide how many hops to take back using STEP=n). This is great, especially for tasks like populating a database.

So why don’t I use migrations? I do, but sometimes, especially in a proof-of-concept or throw-away app, I find it faster to skip the proper methods and write up a quick function. If you think that’s bad, you should see my lingering Python addiction. Sometimes I catch myself metaprogramming Ruby code in Python.

Understanding Basic Database Relationships in Rails

Nixon ERD

This short tutorial will be beneficial for you if database relationships and keywords like belongs_to and has_many confuse you, or if you’re trying to find out how relationships are implemented in Rails. As we create a small demonstration project, you’ll see that one beauty of Rails is how it does most of the work gluing everything together, after you’ve supplied it with information about your database’s structure.

But first — why bother learning about relationships? Very simply, they eliminate a major problem called an update anomaly, and they will probably save you disk space. Having info repeated in multiple entries can be problematic. How would you update a mass misspelling? Would you even notice a misspelled entry? Database normalization and multiple intertwined tables (via relationships) can curb this problem. Fortunately, ActiveRecord makes this easy.

For example, if you store the name and location of all your users in the same database table, you might be wasting disk space by having the same information repeated in multiple entries. You would be wasting a lot of space if your clam-cake-vendor-review site has hundreds of users living in “the State of Rhode Island and Providence Plantations.” This can be eliminated by having the locations tied to unique IDs in their own table, and associated to a user by their ID. This also makes renaming a location easy. Changing “the State of Rhode Island and Providence Plantations” to “Ocean State” is only done in one location, once.

Rather than going over all possible types of relationships here, I will be covering the very basics; Enough to help you grasp the main idea and see how it is implemented in Rails. Let’s begin by designing a simple project.
Continue reading Understanding Basic Database Relationships in Rails

Using Vim as a Complete Ruby on Rails IDE

vi traced with an optical mouse

NOTE: If you are experiencing segmentation faults with vim and rails.vim, see this post.

When coding in Ruby on Rails, you’ll usually be switching between files and running scripts a lot. It can be time-consuming and frustrating coding Rails using a traditional text editor designed for working on big files individually. Vim lets you hop around within a file with enough speed to activate the cosmic treadmill – but without a plethora of hacks and custom key mappings, it’s weak as a Rails IDE. Fortunately, for those of us who are reluctant to kick the vim habit, Tim Pope comes to the rescue with rails.vim; A plugin that makes working with Rails in vim painless and efficient. In this guide, I will explain how to install and use rails.vim, along with a few other plugins you’ll find useful when writing Rails applications.
Continue reading Using Vim as a Complete Ruby on Rails IDE