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     this(string msg, string f = __FILE__, size_t l = __LINE__) { super(msg, f, l); }
48     this(Throwable causedBy, string f = __FILE__, size_t l = __LINE__) { super(causedBy.msg, f, l); }
49 }
50 
51 /// JDBC java.sql.Types from http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html
52 enum SqlType {
53 	//sometimes referred to as a type code, that identifies the generic SQL type ARRAY.
54 	//ARRAY,
55 	///sometimes referred to as a type code, that identifies the generic SQL type BIGINT.
56 	BIGINT,
57 	///sometimes referred to as a type code, that identifies the generic SQL type BINARY.
58 	//BINARY,
59 	//sometimes referred to as a type code, that identifies the generic SQL type BIT.
60 	BIT,
61 	///sometimes referred to as a type code, that identifies the generic SQL type BLOB.
62 	BLOB,
63 	///somtimes referred to as a type code, that identifies the generic SQL type BOOLEAN.
64 	BOOLEAN,
65 	///sometimes referred to as a type code, that identifies the generic SQL type CHAR.
66 	CHAR,
67 	///sometimes referred to as a type code, that identifies the generic SQL type CLOB.
68 	CLOB,
69 	//somtimes referred to as a type code, that identifies the generic SQL type DATALINK.
70 	//DATALINK,
71 	///sometimes referred to as a type code, that identifies the generic SQL type DATE.
72 	DATE,
73 	///sometimes referred to as a type code, that identifies the generic SQL type DATETIME.
74 	DATETIME,
75 	///sometimes referred to as a type code, that identifies the generic SQL type DECIMAL.
76 	DECIMAL,
77 	//sometimes referred to as a type code, that identifies the generic SQL type DISTINCT.
78 	//DISTINCT,
79 	///sometimes referred to as a type code, that identifies the generic SQL type DOUBLE.
80 	DOUBLE,
81 	///sometimes referred to as a type code, that identifies the generic SQL type FLOAT.
82 	FLOAT,
83 	///sometimes referred to as a type code, that identifies the generic SQL type INTEGER.
84 	INTEGER,
85 	//sometimes referred to as a type code, that identifies the generic SQL type JAVA_OBJECT.
86 	//JAVA_OBJECT,
87 	///sometimes referred to as a type code, that identifies the generic SQL type LONGNVARCHAR.
88 	LONGNVARCHAR,
89 	///sometimes referred to as a type code, that identifies the generic SQL type LONGVARBINARY.
90 	LONGVARBINARY,
91 	///sometimes referred to as a type code, that identifies the generic SQL type LONGVARCHAR.
92 	LONGVARCHAR,
93 	///sometimes referred to as a type code, that identifies the generic SQL type NCHAR
94 	NCHAR,
95 	///sometimes referred to as a type code, that identifies the generic SQL type NCLOB.
96 	NCLOB,
97 	///The constant in the Java programming language that identifies the generic SQL value NULL.
98 	NULL,
99 	///sometimes referred to as a type code, that identifies the generic SQL type NUMERIC.
100 	NUMERIC,
101 	///sometimes referred to as a type code, that identifies the generic SQL type NVARCHAR.
102 	NVARCHAR,
103 	///indicates that the SQL type is database-specific and gets mapped to a object that can be accessed via the methods getObject and setObject.
104 	OTHER,
105 	//sometimes referred to as a type code, that identifies the generic SQL type REAL.
106 	//REAL,
107 	//sometimes referred to as a type code, that identifies the generic SQL type REF.
108 	//REF,
109 	//sometimes referred to as a type code, that identifies the generic SQL type ROWID
110 	//ROWID,
111 	///sometimes referred to as a type code, that identifies the generic SQL type SMALLINT.
112 	SMALLINT,
113 	//sometimes referred to as a type code, that identifies the generic SQL type XML.
114 	//SQLXML,
115 	//sometimes referred to as a type code, that identifies the generic SQL type STRUCT.
116 	//STRUCT,
117 	///sometimes referred to as a type code, that identifies the generic SQL type TIME.
118 	TIME,
119 	//sometimes referred to as a type code, that identifies the generic SQL type TIMESTAMP.
120 	//TIMESTAMP,
121 	///sometimes referred to as a type code, that identifies the generic SQL type TINYINT.
122 	TINYINT,
123 	///sometimes referred to as a type code, that identifies the generic SQL type VARBINARY.
124 	VARBINARY,
125 	///sometimes referred to as a type code, that identifies the generic SQL type VARCHAR.
126 	VARCHAR,
127 }
128 
129 interface Connection {
130 	/// Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
131 	void close();
132 	/// Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.
133 	void commit();
134 	/// Retrieves this Connection object's current catalog name.
135 	string getCatalog();
136 	/// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work.
137 	void setCatalog(string catalog);
138 	/// Retrieves whether this Connection object has been closed.
139 	bool isClosed();
140 	/// Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
141 	void rollback();
142 	/// Retrieves the current auto-commit mode for this Connection object.
143 	bool getAutoCommit();
144 	/// Sets this connection's auto-commit mode to the given state.
145 	void setAutoCommit(bool autoCommit);
146 	// statements
147 	/// Creates a Statement object for sending SQL statements to the database.
148 	Statement createStatement();
149 	/// Creates a PreparedStatement object for sending parameterized SQL statements to the database.
150 	PreparedStatement prepareStatement(string query);
151 }
152 
153 interface ResultSetMetaData {
154 	//Returns the number of columns in this ResultSet object.
155 	int getColumnCount();
156 
157 	// Gets the designated column's table's catalog name.
158 	string getCatalogName(int column);
159 	// 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.
160 	//string getColumnClassName(int column);
161 	// Indicates the designated column's normal maximum width in characters.
162 	int getColumnDisplaySize(int column);
163 	// Gets the designated column's suggested title for use in printouts and displays.
164 	string getColumnLabel(int column);
165 	// Get the designated column's name.
166 	string getColumnName(int column);
167 	// Retrieves the designated column's SQL type.
168 	int getColumnType(int column);
169 	// Retrieves the designated column's database-specific type name.
170 	string getColumnTypeName(int column);
171 	// Get the designated column's number of decimal digits.
172 	int getPrecision(int column);
173 	// Gets the designated column's number of digits to right of the decimal point.
174 	int getScale(int column);
175 	// Get the designated column's table's schema.
176 	string getSchemaName(int column);
177 	// Gets the designated column's table name.
178 	string getTableName(int column);
179 	// Indicates whether the designated column is automatically numbered, thus read-only.
180 	bool isAutoIncrement(int column);
181 	// Indicates whether a column's case matters.
182 	bool isCaseSensitive(int column);
183 	// Indicates whether the designated column is a cash value.
184 	bool isCurrency(int column);
185 	// Indicates whether a write on the designated column will definitely succeed.
186 	bool isDefinitelyWritable(int column);
187 	// Indicates the nullability of values in the designated column.
188 	int isNullable(int column);
189 	// Indicates whether the designated column is definitely not writable.
190 	bool isReadOnly(int column);
191 	// Indicates whether the designated column can be used in a where clause.
192 	bool isSearchable(int column);
193 	// Indicates whether values in the designated column are signed numbers.
194 	bool isSigned(int column);
195 	// Indicates whether it is possible for a write on the designated column to succeed.
196 	bool isWritable(int column);
197 }
198 
199 interface ParameterMetaData {
200 	// Retrieves the fully-qualified name of the Java class whose instances should be passed to the method PreparedStatement.setObject.
201 	//String getParameterClassName(int param);
202 	/// Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information.
203 	int getParameterCount();
204 	/// Retrieves the designated parameter's mode.
205 	int getParameterMode(int param);
206 	/// Retrieves the designated parameter's SQL type.
207 	int getParameterType(int param);
208 	/// Retrieves the designated parameter's database-specific type name.
209 	string getParameterTypeName(int param);
210 	/// Retrieves the designated parameter's number of decimal digits.
211 	int getPrecision(int param);
212 	/// Retrieves the designated parameter's number of digits to right of the decimal point.
213 	int getScale(int param);
214 	/// Retrieves whether null values are allowed in the designated parameter.
215 	int isNullable(int param);
216 	/// Retrieves whether values for the designated parameter can be signed numbers.
217 	bool isSigned(int param);
218 }
219 
220 interface DataSetReader {
221 	bool getBoolean(int columnIndex);
222 	ubyte getUbyte(int columnIndex);
223 	ubyte[] getUbytes(int columnIndex);
224 	byte[] getBytes(int columnIndex);
225 	byte getByte(int columnIndex);
226 	short getShort(int columnIndex);
227 	ushort getUshort(int columnIndex);
228 	int getInt(int columnIndex);
229 	uint getUint(int columnIndex);
230 	long getLong(int columnIndex);
231 	ulong getUlong(int columnIndex);
232 	double getDouble(int columnIndex);
233 	float getFloat(int columnIndex);
234 	string getString(int columnIndex);
235 	DateTime getDateTime(int columnIndex);
236 	Date getDate(int columnIndex);
237 	TimeOfDay getTime(int columnIndex);
238 	Variant getVariant(int columnIndex);
239 	bool isNull(int columnIndex);
240 	bool wasNull();
241 }
242 
243 interface DataSetWriter {
244 	void setFloat(int parameterIndex, float x);
245 	void setDouble(int parameterIndex, double x);
246 	void setBoolean(int parameterIndex, bool x);
247 	void setLong(int parameterIndex, long x);
248 	void setInt(int parameterIndex, int x);
249 	void setShort(int parameterIndex, short x);
250 	void setByte(int parameterIndex, byte x);
251 	void setBytes(int parameterIndex, byte[] x);
252 	void setUlong(int parameterIndex, ulong x);
253 	void setUint(int parameterIndex, uint x);
254 	void setUshort(int parameterIndex, ushort x);
255 	void setUbyte(int parameterIndex, ubyte x);
256 	void setUbytes(int parameterIndex, ubyte[] x);
257 	void setString(int parameterIndex, string x);
258 	void setDateTime(int parameterIndex, DateTime x);
259 	void setDate(int parameterIndex, Date x);
260 	void setTime(int parameterIndex, TimeOfDay x);
261 	void setVariant(int columnIndex, Variant x);
262 
263 	void setNull(int parameterIndex);
264 	void setNull(int parameterIndex, int sqlType);
265 }
266 
267 interface ResultSet : DataSetReader {
268 	void close();
269 	bool first();
270 	bool isFirst();
271 	bool isLast();
272 	bool next();
273 
274 	//Retrieves the number, types and properties of this ResultSet object's columns
275 	ResultSetMetaData getMetaData();
276 	//Retrieves the Statement object that produced this ResultSet object.
277 	Statement getStatement();
278 	//Retrieves the current row number
279 	int getRow();
280 	//Retrieves the fetch size for this ResultSet object.
281 	int getFetchSize();
282 
283 	// from DataSetReader
284 	bool getBoolean(int columnIndex);
285 	ubyte getUbyte(int columnIndex);
286 	ubyte[] getUbytes(int columnIndex);
287 	byte[] getBytes(int columnIndex);
288 	byte getByte(int columnIndex);
289 	short getShort(int columnIndex);
290 	ushort getUshort(int columnIndex);
291 	int getInt(int columnIndex);
292 	uint getUint(int columnIndex);
293 	long getLong(int columnIndex);
294 	ulong getUlong(int columnIndex);
295 	double getDouble(int columnIndex);
296 	float getFloat(int columnIndex);
297 	string getString(int columnIndex);
298     Variant getVariant(int columnIndex);
299 
300     bool isNull(int columnIndex);
301 	bool wasNull();
302 
303 	// additional methods
304 	int findColumn(string columnName);
305 	bool getBoolean(string columnName);
306 	ubyte getUbyte(string columnName);
307 	ubyte[] getUbytes(string columnName);
308 	byte[] getBytes(string columnName);
309 	byte getByte(string columnName);
310 	short getShort(string columnName);
311 	ushort getUshort(string columnName);
312 	int getInt(string columnName);
313 	uint getUint(string columnName);
314 	long getLong(string columnName);
315 	ulong getUlong(string columnName);
316 	double getDouble(string columnName);
317 	float getFloat(string columnName);
318     string getString(string columnName);
319 	DateTime getDateTime(int columnIndex);
320 	Date getDate(int columnIndex);
321 	TimeOfDay getTime(int columnIndex);
322 	Variant getVariant(string columnName);
323 
324     /// to iterate through all rows in result set
325     int opApply(int delegate(DataSetReader) dg);
326 
327 }
328 
329 interface Statement {
330 	ResultSet executeQuery(string query);
331 	int executeUpdate(string query);
332 	int executeUpdate(string query, out Variant insertId);
333 	void close();
334 }
335 
336 /// An object that represents a precompiled SQL statement. 
337 interface PreparedStatement : Statement, DataSetWriter {
338 	/// 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.
339 	int executeUpdate();
340 	/// 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.
341 	int executeUpdate(out Variant insertId);
342 	/// Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
343 	ResultSet executeQuery();
344 
345 	/// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.
346 	ResultSetMetaData getMetaData();
347 	/// Retrieves the number, types and properties of this PreparedStatement object's parameters.
348 	ParameterMetaData getParameterMetaData();
349 	/// Clears the current parameter values immediately.
350 	void clearParameters();
351 
352 	// from DataSetWriter
353 	void setFloat(int parameterIndex, float x);
354 	void setDouble(int parameterIndex, double x);
355 	void setBoolean(int parameterIndex, bool x);
356 	void setLong(int parameterIndex, long x);
357 	void setInt(int parameterIndex, int x);
358 	void setShort(int parameterIndex, short x);
359 	void setByte(int parameterIndex, byte x);
360 	void setBytes(int parameterIndex, byte[] x);
361 	void setUlong(int parameterIndex, ulong x);
362 	void setUint(int parameterIndex, uint x);
363 	void setUshort(int parameterIndex, ushort x);
364 	void setUbyte(int parameterIndex, ubyte x);
365 	void setUbytes(int parameterIndex, ubyte[] x);
366 	void setString(int parameterIndex, string x);
367 	void setDateTime(int parameterIndex, DateTime x);
368 	void setDate(int parameterIndex, Date x);
369 	void setTime(int parameterIndex, TimeOfDay x);
370 	void setVariant(int parameterIndex, Variant x);
371 
372 	void setNull(int parameterIndex);
373 	void setNull(int parameterIndex, int sqlType);
374 }
375 
376 interface Driver {
377 	Connection connect(string url, string[string] params);
378 }
379 
380 interface DataSource {
381 	Connection getConnection();
382 }