Database Visualization

Keeping documentation up-to-date is always a pain in the ass. I was recently working on updating our database diagrams and thought that there may be a way to automate the process.

I found 2 solutions:

1. XMI
2. RailRoad

For solution (1), it worked, but I had to tweak it slightly. Because we are using PostgreSQL, I had a problem when UmlDumper tried to execute current_database for the adapter. This works fine for the MySQL adapter, but no such function exists for the PostgreSQL adapter. I quickly hacked it and just replaced it with a general name for our database, as we didn't have to be dead-on here.

I was quite disappointed when Dia did not know how to open up the XMI file. So, searching around, I found a program called Umbrello. It works great. Unfortunately, I had all my UML classes, but no diagram! I had to drag and drop each one individually (I couldn't figure out how to drag them all at the same time T.T). I then manually added in all the lines and made it pretty. Not too bad, but it was partly automated and partly manual.

For solution (2), it worked great. I used a rake task to generate the svg files. I had several issues with this, which was the fact that RailRoad is used to diagram models in RoR, not the database. Hence, I ended up with a lot of relations that didn't exist in the database. This meant TONS of lines. The lines are also quite hard to follow as they constantly overlap and cross each other.

2 major issues I had was that there is a 1-to-1 relationship (for efficiency purposes). However, the diagram did not reflect this. For example, we have class A and B that are in a 1-to-1 relationship. A will have has_many relationships and so will B. However, the diagram showed that all of B's has_many relationships were related to A, not B. I couldn't figure out why.

The other issue was a logged bug. If the model ends in an 's', the diagram gets messed up. The diagram will automatically drop the trailing 's'. This created incorrect relationships.

For these 2 major issues, I was unable to use solution (2) in the end. However, if more improvements are made, I may give it another chance as the whole process was completely automated. If your interested, it also does Controller diagrams.

If anyone else has a better way of producing automated diagrams of their database or models, give me a shout. I'd love to hear other solutions.


W

Geokit: Oddities

1. When using Geokit + Google, sometimes, giving a valid postal code will fail. It's a very random thing. I'm not sure if it's just Google, but sometimes, it fails, even with a valid postal code. I'm guessing it has something to do with the network or perhaps they limit how many you can ask for in a period of time so you don't overload their server.

2. If you add :within to your find, Geokit doesn't recognize that :within => nil means not to add :within. Sometimes, you have a variable that may or may not be nil, and this controls whether you want to search using a distance. The :origin key works fine with nil, but :within does not. It blindly adds the distance calculations to the find. I had to hack acts_as_mappable.rb a little bit in order to get it to work.

In the apply_distance_scope(options) function, I have:

distance_condition = "#{distance_column_name} <= #{options[:within]}" if options.has_key?(:within) && !options[:within].nil?
distance_condition = "#{distance_column_name} > #{options[:beyond]}" if options.has_key?(:beyond) && !options[:beyond].nil?

and

[:within, :beyond, :range].each { |option| options.delete(option) }

This will take the :within => nil into account and skip the adding of the distance calculations out of the find and remove the useless keys.

3. In the Geokit README, there is a section about using :includes. However, there isn't a section on using :joins. Apparently, when you use :joins, things can screw up. For instance, if I have

Shop.find(:joins => "INNER JOIN products where products.shop_id = shops.id", :conditions => ..., :origin => ..., :within => ...)

You would expect a Shop object to be returned. This is true, but I found a small error. The Product id replaced the Shop id, leaving me with a Shop object with an id of the Product id.

I traced this down to the function add_distance_to_select(...). In this function, it blindly sets the :select option in the find to "*" if it isn't already defined. I'm guessing, because both Shop and Product have ids, they get mixed up somehow.

In order to fix this, I needed to add :select => "shops.*".

Hopefully, this info helps those that are having the same problems. If you have a better solution, feel free to share!


W

Eager Loading + Order By

So I was trying to cut down on the number of queries that I was making on a particular page and ran into a slight problem.

Basically, I had a relationship where a User has many Notifications:

class User
has_many :notifications, :order => 'created_at DESC'
end

In my controller, I wanted to cut down on the queries, so I did some eager loading:

@user = User.find(user_id, :include => :notifications)

However, when I went to go view my page, the notifications were not in the order as I specified. I went to go take a look at the query, and lo-and-behold, there was no ORDER BY stated in the query.

After some thought, it became apparent why no ORDER BY was included. This makes sense because if you eager load many tables, each with its own ORDER BY, it would be difficult, if not impossible, to have everything returned properly.

I would assume, without much thought, that one would assume that this would work. With further thought though, this becomes the correct thing to do.

Just a heads up ;)


