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 string GetSqlCrudSelect <T>(this T input) where T : class, new()
{
var properties = input.GetNonEnumerableProperties(_globalNameExclusion, SnakeNameConversion);
var query = new Query(typeof(T).Name.ToSnakeCase())
.Select(properties.Keys.ToArray());
return(_compiler.Compile(query).Sql);
}
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 string ToRawSQL(Query query)
{
var compiler = new PostgresCompiler();
SqlResult result = compiler.Compile(query);
return(result.ToString());
}
/// <summary>
/// Get PostgreSQL script for table exists abstract method
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected override string GetTableExistScript(string table)
{
var query = new Query("information_schema.tables").Select("*").Where("table_name", table);
var result = _postgresCompiler.Compile(query);
return($"SELECT EXISTS({result});");
}
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)
{
}
}
