www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Question about destructor of database and multiple use access

reply Suliman <evermind live.ru> writes:
I have for next queston.

For example I have class for working with DB (I am using ddbc 
driver). I put some variables as class fields, and init them in 
constructor:

class GDB
{
	Statement stmt;
	Config config;
	MySQLDriver driver;
	DataSource ds;

	this(Config config)
	{
		this.config = config;
	 	driver = new MySQLDriver();
	 	string[string] params;
	    string url = MySQLDriver.generateUrl("localhost", 3306, 
"geodb");
	    params = MySQLDriver.setUserAndPassword("root", "123");
	    ds = new ConnectionPoolDataSourceImpl(driver, url, params);
	}
	
	
	void dbInsert(string login, string date, string type, string 
data)
	{
		string sqlinsert = (`INSERT INTO test (userlogin, date, type, 
data) VALUES ('%s', '%s', '%s', '%s') `, login, date, type, data);
		stmt.executeUpdate(sqlinsert);
	}


	void getIMGsMetadataFromDB(Json request)
	{
		 string sqlSelect = "SELECT * FROM test";
		...
                 stmt.executeQuery ...

	}


....
}

I can't understand in which place I should put:

	auto conn = ds.getConnection();
	scope(exit) conn.close();

	auto stmt = conn.createStatement();
	scope(exit) stmt.close();
	
1. Should declaration of them be field of class?
2. Should I call destructor and how it's should like?
3. If I will not call it would it wrong?
4. If 100 users will come to my site, my code will open 100 
connections? And would open every new connection for every 
request? Can I open single connection and use it for all users?
Jul 28 2016
next sibling parent reply Suliman <evermind live.ru> writes:
class GDB
{
	Config config;
	MySQLDriver driver;
	DataSource ds;
	Connection conn;

	this(Config config)
	{
		this.config = config;
	 	driver = new MySQLDriver();
	 	string[string] params;
	    string url = MySQLDriver.generateUrl("localhost", 3306, 
"geodb");
	    params = MySQLDriver.setUserAndPassword("root", "123");
	    ds = new ConnectionPoolDataSourceImpl(driver, url, params);
		conn = ds.getConnection();
	
	}
	

	void dbInsert(string login, string uploading_date, string 
geometry_type, string data)
	{
	    Statement stmt = conn.createStatement();
		//stmt.executeUpdate("...");
		// some processing of request
		scope(exit) stmt.close(); // closing
	}

	void getIMGsMetadataFromDB(Json request)
	{

	    Statement stmt = conn.createStatement();
      	//stmt.executeWuery("...");
		// some processing of request
		scope(exit) stmt.close(); // closing
	}
	
Is this code is more correct?
Jul 28 2016
parent Dechcaudron <no-reply no-email.com> writes:
On Thursday, 28 July 2016 at 14:24:16 UTC, Suliman wrote:

 	void dbInsert(string login, string uploading_date, string 
 geometry_type, string data)
 	{
 	    Statement stmt = conn.createStatement();
 		//stmt.executeUpdate("...");
 		// some processing of request
 		scope(exit) stmt.close(); // closing
 	}

 	void getIMGsMetadataFromDB(Json request)
 	{

 	    Statement stmt = conn.createStatement();
      	//stmt.executeWuery("...");
 		// some processing of request
 		scope(exit) stmt.close(); // closing
 	}
 	
 Is this code is more correct?


You'd have to go with
Statement stmt = conn.createStatement();
scope(exit) stmt.close();
//stmt.executeUpdate...
//some processing

stmt.close will be called only when leaving the scope, although 
it appears right after stmt initialization. Check this out: 
https://dlang.org/spec/statement.html#scope-guard-statement
Jul 28 2016
prev sibling parent reply Dechcaudron <no-reply no-email.com> writes:
I don't know anything about the driver you are using, but from my 
general experience with DBs I'll try to give you some insight.

On Thursday, 28 July 2016 at 14:01:45 UTC, Suliman wrote:

 1. Should declaration of them be field of class?


I'd say so. If you intend to use each instance of the class for 
more than db operation (which you probably do), you'll probably 
want to keep the connection alive between method calls, 
connecting in the constructor. As for the statement, I don't 
really know what it is about.


 2. Should I call destructor and how it's should like?


You certainly want to close the connection to the db. Basically, 
the destructor is intended to free resources such as dynamic 
memory, closing connections... the GC will take care of dynamic 
memory, but closing the connection to the DB is up to you. So do 
that in the destructor. As for the rest of the fields, I don't 
know if manual cleanup will be required, sorry.


 3. If I will not call it would it wrong?


It would go wrong. Each instance would open a connection to the 
DB and it would never be closed, which is a very bad thing. The 
open connections would go adding up until the DB would not be 
able to accept anymore connections and would probably refuse to 
function.


 4. If 100 users will come to my site, my code will open 100 
 connections? And would open every new connection for every 
 request? Can I open single connection and use it for all users?


It depends where you use this class and how you use it. If you 
create an instance upon receiving a network request, a connection 
would be open for each request, then closed in the destructor. So 
if 100 users go to your site and they all start sending requests 
at the same time, each request would open a db connection.

If you want to avoid this, either process the requests 
sequentially (I don't recommend this) and create and instance of 
this class beforehand, which you will use for all of them. If you 
don't want to do sequential processing (which is likely your 
case), and you still want to keep connections to a minimum, 
create a shared instace of GDB and use it across the threads in 
which requests are processed (syncronization will be required). 
If you want to avoid syncronization issues while still 
maintaining the benefits of shared instances, you could go with 
an instance pool [1]. But opening connections upon request 
receiving is not -that bad-, especially for a start, so long as 
the maximum number of connections doesn't exceed a certain limit. 
But I'd go with the pool.

As for the