W

Postgres + Rails + Ubuntu Troubleshooting

Problem 1 - forgotten password for postgres

Solution - TOUGH! you gotta re-install, that's what happened to me

Problem 2 - sudo apt-get remove postgresql did not remove any of the configuration files

Solution - use apt-get purge postgresql instead, this applies to all other packages

Problem 3 - sudo apt-get purge postgresql did not remove all the associated files

Solution - use sudo apt-get purge postgresql* instead or you can run apt-get autoclean after

Problem 4 - postgres refuses connection, but I swear I entered the password correctly!!!

Solution - postgres is very protective, the account you are logging in must match your current linux account .

Problem 5 - But what if I want postgres to manage its own account?

Solution - edit /etc/postgresql/8.1/main/pg_hba.conf, look near the bottom of the file, modify "Unix domain Socket" to local all all md5, "IPv4 local connections" to host all all 127.0.0.1/32 md5

and don't forget to restart the server by doing /etc/init.d/postgres-8.2 restart

Problem 6 - I installed postgres-pr but rails can't connect to postgres and I tried installing the
regular postgres gem but it failed!

Solution - in order to install the postgres gem, make sure you have the libpq-dev gem installed first. (gem install libpq-dev). Try installing the postgres gem again, it should work!

Problem 7 - I want postgres to run on some other port, what can I do?

Solution - first edit the /etc/postgresql/8.2/main/postgresql.conf, change the port number there first (don't forget to restart the server) then you have to goto your rails directly and edit config/database.yml, add ":port [portnumber]" to all your environments.


J

Mcolumn reference "id" is ambiguous

Today I tried to improve the performance of my action by doing just one db call through eager loading. For those who have no idea what it is, please read our previous post

http://twoblinddevs.blogspot.com/search?q=eager+loading

This is what I wanted to do

Person.find(:first, :include=>[ :person_info, :messages], :conditions=>["id = :user_id", { :user_id => session[:id]}] )

when I refreshed the browser, it shows me this error message - Mcolumn reference "id" is ambiguous.

The reason rails complain is because all three tables have an id column. In order to work around this problem, you simply have to change id = :user_id to people.id = :user_id


J

attachment_fu - configurations

In our model, we'll use the has_attachment command to hook into attachment_fu.

example:

has_attachment :content_type => :image,
:storage => :file_system,
:max_size => 500.kilobytes,
:resize_to => '320x200>',
:thumbnails => { :thumb => '100x100>' }
:processor => 'Rmagick'

validates_as_attachment

Let's look at each option in detail

for storage, you have three options, :db_system (default), :file_system, or :S3. If you are using :db_system, attachment_fu will convert and save the image to your db as a BLOB (binary large object). If :file_system is used, the file will be saved to your hard disk. If :S3 is used, image will be saved to Amazon's S3 server.

:max_size lets you specific the maximum size of the photo, and there's also a :min_size which works the other way

:resize_to - resize your image to an acceptable width and height like facebook :) It takes a string called the Geometry String, which has the format:

< width > x < height > + - < x > + - < y > { % @ ! < > }

where any of the field can be omitted.

By default, width and height are the maximum value. If you enter something like '300x500' the image will expand or contract to fit the width and height value while maintaining the aspect ratio of the image.

If you want to enforce the image size to be exactly the size you specify, you can append an exclamation mark in the end like so: '300x500!'

You can also specify either the width '300' or the height 'x500' where the missing parameter will be chosen to maintain the aspect ratio

You can append % to specify percentage width and height ('110%' - increase, '90%' - decrease, '110%x90%' = increase width, decrease, height)

You can use @ to specify the maximum area in pixels of an image (I don't see how this is gonna be useful)

You can use < or > to change the dimensions of the image only if its width or height exceeds the geometry specification. < resizes the image only if both of its dimensions are less than the geometry specification. For example, if you specify '300x500>' and the image size is '250x250', the image size will not change. However if the image is '1000x1000', the it'll be resized to '300x300' and vice versa.

Finally x and y are offsets for width and height. + causes x and y to be measured from the left or top edges and - measures from the right or bottom edges. And they are always measured in pixels.

:thumbnails - a set of thumbnails to generate, specified by a has of filename suffixes and resizing options. You can omitted it if you don't want thumbnails. Generating multiple thumbnails will look something like this

:thumbnails => { :thumb_big => '500x500', :thumb_small => '100x100' }

:thumbnail_class - set what class to use for thumbnails (defaulted to whatever model you are in) However, you can generate a seperate model with seperate set of validations

:processor - 'ImageScience', 'Rmagick', or 'MiniMagick'. I like Rmagick cause it has the most features, but MiniMagick is less of a memory hog.

:path_prefix - Path to store the uploaded files, which defaults to public/your_table_name
If you are using S3 backend, it defaults to just your_table_name

validates_as_attachment does all the validations for you, so you have nothing to worry about :)


