Script to interact with a database

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.

Quee WM wrote:

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.

Aldric G. wrote:

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.

Quee WM wrote:

Aldric G. wrote:

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.

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

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Quee WM wrote:

Aldric G. wrote:

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.

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

Quee WM wrote:

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

Jim M. wrote:

Quee WM wrote:

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.

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
[email protected]

On Wed, Jan 6, 2010 at 9:51 AM, Quee WM [email protected] wrote:

best way to update the existing records as any code i will write will
only be used to update the table once.

Posted via http://www.ruby-forum.com/.

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).

On Jan 6, 2010, at 4:51 PM, Quee WM wrote:

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

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

Florian G. wrote:

On Jan 6, 2010, at 4:51 PM, Quee WM wrote:

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