 scope(exit) conn.close();


you don't have to worry about that so long as you manage it in 
the destructor. If you don't, and open the connection in a 
method, that line would go right after the connection opening, so 
you ensure it is close upon scope exit.

Cheers!

[1] https://en.wikipedia.org/wiki/Object_pool_pattern
Jul 28 2016
parent reply Lodovico Giaretta <lodovico giaretart.net> writes:
On Thursday, 28 July 2016 at 14:33:26 UTC, Dechcaudron wrote:

 On Thursday, 28 July 2016 at 14:01:45 UTC, Suliman wrote:

 2. Should I call destructor and how it's should like?


 You certainly want to close the connection to the db. 
 Basically, the destructor is intended to free resources such as 
 dynamic memory, closing connections... the GC will take care of 
 dynamic memory, but closing the connection to the DB is up to 
 you. So do that in the destructor.


No! Never run important finalization in a class destructor! The 
GC is not obliged to run the destructors, so you may end up with 
your objects destroyed but the connections still open. For this 
kind of important things, you have to do them manually.
This is true of all major programming languages: JDBC (Java DB 
framework, one of the most used in the world) requires calling 
explicit methods to close connections, statements and result 

sql library.
Jul 28 2016
parent reply Dechcaudron <no-reply no-email.com> writes:
On Thursday, 28 July 2016 at 14:43:32 UTC, Lodovico Giaretta 
wrote:

 No! Never run important finalization in a class destructor! The 
 GC is not obliged to run the destructors, so you may end up 
 with your objects destroyed but the connections still open. For 
 this kind of important things, you have to do them manually.


I always thought that the moment of finalization is undetermined, 
but that the GC does indeed run the destructor... Weird, I'll 
have to look into that. After all what would be the point of 
destructors if they are not guaranteed to be run?

Still, if you are to manually call a cleanup method, you might as 
well call destroy on the instance to force the destructor to run 
right away, right? Not that it makes any difference to call 
instance.cleanup() or destroy(instance) so long as cleanup and 
the destructor contain the same code.
Jul 28 2016
parent reply Lodovico Giaretta <lodovico giaretart.net> writes:
On Thursday, 28 July 2016 at 15:02:58 UTC, Dechcaudron wrote:

 On Thursday, 28 July 2016 at 14:43:32 UTC, Lodovico Giaretta 
 wrote:

 No! Never run important finalization in a class destructor! 
 The GC is not obliged to run the destructors, so you may end 
 up with your objects destroyed but the connections still open. 
 For this kind of important things, you have to do them 
 manually.


 I always thought that the moment of finalization is 
 undetermined, but that the GC does indeed run the destructor... 
 Weird, I'll have to look into that. After all what would be the 
 point of destructors if they are not guaranteed to be run?


The collector does not immediately finalize objects. It just 
schedules them for finalization at a later time. So:
1) If you don't get low on memory, no collection is performed, so 
no object is scheduled for finalization;
2) even if a collection is performed, false pointers may prevent 
some unreachable object from becoming garbage and being scheduled 
for finalization;
3) at program end, live objects are not scheduled for 
finalization;
4) at program end, pending finalizations from previous 
collections may not be run.
Jul 28 2016
parent reply Dechcaudron <no-reply no-email.com> writes:
On Thursday, 28 July 2016 at 15:18:24 UTC, Lodovico Giaretta 
wrote:

 3) at program end, live objects are not scheduled for 
 finalization;
 4) at program end, pending finalizations from previous 
 collections may not be run.


I didn't know these two, can I get source on them?

Also, I'm assuming what I said about calling destroy(instance) is 
as correct as calling a cleanup method?
Jul 28 2016
next sibling parent Lodovico Giaretta <lodovico giaretart.net> writes:
On Thursday, 28 July 2016 at 15:24:22 UTC, Dechcaudron wrote:

 On Thursday, 28 July 2016 at 15:18:24 UTC, Lodovico Giaretta 
 wrote:

 3) at program end, live objects are not scheduled for 
 finalization;
 4) at program end, pending finalizations from previous 
 collections may not be run.


 I didn't know these two, can I get source on them?


I don't have any specific knowledge about the D collector, but it 
is my understanding that most collectors out there work this way, 
because it would be very expensive and bug-prone to do otherwise 
(remember that destructors may do things like get stuck in a loop 
or "resurrect" themselves or other collected objects).


 Also, I'm assuming what I said about calling destroy(instance) 
 is as correct as calling a cleanup method?


Yes, I think so.
Jul 28 2016
prev sibling parent reply Kagamin <spam here.lot> writes:
On Thursday, 28 July 2016 at 15:24:22 UTC, Dechcaudron wrote:

 Also, I'm assuming what I said about calling destroy(instance) 
 is as correct as calling a cleanup method?


Class destructor also automatically calls destructors of struct 
members of the class.
Jul 28 2016
parent Suliman <evermind live.ru> writes:
So my last variant is right?

How can I inspect code, to better understand how GC works? Also 
where I can find idiomatic code with comments? Just to read for 
better understand design solutions.
Jul 29 2016