private void CreateDummyTable( bool addData )
{
// define the table
myTable = new TableSchema( "dummy",
new DataColumn( "stock_id", typeof( long ) ),
new DataColumn( "date", typeof( string ) ),
new DataColumn( "eps", typeof( double ) )
);
myTable.Create();
if ( addData )
{
var table = myTable.Manager().Query( CurrentStockId );
// add dummy values to table
DataRow row = table.NewRow();
row[ "stock_id" ] = CurrentStockId;
row[ "date" ] = "2007";
row[ "eps" ] = 20.5d;
table.Add( row );
row = table.NewRow();
row[ "stock_id" ] = CurrentStockId;
row[ "date" ] = "2005";
row[ "eps" ] = 12.5d;
table.Add( row );
row = table.NewRow();
row[ "stock_id" ] = CurrentStockId;
row[ "date" ] = "2006";
row[ "eps" ] = 17.3d;
table.Add( row );
}
}
public static void Echo( this IMslScript script, TableSchema schema )
{
long ownerId = Interpreter.Context.Scope.Stock.GetId( schema.OwnerIdColumn );
Interpreter.Context.TomScripting.GetManager( schema )
.Query( ownerId ).Rows.Dump();
}
public IEnumerable<DataRow> Query( TableSchema schema )
{
if ( Interpreter.Context.Scope.TryFrom != null && Interpreter.Context.Scope.TryTo != null )
{
return schema.QueryByScope().Rows;
}
else
{
return schema.Manager().Query( Interpreter.Context.Scope.Stock.GetId( schema.OwnerIdColumn ) ).Rows;
}
}
public ScopedTable( TableSchema schema, DataTable table, OriginClause originClause, Func<DataRow, bool> rowFilter )
{
this.Require( x => table != null );
this.Require( x => schema != null );
Schema = schema;
Table = table;
// set the row filter
// -> no operation on deleted rows
myRowFilter = r => r.RowState != DataRowState.Deleted && ( rowFilter == null || rowFilter( r ) );
myTableFilter = CreateOriginClause( originClause );
}
public void NoCurrentStock()
{
Interpreter.Context.Scope.From = new DateTime( 2001, 1, 1 );
Interpreter.Context.Scope.To = new DateTime( 2003, 12, 31 );
var input = new TableSchema( "stock_price",
new DataColumn( "traded_stock_id", typeof( long ) ),
new DataColumn( "date", typeof( string ) ),
new DataColumn( "close", typeof( double ) ) )
.Create();
this.HighLow( input[ "close" ], TimeGrouping.Year );
Assert.Fail();
}
public static ScopedTable Select(this MauiX.IQuery self, TableSchema schema, StockHandle stock)
{
return(self.Select(schema, stock, DateClause.All));
}
public DefaultFilterBuilder( TableSchema schema )
{
Schema = schema;
NotApplyableFallback = PassThroughFilter;
}
protected void CreateTimeSeries( TableSchema table, params double[] values )
{
var dataTable = table.Manager().Query( CurrentStockId );
int i = 0;
foreach ( var value in values )
{
DataRow row = dataTable.NewRow();
row[ "stock_id" ] = CurrentStockId;
row[ "year" ] = 2000 + i++;
row[ "value" ] = value;
dataTable.Add( row );
}
}
public static TableSchema Avg( this IMslScript script, TableSchema from, DataColumn into )
{
return Avg( script, from[ "value" ], into );
}
public static string GetDateString( this DataRow row, TableSchema schema )
{
return row[ schema.DateColumn ].ToString();
}
protected override void Dispose( bool disposing )
{
try
{
if ( IsDisposed )
{
return;
}
if ( disposing )
{
myFindByOwnerIdCmd.TryDispose();
myFindByOwnerIdAndFromToCmd.TryDispose();
myInsertCmd.TryDispose();
myUpdateCmd.TryDispose();
myRemoveCmd.TryDispose();
}
myDB = null;
mySchema = null;
myFindByOwnerIdAndFromToCmd = null;
myInsertCmd = null;
myUpdateCmd = null;
myRemoveCmd = null;
}
finally
{
base.Dispose( disposing );
}
}
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 TableWithoutDate()
{
TableSchema schema = new TableSchema( "test2", myIsPersistent,
new DataColumn( "stock_id", typeof( long ) ),
new DataColumn( "value", typeof( string ) ) );
ITableManager mgr = myScriptingInterface.GetManager( schema );
mgr.CreateTable();
// add some data
var table = mgr.Query( 0 );
AddRow( table, 0, "1" );
AddRow( table, 0, "2" );
AddRow( table, 0, "3" );
AddRow( table, 1, "2" );
// modify some data
{
table = mgr.Query( 0 );
Assert.AreEqual( 3, table.Rows.Count() );
// modify
table.Rows.First()[ "value" ] = "25";
// delete
var row1 = table.Rows.ElementAt( 1 );
var row2 = table.Rows.ElementAt( 2 );
row1.Delete();
row2.Delete();
Assert.AreEqual( 1, mgr.Query( 0 ).Rows.Count() );
// add
AddRow( table, 0, "4" );
}
// check modifications
var rows = mgr.Query( 0 ).Rows.ToList();
Assert.AreEqual( 2, rows.Count );
Assert.AreEqual( "25", rows[ 0 ][ "value" ] );
Assert.AreEqual( "4", rows[ 1 ][ "value" ] );
// AddOrUpdate
var row = table.NewRow();
row[ table.Schema.OwnerIdColumn ] = 0;
row[ "Value" ] = "77";
table.AddOrUpdate( row );
rows = mgr.Query( 0 ).Rows.ToList();
Assert.AreEqual( 2, rows.Count );
Assert.AreEqual( "77", rows[ 0 ][ "value" ] );
Assert.AreEqual( "77", rows[ 1 ][ "value" ] );
}
public void OriginRankingWithMerge()
{
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();
var originClause = new OriginClause( true, 2, 1 );
ScopedTable table = new ScopedTable( schema, t, originClause );
AddRow( table, 1, "2002-01-02 00:00", 1, 2.1d );
AddRow( table, 1, "2002-01-02 00:00", 2, 2.2d );
AddRow( table, 1, "2002-01-02 00:00", 3, 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( 3, rows.Count );
Assert.AreEqual( 2.2d, (double)rows[ 0 ][ "value" ], 0.000001d );
Assert.AreEqual( 3.1d, (double)rows[ 1 ][ "value" ], 0.000001d );
Assert.AreEqual( 4.3d, (double)rows[ 2 ][ "value" ], 0.000001d );
}
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 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 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" ] );
}
internal PersistentTableManager( IDatabaseSC db, TableSchema schema )
{
myDB = db;
Name = schema.Name;
mySchema = schema;
}
internal InMemoryTableManager( DataSet db, DataTable table )
{
myDB = db;
Table = table;
Schema = new TableSchema( table.TableName, table.Columns.ToSet(), false );
}
public ScopedTable( TableSchema schema, DataTable table, OriginClause originClause )
: this(schema, table, originClause, null)
{
}
internal InMemoryTableManager( DataSet db, TableSchema schema )
{
myDB = db;
Table = null;
Schema = schema;
}
public static TableSchema Growth( this IMslScript script, TableSchema from )
{
return Growth( script, from[ "value" ] );
}
public static DateTime GetDate( this DataRow row, TableSchema schema )
{
return GetDate( row, schema.DateColumn );
}
public static TableSchema Percentage( this IMslScript script, TableSchema dividend, TableSchema divisor, DataColumn into )
{
return script.Percentage( dividend[ "value" ], divisor[ "value" ], into );
}
public static void SetDate( this DataRow row, TableSchema schema, DateTime value )
{
row.SetDate( schema.DateColumn, value );
}
public static TimeSeries Create(TableSchema schema, IEnumerable <DataRow> rows, string valueColumn)
{
return(new TimeSeries(rows.Select(row =>
new TimeframedSingleValue(row.GetDate(schema), (double)row[valueColumn]))));
}
public static TableSchema Percentage( this IMslScript script, TableSchema dividend, TableSchema divisor )
{
return script.Percentage( dividend[ "value" ], divisor[ "value" ] );
}
public static TableSchema CopySeries( this IMslScript script, TableSchema from )
{
return CopySeries( script, from[ "value" ] );
}
public static TableSchema CrossSeries( this IMslScript script, TableSchema left, TableSchema right, DataColumn to, Func<double, double, double> Calculator )
{
return CrossSeries( script, left[ "value" ], right[ "value" ], to, Calculator );
}
public static TableSchema CopySeries( this IMslScript script, TableSchema from, DataColumn to )
{
return CopySeries( script, from[ "value" ], to );
}
public static TableSchema Avg( this IMslScript script, TableSchema from )
{
return Avg( script, from[ "value" ] );
}
internal PersistentTableManager(IDatabaseSC db, TableSchema schema)
{
myDB = db;
Name = schema.Name;
mySchema = schema;
}
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 );
}
public static ScopedTable Select(this MauiX.IQuery self, TableSchema schema, StockHandle stock, DateClause dateClause)
{
var mgr = Engine.ServiceProvider.TomScripting().GetManager(schema.Name);
return(mgr.Query(stock.GetId(schema.OwnerIdColumn), dateClause, OriginClause.Default));
}