public NpgQuery CreateTable(NpgTableInfo tableInfo)
{
var text = new StringBuilder();
text.AppendFormat("CREATE TABLE {0} (", tableInfo.Name);
foreach (var columnInfo in tableInfo.Columns)
{
text.AppendFormat("{0} {1} {2},",
columnInfo.Name,
columnInfo.DataType,
columnInfo.NotNull ? "NOT NULL" : "NULL");
}
text.AppendFormat("CONSTRAINT pk_{0} PRIMARY KEY ({1})",
tableInfo.Name,
string.Join(", ", tableInfo.PrimaryKey));
text.AppendFormat(")");
return new NpgQuery(text.ToString());
}
public NpgQuery CreateTable(NpgTableInfo tableInfo)
{
var text = new StringBuilder();
text.AppendFormat("CREATE TABLE {0} (", tableInfo.Name);
foreach (var columnInfo in tableInfo.Columns)
{
text.AppendFormat("{0} {1} {2},",
columnInfo.Name,
columnInfo.DataType,
columnInfo.NotNull ? "NOT NULL" : "NULL");
}
text.AppendFormat("CONSTRAINT pk_{0} PRIMARY KEY ({1})",
tableInfo.Name,
string.Join(", ", tableInfo.PrimaryKey));
text.AppendFormat(")");
return(new NpgQuery(text.ToString()));
}
public void GetPrimaryKeyIndexesTest()
{
var table = new NpgTableInfo
{
Columns = new[]
{
new NpgColumnInfo {Name = "column0"},
new NpgColumnInfo {Name = "column1"},
new NpgColumnInfo {Name = "column2"},
new NpgColumnInfo {Name = "column3"},
new NpgColumnInfo {Name = "column4"},
},
PrimaryKey = new []
{
"column3",
"column1"
}
};
var result = table.GetPrimaryKeyIndexes();
Assert.AreEqual(2, result.Length);
Assert.AreEqual(3, result[0]);
Assert.AreEqual(1, result[1]);
}
public void GetSelectScriptItemTestWithParameters()
{
var columns = new[]
{
new NpgColumnInfo
{
Name = "id",
DataType = NpgDataType.Serial,
NotNull = true
},
new NpgColumnInfo
{
Name = "name",
DataType = NpgDataType.Text,
NotNull = false
},
new NpgColumnInfo
{
Name = "data",
DataType = NpgDataType.Int4,
NotNull = false
}
};
var primaryKey = new[]
{
"id", "name"
};
var tableInfo = new NpgTableInfo
{
Name = "tableName",
Columns = columns,
PrimaryKey = primaryKey
};
var result = _helper.GetSelectScriptItem(
tableInfo,
1000,
new KeyValuePair<string, object>("id", 10),
new KeyValuePair<string, object>("name", "abc"));
Assert.AreEqual(
"SELECT \"id\", \"name\", \"data\" FROM \"tableName\" WHERE \"id\" > :id AND \"name\" > :name ORDER BY \"id\", \"name\" LIMIT 1000",
result.Text);
Assert.AreEqual(2, result.Parameters.Length);
Assert.AreEqual("id", result.Parameters[0].ParameterName);
Assert.AreEqual(10, result.Parameters[0].Value);
Assert.AreEqual("name", result.Parameters[1].ParameterName);
Assert.AreEqual("abc", result.Parameters[1].Value);
}
public void GetSelectScriptItemTest()
{
var columns = new[]
{
new NpgColumnInfo
{
Name = "id",
DataType = NpgDataType.Serial,
NotNull = true
},
new NpgColumnInfo
{
Name = "name",
DataType = NpgDataType.Text,
NotNull = false
},
new NpgColumnInfo
{
Name = "data",
DataType = NpgDataType.Int4,
NotNull = false
}
};
var primaryKey = new[]
{
"id", "name"
};
var tableInfo = new NpgTableInfo
{
Name = "tableName",
Columns = columns,
PrimaryKey = primaryKey
};
var result = _helper.GetSelectScriptItem(tableInfo, 1000);
Assert.AreEqual(
"SELECT \"id\", \"name\", \"data\" FROM \"tableName\" ORDER BY \"id\", \"name\" LIMIT 1000",
result.Text);
Assert.IsEmpty(result.Parameters);
}