public static object ExecuteScalar(this NpgsqlConnection conn, string sql, NpgsqlTransaction tx = null)
{
var cmd = tx == null ? new NpgsqlCommand(sql, conn) : new NpgsqlCommand(sql, conn, tx);
using (cmd)
return cmd.ExecuteScalar();
}
public PostgresUnitOfWork(string connectionString)
{
_connection = new NpgsqlConnection(connectionString);
_connection.Open();
_transaction = _connection.BeginTransaction();
}
public override void AfterUpdate(ParticipantPermission thing, Thing container, ParticipantPermission originalThing,
NpgsqlTransaction transaction, string partition, ISecurityContext securityContext)
{
this.ValidateAccessRightKind(thing);
}
public static async Task <bool> SchemaExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string schemaName)
{
bool schemaExist;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "SELECT count(*) FROM information_schema.schemata where schema_name = @schemaName";
var sqlParameter = new NpgsqlParameter()
{
ParameterName = "@schemaName",
Value = schemaName
};
dbCommand.Parameters.Add(sqlParameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false);
schemaExist = (long)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(schemaExist);
}
public static Task <bool> TypeExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTypeName)
{
throw new NotImplementedException();
}
public static Task DropTypeIfExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, int commandTimeout, string quotedTypeName)
{
throw new NotImplementedException();
}
public static async Task <bool> DatabaseExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction)
{
bool tableExist;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "SELECT count(*) FROM pg_database where pg_database.datname = @databaseName";
var sqlParameter = new NpgsqlParameter()
{
ParameterName = "@databaseName",
Value = connection.Database
};
dbCommand.Parameters.Add(sqlParameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false);
tableExist = (long)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(tableExist);
}
/// <summary>
/// Remove the supplied value from the association property as indicated by the supplied property name.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) from where the requested resource will be removed.
/// </param>
/// <param name="propertyName">
/// The association property from where the supplied value will be removed.
/// </param>
/// <param name="iid">
/// The <see cref="UnitPrefix"/> id that is the source of the link table records.
/// </param>
/// <param name="value">
/// A value for which the link table record will be removed.
/// </param>
/// <returns>
/// True if the link was removed.
/// </returns>
public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value)
{
return(this.UnitPrefixDao.DeleteFromCollectionProperty(transaction, partition, propertyName, iid, value));
}
/// <summary>
/// Reorder the supplied value collection of the association link table indicated by the supplied property name.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource order will be updated.
/// </param>
/// <param name="propertyName">
/// The association property name that will be reordered.
/// </param>
/// <param name="iid">
/// The <see cref="UnitPrefix"/> id that is the source for the reordered link table record.
/// </param>
/// <param name="orderUpdate">
/// The order update information containing the new order key.
/// </param>
/// <returns>
/// True if the link was created.
/// </returns>
public bool ReorderCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, CDP4Common.Types.OrderedItem orderUpdate)
{
return(this.UnitPrefixDao.ReorderCollectionProperty(transaction, partition, propertyName, iid, orderUpdate));
}
/// <summary>
/// Remove the supplied value from the association property as indicated by the supplied property name.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) from where the requested resource will be removed.
/// </param>
/// <param name="propertyName">
/// The association property from where the supplied value will be removed.
/// </param>
/// <param name="iid">
/// The <see cref="RuleVerificationList"/> id that is the source of the link table records.
/// </param>
/// <param name="value">
/// A value for which the link table record will be removed.
/// </param>
/// <returns>
/// True if the link was removed.
/// </returns>
public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value)
{
return(this.RuleVerificationListDao.DeleteFromCollectionProperty(transaction, partition, propertyName, iid, value));
}
/// <summary>
/// Read the data from the database.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource is stored.
/// </param>
/// <param name="ids">
/// Ids to retrieve from the database.
/// </param>
/// <param name="isCachedDtoReadEnabledAndInstant">
/// The value indicating whether to get cached last state of Dto from revision history.
/// </param>
/// <returns>
/// List of instances of <see cref="CDP4Common.DTO.DiagramCanvas"/>.
/// </returns>
public virtual IEnumerable <CDP4Common.DTO.DiagramCanvas> Read(NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids = null, bool isCachedDtoReadEnabledAndInstant = false)
{
using (var command = new NpgsqlCommand())
{
var sqlBuilder = new System.Text.StringBuilder();
if (isCachedDtoReadEnabledAndInstant)
{
sqlBuilder.AppendFormat("SELECT \"Jsonb\" FROM \"{0}\".\"DiagramCanvas_Cache\"", partition);
if (ids != null && ids.Any())
{
sqlBuilder.Append(" WHERE \"Iid\" = ANY(:ids)");
command.Parameters.Add("ids", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = ids;
}
sqlBuilder.Append(";");
command.Connection = transaction.Connection;
command.Transaction = transaction;
command.CommandText = sqlBuilder.ToString();
// log the sql command
this.LogCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var thing = this.MapJsonbToDto(reader);
if (thing != null)
{
yield return(thing as DiagramCanvas);
}
}
}
}
else
{
sqlBuilder.AppendFormat("SELECT * FROM \"{0}\".\"DiagramCanvas_View\"", partition);
if (ids != null && ids.Any())
{
sqlBuilder.Append(" WHERE \"Iid\" = ANY(:ids)");
command.Parameters.Add("ids", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = ids;
}
sqlBuilder.Append(";");
command.Connection = transaction.Connection;
command.Transaction = transaction;
command.CommandText = sqlBuilder.ToString();
// log the sql command
this.LogCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return(this.MapToDto(reader));
}
}
}
}
}
/// <summary>
/// Gets a unique revision number for this transaction by reading it from the RevisionRegistry table, or adding it there if it does not exist yet
/// This ensures that there is only
/// </summary>
/// <param name="transaction">
/// The current transaction
/// </param>
/// <param name="partition">
/// The partition
/// </param>
/// <returns>
/// The current or next available revision number
/// </returns>
public int GetRevisionForTransaction(NpgsqlTransaction transaction, string partition)
{
return(this.RevisionDao.GetRevisionForTransaction(transaction, partition));
}
/// <summary>
/// Insert new values into the IterationRevisionLog table
/// </summary>
/// <param name="transaction">
/// The current transaction
/// </param>
/// <param name="partition">
/// The partition
/// </param>
/// <param name="iteration">
/// The iteration associated to the revision
/// </param>
/// <param name="fromRevision">
/// The starting revision number for the iteration. If null the current revision is used.
/// </param>
/// <param name="toRevision">
/// The to Revision.
/// </param>
public void InsertIterationRevisionLog(NpgsqlTransaction transaction, string partition, Guid iteration, int?fromRevision, int?toRevision)
{
this.RevisionDao.InsertIterationRevisionLog(transaction, partition, iteration, fromRevision, toRevision);
}
public ContaPagarParcelaRepositorio(NpgsqlConnection context, NpgsqlTransaction transaction)
{
this._context = context;
this._transaction = transaction;
}
/// <summary>
/// Updates sitemap area node record
/// </summary>
/// <param name="id"></param>
/// <param name="areaId"></param>
/// <param name="name"></param>
/// <param name="label"></param>
/// <param name="labelTranslations"></param>
/// <param name="iconClass"></param>
/// <param name="url"></param>
/// <param name="type"></param>
/// <param name="entityId"></param>
/// <param name="weight"></param>
/// <param name="accessRoles"></param>
/// <param name="transaction"></param>
public void Update(Guid id, Guid areaId, string name, string label, string labelTranslations,
string iconClass, string url, int type, Guid?entityId, int weight,
List <Guid> accessRoles, List <Guid> entityListPages = null, List <Guid> entityCreatePages = null,
List <Guid> entityDetailsPages = null, List <Guid> entityManagePages = null, NpgsqlTransaction transaction = null, Guid?parentId = null)
{
NpgsqlCommand command = new NpgsqlCommand(
"UPDATE public.app_sitemap_area_node SET " +
"parent_id = @parent_id, area_id = @area_id, name = @name, label = @label, label_translations = @label_translations, " +
"weight = @weight, type = @type, icon_class = @icon_class, url = @url, entity_id = @entity_id, " +
"access_roles = @access_roles, entity_list_pages = @entity_list_pages, entity_create_pages = @entity_create_pages, " +
"entity_details_pages = @entity_details_pages, entity_manage_pages = @entity_manage_pages " +
"WHERE id = @id");
command.Parameters.Add(new NpgsqlParameter("@id", id));
command.Parameters.Add(new NpgsqlParameter("@parent_id", (object)parentId ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@area_id", areaId));
command.Parameters.Add(new NpgsqlParameter("@name", name));
command.Parameters.Add(new NpgsqlParameter("@label", (object)label ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@label_translations", (object)labelTranslations ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@icon_class", (object)iconClass ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@url", (object)url ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@entity_id", (object)entityId ?? DBNull.Value));
command.Parameters.Add(new NpgsqlParameter("@weight", weight));
command.Parameters.Add(new NpgsqlParameter("@type", type));
if (accessRoles != null && accessRoles.Count > 0)
{
command.Parameters.Add("@access_roles", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = accessRoles.ToArray();
}
else
{
command.Parameters.Add("@access_roles", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray();
}
if (entityListPages != null && entityListPages.Count > 0)
{
command.Parameters.Add("@entity_list_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityListPages.ToArray();
}
else
{
command.Parameters.Add("@entity_list_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray();
}
if (entityCreatePages != null && entityCreatePages.Count > 0)
{
command.Parameters.Add("@entity_create_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityCreatePages.ToArray();
}
else
{
command.Parameters.Add("@entity_create_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray();
}
if (entityDetailsPages != null && entityDetailsPages.Count > 0)
{
command.Parameters.Add("@entity_details_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityDetailsPages.ToArray();
}
else
{
command.Parameters.Add("@entity_details_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray();
}
if (entityManagePages != null && entityManagePages.Count > 0)
{
command.Parameters.Add("@entity_manage_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityManagePages.ToArray();
}
else
{
command.Parameters.Add("@entity_manage_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray();
}
if (transaction != null)
{
ExecuteSqlNonQueryCommands(transaction, command);
}
else
{
ExecuteSqlNonQueryCommands(command);
}
}
/// <summary>
/// Update the containment order as indicated by the supplied orderedItem.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource order will be updated.
/// </param>
/// <param name="orderedItem">
/// The order update information containing the new order key.
/// </param>
/// <returns>
/// True if the contained item was successfully reordered.
/// </returns>
public bool ReorderContainment(NpgsqlTransaction transaction, string partition, CDP4Common.Types.OrderedItem orderedItem)
{
throw new NotSupportedException();
}
public static async Task DropTriggerIfExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, int commandTimeout,
string quotedTriggerName, string quotedTableName)
{
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new NpgsqlCommand(string.Format(CultureInfo.InvariantCulture,
"DROP TRIGGER IF EXISTS {0} ON TABLE {1}", quotedTriggerName, quotedTableName), connection))
{
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
}
/// <summary>
/// Execute additional logic after each get function call.
/// </summary>
/// <param name="resultCollection">
/// An instance collection that was retrieved from the persistence layer.
/// </param>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) from which the requested resource is to be retrieved.
/// </param>
/// <param name="ids">
/// Ids to retrieve from the database.
/// </param>
/// <param name="includeReferenceData">
/// Control flag to indicate if reference library data should be retrieved extent=deep or extent=shallow.
/// </param>
/// <returns>
/// A post filtered instance of the passed in resultCollection.
/// </returns>
public override IEnumerable <Thing> AfterGet(IEnumerable <Thing> resultCollection, NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids, bool includeReferenceData = false)
{
var filteredCollection = new List <Thing>();
foreach (var thing in resultCollection)
{
if (this.IsInstanceReadAllowed(transaction, thing, partition))
{
filteredCollection.Add(thing);
}
else
{
Logger.Info("The person " + this.PermissionService.Credentials.Person.UserName + " does not have a read permission for " + thing.GetType().Name + ".");
}
}
return(filteredCollection);
}
public static async Task <(string DatabaseName, string EngineVersion)> GetHelloAsync(NpgsqlConnection connection, NpgsqlTransaction transaction)
{
string dbName = null;
string dbVersion = null;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "SELECT version(), pg_database.datname FROM pg_database WHERE datname = @databaseName;";
var sqlParameter = new NpgsqlParameter()
{
ParameterName = "@databaseName",
Value = connection.Database
};
dbCommand.Parameters.Add(sqlParameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
using (var reader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
reader.Read();
dbVersion = reader.GetString(0);
dbName = reader.GetString(1);
}
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(dbName, dbVersion);
}
/// <summary>
/// Get the requested data from the ORM layer.
/// </summary>
/// <param name="transaction">
/// The transaction object.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource is stored.
/// </param>
/// <param name="ids">
/// Ids to retrieve from the database.
/// </param>
/// <param name="containerSecurityContext">
/// The security context of the container instance.
/// </param>
/// <returns>
/// List of instances of <see cref="UnitPrefix"/>.
/// </returns>
public IEnumerable <Thing> Get(NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids, ISecurityContext containerSecurityContext)
{
return(this.RequestUtils.QueryParameters.ExtentDeep
? this.GetDeep(transaction, partition, ids, containerSecurityContext)
: this.GetShallow(transaction, partition, ids, containerSecurityContext));
}
public static async Task <bool> TableExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTableName)
{
bool tableExist;
var tableName = ParserName.Parse(quotedTableName).ObjectName;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "SELECT count(*) FROM information_schema.tables " +
"WHERE table_type = 'BASE TABLE' AND table_schema != 'pg_catalog' AND table_schema != 'information_schema' " +
"AND table_name = @tableName AND table_schema = @schemaName";
NpgsqlParameter sqlParameter = new NpgsqlParameter()
{
ParameterName = "@tableName",
Value = tableName
};
dbCommand.Parameters.Add(sqlParameter);
sqlParameter = new NpgsqlParameter()
{
ParameterName = "@schemaName",
Value = GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName))
};
dbCommand.Parameters.Add(sqlParameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false);
tableExist = (long)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(tableExist);
}
public SqlCommand(string sql, NpgsqlConnection connection = null, NpgsqlTransaction transaction = null)
{
_command = new NpgsqlCommand(sql, connection ?? Database.CreateConnection(), transaction);
}
public static async Task <bool> TriggerExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTriggerName)
{
bool triggerExist;
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
var commandText = "Select count(*) from Information_schema.triggers " +
"Where trigger_name = @triggerName and trigger_schema = @schemaName";
using (var sqlCommand = new NpgsqlCommand(commandText, connection))
{
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", NpgsqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
var result = await sqlCommand.ExecuteScalarAsync().ConfigureAwait(false);
triggerExist = (long)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(triggerExist);
}
/// <summary>
/// Get columns for table
/// </summary>
public static async Task <SyncTable> GetColumnsForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName)
{
var commandColumn = $"Select column_name, " +
$"ordinal_position, " +
$"data_type, " +
$"character_maximum_length, " +
$"numeric_precision, " +
$"numeric_scale, " +
$"is_nullable, " +
$"is_generated, " +
$"is_identity, " +
$"ind.is_unique, " +
$"identity_start, " +
$"identity_increment " +
$"from information_schema.columns " +
$"where table_name = @tableName and table_schema = @schemaName ";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = "public";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString;
}
var syncTable = new SyncTable(tableNameNormalized);
using (var sqlCommand = new NpgsqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
/// <summary>
/// Get Table
/// </summary>
public static async Task <SyncTable> GetTriggerAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string triggerName, string schemaName)
{
var command = $"Select trigger_name from Information_schema.triggers " +
"Where trigger_name = @triggerName and trigger_schema = @schemaName";
var triggerNameNormalized = ParserName.Parse(triggerName).Unquoted().Normalized().ToString();
var triggerNameString = ParserName.Parse(triggerName).ToString();
var schemaNameString = "public";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString;
}
var syncTable = new SyncTable(triggerNameNormalized, schemaNameString);
using (var npgsqlCommand = new NpgsqlCommand(command, connection))
{
npgsqlCommand.Parameters.AddWithValue("@triggerName", triggerNameString);
npgsqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
npgsqlCommand.Transaction = transaction;
}
using (var reader = await npgsqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
/// <summary>
/// Get Table
/// </summary>
public static async Task <SyncTable> GetTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName)
{
var command = $"SELECT * " +
" FROM information_schema.tables " +
" WHERE table_type = 'BASE TABLE' " +
" AND table_schema != 'pg_catalog' AND table_schema != 'information_schema' " +
" AND table_name=@tableName AND table_schema=@schemaName " +
" ORDER BY table_schema, table_name " +
" LIMIT 1";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = "public";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString;
}
var syncTable = new SyncTable(tableNameNormalized, schemaNameString);
using (var sqlCommand = new NpgsqlCommand(command, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public static int DeleteByProcessId(NpgsqlConnection connection, Guid processId, NpgsqlTransaction transaction = null)
{
var pProcessId = new NpgsqlParameter("processid", NpgsqlDbType.Uuid)
{
Value = processId
};
return(ExecuteCommand(connection,
string.Format("DELETE FROM {0} WHERE \"ProcessId\" = @processid", ObjectName), transaction,
pProcessId));
}
/// <summary>
/// Get primary keys columns
/// </summary>
public static async Task <SyncTable> GetPrimaryKeysForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName)
{
var commandColumn = @"SELECT ccu.column_name, tc.constraint_name, c.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage AS ccu on ccu.constraint_schema = tc.constraint_schema
AND ccu.constraint_name = tc.constraint_name
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE tc.constraint_type = 'PRIMARY KEY' and tc.table_name = @tableName and tc.table_schema=@schemaName;";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = "public";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString;
}
var syncTable = new SyncTable(tableNameNormalized);
using (var command = new NpgsqlCommand(commandColumn, connection))
{
command.Parameters.AddWithValue("@tableName", tableNameString);
command.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
command.Transaction = transaction;
}
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
/// <summary>
/// Remove the supplied value from the association property as indicated by the supplied property name.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) from where the requested resource will be removed.
/// </param>
/// <param name="propertyName">
/// The association property from where the supplied value will be removed.
/// </param>
/// <param name="iid">
/// The <see cref="MeasurementScale"/> id that is the source of the link table records.
/// </param>
/// <param name="value">
/// A value for which the link table record will be removed.
/// </param>
/// <returns>
/// True if the link was removed.
/// </returns>
public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value)
{
throw new NotSupportedException();
}
public static async Task <SyncTable> GetRelationsForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName)
{
var commandRelations = @"
SELECT tc.constraint_name AS ForeignKey,
tc.table_schema AS SchemaName,
tc.table_name AS TableName,
kcu.column_name AS ColumnName,
ccu.table_schema AS ReferenceSchemaName,
ccu.table_name AS ReferenceTableName,
ccu.column_name AS ReferenceColumnName
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = @tableName AND tc.table_schema = @schemaName";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = ParserName.Parse(schemaName).ToString();
// default as dbo
schemaNameString = string.IsNullOrEmpty(schemaNameString) ? "dbo" : schemaNameString;
var syncTable = new SyncTable(tableNameNormalized, schemaNameString);
using (var sqlCommand = new NpgsqlCommand(commandRelations, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public static async Task<object> ExecuteScalarAsync(this NpgsqlConnection conn, string sql, NpgsqlTransaction tx = null)
{
var cmd = tx == null ? new NpgsqlCommand(sql, conn) : new NpgsqlCommand(sql, conn, tx);
using (cmd)
return await cmd.ExecuteScalarAsync();
}
/// <summary>
/// Add the supplied value to the association link table indicated by the supplied property name.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource will be stored.
/// </param>
/// <param name="propertyName">
/// The association property name that will be persisted.
/// </param>
/// <param name="iid">
/// The <see cref="ModellingThingReference"/> id that will be the source for each link table record.
/// </param>
/// <param name="value">
/// A value for which a link table record will be created.
/// </param>
/// <returns>
/// True if the link was created.
/// </returns>
public bool AddToCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value)
{
return(this.ModellingThingReferenceDao.AddToCollectionProperty(transaction, partition, propertyName, iid, value));
}
