2.3. Tutorial 3: create_foodsinc.g

This example creates a database, enters and deletes date, and then emits a unique query to the database and maps the results to instances of a temporary class.

#!/usr/cogent/bin/gamma
/*
 * Create a new table in a mysql database
 */

require ("MySQLSupport");

host := "localhost";
user := "test";
password := "";
database := "test_foodsinc";

/* Derive a class from MYSQL just to prove that we can
   do it.  There is no real need in this case, but if we
   want to specialize the connection somehow, we can. */

class Demo MYSQL
{
}

method Demo.make_row (klass, name, address, phone)
{
  local row = new klass();
  
  row.name = name;
  row.address = address;
  row.phone = phone;
  .insert (row);
  row;
}

function main ()
{
  local		mysql = new Demo();
  local		cust, sup, mistakes, common;
  
  if (!mysql.connect (host, user, password))
    error (string ("Could not connect to host: ", host,
		   " as user: ", user));
  
  if (mysql.select_db (database) == -1)
    mysql.query(string("CREATE DATABASE ", database));  

  if (mysql.select_db (database) == -1)
    error (string ("Could not select or create database: ", database));
  
  /* Drop the test tables.  Ignore errors. */
  try mysql.drop_table ("customers"); catch;
  try mysql.drop_table ("suppliers"); catch;
  
  /* Create new customers and suppliers tables. */
  mysql.create_table ("customers");
  mysql.add_column ("customers", "name", "TEXT", nil, nil);
  mysql.add_column ("customers", "address", "TEXT", nil, t);
  mysql.add_column ("customers", "phone", "TEXT", nil, t);
  
  mysql.create_table ("suppliers");
  mysql.add_column ("suppliers", "name", "TEXT", nil, nil);
  mysql.add_column ("suppliers", "address", "TEXT", nil, t);
  mysql.add_column ("suppliers", "phone", "TEXT", nil, t);
  
  /* Create Gamma classes for Customer and Supplier, using the
     MySQL tables "customers" and "suppliers" as templates for
     the classes. */
  mysql.class_from_table (#Customer, nil, "customers");
  mysql.class_from_table (#Supplier, nil, "suppliers");
  
  /* Create several customers.  Hang onto one of them for later
     manipulation. */
  cust = mysql.make_row (Customer, "Baker Bob's Pastries",
			 "New York", "111-555-1212");
  mysql.make_row (Customer, "Willy Wonka's Chocolate Factory",
		  "Somewhere in Kansas", "222-555-1212");
  mysql.make_row (Customer, "This is a mistake",
		  "Nowhere", "000-000-0000");
  
  /* Create several suppliers. */
  mysql.make_row (Supplier, "Sugar Hill \"North\" Farms",
		  "Kingston, Jamaica", "333-555-1212");
  sup = mysql.make_row (Supplier, "Mack and Molly Milk Farms",
			"Dublin, Ireland", "777-555-1212");
  mysql.make_row (Supplier, "Baker Bob's Pastries",
		  "New York", "111-555-1212");
  mysql.make_row (Supplier, "Oops, another mistake",
		  "Anywhere", "999-555-1212");
  
  /* Change the customer phone number for Baker Bob */
  cust.phone = "444-555-1212";
  mysql.update(cust);
  
  /* Change the city for Mack and Molly */
  sup.address = "Clonmacnoise, Ireland";
  mysql.update(sup);
  
  /* Remove all customers whose address is Nowhere */
  mistakes = mysql.query_to_class
    (Customer, "select * from customers where address = \"Nowhere\"");
  with cust in mistakes do
    {
      princ ("Deleting ", cust.name, "\n");
      mysql.delete (cust);
    }
  
  /* Remove all suppliers whose name includes the word "mistake" */
  mistakes = mysql.query_to_class
    (Supplier, "select * from suppliers where name like \"%mistake%\"");
  with sup in mistakes do
    {
      princ ("Deleting ", sup.name, "\n");
      mysql.delete (sup);
    }
  
  /* Find all customers who are also suppliers, using the name as
     the comparison field.  Print the results.  Yes, we could have
     retrieved both phone numbers in the original select, but that
     would not be as demonstrative.
     
     Note that column names are allowed to contain spaces and
     characters that are not valid Gamma identifier characters.  To
     work with these column names, you must escape the invalid
     characters within the instance variable identifier.  Look at
     "supplier id" for an example.  Life is easier if you avoid
     this.
     */
  
  common = mysql.query_to_temp_class
    (nil, "select c.id as cid, s.id as \"supplier id\", c.name, c.address,
           c.phone from customers c, suppliers s where c.name = s.name");

  with match in common do
    {
      princ (match.name, " is both a customer (id ", match.cid,
	     ") and a supplier (id ", match.supplier\ id, ")\n");
      cust = mysql.query_to_class
	(Customer, string ("select * from customers where id = ",
			   match.cid));
      sup = mysql.query_to_class
	(Supplier, string ("select * from suppliers where id = ",
			   match.supplier\ id));
      princ ("  Purchasing phone number is ", cust[0].phone, "\n");
      princ ("  Sales phone number is ", sup[0].phone, "\n");
    }
  
}

Output

Deleting This is a mistake
Deleting Oops, another mistake
Baker Bob's Pastries is both a customer (id 1) and a supplier (id 3)
  Purchasing phone number is 444-555-1212
  Sales phone number is 111-555-1212