J

attachment_fu - Installation and Setup

attachment_fu plugin is the complete package in handling images for your website. It handles image upload, resize, generate thumbnails, storing image info to database and saving the file to physical disk drive

to install attachment_fu, we type

~$ .script/plugin install http://svn.techno-weenie.net/projects/plugins/attachment_fu/

if you don't have imagemagick and rmagick installed

~$ sudo apt-get install imagemagick
~$ dpkg -l | grep magick
~$ sudo apt-get install libmagick9-dev
~$ sudo gem install rmagick -v=1.15.12

window users: you can go download the imagemagick executable and install rmagick by doing gem install rmagick -v=1.15.12

now let's generate a model to test it out

~$ .script/generate model photo

open up the migration file and enter the following

class CreatePhotos < ActiveRecord::Migration
def self.up create_table :photos do |t|
t.column :user_id, :integer
t.column :parent_id, :integer
t.column :content_type, :string
t.column :filename, :string
t.column :thumbnail, :string
t.column :size, :integer
t.column :width, :integer
t.column :height, :integer
end
end

def self.down
drop_table :photos end
end

:user_id - I'm assuming these photos belong to someone, you can replace it with another model_id depending on the relationship

:parent_id - don't touch, it's reserved for attachment_fu

:content_type - specify the content type of your data, default is image, but it can also be audio, or video the rest are pretty self explanatory, and we'll revisit these fields in a moment. now save your file, and do a quick rake db:migrate


J

the importance of .to_i

many times we'll use data fetched from a database in condition statements. And alot of times (at least I do) we'll assume data returned are numeric types of some kind because that's how it's specified in the database schema.

Big mistake!! .to_i should always be attached to numeric variables when used if you don't want little bugs creep out of nowhere.

One of my function acted all weird because I was comparing "1" with 1 :)

Another common place to make mistake is retrieving params variables, they will always return string values and it's up to you to convert them to your desired types.


J

DRYing Up YAML Fixtures (except for PostgreSQL!!!)

When creating YAML fixtures, there are many records that have many repeated values. For example:

user_1:
name: john
is_active: true

user_2:
name: billy
is_active: true

Here, the is_active field is repeated many times and has the same value true. This may be a default for many records. YAML allows you to set defaults:

defaults: &defaults
is_active: true

user_1:
name: john
<<: *defaults

user_2:
name: billy
<<: *defaults

Obviously, this simple example doesn't save us any time. However, if our defaults include many columns, this can save a lot of typing and headaches when default values need to be changed.

All this is wondeful... EXCEPT when your using PostgreSQL, and it's driving me NUTS!!! For some reason, this DRY method doesn't work. I haven't figured out why yet and my search for the answer has been rather futile. If any one has an answer, I'd like to know the reason.


W

Locking

Usually in an application with more than 1 user, there are several resources that will be shared. And whenever there are shared resources, there are usually race conditions. Most web applications are not exceptions. Shared resources are a very old set of problems that have been solved many times in many different areas, not only for computers.

Luckily, in RoR, it provides a way to handle shared resources: locking. Locking comes in 2 flavours: optimistic and pessimistic.

Usually, optimistic locking would be used for critical sections where race conditions are not expected to happen very often (due to the abortion of the transaction if a conflict occurs), whereas pessimistic locking would be used for critical sections where race conditions are frequent.

There is, however, a huge advantage that optimistic locking has over pessimistic locking, and that is scalability. The fact that pessimistic locking for update locks the row so that no read/update/destroy can be performed on that row until it is released means that users may have to wait indefinitely. Especially when race conditions are frequent, a long queue could develop with arbitrarily long waits.

Unfortunately, with optimistic locking, there is the problem of abortion and requiring the user to redo what they just did. This is obviously not very user-friendly, as the user expects their changes to be made the first time. If the UI is thought out carefully though, the impact of this may be minimal.

RoR, being superly fantastic, supports both! And both are very simple to implement.

With optimistic locking, all you have to do is add a column to your table you want to optimistically lock. Using migrations, it would look like this:

t.column :lock_version, :integer, :null => false, :default => 0

