public override string DBObjectExistsQuery(string name, DBObjectTypeEnum objectType, string schema = null)
{
if (schema == null)
{
schema = DEFAULTSCHEMA;
}
string query = string.Empty;
if (objectType == DBObjectTypeEnum.Database)
{
query = $"SELECT 1 FROM sys.databases WHERE name='{name}'";
}
else if (objectType == DBObjectTypeEnum.Schema)
{
query = $"SELECT 1 FROM sys.schemas WHERE name='{name}'";
}
else if (objectType == DBObjectTypeEnum.Table)
{
query = $"SELECT 1 FROM sys.tables WHERE name='{name}' AND schema_id=SCHEMA_ID('{schema}')";
}
else if (objectType == DBObjectTypeEnum.View)
{
query = $"SELECT 1 FROM sys.views WHERE name='{name}' AND schema_id=SCHEMA_ID('{schema}')";
}
else if (objectType == DBObjectTypeEnum.Function)
{
query = $"SELECT 1 FROM Information_schema.Routines WHERE specific_name='{name}' AND SPECIFIC_SCHEMA='{schema}' AND routine_type='FUNCTION'";
}
else if (objectType == DBObjectTypeEnum.Procedure)
{
query = $"SELECT 1 FROM Information_schema.Routines WHERE specific_name='{name}' AND SPECIFIC_SCHEMA='{schema}' AND routine_type='PROCEDURE'";
}
return(query);
}
public override string DBObjectExistsQuery(string name, DBObjectTypeEnum objectType, string schema = null)
{
if (schema == null)
{
schema = DEFAULTSCHEMA;
}
string query = string.Empty;
if (objectType == DBObjectTypeEnum.Database)
{
query = $"SELECT 1 FROM pg_database WHERE datname ILIKE '{name}'";
}
else if (objectType == DBObjectTypeEnum.Schema)
{
query = $"SELECT 1 FROM information_schema.schemata WHERE schema_name = '{name}'";
}
else if (objectType == DBObjectTypeEnum.Table)
{
query = $"SELECT 1 FROM information_schema.tables WHERE table_schema = '{schema}' AND table_name ILIKE '{name}'";
}
else if (objectType == DBObjectTypeEnum.View)
{
query = $"SELECT 1 FROM information_schema.views WHERE table_schema = '{schema}' AND table_name ILIKE '{name}'";
}
else if (objectType == DBObjectTypeEnum.Function)
{
query = $"SELECT 1 FROM pg_proc a JOIN pg_namespace b ON a.pronamespace=b.oid WHERE a.proname ILIKE '{name}' AND b.nspname='{schema}'";
}
else if (objectType == DBObjectTypeEnum.Procedure)
{
query = $"SELECT 1 FROM pg_proc a JOIN pg_namespace b ON a.pronamespace=b.oid WHERE a.proname ILIKE '{name}' AND b.nspname='{schema}'";
}
return(query);
}
public override string DBObjectExistsQuery(string name, DBObjectTypeEnum objectType, string schema = null)
{
if (schema == null)
{
schema = DEFAULTSCHEMA;
}
string query = string.Empty;
if (objectType == DBObjectTypeEnum.Database)
{
throw new NotSupportedException();
}
else if (objectType == DBObjectTypeEnum.Schema)
{
throw new NotSupportedException();
}
else if (objectType == DBObjectTypeEnum.Table)
{
query = $"SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = '{name}'";
}
else if (objectType == DBObjectTypeEnum.View)
{
query = $"SELECT 1 FROM sqlite_master WHERE type = 'view' AND name = '{name}'";
}
else if (objectType == DBObjectTypeEnum.Function)
{
throw new NotSupportedException();
}
else if (objectType == DBObjectTypeEnum.Procedure)
{
throw new NotSupportedException();
}
return(query);
}
public abstract string DBObjectExistsQuery(string name, DBObjectTypeEnum objectType, string schema = null);
