Database

Database setup
https://www.codedojo.com/?p=2132

There will be two types of database. The local database and the server database.

The local database is a list of all the data that the player can access The server database
 * Receives data from the server between turn cycles
 * Sends all actions taken by the player as requests to the server
 * Currently a Postgresql database

For a lan game this will be a server hosted via the host. All players will connect to that database via their local database. If the game ever succeeds I can host databases myself.

Current options
 * Host on a website and then use php to send pull requests etc
 * Upgrade to the beta and attempt to have postgresql compatibility
 * Upgrade to .net 4.6 (experimental) in the current unity
 * have one player host an additional sqllite server and have unity communicate with one another to message that server

Database structure
There are groups of tables local to the users machine, the players DB and the game DBs. The Player DB will contain everything that the player has configured, like username, player preferences and the like. The game DBs are the databases created for each of the game states. Every save, new game or map created needs to have it's own database.

Preferred Solution
Have the Player Tables and the Game Tables be seperate databases, with each map generating it's own schema to keep it's files seperate from one another.

Secondary Options
Postgres may have trouble with accessing multiple databases simultaneously, if this is the case the player table could be made into a schema aswell as all of the databases.

Saves
PgSqlDump pgSqlDump = new PgSqlDump; pgSqlDump.Connection = conn; pgSqlDump.Backup("d:\\sqldump1.dmp");

UUIDs
CREATE IF NOT EXISTS EXTENSION "pgcrypto";

INSERT INTO items VALUES( gen_random_uuid, 54.321, 31, 'desc 1', 31.94 ) ;

| CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Multi commit
The inefficiency was in the gameobject creation. Need to develop a queue and all the commands to it and then run it in a batch command. (This is only for updates since selects are always done in batch)

Idea 1
Each table has a private commit string that can be added to and told to be committed when finished.

Methods

 * Start transaction
 * Check the string
 * Add command
 * Takes in params for the given table and then does the command into the database
 * End transaction
 * Execute the command string
 * Clean the string

Idea 2
Have a transaction class that is created which holds the command string and can add to the string and eventually execute the command.

Class Structure
Base Class ~Base Class {    if(m_command != null) {       Throw new TransactionNotFinishedError(m_command); } } Data Base Command
 * Command string
 * Execute command
 * Kill transaction
 * Error for killing an unfinished transaction
 * m_Table


 * Add to command string based on table parameters

Idea 3(Which will need to be implemented anyway)
Allow commands to take in arrays as a parameter. From the array generate the command and then do it as a single execution. Want to make array collection simple so as to not lose to much of the performance between the two arrays.

Have the database create the game objects
 * Database creates the data and adds it to it's string, then tells the tile to spawn off it's game object and moves onto the next tile

Transactions (Idea 4)
Create a wrapper class for transactions for each table

