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; }
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); }