mars.alasql source code

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