Column Type Gotchas in Rails

The choice of column type is not necessarily a simple choice and depends on both the database you’re using and the requirements of your application.

:binary

Depending on your particular usage scenario, storing binary data in the database can cause big performance problems. Active Record doesn’t generally exclude any columns when it loads objects from the database, and putting large binary attributes on commonly used models will increase the load on your database server significantly. If you must put binary content in a commonly-used class, take advantage of the select method to only bring back the columns you need.
:boolean

The way that boolean values are stored varies from database to database. Some use 1 and 0 integer values to represent true and false, respectively. Others use characters such as T and F. Rails handles the mapping between Ruby’s true and false very well, so you don’t need to worry about the underlying scheme yourself. Setting attributes directly to database values such as 1 or F may work correctly, but is considered an anti-pattern.
:datetime and :timestamp

The Ruby class that Rails maps to datetime and timestamp columns is Time. In 32-bit environments, Time doesn’t work for dates before 1902. Ruby’s DateTime class does work with year values prior to 1902, and Rails falls back to using it if necessary. It doesn’t use DateTime to begin for performance reasons. Under the covers, Time is implemented in C and is very fast, whereas DateTime is written in pure Ruby and is comparatively slow.
:time

It’s very, very rare that you want to use a :time datatype; perhaps if you’re modeling an alarm clock. Rails will read the contents of the database as hour, minute, and second values, into a Time object with dummy values for the year, month, and day.
:decimal

Older versions of Rails (prior to 1.2) did not support the fixed-precision :decimal type and as a result many old Rails applications incorrectly used :float datatypes. Floating-point numbers are by nature imprecise, so it is important to choose :decimal instead of :float for most business-related applications.

 

If you’re using a float to store values which need to be precise, such as money, you’re a jackass.
Floating point calculations are done in binary rather than decimal, so rounding errors abound in
places you wouldn’t expect.
>>0.1+0.2 == 0.3

=>false
>>BigDecimal(‘0.1’) + BigDecimal(‘0.2’) == BigDecimal(‘0.3’)
=>true

:float

Don’t use floats to store currency values, or more accurately, any type of data that needs fixed precision. Since floating-point numbers are pretty much approxi mations, any single representation of a number as a float is probably okay. However, once you start doing mathematical operations or comparisons with float values, it is ridiculously easy to introduce difficult to diagnose bugs into your application.
:integer and :string

There aren’t many gotchas that I can think of when it comes to integers and strings. They are the basic data building blocks of your application,
and many Rails developers leave off the size specification, which results in the default maximum sizes of 11 digits and 255 characters, respectively. You should keep in mind that you won’t get an error if you try to store values that exceed the maximum size defined for the database column, which again, is 255 characters by default. Your string will simply get truncated. Use validations to make sure that user-entered data does not exceed the maximum size allowed.
:text

There have been reports of text fields slowing down query performance on some databases, enough to be a consideration for applications that need to scale to high loads. If you must use a text column in a performance-critical application, put it in a separate table.


Column Type Mappings in Rails

The reason for declaring a database column as type string is that Rails migrations are meant to be database-agnostic. That’s why you could (as I’ve done on occasion) develop using Postgres as your database and deploy in production to Oracle.

 

It is useful to have a reference of how migration’s generic types map to database-specific types. The mappings for the databases most commonly used with Rails are in Table below.

 
Each connection adapter class has a native_database_types hash which establishes the mapping described in Table below. If you need to look up the mappings for a database not listed in Table below, you can pop open the adapter Ruby code and find the native_database_types hash, like the following one inside the PostgreSQLAdapter 

 

 

NATIVE_DATABASE_TYPES = {
:primary_key => “serial primary key”.freeze,
:string => { :name => “character varying”, :limit => 255 },
:text => { :name => “text” },
:integer => { :name => “integer” },
:float => { :name => “float” },
:decimal => { :name => “decimal” },
:datetime => { :name => “timestamp” },
:timestamp => { :name => “timestamp” },
:time => { :name => “time” },
:date => { :name => “date” },
:binary => { :name => “bytea” },
:boolean => { :name => “boolean” },
:xml > { :name => “xml” }
}

CONNECT 2 DATABASES IN A SINGLE RAILS APPLICATION

Many a times , the need of the situation is to connect ,multiple databases to a single rails app.

This seems to be a bit complex issue since we are all used to connect a single database to a rails app.

But after some googling, I found out that in rails, it’s a damn simple thing.

I will show you how I did it.

I have a rails app called “multidbtest1”.

It has a table called as User.

I wanted to connect it to another database.So I created another app called “multidbtest3

It had a table called Customer.

My requirement is to use Customer table in multibdtest1.

We know that database connections are handled in database.yml file.

So we copy the contents of database.yml of multidbtest3 in database.yml of multidbtest1.

Thus, the database.yml file of multidbtest1 looks like:

# SQLite version 3.x

#   gem install sqlite3

development:

  adapter: sqlite3

  database: db/development.sqlite3

  pool: 5

  timeout: 5000

# Warning: The database defined as “test” will be erased and

# re-generated from your development database when you run “rake”.

# Do not set this db to the same as development or production.

test:

  adapter: sqlite3

  database: db/test.sqlite3

  pool: 5

  timeout: 5000

