public Task UpsertAsync(NsDatabase database, string tableName, string json, object id)
{
Validate.NotNull(database, nameof(database));
Validate.NotNull(json, nameof(json));
Validate.NotNull(id, nameof(id));
throw new NotImplementedException();
}
public Task UpdateAsync(NsDatabase database, string tableName, string json, object id)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
Validate.NotNull(json, nameof(json));
Validate.NotNull(id, nameof(id));
throw new NotImplementedException();
}
public async Task EnsureTableAsync(NsDatabase database, string tableName, ObjectIdType objectIdType = ObjectIdType.Guid)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
await ExecuteNonQueryAsync(
$"CREATE TABLE IF NOT EXISTS \"{database.Name}\".\"{tableName}\"(" +
(objectIdType == ObjectIdType.Guid ? $"_id UUID" : "BIGSERIAL") + " PRIMARY KEY," +
$"_document TEXT NOT NULL)");
}
public async Task DeleteTableAsync(NsDatabase database, string tableName)
{
Validate.NotNull(database, nameof(database));
await ExecuteNonQueryAsync(
$"USE [{database.Name}]",
$"IF EXISTS (select * from sysobjects where name='{tableName}' and xtype='U')",
$"BEGIN",
$"DROP TABLE [dbo].[{tableName}]",
$"END");
}
public async Task DeleteIndexAsync(NsDatabase database, string tableName, string field)
{
Validate.NotNull(database, nameof(database));
var columnName = field.Replace(".", "_");
await ExecuteNonQueryAsync(
$"USE [{database.Name}]",
$"IF EXISTS(SELECT * FROM sys.indexes WHERE name = \'IDX_{columnName}\' AND object_id = OBJECT_ID(\'{tableName}\'))",
$"BEGIN",
$"DROP INDEX [IDX_{columnName}] ON [dbo].[{tableName}]",
$"END");
}
public async Task <int> CountAsync(NsDatabase database, string tableName, TypeReflector typeReflector, Query.Query query = null)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
var sql = new List <string>();
var parameters = new List <KeyValuePair <int, object> >();
SqlUtils.ConvertToSql(sql, typeReflector, database.Name, tableName, parameters, query, selectCount: true);
return((int)(await ExecuteNonQueryAsync(sql.ToArray(),
parameters.ToDictionary(_ => $"@{_.Key}", _ => _.Value), executeAsScalar: true)));
}
public async Task <NsDocument> FindAsync(NsDatabase database, string tableName, object id)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
var sql = new List <string>();
sql.Append($"SELECT _id, _document FROM \"{database.Name}\".\"{tableName}\"")
.Append($"WHERE (_id = @1)");
return((await ExecuteReaderAsync(sql.ToArray(), new Dictionary <string, object>()
{
{ "@1", id }
}))
.FirstOrDefault());
}
public async Task <object> InsertAsync(NsDatabase database, string tableName, string json, object id, ObjectIdType typeOfObjectId)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
Validate.NotNull(json, nameof(json));
if (id != null)
{
await ExecuteNonQueryAsync(new[]
{
$"USE [{database.Name}]",
$"INSERT INTO [dbo].[{tableName}]",
$" ([_id]",
$" ,[_document])",
$" VALUES",
$" (@id",
$" ,@document)",
$"SELECT SCOPE_IDENTITY();"
},
new Dictionary <string, object>()
{
{ "@id", id },
{ "@document", json }
},
executeAsScalar : false);
return(id);
}
return(await ExecuteNonQueryAsync(new[]
{
$"USE [{database.Name}]",
$"INSERT INTO [dbo].[{tableName}]",
$" ([_document])" +
$"output INSERTED._id",
$" VALUES",
$" (@document)"
},
new Dictionary <string, object>()
{
{ "@document", json }
},
executeAsScalar : true));
}
public async Task EnsureTableAsync(NsDatabase database, string tableName, ObjectIdType objectIdType = ObjectIdType.Guid)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
await ExecuteNonQueryAsync(
$"USE [{database.Name}]",
$"IF NOT EXISTS (select * from sysobjects where name='{tableName}' and xtype='U')",
$"BEGIN",
$"CREATE TABLE [dbo].[{tableName}](",
objectIdType == ObjectIdType.Guid? "[_id] [uniqueidentifier] NOT NULL," : "[_id] [int] IDENTITY(1,1) NOT NULL",
$"[_document] [nvarchar](max) NOT NULL,",
$"CONSTRAINT [PK_{tableName}] PRIMARY KEY CLUSTERED ",
$"(",
$"[_id] ASC",
$") WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]",
$") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]",
objectIdType == ObjectIdType.Guid?$"ALTER TABLE [dbo].[{tableName}] ADD CONSTRAINT [DF_{tableName}__id] DEFAULT (newid()) FOR [_id]" : string.Empty,
$"END");
}
public async Task <object> InsertAsync(NsDatabase database, string tableName, string json, object id, ObjectIdType typeOfObjectId)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
Validate.NotNull(json, nameof(json));
if (id != null || typeOfObjectId == ObjectIdType.Guid)
{
await ExecuteNonQueryAsync(new[]
{
$"INSERT INTO \"{database.Name}\".\"{tableName}\"",
$" (\"_id\"",
$" ,\"_document\")",
$" VALUES",
$" (@id",
$" ,@document)"
},
new Dictionary <string, object>()
{
{ "@id", id ?? Guid.NewGuid() },
{ "@document", json }
},
executeAsScalar : false);
return(id);
}
return(await ExecuteNonQueryAsync(new[]
{
$"INSERT INTO \"{database.Name}\".\"{tableName}\"",
$" (\"_document\")" +
$" VALUES",
$" (@document)",
$"RETURNING _id"
},
new Dictionary <string, object>()
{
{ "@document", json }
},
executeAsScalar : true));
}
public async Task <IEnumerable <NsDocument> > FindAsync(NsDatabase database, string tableName, TypeReflector typeReflector, Query.Query query = null, SortDescription[] sorts = null, int skip = 0, int take = 0)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
var sql = new List <string>();
var parameters = new List <KeyValuePair <int, object> >();
SqlUtils.ConvertToSql(sql, typeReflector, database.Name, tableName, parameters, query, sorts);
if (skip > 0)
{
sql.Append($"OFFSET {skip} ROWS");
}
if (take < int.MaxValue)
{
sql.Append($"FETCH NEXT {take} ROWS ONLY");
}
return(await ExecuteReaderAsync(sql.ToArray(), parameters.ToDictionary(_ => $"@{_.Key}", _ => _.Value)));
}
public Task DeleteAsync(NsDatabase database, string tableName, object id)
{
throw new NotImplementedException();
}
public async Task DeleteTableAsync(NsDatabase database, string tableName)
{
Validate.NotNull(database, nameof(database));
await ExecuteNonQueryAsync(
$"DROP TABLE IF EXISTS \"{database}\".\"{tableName}\"");
}
public async Task EnsureIndexAsync(NsDatabase database, string tableName, TypeReflector typeReflector, string path, bool unique = false, bool ascending = true)
{
Validate.NotNull(database, nameof(database));
Validate.NotNullOrEmptyOrWhiteSpace(tableName, nameof(tableName));
Validate.NotNullOrEmptyOrWhiteSpace(path, nameof(path));
Validate.NotNull(typeReflector, nameof(typeReflector));
/*
* USE [DatabaseTest_Index]
* IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[posts]') AND name = 'Updated')
* BEGIN
* ALTER TABLE [dbo].[Posts]
* ADD [Updated] AS (CONVERT(datetime2, JSON_VALUE([_document],'$.Updated'), 102))
* END
* IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'IDX_Updated' AND object_id = OBJECT_ID('posts'))
* BEGIN CREATE NONCLUSTERED INDEX [IDX_Updated] ON [dbo].[posts]
* ( [Updated] DESC )
* WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
* END
*/
var fieldTypes = typeReflector.Navigate(path).ToList();
if (!fieldTypes.Any())
{
throw new InvalidOperationException($"Path '{path}' doesn't represent a valid property");
}
if (fieldTypes.Any(_ => _.IsArray))
{
throw new InvalidOperationException($"Unable to create an index for path '{path}', it traverses arrays or collections of objects");
}
var columnName = path.Replace(".", "_");
string addColumnCommand = null;
var lastFieldType = fieldTypes.Last();
if (lastFieldType.Is(typeof(DateTime)))
{
addColumnCommand = $"ADD [{columnName}] AS (CONVERT(datetime2, JSON_VALUE([_document],'$.{path}'), 102))";
}
else if (lastFieldType.Is(typeof(int)))
{
addColumnCommand = $"ADD [{columnName}] AS (CONVERT(int, JSON_VALUE([_document],'$.{path}')))";
}
if (addColumnCommand == null)
{
throw new InvalidOperationException($"Type '{lastFieldType.Type}' is not (yet?) supported for indexes");
}
await ExecuteNonQueryAsync(
$"USE [{database.Name}]",
$"IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N\'[dbo].[{tableName}]\') AND name = \'{columnName}\')",
$"BEGIN",
$"ALTER TABLE [dbo].[Posts]",
addColumnCommand,
$"END",
$"IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = \'IDX_{columnName}\' AND object_id = OBJECT_ID(\'{tableName}\'))",
$"BEGIN",
$"CREATE {(unique ? "UNIQUE" : string.Empty)} NONCLUSTERED INDEX [IDX_{columnName}] ON [dbo].[{tableName}]",
$"( [{columnName}] {(ascending ? "ASC" : "DESC")} )",
$"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]",
$"END");
}
public async Task DeleteDatabaseAsync(NsDatabase database)
{
Validate.NotNull(database, nameof(database));
await ExecuteNonQueryAsync($"DROP DATABASE \"{database.Name}\"");
}
