Tuesday, June 21, 2005 1:22 AM bart

Adventures in Comega - part 5 bis (database access)

Introduction

In the first part of episode 5 of my series on "Adventures in Comega" I showed you the internal IL-kitchen of database access in Comega and how the Sql2Comega.exe tool is used to export the metadata of a database for usage in the Comega language. Now I'll show you the various constructs that are available in Comega to perform database operations using built-in keywords.

Selection

Okay, the first one I showed in the previous post already is the "select"-keyword to perform ... right, selection. The syntax should be well-known and consists of a "select ... from ..." skeleton with some optional clauses I'll cover further on. An example looks like this:

results = select * from DB.Products

You don't have to worry about the type of the results-variable, it's the job of the Comega compiler to generate the necessairy constructs to make it work, as we saw in the previous post (type infer). Now you can use the foreach keyword to iterate over the rows:

foreach (result in results)
{
     string pname = result.ProductName;
     double uprice = result.UnitPrice;
     //...
}

In fact, results is just a stream (see first episodes of this series for more information about the concept of "streams"). If you do want to specify the stream type explicitly, you can do:

struct { SqlString ProductName; SqlMoney UnitPrice; }* result = select ProductName, UnitPrice from DB.Products

But this is not needed as you can see in the previous code snippets. However, it can be useful when you're using aliases:

struct { SqlString Name; }* result = select ProductName as Name from DB.Products

You can go even further by declaring a field without a name, using parentheses. I won't cover this over here, there's a sample in the Comega documentation stuff. The iteration alias can be specified using the "in" keyword:

foreach (row in select p.ProductName from p in DB.Products) { ... }

Projection

Right, we can select fields in rows. Assume you want to map these fields to attributes in class instances? How to do that? A straightforward way is this one:

class Product
{
     private SqlString ProductName;
     private SqlMoney UnitPrice;

     public Product(SqlString ProductName, SqlMoney UnitPrice)
     {
          this.ProductName = ProductName;
          this.UnitPrice = UnitPrice;
     }

     //...
}

foreach (row in select ProductName, UnitPrice from DB.Products)
{
     Product p = new Product(row.ProductName, row.UnitPrice);
     //...
}

Well, this should work. However, using projection you can actually do this work automatically, by generating a steam of Product instances (Product*):

Product* products = select new Product(ProductName, UnitPrice) from DB.Products;

Although I didn't cover XML support in Comega to the full extent yet, I'll show you how you can use XML syntax in Comega too:

class Product
{
     struct
     {
          SqlString ProductName;
          SqlMoney UnitPrice;
     }
}

Product* products =
     select
         
<Product>
               <ProductName>{ProductName}</ProductName>
               <UnitPrice>{UnitPrice}</UnitPrice>
          </Product>
     from DB.Products;

This is one of my favorite features in Comega I have to admit. Although IntelliSense is rather poor for the moment (you shouldn't expect this kind of stuff to work in experimental releases) I'm sure that a well-working IntelliSense will boost developer productivity when writing this kind of code (as the matter in fact, the <Product>-section can be generated almost automatically).

Beside of using the normal constructors in the projections, you can use functions too, or tuple constructors:

public SqlString ConvertPrice(SqlMoney unitPrice)
{
     return "EUR " + unitPrice;
}

foreach (row in select ConvertPrice(UnitPrice) from DB.Products)
{
     //row is SqlString
}

or

foreach (row in select new{Name=ProductName, Price=UnitPrice} from DB.Products)
{
     //row is tuple type with correctly-typed child attributes
}

Restricting the selection results

First of all, there's the wellknown select clause. Thanks to the integration with the Comega language you'll have a strongly-typed and type-checked syntax for the specification of where-clauses:

results = select ProductID, SupplierID from DB.Products where UnitsInStock < 5;

Other operators are >, ==, !=, <=, >=. To combine expressions the operators && (and) and || (or) can be used. It's important to realize that the selection is happening on the server, so the where-clause is translated into a WHERE-clause in a SQL statement behind the scenes. When using a variable in the condition, it will be evaluated once:

int baseLevel = 5;
results = select ProductID, SupplierID from DB.Products where UnitsInStock < baseLevel;

Next, we have the distinct keyword at our service:

results = select distinct SupplierID from DB.Products;

and the top keyword which can be useful in combinatio with sort operations too (just to give an example):

int supplier = ...;
results = select top 10 ProductName from DB.Products where SupplierID == supplier order by UnitPrice desc;

Should look familiar. The top keyword can also be combined with the keyword percent to have a factor relative to the number of selected rows (e.g. top 25 percent).

Singleton select

Now, one of the problems we have in here is that we always retrieve a stream of records, even when there's only one result. A selection on a primary key will typically generate only one result. The same holds for aggregates which are supported too:

int n = select singleton count(*) from DB.Products;
string name = select singleton ProductName from DB.Products where ProductID=1;

Joins

Okay, I guess you already can see how transparent the use of SQL keywords in Comega is, compared to the equivalent keywords in the T-SQL language. The same holds for all kind of joins:

  • just using a where-clause and different selected tables, aliased with a name (select x.PK, x.Bla, y.Bla from x as DB.TableX, y as DB.TableY where x.PK == y.FK)
  • inner joins with inner join ... in ... on ...
  • left joins with left join ... in ... on ...
  • right joins, outer joins

Grouping, aggregates, having

Exactly the same as in "classic SQL". Just try it and it should work :-)

Subqueries and quantifiers

Subqueries are supported too, which allows nesting of selects. The simplest form of this is to use a singleton select in the subquery expression and to compare that result with some given value. The documentation shows this relevant sample:

   rows = select ContactName, Phone
            from c in DB.Customers
            where (select singleton Count(OrderID)
                     from o in DB.Orders
                     where o.CustomerID == c.CustomerID)
> 10;

So, as you can see, the nested query has access to the fields of the outer query (in this case, the CustomerID) for comparison in the where clause. The idea is that a singleton select returns a scalar value, not a stream, so it can be compared with some other value or it can be inserted in the results of a select as a field. However, sometimes you want to operate on streams (e.g. a query based on the existence of a result performed by a subquery). To allow this, Comega has the operators all, any, exists that we call quantifiers. The usage of these is pretty straightforward and more information can be found in the documentation once again.

Functions

Although not all T-SQL functions are supported (luckily :o) the most relevant ones are, including mathematical functions, string functions and date/time functions.

More to come

In the next post for episode 5 I'll show the DML statements insert, update and delete, as well as transactions.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under:

Comments

No Comments