VITA Tutorial

Part 3. Database keys, indexes, NULLs, special SQL types

Outline

In this part of the tutorial we will see how to use a number of Attributes defined by VITA to create and maintain keys and indexes in the database. We will also see how to map a property to a specific, non-default SQL type for a column in the database table.

Entity attributes for keys and indexes

VITA framework provides a range of facilities for automatically maintaining the database schema synced with entity model defined in .NET code. It provides a very convenient mechanism for managing the database schema in application life-cycle, including production deployment. The table structure is derived from entity definitions. Table relations are produced from entity references in properties. CRUD SQL commands and stored procedures are generated automatically.
For additional database artifacts like keys and indexes you can use specialized attributes that describe these artifacts, so data management engine can maintain them as well:
  • [PrimaryKey] - the primary key of the table
  • [ClusteredIndex] - the table clustered index, specifying the actual order in which data rows are stored in the table
  • [Index] - table index, used for speeding up searches
  • [Unique] - unique table index, prevents duplicate rows in the table
These attributes may be placed either on entity itself, or on entity property:
  • Property: the system assumes that the only column in the index/key is the column associated with the property.
  • Entity: in this case, the attribute must have a parameter containing a comma-separated list of (property) names. The actual index/key in the database covers the columns that correspond to properties in the list.
For attributes placed on entity and having a list of names as a parameter, the following rules apply for the names in the list:
  • A name in the list can be a property name for a property of primitive type like string, int, etc. The actual index or key will include the corresponding column
  • A name can be a database column name corresponding to an entity property. The column name for a property is specified using the [Column] attribute. This is useful in code generation in database-first approach
  • A name can be the name of the entity reference property - the property that references other entity. The database index will include the foreign key column(s) corresponding to the entity reference.
  • A name can be followed by a direction specifier like 'FirstName:DESC' or 'LastName:ASC', to explicitly set the column order in the index. (only for Index and Unique attributes).
Note that the framework will verify all names in these lists at startup time, and will throw a clear error if there is a mismatch due to typo.

Companion types

Before we start 'decorating' our book store entities with attributes for keys and indexes, we would like to introduce one convenient facility provided by VITA - companion types. Companion type is a type (interface) which is not an entity by itself, but is related to some entity and is used as a container for attributes that are placed on it instead of the 'main' entity. It is in fact a feature similar to partial classes in c#, allowing to split the definition of a class between several source files.
You may find it convenient to isolate all database-related attributes for all entities in your model into a separate source file. This cleans up the entity definitions from extra database-related 'noise'; the source file containing all this database-tuning into might be maintained by a dedicated developer or even by a database administrator.
All you need to do in this scenario is define a number of companion types (entity-like interfaces) decorated with attributes in a separate source file. All companion types are registered using EntityModule.RegisterCompanionTypes( . . . ) method in entity module constructor.
The following section shows an example of using companion types to define some database-related attributes.

Books model with keys and indexes

Let's extend our Books model and add attributes specifying extra database keys and indexes using companion types:

  // file EntityKeys.cs
  [Index("Publisher,Category,Title"), ClusteredIndex("PublishedOn,Id")]
  public interface IBookKeys : IBook {
    [Index]
    new string Title { get; set; }
  }

  public interface IPublisherKeys : IPublisher {
    [Unique]
    new string Name { get; set; }
  }

  [ClusteredIndex("LastName,Id")]
  public interface IAuthorKeys : IAuthor {
    [Index]
    new string LastName { get; set; }
  }

We added a unique index on IPublisher.Name field to prevent duplicate publishers. We set the clustered index on IBook entity to contain publishing date - so rows in the table will be placed in chronological order. The alternative might be to use "Publisher,PublishedOn" for clustered index - in this case the books for each publisher would be stored together. We also create regular index for book title - that would advantageous for some searches. The companion types will be registered in BooksModule constructor - see below.
The entity definitions do not change, except one property - IBook.PublishedOn property - it has an extra [Column] attribute - we will talk about it in the next section. Also notice the RegisterCompanionTypes call in entity module constructor - this makes our companion types known to the execution engine.

namespace Vita.Tutorial.Part3 {
  // EntityModel.cs
  [Flags]
  public enum BookEdition {
    Paperback = 0x01,
    Hardcover = 0x02,
    EBook = 0x04,
  }

  public enum BookCategory {
    Programming,
    Fiction,
    Kids,
  }

  [Entity, OrderBy("Name")]
  public interface IPublisher {
    [PrimaryKey, Auto]
    Guid Id { get; }
    string Name { get; set; } //Unique key on Name is specified in IPublisherKeys companion type
    IList<IBook> Books { get; }
  }

