From: ruby-talk@...
Date: 2002-04-11T03:31:41+09:00
Subject: Re: Advice for best approach?

dmcnulty@mindspring.com (Dan McNulty) writes:

> I am writing a relatively simple app that include Ruby::DBI access to
> MySQL.  Currently, I have a file (autocert.sql) that contains all my
> SQL statements like this:
> 
> insert_user_sql     = "INSERT INTO users 
>                     (ntid, fname, lname, email, ou) 
>                     VALUES
>                     (\"#{somecert.ntid}\", \"#{somecert.fname}\", 
>                      \"#{somecert.lname}\", 
>                      \"#{somecert.email}\", \"#{somecert.ou}\");"
> 
> Then I have a Ruby program contained in a file (*.rb) and a file that
> contains all my functions (*.rbh).  The .rb file "load"s the .rbh file
> and I just call functions out of there, that works fine.  Here's an
> example of a function that loads a new user into MySQL (it users the
> SQL statement from above):
> 
> def addusertodb(somecert)
>     load "autocert.sql"
>     tmp = doqry(insert_user_sql)
>     return tmp
> end
> 
> "somecert" is an object with the appropriate members required by the
> SQL statement.
> 
> When I load the file containing the SQL statements, Ruby complains
> about an undefined local variable "somecert", which I kinda expected. 
> Obviously I have scope issue.
> 
> My question is:  how should I organize this program so I can:
> a) keep my sql statements in a single, separate file
> b) use my sql statements and substitute values into them
> c) not write an explicit function that passes the values into the
> function scope and passes the completed SQL statement back (too much
> work, but I know this will work)

If I were you, I'd group SQL statements by their purpose in their own
function. This is because each SQL statement has the potential to
return many different things, and also because sometimes you need more
than one SQL statement to accomplish some purposes.

For example, if you want to insert authorisation permission for a
given user, but you want to make sure that the referenced user has
existed, then you'd simply say:

def insertAuth(userid, can_write, can_read)
  sql_search_user = "SELECT * FROM USER WHERE userid=#{userid}"
  doqry(search_user_sql) { |x|
       #if userid not found, return
  }
  sql_insert_auth = "INSERT ......"
  if doqry(sql_insert_auth) != 1 #not exactly one row was affected
     raise "Insertion failed"
  end
end

Thus, if you later want to expand the capabilities of insertAuth (such
as returning the generated primary key (mysql_last_insert)), you can do
that easily and without breaking lots of existing code.

Then I'd define many other functions, each for a specific purpose, and
store all of them into a file, say, sql_access.rb. Doing this, you
won't polute your general code with dbi-specific code. Because who
knows, tomorrow there is a better dbi that offers 200% speed
improvement. You'd want to be able to locate all previous dbi code and
replace them with new one.

YS.