1 2 3 module mars.alasql; 4 5 import std.algorithm; 6 import std.array; 7 import std.conv; 8 import std.format; 9 import std.range; 10 11 import mars.defs; 12 13 string selectFrom(const(Table) table) 14 { 15 return "select * from %s".format(table.name); 16 } 17 unittest { 18 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[],[]).selectFrom; 19 assert( sql == "select * from bar", sql ); 20 } 21 22 string selectFromWhere(const(Table) table) 23 { 24 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 25 return "select * from %s where %s".format( 26 table.name, 27 cols.map!( (c) => c.name ~ " = $key" ~ c.name).join(" AND "), 28 ); 29 } 30 unittest { 31 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[0],[]).selectFromWhere; 32 assert( sql == "select * from bar where foo = $keyfoo", sql ); 33 } 34 35 string insertIntoParameter(const(Table) table) 36 { 37 auto columns = table.decorateRows? table.decoratedCols : table.columns; 38 return "insert into %s (%s) values (%s)" 39 .format(table.name, columns.map!( (c) => c.name).join(", "), columns.map!( (c) => "$" ~ c.name).join(", ")); 40 } 41 unittest { 42 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[],[]).insertIntoParameter; 43 assert( sql == "insert into bar (foo, baz) values ($foo, $baz)", sql ); 44 } 45 46 string updateParameter(const(Table) table) 47 { 48 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 49 return "update %s set %s where %s".format( 50 table.name, 51 table.columns.map!( (c) => c.name ~ " = $" ~ c.name).join(", "), 52 cols.map!( (c) => c.name ~ " = $key" ~ c.name).join(" AND "), 53 ); 54 } 55 unittest { 56 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[0],[]).updateParameter; 57 assert( sql == "update bar set foo = $foo, baz = $baz where foo = $keyfoo", sql ); 58 auto sql2 = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[],[]).updateParameter; 59 assert( sql2 == "update bar set foo = $foo, baz = $baz where foo = $keyfoo AND baz = $keybaz", sql2 ); 60 } 61 62 string updateDecorationsParameter(const(Table) table) 63 { 64 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 65 return "update %s set %s where %s".format( 66 table.name, 67 ["mars_who = $mars_who", "mars_what = $mars_what", "mars_when = $mars_when"].join(", "), 68 cols.map!( (c) => c.name ~ " = $key" ~ c.name).join(" AND "), 69 ); 70 } 71 unittest { 72 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false), Col("bak", Type.text)],[0, 1],[]).updateDecorationsParameter; 73 assert( sql == "update bar set mars_who = $mars_who, mars_what = $mars_what, mars_when = $mars_when where foo = $keyfoo AND baz = $keybaz", sql ); 74 } 75 76 string updateDecoratedRecord(const(Table) table) 77 { 78 auto pkKeys = table.pkCols.length >0? table.pkCols : table.columns; 79 auto cols = table.columns.map!"a.name".array ~ ["mars_who", "mars_what", "mars_when"]; 80 return "update %s set %s where %s".format( 81 table.name, 82 cols.map!( (c) => c ~ " = $" ~ c).join(", "), 83 pkKeys.map!( (c) => c.name ~ " = $key" ~ c.name).join(" AND "), 84 ); 85 } 86 unittest { 87 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false), Col("bak", Type.text)],[0, 1],[]).updateDecoratedRecord; 88 assert( sql == "update bar set foo = $foo, baz = $baz, bak = $bak, mars_who = $mars_who, mars_what = $mars_what, mars_when = $mars_when where foo = $keyfoo AND baz = $keybaz", sql ); 89 } 90 91 string deleteFromParameter(const(Table) table) 92 { 93 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 94 return "delete from %s where %s".format( 95 table.name, 96 cols.map!( (c) => c.name ~ " = $key" ~ c.name).join(" AND "), 97 ); 98 } 99 unittest { 100 auto sql = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[0],[]).deleteFromParameter; 101 assert( sql == "delete from bar where foo = $keyfoo", sql ); 102 } 103 104 string pkValuesJs(const(Table) table) 105 { 106 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 107 return "(function a(r){ return { %s }; })".format( 108 cols.map!( (c) => c.name ~ ": r." ~ c.name).join(", "), 109 ); 110 } 111 unittest { 112 auto js = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[0],[]).pkValuesJs; 113 assert( js == "(function a(r){ return { foo: r.foo }; })", js); 114 } 115 116 string pkValuesWhereJs(const(Table) table) 117 { 118 auto cols = table.pkCols.length >0? table.pkCols : table.columns; 119 return "(function a(r){ return { %s }; })".format( 120 cols.map!( (c) => "key" ~ c.name ~ ": r." ~ c.name).join(", "), 121 ); 122 } 123 unittest { 124 auto js = Table("bar", [Col("foo", Type.text, false), Col("baz", Type.text, false)],[0],[]).pkValuesWhereJs; 125 assert( js == "(function a(r){ return { keyfoo: r.foo }; })", js); 126 } 127 128 string referenceJs(const(Table) table, const(Schema) schema) 129 { 130 auto references = table.references; 131 string[] rrr; 132 foreach(reference; references){ 133 auto referencedTable = schema.tableNamed(reference.referencedTable); 134 rrr ~= ["{ referenceCols: [%s], referencedTable: '%s', referencedIndex: %d, referencedCols: [%s] }".format( 135 reference.referenceCols.map!((i) => "'" ~ table.columns[i].name ~ "'").join(", "), 136 reference.referencedTable, 137 referencedTable.index, 138 reference.referencedCols.map!((i) => "'" ~ referencedTable.columns[i].name ~ "'").join(", ") 139 )]; 140 } 141 string sss = "(function a(c) { return [" ~ rrr.join(", ") ~ "]; })"; 142 return sss; 143 } 144 unittest { 145 enum sc = Schema("testschema", [ 146 immutable Table("bar1", [Col("foo", Type.text, false), Col("poo", Type.text, false)], [0, 1], [] ), 147 immutable Table("bar2", [Col("foo", Type.text, false), Col("poo", Type.text, false)], [], [Reference([0,1], "bar1", [0,1])]), 148 ]); 149 enum eee = referenceJs(sc.tables[1], sc); 150 static assert( eee == "(function a(c) { return [{ referenceCols: ['foo', 'poo'], referencedTable: 'bar1', referencedIndex: 0, referencedCols: ['foo', 'poo'] }]; })", eee); 151 } 152 153 154 string createDatabase(const(Schema) schema) 155 { 156 return schema.tables.map!( t => createTable(schema, t) )().join("; "); 157 } 158 159 /** 160 * Passing the schema is necessary for being able to refer to foreign table columns names. 161 */ 162 string createTable(const(Schema) schema, const(Table) table) 163 { 164 165 auto primaryKey(T)(T t){ 166 ulong i = t.index; 167 string v = t.value; 168 169 string postfix = ""; 170 if( canFind(table.primaryKey, i) ) // table.primaryKey.length ==1 && table.primaryKey[0] == i ) 171 postfix = " primary key"; 172 return typeof(t)(i, v ~ postfix); 173 } 174 175 string references(T)(T t){ 176 string r = t.value; 177 ulong i = t.index; 178 179 foreach(reference; table.references){ 180 // ... alasql 0.3.6 does not support references with multiple columns, so skip them. 181 if( reference.referenceCols.length > 1) continue; 182 183 assert(reference.referenceCols.length == 1); // only one col in alasql references 184 assert(reference.referencedCols.length == 1); // idem 185 if( reference.referenceCols[0] == i ){ 186 r ~= " references " ~ reference.referencedTable ~ "("; 187 import std.algorithm : find; import std.range : front; 188 auto tbs = schema.tables.find!( t => t.name == reference.referencedTable ); 189 if( tbs.empty ) assert(false, "table "~ table.name ~ " reference table " ~ reference.referencedTable ~ " that does not exists"); 190 auto tb = tbs.front; 191 r ~= tb.columns[reference.referencedCols[0]].name ~ ")"; 192 } 193 } 194 return r; 195 } 196 197 string cols = (table.decorateRows? table.decoratedCols : table.columns) 198 .map!( (c){ return c.asNameTypeNull; }) 199 .ctfeEnumerate 200 //.map!(primaryKey!(EnumerateResult!(ulong, string))) 201 .map!(references!(EnumerateResult!(ulong, string))) 202 .join(", "); 203 204 205 string primaryKeys = ""; 206 if( table.primaryKey.length > 0 ){ 207 primaryKeys = ", primary key (" ~ table.primaryKey.map!( i => table.columns[i].name ).join(", ") ~ ")"; 208 } 209 210 string sql = "create table " ~ table.name ~ " (" ~ cols ~ primaryKeys ~ ")"; 211 return sql; 212 213 } 214 unittest { 215 auto sc = Schema("testschema", [ 216 immutable Table("bar1", [ Col("foo", Type.text, false) ], [], [] ), 217 immutable Table("bar2", [Col("foo", Type.text, false), Col("poo", Type.text, false)], [1], []), 218 immutable Table("bar3", [Col("foo", Type.text, false)], [], [Reference([0], "bar2", [1])]), 219 immutable Table("bar4", [Col("foo", Type.text, false), Col("bar", Type.text, false)], [], [Reference([0,1], "bar1", [0,1])]), 220 immutable Table("bar5", [Col("foo", Type.text, false), Col("bar", Type.text, false)], [0, 1], []), 221 immutable Table("bar6", [Col("foo", Type.text, false), Col("bar", Type.text, false)], [0, 1], [], 6, Yes.durable, Yes.decorateRows), 222 ]); 223 224 string sql = sc.createTable(sc.tables[0]); 225 assert(sql == "create table bar1 (foo text not null)", sql); 226 sql = sc.createTable(sc.tables[1]); 227 assert(sql == "create table bar2 (foo text not null, poo text not null, primary key (poo))", sql); 228 sql = sc.createTable(sc.tables[2]); 229 assert(sql == "create table bar3 (foo text not null references bar2(poo))", sql); 230 sql = sc.createTable(sc.tables[3]); 231 assert(sql == "create table bar4 (foo text not null, bar text not null)", sql); 232 sql = sc.createTable(sc.tables[4]); 233 assert(sql == "create table bar5 (foo text not null, bar text not null, primary key (foo, bar))", sql); 234 sql = sc.createTable(sc.tables[5]); 235 assert(sql == "create table bar6 (foo text not null, bar text not null, mars_who text not null, mars_what text not null, mars_when text not null, primary key (foo, bar))", sql); 236 237 } 238 239 string asNameTypeNull(const(Col) col) 240 { 241 return col.name ~ " " ~ col.type.toSql ~ (col.null_? "" : " not null"); 242 } 243 unittest { 244 assert( Col("foo", Type.text, false).asNameTypeNull == "foo text not null" ); 245 } 246 247 /** 248 * See_Also: https://github.com/agershun/alasql/wiki/Data%20Types 249 */ 250 string toSql(Type t){ 251 import std.conv : to; 252 253 final switch(t) with(Type) { 254 case boolean: return "boolean"; 255 case integer: return "integer"; 256 case bigint: return "integer"; 257 case smallint: return "smallint"; 258 case text: return "text"; 259 case real_: return "real"; 260 case doublePrecision: return "double precision"; 261 // ... this is not really supported by alasql, as in javascript we are using 'Buffer', but actually the javascript code 262 // doesn't perform any check for unknown type, so ... 263 case bytea: return "bytea"; 264 265 // ... the mars client library is handling directly the 'autoincrement' alasql type, as we need to 266 // reconcile the optimistic updated serial with the server ... 267 case serial: return "smallint"; 268 case smallserial: return "integer"; 269 270 // ... temptative: actually I'm not using date in 'cached' tables, so it's just a matter of avoiding that 271 case date: return "text"; 272 273 case unknown: 274 case varchar: // varchar(n), tbd as column 275 assert(false, t.to!string); // not implemented right now, catch at CT 276 } 277 } 278 unittest { 279 assert( Type.text.toSql == "text" ); 280 assert( Type.integer.toSql == "integer" ); 281 } 282 283 private 284 { 285 // BUG see https://issues.dlang.org/show_bug.cgi?id=15064 286 struct EnumerateResult(I,V) { I index; V value; } 287 struct enumerator(R) { 288 import std.range : ElementType; 289 R r; 290 ulong j = 0; 291 @property EnumerateResult!(ulong, ElementType!R) front() { return EnumerateResult!(ulong, ElementType!R)(j, r.front); } 292 auto empty() { return r.empty; } 293 void popFront() { j++; r.popFront(); } 294 } 295 auto ctfeEnumerate(R)(R r){ return enumerator!R(r); } 296 }