1 /**
2  * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 
3  * 
4  * Source file ddbc/common.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  * This module contains some useful base class implementations for writing Driver for particular RDBMS.
15  * As well it contains useful class - ConnectionPoolDataSourceImpl - which can be used as connection pool.
16  *
17  * You can find usage examples in unittest{} sections.
18  *
19  * Copyright: Copyright 2013
20  * License:   $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
21  * Author:   Vadim Lopatin
22  */
23 module ddbc.common;
24 import ddbc.core;
25 import std.algorithm;
26 import std.exception;
27 import std.stdio;
28 import std.conv;
29 import std.variant;
30 
31 /// Implementation of simple DataSource: it just holds connection parameters, and can create new Connection by getConnection().
32 /// Method close() on such connection will really close connection.
33 class DataSourceImpl : DataSource {
34 	Driver driver;
35 	string url;
36 	string[string] params;
37 	this(Driver driver, string url, string[string]params) {
38 		this.driver = driver;
39 		this.url = url;
40 		this.params = params;
41 	}
42 	override Connection getConnection() {
43 		return driver.connect(url, params);
44 	}
45 }
46 
47 /// Delegate type to create DDBC driver instance.
48 alias DriverFactoryDelegate = Driver delegate();
49 /// DDBC Driver factory.
50 /// Can create driver by name or DDBC URL.
51 class DriverFactory {
52     private __gshared static DriverFactoryDelegate[string] _factoryMap;
53 
54     /// Registers driver factory by URL prefix, e.g. "mysql", "postgresql", "sqlite"
55     /// Use this method to register your own custom drivers
56     static void registerDriverFactory(string name, DriverFactoryDelegate factoryDelegate) {
57         _factoryMap[name] = factoryDelegate;
58     }
59     /// Factory method to create driver by registered name found in ddbc url, e.g. "mysql", "postgresql", "sqlite"
60     /// List of available drivers depend on configuration
61     static Driver createDriverForURL(string url) {
62         return createDriver(extractDriverNameFromURL(url));
63     }
64     /// Factory method to create driver by registered name, e.g. "mysql", "postgresql", "sqlite"
65     /// List of available drivers depend on configuration
66     static Driver createDriver(string driverName) {
67         if (auto p = (driverName in _factoryMap)) {
68             // found: call delegate to create driver
69             return (*p)();
70         } else {
71             throw new SQLException("DriverFactory: driver is not found for name \"" ~ driverName ~ "\"");
72         }
73     }
74 }
75 
76 /// To be called on connection close
77 interface ConnectionCloseHandler {
78 	void onConnectionClosed(Connection connection);
79 }
80 
81 /// Wrapper class for connection
82 class ConnectionWrapper : Connection {
83 	private ConnectionCloseHandler pool;
84 	private Connection base;
85 	private bool closed;
86 
87 	this(ConnectionCloseHandler pool, Connection base) {
88 		this.pool = pool;
89 		this.base = base;
90 	}
91 	override void close() {
92 		assert(!closed, "Connection is already closed");
93 		closed = true;
94 		pool.onConnectionClosed(base); 
95 	}
96 	override PreparedStatement prepareStatement(string query) { return base.prepareStatement(query); }
97 	override void commit() { base.commit(); }
98 	override Statement createStatement() { return base.createStatement(); }
99 	override string getCatalog() { return base.getCatalog(); }
100 	override bool isClosed() { return closed; }
101 	override void rollback() { base.rollback(); }
102 	override bool getAutoCommit() { return base.getAutoCommit(); }
103 	override void setAutoCommit(bool autoCommit) { base.setAutoCommit(autoCommit); }
104 	override void setCatalog(string catalog) { base.setCatalog(catalog); }
105 }
106 
107 // remove array item inplace
108 static void myRemove(T)(ref T[] array, size_t index) {
109     for (auto i = index; i < array.length - 1; i++) {
110         array[i] = array[i + 1];
111     }
112     array[$ - 1] = T.init;
113     array.length = array.length - 1;
114 }
115 
116 // remove array item inplace
117 static void myRemove(T : Object)(ref T[] array, T item) {
118     int index = -1;
119     for (int i = 0; i < array.length; i++) {
120         if (array[i] is item) {
121             index = i;
122             break;
123         }
124     }
125     if (index < 0)
126         return;
127     for (auto i = index; i < array.length - 1; i++) {
128         array[i] = array[i + 1];
129     }
130     array[$ - 1] = T.init;
131     array.length = array.length - 1;
132 }
133 
134 // TODO: implement limits
135 // TODO: thread safety
136 /// Simple connection pool DataSource implementation.
137 /// When close() is called on connection received from this pool, it will be returned to pool instead of closing.
138 /// Next getConnection() will just return existing connection from pool, instead of slow connection establishment process.
139 class ConnectionPoolDataSourceImpl : DataSourceImpl, ConnectionCloseHandler {
140 private:
141 	int maxPoolSize;
142 	int timeToLive;
143 	int waitTimeOut;
144 
145 	Connection [] activeConnections;
146 	Connection [] freeConnections;
147 
148 public:
149 
150 	this(Driver driver, string url, string[string]params = null, int maxPoolSize = 1, int timeToLive = 600, int waitTimeOut = 30) {
151 		super(driver, url, params);
152 		this.maxPoolSize = maxPoolSize;
153 		this.timeToLive = timeToLive;
154 		this.waitTimeOut = waitTimeOut;
155 	}
156 
157 	override Connection getConnection() {
158 		Connection conn = null;
159         //writeln("getConnection(): freeConnections.length = " ~ to!string(freeConnections.length));
160         if (freeConnections.length > 0) {
161             //writeln("getConnection(): returning free connection");
162             conn = freeConnections[freeConnections.length - 1]; // $ - 1
163             auto oldSize = freeConnections.length;
164             myRemove(freeConnections, freeConnections.length - 1);
165             //freeConnections.length = oldSize - 1; // some bug in remove? length is not decreased...
166             auto newSize = freeConnections.length;
167             assert(newSize == oldSize - 1);
168         } else {
169             //writeln("getConnection(): creating new connection");
170             try {
171                 conn = super.getConnection();
172             } catch (Throwable e) {
173                 //writeln("exception while creating connection " ~ e.msg);
174                 throw e;
175             }
176             //writeln("getConnection(): connection created");
177         }
178         auto oldSize = activeConnections.length;
179         activeConnections ~= conn;
180         auto newSize = activeConnections.length;
181         assert(oldSize == newSize - 1);
182         auto wrapper = new ConnectionWrapper(this, conn);
183 		return wrapper;
184 	}
185 
186 	void removeUsed(Connection connection) {
187         //writeln("removeUsed");
188         //writeln("removeUsed - activeConnections.length=" ~ to!string(activeConnections.length));
189 		foreach (i, item; activeConnections) {
190 			if (item == connection) {
191                 auto oldSize = activeConnections.length;
192 				//std.algorithm.remove(activeConnections, i);
193                 myRemove(activeConnections, i);
194                 //activeConnections.length = oldSize - 1;
195                 auto newSize = activeConnections.length;
196                 assert(oldSize == newSize + 1);
197                 return;
198 			}
199 		}
200 		throw new SQLException("Connection being closed is not found in pool");
201 	}
202 
203 	override void onConnectionClosed(Connection connection) {
204         //writeln("onConnectionClosed");
205         assert(connection !is null);
206         //writeln("calling removeUsed");
207         removeUsed(connection);
208         //writeln("adding to free list");
209         auto oldSize = freeConnections.length;
210         freeConnections ~= connection;
211         auto newSize = freeConnections.length;
212         assert(newSize == oldSize + 1);
213     }
214 }
215 
216 /// Helper implementation of ResultSet - throws Method not implemented for most of methods.
217 /// Useful for driver implementations
218 class ResultSetImpl : ddbc.core.ResultSet {
219 public:
220     override int opApply(int delegate(DataSetReader) dg) { 
221         int result = 0;
222         if (!first())
223             return 0;
224         do { 
225             result = dg(cast(DataSetReader)this); 
226             if (result) break; 
227         } while (next());
228         return result; 
229     }
230     override void close() {
231 		throw new SQLException("Method not implemented");
232 	}
233 	override bool first() {
234 		throw new SQLException("Method not implemented");
235 	}
236 	override bool isFirst() {
237 		throw new SQLException("Method not implemented");
238 	}
239 	override bool isLast() {
240 		throw new SQLException("Method not implemented");
241 	}
242 	override bool next() {
243 		throw new SQLException("Method not implemented");
244 	}
245 	
246 	override int findColumn(string columnName) {
247 		throw new SQLException("Method not implemented");
248 	}
249 	override bool getBoolean(int columnIndex) {
250 		throw new SQLException("Method not implemented");
251 	}
252 	override bool getBoolean(string columnName) {
253 		return getBoolean(findColumn(columnName));
254 	}
255 	override ubyte getUbyte(int columnIndex) {
256 		throw new SQLException("Method not implemented");
257 	}
258 	override ubyte getUbyte(string columnName) {
259 		return getUbyte(findColumn(columnName));
260 	}
261 	override byte getByte(int columnIndex) {
262 		throw new SQLException("Method not implemented");
263 	}
264 	override byte getByte(string columnName) {
265 		return getByte(findColumn(columnName));
266 	}
267 	override byte[] getBytes(int columnIndex) {
268 		throw new SQLException("Method not implemented");
269 	}
270 	override byte[] getBytes(string columnName) {
271 		return getBytes(findColumn(columnName));
272 	}
273 	override ubyte[] getUbytes(int columnIndex) {
274 		throw new SQLException("Method not implemented");
275 	}
276 	override ubyte[] getUbytes(string columnName) {
277 		return getUbytes(findColumn(columnName));
278 	}
279 	override short getShort(int columnIndex) {
280 		throw new SQLException("Method not implemented");
281 	}
282 	override short getShort(string columnName) {
283 		return getShort(findColumn(columnName));
284 	}
285 	override ushort getUshort(int columnIndex) {
286 		throw new SQLException("Method not implemented");
287 	}
288 	override ushort getUshort(string columnName) {
289 		return getUshort(findColumn(columnName));
290 	}
291 	override int getInt(int columnIndex) {
292 		throw new SQLException("Method not implemented");
293 	}
294 	override int getInt(string columnName) {
295 		return getInt(findColumn(columnName));
296 	}
297 	override uint getUint(int columnIndex) {
298 		throw new SQLException("Method not implemented");
299 	}
300 	override uint getUint(string columnName) {
301 		return getUint(findColumn(columnName));
302 	}
303 	override long getLong(int columnIndex) {
304 		throw new SQLException("Method not implemented");
305 	}
306 	override long getLong(string columnName) {
307 		return getLong(findColumn(columnName));
308 	}
309 	override ulong getUlong(int columnIndex) {
310 		throw new SQLException("Method not implemented");
311 	}
312 	override ulong getUlong(string columnName) {
313 		return getUlong(findColumn(columnName));
314 	}
315 	override double getDouble(int columnIndex) {
316 		throw new SQLException("Method not implemented");
317 	}
318 	override double getDouble(string columnName) {
319 		return getDouble(findColumn(columnName));
320 	}
321 	override float getFloat(int columnIndex) {
322 		throw new SQLException("Method not implemented");
323 	}
324 	override float getFloat(string columnName) {
325 		return getFloat(findColumn(columnName));
326 	}
327 	override string getString(int columnIndex) {
328 		throw new SQLException("Method not implemented");
329 	}
330 	override string getString(string columnName) {
331 		return getString(findColumn(columnName));
332 	}
333     override Variant getVariant(int columnIndex) {
334         throw new SQLException("Method not implemented");
335     }
336     override Variant getVariant(string columnName) {
337         return getVariant(findColumn(columnName));
338     }
339 
340 	override bool wasNull() {
341 		throw new SQLException("Method not implemented");
342 	}
343 
344 	override bool isNull(int columnIndex) {
345 		throw new SQLException("Method not implemented");
346 	}
347 
348 	//Retrieves the number, types and properties of this ResultSet object's columns
349 	override ResultSetMetaData getMetaData() {
350 		throw new SQLException("Method not implemented");
351 	}
352 	//Retrieves the Statement object that produced this ResultSet object.
353 	override Statement getStatement() {
354 		throw new SQLException("Method not implemented");
355 	}
356 	//Retrieves the current row number
357 	override int getRow() {
358 		throw new SQLException("Method not implemented");
359 	}
360 	//Retrieves the fetch size for this ResultSet object.
361 	override int getFetchSize() {
362 		throw new SQLException("Method not implemented");
363 	}
364 	override std.datetime.DateTime getDateTime(int columnIndex) {
365 		throw new SQLException("Method not implemented");
366 	}
367 	override std.datetime.Date getDate(int columnIndex) {
368 		throw new SQLException("Method not implemented");
369 	}
370 	override std.datetime.TimeOfDay getTime(int columnIndex) {
371 		throw new SQLException("Method not implemented");
372 	}
373 }
374 
375 /// Column metadata object to be used in driver implementations
376 class ColumnMetadataItem {
377 	string 	catalogName;
378 	int	    displaySize;
379 	string 	label;
380 	string  name;
381 	int 	type;
382 	string 	typeName;
383 	int     precision;
384 	int     scale;
385 	string  schemaName;
386 	string  tableName;
387 	bool 	isAutoIncrement;
388 	bool 	isCaseSensitive;
389 	bool 	isCurrency;
390 	bool 	isDefinitelyWritable;
391 	int 	isNullable;
392 	bool 	isReadOnly;
393 	bool 	isSearchable;
394 	bool 	isSigned;
395 	bool 	isWritable;
396 }
397 
398 /// parameter metadata object - to be used in driver implementations
399 class ParameterMetaDataItem {
400 	/// Retrieves the designated parameter's mode.
401 	int mode;
402 	/// Retrieves the designated parameter's SQL type.
403 	int type;
404 	/// Retrieves the designated parameter's database-specific type name.
405 	string typeName;
406 	/// Retrieves the designated parameter's number of decimal digits.
407 	int precision;
408 	/// Retrieves the designated parameter's number of digits to right of the decimal point.
409 	int scale;
410 	/// Retrieves whether null values are allowed in the designated parameter.
411 	int isNullable;
412 	/// Retrieves whether values for the designated parameter can be signed numbers.
413 	bool isSigned;
414 }
415 
416 /// parameter set metadate implementation object - to be used in driver implementations
417 class ParameterMetaDataImpl : ParameterMetaData {
418 	ParameterMetaDataItem [] cols;
419 	this(ParameterMetaDataItem [] cols) {
420 		this.cols = cols;
421 	}
422 	ref ParameterMetaDataItem col(int column) {
423 		enforceEx!SQLException(column >=1 && column <= cols.length, "Parameter index out of range");
424 		return cols[column - 1];
425 	}
426 	// Retrieves the fully-qualified name of the Java class whose instances should be passed to the method PreparedStatement.setObject.
427 	//String getParameterClassName(int param);
428 	/// Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information.
429 	int getParameterCount() {
430 		return cast(int)cols.length;
431 	}
432 	/// Retrieves the designated parameter's mode.
433 	int getParameterMode(int param) { return col(param).mode; }
434 	/// Retrieves the designated parameter's SQL type.
435 	int getParameterType(int param) { return col(param).type; }
436 	/// Retrieves the designated parameter's database-specific type name.
437 	string getParameterTypeName(int param) { return col(param).typeName; }
438 	/// Retrieves the designated parameter's number of decimal digits.
439 	int getPrecision(int param) { return col(param).precision; }
440 	/// Retrieves the designated parameter's number of digits to right of the decimal point.
441 	int getScale(int param) { return col(param).scale; }
442 	/// Retrieves whether null values are allowed in the designated parameter.
443 	int isNullable(int param) { return col(param).isNullable; }
444 	/// Retrieves whether values for the designated parameter can be signed numbers.
445 	bool isSigned(int param) { return col(param).isSigned; }
446 }
447 
448 /// Metadata for result set - to be used in driver implementations
449 class ResultSetMetaDataImpl : ResultSetMetaData {
450 	ColumnMetadataItem [] cols;
451 	this(ColumnMetadataItem [] cols) {
452 		this.cols = cols;
453 	}
454 	ref ColumnMetadataItem col(int column) {
455 		enforceEx!SQLException(column >=1 && column <= cols.length, "Column index out of range");
456 		return cols[column - 1];
457 	}
458 	//Returns the number of columns in this ResultSet object.
459 	override int getColumnCount() { return cast(int)cols.length; }
460 	// Gets the designated column's table's catalog name.
461 	override string getCatalogName(int column) { return col(column).catalogName; }
462 	// 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.
463 	//override string getColumnClassName(int column) { return col(column).catalogName; }
464 	// Indicates the designated column's normal maximum width in characters.
465 	override int getColumnDisplaySize(int column) { return col(column).displaySize; }
466 	// Gets the designated column's suggested title for use in printouts and displays.
467 	override string getColumnLabel(int column) { return col(column).label; }
468 	// Get the designated column's name.
469 	override string getColumnName(int column) { return col(column).name; }
470 	// Retrieves the designated column's SQL type.
471 	override int getColumnType(int column) { return col(column).type; }
472 	// Retrieves the designated column's database-specific type name.
473 	override string getColumnTypeName(int column) { return col(column).typeName; }
474 	// Get the designated column's number of decimal digits.
475 	override int getPrecision(int column) { return col(column).precision; }
476 	// Gets the designated column's number of digits to right of the decimal point.
477 	override int getScale(int column) { return col(column).scale; }
478 	// Get the designated column's table's schema.
479 	override string getSchemaName(int column) { return col(column).schemaName; }
480 	// Gets the designated column's table name.
481 	override string getTableName(int column) { return col(column).tableName; }
482 	// Indicates whether the designated column is automatically numbered, thus read-only.
483 	override bool isAutoIncrement(int column) { return col(column).isAutoIncrement; }
484 	// Indicates whether a column's case matters.
485 	override bool isCaseSensitive(int column) { return col(column).isCaseSensitive; }
486 	// Indicates whether the designated column is a cash value.
487 	override bool isCurrency(int column) { return col(column).isCurrency; }
488 	// Indicates whether a write on the designated column will definitely succeed.
489 	override bool isDefinitelyWritable(int column) { return col(column).isDefinitelyWritable; }
490 	// Indicates the nullability of values in the designated column.
491 	override int isNullable(int column) { return col(column).isNullable; }
492 	// Indicates whether the designated column is definitely not writable.
493 	override bool isReadOnly(int column) { return col(column).isReadOnly; }
494 	// Indicates whether the designated column can be used in a where clause.
495 	override bool isSearchable(int column) { return col(column).isSearchable; }
496 	// Indicates whether values in the designated column are signed numbers.
497 	override bool isSigned(int column) { return col(column).isSigned; }
498 	// Indicates whether it is possible for a write on the designated column to succeed.
499 	override bool isWritable(int column) { return col(column).isWritable; }
500 }
501 
502 version (unittest) {
503     void unitTestExecuteBatch(Connection conn, string[] queries) {
504         Statement stmt = conn.createStatement();
505         foreach(query; queries) {
506 			//writeln("query:" ~ query);
507             stmt.executeUpdate(query);
508         }
509     }
510 }
511 
512 // utility functions
513 
514 /// removes ddbc: prefix from string (if any)
515 /// e.g., for "ddbc:postgresql://localhost/test" it will return "postgresql://localhost/test"
516 string stripDdbcPrefix(string url) {
517     if (url.startsWith("ddbc:"))
518         return url[5 .. $]; // strip out ddbc: prefix
519     return url;
520 }
521 
522 /// extracts driver name from DDBC URL
523 /// e.g., for "ddbc:postgresql://localhost/test" it will return "postgresql"
524 string extractDriverNameFromURL(string url) {
525     url = stripDdbcPrefix(url);
526     import std.string;
527     int colonPos = cast(int)url.indexOf(":");
528     if (colonPos < 0)
529         return url;
530     return url[0 .. colonPos];
531 }
532 
533 /// extract parameters from URL string to string[string] map, update url to strip params
534 void extractParamsFromURL(ref string url, ref string[string] params) {
535     url = stripDdbcPrefix(url);
536     import std.string : lastIndexOf, split;
537     ptrdiff_t qmIndex = lastIndexOf(url, '?');
538     if (qmIndex >= 0) {
539         string urlParams = url[qmIndex + 1 .. $];
540         url = url[0 .. qmIndex];
541         string[] list = urlParams.split(",");
542         foreach(item; list) {
543             string[] keyValue = item.split("=");
544             if (keyValue.length == 2) {
545                 params[keyValue[0]] = keyValue[1];
546             }
547         }
548     }
549 }
550 
551 /// sets user and password parameters in parameter map
552 public void setUserAndPassword(ref string[string] params, string username, string password) {
553     params["user"] = username;
554     params["password"] = password;
555 }
556 
557 // factory methods
558 
559 /// Helper function to create DDBC connection, automatically selecting driver based on URL
560 Connection createConnection(string url, string[string]params = null) {
561     Driver driver = DriverFactory.createDriverForURL(url);
562     return driver.connect(url, params);
563 }
564 
565 /// Helper function to create simple DDBC DataSource, automatically selecting driver based on URL
566 DataSource createDataSource(string url, string[string]params = null) {
567     Driver driver = DriverFactory.createDriverForURL(url);
568     return new DataSourceImpl(driver, url, params);
569 }
570 
571 /// Helper function to create connection pool data source, automatically selecting driver based on URL
572 DataSource createConnectionPool(string url, string[string]params = null, int maxPoolSize = 1, int timeToLive = 600, int waitTimeOut = 30) {
573     Driver driver = DriverFactory.createDriverForURL(url);
574     return new ConnectionPoolDataSourceImpl(driver, url, params, maxPoolSize, timeToLive, waitTimeOut);
575 }
576