public void GetInsertScriptItemMultipleRowsTest()
{
var tableData = new TableData {
TableName = "Table",
Columns = new[] { "id", "name", "data" },
Data = new[] {
new object[] { 1, "name1", "data1" },
new object[] { 2, "name2", "data2" }
}
};
NpgQuery result = _helper.GetInsertScriptItem(tableData);
Assert.AreEqual(
"INSERT INTO \"Table\" (\"id\", \"name\", \"data\") VALUES (:id0, :name0, :data0), (:id1, :name1, :data1)",
result.Text);
Assert.AreEqual(6, result.Parameters.Length);
Assert.AreEqual("id0", result.Parameters[0].ParameterName);
Assert.AreEqual(1, result.Parameters[0].Value);
Assert.AreEqual("name0", result.Parameters[1].ParameterName);
Assert.AreEqual("name1", result.Parameters[1].Value);
Assert.AreEqual("data0", result.Parameters[2].ParameterName);
Assert.AreEqual("data1", result.Parameters[2].Value);
Assert.AreEqual("id1", result.Parameters[3].ParameterName);
Assert.AreEqual(2, result.Parameters[3].Value);
Assert.AreEqual("name1", result.Parameters[4].ParameterName);
Assert.AreEqual("name2", result.Parameters[4].Value);
Assert.AreEqual("data1", result.Parameters[5].ParameterName);
Assert.AreEqual("data2", result.Parameters[5].Value);
}
private void EnsureTable(int ordinal, IEnumerable <string> tables)
{
string tableName = GetTableName(ordinal);
if (!tables.Contains(tableName))
{
var tb = new NpgTableInfoBuilder(tableName);
tb.AddColumn(DbStr.Time, NpgDataType.Int8, true);
tb.AddPrimaryKey(DbStr.Time);
NpgQuery createTableQuery = _npgQueryHelper.CreateTable(tb.Build());
_npgHelper.ExecuteScript(_connection, new[] { createTableQuery });
}
var columns = _npgHelper.GetColumns(_connection, tableName);
foreach (int trendTagId in _trendTagIds)
{
string column = GetColumnName(trendTagId);
if (columns.Contains(column))
{
continue;
}
NpgQuery createColumnQuery = _npgQueryHelper.CreateColumn(
tableName,
new NpgColumnInfo {
Name = column,
DataType = NpgDataType.Float8,
NotNull = false
});
_npgHelper.ExecuteNonQuery(_connection, createColumnQuery);
}
}
public void EnsureTable(NpgsqlConnection connection)
{
var tables = _npgHelper.GetTables(connection);
if (tables.Contains(DbStr.Users))
{
return;
}
var tb = new NpgTableInfoBuilder(DbStr.Users);
tb.AddColumn(DbStr.Id, NpgDataType.Int8, true);
tb.AddColumn(DbStr.Name, NpgDataType.Text, true);
tb.AddColumn(DbStr.Description, NpgDataType.Text, true);
tb.AddColumn(DbStr.Photo, NpgDataType.Bytea);
tb.AddColumn(DbStr.Password, NpgDataType.Text);
tb.AddColumn(DbStr.Enabled, NpgDataType.Bool, true);
tb.AddColumn(DbStr.CanChange, NpgDataType.Bool, true);
tb.AddColumn(DbStr.Privilege, NpgDataType.Int4);
tb.AddPrimaryKey(DbStr.Id);
NpgQuery createUsersTableQuery = _npgQueryHelper.CreateTable(tb.Build());
NpgQuery createNameIndexQuery =
_npgQueryHelper.CreateIndex(DbStr.Users, unique: true, columns: DbStr.Name);
_npgHelper.ExecuteScript(connection, new[] { createUsersTableQuery, createNameIndexQuery });
}
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 bool SetPassword(NpgsqlConnection connection, long id, string newPassword)
{
NpgQuery query = _npgQueryHelper.UpdateWhere(
DbStr.Users, new Eq(DbStr.Id, id), new[] { DbStr.Password }, new object[] { newPassword });
int result = _npgHelper.ExecuteNonQuery(connection, query);
return(result == 1);
}
public void Delete(NpgsqlConnection connection, DateTime time)
{
NpgQuery query = _npgQueryHelper.DeleteWhere(
_tableName,
new Eq(DbStr.Time, time.Ticks));
_npgHelper.ExecuteNonQuery(connection, query);
}
public User[] GetByNameAndPassword(NpgsqlConnection connection, string name, string password)
{
NpgQuery query = _npgQueryHelper.Select(
DbStr.Users, _userColumns,
new And(new Eq(DbStr.Name, name), new Eq(DbStr.Password, password)));
var users = _npgHelper.ExecuteReader(connection, query, GetUser);
return(users);
}
public bool Update(NpgsqlConnection connection, LogItem item)
{
NpgQuery query = _npgQueryHelper.UpdateWhere(
_tableName,
new Eq(DbStr.Time, item.Time.Ticks),
new[] { DbStr.Text, DbStr.Bytes },
new object[] { item.Text, item.Bytes });
return(_npgHelper.ExecuteNonQuery(connection, query) == 1);
}
public bool IsPHmiDatabase(string connectionString, string database)
{
var connectionParameters = new NpgConnectionParameters();
connectionParameters.Update(connectionString);
connectionParameters.Database = database;
var query = new NpgQuery(string.Format("SELECT 1 FROM settings WHERE phmi_guid = '{0}'", PHmiConstants.PHmiGuid));
var result = _npgHelper.ExecuteScalar(connectionParameters.ConnectionString, query);
return(result != null);
}
public void GetInsertScriptItemEmptyTest()
{
var tableData = new TableData {
TableName = "Table",
Columns = new[] { "id", "name", "data" },
Data = new object[0][]
};
NpgQuery result = _helper.GetInsertScriptItem(tableData);
Assert.IsNull(result);
}
public Tuple <int?, string, string> GetPrivilegeAndNameAndPassword(NpgsqlConnection connection,
long id)
{
NpgQuery query = _npgQueryHelper.Select(
DbStr.Users, new[] { DbStr.Privilege, DbStr.Name, DbStr.Password }, new Eq(DbStr.Id, id));
var cridentials = _npgHelper.ExecuteReader(connection, query,
reader => new Tuple <int?, string, string>(reader.GetNullableInt32(0), reader.GetString(1),
reader.GetNullableString(2)));
return(cridentials.SingleOrDefault());
}
public Tuple <long, string>[] GetIds(NpgsqlConnection connection, long minId, long maxId)
{
NpgQuery getRepoUsersIdsQuery = _npgQueryHelper
.Select(DbStr.Users, new[] { DbStr.Id, DbStr.Name },
new And(new Ge(DbStr.Id, minId), new Le(DbStr.Id, maxId)));
var repoUsersIds = _npgHelper.ExecuteReader(
connection,
getRepoUsersIdsQuery,
reader => new Tuple <long, string>(reader.GetInt64(0), reader.GetString(1)));
return(repoUsersIds);
}
public void Insert(NpgsqlConnection connection, LogItem item)
{
var values = new object[] {
item.Time.Ticks,
item.Text,
item.Bytes
};
NpgQuery query = _npgQueryHelper.Insert(
_tableName,
_columns,
new[] { values });
_npgHelper.ExecuteNonQuery(connection, query);
}
public Tuple <DateTime, double?[]>[] GetSamples(int[] trendTagIds, DateTime startTime,
DateTime?endTime, int rarerer)
{
var columns = new List <string>(trendTagIds.Length + 1)
{
DbStr.Time
};
columns.AddRange(trendTagIds.Select(GetColumnName));
var columnsArr = columns.ToArray();
var parameters = new List <NpgsqlParameter>();
var queryTexts = new List <string>();
for (var i = 0; i < TrendTableSelector.TablesCount; i++)
{
if (rarerer != 0 && rarerer != i + 1)
{
continue;
}
string tableName = GetTableName(i);
IWhereOp whereOp = new Ge(DbStr.Time, startTime.Ticks);
if (endTime.HasValue)
{
whereOp = new And(whereOp, new Le(DbStr.Time, endTime.Value.Ticks));
}
string queryText = _npgQueryHelper.Select(
parameters,
tableName,
columnsArr,
whereOp,
limit: MaxSamplesToRetrieve);
queryTexts.Add(queryText);
}
NpgQuery query = queryTexts.Count == 1
? new NpgQuery(queryTexts[0], parameters.ToArray())
: _npgQueryHelper.Union(parameters, queryTexts, ColumnsOfTime, true, MaxSamplesToRetrieve);
return(_npgHelper.ExecuteReader(_connection, query, reader => {
DateTime time = reader.GetDateTimeFormTicks(0);
var values = new double?[columns.Count - 1];
for (var j = 1; j < columns.Count; j++)
{
values[j - 1] = reader.GetNullableDouble(j);
}
return new Tuple <DateTime, double?[]>(time, values);
}));
}
public User[] Get(NpgsqlConnection connection, CriteriaType criteriaType, string name, int count)
{
IWhereOp whereOp;
bool asc;
switch (criteriaType)
{
case CriteriaType.DownFromInfinity:
whereOp = null;
asc = true;
break;
case CriteriaType.DownFrom:
whereOp = new Gt(DbStr.Name, name);
asc = true;
break;
case CriteriaType.DownFromOrEqual:
whereOp = new Ge(DbStr.Name, name);
asc = true;
break;
case CriteriaType.UpFromInfinity:
whereOp = null;
asc = false;
break;
case CriteriaType.UpFrom:
whereOp = new Lt(DbStr.Name, name);
asc = false;
break;
case CriteriaType.UpFromOrEqual:
whereOp = new Le(DbStr.Name, name);
asc = false;
break;
default:
throw new NotSupportedException("CriteriaType " + criteriaType);
}
int limit = Math.Min(count, MaxUsersToRetrieve);
NpgQuery query = _npgQueryHelper.Select(
DbStr.Users, _userColumns, whereOp, new[] { DbStr.Name }, asc, limit);
var users = _npgHelper.ExecuteReader(connection, query, GetUser);
return(asc ? users : users.Reverse().ToArray());
}
public void DeleteOld(NpgsqlConnection connection, DateTime oldStartTime)
{
NpgQuery selectQuery = _npgQueryHelper.Select(
_tableName,
new[] { DbStr.Time },
new Le(DbStr.Time, oldStartTime.Ticks),
new[] { DbStr.Time },
true,
MaxItemsToDeletePerTime,
true);
NpgQuery query = _npgQueryHelper.DeleteWhere(
_tableName,
new In(DbStr.Time, selectQuery));
_npgHelper.ExecuteNonQuery(connection, query);
}
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 void EnsureTable(NpgsqlConnection connection)
{
var tables = _npgHelper.GetTables(connection);
if (tables.Contains(_tableName))
{
return;
}
var tb = new NpgTableInfoBuilder(_tableName);
tb.AddColumn(DbStr.Time, NpgDataType.Int8, true);
tb.AddColumn(DbStr.Text, NpgDataType.Text);
tb.AddColumn(DbStr.Bytes, NpgDataType.Bytea);
tb.AddPrimaryKey(DbStr.Time);
NpgQuery createTableQuery = _npgQueryHelper.CreateTable(tb.Build());
_npgHelper.ExecuteScript(connection, new[] { createTableQuery });
}
public void DeleteOld(DateTime oldTime)
{
for (var i = 0; i < TrendTableSelector.TablesCount; i++)
{
string tableName = GetTableName(i);
NpgQuery selectQuery = _npgQueryHelper.Select(
tableName,
ColumnsOfTime,
new Le(DbStr.Time, oldTime.Ticks),
ColumnsOfTime,
true,
MaxSamplesToDeletePerTime,
true);
NpgQuery query = _npgQueryHelper.DeleteWhere(
tableName,
new In(DbStr.Time, selectQuery));
_npgHelper.ExecuteNonQuery(_connection, query);
}
}
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
};
NpgQuery 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 Insert(DateTime time, int tableIndex, Tuple <int, double>[] samples)
{
string tableName = GetTableName(tableIndex);
var columns = new string[samples.Length + 1];
columns[0] = DbStr.Time;
for (var i = 0; i < samples.Length; i++)
{
columns[i + 1] = GetColumnName(samples[i].Item1);
}
var values = new object[samples.Length + 1];
values[0] = time.Ticks;
for (var i = 0; i < samples.Length; i++)
{
values[i + 1] = samples[i].Item2;
}
NpgQuery query = _npgQueryHelper.Insert(tableName, columns, new[] { values });
_npgHelper.ExecuteNonQuery(_connection, query);
}
public User[] Get(NpgsqlConnection connection, long[] ids)
{
NpgQuery query = _npgQueryHelper.Select(DbStr.Users, _userColumns, new In(DbStr.Id, ids));
return(_npgHelper.ExecuteReader(connection, query, GetUser));
}
public In(string column, NpgQuery selectQuery)
{
_column = column;
_selectQuery = selectQuery;
}
public In(string column, NpgQuery selectQuery)
{
_column = column;
_selectQuery = selectQuery;
}
protected override void OkCommandExecuted(object obj)
{
InProgress = true;
ProgressIsIndeterminate = true;
var script = _service.ScriptHelper.ExtractScriptLines(PHmiModelContext.GetPHmiScriptStream());
Progress = 0;
ProgressMax = script.Length;
_service.ActionHelper.Async(() => {
FileStream stream = null;
try {
_service.NpgHelper.CreateDatabase(ConnectionParameters);
_service.ActionHelper.Dispatch(() => { ProgressIsIndeterminate = false; });
_service.NpgHelper.ExecuteScript(
ConnectionParameters.ConnectionString,
script.Select(r => new NpgQuery(r)).ToArray(),
true,
row => _service.ActionHelper.Dispatch(() => { Progress = row; }));
_service.ActionHelper.Dispatch(() => { ProgressIsIndeterminate = true; });
stream = File.OpenRead(FileName);
BinaryFormatter binaryFormatter = _service.ExImHelper.CreateFormatter();
string prevTable = string.Empty;
var createScript = _service.ScriptHelper.ExtractScriptLines(
PHmiModelContext.GetPHmiScriptStream());
var tables = _service.ExImHelper.GetTables(createScript);
SerialHelper serialHelper = SerialHelper.Create(tables);
using (var connection = new NpgsqlConnection(ConnectionParameters.ConnectionString)) {
while (stream.Position != stream.Length)
{
var tableData = (TableData)binaryFormatter.Deserialize(stream);
NpgQuery scriptItem = _service.ExImHelper.GetInsertScriptItem(tableData);
if (scriptItem == null)
{
continue;
}
int count = _service.NpgHelper.ExecuteNonQuery(connection, scriptItem);
string table = prevTable;
_service.ActionHelper.Dispatch(() => {
if (table != tableData.TableName && !string.IsNullOrEmpty(table))
{
TablesStored++;
}
RowsStored += count;
});
prevTable = tableData.TableName;
serialHelper.Update(tableData);
}
foreach (NpgQuery alterScript in serialHelper.CreateAlterScriptItem())
{
_service.NpgHelper.ExecuteNonQuery(connection, alterScript);
}
}
_service.DialogHelper.Message(
string.Format("{0} {1}{2}{3} {4}",
Res.TablesStored,
TablesStored.ToString("N0"),
Environment.NewLine,
Res.RowsStored,
RowsStored.ToString("N0")),
Res.ImportCompleted,
owner: View);
_service.ActionHelper.Dispatch(() => base.OkCommandExecuted(obj));
} catch (Exception exception) {
ExceptionDialog.Show(exception, View);
} finally {
if (stream != null)
{
stream.Dispose();
}
_service.ActionHelper.Dispatch(() => {
InProgress = false;
ProgressIsIndeterminate = false;
});
}
});
}
public Tuple <DateTime, double?[]>[] GetPage(int[] trendTagIds, CriteriaType criteriaType,
DateTime criteria, int maxCount)
{
IWhereOp whereOp;
bool asc;
switch (criteriaType)
{
case CriteriaType.DownFromInfinity:
whereOp = null;
asc = false;
break;
case CriteriaType.DownFrom:
whereOp = new Lt(DbStr.Time, criteria.Ticks);
asc = false;
break;
case CriteriaType.DownFromOrEqual:
whereOp = new Le(DbStr.Time, criteria.Ticks);
asc = false;
break;
case CriteriaType.UpFromInfinity:
whereOp = null;
asc = true;
break;
case CriteriaType.UpFrom:
whereOp = new Gt(DbStr.Time, criteria.Ticks);
asc = true;
break;
case CriteriaType.UpFromOrEqual:
whereOp = new Ge(DbStr.Time, criteria.Ticks);
asc = true;
break;
default:
throw new NotSupportedException("CriteriaType " + criteriaType);
}
var columns = new List <string>(trendTagIds.Length + 1)
{
DbStr.Time
};
columns.AddRange(trendTagIds.Select(GetColumnName));
var columnsArr = columns.ToArray();
int limit = Math.Min(maxCount, MaxSamplesToRetrieve);
var parameters = new List <NpgsqlParameter>();
var queryTexts = new List <string>();
for (var i = 0; i < TrendTableSelector.TablesCount; i++)
{
string queryText = _npgQueryHelper.Select(
parameters,
_tableName + "_" + i,
columnsArr,
whereOp,
ColumnsOfTime,
asc,
limit);
queryTexts.Add(queryText);
}
NpgQuery query = _npgQueryHelper.Union(parameters, queryTexts, ColumnsOfTime, asc, limit);
var result = _npgHelper.ExecuteReader(_connection, query, reader => {
DateTime time = reader.GetDateTimeFormTicks(0);
var values = new double?[columns.Count - 1];
for (var j = 1; j < columns.Count; j++)
{
values[j - 1] = reader.GetNullableDouble(j);
}
return(new Tuple <DateTime, double?[]>(time, values));
});
return(asc ? result.Reverse().ToArray() : result);
}
public LogItem[] GetItems(
NpgsqlConnection connection, CriteriaType criteriaType, DateTime criteria, int maxCount,
bool includeBytes)
{
IWhereOp whereOp;
bool asc;
switch (criteriaType)
{
case CriteriaType.DownFromInfinity:
whereOp = null;
asc = false;
break;
case CriteriaType.DownFrom:
whereOp = new Lt(DbStr.Time, criteria.Ticks);
asc = false;
break;
case CriteriaType.DownFromOrEqual:
whereOp = new Le(DbStr.Time, criteria.Ticks);
asc = false;
break;
case CriteriaType.UpFromInfinity:
whereOp = null;
asc = true;
break;
case CriteriaType.UpFrom:
whereOp = new Gt(DbStr.Time, criteria.Ticks);
asc = true;
break;
case CriteriaType.UpFromOrEqual:
whereOp = new Ge(DbStr.Time, criteria.Ticks);
asc = true;
break;
default:
throw new NotSupportedException("CriteriaType " + criteriaType);
}
var columns = includeBytes ? _columns : _columnsWithoutBytes;
NpgQuery query = _npgQueryHelper.Select(
_tableName, columns, whereOp, new[] { DbStr.Time }, asc,
Math.Min(maxCount, MaxItemsToRetrieve));
var alarms = _npgHelper.ExecuteReader(connection, query, reader => {
var i = new LogItem {
Time = reader.GetDateTimeFormTicks(0),
Text = reader.GetNullableString(1)
};
if (includeBytes)
{
i.Bytes = reader.GetByteArray(2);
}
return(i);
});
return(asc ? alarms.Reverse().ToArray() : alarms);
}
