SqlSelect& SqlSelect::Limit(int limit) {
ASSERT(text.StartsWith("select "));
String s = AsString(limit);
text.Insert(6, SqlCode(MSSQL, " top " + s)());
text << SqlCode(MSSQL, "")(" limit " + s);
return *this;
}
SqlSet SqlSelect::AsTable(const SqlId& tab) const
{
StringBuffer t;
t << SqlCode(MSSQL|PGSQL, "")("(")
<< "(" << text << ") as \t" << tab.ToString() << '\t'
<< SqlCode(MSSQL|PGSQL, "")(")");
return SqlSet(String(t), SqlSet::HIGH);
}
String SqlSet::operator()(int at, byte cond) const {
if(IsEmpty()) return "null";
if(at <= priority)
return text;
StringBuffer out;
out << SqlCode(cond, "(")() << text << SqlCode(cond, ")")();
return out;
}
SqlSelect& SqlSelect::SetOp(const SqlSelect& s2, const char *op)
{
String q;
q << SqlCode(SQLITE3, "")("((")
<< text
<< SqlCode(SQLITE3, "")(")")
<< op
<< SqlCode(SQLITE3, "")("(")
<< s2.text
<< SqlCode(SQLITE3, "")("))")
;
text = q;
return *this;
}
SqlWith& SqlWith::WithRecursive(SqlId table)
{
text << (text.GetCount() ? ", " : "with ")
<< SqlCode(MSSQL, "")("recursive ")
<< table.Quoted();
args = false;
return *this;
}
SqlSelect& SqlSelect::operator-=(const SqlSelect& s2) {
return SetOp(s2, SqlCode(MSSQL|PGSQL|SQLITE3," except ")(" minus "));
}
SqlSelect& SqlSelect::Get() {
text = "select " + text + SqlCode(ORACLE, " from DUAL")("");
valid = true;
return *this;
}
SqlSelect& SqlSelect::ForUpdate() {
text << SqlCode(SQLITE3, "")(" for update");
return *this;
}
SqlVal GetYearDayIndex(const SqlVal& date)
{
SqlVal mssql("substring(convert(varchar(max), " + ~date + ", 1), 1, 5)", SqlS::FN);
SqlVal oracle(SqlFunc("to_char", date, "MM/DD"));
return SqlVal(SqlCode(MSSQL, ~mssql)(~oracle), SqlS::FN);
}
SqlId SchemaId(const SqlId& table_id, const SqlId& alias_id)
{
return SqlId(SchemaTableName(~table_id) + SqlCode(MSSQL, " as ")(" ") + ~alias_id);
}
SqlBool Like(const SqlVal& a, const SqlVal& b, bool cs) {
return SqlBool(a, SqlCode
(MY_SQL, cs ? " like binary " : " like ")
(PGSQL, cs ? " like " : " ilike ")
(" like "), b, SqlS::COMP);
}
SqlBool IsSame(const SqlVal& a, const SqlVal& b)
{
return SqlBool(a, SqlCode(MY_SQL, "<=>")(" is not distinct from "), b, SqlS::COMP);
}
SqlBool NotLike(const SqlVal& a, const SqlVal& b, bool cs) {
return SqlBool(a, SqlCode
(PGSQL, cs ? " not like " : " not ilike ")
(" not like "), b, SqlS::COMP);
}
SqlSet operator-(const SqlSet& s1, const SqlSet& s2) {
if(s1.IsEmpty() || s2.IsEmpty())
return s1;
return SqlSet(s1(SqlSet::SET) + SqlCode(MSSQL|PGSQL|SQLITE3, " except ")(" minus ") + s2(SqlSet::SET), SqlSet::SETOP);
}