  [Entity, Paged, OrderBy("PublishedOn:DESC")]
  public interface IBook {
    [PrimaryKey, Auto]
    Guid Id { get; }
    [Size(50)] 
    string Title { get; set; }
    [Column(DbType = DbType.Date)]
    DateTime PublishedOn { get; set; }
    [Size(250), Nullable]
    string Description { get; set; }
    [Memo, Nullable]
    string Abstract { get; set; }
    BookCategory Category { get; set; }
    BookEdition Editions { get; set; }
    double Price { get; set; }
    IPublisher Publisher { get; set; }
    [ManyToMany(typeof(IBookAuthor))]
    IList<IAuthor> Authors { get; }
  } 

  [Entity, Paged, OrderBy("LastName,FirstName")]
  public interface IAuthor {
    [PrimaryKey, Auto]
    Guid Id { get; }
    string FirstName { get; set; }
    string LastName { get; set; }
    [ManyToMany(typeof(IBookAuthor))]
    IList<IBook> Books { get; }
  }

  [Entity, PrimaryKey("Book,Author")]
  public interface IBookAuthor {
    IBook Book { get; set; }
    IAuthor Author { get; set; }
  }

  public class MainBooksModule : EntityModule {
    public MainBooksModule(EntityArea area) : base(area, "Main") {
      RegisterEntities(typeof(IBook), typeof(IPublisher), typeof(IAuthor), typeof(IBookAuthor));
      //Register companion types that describe keys and indexes on entities
      this.RegisterCompanionTypes(typeof(IBookKeys), typeof(IAuthorKeys), typeof(IPublisherKeys));
    }
  }

  public class Part3EntityModel : EntityModel {
    public MainBooksModule Main;
    public Part3EntityModel() : base("Part3-Model") {
      var mainArea = AddArea("Part3", "part3");
      Main = new MainBooksModule(mainArea);
    }
  }

Nullable properties and [Nullable] attribute.

A column in the database table might allow NULLs - 'no value', and .NET/CLR has somewhat similar concept of 'null' reference value. There are some corner cases, however. VITA tries to make a best-matching arrangement in regards to nullability when creating or comparing a database model vs an entity model as it is specified through entity definitions. Here is some overview of rules it uses.
  • Value Types - By default non-nullable value types (like 'int') are mapped to non-nullable columns. Nullable<T> types (like 'int?') are mapped to nullable columns. You can mark a value-type property (like 'int') with Nullable attribute - in this case, the column is nullable. Null database values are automatically mapped to default values for the type. So for a NULL value in the database the 'int' property would return 0; accordingly, assigning 0 value results in NULL value in the database column.
  • String type - By default, string properties are mapped to non-nullable columns. Additionally, the property does not allow empty string value - this is enforced by pre-save validation. You have to specify Nullable attribute to make the column nullable, and to allow null .NET values (they are mapped to NULL database value.
  • Entity References - entity references (like IBook.Publisher) are not nullable by default. The foreign key columns generated in the database table are made non-nullable. You can make the column nullable using the Nullable attribute on the property.

Mapping properties to specific SQL types in the database

Let's look again at the definition of IBook.PublishedOn property:
    [Column(DbType = DbType.Date)]
    DateTime PublishedOn { get; set; }
The Column attribute appearing in this version identifies a specific SQL type - Date - for the corresponding column in the database. If you look in the database after executing this part's code, you will see that PublishedOn property has type 'Date'. Without the attribute the type would have been 'DateTime2' - default for a DateTime .NET type.
You can use the Column attribute to map .NET entity properties to specific database types. This is especially useful when you start with existing database and use database-first approach. You need to have an entity model that exactly matches the database - and in many cases, you have to override the default mapping for .NET types and specify the SQL type explicitly. Entity generator tool does this automatically for all SQL types that do match the default mapping.
The DbType values you can use are the values of the System.Data.DbType enumeration. For example, you can use DbType.AnsiStringFixedLength, combined with 'Size=10' in the Column attribute to choose the SQL type 'char(10)'.
For some MS SQL Server types there are no matching DbType values - for example, 'Geography' SQL type. For these types, you can use values defined in the CustomDbTypes static class, for example:
        [Column(DbType = CustomDbTypes.Geography), Nullable]
        byte[] GeographyProp { get; set; }
For a complete example of using specific SQL types see the DataTypesTest.cs unit test file in the Vita.UnitTests.Vs project.

Running the sample code

The sample code for this part does very little - it opens the entity store and then prints the index information. At startup time the system checks the database schema and creates the missing keys and indexes. The sample code queries the database with a somewhat tricky query that returns a table listing all keys and indexes. The index/key list is printed to the console:

tut_part3_output.jpg

The output contains additional flags for each index/key like Unique, Clustered etc. Rows with no flags identify regular indexes.
Note that database schema update process automatically detects when index composition (list of columns) or any of the columns in the index change compared to previous version - in this case it drops and recreates the index/key.

Tutorial Home

Last edited Mar 29, 2013 at 3:34 AM by rivantsov, version 23

Comments

No comments yet.