Tag Archives: Databases

‘belongs_to’ and ‘has_one’ Differentiated

Posted on by
1

One of the more common confusions with ActiveRecord associations is the difference between the `has_one` and `belongs_to` relationships.

However, they are not interchangeable, and there are some serious differences. A `belongs_to` can only go in the class that holds the foreign key whereas `has_one` means that there is a foreign key in another table that references this class. So `has_one` can only go in a class that is referenced by a column in another table.

So this is wrong:

class Transaction < ActiveRecord::Base
  # The transactions table has a order_id  
  has_one :order                
end

class Order < ActiveRecord::Base
  # The orders table has a transaction_id
  has_one :transaction          
end

So is this:

class Transaction < ActiveRecord::Base
  # The transactions table has a order_id
  belongs_to :order             
end

class Order < ActiveRecord::Base
  # The orders table has a transaction_id
  belongs_to :transaction     
end

For a two-way association, you need one of each, and they have to go in the right class. Even for a one-way association, it matters which one you use, and which direction you use it:

class Transaction < ActiveRecord::Base
  # This table has no foreign keys
  has_one :order             
end

class Order < ActiveRecord::Base
  # The orders table has a transaction_id
  belongs_to :transaction     
end

Convert SQL Server Database to a SQLite Database

Posted on by
Reply

Recently, I wanted to resurrect an old project of mine I worked on in my spare time originally designed to work on .Net technologies. Naturally, I backed it against a SQL Server 2005 Database which turned out to be a bad idea because it made portability of that data a bit of a nightmare.

So it was with great happiness that I found this tool (mirrored here) by liron.levi who posted an article on CodeProject on how to accomplish this task. You still need a Windows machine and an install of SQL Server (Express Edition with Advanced Tools is alright) but it got the job done just fine for me.

If you want a no-fuss easy way to convert your databases into a format easier to deal with and a lot more portable to-boot.

Howto Backup PostgreSQL Databases Server With pg_dump command

Posted on by
Reply

Recently I had do to a lots of PostgreSQL database administration as I needed to move several databases onto  a production server.  PostgreSQL is one of the most robust, open source database servers available, and for my money, faster and generally better than MySQL. Like MySQL database server, it provides utilities for creating a backup.

Backup database using pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time.

$ pg_dump table | gzip -c > table.dump.tar.gz

Another option is use to pg_dumpall command. As a name suggest it dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:

$ pg_dumpall | gzip -c > all.dump.tar.gz


Moving the Default PostgreSQL Data Directory (Windows)

Posted on by
3

PostgreSQL for Windows installs the PGDATA directory by default into “C:\<Program Files>\PostgreSQL\some version\data”.  While is usually ok for development and even some production environments, it can be somewhat limited given the typically large amount of disk activity on (what is normally) the system volume.

This step-by-step post aims to explain the fastest way to change the default PGDATA directory to another location.

Step 1:

Close all application that are currently connected to your database, then go to Windows Services Management and stop the PostgreSQL service.  Naturally, the fastest way to get to Windows Services Management is by right clicking on my computer -> manage, and then click on the services node in the computer management tree:

Once the Postgres service is stopped, leave computer the Computer Management Console open (we’ll need it again in a second).

Step 2:

Copy the “C:\<Program Files>\PostgreSQL\some version\data” directory to the new location.  In my own attempts, moves do not seem to work, but the redundant disk usage should be pretty minimal and at least you will have an old backup in the default location if something goes wrong.

Step 3 (Very Important):

Rick click on the new “data” directory, and click properties, and select the security tab.  Give the local Postgres user (or for advanced users the user account Postgres uses) and give full permission on that direct and all child objects (sometimes you need to reset the child permission inheritance).  Click ok on the properties window.

Step 4:

Open regedit.exe by clicking on the start button and typing “rededit” in either the search box, or start->run box (depending on whether your using Vista or XP) and navigate to the “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-some version” branch. Double click on “ImagePath” and change the directory after the “–D” option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.

Step 5:

Close regedit and go back to Computer Management and restart the Postgres service. Close Computer management.

After  completeing these steps, and assuming something hasn’t gone wrong, you should now have Postgres running from the new location.

(Reference: http://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows)