Database classes

Note: Starcounter 3.0.0 is currently in preview stage. This API might be changed in the future releases without backwards compatibility.

Introduction

The database schema in Starcounter is defined by C# classes with the [Database] attribute:

using Starcounter.Nova;

[Database]
public abstract class Person
{
    public abstract string FirstName { get; set; }
    public abstract string LastName { get; set; }
    public string FullName => $"{FirstName} {LastName}";
}

All instances of database classes created with the IDatabaseContext.Insert method are stored persistently.

Constructors

Database classes support default constructors. Non-private default constructors are called when a new instance is created with IDatabaseContext.Insert.

For example, this is a valid database class with a constructor:

using Starcounter.Nova;

[Database]
public abstract class Order
{
    public Order()
    {
        this.Created = DateTime.Now;
    }

    public abstract DateTime Created { get; set; }
}

It's possible to have constructors with parameters, although, they are never called when using IDatabaseContext.Insert. Constructors with parameters in database classes can be useful for unit testing purposes when you want to inject dependencies or other arguments into a class. If you add a constructor with parameters to a database class, you also have to add a default constructor.

Warning: All C# access modifiers are accepted for constructors, except for internal. Using it will throw ScErrSchemaCodeMismatch (SCERR4177) exception.

Fields and properties

Database classes should only use properties - either auto-implemented or with an explicitly declared body.

Properties should also be public and either abstract or virtual. It is recommended to use abstract properties to reduce application memory footprint.

Collections

It's also possible to have methods to retrieve collections of the database objects.

public IEnumerable<Person> SelectFriends(IDatabaseContext db)
{
    return db.Sql<Person>("SELECT p FROM Person p");
}

These properties and fields are not allowed:

public string[] Names { get; set; }
public List<Person> People { get; }
public IEnumerable Animals;

To access collections from the database objects, first retrieve the object and then call the method that returns the collection:

var transactor = services.GetRequiredService<ITransactor>();

transactor.Transact(db =>
{
    var person = db.Sql<Person>("SELECT p FROM Person p").First();
    IEnumerable<Person> friends = person.Friends;
});

Note: In the previous versions of Starcounter with static database access via the Db class it was possible to define IEnumerable properties, not methods. This type of syntax will come back in the next alpha release.

Indexing

Database indexes can be defined with CREATE INDEX SQL query. Unique and not unique indexes are supported.

var ddlExecutor = services.GetRequiredService<IDdlExecutor>();

ddlExecutor.Execute("CREATE INDEX IX_Person_FirstName ON Person (FirstName)");

A single property index can be created with the [Index] attribute:

using Starcounter.Nova;

[Database]
public class Person
{
    [Index]
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

Note: In the previous versions of Starcounter it was possible to execute DDL statements using Db.SQL method. This is no longer allowed, all the DDL statements have to be executed using the IDdlExecutor service.

Limitations

Property limit

Database classes can have a maximum of 112 properties for performance reasons. The limit applies to the total number of persistent properties (including all inherited) per class.

Thus, this is not allowed:

[Database]
public class LargeClass
{
    public virtual string Property1 { get; set; }
    public virtual string Property2 { get; set; }

    // ...

    public virtual string Property113 { get; set; }
}

If a database class has more than 113 properties, Starcounter throws ScErrToManyAttributes (SCERR4013).

Nested classes

Nested database classes are not supported. The limitation is that inner database classes cannot be queried with SQL.

Relations

One-to-many relations

We recommend modeling one-to-many relationships by having references both ways - the child has a reference to the parent and the parent has a method to select all the children.

In this example there is a one-to-many relationship between Department and Employee:

using Starcounter.Nova;

[Database]
public class Department
{
    public IEnumerable<Employee> SelectEmployees(IDatabaseContext db)
    {
        return db.Sql<Employee>
        (
            "SELECT e FROM Employee e WHERE e.Department = ?",
            this
        );
    }
}

[Database]
public class Employee
{
    public abstract Department Department { get; set; }
}

Many-to-many relations

We recommend modeling many-to-many relationships with an associative class.

In this example there is a many-to-many relation between Person and Company - to represent this many-to-many relationship we use the associative class Shares:

using Starcounter.Nova;

[Database]
public class Person
{
    public IEnumerable<Company> SelectEquityPortfolio(IDatabaseContext db)
    {
        return db.Sql<Company>
        (
            "SELECT s.Equity FROM Shares s WHERE s.Owner = ?",
            this
        );
    }
}

[Database]
public class Company
{
    public IEnumerable<Person> SelectShareHolders(IDatabaseContext db)
    {
        return db.Sql<Person>
        (
            "SELECT s.Owner FROM Shares s WHERE s.Equity = ?",
            this
        );
    }
}

[Database]
public class Shares
{
    public virtual Person Owner { get; set; }
    public virtual Company Equity { get; set; }
    public virtual int Quantity { get; set; }
}

Inheritance

Any database class can inherit from any other database class.

using Starcounter.Nova;

[Database]
public class Customer
{
   public virtual string Name { get; set; }
}

public class PrivateCustomer : Customer
{
   public virtual string Gender { get; set; }
}

public class CorporateCustomer : Customer
{
   public virtual string VatNumber { get; set; }
}

The [Database] attribute is inherited from base - to subclasses. Any class that directly or indirectly inherits a class with the [Database] attribute becomes a database class. In the example above, both PrivateCustomer and CorporateCustomer become database classes due to them inheriting Customer.

The table Customer will contain all PrivateCustomers and all CorporateCustomers. So if there is a private customer called "Goldman, Carl" and a corporate customer called "Goldman Sachs", the result of SELECT C FROM Customer c will contain both of them.

Inheriting from non-database classes

A database class cannot inherit from a class that's not a database class. This will throw, during compile-time, System.NotSupportedException or ScErrSchemasDoNotMatch (SCERR15009) depending on how the base class is defined.

It's also not possible to cast a non-database class to a database class.

Comparing database objects

Database objects can be checked for equality with the Equals method. Comparing database objects with object.ReferenceEquals or the == operator always returns false if any of the objects are retrieved from the database:

var transactor = services.GetRequiredService<ITransactor>();

transactor.Transact(db =>
{
    var firstProduct = db.Insert<Product>();
    var secondProduct = db.Insert<Product>();
    var anotherFirstProduct = db.Get<Product>(db.GetOid(firstProduct));

    // Checks if two database objects are equal 
    Console.WriteLine(firstProduct.Equals(secondProduct)); // => false
    Console.WriteLine(firstProduct.Equals(anotherFirstProduct)); // => true

    // Returns false for different object or objects retrieved from the database

    // Writes: false
    Console.WriteLine(firstProduct == secondProduct);

    // Writes: false
    Console.WriteLine(firstProduct == anotherFirstProduct);

    // Writes: true
    Console.WriteLine(firstProduct == firstProduct);

    // Writes: false
    Console.WriteLine(object.ReferenceEquals(firstProduct, secondProduct));

    // Writes: false
    Console.WriteLine(object.ReferenceEquals(firstProduct, anotherFirstProduct));

    // Writes: true
    Console.WriteLine(object.ReferenceEquals(firstProduct, firstProduct));
});

Database object identity

Starcounter automatically assigns an UInt64 unique key for each database object. The key is unique across entire database not across one table.

Get object's unique key

var transactor = services.GetRequiredService<ITransactor>();

transactor.Transact(db =>
{
    var p = db.Insert<Product>();
    ulong oid = db.GetOid(p);
});

Get object by unique key

var transactor = services.GetRequiredService<ITransactor>();

transactor.Transact(db =>
{
    var p = db.Get<Product>(oid);
});

Querying by object's unique key

var transactor = services.GetRequiredService<ITransactor>();

transactor.Transact(db =>
{
    var product = db.Sql<Product>("SELECT p FROM Product p WHERE p.ObjectNo = ?", oid)
        .FirstOrDefault();
});

Notes

• Zero (0) is not a valid key.

• Currently it is not possible to insert a database object with predefined unique key.

• It is possible to compare database objects by their unique keys.