1 /**
2  * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 
3  * 
4  * Source file ddbc/core.d.
5  *
6  * DDBC library attempts to provide implementation independent interface to different databases.
7  * 
8  * Set of supported RDBMSs can be extended by writing Drivers for particular DBs.
9  * Currently it only includes MySQL Driver which uses patched version of MYSQLN (native D implementation of MySQL connector, written by Steve Teale)
10  * 
11  * JDBC documentation can be found here:
12  * $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR)
13  *
14  * Limitations of current version: readonly unidirectional resultset, completely fetched into memory.
15  * 
16  * Its primary objects are:
17  * $(UL
18  *    $(LI Driver: $(UL $(LI Implements interface to particular RDBMS, used to create connections)))
19  *    $(LI Connection: $(UL $(LI Connection to the server, and querying and setting of server parameters.)))
20  *    $(LI Statement: Handling of general SQL requests/queries/commands, with principal methods:
21  *       $(UL $(LI executeUpdate() - run query which doesn't return result set.)
22  *            $(LI executeQuery() - execute query which returns ResultSet interface to access rows of result.)
23  *        )
24  *    )
25  *    $(LI PreparedStatement: Handling of general SQL requests/queries/commands which having additional parameters, with principal methods:
26  *       $(UL $(LI executeUpdate() - run query which doesn't return result set.)
27  *            $(LI executeQuery() - execute query which returns ResultSet interface to access rows of result.)
28  *            $(LI setXXX() - setter methods to bind parameters.)
29  *        )
30  *    )
31  *    $(LI ResultSet: $(UL $(LI Get result of query row by row, accessing individual fields.)))
32  * )
33  *
34  * You can find usage examples in unittest{} sections.
35  *
36  * Copyright: Copyright 2013
37  * License:   $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
38  * Author:   Vadim Lopatin
39  */
40 module ddbc.core;
41 
42 import std.exception;
43 import std.variant;
44 import std.datetime;
45 
46 class SQLException : Exception {
47     protected string _stateString;
48     this(string msg, string stateString, string f = __FILE__, size_t l = __LINE__) { super(msg, f, l); _stateString = stateString; }
49     this(string msg, string f = __FILE__, size_t l = __LINE__) { super(msg, f, l); }
50     this(Throwable causedBy, string f = __FILE__, size_t l = __LINE__) { super(causedBy.msg, causedBy, f, l); }
51     this(string msg, Throwable causedBy, string f = __FILE__, size_t l = __LINE__) { super(causedBy.msg, causedBy, f, l); }
52     this(string msg, string stateString, Throwable causedBy, string f = __FILE__, size_t l = __LINE__) { super(causedBy.msg, causedBy, f, l); _stateString = stateString; }
53 }
54 
55 class SQLWarning {
56     // stub
57 }
58 
59 /// JDBC java.sql.Types from http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html
60 enum SqlType {
61 	//sometimes referred to as a type code, that identifies the generic SQL type ARRAY.
62 	//ARRAY,
63 	///sometimes referred to as a type code, that identifies the generic SQL type BIGINT.
64 	BIGINT,
65 	///sometimes referred to as a type code, that identifies the generic SQL type BINARY.
66 	//BINARY,
67 	//sometimes referred to as a type code, that identifies the generic SQL type BIT.
68 	BIT,
69 	///sometimes referred to as a type code, that identifies the generic SQL type BLOB.
70 	BLOB,
71 	///somtimes referred to as a type code, that identifies the generic SQL type BOOLEAN.
72 	BOOLEAN,
73 	///sometimes referred to as a type code, that identifies the generic SQL type CHAR.
74 	CHAR,
75 	///sometimes referred to as a type code, that identifies the generic SQL type CLOB.
76 	CLOB,
77 	//somtimes referred to as a type code, that identifies the generic SQL type DATALINK.
78 	//DATALINK,
79 	///sometimes referred to as a type code, that identifies the generic SQL type DATE.
80 	DATE,
81 	///sometimes referred to as a type code, that identifies the generic SQL type DATETIME.
82 	DATETIME,
83 	///sometimes referred to as a type code, that identifies the generic SQL type DECIMAL.
84 	DECIMAL,
85 	//sometimes referred to as a type code, that identifies the generic SQL type DISTINCT.
86 	//DISTINCT,
87 	///sometimes referred to as a type code, that identifies the generic SQL type DOUBLE.
88 	DOUBLE,
89 	///sometimes referred to as a type code, that identifies the generic SQL type FLOAT.
90 	FLOAT,
91 	///sometimes referred to as a type code, that identifies the generic SQL type INTEGER.
92 	INTEGER,
93 	//sometimes referred to as a type code, that identifies the generic SQL type JAVA_OBJECT.
94 	//JAVA_OBJECT,
95 	///sometimes referred to as a type code, that identifies the generic SQL type LONGNVARCHAR.
96 	LONGNVARCHAR,
97 	///sometimes referred to as a type code, that identifies the generic SQL type LONGVARBINARY.
98 	LONGVARBINARY,
99 	///sometimes referred to as a type code, that identifies the generic SQL type LONGVARCHAR.
100 	LONGVARCHAR,
101 	///sometimes referred to as a type code, that identifies the generic SQL type NCHAR
102 	NCHAR,
103 	///sometimes referred to as a type code, that identifies the generic SQL type NCLOB.
104 	NCLOB,
105 	///The constant in the Java programming language that identifies the generic SQL value NULL.
106 	NULL,
107 	///sometimes referred to as a type code, that identifies the generic SQL type NUMERIC.
108 	NUMERIC,
109 	///sometimes referred to as a type code, that identifies the generic SQL type NVARCHAR.
110 	NVARCHAR,
111 	///indicates that the SQL type is database-specific and gets mapped to a object that can be accessed via the methods getObject and setObject.
112 	OTHER,
113 	//sometimes referred to as a type code, that identifies the generic SQL type REAL.
114 	//REAL,
115 	//sometimes referred to as a type code, that identifies the generic SQL type REF.
116 	//REF,
117 	//sometimes referred to as a type code, that identifies the generic SQL type ROWID
118 	//ROWID,
119 	///sometimes referred to as a type code, that identifies the generic SQL type SMALLINT.
120 	SMALLINT,
121 	//sometimes referred to as a type code, that identifies the generic SQL type XML.
122 	//SQLXML,
123 	//sometimes referred to as a type code, that identifies the generic SQL type STRUCT.
124 	//STRUCT,
125 	///sometimes referred to as a type code, that identifies the generic SQL type TIME.
126 	TIME,
127 	//sometimes referred to as a type code, that identifies the generic SQL type TIMESTAMP.
128 	//TIMESTAMP,
129 	///sometimes referred to as a type code, that identifies the generic SQL type TINYINT.
130 	TINYINT,
131 	///sometimes referred to as a type code, that identifies the generic SQL type VARBINARY.
132 	VARBINARY,
133 	///sometimes referred to as a type code, that identifies the generic SQL type VARCHAR.
134 	VARCHAR,
135 }
136 
137 interface Connection {
138 	/// Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
139 	void close();
140 	/// Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.
141 	void commit();
142 	/// Retrieves this Connection object's current catalog name.
143 	string getCatalog();
144 	/// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work.
145 	void setCatalog(string catalog);
146 	/// Retrieves whether this Connection object has been closed.
147 	bool isClosed();
148 	/// Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
149 	void rollback();
150 	/// Retrieves the current auto-commit mode for this Connection object.
151 	bool getAutoCommit();
152 	/// Sets this connection's auto-commit mode to the given state.
153 	void setAutoCommit(bool autoCommit);
154 	// statements
155 	/// Creates a Statement object for sending SQL statements to the database.
156 	Statement createStatement();
157 	/// Creates a PreparedStatement object for sending parameterized SQL statements to the database.
158 	PreparedStatement prepareStatement(string query);
159 }
160 
161 interface ResultSetMetaData {
162 	//Returns the number of columns in this ResultSet object.
163 	int getColumnCount();
164 
165 	// Gets the designated column's table's catalog name.
166 	string getCatalogName(int column);
167 	// Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.
168 	//string getColumnClassName(int column);
169 	// Indicates the designated column's normal maximum width in characters.
170 	int getColumnDisplaySize(int column);
171 	// Gets the designated column's suggested title for use in printouts and displays.
172 	string getColumnLabel(int column);
173 	// Get the designated column's name.
174 	string getColumnName(int column);
175 	// Retrieves the designated column's SQL type.
176 	int getColumnType(int column);
177 	// Retrieves the designated column's database-specific type name.
178 	string getColumnTypeName(int column);
179 	// Get the designated column's number of decimal digits.
180 	int getPrecision(int column);
181 	// Gets the designated column's number of digits to right of the decimal point.
182 	int getScale(int column);
183 	// Get the designated column's table's schema.
184 	string getSchemaName(int column);
185 	// Gets the designated column's table name.
186 	string getTableName(int column);
187 	// Indicates whether the designated column is automatically numbered, thus read-only.
188 	bool isAutoIncrement(int column);
189 	// Indicates whether a column's case matters.
190 	bool isCaseSensitive(int column);
191 	// Indicates whether the designated column is a cash value.
192 	bool isCurrency(int column);
193 	// Indicates whether a write on the designated column will definitely succeed.
194 	bool isDefinitelyWritable(int column);
195 	// Indicates the nullability of values in the designated column.
196 	int isNullable(int column);
197 	// Indicates whether the designated column is definitely not writable.
198 	bool isReadOnly(int column);
199 	// Indicates whether the designated column can be used in a where clause.
200 	bool isSearchable(int column);
201 	// Indicates whether values in the designated column are signed numbers.
202 	bool isSigned(int column);
203 	// Indicates whether it is possible for a write on the designated column to succeed.
204 	bool isWritable(int column);
205 }
206 
207 interface ParameterMetaData {
208 	// Retrieves the fully-qualified name of the Java class whose instances should be passed to the method PreparedStatement.setObject.
209 	//String getParameterClassName(int param);
210 	/// Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information.
211 	int getParameterCount();
212 	/// Retrieves the designated parameter's mode.
213 	int getParameterMode(int param);
214 	/// Retrieves the designated parameter's SQL type.
215 	int getParameterType(int param);
216 	/// Retrieves the designated parameter's database-specific type name.
217 	string getParameterTypeName(int param);
218 	/// Retrieves the designated parameter's number of decimal digits.
219 	int getPrecision(int param);
220 	/// Retrieves the designated parameter's number of digits to right of the decimal point.
221 	int getScale(int param);
222 	/// Retrieves whether null values are allowed in the designated parameter.
223 	int isNullable(int param);
224 	/// Retrieves whether values for the designated parameter can be signed numbers.
225 	bool isSigned(int param);
226 }
227 
228 interface DataSetReader {
229 	bool getBoolean(int columnIndex);
230 	ubyte getUbyte(int columnIndex);
231 	ubyte[] getUbytes(int columnIndex);
232 	byte[] getBytes(int columnIndex);
233 	byte getByte(int columnIndex);
234 	short getShort(int columnIndex);
235 	ushort getUshort(int columnIndex);
236 	int getInt(int columnIndex);
237 	uint getUint(int columnIndex);
238 	long getLong(int columnIndex);
239 	ulong getUlong(int columnIndex);
240 	double getDouble(int columnIndex);
241 	float getFloat(int columnIndex);
242 	string getString(int columnIndex);
243 	DateTime getDateTime(int columnIndex);
244 	Date getDate(int columnIndex);
245 	TimeOfDay getTime(int columnIndex);
246 	Variant getVariant(int columnIndex);
247 	bool isNull(int columnIndex);
248 	bool wasNull();
249 }
250 
251 interface DataSetWriter {
252 	void setFloat(int parameterIndex, float x);
253 	void setDouble(int parameterIndex, double x);
254 	void setBoolean(int parameterIndex, bool x);
255 	void setLong(int parameterIndex, long x);
256 	void setInt(int parameterIndex, int x);
257 	void setShort(int parameterIndex, short x);
258 	void setByte(int parameterIndex, byte x);
259 	void setBytes(int parameterIndex, byte[] x);
260 	void setUlong(int parameterIndex, ulong x);
261 	void setUint(int parameterIndex, uint x);
262 	void setUshort(int parameterIndex, ushort x);
263 	void setUbyte(int parameterIndex, ubyte x);
264 	void setUbytes(int parameterIndex, ubyte[] x);
265 	void setString(int parameterIndex, string x);
266 	void setDateTime(int parameterIndex, DateTime x);
267 	void setDate(int parameterIndex, Date x);
268 	void setTime(int parameterIndex, TimeOfDay x);
269 	void setVariant(int columnIndex, Variant x);
270 
271 	void setNull(int parameterIndex);
272 	void setNull(int parameterIndex, int sqlType);
273 }
274 
275 interface ResultSet : DataSetReader {
276 	void close();
277 	bool first();
278 	bool isFirst();
279 	bool isLast();
280 	bool next();
281 
282 	//Retrieves the number, types and properties of this ResultSet object's columns
283 	ResultSetMetaData getMetaData();
284 	//Retrieves the Statement object that produced this ResultSet object.
285 	Statement getStatement();
286 	//Retrieves the current row number
287 	int getRow();
288 	//Retrieves the fetch size for this ResultSet object.
289 	int getFetchSize();
290 
291 	// from DataSetReader
292 	bool getBoolean(int columnIndex);
293 	ubyte getUbyte(int columnIndex);
294 	ubyte[] getUbytes(int columnIndex);
295 	byte[] getBytes(int columnIndex);
296 	byte getByte(int columnIndex);
297 	short getShort(int columnIndex);
298 	ushort getUshort(int columnIndex);
299 	int getInt(int columnIndex);
300 	uint getUint(int columnIndex);
301 	long getLong(int columnIndex);
302 	ulong getUlong(int columnIndex);
303 	double getDouble(int columnIndex);
304 	float getFloat(int columnIndex);
305 	string getString(int columnIndex);
306     Variant getVariant(int columnIndex);
307 
308     bool isNull(int columnIndex);
309 	bool wasNull();
310 
311 	// additional methods
312 	int findColumn(string columnName);
313 	bool getBoolean(string columnName);
314 	ubyte getUbyte(string columnName);
315 	ubyte[] getUbytes(string columnName);
316 	byte[] getBytes(string columnName);
317 	byte getByte(string columnName);
318 	short getShort(string columnName);
319 	ushort getUshort(string columnName);
320 	int getInt(string columnName);
321 	uint getUint(string columnName);
322 	long getLong(string columnName);
323 	ulong getUlong(string columnName);
324 	double getDouble(string columnName);
325 	float getFloat(string columnName);
326     string getString(string columnName);
327 	DateTime getDateTime(int columnIndex);
328 	Date getDate(int columnIndex);
329 	TimeOfDay getTime(int columnIndex);
330 	Variant getVariant(string columnName);
331 
332     /// to iterate through all rows in result set
333     int opApply(int delegate(DataSetReader) dg);
334 
335 }
336 
337 interface Statement {
338 	ResultSet executeQuery(string query);
339 	int executeUpdate(string query);
340 	int executeUpdate(string query, out Variant insertId);
341 	void close();
342 }
343 
344 /// An object that represents a precompiled SQL statement. 
345 interface PreparedStatement : Statement, DataSetWriter {
346 	/// Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
347 	int executeUpdate();
348 	/// Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
349 	int executeUpdate(out Variant insertId);
350 	/// Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
351 	ResultSet executeQuery();
352 
353 	/// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.
354 	ResultSetMetaData getMetaData();
355 	/// Retrieves the number, types and properties of this PreparedStatement object's parameters.
356 	ParameterMetaData getParameterMetaData();
357 	/// Clears the current parameter values immediately.
358 	void clearParameters();
359 
360 	// from DataSetWriter
361 	void setFloat(int parameterIndex, float x);
362 	void setDouble(int parameterIndex, double x);
363 	void setBoolean(int parameterIndex, bool x);
364 	void setLong(int parameterIndex, long x);
365 	void setInt(int parameterIndex, int x);
366 	void setShort(int parameterIndex, short x);
367 	void setByte(int parameterIndex, byte x);
368 	void setBytes(int parameterIndex, byte[] x);
369 	void setUlong(int parameterIndex, ulong x);
370 	void setUint(int parameterIndex, uint x);
371 	void setUshort(int parameterIndex, ushort x);
372 	void setUbyte(int parameterIndex, ubyte x);
373 	void setUbytes(int parameterIndex, ubyte[] x);
374 	void setString(int parameterIndex, string x);
375 	void setDateTime(int parameterIndex, DateTime x);
376 	void setDate(int parameterIndex, Date x);
377 	void setTime(int parameterIndex, TimeOfDay x);
378 	void setVariant(int parameterIndex, Variant x);
379 
380 	void setNull(int parameterIndex);
381 	void setNull(int parameterIndex, int sqlType);
382 }
383 
384 interface Driver {
385 	Connection connect(string url, string[string] params);
386 }
387 
388 interface DataSource {
389 	Connection getConnection();
390 }
391 
392 /// Helper function to make url in form driverName://host:port/dbname?param1=value1,param2=value2
393 string makeDDBCUrl(string driverName, string host, int port, string dbName, string[string] params = null) {
394     import std.conv : to;
395     char[] res;
396     res.assumeSafeAppend;
397     res ~= driverName;
398     res ~= "://";
399     res ~= host;
400     res ~= ":";
401     res ~= to!string(port);
402     res ~= "/";
403     res ~= dbName;
404     bool firstParam = true;
405     foreach(key, value; params) {
406         if (firstParam) {
407             res ~= "?";
408             firstParam = false;
409         } else {
410             res ~= ",";
411         }
412         res ~= key;
413         res ~= "=";
414         res ~= value;
415     }
416     return res.dup;
417 }
418