Defining a Table
Defining a table is no more difficult than creating a class. All the properties of the class become columns in your table. The only requirement is that all table instances are uniquely identified by an Object Identifier (OID) which is defined as a signed 32-bit integer. OIDs are unique by table.
public class ItemTab : IBaseTable {
[PrimaryKey, AutoIncrement]
public int OID { get; set; }
[MaxLength(255)]
public string Settings { get; set; }
}
public class ItemTab : IBaseTable {
[PrimaryKey, AutoIncrement]
public int OID { get; set; }
[MaxLength(255)]
public string Settings { get; set; }
}
Table attributes
The following attributes can be applied to classes:
- Table("Name") - Defines the physical name of the table in the database. If not specified, defaults to the class name.
- PrimaryKey - Defines the primary key for the table.
- AutoIncrement - Defines causes the primary key to be automatically calculated when a new row is added to the table.
- Column("Name") - Defines an alternate name for the column. If not specified, defaults to the property name.
- Indexed - Defines an index for this column speeding up queries against it.
- Indexed("name",order) - Defines an index for this column speeding up queries against it.
- MaxLength(number) - Defines the maximum length for a string column. If not used strings can be up to 2 billion characters.
- NotNull - Defines a column that cannot contain null values.
- Collation("name") - Defines a default sort for the table.
- Ignore - Ignores this property in the table definition.
Creating a table (Done only once when creating a database)
This is a method that is housed, typically, within the class implementing the IBaseTable interface, though because it's static, it can be defined anywhere. When creating a database, you simply call this method to create the table. All properties in a class of type IBaseTable are created as columns in the SQLite table of the same name as the class.
public static void CreateTable() {
Repository.GetTemporaryDatabaseConnection().CreateTable<ItemTab>();
}
public static void CreateTable() {
Repository.GetTemporaryDatabaseConnection().CreateTable<ItemTab>();
}
Initializing a database and creating factories
Each instance in the system is uniquely identified by an Object IDentifier (OID). It's a sequentially assigned number that uniquely identifies all your rows in the table. This is a call to the database that actually transforms an OID to a row from a table.
Once you have a table, you need to transform that table row instance into a domain object.
public static void Initialize() {
// Initialize a set of GetOID methods
Repository.AddGetOIDMethod<ItemTab>( ItemTab.GetByOID );
Repository.AddGetOIDMethod<ReferenceTab>( ReferenceTab.GetByOID );
Repository.AddGetOIDMethod<ValueTab>( ValueTab.GetByOID );
Repository.AddGetOIDMethod<LibrarySettingTab>( LibrarySettingTab.GetByOID );
// Initialize Factories for transforming tables to objects
Repository.AddReadFactory<ItemTab>( ItemReadFactory );
Repository.AddReadFactory<ValueTab>( ValueReadFactory );
Repository.AddReadFactory<ReferenceTab>( ReferenceReadFactory );
Repository.AddReadFactory<LibrarySettingTab>( LibrarySettingReadFactory );
}
This EXAMPLE method that shows how to define what is actually in a Read Factory. When we add a read factory, we are stating we are only calling The ItemReadFactory for instances of ItemTab. Therefore, it is safe to cast aRow into ROW.
private static Item ItemReadFactory(IBaseTable aRow) {
ItemTab ROW = aRow as ItemTab;
return new Item( ROW.OID , ROW.Settings ) {
}
If you look at the Repository code, AddReadFactory has one parameter "Func<IBaseTable,BaseClass>" which is a function that accepts a class that implements IBaseTable and returns BaseClass.
SINGLE TABLE INHERITANCE
If you want to use Fowler's Single Table Inheritance, you define a field in your table called "SubType", when you read from the table you put in a conditional that evaluates the SubType value and calls the right object initializer. Alternatively, if you have a lot of subtypes, I recommend you use a "Dictionary<string,Func<IBaseTable,BaseClass>>".
Once you have a table, you need to transform that table row instance into a domain object.
public static void Initialize() {
// Initialize a set of GetOID methods
Repository.AddGetOIDMethod<ItemTab>( ItemTab.GetByOID );
Repository.AddGetOIDMethod<ReferenceTab>( ReferenceTab.GetByOID );
Repository.AddGetOIDMethod<ValueTab>( ValueTab.GetByOID );
Repository.AddGetOIDMethod<LibrarySettingTab>( LibrarySettingTab.GetByOID );
// Initialize Factories for transforming tables to objects
Repository.AddReadFactory<ItemTab>( ItemReadFactory );
Repository.AddReadFactory<ValueTab>( ValueReadFactory );
Repository.AddReadFactory<ReferenceTab>( ReferenceReadFactory );
Repository.AddReadFactory<LibrarySettingTab>( LibrarySettingReadFactory );
}
This EXAMPLE method that shows how to define what is actually in a Read Factory. When we add a read factory, we are stating we are only calling The ItemReadFactory for instances of ItemTab. Therefore, it is safe to cast aRow into ROW.
private static Item ItemReadFactory(IBaseTable aRow) {
ItemTab ROW = aRow as ItemTab;
return new Item( ROW.OID , ROW.Settings ) {
}
If you look at the Repository code, AddReadFactory has one parameter "Func<IBaseTable,BaseClass>" which is a function that accepts a class that implements IBaseTable and returns BaseClass.
SINGLE TABLE INHERITANCE
If you want to use Fowler's Single Table Inheritance, you define a field in your table called "SubType", when you read from the table you put in a conditional that evaluates the SubType value and calls the right object initializer. Alternatively, if you have a lot of subtypes, I recommend you use a "Dictionary<string,Func<IBaseTable,BaseClass>>".
Opening, DELETING and creating Databases
To open a database is super straightforward:
Repository.InitializeRepository( "<<FULL PATH TO SQLite Database File>>" );
Databases are fully self contained within the file you create, so to delete a database you simply delete the file.
If you are creating a new database you say then perform CreateTable actions after you initialize the repository:
ItemTab.CreateTable();
ReferenceTab.CreateTable();
ValueTab.CreateTable();
LibrarySettingTab.CreateTable();
You will need your own logic to define when you can open an existing database, when you are creating a SQLite database from scratch and when you are deleting a database.
Repository.InitializeRepository( "<<FULL PATH TO SQLite Database File>>" );
Databases are fully self contained within the file you create, so to delete a database you simply delete the file.
If you are creating a new database you say then perform CreateTable actions after you initialize the repository:
ItemTab.CreateTable();
ReferenceTab.CreateTable();
ValueTab.CreateTable();
LibrarySettingTab.CreateTable();
You will need your own logic to define when you can open an existing database, when you are creating a SQLite database from scratch and when you are deleting a database.