Database Connection Sharing Between Extensions - Core Component plus Small JDatabase change would be needed

[orware]

I think I had brought this up in another topic sometime last year, but
the topic has come up again in my work and this time I'm thinking of
putting together what would be required.

This is somewhat related to Mitch's topic (in regards to being able to
share some type of information between components) but it regards
sharing non-Joomla database connections between numerous extensions.

Here's the scenario:
I've created a number of extensions now that all use the Oracle driver
I built for Joomla. I use it to pull needed data out from an Oracle
database to present it via a Joomla-based frontend. If needed, I also
create database tables within my Joomla installation and store those
via the normal MySQL driver (so, I'm still installing Joomla on a
MySQL database, I just have the need to periodically pull data from
this other data source).

Before now I've been able to have the applications separate from each
other (that is, installed in separate Joomla installations), but this
week I began working on a new site for the college that makes use of
several extensions, all of which require a connection at some point to
this Oracle database (an authentication plugin, two components, and
more is on the way).

What I'm finding is that I need to store the required information to
create the database connection within each extension. Since I'm not
creating a suite of extensions I'm not thinking about tightly coupling
things by putting the connection info in just one of the extensions
and always checking it, though the thought has occurred to me.

The problem for me would be that if something were to change I would
need to go into every extension and update the connection info.

Instead my thought would be to have a "Database Manager" component
which would allow Adding/Editing/Removing connections to additional
databases aside from the one currently being used by Joomla.

The component would be really simple and the table required would
mimic that required to create the array required by
JDatabase::getInstance():
#__database
id
name (this would be a descriptive name for the connection like
"Student Database")
driver
host
user
password (since we can't use SHA-1 hashing, maybe the MySQL AES
Encryption/Decryption might be used, or not)
database
port
...(possibly some additional fields)

Then the JDatabase::getInstance() method would be modified to take a
string as input (the string being a valid value from the "name" field
in #__database).

If given a string, then JDatabase::getInstance() will go to the
#__database table and pull out the associated connection info for that
name and will then create the instance using that info for the
connection.

In addition to the component and small change to JDatabase, there
would also be a need to have some new parameter/html types for pulling
out and displaying a list of additional database connections currently
available on the site (as well as the ability to filter by the driver
in case we just want the list to show the additional MySQL databases
available).

This would make things much more manageable on my end, as I could then
input my Oracle database connection info once into the Database
Manager component and then use these new database parameter types
within my extensions to configure which Oracle database connection I
want them to use.

And with this other big project coming up this would also be handy
since it is required to be able to switch between the various database
instances we have (for example we have a "PROD" instance that serves
as our production database, but we also have clones of it made
periodically named "TEST", "FINANCE", etc. each of which would require
a different database connection string...it would be much more elegant
to be able to add a database parameter to my config, choose the 3
connections I want to use and save my changes, rather than let's say,
including 3 sets of similar parameters for me to enter in the
information directly...database_1, port_1, user_1, etc.)

I hope others feel that this might be a useful addition, and I feel it
would help make Joomla easier to use as "glue" between disparate
systems (I might be remembering wrong, but I seem to remember Louis
mentioning Joomla, the framework, PHP or all three as being a great
"glue" for connecting various systems and that being a big part of the
fun of working with it).

-Omar

[G. D. Speer]

I have client asking me for the same kind of integration to their back
office CRM.
I also recall Louis saying something similar at JDay this Spring, incluing a
specific reference to Glue - sounds like a New Project Name!
It would be nice to centralize and isolate all the DA connectivity stuff;
become
database agnostic and a robust setup to manage multiple connections.

--------------------------------------------------------------------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.92/2203 - Release Date: 06/26/09
05:53:00

[Mitch Pirtle]

Even more so for me, as I no longer use a relational database engine
at all! I am using a mix of MongoDB for unstructured data that can
still be accessed by queries and aggregate information, and project
voldemort with bdb storage for wicked fast and auto-sharded key/value
storage. Creating a JDatabase class for either makes no sense as
neither support SQL... They simply are not relational databases.

I have a suspicion that this is where online publishing is headed, as
we find ourselves programming in objects but completely stuffed with a
stinky old relational engine for storage of data. (gasp!)

-- Mitch

[Omar Ramos]

Thanks Mitch, that sounds like it would be another use case that would benefit as well and taking a look at the PHP Tutorial section of the MongoDB site:
http://www.mongodb.org/display/DOCS/PHP+Tutorial

Connecting to MongoDB is still very similar to connecting to a normal RDBMS (in terms of requiring a host/port and possibly username/password, etc.) so that simple #__database schema I put in my previous message looks like it would still work in a MongoDB situation as well,

-Omar

[pollen8]

+1 for this idea, I can think that extensions may well want to connect
to rest services as well to do CRUD, oh and perhaps SPARQL

[Hannes Papenberg]

I wouldn't make all this that complicated. Why do you need a component
to store this data? Effectively, a system plugin would already be
enough. In its parameters, it stores the DB connection details and
stores them in JApplication when its loaded. It also includes the DB
driver necessary. Then your extension can use that data and the DB
driver to connect to the different data source and you're done. That
plugin can provide the connection data for several of your extension,
you only need to type them in once and it works in Joomla without
hacking the system or adding more interface options than at the moment.

Hannes

pollen8 schrieb:

[Mitch Pirtle]

Typical Hannes response, simple and to the point :^P

System plugin it is, then - I cannot really make use of JDatabase for
non-SQL databases (other than the connection management, which in
itself can vary wildly). And I am totally in agreement with pollen8
regarding REST services as I'm building them too.

-- Mitch

[Omar Ramos]

A few months ago Hannes I would have agreed with you because this is the approach I have been using (partly), but the place where the plugin approach fails is when you need more than one connection to the same type of database.

For example, if I wanted to connect to 3 different Oracle databases I would need to create 3 separate plugins, even though they are all very similar (I would have to make the names different) because I cannot create copies of plugins in Joomla like I can for modules. That's making more duplication (albeit not a very bothersome amount of it). Additionally, it would be more difficult to know which type of database I'm dealing with, causing me to pull out and search the parameters til I find one with a driver type of "oracle".

To me the current method using plugins doesn't feel like an extensible solution, and I'm not trying to make things complicated, I'm trying to make them simpler.

This wouldn't be something needed by everyone, but having it available (even if it's turned off and hidden in the interface by default) would be useful.

-Omar

[Andrew Eddie]

I suggest moving this discussion to the Framework list and you all
getting your heads together to work out what sort of other API is
needed to support the goals you are trying to achieve. I would,
however, separate a goal of trying to run Joomla on any database
engine from giving you the ability to make calls to another database
(eg, Oracle, or I've done cases with SQL Server) during a typical
Joomla page load. I think the former is quite difficult to find a
one-size-fits-all solution (case in point, SQL Server's daft way of
handling pagination of data) but the latter is something that is
reasonable to support.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer




2009/6/29 Omar Ramos <orw...@gmail.com>: