Composite Keys and Dates

[nuu]

Question 1:

SQLite3 allows the following composite key definition.

CREATE TABLE market (

    exchangeId TEXT NOT NULL,

    marketId TEXT NOT NULL,

    status TEXT NOT NULL, -- status from exchange

    baseAsset TEXT NOT NULL,

    baseAssetPrecision INTEGER NOT NULL,

    quoteAsset TEXT NOT NULL,

    quotePrecision INTEGER NOT NULL,

    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (exchangeId, marketId)

);

How do I define this in Unity?  Would the following work?

public class MarketTable

{

    [PrimaryKey]

    public string exchangeId;



    [PrimaryKey]

    public string marketId;



    public string status;

    public string baseAsset;

    public int baseAssetPrecision;

    public string quoteAsset;

    public int quotePrecision;

    public string timestamp;

}


Question 2:

Regarding SQLite3 timestamp, is there a unity c# equivalent type?

Thanks. 

[echo17]

Currently SimpleSQL only recognizes a single primary key, but I will look into seeing if I can add multiple keys in the reflection. Keep in mind, the only thing the attributes are useful for is when you want to run the helper command CreateTable at runtime. You can still create tables at design time in a database management software with two primary keys. You can also write your own sql command and just run that if you want the table created at runtime. In other words, the attributes do not need to be added if you are not creating the table at runtime with the command CreateTable.

So you could run this:


var sql = "CREATE TABLE "MarketTable" ("ExchangeId" varchar(140) NOT NULL ,"MarketId" varchar(140), "Status" varchar(140), "BaseAsset" varchar(140), "BaseAssetPrecision" integer, "QuoteAsset" varchar(140), "QuotePrecision" integer, "Timestamp" varchar(140), PRIMARY KEY("ExchangeId","MarketId"));"

dbManager.Execute(sql);


One other thing: you didn't have the get / set parts of the parameters and the parameter names need to be capitalized (not sure if that is a Unity restriction or Visual Studio one). So a modified version of your class could be:


public class MarketTable
{
   public string ExchangeId { get; set; }

   public string MarketId { get; set; }

   public string Status { get; set; }

   public string BaseAsset { get; set; }

   public int BaseAssetPrecision { get; set; }

   public string QuoteAsset { get; set; }

   public int QuotePrecision { get; set; }

   public string Timestamp { get; set; }
}


For timestamp, I just store as TEXT. You can then convert the values at runtime, or just provide a helper property that does it for you. Something like:


public class MarketTable
{
   public string ExchangeId { get; set; }

   public string MarketId { get; set; }

   public string Status { get; set; }

   public string BaseAsset { get; set; }

   public int BaseAssetPrecision { get; set; }

   public string QuoteAsset { get; set; }

   public int QuotePrecision { get; set; }

   public string Timestamp { get; set; }

   public DateTime TimeStampConverted
  {
       get
       {
           return Convert.ToDateTime(TimeStamp);
       }
  }
}


The extra property won't interfere with the reflection and SimpleSQL will ignore it in your queries. It is simply a wrapper / converter for one of the actual reflected fields.