public static SqlResult Compile(this Query query, string databaseType)
{
Compiler compiler = null;
if (DataBaseType == ConnectionType.MYSQL)
{
compiler = new MySqlCompiler();
}
else if (DataBaseType == ConnectionType.SQL)
{
compiler = new MySqlCompiler();
}
else if (DataBaseType == ConnectionType.ORACLE)
{
compiler = new OracleCompiler();
}
else if (DataBaseType == ConnectionType.SQLITE)
{
compiler = new SqliteCompiler();
}
else if (DataBaseType == ConnectionType.POSTRGRESQL)
{
compiler = new PostgresCompiler();
}
return(compiler.Compile(query));
}
static SqlStatements()
{
var conn = DatabaseFactory.GetFactory().GetConnection();
var compiler = new PostgresCompiler();
queryfactory = new QueryFactory(conn, compiler);
}
public void Postgres()
{
var compiler = new PostgresCompiler();
var helper = compiler.GetHelper();
var query = new Query()
.From(new Query("category_translations").As("t"))
.Join(new Query("categories").As("c"), j => j.On("c.Id", "t.CategoryId"))
// فعلا سطح اول
.Where("c.ParentId", Guid.Empty)
.Where("t.Culture", "fa")
.Where("c.Visible", true)
.Select("t.Id as TranslationId", "t.Title", "t.Subtitle", "t.Thumbs");
var queryString = compiler.Compile(query);
query = new Query()
.From(new Query("table").As("table_alias"))
.Select(helper.Select.NoAlias <TableModel>(false, "table_alias", "Password"));
queryString = compiler.Compile(query);
var c2 = new SqlServerCompiler();
var h2 = c2.GetHelper();
var query2 = new Query()
.From(new Query("table").As("table_alias"))
.Select(h2.Select.NoAlias <TableModel>(false, "table_alias", "Password"));
var s2 = c2.Compile(query2);
}
public static Compiler GetCompiler(DatabaseType databaseType, string connectionString)
{
Compiler compiler = null;
if (databaseType == DatabaseType.MySql)
{
compiler = new MySqlCompiler();
}
else if (databaseType == DatabaseType.SqlServer)
{
compiler = new SqlServerCompiler
{
UseLegacyPagination = DatoryUtils.IsUseLegacyPagination(databaseType, connectionString)
};
}
else if (databaseType == DatabaseType.PostgreSql)
{
compiler = new PostgresCompiler();
}
else if (databaseType == DatabaseType.Oracle)
{
compiler = new OracleCompiler
{
UseLegacyPagination = DatoryUtils.IsUseLegacyPagination(databaseType, connectionString)
};
}
return(compiler);
}
public static SqlResult CompileWithLastId(this PostgresCompiler compiler, Query query, string primaryKeyName = "id")
{
SqlResult result = compiler.Compile(query);
//return new SqlResult(result.Sql + ";SELECT lastval();", result.RawBindings);
return(new SqlResult(result.Sql + $" RETURNING \"{primaryKeyName}\"", result.RawBindings));
}
private static Compiler GetCompiler <T>()
where T : IDbConnection, new()
{
Compiler result;
var name = typeof(T).Name;
switch (name)
{
case "SqliteConnection ":
result = new SqliteCompiler();
break;
case "FbConnection":
result = new FirebirdCompiler();
break;
case "MySqlConnection":
result = new MySqlCompiler();
break;
case "OracleConnection":
result = new OracleCompiler();
break;
case "NpgsqlConnection":
result = new PostgresCompiler();
break;
default:
result = new SqlServerCompiler();
break;
}
return(result);
}
private static string ToRawSQL(Query query)
{
var compiler = new PostgresCompiler();
SqlResult result = compiler.Compile(query);
return(result.ToString());
}
public void Delete_WithoutCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete() as DbCompileResult;
Assert.NotNull(result);
Assert.Equal("DELETE FROM Employee", result.SqlQuery);
}
public async Task <TResult> ExecuteQuery <TResult>(Func <QueryFactory, Task <TResult> > execute)
{
using (var connection = new NpgsqlConnection(_connectionString))
{
await connection.OpenAsync();
var compiler = new PostgresCompiler();
var db = new QueryFactory(connection, compiler);
return(await execute(db));
}
}
public void Delete_NotNullCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Name != null) as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(0, result.QueryParameters.Count);
Assert.Equal("DELETE FROM Employee WHERE (\"Name\" IS NOT NULL)", result.SqlQuery);
}
private async Task <TResult> ExecuteQuery <TResult>(Func <QueryFactory, Task <TResult> > execute)
{
using (var connection = new NpgsqlConnection(_options.ConnectionString))
{
await connection.OpenAsync();
connection.TypeMapper.UseJsonNet();
var compiler = new PostgresCompiler();
var db = new QueryFactory(connection, compiler);
return(await execute(db));
}
}
public void Delete_GreaterThanCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Id > 1) as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(1, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("DELETE FROM Employee WHERE (\"Id\" > @P0)", result.SqlQuery);
}
public void Delete_ContainsCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Name.Contains("test")) as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(1, result.QueryParameters.Count);
Assert.Equal("test", result.QueryParameters["@P0"]);
Assert.Equal("DELETE FROM Employee WHERE (\"Name\" LIKE '%' || @P0 || '%')", result.SqlQuery);
}
public static async IAsyncEnumerable <T> QueryKataAsync <T>(this NpgsqlConnection conn, SqlKata.Query query)
{
var compiled = new PostgresCompiler().Compile(query);
await using var reader = await conn.ExecuteReaderAsync(compiled.Sql, compiled.NamedBindings);
var parser = reader.GetRowParser <T>();
while (await reader.ReadAsync())
{
yield return(parser(reader));
}
}
public void Delete_SimpleEqualWithOrCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => e.Id == 1 || e.Name == "test") as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(2, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("test", result.QueryParameters["@P1"]);
Assert.Equal("DELETE FROM Employee WHERE ((\"Id\" = @P0) OR (\"Name\" = @P1))", result.SqlQuery);
}
public void Delete_AndWithOrWithGroupingCondition_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <Employee>(compiler);
var result = builder.Delete(e => (e.Id == 1 && e.Name == "value1") || e.Name != "value2") as DbCompileResult;
Assert.NotNull(result);
Assert.Equal(3, result.QueryParameters.Count);
Assert.Equal(1, result.QueryParameters["@P0"]);
Assert.Equal("value1", result.QueryParameters["@P1"]);
Assert.Equal("value2", result.QueryParameters["@P2"]);
Assert.Equal("DELETE FROM Employee WHERE (((\"Id\" = @P0) AND (\"Name\" = @P1)) OR (\"Name\" <> @P2))", result.SqlQuery);
}
public static void AddAdoDataAccess(this IServiceCollection services, string connectionString)
{
services.AddTransient <DbConnection>(s => ConnectionFactory.Create(connectionString));
services.AddTransient <QueryFactory>(s =>
{
var sqlCompiler = new PostgresCompiler();
var connection = s.GetService <DbConnection>();
return(new QueryFactory(connection, sqlCompiler));
});
services.AddRepositories();
}
private static QueryFactory SqlServerQueryFactory()
{
var compiler = new PostgresCompiler();
var connection = new SqlConnection(
"Server=tcp:localhost,1433;Initial Catalog=Lite;User ID=sa;Password=P@ssw0rd"
);
var db = new QueryFactory(connection, compiler);
db.Logger = result =>
{
Console.WriteLine(result.ToString());
};
return(db);
}
public void ShouldHitDB()
{
var Configuration = StubConfiguration.GetTestAppsettings();
var str = Configuration["ConnectionString"];
var connection = new NpgsqlConnection(
str //"Host=localhost;Port=5432;Username=ahatch1490;Database=exposure;"
);
var compiler = new PostgresCompiler();
var q = new QueryFactory(connection, compiler);
var sites = q.Query("sites").Limit(10).Get <Site>();
Assert.True(sites.Count() == 1);
}
public void Insert_WithColumOrAttribute_Test()
{
var compiler = new PostgresCompiler();
var builder = new QueryBuilder <EmployeeWithAttributes>(compiler);
var employee = new EmployeeWithAttributes()
{
Id = 1,
Name = "Employee Name"
};
var result = builder.Insert(employee) as DbCompileResult;
Assert.NotNull(result.QueryParameters);
Assert.Equal(1, result.QueryParameters.Count);
Assert.Equal(result.QueryParameters["@P0"], employee.Name);
Assert.Equal("INSERT INTO dbo.employee (\"name\") VALUES (@P0)", result.SqlQuery);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
services.AddSingleton <IContextClient, ContextClient>();
services.AddSingleton <IGoodReadsSettings, GoodReadsSettings>();
services.AddSingleton <IXMLClient, XMLClient>();
services.AddSingleton <IGoodReadsService, GoodReadsService>();
services.AddSingleton <SiteService, SiteService>();
services.AddSingleton(qf => {
var str = Configuration["ConnectionString"]; //"Host=localhost;Port=5432;Username=ahatch1490;Database=exposure;"
var connection = new NpgsqlConnection(str);
var compiler = new PostgresCompiler();
return(new QueryFactory(connection, compiler));
});
}
public void Test2()
{
var connStr = "User ID=postgres;Password=1234;Host=localhost;Port=5432;Database=DemoForGenericRepos;Pooling=true;";
var compiler = new PostgresCompiler();
var logger = new ConsoleLogger(LogLevel.Debug | LogLevel.Error | LogLevel.Warning);
using (var crud = new Crud(compiler, logger, () => { return(new NpgsqlConnection(conStr)); }))
{
try
{
var employee = crud.Select <Employee>(e => e.FirstName == "fist name");
}
catch (Exception ex)
{
}
}
}
public static IServiceCollection AddPostgressSQL(this IServiceCollection services, IConfiguration configuration, bool addHealthCheck = true)
{
var logger = services.BuildServiceProvider().GetRequiredService <ILogger <PostgresSettings> >();
services.Configure <PostgresSettings>(configuration.GetSection(nameof(PostgresSettings)));
services.AddScoped <SqlKataPostgresConnection>((provider) =>
{
var settings = provider.GetRequiredService <IOptions <PostgresSettings> >().Value;
var connection = new NpgsqlConnection(configuration.GetConnectionString("PostgreSQL"));
void ProvideClientCertificates(X509CertificateCollection clientCerts)
{
var clientCertPath = settings.ClientCertPath;
var cert = new X509Certificate2(clientCertPath);
clientCerts.Add(cert);
}
if (!string.IsNullOrEmpty(settings.ClientCertPath))
{
connection.ProvideClientCertificatesCallback += ProvideClientCertificates;
}
var compiler = new PostgresCompiler();
return(new SqlKataPostgresConnection(connection, compiler)
{
Logger = compiled => logger.LogInformation(compiled.ToString())
});
});
//Adicionando healthcheck
if (addHealthCheck)
{
services.AddHealthChecks()
.AddNpgSql(configuration.GetConnectionString("PostgreSQL"), tags: new string[] { "db", "sql", "postgres" });
}
return(services);
}
public void Test1()
{
var compiler = new PostgresCompiler();
var logger = new ConsoleLogger(LogLevel.Debug | LogLevel.Error | LogLevel.Warning);
using (var crud = new Crud(compiler, logger, () => { return(new NpgsqlConnection(conStr)); }))
{
// Assert.Throws<System.Net.Sockets.SocketException>(() =>
// {
// var affectedRows = crud.Insert(new Employee()
// {
// Guid = Guid.NewGuid(),
// FirstName = "fist name",
// LastName = "last name",
// Active = true,
// CreateDate = DateTime.Now,
// });
// Assert.Equal(1, affectedRows);
// });
}
}
public static QueryFactory CreateInstance(IDbConnection connection)
{
if (connection.GetType() == typeof(MySqlConnection))
{
var compiler = new MySqlCompiler();
return(new QueryFactory(connection, compiler));
}
if (connection.GetType() == typeof(NpgsqlConnection))
{
var compiler = new PostgresCompiler();
return(new QueryFactory(connection, compiler));
}
if (connection.GetType() == typeof(SqlConnection))
{
var compiler = new SqlServerCompiler();
return(new QueryFactory(connection, compiler));
}
return(null);
}
public static NpgsqlCommand ToNpgsqlCommand(this Query query)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
SqlResult compiledQuery = new PostgresCompiler()
.Compile(query);
NpgsqlCommand compiledCommand = new NpgsqlCommand(compiledQuery.Sql);
if (compiledQuery.NamedBindings != null)
{
foreach (KeyValuePair <string, object> paramWithValue in compiledQuery.NamedBindings)
{
compiledCommand.Parameters.AddWithValue(paramWithValue.Key,
paramWithValue.Value);
}
}
return(compiledCommand);
}
private void JsonData_TextChanged()
{
if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.SELECT)
{
var compiler = new PostgresCompiler();
Query select = new Query((string)((TreeViewItem)TrvQueueList.SelectedItem).Header);
bool found = false;
foreach (StackPanelLocal stkl in StkJsonData.Children)
{
if (Regex.IsMatch(stkl.Text, @"^.*=(['].+[']|[0-9]+)$"))
{
found = true;
if (Regex.IsMatch(stkl.Text, @"='true'$"))
{
select.WhereTrue(Regex.Match(stkl.Text, @"^.*(?=[=])").Value);
}
else if (Regex.IsMatch(stkl.Text, @"='false'$"))
{
select.WhereFalse(Regex.Match(stkl.Text, @"^.*(?=[=])").Value);
}
else
{
select.WhereLike(Regex.Match(stkl.Text, @"^.*(?=[=])").Value, Regex.Match(stkl.Text, @"(?<=[=]).*$").Value, true);
}
}
}
if (!found)
{
var res1 = compiler.Compile(select);
TxtQueryStart.Text = res1.Sql;
return;
}
SqlResult result = compiler.Compile(select);
string sql = result.Sql;
List <object> bindings = result.Bindings;
int i = 0;
foreach (object o in bindings)
{
sql = sql.Replace($"@p{i++}", o.ToString());
}
TxtQueryStart.Text = sql;
}
else if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.INSERT)
{
var compiler = new PostgresCompiler();
Query insert = new Query((string)((TreeViewItem)TrvQueueList.SelectedItem).Header);
List <string> lst = new List <string>();
foreach (StackPanelLocal stkl in StkJsonData.Children)
{
if (Regex.IsMatch(stkl.Text, @"^.*=(['].+[']|[0-9]+)$"))
{
lst.Add(stkl.Text);
}
}
if (lst.Count == 0)
{
TxtQueryStart.Text = $"INSERT INTO {((TreeViewItem)TrvQueueList.SelectedItem).Header}";
return;
}
List <string> names = new List <string>();
List <string> values = new List <string>();
foreach (string s in lst)
{
names.Add(Regex.Match(s, @"^.*(?=[=])").Value);
values.Add(Regex.Match(s, @"(?<=[=]).*$").Value);
}
insert.AsInsert(names.ToArray(), values.ToArray());
SqlResult result = compiler.Compile(insert);
string sql = result.Sql;
List <object> bindings = result.Bindings;
int i = 0;
foreach (object o in bindings)
{
sql = sql.Replace($"@p{i++}", o.ToString());
}
TxtQueryStart.Text = sql;
}
else if (((SQL.QueryType)LstQueryTypes.SelectedItem) == SQL.QueryType.UPDATE)
{
}
}
public PostgreSqlLimitTests()
{
compiler = Compilers.Get <PostgresCompiler>(EngineCodes.PostgreSql);
}
/// <summary>
/// Create new instance of PostgreSQL script generator
/// </summary>
/// <param name="options"></param>
public PostgresqlTestGenerator(Action <TestGeneratorOptions> options = null) :
base(Models.DatabaseKind.PostgreSql, options)
{
_postgresCompiler = new PostgresCompiler();
}
public ValuesController(CosmosDbService cosmosDbService, PostgresCompiler compiler)
{
_cosmosDbService = cosmosDbService;
_compiler = compiler;
}
