ELENESSKI GAMES
  • Home
  • Games
    • Worzzler
    • The Lost Tribes
  • Unity
    • Facade Generator for Archimatix
    • Elenesski Object Database
    • Generic Move Camera
    • Unity C# Library
  • SQLINQ
    • Change Log
    • Concepts >
      • Overview
      • The Patterns
      • Database & Factories
      • Working with Tables
      • Custom Queries
      • Lazy Loading
      • Units of Work
    • Video Tutorials
    • Getting Started
    • Code Examples >
      • Implementing Behavior
      • Finding Objects
      • Custom Searches
      • Converters and Transformers
      • Object Association Management
      • New Objects
      • Creating Database
      • Associating Objects
      • Deleting Objects
      • Validating Objects
      • Saving
    • Sample Model >
      • Tutorial Model
      • Table Classes Code
      • Domain Classes Code
      • Factories Code
    • OSX Developers
    • Bridge Building
    • External Websites
    • Class Documentation
  • Presentations
    • 3D Ship Design
  • Music
    • Jamendo (Main)
    • Blend
    • Sound Cloud
    • Mixcloud (DJ)
    • DJ Demos
  • YouTube

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; }
}

 

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.

The following attributes can be applied to properties:
  • 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>();
        }
 

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>>".



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.