uim.sql.tables.select source code

1 module uim.sql.tables.select;
2 
3 import uim.sql;
4 
5 string sqlSelectFromTable(string tableName, string attributes = "*") { return "SELECT "~attributes~" FROM "~tableName; }
6 string sqlWhere(string condition) { return "WHERE "~condition; }
7 string sqlLimit(string limit) { return "LIMIT "~limit; }
8 string sqlLimit(size_t limit) { return "LIMIT "~to!string(limit); }
9 
10 class DSQLSelect : DSQLQueryStatement {
11 	protected string _columns;
12 	protected string _table;
13 	protected string _where;
14 	protected string _orderBy;
15 	protected string _groupBy;
16 	protected size_t _limit;
17 	protected size_t _offset;
18 
19 	this() { } 
20 	this(string attributes, string from = "", string condition = "") { this(); columns(attributes).table(from).where(condition); } 
21 	this(string[] attributes, string from = "", string condition = "") { this(); columns(attributes).table(from).where(condition); }
22 
23     auto clear() { 
24 		_columns = null;
25 		_table = null;
26 		_where = null;
27 		_orderBy = null;
28 		_groupBy = null;
29 		_limit = 0;
30 		_offset = 0;
31 		return this;
32 	}
33 	auto columns(string[] attributes...) {
34 		return columns(attributes);
35 	} 
36 	auto columns(string[] attributes) {
37 		_columns = attributes.join(",");
38 		if (_columns.length == 0) _columns = "*";
39 		writeln("Columns = ", _columns);
40 
41 		return this;
42 	} 
43 	auto table(string table) {
44 		if (table.length > 0) _table = table;
45 		return this;
46 	} 
47 	auto where(string condition = null) {
48 		if (condition.length > 0) _where = condition;
49 		return this;
50 	} 
51 	auto where(string[] conditions) {
52 		if (conditions.length > 0) _where = AND(conditions);
53 		return this;
54 	} 
55 	auto orderBy(string columns = null) {
56 		if (columns.length > 0) _orderBy = columns;
57 		return this;
58 	} 
59 	auto orderBy(string[] columns) {
60 		if (columns.length > 0) _orderBy = columns.join(",");
61 		return this;
62 	} 
63 	auto groupBy(string columns = null) {
64 		if (columns.length > 0) _groupBy = columns;
65 		return this;
66 	} 
67 	auto groupBy(string[] columns) {
68 		if (columns.length > 0) _groupBy = columns.join(",");
69 		return this;
70 	} 
71 	auto limit(size_t value) {
72 		if (value > 0) _limit = value;
73 		return this;
74 	} 
75 	auto offset(size_t value) {
76 		if (value > 0) _offset = value;
77 		return this;
78 	} 
79 	// auto opCall(Database db) {
80 		// auto sql = toSQL;
81 		// writeln(sql);
82 		// return db.execute(sql);
83 	// }
84 
85 	override string toSQL() {
86 		if (_columns.length == 0) _columns = "*";
87 		string sql = "SELECT "~_columns~" FROM "~_table;
88 		if (_where.length > 0) sql ~= " WHERE "~_where;
89 		if (_orderBy.length > 0) sql ~= " ORDER BY "~_orderBy;
90 		if (_groupBy.length > 0) sql ~= " GROUP BY "~_groupBy;
91 		if (_limit > 0) sql ~= " LIMIT %s".format(_limit);
92 		if (_offset > 0) sql ~= " OFFSET %s".format(_offset);
93 		return sql;
94 	}
95 }
96 auto SQLSelect() { return new DSQLSelect(); } 
97 auto SQLSelect(string attributes, string from = "", string where = "") { return new DSQLSelect(attributes, from, where); } 
98 auto SQLSelect(string[] attributes, string from = "", string where = "") { return new DSQLSelect(attributes, from, where); } 
99 
100 unittest {
101 	writeln("Testing ", __MODULE__);
102 
103 	assert(sqlSelectFromTable("aTable") == "SELECT * FROM aTable");
104 	assert(sqlSelectFromTable("aTable", "a, b") == "SELECT a, b FROM aTable");
105 
106 	assert(SQLSelect.table("table") == "SELECT * FROM table");
107 
108 	assert(SQLSelect.table("table").columns("xxx") == "SELECT xxx FROM table");
109 	assert(SQLSelect.table("table").columns("") == "SELECT * FROM table");
110 	assert(SQLSelect.table("table").columns("*") == "SELECT * FROM table");
111 }