public async Task <int> InsertDb(DbDefinition dbdefinition, DbObject[] dbObjects, DbColumn[] columns, DbIndex[] indices, DbIndexColumn[] indicesColumns)
{
return(await _db.CommandAsync(async (conn, trn, timeout) =>
{
string sql = @"INSERT INTO 'SchemaSearch.Dbs' (Server, DbName, IndexDateUtc)
Values (@Server, @DbName, @IndexDateUtc);";
await conn.ExecuteAsync(sql, dbdefinition, trn, timeout);
sql = "SELECT last_insert_rowid()";
var dbId = await conn.QuerySingleAsync <int>(sql, dbdefinition, trn, timeout);
dbObjects.ForEach(p => p.DbId = dbId);
columns.ForEach(p => p.DbId = dbId);
indices.ForEach(p => p.DbId = dbId);
indicesColumns.ForEach(p => p.DbId = dbId);
sql = @"INSERT INTO 'SchemaSearch.DbObjects' (DbId, ObjectId, Type, SchemaName, Name, Definition, ModificationDate, ParentObjectId)
Values (@DbId, @ObjectId, @Type, @SchemaName, @Name, @Definition, @ModificationDate, @ParentObjectId);";
await conn.ExecuteAsync(sql, dbObjects, trn, timeout);
sql = @"INSERT INTO 'SchemaSearch.DbColumns' (DbId, TableId, Name, Datatype, Precision, Definition)
Values (@DbId, @TableId, @Name, @Datatype, @Precision, @Definition);";
await conn.ExecuteAsync(sql, columns, trn, timeout);
sql = @"INSERT INTO 'SchemaSearch.DbIndices' (DbId, OwnerId, IndexNumber, Name, Type, FilterDefinition, IsUnique)
Values (@DbId, @OwnerId, @IndexNumber, @Name, @Type, @FilterDefinition, @IsUnique);";
await conn.ExecuteAsync(sql, indices, trn, timeout);
sql = @"INSERT INTO 'SchemaSearch.DbIndicesColumns' (DbId, OwnerId, IndexNumber, IndexColumnId, OwnerColumnId, ColumnName, IsDesc, Included)
Values (@DbId, @OwnerId, @IndexNumber, @IndexColumnId, @OwnerColumnId, @ColumnName, @IsDesc, @Included);";
await conn.ExecuteAsync(sql, indicesColumns, trn, timeout);
return dbId;
}));
}
/// <summary>
/// Execute SQL command.
/// </summary>
/// <returns></returns>
internal override GatewayResult Execute()
{
GatewayResult result = new GatewayResult();
try
{
if (DbDefinition != null)
{
DatabaseDefinition dbDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(dbDefinition);
result.ErrorCode = GatewayAdapter.Gateway.SQLExecute(dbDefinition, sqlStatement, storageAttributes, out statementReturnedValues, ref dbFields);
for (int i = 0; i < statementReturnedValues.Length; i++)
{
if (statementReturnedValues[i] != null)
{
statementReturnedValues[i] = GatewayAdapter.StorageConvertor.ConvertGatewayToRuntimeField(dbFields[i], statementReturnedValues[i]);
}
}
}
else
{
result.ErrorCode = GatewayErrorCode.DatasourceNotExist;
}
}
catch
{
throw new NotImplementedException();
}
SetErrorDetails(result);
return(result);
}
/// <summary>
/// Execute DbDisconnect command.
/// </summary>
/// <returns></returns>
internal override GatewayResult Execute()
{
GatewayResult result = new GatewayResult();
try
{
if (DbDefinition != null)
{
DatabaseDefinition dbDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(dbDefinition);
result.ErrorCode = GatewayAdapter.Gateway.DbDisconnect(dbDefinition.Location, out tableName);
}
else
{
result.ErrorCode = GatewayErrorCode.DatasourceNotExist;
}
}
catch
{
throw new NotImplementedException();
}
SetErrorDetails(result);
return(result);
}
internal override GatewayResult Execute()
{
GatewayResult result = new GatewayResult();
DatabaseDefinition dbDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(dbDefinition);
result.ErrorCode = GatewayAdapter.Gateway.FileRename(DataSourceDefinition, DestinationDataSourceDefinition, dbDefinition);
SetErrorDetails(result);
return(result);
}
/// <summary>
/// set error details on the command
/// </summary>
/// <param name="result"></param>
protected virtual void SetErrorDetails(GatewayResult result)
{
string errorString = "";;
int errorCode = 0;
if (!result.Success)
{
// TODO: Error handling.
// Temporary !!!
DatabaseDefinition databaseDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(databaseDefinition);
GatewayAdapter.Gateway.LastError(databaseDefinition, false, ref errorCode, ref errorString);
result.ErrorParams = new object[] { DataSourceDefinition.Name, errorString };
}
}
internal override GatewayResult Execute()
{
Record();
GatewayResult result = new GatewayResult();
RTDataSource rtDataSource = GatewayAdapter.GetDataSource(DataSourceDefinition);
//SerializeDatasource(DataSourceDefinition);
//Serialize();
if (rtDataSource == null)
{
rtDataSource = new RTDataSource(DataSourceDefinition);
rtDataSource.GatewayAdapter = GatewayAdapter;
}
// TODO: Access (HDLINFO hdl_info_tbl_.datatbl)
// TODO: Foreign keys
// share & mode are ignored
if (!rtDataSource.IsOpened)
{
try
{
DatabaseDefinition dbDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(dbDefinition);
result.ErrorCode = GatewayAdapter.Gateway.FileOpen(DataSourceDefinition, dbDefinition, FileName, Access,
DbShare.Write, DbOpen.Normal, null);
}
catch (FileNotFoundException ex)
{
throw new ApplicationException("The SQLite database couldn't be opened.", ex);
}
}
if (result.Success)
{
rtDataSource.Open();
}
SetErrorDetails(result);
return(result);
}
internal override GatewayResult Execute()
{
Record();
GatewayAdapterCursor gatewayAdapterCursor = new GatewayAdapterCursor(RuntimeCursor.CursorDefinition);
gatewayAdapterCursor.CursorType = CursorType.Regular;
GatewayResult result = new GatewayResult();
DatabaseDefinition dbDefinition = (DatabaseDefinition)DbDefinition.Clone();
UpdateDataBaseLocation(dbDefinition);
result.ErrorCode = GatewayAdapter.Gateway.CrsrPrepare(gatewayAdapterCursor, dbDefinition);
if (result.Success)
{
GatewayAdapter.AddCursor(RuntimeCursor, gatewayAdapterCursor);
}
// db_rng_val_alloc ??
SetErrorDetails(result);
return(result);
}
public async Task <int> IndexAsync(DbConnectionString dbConnectionString)
{
if (DbHelper.IsSystemDb(dbConnectionString.Database))
{
throw new Exception("Cannot index system db: " + dbConnectionString.Database);
}
using (await _asyncLock.LockAsync())
{
var dbId = await DbExistsAsync(dbConnectionString);
if (dbId > 0)
{
return(dbId);
}
string dbObjectsQuery = @"
WITH DefinitionInfo (object_id, definition)
AS
(
SELECT m.object_id , m.definition from sys.sql_modules as m
UNION
SELECT c.object_id, 'CHECK ' + c.definition from sys.check_constraints as c
UNION
SELECT dc.object_id, 'DEFAULT' + dc.definition + ' FOR [' + c.name + ']'
FROM sys.default_constraints as dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
UNION
SELECT s.object_id, s.base_object_name from sys.synonyms as s
)
SELECT o.object_id AS 'objectId' , o.type_desc as 'type', schema_name(o.schema_id) AS 'schemaName', o.name, DefinitionInfo.definition, o.modify_date as ModificationDate, Parent.object_id as parentObjectId
FROM sys.objects AS o
LEFT JOIN DefinitionInfo ON DefinitionInfo.object_id = o.object_id
LEFT JOIN sys.objects AS Parent ON Parent.object_id = o.parent_object_id and Parent.is_ms_shipped = 0
WHERE o.is_ms_shipped = 0 AND NOT (o.parent_object_id > 0 AND Parent.object_id IS NULL)";
string columnsQuery = @"
SELECT c.id as 'TableId', c.name, t.name AS 'datatype', c.prec AS 'precision', c.scale AS 'scale', cc.definition
FROM sys.syscolumns AS c
INNER JOIN sys.objects AS o ON c.id = o.object_id
INNER JOIN sys.types AS t ON c.xusertype = t.user_type_id
LEFT JOIN sys.computed_columns AS cc ON cc.object_id = o.object_id AND cc.column_id = c.colid
WHERE o.type = 'U' and o.is_ms_shipped = 0
ORDER BY c.id, c.colid";
string indicesQuery = @"
SELECT
t.object_id ownerId,
i.index_id 'indexNumber',
i.[name],
i.type_desc 'type',
i.filter_definition FilterDefinition,
i.is_unique IsUnique
FROM sys.indexes i
INNER JOIN sys.objects t ON t.object_id = i.object_id
WHERE t.is_ms_shipped = 0 AND index_id > 0
ORDER BY t.object_id, i.index_id";
string indicesColumnsQuery = @"
SELECT
OwnerId = ind.object_id,
IndexNumber = ind.index_id,
IndexColumnId = ic.index_column_id,
OwnerColumnId = col.column_id,
ColumnName = col.name,
IsDesc = ic.is_descending_key,
Included = ic.is_included_column
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN sys.objects t ON ind.object_id = t.object_id
WHERE
t.is_ms_shipped = 0
ORDER BY
ind.object_id, ind.index_id, ic.index_column_id;";
using (var connection = new SqlConnection(dbConnectionString.ConnectionString))
{
var dbobjects = (await connection.QueryAsync <DbObject>(dbObjectsQuery)).ToArray();
var dbColumns = (await connection.QueryAsync <DbColumn>(columnsQuery)).ToArray();
var dbIndices = (await connection.QueryAsync <DbIndex>(indicesQuery)).ToArray();
var dbIndicesColumns = (await connection.QueryAsync <DbIndexColumn>(indicesColumnsQuery)).ToArray();
var dbDefinition = new DbDefinition()
{
DbName = dbConnectionString.Database,
Server = dbConnectionString.Server,
IndexDateUtc = DateTime.UtcNow
};
return(await _schemaSearchRepository.InsertDb(dbDefinition, dbobjects, dbColumns, dbIndices, dbIndicesColumns));
}
}
}
