Rake: A basic intro of Rake and db migration using rake

Rake is a simple ruby build program with capabilities similar to make. Rake has the following features:

• Rakefiles (rake‘s version of Makefiles) are completely defined in standard Ruby syntax. No XML files to edit. No quirky Makefile syntax to worry about (is that a tab or a space?)
• Users can specify tasks with prerequisites.
• Rake supports rule patterns to synthesize implicit tasks.
• Flexible FileLists? that act like arrays but know about manipulating file names and paths.
• A library of prepackaged tasks to make building rakefiles easier.

You can get the tasks lists by executing ruby --tasks a selective approach of previous would be ruby --tasks db which shows all the tasks associated with db

• rake db:fixtures:load # Load fixtures into the current environment's database. Load specific fixtures using FIXTURES=x,y
• rake db:migrate # Migrate the database through scripts in db/migrate. Target specific version with VERSION=x
• rake db:remove_unknown # Remove all migrations in db/migrate that is missing from ENVMIGRATION_DIR? directory
• rake db:schema:dump # Create a db/schema.rb file that can be portably used against any DB supported by AR
• rake db:schema:load # Load a schema.rb file into the database
• rake db:sessions:clear # Clear the sessions table
• rake db:sessions:create # Creates a sessions table for use with CGI::Session::ActiveRecordStore?
• rake db:status # Display schema status in parsable YAML format
• rake db:structure:dump # Dump the database structure to a SQL file
• rake db:test:clone # Recreate the test database from the current environment's database schema
• rake db:test:clone_structure # Recreate the test databases from the development structure
• rake db:test:prepare # Prepare the test database and load the schema
• rake db:test:purge # Empty the test database
• rake db:unmigrate # Remove a specific migration based on MIGRATION_FILE (no validation is done)

Migration

• rake db:migrate : (Migrate the database through scripts in db/migrate. Target specific version with VERSION=x)
• Generate script for your acction example: script/generate migration DummyMigration?
• it creates a file something like 01211257749_dummy_migration.rb in the directory vim db/migrate/
• and also makes an entry in history.txt in the same folder do vim db/migrate/history.txt to check that

Unmigration

• Revert back your changes using the following syntax

rake db:unmigrate MIGRATION_FILE=db/migrate/01211257749_dummy_migration.rb
rake --tasks db

Structure Dump

• Syntax: rake db:structure:dump * creates a file something like development_structure.sql on the db folder * you can change the default schema by modifying the database config file
• cd config/
• vim database.yml
• rake db:structure:dump RAILS_ENV=production
• creates a structure dump for vim production_structure.sql

Mysql: Bulk Inserts involving updates

Lets think of a situation: where you have to bulk insert,
but there might be some data which is already there in the db, and you just want to modify it.
In such cases, something like the following query will help you.

Insert
 Into t1 (id,val1,va2)
 Values (1,1,1),(2,2,2),(3,3,3)
On Duplicate Key Update
 val1= val1+ values(val1),
 val2= val2 + values(val2)
The key is to use the clause "On Duplicate Key Update"

Mysql: Show user defined functions for some schema

If you have to look for the user defined functions in a mysql schema, here is the sample syntax to do so:

select
*
from
information_schema.ROUTINES
where
ROUTINE_SCHEMA=[schemaname] \G

PS: I use "\G" for better formatting, its not something which is necessary.

Mysql: Find Indexes on a Table

Select
COLUMN_NAME,
INDEX_NAME,
INDEX_TYPE
From
information_schema.STATISTICS
where
TABLE_NAME=[table name]
and TABLE_SCHEMA=[schema name]