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.