# 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: ```csharp 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: ```csharp 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. ```csharp public IEnumerable SelectFriends(IDatabaseContext db) { return db.Sql("SELECT p FROM Person p"); } ``` These properties and fields are not allowed: ``` public string[] Names { get; set; } public List 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(); transactor.Transact(db => { var person = db.Sql("SELECT p FROM Person p").First(); IEnumerable 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`](https://www.w3schools.com/sql/sql_create_index.asp) SQL query. Unique and not unique indexes are supported. ```csharp var ddlExecutor = services.GetRequiredService(); ddlExecutor.Execute("CREATE INDEX IX_Person_FirstName ON Person (FirstName)"); ``` A single property index can be created with the `[Index]` attribute: ```csharp 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: ```csharp [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`: ```csharp using Starcounter.Nova; [Database] public class Department { public IEnumerable SelectEmployees(IDatabaseContext db) { return db.Sql ( "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`: ```csharp using Starcounter.Nova; [Database] public class Person { public IEnumerable SelectEquityPortfolio(IDatabaseContext db) { return db.Sql ( "SELECT s.Equity FROM Shares s WHERE s.Owner = ?", this ); } } [Database] public class Company { public IEnumerable SelectShareHolders(IDatabaseContext db) { return db.Sql ( "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. ```csharp 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: ```csharp var transactor = services.GetRequiredService(); transactor.Transact(db => { var firstProduct = db.Insert(); var secondProduct = db.Insert(); var anotherFirstProduct = db.Get(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 ```csharp var transactor = services.GetRequiredService(); transactor.Transact(db => { var p = db.Insert(); ulong oid = db.GetOid(p); }); ``` ### Get object by unique key ```csharp var transactor = services.GetRequiredService(); transactor.Transact(db => { var p = db.Get(oid); }); ``` ### Querying by object's unique key ```csharp var transactor = services.GetRequiredService(); transactor.Transact(db => { var product = db.Sql("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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.starcounter.io/3.0.0-alpha-20190930/database-classes.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.