production:

  adapter: sqlite3

  database: db/production.sqlite3

  pool: 5

  timeout: 5000

—————————-copied content of multidbtest3—————————-

 #SQLite version 3.x

#   gem install sqlite3

development:

  adapter: sqlite3

  database: db/development.sqlite3

  pool: 5

  timeout: 5000

# Warning: The database defined as “test” will be erased and

# re-generated from your development database when you run “rake”.

# Do not set this db to the same as development or production.

test:

  adapter: sqlite3

  database: db/test.sqlite3

  pool: 5

  timeout: 5000

production:

  adapter: sqlite3

  database: db/production.sqlite3

  pool: 5

  timeout: 5000

But this seems to be obviously wrong, so we make some changes.In the database field , we must specify the name and path of the database.The modifies file looks like:

# SQLite version 3.x

#   gem install sqlite3

development:

  adapter: sqlite3

  database: db/development.sqlite3

  pool: 5

  timeout: 5000

# Warning: The database defined as “test” will be erased and

# re-generated from your development database when you run “rake”.

# Do not set this db to the same as development or production.

test:

  adapter: sqlite3

  database: db/test.sqlite3

  pool: 5

  timeout: 5000

production:

  adapter: sqlite3

  database: db/production.sqlite3

  pool: 5

  timeout: 5000

 

—————————copied content of multidbtest3—————————-

 #SQLite version 3.x

#   gem install sqlite3

multidbtest3_development:

  adapter: sqlite3

  database: C:/Users/Rushabh/RubymineProjects/multidbtest3/db/development.sqlite3

  pool: 5

  timeout: 5000

# Warning: The database defined as “test” will be erased and

# re-generated from your development database when you run “rake”.

# Do not set this db to the same as development or production.

multidbtest3_test:

  adapter: sqlite3

  database: :/Users/Rushabh/RubymineProjects/multidbtest3/db/test.sqlite3

  pool: 5

  timeout: 5000

multidbtest3_production:

  adapter: sqlite3

  database: :/Users/Rushabh/RubymineProjects/multidbtest3/db/production.sqlite3

  pool: 5

  timeout: 5000

That’s all!!  The database part is done.

Now how shall we use the database ?

Its even simpler.

Create a model class called “Customer”

The model file should look like :

class Customer < ActiveRecord::Base 

  establish_connection “multidbtest3_#{Rails.env}” 

end

That’s it.

Now in any controller we can access the contents of Customer table.

For eg :

@customers = Customer.find(:all)

Enjoy

Postgre SQL

  •  FEATURES OF POSTGRESQL:

PostgreSQL supports all of the features you would expect from an open source RDMS, including many that are found in commercial databases (Oracle, DB2, SQL Server) such as:

  • The basics: viewstriggersindexesforeign keys, ACIDity, transactionsquery optimization, comprehensive SQL support and data typesautovacuum (keeps your table statistics up to date).
  • The not so basics: Features that may not be seen on other DBMSes include reverse, partial and expression indexes, table partitioning, table inheritance, cursors, data domains, user-defined operators, arrays and regular expressions.
  • Procedural Languages: analogous to Oracle’s PL/SQL or SQL Server’s T/SQL, PostgreSQL supports internal procedural languages for when you need to get down and dirty with the data. Additionally, it supports a wide range of languages including Ruby.
  • Rules, which pretty much allow you to rewrite an incoming query. A typical use of Rules is to implement updatable views.
  • Multi-Version Concurrency Control: MVCC is how PostgreSQL (and other DBMSes) deal with concurrency and table locking. In practical terms, it allows for database reads while the data is being writen.
  • Write-Ahead-Log: the WAL is the mechanism by which PostgreSQL writes transactions to the log before they are written to the database. This increases reliability in the unlikely event of a database crash, as there will be a transaction log by which to rebuild the database’s state. PostgreSQL includes many configuration parameters to tweak the behavior of the WAL.
  • PostgreSQL scales up by efficiently using multi-core servers. It also sport an asynchronous processing API. Subselects are fast, you can refer to tmp tables more than once in a query and it can use more than one index per query, making it suitable for data warehousing applications as well.
  • tsearch2, which is PostgreSQL’s highly efficient full text search component. If you are committed to PostgreSQL, this is a very high performant search engine for PostgreSQL (as an alternative to solr orsphinx, for instance), with the added benefit that you’re not running a separate daemon or search service.
  • PostGIS for geospacial objects.
  • There are many replication options, although non of them are built into the core. This will change very soon, though.
  • You can tweak its brains out: open up postgresql.conf, and you’ll find many configuration options that can be tweaked to your application load and server capabilities. If you’re like me, this is lots of fun. I will say, however, that it will take time to understand many of the options and how they affect each other.
  • Excellent documentation, which not only goes through the basics of setting up and using PostgreSQL, but really gets into the details of the inner workings of the system. This is an invaluable resource, not only for day-to-day development but also for tweaking the database’s configuration files.
  • Much more in the contrib packages.
  •  ADDITIONAL INFORMATION LINK:

postgresql wiki

  • DOWNLOAD POSTGREsql :

download/

  •  MAJOR DIFFERENCES FROM MYSQL:

MYSQL Vs Postgresql