Script to interact with a database

[Quee WM]

I have an application in rails in which i am adding a new column in one
of the tables. The value for the new column is a bit.ly link and I need
to populate this for each record in the table approx. 250 rows will be
affected.

Q1: Can this be done outside the application? i.e. write a script that
can interact with the db and then populate the appropriate column? And
is this advisable?

or

Q2: if the aforementioned idea is not a good way then what will be the
best way to update the existing records as any code i will write will
only be used to update the table once.

 

[Aldric Giacomoni]

I have an application in rails in which i am adding a new column in one
of the tables. The value for the new column is a bit.ly link and I need
to populate this for each record in the table approx. 250 rows will be
affected.

Q1: Can this be done outside the application? i.e. write a script that
can interact with the db and then populate the appropriate column? And
is this advisable?

or

Q2: if the aforementioned idea is not a good way then what will be the
best way to update the existing records as any code i will write will
only be used to update the table once.

Sounds like you should be posting this in the Rails forum / newsgroup.
Also sounds like you should look into migrations.

 

[Quee WM]

Sounds like you should be posting this in the Rails forum / newsgroup.
Also sounds like you should look into migrations.

I shall try posting to the rails forum, but i would still very much like
to learn how a script can interact with a database using active record.
and any pointers in that directions will be greatly helpful.

 

[Marnen Laibow-Koser]

I shall try posting to the rails forum, but i would still very much like
to learn how a script can interact with a database using active record.
and any pointers in that directions will be greatly helpful.

That can also be better answered in the Rails forum, I think.

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
(e-mail address removed)

 

[Aldric Giacomoni]

I shall try posting to the rails forum, but i would still very much like
to learn how a script can interact with a database using active record.
and any pointers in that directions will be greatly helpful.

Migrations, as I mentioned, are excellent for one-time work on tables,
including addition of columns and data.

 

[Jim Maher]

I have an application in rails in which i am adding a new column in one
of the tables. The value for the new column is a bit.ly link and I need
to populate this for each record in the table approx. 250 rows will be
affected.

Q1: Can this be done outside the application? i.e. write a script that
can interact with the db and then populate the appropriate column? And
is this advisable?

or

Q2: if the aforementioned idea is not a good way then what will be the
best way to update the existing records as any code i will write will
only be used to update the table once.

Can you update the database outside of your Rails application? Yes.

Should you? Many would say NO, but in practice I have never yet
encountered an app that was ALWAYS updated only with the app. The DB
itself, various utilities, one-time scripts, external apps (e.g., Excel,
etc.) are often used. Be careful, have a backup, be careful again,
test, then be VERY careful.

Using a plain Ruby script to interact with the datavbase is certainly
possible, and not necessarily using ActiveRecord. You might consider
using something like Sequel (v3.8.0), which is a database toolkit for
Ruby (I have NOT used this myself).

But, again, be VERY careful. Any updates you do outside your app may
NOT conform to your business rules. That's up to you.

j

 

[Florian Gilcher]

I have an application in rails in which i am adding a new column in = one
of the tables. The value for the new column is a bit.ly link and I = need
to populate this for each record in the table approx. 250 rows will be
affected.
=20
Q1: Can this be done outside the application? i.e. write a script that
can interact with the db and then populate the appropriate column? And
is this advisable?
=20
or
=20
Q2: if the aforementioned idea is not a good way then what will be the
best way to update the existing records as any code i will write will
only be used to update the table once.

Sure. If you use the "script/runner"-Script instead of the plain Ruby =
interpreter, your script will run in the fully loaded application =
environment. For one-shot scripts that are not exactly migrations, this =
is an acceptable way to do this. So:

script/runner -e development path/to/my/script.rb

Regards,
Florian=

 

[Marnen Laibow-Koser]

Can you update the database outside of your Rails application? Yes.

Should you? Many would say NO, but in practice I have never yet
encountered an app that was ALWAYS updated only with the app.

You should see my apps, then. Unless the DB is corrupted (which has
only happened once), I usually use script/console for data tweaking.

The DB
itself, various utilities, one-time scripts, external apps (e.g., Excel,
etc.) are often used. Be careful, have a backup, be careful again,
test, then be VERY careful.

Yes. And this is one reason that I always recommend having data
integrity constraints in the DB, not just the app.

Using a plain Ruby script to interact with the datavbase is certainly
possible, and not necessarily using ActiveRecord. You might consider
using something like Sequel (v3.8.0), which is a database toolkit for
Ruby (I have NOT used this myself).

I have. But in a Rails app such as the OP has, it is safer not to do
this.

But, again, be VERY careful. Any updates you do outside your app may
NOT conform to your business rules. That's up to you.

j

Best,
-- 
Marnen Laibow-Koser
http://www.marnen.org
(e-mail address removed)

 

[Josh Cheek]

[Note: parts of this message were removed to make it a legal post.]

I have an application in rails in which i am adding a new column in one
of the tables. The value for the new column is a bit.ly link and I need
to populate this for each record in the table approx. 250 rows will be
affected.

Q1: Can this be done outside the application? i.e. write a script that
can interact with the db and then populate the appropriate column? And
is this advisable?

Q2: if the aforementioned idea is not a good way then what will be the
best way to update the existing records as any code i will write will
only be used to update the table once.

I'd make a method in your model that gets the address, then have it get that
address upon creating a record. For all the records currently in there, just
go to the console, iterate through each, and invoke that method on them.

To make the method that updates it, you can use the api, or a gem:
http://code.google.com/p/bitly-api/wiki/ApiDocumentation
http://gemcutter.org/search?query=bit.ly

For a 1 time edit with 250 records, I'd use the console script/console , for
recurring edits, I'd create a rake task
http://railscasts.com/episodes/66-custom-rake-tasks

If you have a large number of records, you might need to do something
different, but I'm not sure how to handle that.

Sometimes, due to constraints within your app's model, you need to edit the
db directly, I use sequel pro http://www.sequelpro.com but this really
almost never occurs (or at least shouldn't).

 

[Quee WM]

Thanks a lot for the help. seems the Rails group wins as they made a
strong point about using the migration to take care of this task. Will
play with code there and see what happens.

Once again thanks.

Q