/// <summary>
/// Generates INSERT query for <see cref="TableMeta"/>.
/// </summary>
/// <param name="tableMeta">Meta data of table being inspected.</param>
/// <returns>String, the INSERT query.</returns>
public static string GenerateInsertQuery(TableMeta tableMeta)
{
string columnList = string.Format("{0}{1}{2}", "(", string.Join(", ", tableMeta.Columns.Where(c => c.IsPrimaryKey == false).Select(m => string.Concat("[", m.Name, "]"))), ")");
string argumentList = string.Format("{0}{1}{2}", "(", string.Join(", ", tableMeta.Columns.Where(c => c.IsPrimaryKey == false).Select(m => string.Concat("@", m.Name))), ")");
return(string.Format("INSERT INTO {0}.{1} {2} VALUES {3} ", tableMeta.SchemaName, tableMeta.Name, columnList, argumentList));
}
internal static void gridView_CellEditorInitialize(TableMeta tableMeta, ASPxGridViewEditorEventArgs e)
{
var columnMeta = tableMeta.Columns.SingleOrDefault(c => c.Name.Equals(e.Column.FieldName, System.StringComparison.InvariantCultureIgnoreCase));
if (columnMeta == null)
{
throw new ArgumentException(string.Format("Column meta entry not found for column ", e.Column.FieldName));
}
var sqlDataSource = e.Editor.DataSource as SqlDataSource;
ColumnMeta filterColumnMeta = null;
if (!string.IsNullOrEmpty(columnMeta.FilterColumn))
{
filterColumnMeta = columnMeta.ReferenceTable.Columns.SingleOrDefault(c => c.Name.Equals(columnMeta.FilterColumn));
}
if (filterColumnMeta != null)
{
sqlDataSource.SelectCommand = SqlHelper.AddWhereConditionIfNotExists(sqlDataSource.SelectCommand, filterColumnMeta.Name);
bool found = false;
foreach (Parameter parameter in sqlDataSource.SelectParameters)
{
if (parameter.Name.Equals(filterColumnMeta.Name, StringComparison.InvariantCultureIgnoreCase))
{
found = true;
break;
}
}
if (!found)
{
sqlDataSource.SelectParameters.Add(new Parameter(filterColumnMeta.Name, filterColumnMeta.DataType, e.KeyValue.ToString()));
}
}
e.Editor.DataBind();
}
public FormLayoutCreator(TableMeta tableMeta, string connectionString, string[] idValues, bool readOnly)
{
this.tableMeta = tableMeta;
this.connectionString = connectionString;
this.idValues = idValues;
this.readOnly = readOnly;
}
public void CreateMultiAutoIncrementColumnTableTest_ShouldFailed()
{
var schema = new PrimaryKeySchema
{
{ "PK0", ColumnValueType.String },
{ "PK1", ColumnValueType.Integer, PrimaryKeyOption.AUTO_INCREMENT },
{ "PK2", ColumnValueType.Integer, PrimaryKeyOption.AUTO_INCREMENT }
};
var tableMeta = new TableMeta(TestTableName, schema);
var tableOptions = new TableOptions
{
MaxVersions = 10,
TimeToLive = -1
};
var reservedThroughput = new CapacityUnit(0, 0);
var request = new CreateTableRequest(tableMeta, reservedThroughput)
{
TableOptions = tableOptions
};
try{
OTSClient.CreateTable(request);
WaitForTableReady();
}catch (Exception e) {
Assert.IsTrue(e.Message.Contains("AUTO_INCREMENT primary key count must <= 1"));
}
}
private static string JoinOn(TableMeta metaA, string aliasA, TableMeta metaB, string aliasB)
{
var join = FindJoin(metaA, metaB);
if (join != null)
{
return(string.Format("{0}.{1} == {2}.{3}", aliasA, join.Name, aliasB, metaB.Columns.First(c => c.PrimaryKey).Name));
}
join = FindJoin(metaB, metaA);
if (join != null)
{
return(string.Format("{0}.{1} == {2}.{3}", aliasB, join.Name, aliasA, metaA.Columns.First(c => c.PrimaryKey).Name));
}
join = (from colA in metaA.Columns
join colB in metaB.Columns on colA.ParentTableType equals colB.ParentTableType
select colA).FirstOrDefault();
if (join != null)
{
return(string.Format("{0}.{1} == {2}.{3}", aliasA, join.Name, aliasB, metaA.Columns.First(c => c.ParentTableType == join.ParentTableType).Name));
}
throw new ArgumentException("There is no ForeignKey relationship between entities " + metaA.TableType.Name + " and " + metaB.TableType.Name);
}
internal static string SelectRowSql(TableMeta meta, string tableName)
{
return string.Format("SELECT {0} FROM {1} WHERE {2} ",
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}", col.Name)),
tableName,
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey), (sb, col) => sb.AppendFormat("{0} = {1}", col.Name, col.ParamName), " AND "));
}
public string GenerateDDL(Type tp, SchemaContext context)
{
// Fix type name => '.' to '_'
TableMeta tableMeta = new TableMeta(tp.Name.Replace('.', '_'), context);
// Loop through all obj properties
// Leaf -> Column
// Complex -> Build path / Cache mapping
PropertyInfo[] properties = tp.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo pi in properties)
{
Type unerLyingType = TypeInterrogator.GetUnderlyingType(pi.PropertyType);
//if (IsSkippedType(unerLyingType))
// return;
if (TypeInterrogator.IsSingleValueType(unerLyingType))
{
tableMeta.ColMetaList.Add(BuildColumnMeta(pi, unerLyingType));
}
//else if (TypeInterrogator.IsCollectionType(unerLyingType))
// BuildCollectionCtrl(pi, pi.PropertyType, typeObj);
//else
// BuildCompositeCtrl(unerLyingType, pi.GetValue(typeObj, null));
}
return(tableMeta.BuildCreateDDL());
}
internal static string SelectRowSql(TableMeta meta, string tableName)
{
return(string.Format("SELECT {0} FROM {1} WHERE {2} ",
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}", col.Name)),
tableName,
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey), (sb, col) => sb.AppendFormat("{0} = {1}", col.Name, col.ParamName), " AND ")));
}
public void TestNamedExpression()
{
var input = "table.a as name FROM";
var ctx = new Machine().Initialize();
var table = new TableMeta()
{
Name = "table"
};
Context.AddColumn(new ColumnMeta()
{
Name = "a", Type = ColumnType.Number
}, table);
ctx.AddTable(table);
var parser = new Parser();
var result = parser.ParseExpression(input, 0, ctx, new SelectStatement(), new Parser.ExpressionFlags()
{
IdentifierAllowed = true
}, true, false);
Assert.IsNotNull(result.Identifier);
Assert.AreEqual(result.Identifier, "name");
}
public void TestBasicColumnReference()
{
var ctx = new Machine().Initialize();
var table = new TableMeta()
{
Name = "table"
};
Context.AddColumn(new ColumnMeta()
{
Name = "a", Type = ColumnType.Number
}, table);
ctx.AddTable(table);
var input = "table.a";
var parser = new Parser();
var reference = parser.ParseReference(input, 0, ctx, new SelectStatement(), new Parser.ReferenceFlags()
{
ResolveReferences = true
});
var expected = new ColumnReference()
{
Column = "a", Table = "table", InputLength = input.Length
};
Assert.AreEqual(reference, expected);
}
private Insert(XContext context) : base(context)
{
this.tableMeta = TableMeta.From <T>();
this.tableName = this.tableMeta.TableName;
this.namedType = new NamedType(this.tableMeta.Type, this.tableName);
this.typeVisitor.Add(this.namedType);
}
public CreateTableRequest(TableMeta tableMeta, CapacityUnit reservedThroughput, List <IndexMeta> indexMetas)
{
TableMeta = tableMeta;
ReservedThroughput = reservedThroughput;
TableOptions = new TableOptions();
IndexMetas = indexMetas;
}
private static void PrepareTable()
{
// 创建表
OTSClient otsClient = Config.GetClient();
IList <string> tables = otsClient.ListTable(new ListTableRequest()).TableNames;
if (tables.Contains(TableName))
{
return;
}
PrimaryKeySchema primaryKeySchema = new PrimaryKeySchema
{
{ "pk0", ColumnValueType.Integer },
{ "pk1", ColumnValueType.String }
};
TableMeta tableMeta = new TableMeta(TableName, primaryKeySchema);
CapacityUnit reservedThroughput = new CapacityUnit(0, 0);
CreateTableRequest request = new CreateTableRequest(tableMeta, reservedThroughput);
otsClient.CreateTable(request);
}
/// <summary>
/// 设置数据文档的表
/// </summary>
/// <param name="document">文档</param>
/// <param name="table">当前表</param>
/// <param name="no">当前表编号,从1开始</param>
public static void SetTableWord(XWPFDocument document, TableMeta table, Int32 no)
{
//表名
XWPFParagraph p = document.CreateParagraph();
p.Alignment = ParagraphAlignment.LEFT;
XWPFRun r = p.CreateRun();
r.SetText($"{no}.{table.TableName}");
r.FontSize = 14;
r.IsBold = true;
if (!string.IsNullOrEmpty(table.Comment))
{
//表注释
p = document.CreateParagraph();
p.Alignment = ParagraphAlignment.LEFT;
r = p.CreateRun();
r.SetText(table.Comment);
r.FontSize = 14;
r.IsBold = true;
}
//表格
XWPFTable grid = document.CreateTable(table.Columns.Count + 1, 5);
grid.Width = 2500;
grid.SetColumnWidth(0, 256 * 2);
grid.SetColumnWidth(1, 256 * 2);
grid.SetColumnWidth(2, 256 * 1);
grid.SetColumnWidth(3, 256 * 1);
grid.SetColumnWidth(4, 256 * 4);
//设置表头
XWPFTableRow row = grid.GetRow(0);
row.GetCell(0).SetParagraph(SetCellText(document, grid, "字段名"));
row.GetCell(1).SetParagraph(SetCellText(document, grid, "类型"));
row.GetCell(2).SetParagraph(SetCellText(document, grid, "是否主键"));
row.GetCell(3).SetParagraph(SetCellText(document, grid, "可为空"));
row.GetCell(4).SetParagraph(SetCellText(document, grid, "说明"));
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnMeta col = table.Columns[i];
row = grid.GetRow(i + 1);
row.GetCell(0).SetParagraph(SetCellText(document, grid, col.ColumnName));
row.GetCell(1).SetParagraph(SetCellText(document, grid, col.FieldTypeName));
row.GetCell(2).SetParagraph(SetCellText(document, grid, col.IsKey ? "是" : "否"));
row.GetCell(3).SetParagraph(SetCellText(document, grid, col.AllowDBNull ? "是" : "否"));
row.GetCell(4).SetParagraph(SetCellText(document, grid, string.IsNullOrEmpty(col.Comment)?string.Empty:col.Comment));
}
}
/// <summary>
/// Obtains data source for look up type grid columns.
/// </summary>
/// <param name="tableMeta">Meta data about look up table.</param>
/// <param name="connectionString">Connection string to the underlying database.</param>
/// <returns>An instance of <see cref="SqlDataSource"/>.</returns>
internal static SqlDataSource GetLookUpDataSource(TableMeta tableMeta, string connectionString)
{
var ds = new SqlDataSource();
ds.ConnectionString = connectionString;
ds.SelectCommand = SqlHelper.GenerateSelectQuery(tableMeta);
return(ds);
}
public void Setup()
{
_object = new TableMeta(Some.Name);
_syntaxProviderMock = new Mock <ISyntaxProvider>();
_syntaxProviderMock.Setup(p => p.IsSeparator('.')).Returns(true);
_syntaxProviderMock.Setup(p => p.IsSeparator(' ')).Returns(true);
_evaluator = new Mock <ICSharpEvaluator>();
_parser = new DbToolSyntaxParser(_syntaxProviderMock.Object, _evaluator.Object);
}
public void TableMeta_ToString_should_match_name_property()
{
var tableMeta = new TableMeta();
tableMeta.Name = "MyTable";
tableMeta.SchemaName = "dbo";
tableMeta.Caption = "My Table";
Assert.AreEqual(tableMeta.Name, tableMeta.ToString());
}
public static void TableOperations()
{
// 创建表
OTSClient otsClient = Config.GetClient();
{
Console.WriteLine("Start create table...");
PrimaryKeySchema primaryKeySchema = new PrimaryKeySchema
{
{ "pk0", ColumnValueType.Integer },
{ "pk1", ColumnValueType.String }
};
TableMeta tableMeta = new TableMeta(TableName, primaryKeySchema);
CapacityUnit reservedThroughput = new CapacityUnit(0, 0);
CreateTableRequest request = new CreateTableRequest(tableMeta, reservedThroughput);
otsClient.CreateTable(request);
Console.WriteLine("Table is created: " + TableName);
}
//// 更新表
//{
// Thread.Sleep(60 * 1000); // 每次更新表需要至少间隔1分钟
// Console.WriteLine("Start update table...");
// CapacityUnit reservedThroughput = new CapacityUnit(0, 0); // 将预留CU调整为0，0
// UpdateTableRequest request = new UpdateTableRequest(TableName, reservedThroughput);
// UpdateTableResponse response = otsClient.UpdateTable(request);
// Console.WriteLine("LastIncreaseTime: " + response.ReservedThroughputDetails.LastIncreaseTime);
// Console.WriteLine("LastDecreaseTime: " + response.ReservedThroughputDetails.LastDecreaseTime);
// Console.WriteLine("NumberOfDecreaseToday: " + response.ReservedThroughputDetails.LastIncreaseTime);
// Console.WriteLine("ReadCapacity: " + response.ReservedThroughputDetails.CapacityUnit.Read);
// Console.WriteLine("WriteCapacity: " + response.ReservedThroughputDetails.CapacityUnit.Write);
//}
// 描述表
{
Console.WriteLine("Start describe table...");
DescribeTableRequest request = new DescribeTableRequest(TableName);
DescribeTableResponse response = otsClient.DescribeTable(request);
Console.WriteLine("LastIncreaseTime: " + response.ReservedThroughputDetails.LastIncreaseTime);
Console.WriteLine("LastDecreaseTime: " + response.ReservedThroughputDetails.LastDecreaseTime);
Console.WriteLine("NumberOfDecreaseToday: " + response.ReservedThroughputDetails.LastIncreaseTime);
Console.WriteLine("ReadCapacity: " + response.ReservedThroughputDetails.CapacityUnit.Read);
Console.WriteLine("WriteCapacity: " + response.ReservedThroughputDetails.CapacityUnit.Write);
}
//// 删除表
//{
// Console.WriteLine("Start delete table...");
// DeleteTableRequest request = new DeleteTableRequest(TableName);
// otsClient.DeleteTable(request);
// Console.WriteLine("Table is deleted.");
//}
}
internal static string CreateTable(TableMeta meta, string tableName)
{
if ( (meta.Columns.Count( col => col.PrimaryKey && col.AutoIncrement ) == 1) ||
(meta.Columns.Count( col => col.PrimaryKey) == 0))
return String.Format("CREATE TABLE if not exists {0} ({1}); ", tableName,
BuildColumnList( meta.Columns, (sb, col) => sb.AppendFormat( "{0} {1}", col.Name, col.SqlType ) ) );
return String.Format("CREATE TABLE if not exists {0} ({1}, PRIMARY KEY ({2})); ", tableName,
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0} {1}", col.Name, col.SqlType)),
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey) , (sb, col) => sb.AppendFormat("{0} ", col.Name)));
}
/// <summary>
/// Generates SELECT query for a <see cref="TableMeta"/>.
/// </summary>
/// <param name="tableMeta">Meta data of table being inspected.</param>
/// <returns>String, the SELECT query.</returns>
public static string GenerateSelectQuery(TableMeta tableMeta, bool byId = false)
{
string columnList = string.Join(", ", tableMeta.Columns.Select(m => string.Concat("[", m.Name, "]")));
if (byId)
{
return(string.Format("SELECT {0} FROM [{1}].[{2}] WHERE {3} ", columnList, tableMeta.SchemaName, tableMeta.Name,
GenerateWhereConditionForPrimaryKeys(tableMeta.PrimaryKeys)));
}
return(string.Format("SELECT {0} FROM [{1}].[{2}] ", columnList, tableMeta.SchemaName, tableMeta.Name));
}
/// <summary>
/// 构造一个查询命令
/// </summary>
/// <param name="context"></param>
internal Query(XContext context) : base(context)
{
this.tableMeta = TableMeta.From <T>();
this.tableName = this.tableMeta.TableName;
if (tableMeta.IsSimpleType())
{
throw Error.Exception("查询的实体类型不正确。");
}
this.namedType = new NamedType(this.tableMeta.Type, this.tableName);
this.typeVisitor.Add(this.namedType);
}
/// <summary>
/// Parameterized constructor of <see cref="DetailGridTemplate"/>.
/// </summary>
/// <param name="masterTableMeta">Meta data of master table.</param>
/// <param name="connectionString">The connection string to underlying database.</param>
public DetailGridTemplate(TableMeta masterTableMeta, string connectionString, List <PermissionType> permissions)
{
this.masterTableMeta = masterTableMeta;
if (masterTableMeta.Children.Where(c => c.IsRendered).Count() != 1)
{
throw new ArgumentException(string.Format("Master table {0} has no child table or more than 1 child tables.", masterTableMeta.Name));
}
this.detailTable = masterTableMeta.Children[0];
this.connectionString = connectionString;
this.permissions = permissions;
}
public void CreateTestTable(string tableName, PrimaryKeySchema schema, CapacityUnit reservedThroughput, bool waitFlag = true)
{
var tableMeta = new TableMeta(tableName, schema);
var request = new CreateTableRequest(tableMeta, reservedThroughput);
OTSClient.CreateTable(request);
if (waitFlag)
{
WaitForTableReady();
}
}
protected TableMeta BuildTable(TableDef tableDefTmp,
ref ArrayList indexList,
ref ArrayList primaryKeyList,
ref ArrayList uniqkeyList,
ref ArrayList foreignKeyList,
ref StringBuilder strRowDataSql,
ref ArrayList colAlterList)
{
DDLActionEnum tblAction = ValidateVersion(tableDefTmp);
if (tblAction == DDLActionEnum.NONE) // not in range!
{
return(null);
}
TableMeta tableMetaTmp = new TableMeta(tableDefTmp.Name, _context);
ColumnCollection columns = tableDefTmp.Columns;
foreach (Column col in columns)
{
ColumnMeta colMeta = new ColumnMeta(col.ColumnName, col.DataType, _dtMap);
colMeta.Default = col.ExplicitDefaultValue;
colMeta.Nullable = col.IsNullable; // more...
colMeta.DataType.StringLength = col.MaxStringLength;
if (tblAction == DDLActionEnum.MODIFY)
{
DDLActionEnum colAlterAction = ValidateVersion(col);
if (colAlterAction != DDLActionEnum.NONE)
{
ColumnAlter colAlterTmp = new ColumnAlter(tableMetaTmp, colMeta);
colAlterTmp.AlterAction = (int)colAlterAction;
colAlterList.Add(colAlterTmp);
}
}
tableMetaTmp.ColMetaList.Add(colMeta);
}
BuildIndexAndConstraints(tableDefTmp, tableMetaTmp, ref indexList,
ref primaryKeyList, ref uniqkeyList, ref foreignKeyList);
strRowDataSql.Append(TableRowSQLBuilder.BuildInsertLine(tableDefTmp.TableRows, tableMetaTmp, _LowVersion, _HighVersion));
if (tblAction == DDLActionEnum.MODIFY) // do not build a new table
{
tableMetaTmp = null;
}
return(tableMetaTmp);
}
internal static string CreateTable(TableMeta meta, string tableName)
{
if ((meta.Columns.Count(col => col.PrimaryKey && col.AutoIncrement) == 1) ||
(meta.Columns.Count(col => col.PrimaryKey) == 0))
{
return(String.Format("CREATE TABLE if not exists {0} ({1}); ", tableName,
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0} {1}", col.Name, col.SqlType))));
}
return(String.Format("CREATE TABLE if not exists {0} ({1}, PRIMARY KEY ({2})); ", tableName,
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0} {1}", col.Name, col.SqlType)),
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey), (sb, col) => sb.AppendFormat("{0} ", col.Name))));
}
public void TableMeta_primary_keys_defined()
{
var tableMeta = new TableMeta();
tableMeta.Name = "MyTable";
tableMeta.SchemaName = "dbo";
tableMeta.Caption = "My Table";
tableMeta.Columns.Add(new ColumnMeta()
{
Name = "Id", OrderNo = 1, DataType = TypeCode.Int32, IsPrimaryKey = true, IsRequired = true, Table = tableMeta
}
);
Assert.IsTrue(tableMeta.PrimaryKeys.Length == 1);
}
public void CreateTestTable()
{
var primaryKeys = new PrimaryKeySchema();
primaryKeys.Add("PK0", ColumnValueType.String);
primaryKeys.Add("PK1", ColumnValueType.Integer);
var tableMeta = new TableMeta("SampleTableName", primaryKeys);
var reservedThroughput = new CapacityUnit(0, 0);
var request = new CreateTableRequest(tableMeta, reservedThroughput);
OTSClient.CreateTable(request);
WaitForTableReady();
}
public Type CreateType(TableMeta table)
{
var builder = DynamicAssembly.BuildClass(table.Name)
.WithAttribute(() => new DbTableAttribute(table.Name))
.WithAttribute <SerializableAttribute>();
foreach (var column in table.Columns)
{
builder.AddProperty(column.Name, column.CSharpType);
}
var type = builder.CreateType();
return(type);
}
/// <summary>
/// Parameterized-constructor for <see cref="DetailGridCreator"/>.
/// </summary>
/// <param name="detailTable">An instance of <see cref="TableMetaRelation"/>, provides meta-data about relation
/// between master and detail tables.</param>
/// <param name="masterTableMeta">Master table meta data, an instance of <see cref="TableMeta"/>.</param>
/// <param name="masterKey">The primary key of master table.</param>
/// <param name="connectionString">Connection string to underlying database.</param>
/// <param name="permissions"></param>
public DetailGridCreator(TableMetaRelation detailTable, TableMeta masterTableMeta, object masterKey, string connectionString,
List <PermissionType> permissions)
{
this.detailTableMeta = detailTable.Child;
this.masterTableMeta = masterTableMeta;
this.masterKey = masterKey;
this.connectionString = connectionString;
this.gridId = string.Concat(detailTableMeta.Name.ToCamelCase(), "GridView");
this.foreignKey = detailTableMeta.Columns.Where(c => c.IsForeignKey == true && c.Name == detailTable.ForeignKeyName)
.SingleOrDefault();
if (foreignKey == null)
{
throw new ArgumentException(string.Format("FK to table {0} not found", masterTableMeta.Name));
}
this.permissions = permissions;
}
private void CreateTable()
{
var otsClient = OTSClient;
var primaryKeySchema = new PrimaryKeySchema();
primaryKeySchema.Add("PK0", ColumnValueType.String);
primaryKeySchema.Add("PK1", ColumnValueType.Integer);
var tableMeta = new TableMeta("SampleTable", primaryKeySchema);
var reservedThroughput = new CapacityUnit(0, 0);
var request = new CreateTableRequest(tableMeta, reservedThroughput);
var response = otsClient.CreateTable(request);
WaitForTableReady();
}
private void CreateTable(String tableName)
{
foreach (var tableItem in OTSClient.ListTable(new ListTableRequest()).TableNames)
{
OTSClient.DeleteTable(new DeleteTableRequest(tableItem));
}
var primaryKeySchema = new PrimaryKeySchema();
primaryKeySchema.Add("PK0", ColumnValueType.Integer);
var tableMeta = new TableMeta(tableName, primaryKeySchema);
var reservedThroughput = new CapacityUnit(0, 0);
var request = new CreateTableRequest(tableMeta, reservedThroughput);
var response = OTSClient.CreateTable(request);
WaitForTableReady();
}
public void CreateTableAndDelete()
{
string tableName = "SampleTableName";
var primaryKeys = new PrimaryKeySchema
{
{ "PK0", ColumnValueType.String },
{ "PK1", ColumnValueType.Integer }
};
var tableOption = new TableOptions
{
MaxVersions = 1,
TimeToLive = -1
};
var tableMeta = new TableMeta(tableName, primaryKeys);
var reservedThroughput = new CapacityUnit(0, 0);
var request1 = new CreateTableRequest(tableMeta, reservedThroughput)
{
TableOptions = tableOption
};
var response1 = OTSClient.CreateTable(request1);
var request2 = new ListTableRequest();
var response2 = OTSClient.ListTable(request2);
Assert.IsTrue(response2.TableNames.Contains(tableName));
Thread.Sleep(1000);
var request3 = new DescribeTableRequest(tableName);
var response3 = OTSClient.DescribeTable(request3);
Assert.AreEqual(tableName, response3.TableMeta.TableName);
Assert.AreEqual(primaryKeys, response3.TableMeta.PrimaryKeySchema);
Assert.AreEqual(reservedThroughput.Read, response3.ReservedThroughputDetails.CapacityUnit.Read);
Assert.AreEqual(reservedThroughput.Write, response3.ReservedThroughputDetails.CapacityUnit.Write);
OTSClient.DeleteTable(new DeleteTableRequest(tableName));
var request4 = new ListTableRequest();
var response4 = OTSClient.ListTable(request4);
Assert.IsFalse(response4.TableNames.Contains(tableName));
}
internal static string InsertSql(TableMeta meta, string tableName)
{
return string.Format("INSERT INTO {0} ({1}) VALUES ({2}); ", tableName,
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}", col.Name)),
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}", col.ParamName)));
}
/// <summary>
/// Find ForeignKey in Meta-B which relates to Meta-A
/// </summary>
/// <param name="metaA"></param>
/// <param name="metaB"></param>
/// <returns></returns>
private static TableColumn FindJoin(TableMeta metaA, TableMeta metaB)
{
return metaA.Columns.FirstOrDefault(t => t.IsForeignKey && t.ParentTableType == metaB.TableType);
}
private static string JoinOn(TableMeta metaA, string aliasA, TableMeta metaB, string aliasB)
{
var join = FindJoin(metaA, metaB);
if (join != null)
return string.Format("{0}.{1} == {2}.{3}", aliasA, join.Name, aliasB, metaB.Columns.First(c => c.PrimaryKey).Name);
join = FindJoin(metaB, metaA);
if (join != null)
return string.Format("{0}.{1} == {2}.{3}", aliasB, join.Name, aliasA, metaA.Columns.First(c => c.PrimaryKey).Name);
join = (from colA in metaA.Columns
join colB in metaB.Columns on colA.ParentTableType equals colB.ParentTableType
select colA).FirstOrDefault();
if (join != null)
return string.Format("{0}.{1} == {2}.{3}", aliasA, join.Name, aliasB, metaA.Columns.First(c => c.ParentTableType == join.ParentTableType ).Name);
throw new ArgumentException( "There is no ForeignKey relationship between entities " + metaA.TableType.Name + " and " + metaB.TableType.Name );
}
internal static string ColumnsWithAlias(TableMeta meta, string alias)
{
return BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}.{1}", alias, col.Name));
}
public void DropHtmlObjectInDataBase(ref List<Html> htmlObjects)
{
int total = htmlObjects.Count;
for (int c = 0; c < total; c++)
{
//----TableLink----
m_tableLink = new TableLink { Link = htmlObjects[0].Link, Title = htmlObjects[0].Title, VisiteDate = htmlObjects[0].VisiteDate };
m_dataBase.TableLink.InsertOnSubmit(m_tableLink);
m_dataBase.SubmitChanges();
// breakpoint
string link = htmlObjects[0].Link;
var req = m_dataBase.TableLink.Where(l => l.Link == link).Take(1);
int linkId = req.First().Id;
//----TableH1----
for (int i = 0; i < htmlObjects[0].ListH1.Count; i++)
{
m_tableH1 = new TableH1 { IdLink = linkId, InnerText = htmlObjects[0].ListH1[i] };
m_dataBase.TableH1.InsertOnSubmit(m_tableH1);
}
//----TableH2----
for (int i = 0; i < htmlObjects[0].ListH2.Count; i++)
{
m_tableH2 = new TableH2 { IdLink = linkId, InnerText = htmlObjects[0].ListH2[i] };
m_dataBase.TableH2.InsertOnSubmit(m_tableH2);
}
//----TableMeta----
for (int i = 0; i < htmlObjects[0].ListMeta.Count; i++)
{
m_tableMeta = new TableMeta { IdLink = linkId, InnerText = htmlObjects[0].ListMeta[i].Content, Name = htmlObjects[0].ListMeta[i].Name };
m_dataBase.TableMeta.InsertOnSubmit(m_tableMeta);
}
//----TableEmail----
for (int i = 0; i < htmlObjects[0].Email.Count; i++)
{
m_tableEmail = new TableEmail { IdLink = linkId, Address = htmlObjects[0].Email[i] };
m_dataBase.TableEmail.InsertOnSubmit(m_tableEmail);
}
htmlObjects.RemoveAt(0);
}
WebLink.LinkVisited.Clear();
htmlObjects.Clear();
m_dataBase.SubmitChanges();
}
internal static string DeleteRowSql(TableMeta meta, string tableName)
{
return string.Format("DELETE FROM {0} WHERE {1}", tableName,
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey), (sb, col) => sb.AppendFormat("{0} = {1}", col.Name, col.ParamName), " AND "));
}
internal static string DeleteSql(TableMeta meta, string tableName)
{
return string.Format("DELETE FROM {0}", tableName);
}
internal static string UpdateSql(TableMeta meta, string tableName)
{
List<TableColumn> columns = new List<TableColumn>();
foreach (TableColumn item in meta.Columns)
{
if (!item.PrimaryKey)
columns.Add(item);
}
return string.Format("UPDATE {0} SET {1} WHERE {2};", tableName,
BuildColumnList(columns,
(sb, col) =>
{
if (!col.PrimaryKey)
sb.AppendFormat("{0} = {1}", col.Name, col.ParamName);
}),
BuildColumnList(meta.Columns.Where(col => col.PrimaryKey), (sb, col) => sb.AppendFormat("{0} = {1}", col.Name, col.ParamName), " AND "));
}
internal static string SelectAllSql(TableMeta meta, string tableName)
{
return string.Format("SELECT {0} FROM {1}",
BuildColumnList(meta.Columns, (sb, col) => sb.AppendFormat("{0}", col.Name)),
tableName);
}