It is important that the column name is lock_version, and the default value is 0. Now, whenever an update occurs, the lock_version is compared to the one in the database. If there are any differences, then a StaleObjectError is raised and must be handled in any fashion in which you deem appropriate. More information about RoR's optimistic locking can be found here.

With pessimistic locking, all you do is lock it down when you do a find:

Person.find(:all, :lock => true)

This will pessimistically lock any rows returned. If you already have an object, you can use the method object#lock!:

person = Person.find(1)
person.lock!

More information about RoR's pessimistic locking can be found here.

Well, hopefully that was interesting to you. One more tip about locking is to watch out for deadlocks! Good luck!


W

Getting the Year from a Date Column

So I was running our unit tests that we had created after moving to PostgreSQL, and I found a database specific error. I am so thankful for our unit tests now. Instead of testing the application by hand, all I had to do was click the mouse button. I can't stress enough how important tests are.

Anyways, what I had in my code was a specific call to a MySQL function called YEAR(). This apparently was not supported by PostgreSQL. After doing some searching, I found another SQL function called EXTRACT. This seems to be supported in several databases including MySQL, PostgreSQL, and Oracle(9i, 10g, 11g).

For my purposes of getting the year, I used:

EXTRACT(YEAR FROM date_column)

More information about the EXTRACT function can be found here.

Hopefully this helps anyone having database portability problems!


W

PostgreSQL

RoR is amazing! I love how everything is so encapsulated! After doing another project where I switched from MySQL to PostgreSQL, I wanted to try out PostgreSQL with RoR. Usually, this means a lot of backend changes and porting SQL scripts.

However, with RoR, it requires very little changes. Firstly, your whole entire database structure is created using the migration scripts. Since the migration scripts are written in Ruby, nothing has to change about these scripts. Any database access from your application do not need to be changed since they are also in Ruby. Then you must be asking, "What needs to be changed?" Very simple, the database.yml needs to be changed. Actually, only 1 line of it. This is the configuration file that RoR uses to access the database. Just change:

adapter: mysql

to

adapter: postgresql

That's it! Really, that's it! Well, actually, no... you need to install the Ruby gem that handles PostgreSQL. Since I'm developing on a Windows machine, I just enter:

>gem install ruby-postgres

For more information, you can visit this page:

http://wiki.rubyonrails.org/rails/pages/PostgreSQL

And truly, that's it. If you don't believe me, give it a try! Changing your database couldn't be easier!


W

RadRails: Data Perspective

So I tried using this new perspective that comes with RadRails. It allows you to view your database in Eclipse and allows for SQL queries and executions.

However, I was running into some problems with my database.yml file. It kept giving me an error about invalid YAML syntax. However, RoR connects perfectly fine with it. I finally figured out that the Data Perspective doesn't like eRb.

I had previously DRYed up our database.yml by specifying our login information in one place, and then attaching it to the end of the hash for each database. However, the Data Perspective doesn't recognize this type of syntax and reports an error. After reverting our database.yml to its original form, the Data Perspective came to life.

Hope this helps anyone else running into the same problem.


W

Migration

Wow. I must say, I'm totally impressed with this feature in RoR. I'm so glad that this feature comes packaged with RoR, and that I don't have to do this manually. I remember writing SQL files to do this, and keeping them organized so I can run them later. It was a pain in the ass!

When I heard about this built-in feature, my draw dropped. Migration basically allows you to manipulate the database in any way. Each file has an 'up' and a 'down'. The 'up' makes changes to your database, whereas the 'down' rolls back your changes. Each file is also versioned, so you can quickly move your database to any previous version, or blow it away entirely and rebuild it. This is nice when you first start developing because there may be some kinks in the database design. However, when the database becomes stable and the application is ready to use, user data is stored, and this data should not be lost when adding upgrades or rollbacks. The migration files give a nice way to centralize and remember the changes and their rollbacks.

RoR has an online API documentation that is quite useful and can be found here:

http://api.rubyonrails.org/

Under ActiveRecord::Migration contains all of the possible things you can do with migration.

By running:

>rake db:migrate VERSION=0

RoR will rollback all changes in reverse version order. This will basically blow away your database. If your keen, you'll notice that VERSION is specified, and that the number refers to the version you want your database to be at. So if you're currently on version 10, you could have VERSION=9, which would rollback the changes when version 10 was made.

By running:

>rake db:migrate

RoR will apply all the versions after the current version of your database. Hence, if you have a fresh database, using this command will get you up to speed right away!

Migration... Check it out if you have time. I hope it'll blow you away as much as it did for me.

W