Select over Objects Tutorial

This tutorial shows how to use SQL Select to select over objects in a simple program.

Sample Files

To run this tutorial, you may use the following project and source files:

CDs.cwproj
main.cw

These files are located in the \samples\Basics\CDs subdirectory under the path where you installed Cω, which by default is C:\Program Files\Comega.

Tutorial

For our next example, we will use a small database of CDs. The actual data can be defined directly as we do here, or we can read it from an external SQL database. The type of CDs exposes the CD database as a possibly empty stream of structs.

enum CDStyle {Alt, Pop, Classic}

static struct{ string Title; string Artist; CDStyle Style; int Year;}* CDs =
  new{new{ Title="Lucky Frog", Artist="Holly Holt", Style=CDStyle.Alt, Year=2001},
      new{ Title=" Eat This ", Artist="Shu Ito", Style=CDStyle.Pop, Year=1995},
      new{ Title="Stop Light Green", Artist="Robert O'Hara", Style=CDStyle.Alt, Year=1981},
      new{ Title="Noctures", Artist="Chopin", Style=CDStyle.Classic, Year=1892},
      new{ Title="Mimosa!", Artist="Brian Groth", Style=CDStyle.Alt, Year=1980}
  },

In the queries and filters that we have seen so far, member access was performed on a single instance or on a single stream of instances, and projected onto another single object (or stream). Using normal member access we can easily select all titles of alternative albums using the filter-expression CDs[Style == CDStyle.Alt].Title.

Database queries on the other hand usually join together several streams of tuples (tables) and project them onto new tables. For example a typical query in that style would be to select the titles and artists of all alternative albums:

alts = select Title, Artist from CDs where Style == CDStyle.Alt;

It is possible to encode this particular query using normal selection, however the resulting code is more repetitive:

 alts = CDs[ Style == CDStyle.Alt ].{
   return new{Title = it.Title, Artist = it.Artist};
 };

In case we actually do read the data from an actual database, the Cω compiler will often map the select query onto a real SQL query. For path-based queries such as shown in the Transitive Member Access Tutorial, this would be much harder, especially if, filter, projections, apply-to-all are hand "optimized'" into a single operation.

Note that the previous select query requests a stream of CDs and returns a stream of tuples of type struct{string Title; string Artist;}*. Without anonymous structs, we would either have been forced to define a new type to hold the values returned by the query, or fall back to a completely untyped and late bound framework such as ADO.NET:

struct{string Title; string Artist;}* alts = {
    string q = "SELECT Title, Artist FROM CDs WHERE Style=CDStyle.Alt";
    IReader r = new SqlCommand(q).ExecuteReader();
    while (r.Read()) yield return new{ Title=r.GetString(0), Artist=r.GetString(1)};
    r.Close();
  };

You might ask why we need two different syntaxes for member access. The answer is that both styles are useful and largely complementary. Transitive and type based member access is mainly used to project from semi-structured data. They allow us to find information in an arbitrary object graph. In contrast, select style member access is mainly used to restructure data and works particularly well over flat tables. The same situation occurs in languages such as Haskell where on the one hand we can write "map-filter-reduce''-style programs (which corresponds to member access syntax) and on the other hand list comprehensions (which correspond to our select syntax).

Example

The following is a complete Cω program that demonstrates the concepts discussed above.

using System;

public class Test {

  enum CDStyle{Alt, Pop, Classic}
  
  static struct{ string Title; string Artist; CDStyle Style; int Year;}* CDs =
  new{new{ Title="Lucky Frog", Artist="Holly Holt", Style=CDStyle.Alt, Year=2001},
      new{ Title=" Eat This ", Artist="Shu Ito", Style=CDStyle.Pop, Year=1995},
      new{ Title="Stop Light Green", Artist="Robert O'Hara", Style=CDStyle.Alt, Year=1981},
      new{ Title="Noctures", Artist="Chopin", Style=CDStyle.Classic, Year=1892},
      new{ Title="Mimosa!", Artist="Brian Groth", Style=CDStyle.Alt, Year=1980}
  };

  public static void Main() {

    Console.WriteLine("Using SQL select ...");
    alts1 = select Title, Artist from CDs where Style == CDStyle.Alt;
    alts1.{ Console.WriteLine("Title = {0}, Artist =  {1}", it.Title, it.Artist); };

    Console.WriteLine("Using generalized member access ...");
    alts2 = CDs[ Style == CDStyle.Alt ].{
              return new{Title = it.Title, Artist = it.Artist};
            };
    alts2.{ Console.WriteLine("Title = {0}, Artist =  {1}", it.Title, it.Artist); };
    Console.ReadLine();
  }
}

Output

Using SQL select ...
Title = Lucky Frog, Artist =  Holly Holt
Title = Stop Light Green, Artist =  Robert O'Hara
Title = Mimosa!, Artist =  Brian Groth
Using generalized member access ...
Title = Lucky Frog, Artist =  Holly Holt
Title = Stop Light Green, Artist =  Robert O'Hara
Title = Mimosa!, Artist =  Brian Groth

Select over Objects Tutorial

Sample Files

Further Reading

Tutorial

Example

Output