abstract class Transaction : Select, Update, Insert {    SQLType m_type; //insert,update, select make an enum private connectionString; private primarykey; private Dictionary column; //column name, valuetype bool touched; GetType Transaction(SQLType givenType) {         m_type =givenType; touched=false; }    ~Transaction {         if(commandString != null) {              throw new UnfinishedTransactionException; }    }     public static ExecuteCommand(Transaction givenTransaction) {         if(!touched){throw new Unimplementtransaction;} if(givenTransaction.GetType != SQLType.select) {              Database.ExecuteCommand(commandstring); commandstring = null; Destroy(givenTransaction); }         throw new WrongTransactionTypeError; }      public static Idatareader ExecuteSelect(Transaction givenTransaction) {         if(!touched){throw new Unimplementtransaction;} if(givenTransaction.GetType == SQLType.select) {              Idatareader output = Database.ExecuteSQL(commandstring); commandstring = null; Destroy(givenTransaction); }         throw new WrongTransactionTypeError; }  }  Interface Select {    AddSelectCondition(string columnName, string Value); public Idatareader ExecuteSelect; } Interface Update {     UpdateItem(string key, string[] givenfields); UpdateItem(uuid givenItem, string columnName, string value); } Interface Insert {     AddNewItem(T givenItem); } TableTransaction : Transaction {    Table(m_type) {         switch(m_type) {              case SQLType.select: connectionstring = "select * from Table"; break; case SQLType.update: connectionstring = "update Table "; break; case SQLType.insert: connectionstring = "insert into Table (col1,col2,col3)"; break; }    }     public void AddSelectCondition(string givenColumn, givenValue) {         switch(m_type) {              if(touched) {                  connectionstring += " And "; }              else{touched = false;} case SQLType.select :connectionstring += "where "+givenColumn +"="+givenValue; }    }     public void UpdateItem(Item givenItem) {         dictionary givenfield = dictionary; keyvaluepair key= new keyvaluepair(key, item.col1); //this is the key column dictionary.add("col2", (string)item.col2); dictionary.add("col3", (string)item.col3); UpdateItem(key, givenfield); }    public void UpdateItem(string column, ColumnField oldValue, ColumnField newValue) {         dictionary givenfield = dictionary; keyvaluepair key= new keyvaluepair(column, oldValue); //this is the key column dictionary.add(oldValue, newValue); UpdateItem(key, givenfield); }    public void UpdateItem(keyvaluepair key, string[] givenField) {         if(touched) {            connectionstring += ", "; }         else{touched = false;} connectionstring +="set "; bool firstItem; foreach(KeyValuePair item in myDictionary) {               if(!firstItem) {                   connectionstring +=", " }              else {firstItem = true;} connectionstring+=item.key+"="+item.value; }         connectionstring+= "where "+key.key+"="+key.value; } }

LINQ (Idea 5)
DataContext db = new DataContext(dbconnection);
 * https://msdn.microsoft.com/en-us/library/bb384470.aspx
 * https://msdn.microsoft.com/en-us/library/bb384511.aspx
 * https://msdn.microsoft.com/en-us/library/bb384396.aspx

[global::System.Data.Linq.Mapping.TableAttribute(Name="chevauchee.tile")]    <Type Name="User"> <Column Name="UserId" Type="System.Guid" DbType="Uniqueidentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" /> <Column Name="Username" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false" /> <Column Name="ApplicationName" Type="System.String" DbType="NVarChar(255) NOT NULL" CanBeNull="false" /> <Column Name="Email" Type="System.String" DbType="NVarChar(128) NOT NULL" CanBeNull="false" /> <Column Name="Comment" Type="System.String" DbType="NVarChar(255)" CanBeNull="true" /> <Column Name="Password" Type="System.String" DbType="NVarChar(128) NOT NULL" CanBeNull="false" /> <Column Name="PasswordQuestion" Type="System.String" DbType="NVarChar(255)" CanBeNull="true" /> <Column Name="PasswordAnswer" Type="System.String" DbType="NVarChar(255)" CanBeNull="true" /> <Column Name="IsApproved" Type="System.Boolean" DbType="Bit" CanBeNull="true" /> <Column Name="LastActivityDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="LastLoginDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="LastPasswordChangedDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="CreationDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="IsOnLine" Type="System.Boolean" DbType="Bit" CanBeNull="true" /> <Column Name="IsLockedOut" Type="System.Boolean" DbType="Bit" CanBeNull="true" /> <Column Name="LastLockedOutDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="FailedPasswordAttemptCount" Type="System.Int32" DbType="Int" CanBeNull="true" /> <Column Name="FailedPasswordAttemptWindowStart" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="FailedPasswordAnswerAttemptCount" Type="System.Int32" DbType="Int" CanBeNull="true" /> <Column Name="FailedPasswordAnswerAttemptWindowStart" Type="System.DateTime" DbType="DateTime" CanBeNull="true" /> <Column Name="OneTimeToken" Type="System.Guid" DbType="Uniqueidentifier" CanBeNull="true" /> <Column Name="LoginCount" Type="System.Int32" DbType="INT" CanBeNull="true" /> <Column Name="LastVerifiedDate" Type="System.DateTime" CanBeNull="true" /> <Association Name="User_UsersInRole" Member="UsersInRoles" ThisKey="UserId" OtherKey="UserId" Type="UsersInRole" /> <Association Name="User_Profile" Member="Profile" ThisKey="UserId" OtherKey="UserId" Type="Profile" Cardinality="One" /> <Association Name="User_MemberProduct" Member="MemberProducts" ThisKey="UserId" OtherKey="UserId" Type="MemberProduct" /> <Association Name="User_ProductManager" Member="ProductManagers" ThisKey="UserId" OtherKey="UserId" Type="ProductManager" /> <Association Name="User_Feedback" Member="Feedbacks" ThisKey="UserId" OtherKey="UserId" Type="Feedback" /> <Association Name="User_PurchaseRecord" Member="PurchaseRecords" ThisKey="UserId" OtherKey="userId" Type="PurchaseRecord" /> <Association Name="User_ISS_KEYWORD_TBL" Member="ISS_KEYWORD_TBLs" ThisKey="UserId" OtherKey="AddedBy" Type="ISS_KEYWORD_TBL" /> <Association Name="User_ISS_RESOURCE_HISTORY_TBL" Member="ISS_RESOURCE_HISTORY_TBLs" ThisKey="UserId" OtherKey="ModifiedBy" Type="ISS_RESOURCE_HISTORY_TBL" /> <Association Name="User_ISS_LOCATION_TBL" Member="ISS_LOCATION_TBLs" ThisKey="UserId" OtherKey="AddedBy" Type="ISS_LOCATION_TBL" /> <Association Name="User_UserDownloadRecord" Member="UserDownloadRecords" ThisKey="UserId" OtherKey="UserId" Type="UserDownloadRecord" /> <Association Name="User_TK_FLOATING_LICENCE_USER" Member="TK_FLOATING_LICENCE_USERs" ThisKey="UserId" OtherKey="User_Id" Type="TK_FLOATING_LICENCE_USER" /> <Association Name="User_TK_LOGIN_FAILURE_EMAIL_ACCESS" Member="TK_LOGIN_FAILURE_EMAIL_ACCESSes" ThisKey="UserId" OtherKey="USER_ID" Type="TK_LOGIN_FAILURE_EMAIL_ACCESS" /> <Association Name="TK_PRODUCT_USAGE_User" Member="TK_PRODUCT_USAGE" ThisKey="UserId" OtherKey="USER_ID" Type="TK_PRODUCT_USAGE" IsForeignKey="true" /> <Association Name="TK_PRODUCT_LOGIN_ERRORS_User" Member="TK_PRODUCT_LOGIN_ERRORS" ThisKey="UserId" OtherKey="UserId" Type="TK_PRODUCT_LOGIN_ERRORS" IsForeignKey="true" /> </Type> <Function Name="dbo.GetNewId" Method="GetNewId" IsComposable="true"> <Return Type="System.Guid" /> </Function> </Database>

Primary Keys
http://www.postgresqltutorial.com/postgresql-primary-key/

Data Readers
Use the Data Reader to iterate through the rows, while iterating through the rows you may get the desired columns by using ["<ColumnName>"]. Could build a wrapper for the Datareader in the transaction but not sure if the encapsulation is worth it

Common Queries
Getting all controled tiles from a character select * from nobility (take the m_rank and the m_kingdom)

where character_id = m_id

select * from tile

tile left join on domain, domain inner join character, character inner join nobility

where nobility.kingdom_id = m_kingdom

where nobility.rank < m_rank or where nobility.character_id = m_character_id

Queries
Select all oaths for a player select * from oaths (grab secondary oath as opposite_oath)

outer join oath_items on oathID and character

select * from oaths

outer join oath_items on oathID and character

where oathId = opposite_oath

Alliances
Since an oath will by definition affect all other parties of the opposite side, this can make an alliance an awkward proposition to ask for in a multi party negotiation. This means that when an alliance is entered into, all parties within the alliance must be specified. There are two ways to do an alliance the first is to spawn off individual oaths between all of the members.

Creating the Map
CreateMap {   if (database == null) {       CreateMap; }   int[][] tileBounds = new int[2]; tilebounds[0] = new int[]{cam.pos.x-horizontalDiameter,cam.pos.y-verticalDiameter}; tilebounds[1] = new int[]{cam.pos.x+horizontalDiameter, cam.pos.y+verticalDiameter}; CreateView; Task.Run( =>ExtendView); } private void CreateView(int[][] givenBound) {    IDataReader viewBox = TileTransaction.SelectBox(givenBound); }

static IDataReader TileTransaction.SelectBox(int[][] givenBound) {   TileTransaction selectSquare = CreateTileTransaction; selectSquare.dbcommand.commandtext = "SELECT * FROM TILE WHERE ((x="+givenBound[0][0]+" OR x="+givenBound[1][0]+")AND (y>="+givenBound[0][1]+"OR y<="+givenBound[1][1]+")   OR (y="+givenBound[0][1]+" OR y="+givenBound[1][1]+")AND (x>="+givenBound[0][0]+"OR x<="+givenBound[1][0]+")";    return selectSquare.ExecuteReader; }