1 package goqu 2 3 import ( 4 "github.com/doug-martin/goqu/v9/exp" 5 ) 6 7 type ( 8 Expression = exp.Expression 9 Ex = exp.Ex 10 ExOr = exp.ExOr 11 Op = exp.Op 12 Record = exp.Record 13 Vals = exp.Vals 14 // Options to use when generating a TRUNCATE statement 15 TruncateOptions = exp.TruncateOptions 16 ) 17 18 // emptyWindow is an empty WINDOW clause without name 19 var emptyWindow = exp.NewWindowExpression(nil, nil, nil, nil) 20 21 const ( 22 Wait = exp.Wait 23 NoWait = exp.NoWait 24 SkipLocked = exp.SkipLocked 25 ) 26 27 // Creates a new Casted expression 28 // Cast(I("a"), "NUMERIC") -> CAST("a" AS NUMERIC) 29 func Cast(e exp.Expression, t string) exp.CastExpression { 30 return exp.NewCastExpression(e, t) 31 } 32 33 // Creates a conflict struct to be passed to InsertConflict to ignore constraint errors 34 // InsertConflict(DoNothing(),...) -> INSERT INTO ... ON CONFLICT DO NOTHING 35 func DoNothing() exp.ConflictExpression { 36 return exp.NewDoNothingConflictExpression() 37 } 38 39 // Creates a ConflictUpdate struct to be passed to InsertConflict 40 // Represents a ON CONFLICT DO UPDATE portion of an INSERT statement (ON DUPLICATE KEY UPDATE for mysql) 41 // 42 // InsertConflict(DoUpdate("target_column", update),...) -> 43 // INSERT INTO ... ON CONFLICT DO UPDATE SET a=b 44 // InsertConflict(DoUpdate("target_column", update).Where(Ex{"a": 1},...) -> 45 // INSERT INTO ... ON CONFLICT DO UPDATE SET a=b WHERE a=1 46 func DoUpdate(target string, update interface{}) exp.ConflictUpdateExpression { 47 return exp.NewDoUpdateConflictExpression(target, update) 48 } 49 50 // A list of expressions that should be ORed together 51 // Or(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) OR ("b" = 11)) 52 func Or(expressions ...exp.Expression) exp.ExpressionList { 53 return exp.NewExpressionList(exp.OrType, expressions...) 54 } 55 56 // A list of expressions that should be ANDed together 57 // And(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) AND ("b" = 11)) 58 func And(expressions ...exp.Expression) exp.ExpressionList { 59 return exp.NewExpressionList(exp.AndType, expressions...) 60 } 61 62 // Creates a new SQLFunctionExpression with the given name and arguments 63 func Func(name string, args ...interface{}) exp.SQLFunctionExpression { 64 return exp.NewSQLFunctionExpression(name, args...) 65 } 66 67 // used internally to normalize the column name if passed in as a string it should be turned into an identifier 68 func newIdentifierFunc(name string, col interface{}) exp.SQLFunctionExpression { 69 if s, ok := col.(string); ok { 70 col = I(s) 71 } 72 return Func(name, col) 73 } 74 75 // Creates a new DISTINCT sql function 76 // DISTINCT("a") -> DISTINCT("a") 77 // DISTINCT(I("a")) -> DISTINCT("a") 78 func DISTINCT(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("DISTINCT", col) } 79 80 // Creates a new COUNT sql function 81 // COUNT("a") -> COUNT("a") 82 // COUNT("*") -> COUNT("*") 83 // COUNT(I("a")) -> COUNT("a") 84 func COUNT(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("COUNT", col) } 85 86 // Creates a new MIN sql function 87 // MIN("a") -> MIN("a") 88 // MIN(I("a")) -> MIN("a") 89 func MIN(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("MIN", col) } 90 91 // Creates a new MAX sql function 92 // MAX("a") -> MAX("a") 93 // MAX(I("a")) -> MAX("a") 94 func MAX(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("MAX", col) } 95 96 // Creates a new AVG sql function 97 // AVG("a") -> AVG("a") 98 // AVG(I("a")) -> AVG("a") 99 func AVG(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("AVG", col) } 100 101 // Creates a new FIRST sql function 102 // FIRST("a") -> FIRST("a") 103 // FIRST(I("a")) -> FIRST("a") 104 func FIRST(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("FIRST", col) } 105 106 // Creates a new LAST sql function 107 // LAST("a") -> LAST("a") 108 // LAST(I("a")) -> LAST("a") 109 func LAST(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("LAST", col) } 110 111 // Creates a new SUM sql function 112 // SUM("a") -> SUM("a") 113 // SUM(I("a")) -> SUM("a") 114 func SUM(col interface{}) exp.SQLFunctionExpression { return newIdentifierFunc("SUM", col) } 115 116 // Creates a new COALESCE sql function 117 // COALESCE(I("a"), "a") -> COALESCE("a", 'a') 118 // COALESCE(I("a"), I("b"), nil) -> COALESCE("a", "b", NULL) 119 func COALESCE(vals ...interface{}) exp.SQLFunctionExpression { 120 return Func("COALESCE", vals...) 121 } 122 123 //nolint:stylecheck,golint // sql function name 124 func ROW_NUMBER() exp.SQLFunctionExpression { 125 return Func("ROW_NUMBER") 126 } 127 128 func RANK() exp.SQLFunctionExpression { 129 return Func("RANK") 130 } 131 132 //nolint:stylecheck,golint // sql function name 133 func DENSE_RANK() exp.SQLFunctionExpression { 134 return Func("DENSE_RANK") 135 } 136 137 //nolint:stylecheck,golint // sql function name 138 func PERCENT_RANK() exp.SQLFunctionExpression { 139 return Func("PERCENT_RANK") 140 } 141 142 //nolint:stylecheck,golint //sql function name 143 func CUME_DIST() exp.SQLFunctionExpression { 144 return Func("CUME_DIST") 145 } 146 147 func NTILE(n int) exp.SQLFunctionExpression { 148 return Func("NTILE", n) 149 } 150 151 //nolint:stylecheck,golint //sql function name 152 func FIRST_VALUE(val interface{}) exp.SQLFunctionExpression { 153 return newIdentifierFunc("FIRST_VALUE", val) 154 } 155 156 //nolint:stylecheck,golint //sql function name 157 func LAST_VALUE(val interface{}) exp.SQLFunctionExpression { 158 return newIdentifierFunc("LAST_VALUE", val) 159 } 160 161 //nolint:stylecheck,golint //sql function name 162 func NTH_VALUE(val interface{}, nth int) exp.SQLFunctionExpression { 163 if s, ok := val.(string); ok { 164 val = I(s) 165 } 166 return Func("NTH_VALUE", val, nth) 167 } 168 169 // Creates a new Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case 170 // sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table"). 171 // 172 // The identifier will be split by '.' 173 // 174 // Table and Column example 175 // I("table.column") -> "table"."column" //A Column and table 176 // Schema table and column 177 // I("schema.table.column") -> "schema"."table"."column" 178 // Table with star 179 // I("table.*") -> "table".* 180 func I(ident string) exp.IdentifierExpression { 181 return exp.ParseIdentifier(ident) 182 } 183 184 // Creates a new Column Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case 185 // sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table"). 186 // An Identifier can represent a one or a combination of schema, table, and/or column. 187 // C("column") -> "column" //A Column 188 // C("column").Table("table") -> "table"."column" //A Column and table 189 // C("column").Table("table").Schema("schema") //Schema table and column 190 // C("*") //Also handles the * operator 191 func C(col string) exp.IdentifierExpression { 192 return exp.NewIdentifierExpression("", "", col) 193 } 194 195 // Creates a new Schema Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case 196 // sensitivity and in certain databases allows for special characters, (e.g. "curr-schema", "my schema"). 197 // S("schema") -> "schema" //A Schema 198 // S("schema").Table("table") -> "schema"."table" //A Schema and table 199 // S("schema").Table("table").Col("col") //Schema table and column 200 // S("schema").Table("table").Col("*") //Schema table and all columns 201 func S(schema string) exp.IdentifierExpression { 202 return exp.NewIdentifierExpression(schema, "", "") 203 } 204 205 // Creates a new Table Identifier, the generated sql will use adapter specific quoting or '"' by default, this ensures case 206 // sensitivity and in certain databases allows for special characters, (e.g. "curr-table", "my table"). 207 // T("table") -> "table" //A Column 208 // T("table").Col("col") -> "table"."column" //A Column and table 209 // T("table").Schema("schema").Col("col) -> "schema"."table"."column" //Schema table and column 210 // T("table").Schema("schema").Col("*") -> "schema"."table".* //Also handles the * operator 211 func T(table string) exp.IdentifierExpression { 212 return exp.NewIdentifierExpression("", table, "") 213 } 214 215 // Create a new WINDOW clause 216 // W() -> () 217 // W().PartitionBy("a") -> (PARTITION BY "a") 218 // W().PartitionBy("a").OrderBy("b") -> (PARTITION BY "a" ORDER BY "b") 219 // W().PartitionBy("a").OrderBy("b").Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b") 220 // W().PartitionBy("a").OrderBy(I("b").Desc()).Inherit("w1") -> ("w1" PARTITION BY "a" ORDER BY "b" DESC) 221 // W("w") -> "w" AS () 222 // W("w", "w1") -> "w" AS ("w1") 223 // W("w").Inherit("w1") -> "w" AS ("w1") 224 // W("w").PartitionBy("a") -> "w" AS (PARTITION BY "a") 225 // W("w", "w1").PartitionBy("a") -> "w" AS ("w1" PARTITION BY "a") 226 // W("w", "w1").PartitionBy("a").OrderBy("b") -> "w" AS ("w1" PARTITION BY "a" ORDER BY "b") 227 func W(ws ...string) exp.WindowExpression { 228 switch len(ws) { 229 case 0: 230 return emptyWindow 231 case 1: 232 return exp.NewWindowExpression(I(ws[0]), nil, nil, nil) 233 default: 234 return exp.NewWindowExpression(I(ws[0]), I(ws[1]), nil, nil) 235 } 236 } 237 238 // Creates a new ON clause to be used within a join 239 // ds.Join(goqu.T("my_table"), goqu.On( 240 // goqu.I("my_table.fkey").Eq(goqu.I("other_table.id")), 241 // )) 242 func On(expressions ...exp.Expression) exp.JoinCondition { 243 return exp.NewJoinOnCondition(expressions...) 244 } 245 246 // Creates a new USING clause to be used within a join 247 // ds.Join(goqu.T("my_table"), goqu.Using("fkey")) 248 func Using(columns ...interface{}) exp.JoinCondition { 249 return exp.NewJoinUsingCondition(columns...) 250 } 251 252 // Creates a new SQL literal with the provided arguments. 253 // L("a = 1") -> a = 1 254 // You can also you placeholders. All placeholders within a Literal are represented by '?' 255 // L("a = ?", "b") -> a = 'b' 256 // Literals can also contain placeholders for other expressions 257 // L("(? AND ?) OR (?)", I("a").Eq(1), I("b").Eq("b"), I("c").In([]string{"a", "b", "c"})) 258 func L(sql string, args ...interface{}) exp.LiteralExpression { 259 return Literal(sql, args...) 260 } 261 262 // Alias for goqu.L 263 func Literal(sql string, args ...interface{}) exp.LiteralExpression { 264 return exp.NewLiteralExpression(sql, args...) 265 } 266 267 // Create a new SQL value ( alias for goqu.L("?", val) ). The prrimary use case for this would be in selects. 268 // See examples. 269 func V(val interface{}) exp.LiteralExpression { 270 return exp.NewLiteralExpression("?", val) 271 } 272 273 // Creates a new Range to be used with a Between expression 274 // exp.C("col").Between(exp.Range(1, 10)) 275 func Range(start, end interface{}) exp.RangeVal { 276 return exp.NewRangeVal(start, end) 277 } 278 279 // Creates a literal * 280 func Star() exp.LiteralExpression { return exp.Star() } 281 282 // Returns a literal for DEFAULT sql keyword 283 func Default() exp.LiteralExpression { 284 return exp.Default() 285 } 286 287 func Lateral(table exp.AppendableExpression) exp.LateralExpression { 288 return exp.NewLateralExpression(table) 289 } 290 291 // Create a new ANY comparison 292 func Any(val interface{}) exp.SQLFunctionExpression { 293 return Func("ANY ", val) 294 } 295 296 // Create a new ALL comparison 297 func All(val interface{}) exp.SQLFunctionExpression { 298 return Func("ALL ", val) 299 } 300 301 func Case() exp.CaseExpression { 302 return exp.NewCaseExpression() 303 } 304