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 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
};
NpgQuery 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);
}
public NpgTableInfo[] GetTables(string[] script)
{
var tables = new List <NpgTableInfo>();
var constraints = new List <NpgScriptConstraint>();
foreach (string row in script)
{
NpgTableInfo table = GetTable(row);
if (table != null)
{
tables.Add(table);
}
else
{
NpgScriptConstraint constraint = GetConstraint(row);
if (constraint != null)
{
constraints.Add(constraint);
}
}
}
for (var i = 0; i < tables.Count; i++)
{
var breakCycle = true;
for (var tableIndex = 0; tableIndex < tables.Count; tableIndex++)
{
NpgTableInfo table = tables[tableIndex];
NpgScriptConstraint constraint =
constraints.FirstOrDefault(c => c.TableName == table.Name);
if (constraint != null)
{
NpgTableInfo referencedTable =
tables.FirstOrDefault(t => t.Name == constraint.ReferencedTable);
if (referencedTable != null)
{
int refTableIndex = tables.IndexOf(referencedTable);
if (refTableIndex > tableIndex)
{
tables.RemoveAt(refTableIndex);
tables.Insert(tableIndex, referencedTable);
breakCycle = false;
}
}
}
}
if (breakCycle)
{
break;
}
}
return(tables.ToArray());
}
public void GetTableTest()
{
NpgTableInfo result = _helper.GetTable(
@"create table alarm_tags (
id serial not null,
name text not null,
ref_dig_tags int4 not null
constraint pk_alarm_tags primary key (id)
);");
Assert.AreEqual("alarm_tags", result.Name);
Assert.AreEqual(new[] { "id" }, result.PrimaryKey);
Assert.AreEqual(3, result.Columns.Length);
}
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 object[][] GetData(
NpgsqlConnection connection, NpgTableInfo table, KeyValuePair <string, object>[] startParameters)
{
Func <NpgsqlDataReader, object[]> convertFunc = reader => {
var values = new object[table.Columns.Length];
for (var i = 0; i < table.Columns.Length; i++)
{
values[i] = reader.GetValue(i);
}
return(values);
};
NpgQuery scriptItem = _service.ExImHelper.GetSelectScriptItem(table, MaxRows, startParameters);
var result = _service.NpgHelper.ExecuteReader(connection, scriptItem, convertFunc);
return(result);
}
public NpgQuery GetSelectScriptItem(NpgTableInfo tableInfo, int maxRows, params KeyValuePair <string, object>[] startParameters)
{
var scriptParameters =
startParameters.Any()
? " WHERE " + string.Join(" AND ", startParameters.Select(p => string.Format("\"{0}\" > :{0}", p.Key)))
: string.Empty;
var scriptRow = string.Format(
"SELECT {0} FROM {1}{2} ORDER BY {3} LIMIT {4}",
string.Join(", ", tableInfo.Columns.Select(c => string.Format("\"{0}\"", c.Name))),
string.Format("\"{0}\"", tableInfo.Name),
scriptParameters,
string.Join(", ", tableInfo.PrimaryKey.Select(p => string.Format("\"{0}\"", p))),
maxRows);
var parameters =
startParameters.Any()
? startParameters.Select(p => new NpgsqlParameter(p.Key, p.Value)).ToArray()
: null;
return(new NpgQuery(scriptRow, parameters));
}
public static SerialHelper Create(NpgTableInfo[] tableInfos)
{
var helper = new SerialHelper();
foreach (var tableInfo in tableInfos)
{
foreach (var columnInfo in tableInfo.Columns)
{
if (columnInfo.DataType == NpgDataType.Serial
|| columnInfo.DataType == NpgDataType.Bigserial)
{
if (!helper.Items.ContainsKey(tableInfo.Name))
helper.Items.Add(tableInfo.Name, new Dictionary<string, SerialHelperItem>());
helper.Items[tableInfo.Name].Add(columnInfo.Name, new SerialHelperItem
{
Table = tableInfo.Name,
Column = columnInfo.Name
});
}
}
}
return helper;
}
public NpgQuery GetSelectScriptItem(NpgTableInfo tableInfo, int maxRows, params KeyValuePair<string, object>[] startParameters)
{
var scriptParameters =
startParameters.Any()
? " WHERE " + string.Join(" AND ", startParameters.Select(p => string.Format("\"{0}\" > :{0}", p.Key)))
: string.Empty;
var scriptRow = string.Format(
"SELECT {0} FROM {1}{2} ORDER BY {3} LIMIT {4}",
string.Join(", ", tableInfo.Columns.Select(c => string.Format("\"{0}\"", c.Name))),
string.Format("\"{0}\"", tableInfo.Name),
scriptParameters,
string.Join(", ", tableInfo.PrimaryKey.Select(p => string.Format("\"{0}\"", p))),
maxRows);
var parameters =
startParameters.Any()
? startParameters.Select(p => new NpgsqlParameter(p.Key, p.Value)).ToArray()
: null;
return new NpgQuery(scriptRow, parameters);
}
public void GetTableReturnsNullIfItIsNotTable()
{
NpgTableInfo result = _helper.GetTable(string.Empty);
Assert.That(result, Is.Null);
}