public void NotNullOk()
{
var col = new DataColumn("stock_id", typeof(long));
col.AllowDBNull = false;
TableSchema schema = new TableSchema("test1",
col,
new DataColumn("value", typeof(double)));
DataTable t = schema.NewTempTable();
ScopedTable table = new ScopedTable(schema, t, null);
DataRow row = table.NewRow();
row["stock_id"] = 1;
row["value"] = 23.0d;
table.Add(row);
var rows = table.Rows.ToList();
Assert.AreEqual(1, rows.Count);
Assert.AreEqual(1, row["stock_id"]);
Assert.AreEqual(23.0d, row["value"]);
}
public void Select()
{
TableSchema schema = CreateSchema("test1");
ITableManager mgr = myScriptingInterface.GetManager(schema);
mgr.CreateTable();
// add some data
ScopedTable table = mgr.Query(0);
AddRow(table, 0, new DateTime(2001, 1, 1), 1);
AddRow(table, 0, new DateTime(2002, 1, 1), 2);
table = mgr.Query(1);
AddRow(table, 1, new DateTime(2010, 1, 1), 12);
// select
var rows = mgr.Query(0).Rows.ToList();
Assert.AreEqual(2, rows.Count);
Assert.AreNotEqual(DBNull.Value, rows[0]["id"]);
Assert.AreNotEqual(DBNull.Value, rows[1]["id"]);
Assert.AreNotEqual(rows[0]["id"], rows[1]["id"]);
Assert.AreEqual(0, rows[0]["stock_id"]);
Assert.AreEqual(new DateTime(2001, 1, 1), rows[0].GetDate(schema));
Assert.AreEqual(1, rows[0]["value"]);
Assert.AreEqual(0, rows[1]["stock_id"]);
Assert.AreEqual(new DateTime(2002, 1, 1), rows[1].GetDate(schema));
Assert.AreEqual(2, rows[1]["value"]);
Assert.IsTrue(rows.All(r => r.RowState == DataRowState.Unchanged));
}
public void OriginAddOrUpdate()
{
TableSchema schema = new TableSchema("OriginTest",
new DataColumn("stock_id", typeof(long)),
new DataColumn("date", typeof(string)),
new DataColumn("datum_origin_id", typeof(long)),
new DataColumn("value", typeof(double)));
DataTable t = schema.NewTempTable();
ScopedTable table = new ScopedTable(schema, t, null);
AddRow(table, 1, "2002-01-02 00:00", 1, 2.1d);
AddRow(table, 1, "2002-01-02 00:00", 1, 2.2d);
AddRow(table, 1, "2002-01-02 00:00", 1, 2.3d);
AddRow(table, 1, "2003-01-02 00:00", 1, 3.1d);
AddRow(table, 1, "2003-01-02 00:00", 3, 3.3d);
AddRow(table, 1, "2004-01-02 00:00", 3, 4.3d);
var rows = table.Rows.ToList();
Assert.AreEqual(4, rows.Count);
Assert.AreEqual(2.3d, (double)rows[0]["value"], 0.000001d);
Assert.AreEqual(3.1d, (double)rows[1]["value"], 0.000001d);
Assert.AreEqual(3.3d, (double)rows[2]["value"], 0.000001d);
Assert.AreEqual(4.3d, (double)rows[3]["value"], 0.000001d);
}
public void SelectMultiple()
{
TableSchema schema = CreateSchema("test1");
ITableManager mgr = myScriptingInterface.GetManager(schema);
mgr.CreateTable();
// add some data
ScopedTable table = mgr.Query(0);
AddRow(table, 0, new DateTime(2001, 1, 1), 1);
AddRow(table, 0, new DateTime(2002, 1, 1), 2);
AddRow(table, 1, new DateTime(2010, 1, 1), 2);
// first select
var table1 = mgr.Query(0).Rows.ToList();
Assert.AreEqual(2, table1.Count());
Assert.AreEqual(1, table1[0]["value"]);
Assert.AreEqual(2, table1[1]["value"]);
Assert.IsTrue(table1.All(r => r.RowState == DataRowState.Unchanged));
// now select again
var table2 = mgr.Query(0).Rows.ToList();
Assert.AreEqual(2, table1.Count());
Assert.AreEqual(1, table1[0]["value"]);
Assert.AreEqual(2, table1[1]["value"]);
Assert.IsTrue(table1.All(r => r.RowState == DataRowState.Unchanged));
}
public override void SetUp()
{
base.SetUp();
myDB = myMockery.NewMock <IDatabaseSC>();
var serviceProvider = new ServiceProvider();
serviceProvider.RegisterService("TOM Database", myDB);
myScriptingInterface = new ScriptingInterface();
myScriptingInterface.Init(serviceProvider);
// prepare data
mySchema = CreateSchema("test1");
myMgr = myScriptingInterface.GetManager(mySchema);
myMgr.CreateTable();
// add some data
ScopedTable table = myMgr.Query(0);
AddRow(table, 0, 0, new DateTime(2001, 1, 1), 1);
AddRow(table, 0, 0, new DateTime(2002, 1, 1), 2);
AddRow(table, 0, 1, new DateTime(2002, 1, 1), 12);
AddRow(table, 0, 0, new DateTime(2003, 1, 1), 3);
AddRow(table, 0, 1, new DateTime(2003, 1, 1), 13);
AddRow(table, 0, 2, new DateTime(2003, 1, 1), 23);
AddRow(table, 0, 0, new DateTime(2004, 1, 1), 4);
AddRow(table, 0, 1, new DateTime(2005, 1, 1), 15);
table = myMgr.Query(1);
AddRow(table, 1, 1, new DateTime(2002, 1, 1), 112);
}
public static void Single(this DataColumn col, object value)
{
var inputTable = Interpreter.Context.TomScripting.GetManager(col.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
long ownerId = stock.GetId(inputTable.Schema.OwnerIdColumn);
using (TransactionScope trans = new TransactionScope())
{
object obj = value;
if (col.DataType == typeof(double))
{
obj = Convert.ToDouble(value, CultureInfo.InvariantCulture);
}
ScopedTable result = inputTable.Query(ownerId);
DataRow row = result.NewRow();
row[inputTable.Schema.OwnerIdColumn] = ownerId;
row[col.ColumnName] = obj;
result.AddOrUpdate(row, col.ColumnName);
trans.Complete();
}
}
private void SetupInterpreter()
{
AddDummyStock();
TableSchema schema = new TableSchema("stock_price",
new DataColumn("traded_stock_id", typeof(long)),
new DataColumn("date", typeof(string)),
new DataColumn("close", typeof(double)))
.Create();
// we have a datum so we need the table now
ScopedTable table = schema.Manager().Query(CurrentStockId);
AddStockPrice(table, "2000-12-12 00:00", 12.2d);
AddStockPrice(table, "2000-12-11 00:00", 12.1d);
AddStockPrice(table, "2000-12-13 00:00", 12.3d);
AddStockPrice(table, "2001-12-12 00:00", 13.2d);
AddStockPrice(table, "2001-12-11 00:00", 13.1d);
AddStockPrice(table, "2001-12-13 00:00", 13.3d);
AddStockPrice(table, "2002-12-12 00:00", 14.2d);
AddStockPrice(table, "2002-12-11 00:00", 14.1d);
AddStockPrice(table, "2002-12-13 00:00", 14.3d);
AddStockPrice(table, "2003-12-12 00:00", 15.2d);
AddStockPrice(table, "2003-12-11 00:00", 15.1d);
AddStockPrice(table, "2003-12-13 00:00", 15.3d);
AddStockPrice(table, "2004-12-12 00:00", 16.2d);
AddStockPrice(table, "2004-12-11 00:00", 16.1d);
AddStockPrice(table, "2004-12-13 00:00", 16.3d);
}
protected void AddRow(ScopedTable table, long id, string value)
{
DataRow row = table.NewRow();
row[table.Schema.OwnerIdColumn] = id;
row["Value"] = value;
table.Add(row);
}
private void AddStockPrice(ScopedTable table, string date, double value)
{
DataRow row = table.NewRow();
row["traded_stock_id"] = CurrentTradedStockId;
row.SetDate(table.Schema, DateTime.Parse(date));
row["close"] = value;
table.Add(row);
}
protected void AddRow(ScopedTable table, long id, DateTime date, int value)
{
DataRow row = table.NewRow();
row[table.Schema.OwnerIdColumn] = id;
row.SetDate(table.Schema, date);
row["Value"] = value;
table.Add(row);
}
public static TableSchema Growth(this IMslScript script, DataColumn fromCol, DataColumn into)
{
if (into == null)
{
TempTable.RewriteOwnerId(fromCol).Create();
into = TempTable["value"];
}
var inputTable = Interpreter.Context.TomScripting.GetManager(fromCol.Table.TableName);
var outTable = Interpreter.Context.TomScripting.GetManager(into.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
var from = Interpreter.Context.Scope.From;
var to = Interpreter.Context.Scope.To;
long ownerId = stock.GetId(inputTable.Schema.OwnerIdColumn);
using (TransactionScope trans = new TransactionScope())
{
ScopedTable inputData = inputTable.Query(ownerId, new DateClause(from, to), OriginClause.Default);
var q = from row in inputData.Rows
orderby row.GetDateString(inputData.Schema) ascending
select new
{
Date = row.GetDate(inputData.Schema),
Value = row.Field <double>(fromCol.ColumnName),
};
var result = outTable.Query(ownerId);
double prev = double.MaxValue;
foreach (var row in q)
{
if (prev == double.MaxValue)
{
prev = row.Value;
}
double growth = row.Value - prev;
prev = row.Value;
DataRow outRow = result.NewRow();
outRow[result.Schema.OwnerIdColumn] = ownerId;
outRow.SetDate(result.Schema, row.Date);
outRow[into.ColumnName] = growth;
result.AddOrUpdate(outRow, into.ColumnName);
}
trans.Complete();
}
return(Interpreter.Context.TomScripting.GetManager(into.Table.TableName).Schema);
}
private void AddRow(ScopedTable table, long ownerId, string date, long origin, double value)
{
DataRow row = table.NewRow();
row[table.Schema.OwnerIdColumn] = ownerId;
row.SetDate(table.Schema, DateTime.Parse(date));
row[table.Schema.OriginColumn] = origin;
row["value"] = value;
table.AddOrUpdate(row);
}
public static IEnumerable <double> ToSet(this IMslScript script, DataColumn fromCol)
{
var inputTable = Interpreter.Context.TomScripting.GetManager(fromCol.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
var from = Interpreter.Context.Scope.From;
var to = Interpreter.Context.Scope.To;
long ownerId = stock.GetId(inputTable.Schema.OwnerIdColumn);
ScopedTable inputData = inputTable.Query(ownerId, new DateClause(from, to), OriginClause.Default);
return(inputData.Rows.Select(row => row.Field <double>(fromCol.ColumnName)));
}
public void Dispose()
{
foreach (var scoped in ScopedTable)
{
var disposable = scoped.Value as IDisposable;
if (disposable != null)
{
disposable.Dispose();
}
}
DependencyEntryTable.Clear();
PropertyEntryTable.Clear();
HasPropertyEntryTable.Clear();
CompileTable.Clear();
ScopedTable.Clear();
}
public void SelectDateTime()
{
TableSchema schema = CreateSchema("test1");
ITableManager mgr = myScriptingInterface.GetManager(schema);
mgr.CreateTable();
// add some data
ScopedTable table = mgr.Query(0);
AddRow(table, 0, DateTime.Now, 25);
// select
var rows = mgr.Query(0, new DateClause(DateTime.Now)).Rows.ToList();
Assert.AreEqual(1, rows.Count);
Assert.AreEqual(25, rows[0]["value"]);
}
public void SetTimestamp()
{
TableSchema schema = new TableSchema("test1",
new DataColumn("stock_id", typeof(long)),
new DataColumn("timestamp", typeof(string)),
new DataColumn("value", typeof(double)));
DataTable t = schema.NewTempTable();
ScopedTable table = new ScopedTable(schema, t, null);
DataRow row = table.NewRow();
row["stock_id"] = 1;
row["value"] = 23.0d;
table.Add(row);
Assert.IsTrue(DateTime.Now.AlmostEquals(row.GetDate("timestamp"), 1));
}
public void NotNullIsNull()
{
var col = new DataColumn("stock_id", typeof(long));
col.AllowDBNull = false;
TableSchema schema = new TableSchema("test1",
col,
new DataColumn("value", typeof(double)));
DataTable t = schema.NewTempTable();
ScopedTable table = new ScopedTable(schema, t, null);
DataRow row = table.NewRow();
row["value"] = 23.0d;
table.Add(row);
}
public void SelectFromTo()
{
TableSchema schema = CreateSchema("test1");
ITableManager mgr = myScriptingInterface.GetManager(schema);
mgr.CreateTable();
// add some data
ScopedTable table = mgr.Query(0);
AddRow(table, 0, new DateTime(2001, 1, 1), 1);
AddRow(table, 0, new DateTime(2002, 1, 1), 2);
AddRow(table, 0, new DateTime(2003, 1, 1), 3);
AddRow(table, 0, new DateTime(2004, 1, 1), 4);
AddRow(table, 0, new DateTime(2005, 1, 1), 5);
table = mgr.Query(1);
AddRow(table, 1, new DateTime(2005, 1, 1), 22);
// select
var rows = mgr.Query(0, new DateClause(new DateTime(2002, 1, 1), new DateTime(2005, 1, 1))).Rows.ToList();
Assert.AreEqual(4, rows.Count);
Assert.AreEqual(2002, rows[0].GetDate(schema).Year);
Assert.AreEqual(2003, rows[1].GetDate(schema).Year);
Assert.AreEqual(2004, rows[2].GetDate(schema).Year);
Assert.AreEqual(2005, rows[3].GetDate(schema).Year);
Assert.IsTrue(rows.All(r => r.RowState == DataRowState.Unchanged));
// select with swapped to and from to check robustnes
rows = mgr.Query(0, new DateClause(new DateTime(2005, 1, 1), new DateTime(2002, 1, 1))).Rows.ToList();
Assert.AreEqual(4, rows.Count);
Assert.AreEqual(2002, rows[0].GetDate(schema).Year);
Assert.AreEqual(2003, rows[1].GetDate(schema).Year);
Assert.AreEqual(2004, rows[2].GetDate(schema).Year);
Assert.AreEqual(2005, rows[3].GetDate(schema).Year);
}
public static TableSchema CopySeries(this IMslScript script, DataColumn fromCol, DataColumn toCol)
{
if (toCol == null)
{
TempTable.Create();
toCol = TempTable["value"];
}
var inputTable = Interpreter.Context.TomScripting.GetManager(fromCol.Table.TableName);
var outTable = Interpreter.Context.TomScripting.GetManager(toCol.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
var from = Interpreter.Context.Scope.From;
var to = Interpreter.Context.Scope.To;
long ownerId = stock.GetId(inputTable.Schema.OwnerIdColumn);
using (TransactionScope trans = new TransactionScope())
{
ScopedTable fromData = inputTable.Query(ownerId, new DateClause(from, to), OriginClause.Default);
var result = outTable.Query(ownerId);
foreach (DataRow row in fromData.Rows)
{
DataRow outRow = result.NewRow();
outRow[result.Schema.OwnerIdColumn] = ownerId;
outRow.SetDate(outTable.Schema, row.GetDate(fromData.Schema));
outRow[toCol.ColumnName] = row[fromCol.ColumnName];
result.AddOrUpdate(outRow, toCol.ColumnName);
}
trans.Complete();
}
return(Interpreter.Context.TomScripting.GetManager(toCol.Table.TableName).Schema);
}
public static TableSchema Aggregate(this IMslScript script, DataColumn fromCol, DataColumn toCol, Func <IEnumerable <double>, double> Calculator)
{
if (toCol == null)
{
TempTable.RewriteOwnerId(fromCol).Create();
toCol = TempTable["value"];
}
var inputTable = Interpreter.Context.TomScripting.GetManager(fromCol.Table.TableName);
var outTable = Interpreter.Context.TomScripting.GetManager(toCol.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
var from = Interpreter.Context.Scope.From;
var to = Interpreter.Context.Scope.To;
long ownerId = stock.GetId(inputTable.Schema.OwnerIdColumn);
using (TransactionScope trans = new TransactionScope())
{
ScopedTable inputData = inputTable.Query(ownerId, new DateClause(from, to), OriginClause.Default);
// calculate
var aggr = Calculator(inputData.Rows.Select(row => row.Field <double>(fromCol.ColumnName)));
var outData = outTable.Query(ownerId);
DataRow outRow = outData.NewRow();
outRow[outData.Schema.OwnerIdColumn] = ownerId;
outRow[toCol.ColumnName] = aggr;
outData.AddOrUpdate(outRow, toCol.ColumnName);
trans.Complete();
}
return(Interpreter.Context.TomScripting.GetManager(toCol.Table.TableName).Schema);
}
public void AddOrUpdateWithDate()
{
TableSchema schema = new TableSchema("test1",
new DataColumn("stock_id", typeof(long)),
new DataColumn("date", typeof(string)),
new DataColumn("value", typeof(double)));
DataTable t = schema.NewTempTable();
ScopedTable table = new ScopedTable(schema, t, null);
// initial row
{
DataRow row = table.NewRow();
row["stock_id"] = 1;
row.SetDate(schema, DateTime.Parse("2002-01-01 00:00"));
row["value"] = 23.0d;
table.AddOrUpdate(row);
}
// same id, same date => update
{
DataRow row = table.NewRow();
row["stock_id"] = 1;
row.SetDate(schema, DateTime.Parse("2002-01-01 00:00"));
row["value"] = 42.0d;
table.AddOrUpdate(row);
}
// same id, new date => add
{
DataRow row = table.NewRow();
row["stock_id"] = 1;
row.SetDate(schema, DateTime.Parse("2002-01-02 00:00"));
row["value"] = 25.0d;
table.AddOrUpdate(row);
}
// new stock_id, same date => add
{
DataRow row = table.NewRow();
row["stock_id"] = 2;
row.SetDate(schema, DateTime.Parse("2002-01-02 00:00"));
row["value"] = 37.0d;
table.AddOrUpdate(row);
}
var rows = table.Rows.ToList();
Assert.AreEqual(3, rows.Count);
Assert.AreEqual(1, rows[0][schema.OwnerIdColumn]);
Assert.AreEqual(42.0d, rows[0]["value"]);
Assert.AreEqual(1, rows[1][schema.OwnerIdColumn]);
Assert.AreEqual(25.0d, rows[1]["value"]);
Assert.AreEqual(2, rows[2][schema.OwnerIdColumn]);
Assert.AreEqual(37.0d, rows[2]["value"]);
}
public static TableSchema HighLow(this IMslScript script, DataColumn value, TimeGrouping grouping, DataColumn intoHigh, DataColumn intoLow)
{
if (intoHigh == null)
{
TempTable.Create();
intoHigh = TempTable["high"];
}
if (intoLow == null)
{
TempTable.Create();
intoLow = TempTable["low"];
}
if (intoHigh.Table.TableName != intoLow.Table.TableName)
{
throw new Exception("High and low columns need to belong to the same table");
}
var inputTable = Interpreter.Context.TomScripting.GetManager(value.Table.TableName);
var outTable = Interpreter.Context.TomScripting.GetManager(intoHigh.Table.TableName);
var stock = Interpreter.Context.Scope.Stock;
var from = Interpreter.Context.Scope.From;
var to = Interpreter.Context.Scope.To;
using (TransactionScope trans = new TransactionScope())
{
long ownerId = stock.GetId(outTable.Schema.OwnerIdColumn);
var result = outTable.Query(ownerId);
ScopedTable inputData = inputTable.Query(ownerId, new DateClause(from, to), OriginClause.Default);
DateTime?currentPeriod = null;
DateTime?newPeriod = null;
double low = double.MaxValue;
double high = double.MinValue;
// helper Lambda which adds a found result to the result table
Action AddResult = delegate()
{
// handle no min, max could be determined
if (low == double.MaxValue)
{
myLogger.Warning("stock_price_low could not be determined for: TradedStockId = {0}", stock.TradedStock.Id);
low = -1;
}
if (high == double.MinValue)
{
myLogger.Warning("stock_price_high could not be determined for: TradedStockId = {0}", stock.TradedStock.Id);
high = -1;
}
DataRow outRow = result.NewRow();
outRow[result.Schema.OwnerIdColumn] = ownerId;
outRow.SetDate(result.Schema, currentPeriod.Value);
outRow[intoHigh.ColumnName] = high;
outRow[intoLow.ColumnName] = low;
result.AddOrUpdate(outRow, intoHigh.ColumnName, intoLow.ColumnName);
};
foreach (DataRow row in inputData.Rows.OrderBy(row => row[inputTable.Schema.DateColumn]))
{
DateTime date = row.GetDate(inputTable.Schema);
// convert the new period
if (grouping == TimeGrouping.Year)
{
newPeriod = Blade.DateTimeExtensions.FirstOfYear(date.Year);
}
else if (grouping == TimeGrouping.Month)
{
newPeriod = new DateTime(date.Year, date.Month, 1);
}
else
{
throw new InvalidOperationException("Unsupported time period definition: " + grouping);
}
// new period start detected other than the first one?
if (currentPeriod != newPeriod && currentPeriod != null)
{
AddResult();
// reset temp variables
currentPeriod = newPeriod;
high = double.MinValue;
low = double.MaxValue;
}
else if (currentPeriod == null)
{
// handle first period
currentPeriod = newPeriod;
}
double close = (double)row[value.ColumnName];
if (close == -1)
{
myLogger.Warning("Ignoring invalid close price for: TradedStockId = {0}", stock.TradedStock.Id);
continue;
}
low = Math.Min(close, low);
high = Math.Max(close, high);
}
// we left the time scope but is there s.th. left to store?
if (low != double.MaxValue && high != double.MinValue)
{
AddResult();
}
trans.Complete();
}
return(Interpreter.Context.TomScripting.GetManager(intoHigh.Table.TableName).Schema);
}
