Couple of potential DBAPI issues

MODX Staff
10,725 Posts

opengeek Reply #1, 21 years, 1 month ago

Been using the DBAPI to build a form generator and came across a few issues while using the API. Any feedback on these issues would be wonderful.

1. The connect() method does not work when sending custom properties to it (e.g. hostname, dbname, user and password) -- it sets many of the values to be the host on accident...

		$uid = $uid ? $host:$this->config['user'];
		$pwd = $pwd ? $host:$this->config['pass'];
		$host = $host ? $host:$this->config['host'];
		$dbase = $host ? $host:$this->config['dbase'];

should be...

		$uid = $uid ? $uid:$this->config['user'];
		$pwd = $pwd ? $pwd:$this->config['pass'];
		$host = $host ? $host:$this->config['host'];
		$dbase = $dbase ? $dbase:$this->config['dbase'];

or possibly...

		$uid = $host ? $uid:$this->config['user'];
		$pwd = $host ? $pwd:$this->config['pass'];
		$host = $host ? $host:$this->config['host'];
		$dbase = $host ? $dbase:$this->config['dbase'];

2. The connect method uses mysql_connect() instead of mysql_pconnect(). Is there a reason for this? If not I recommend using the pconnect() method as it actually selects the database and keeps that selection through a request/response sequence, rather than forcing developers to include the database name in all of their queries. I’m using a modified local version with pconnect() and have not detected any issues with it’s use vs. connect().

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

1,732 Posts

xwisdom Reply #2, 21 years, 1 month ago

Very nice cathc Jason,

I’ve made the changes and have add a new $persist option for those who want to use persistent connections.:

function connect($host='',$dbase='', $uid='',$pwd='',$persist=0){
	global $modx;
	$uid = $uid ? $uid:$this->config['user'];
	$pwd = $pwd ? $pwd:$this->config['pass'];
	$host = $host ? $host:$this->config['host'];
	$dbase = $dbase ? $dbase:$this->config['dbase'];
	$tstart = $modx->getMicroTime(); 
	if(@!$this->conn = ($persist ? mysql_pconnect($host, $uid, $pwd):mysql_connect($host, $uid, $pwd))) {
		$modx->messageQuit("Failed to create the database connection!");
		exit;
	} else {
		mysql_select_db($dbase);
		$tend = $modx->getMicroTime(); 
		$totaltime = $tend-$tstart;
		if($modx->dumpSQL) {
			$modx->queryCode .= "<fieldset style='text-align:left'><legend>Database connection</legend>".sprintf("Database connection was created in %2.4f s", $totaltime)."</fieldset><br />";
		}
		$this->isConnected = true;
		$this->queryTime = $this->queryTime+$totaltime;
	}	
}

Here’s some notes on using persistent connections:

...the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

...Using persistent connections can require a bit of tuning of your Apache and MySQL configurations to ensure that you do not exceed the number of connections allowed by MySQL.

xWisdom
www.xwisdomhtml.com
The fear of the Lord is the beginning of wisdom:
MODx Co-Founder - Create and do more with less.

MODX Staff
10,725 Posts

opengeek Reply #3, 21 years, 1 month ago

In regards to database selection, why is it that the database container is never selected unless I use the persistent connection? Is this expected behavior?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

1,732 Posts

xwisdom Reply #4, 21 years, 1 month ago

shouldn’t be the mysql_select_db($dbase); line should have selected the database

hmmm, unless it has something to do with those `` characters around the db name?

xWisdom
www.xwisdomhtml.com
The fear of the Lord is the beginning of wisdom:
MODx Co-Founder - Create and do more with less.

MODX Staff
10,725 Posts

opengeek Reply #5, 21 years, 1 month ago

Confirmed Raymond ... the `` characters are indeed preventing the database from being selected properly, which forces us to use the dbname in every query.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

1,732 Posts

xwisdom Reply #6, 21 years, 1 month ago

ok
I think we will strip them away

xWisdom
www.xwisdomhtml.com
The fear of the Lord is the beginning of wisdom:
MODx Co-Founder - Create and do more with less.

1 Posts

Zombie Reply #7, 21 years, 1 month ago

Being late to the game on this, can someone give me the 30 seconds on DBAPI, what it gains us, and how much, if any, of the core is using it?

1,732 Posts

xwisdom Reply #8, 21 years, 1 month ago

The DBAPI seeks to consolidate all DB functions into a single class object for making posible for reuse these functions. It’s one step closer to DB abstraction. DBAPI is used to replace earlier db functions found on the MODx object.

You might want to have a look at the source to see that functions are supported. No docs available bjust yet

xWisdom
www.xwisdomhtml.com
The fear of the Lord is the beginning of wisdom:
MODx Co-Founder - Create and do more with less.

1 Posts

Zombie Reply #9, 21 years, 1 month ago

So I assume, then, that we want to be actively converting all the mysql calls to this, right?

MODX Staff
10,725 Posts

opengeek Reply #10, 21 years, 1 month ago

Quote from: infoclipper at Aug 18, 2005, 11:09 PM

So I assume, then, that we want to be actively converting all the mysql calls to this, right?

Yes, absolutely, IMO. If we want to support other DB environments in the future, we must wrap all of our SQL interaction so that it can become independent of the syntax and rules governing the various DB platforms.

And while we’re on the subject, I would also like to see us architect the DB abstraction layer so that we could call the utility functions directly (via DBAPI) as we can now, as well as create persistent objects by simply creating a class that extends a base persistentObject class. This would simplify code maintenance, make the code cleaner and more readable, as well as provide a facility for contributing developers and end-users to easily create persistent objects (via coding classes directly, or even via a wizard-like interface that might generate such a class file) they can use within the framework. From there we can take it to the next level, adding DB caching features, true transaction handling, advanced searching capabilities not dependent on the DB platform, and true separation of business logic from persistence logic (by applying Memento and/or Delegate design patterns). That means optimized persistence handlers for each major database as we need it (or advanced users could add them as well, without disturbing the core logic of our product).

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek