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.
Last updated