Handling forms on database driven websites

Ever since I learned Perl, Ruby and MySQL, I’ve built several database
driven websites. Again and again, I have coded HTML forms, and Perl or
Ruby code to process the data submitted from those HTML forms and
insert them into the MySQL database.

I’ve always felt that the process is overly tedious, and looked for
ways to improve it. For example, suppose I have a table in my database
that contains a person’s name, e-mail address, and phone number. My
first attempt at writing a script to process the form would look like
this:

name = $cgi[‘name’][0].to_s
email = $cgi[‘email’][0].to_s
phone = $cgi[‘phone’][0].to_s
if name.length == 0
puts "ERROR: Name cannot be blank. Please press BACK."
exit
end
unless email =~ /^\w+@\w+.\w+$/
puts "ERROR: E-mail appears to be invalid. Please press BACK."
exit
end
unless phone =~ /^\d\d\d-\d\d\d-\d\d\d\d$/
puts "ERROR: Phone must be in ###-###-#### format. Please press BACK."
exit
end

Everything checks out ok

mysql.query(“INSERT INTO persons SET name=’#{Mysql.quote(name)}’, email=’#{Mysql.quote(email)}’, phone=’#{Mysql.quote(phone)}’”)
puts “Submission successful!”

After gaining more experience, my current approach is to have an
object-oriented Form object that performs HTML generation of a form
and validation of the submitted form for me, as well as actually
redisplaying the form with the previous values filled in, along with
error messages on the offending fields rather than just asking the
user to press BACK:

class PersonForm < Form
@@fields = {
# key => [label, type, extra arguments]
‘name’ => [‘Name’, Forms::Text, {‘maxlength’ => 20}],
‘email’ => [‘E-mail Address’, Forms::Email],
‘phone’ => [‘Phone Number’, Forms::Phone]
}
end
form = PersonForm.new
form.values = $cgi.params
form.validate
if form.valid?
Db.insert_hash(‘persons’, form.values)
Response.Redirect(‘person.rhtml’, {‘id’ => Db.insert_id()})
else
puts "

There were errors in your form. Please fix them.

"
form.display
end

which looks considerably nicer, but still feels too tedious when I’m
building a website that has many of these forms to make. I also find
myself having to duplicate my data definitions; Ruby sees this:

    'name' => ['Name', Forms::Text, {'maxlength' => 20}],
    'email' => ['E-mail Address', Forms::Email],
    'phone' => ['Phone Number', Forms::Phone]

And the MySQL sees another data definition for the same data:

    name        VARCHAR(20)
    email       VARCHAR(80)
    phone       DECIMAL(10, 0)

So, I am still in search of the perfect, minimal-effort way to
generate HTML for, and process the input of forms on a database-driven
website.

Does anyone have any pointers to offer?

P.S. I’ve heard people here say that PostgreSQL is better than MySQL.
How would it help in my situation?

Philip Mak wrote:

which looks considerably nicer, but still feels too tedious when I’m
building a website that has many of these forms to make. I also find
myself having to duplicate my data definitions; Ruby sees this:

    'name' => ['Name', Forms::Text, {'maxlength' => 20}],
    'email' => ['E-mail Address', Forms::Email],
    'phone' => ['Phone Number', Forms::Phone]

And the MySQL sees another data definition for the same data:

    name        VARCHAR(20)
    email       VARCHAR(80)
    phone       DECIMAL(10, 0)

So, I am still in search of the perfect, minimal-effort way to
generate HTML for, and process the input of forms on a database-driven
website.

Does anyone have any pointers to offer?

P.S. I’ve heard people here say that PostgreSQL is better than MySQL.
How would it help in my situation?

Yes it is. It has long has many of the features that MySQL is only
beginning to take place (i.e. FK, ACID, etc.). But I don’t think it’ll
help in your case as yours is not a DB-related problem. It is a
paradigm-shear between your language (Ruby) and your medium (MySQL).

This is as Ruby is a pure OO while MySQL or any RDBMS is not. The ideal
case is to find a OODBMS and thus data will have a one-to-one mapping
between Ruby and the OODBMS.

Having said that I do not know of any viable Opensourced OODBMS around
(I’m very happy if someone could confirm otherwise). Therefore, you
could do a second-best solution is to have another level of indirection,
a meta-data-definition. I have done this before using XML, below is an
oversimplified sample:

varchar 20

You could then write a Ruby layer to decode this and generate the Ruby
class (which would be then be eval’ed dynamically later), and the
MySQL/PostgreSQL SQL script.

You could then write a DTD or XMLschema to manage the integrity of your
XML-based Metadata Definition.

After which you could generate a PageTemplate (it’s great! I’m using it
with fantastic results) form template, which could then be instantiated
dynamically depending on what type of forms. This is an excellent
solution if you have lots of similar forms, differing just is a couple
of attributes or fields.

···


Wai-Sun “Squidster” Chia
Consulting & Integration
Linux/Unix/Web Developer Dude

Philip Mak pmak@animeglobe.com wrote in message news:20020812074049.GH5036@trapezoid.interserver.net

So, I am still in search of the perfect, minimal-effort way to
generate HTML for, and process the input of forms on a database-driven
website.

Does anyone have any pointers to offer?

There always seems to be this amount of pain. But here’s an idea:

class Form
def insert
unless (table_exists?)
create_table
end

    sql_insert_data         
end

end

Sure you’re checking for the table each time, but this could make
things easier for you.

~ Patrick

[snip]

I’ve only done this once or twice, but I’ve felt
the same thing – that I was violating the DRY
principle over and over.

What I’ve thought of is a little code generator
that would take a simple data description and
generate code from it.

Does that approach interest you? Would you want
to collaborate on it?

Hal

···

----- Original Message -----
From: “Philip Mak” pmak@animeglobe.com
To: “ruby-talk ML” ruby-talk@ruby-lang.org
Sent: Monday, August 12, 2002 2:40 AM
Subject: Handling forms on database driven websites

Ever since I learned Perl, Ruby and MySQL, I’ve built several database
driven websites. Again and again, I have coded HTML forms, and Perl or
Ruby code to process the data submitted from those HTML forms and
insert them into the MySQL database.

I’ve always felt that the process is overly tedious, and looked for
ways to improve it.

Wai-Sun Chia waisun.chia@hp.com wrote in message news:3D576CCA.7040003@hp.com

Having said that I do not know of any viable Opensourced OODBMS around
(I’m very happy if someone could confirm otherwise).

The only solid one I know of is GOODS
(http://www.garret.ru/~knizhnik/goods.html). I think Jason Voegele
was working on a Ruby client for it at one point, but I haven’t heard
anything from him for a while. I have a Smalltalk client that
shouldn’t be too hard to port, either - at the least, it should act as
a more precise specification than the protocol’s documentation does…
;-).

“Hal E. Fulton” hal9000@hypermetrics.com writes:

What I’ve thought of is a little code generator
that would take a simple data description and
generate code from it.

Does that approach interest you? Would you want
to collaborate on it?

I’ll be giving a short talk at RubyConf on just that. For my current
web project I have a set of classes that let me specify my tables like
this:

 class ChallengeDescTable < Table
   table "challenge_desc" do
     field autoinc,       :chd_id,           pk
     field int,           :chd_season,       references(SeasonTable)
     field boolean,       :chd_primary_only
     field int,           :chd_levels        # bitmask
     field varchar(100),  :chd_name
     field varchar(10000),:chd_desc
     field varchar(200),  :chd_file_path
     field varchar(200),  :chd_icon_url
   end
 end

 # The challenges for a season

 class ChallengeTable < Table
   table "challenge" do
     field autoinc,       :cha_id,           pk
     field int,           :cha_aff_id,       references(AffiliateTable)
     field int,           :cha_chd_id,       references(ChallengeDescTable)
     field int,           :cha_levels        # bitmask
   end
 end

 # and a convenient view
 class ChallengeViewTable < Table
   view "challenge_view",
     [ChallengeTable, ChallengeDescTable],
     "cha_chd_id = chd_id"
 end

From this, the code generates

  1. The SQL DDL to create and drop the schema

  2. A DOT diagram of the schema

  3. A set of accessor classes that work alongside a simple persitence
    layer. Using these, I can do things like

    challenge = store.select_one(ChallengeTable, “cha_id=?”, id)
    puts challenge.cha_levels

The basic accessor objects are never used stand-alone: instead they
are wrapped in business objects that give them meaningful
behavior. Thus, the Challenge business object contains code such as

 class Challenge < BusinessObject

   def Challenge.for_affiliate(aff_id)
     $store.select(ChallengeTable, "cha_aff_id=?", aff_id).map do |c|
       new(c)
     end
   end

   def Challenge.delete(cha_id)
     $store.delete_where(ChallengeTable, "cha_id=?", cha_id)
   end


   def Challenge.with_id(cha_id)
     maybe_return($store.select_one(ChallengeTable, "cha_id=?", cha_id))
   end

etc…

The business objects also provide behavior to let them play with web
forms. I use the RDoc template code (with extensions to handle form
controls tidily), so the method that fetches a database about and uses
it to populate a form would look like:

def fetch_and_display(id)
@session.cha = Challenge.with_id(id)
display_common
end

def display_common
form_data = { ‘action_url’ = url(:handle_form) }
@session.cha.add_to_hash(form_data)
standard_page(“Edit Challenge”, form_data, EDIT_CHALLENGE)
end

The code that then reads back the results looks like this:

def handle_form
form_data = hash_form_cgi
@session.cha.from_hash(form_data)
errors = @cha.error_list
if errors.empty?
@session.cha.save
main_menu
else
error_list(errors)
display_common
end
end

This approach also lets business objects handle nested objects any way
they want: it’s pleasantly transparent to the code that uses them.

Cheers

Dave

I’ll be giving a short talk at RubyConf on just that.

Looks cool.

When will v 1.0 of PragDave’s OODB Layer be released?

Chris

That open source database (previously proprietary) claims to support
object-orientation. I’ve been meaning to check it out to see what it
does in actuality, but I haven’t had time to.

···

On Tue, Aug 13, 2002 at 03:08:26AM +0900, Avi Bryant wrote:

Wai-Sun Chia waisun.chia@hp.com wrote in message news:3D576CCA.7040003@hp.com

Having said that I do not know of any viable Opensourced OODBMS around
(I’m very happy if someone could confirm otherwise).

“Chris Morris” chrismo@clabs.org writes:

Looks cool.

When will v 1.0 of PragDave’s OODB Layer be released?

It’s not really an OODB: it’s just a veneer on top of a relational
model. Not idea when it’ll be released: I’m snowed under using it
just now. :slight_smile:

Dave

“Philip Mak” pmak@animeglobe.com wrote in message
news:20020812212736.GI5036@trapezoid.interserver.net

Wai-Sun Chia waisun.chia@hp.com wrote in message
news:3D576CCA.7040003@hp.com

Having said that I do not know of any viable Opensourced OODBMS around
(I’m very happy if someone could confirm otherwise).

http://www.sapdb.org/sap_db_FAQ.htm

That open source database (previously proprietary) claims to support
object-orientation. I’ve been meaning to check it out to see what it
does in actuality, but I haven’t had time to.

Doesn’t seem to be much native OODB. Core interface is ODBC, JDBC. Some OLAP
build on top of RDBMS. Saw a brief OODB mention, but can’t find where there
is supposed to be an OO interface, but SAP the product may have an OO layer.

What’s wrong with GOODS anyway? Seem’s the GUI has done his homework,
although I haven’t tested it. Architecture and tradeoff’s seems ok. There
are several databases depending on you preferences.

Mikkel

GigaBase?
large on-disk, fast transactions
http://www.garret.ru/~knizhnik/gigabase.html

FastBase?
in-memory
http://www.garret.ru/~knizhnik/fastdb.html

GOODS
distributed
http://www.garret.ru/~knizhnik/goods.html

Many other DB links from author of FastBase? and GigaBase?
http://www.garret.ru/~knizhnik/

···

On Tue, Aug 13, 2002 at 03:08:26AM +0900, Avi Bryant wrote: