Hi,

Following few threads about Joomla! and non-MySQL back end databases I've read about a so called JQuery class but can't see any further information.

In case Joomla! is planned to be a DB-transperant CMS in the future, is there anyone working on a Query Builder class? 


Plamen

Hi,

In the early stages of 1.5 we had a JQuery class ... it was built by masterchief ... as the code base progressed it was taken out because we weren't deemed "ready" for it.  It still exists in a couple of the summer of code repositories I believe and I am certain masterchief has a copy but it is no longer in the core trunk .... for now

I hope that helps.

Louis

Yep, it helps as a starting point :-) 

Thanks!

Plamen

you interested in working on one?

Louis

Yep, I'd like to see if I can help.

See the reason why I am asking about it:

"SQL queries - too much MySQL. PostgreSQL is well possible": http://forum.joomla.org/index.php/topic,193981.0.html.

Plamen

Found you a starting point... goto town

Code: Select all

<?php
/**
* @version      $Id: helper.php 747 2006-08-10 22:32:34Z eddieajau $
* @package      JX-Open
* @subpackage   Database
* @copyright   (C) 2007 New Life in IT Pty Ltd. All rights reserved.
* @license      LGPL <http://www.gnu.org/licenses/lgpl.html>
*/

/**
* Query Element Class
*
* @author      Andrew Eddie <andrew.eddie@newlifeinit.com>
* @package      JX-Open
* @subpackage   Database
*/
class JXQueryElement
{
   /** @var string The name of the element */
   var $_name = null;
   /** @var array An array of elements */
   var $_elements = null;
   /** @var string Glue piece */
   var $_glue = null;

   /**
    * Constructor
    * @param   string   The name of the element
    * @param   mixed   String or array
    * @param   string   The glue for elements
    */
   function JXQueryElement( $name, $elements, $glue=',' )
   {
      $this->_elements   = array();
      $this->_name      = $name;
      $this->append( $elements );
      $this->_glue      = $glue;
   }

   /**
    * Appends element parts to the internal list
    * @param   mixed   String or array
    */
   function append( $elements )
   {
      if (is_array( $elements )) {
         $this->_elements = array_unique( array_merge( $this->_elements, $elements ) );
      } else {
         $this->_elements = array_unique( array_merge( $this->_elements, array( $elements ) ) );
      }
   }

   /**
    * Render the query element
    * @return   string
    */
   function toString()
   {
      return "\n{$this->_name} " . implode( $this->_glue, $this->_elements );
   }
}

/**
* Query Building Class
*
* @author      Andrew Eddie <andrew.eddie@newlifeinit.com>
* @package      JX-Open
* @subpackage   Database
*/
class JXQuery
{
   /** @var string The query type */
   var $_type = '';
   /** @var object The select element */
   var $_select = null;
   /** @var object The from element */
   var $_from = null;
   /** @var object The join element */
   var $_join = null;
   /** @var object The where element */
   var $_where = null;
   /** @var object The where element */
   var $_group = null;
   /** @var object The where element */
   var $_having = null;
   /** @var object The where element */
   var $_order = null;

   /**
    * Constructor
    */
   function JXQuery()
   {
   }

   /**
    * @param   mixed   A string or an array of field names
    */
   function select( $columns )
   {
      $this->_type = 'select';
      if (is_null( $this->_select )) {
         $this->_select = new JXQueryElement( 'SELECT', $columns );
      } else {
         $this->_select->append( $columns );
      }
   }

   /**
    * @param   mixed   A string or array of table names
    */
   function from( $tables )
   {
      if (is_null( $this->_from )) {
         $this->_from = new JXQueryElement( 'FROM', $tables );
      } else {
         $this->_from->append( $tables );
      }
   }

   /**
    * @param   string
    * @param   string
    */
   function join( $type, $conditions )
   {
      if (is_null( $this->_join )) {
         $this->_join = array();
      }
      $this->_join[] = new JXQueryElement( strtoupper( $type ) . ' JOIN', $conditions );
   }

   /**
    * @param   mixed   A string or array of where conditions
    * @param   string
    */
   function where( $conditions, $glue='AND' )
   {
      if (is_null( $this->_where )) {
         $glue = strtoupper( $glue );
         $this->_where = new JXQueryElement(  'WHERE', $conditions, "\n\t$glue " );
      } else {
         $this->_where->append( $conditions );
      }
   }

   /**
    * @param   mixed   A string or array of ordering columns
    */
   function group( $columns )
   {
      if (is_null( $this->_group )) {
         $this->_group = new JXQueryElement( 'GROUP BY', $columns );
      } else {
         $this->_group->append( $columns );
      }
   }

   /**
    * @param   mixed   A string or array of ordering columns
    */
   function having( $columns )
   {
      if (is_null( $this->_having )) {
         $this->_having = new JXQueryElement( 'HAVING', $columns );
      } else {
         $this->_having->append( $columns );
      }
   }

   /**
    * @param   mixed   A string or array of ordering columns
    */
   function order( $columns )
   {
      if (is_null( $this->_order )) {
         $this->_order = new JXQueryElement( 'ORDER BY', $columns );
      } else {
         $this->_order->append( $columns );
      }
   }

   /**
    * @return   string   The completed query
    */
   function toString()
   {
      $query = '';

      switch ($this->_type)
      {
         case 'select':
            $query .= $this->_select->toString();
            $query .= $this->_from->toString();
            if ($this->_join) {
               // special case for joins
               foreach ($this->_join as $join) {
                  $query .= $join->toString();
               }
            }
            if ($this->_where) {
               $query .= $this->_where->toString();
            }
            if ($this->_group) {
               $query .= $this->_group->toString();
            }
            if ($this->_having) {
               $query .= $this->_having->toString();
            }
            if ($this->_order) {
               $query .= $this->_order->toString();
            }
            break;
      }

      return $query;
   }
}


Thanks mate ! Will take a look in couple of days.

Plamen

Well, now that I took a quick look over the code I think this class should be started from scratch. Few reasons: nested 'where' conditions, mixed 'glue', i.e. "where (a=1) AND (b=3) OR (c=4) AND ((d=3) AND (e=7))", etc.

Also, I still can't figure out, though some ideas are popping, how to make it DB-aware, I mean Query Builder should be Database Specific! Maybe a base JQuery class and DB-aware classes extending JQuery..?  like

Code: Select all

class JQueryMySQL extends JQuery ...


Plamen

Yep ... I was thinking the same thing actually.

In fact ... I would even go as far as to say taht JDatabase should have a factory for its JQuery class

[php]
class JDatabaseMySQL extends JDatabase \
{
    function getQuery()
    {
        return new JQueryMySQL();
    }
}

class JQueryMySQL extends JQuery
{

}
[/php]

You are more than welcome to rewrite anything you wish go to town ... I gave you that simply as an example

Keep me posted on progress,

Louis

Hi,

Plamendp, if you make this into a project on joomlacode.org, I'll be happy to pitch in with what thoughts, help, coding, and general time I can offer. DB abstraction for the framework is important and I really want to see it done, and done well. Let's get to work on this :-)

Hello
:-Lightinthedark

Hi light,

Have no idea how to create a project. Never been a part of such. Always coding alone or with couple of people. Besides, it's a hobby for me at the moment (or doing it for my company needs only), used to do it for living many many years ago :-) But hey, there is always the first time! So, I'll give it a try and if stuck will ask again.


Plamen

Hi,

Is this:
http://joomlacode.org/gf/search/?search=project_0&type=project&words=JQuery&Search=Search
created by you plamendp? I get a "permission denied" error when I try to view the project. If it's your new project, is there any chance I could be added as a developer? If it's not yours but some old remnant, we should probably contact the developers of that project and join that existing resource rather than create a duplicate. Would anyone know how to contact the folks responsible for that project?

Hello
:-Lightinthedark

OK, I've submitted a project JQuery for 1.5 on the joomforge since the other JQuery project's name suggests that it's for 1.0, and it seems to be old and innactive. I'll edit this post once the project is approved to give the url and point further technical discussions to the project's forum.

Although I have taken the step of setting up the project, and intend to be well involved, I make no claim to be any sort of lead developer or someone who can/will do this all on his own, and would like to take a moment to appeal to other developers to pitch in where they can.

Hello
:-Lightinthedark

[edit] The project is now set up and approved, and I've started the ball rolling with a todo and forum post to kick off discussion of design. The project can be accessed at:

http://joomlacode.org/gf/project/jquery_1_5/

If anyone can help with this work, all applications are welcome, and I'll add anyone to the project that thinks they can help.

I'll take a look tomorrow.. got some coding already done and many questions (expected) raising. It seems JQuery is gona be an SQL-code atoms provider, atoms specific for different DBs, but common in terms of RDB logic. Concatenations, for example, or CASE WHEN blocks..

The last one is very interesting btw..  Consider this:

CASE WHEN (a>1) THEN 'some text' ELSE 12345 END

In MySQL the result is string OR number, MySQL is flexible enough to handle this... In Postgresql  both WOULD be results MUST match in TYPE! Now PG needs this:

CASE WHEN (a>1) THEN  'some text' ELSE CAST(12345 AS VARCHAR) END   

or in more generalized way

CASE WHEN (a>1) THEN CAST('some text' AS VARCHAR) ELSE CAST(12345 AS VARCHAR) END   


Hense the need of sqlCaseBlock($conditions, $results, $resultType) to provide uniform SQL piece of code... 

etc etc etc ... 


Plamen

Excuse me if I am a bit off track here, but why don't you try integrating the Adodb or Adodblite libraries? I already use them with some of my components and they work very well. Seems a bit like re-inventing the wheel to me, the creation of another db abstraction library...

-Lobos

Hi,

Integrating external framework/db layer/whatsoever (AdoDB or Pear or ZendFramework, etc.)  is a matter of Core Dev Team decision. I am not a member of that team, but for what I see so far I am under impression they prefer to develop a whole new, its own, Joomla! Database layer and everything else that constitute a Framework. It is tempting to use 3rd party frameworks (easy job), but they are really too common sometimes, providing too simple and casual functionality. 

I personally recommend developing the Joomla! Framework. Think about licenses, sometimes too heavy frameworks, etc.

Particularly, every query builder I met in all frameworks lack something, more or less. Most of them are capable to build only simple queries and does not provide a safety by means of RDBMS SQL incompatibility, just to give you an example. Also: Sub-selects, complex WHERE and FROM/JOIN sections, automated identifier quoting, intuitive and simple API interface, etc. 

A side note: guys, we need a SQL Tokenizer (and Parser). Anyone met a full featured one somewhere? It is am must for proper SQL keywords quoting and query analyze.




Plamen 

Meanwhile...

Check this out, topic "First blood":

http://joomlacode.org/gf/project/jquery ... um_id=6460

Plamen

Isnt this something that also could use the "NBS libraries" SOC project that Enno is working with?

"Extending the Nested Sets Model with "Hardlinked Nested Sets"

http://forum.joomla.org/index.php/topic,189840.0.html

TBH I can't see the relation? Could you please explain? 


Plamen

Plamen -

I looked over your jquery.php and mysql.php attachments and am very impressed with your progress. Is the basic plan that there would be a separate file for each database driver? So, there could be an oracle.php and a mssqlserver.php, etc., with a configuration decision made at installation time?

Of course this is very valuable work for Joomla! and you are to be encouraged for these efforts.

Thanks very much!
Amy

@Darb - Enno's project is not related to this. Essentially, Enno's project could change the database model - the table structures - to implement the node concepts for the content architecture. What Plamen is working on is database abstraction layer where the database itself could be stored in a choice of many RDBMS instead of only MySQL.

AmyStephen wrote:Plamen -

I looked over your jquery.php and mysql.php attachments and am very impressed with your progress. Is the basic plan that there would be a separate file for each database driver? So, there could be an oracle.php and a mssqlserver.php, etc., with a configuration decision made at installation time?

Basicaly, there is no need of different JQuery for every RDBMS. All DB specific tasks (due to different SQL implementation and deviations from SQL "standards") would be done by the underlaying DBO methods (a reference to the currently used DBO is always available as a JQuery property, particualty "_dbo" in current code).  The point is, we should have one and only one Database Specific class: JDatabaseMySQL/JDatabaseOracle/JDatabasePgSQL/etc. providing *all* DB specific behaviour, including SQL-related.

Well, having separate JQueryXXXX classes makes the layer idealy modular and self-explaining, but still, I found the "single DB-aware class" approach more convenient.

Plamen

Actually, I do understand jquery.php to be the database abstraction layer. It will be the same, as you say, for each implementation. My question was more directed at the mysql.php file. I am assuming there will be one of those files containing the class for each database driver supported? Does the mysql.php file have the JDatabaseMySQL class in it?

Thanks for your response,
Amy

The mysql.php is the same mysql.php as the current Joomla! 1.5 mysql.php      It defines the JDatabaseMySQL class.

I just added few methods to provide an SQL-implementation abstraction along with the current DB abstraction, which, generaly speaking appears to be the same thing (?).  Some guru  here, help, please and define terms properly. I am afraid I mess things like abstraction, layers etc   

Summary:

1. New file jquery.php defining JQuery class extending JObject class.
2. Old but modified mysql.php file, defining JDatabaseMySQL class, extending JDatabase
3. There is no class extending JQuery!  JQuery objects are "informed" (during the creation/construction phase) about the DBO they are tied (JDatabaseMySQL, JDatabasePgSQL, etc).

In few days I'll upload the newest code to http://www.joomlacode.org, including pgsql.php (PostgreSQL DB driver).

Plamen

plamendp wrote:including pgsql.php (PostgreSQL DB driver).

Plamen -

It is certainly me using the wrong terms. But, you are doing exactly what I thought. Your new pgsql.php file will contain the PostgresSQL DB driver - the JDatabasePgSQL class. Just like the current mysql.php defines the MySQL DB driver - the JDatabaseMySQL class.

For each database that we would want to offer for use with Joomla!, we would need to create a JDatabaseXYZ class - which would be contained in a new XYZsql.php file. Each of these new database classes would be developed to work as the DBO for your JQuery class.

I believe the JQuery class is the database abstraction layer allowing Joomla! to interact with a number of databases, provided a database class is created for it (and it can work with JQuery.)

I will look for your next upload. This is really great - thanks!
Amy

Thank you Amy!

I get that this could be the data abstraction layer for Joomla.
Which makes this the answer to the question I've been after.

I however do not have the experience to accomplish this and am looking for someone to help me (and ultimately the whole Joomla community) with an mssql.php driver for a JDatabaseMSSQL class.

Plamen-let me know if you could accomplish this with at least the core Joomla install and we'll work our way out from there.  I'm sure 3rd party developers would love to get in on being compliant to your database abstraction layer.

Just so MSSQL doesn't seem like some moronic idea, I'll give you two points to ponder.
1) there are free versions and cheap hosted options for MSSQL...it's not a big deal anymore
2) I just read a report on CMS in a pretty big web hosting mag.  They had four other options in their top 5 that I'd never heard of.  The number 2 was Joomla.  It only ranked low on security.  On the MSSQL platform, it would be significantly more secure.  If that were to happen, Joomla would be the number one choice of businesses worldwide.  They would start asking for it by name.  Think about what this entire community would look like with more funding. 

If someone can make this happen, let me know.  I may be able to get a little bit of funding and I'll see what I can do personally.  I've got three kids and one income, so...any others out there that'd like to speak up...it might be nice if you could help as well.

@Darb - Enno's project is not related to this. Essentially, Enno's project could change the database model - the table structures - to implement the node concepts for the content architecture. What Plamen is working on is database abstraction layer where the database itself could be stored in a choice of many RDBMS instead of only MySQL.

Thanks Amy for explaining better for me, I just missunderstood a little bit just wanted to be sure that you really can´t reuse some of the building classes Enno and others is developing so most of the core logic and its models is to be used in one place only :-)

Seriously looking for a quote on that.  It's not worth posting a project right now.  I just need to see if someone can actually get a standardized data abstraction layer completed with a mssql driver.

Also check out this thred :http://forum.joomla.org/index.php/topic,193981.0.html

Not sure of the main differences here...but I think everyone's looking for the same results.

mknz wrote:Seriously looking for a quote on that.  It's not worth posting a project right now.  I just need to see if someone can actually get a standardized data abstraction layer completed with a mssql driver.

Also check out this thred :http://forum.joomla.org/index.php/topic,193981.0.html

Not sure of the main differences here...but I think everyone's looking for the same results.

I am afraid I can't understand what do you mean, mate :-)  Are you asking

1) if someone is working on Joomla! with MSSQL Database Backend or are you asking
2) if someone is working on a Query Builder for MSSQL ?

These are different things and I am little bit confused.  It might be because my native language ain't English but.. Can you make your question more clear ?

Anyhow, the answer to both questions is "Yes". But not as a single project.

For 1) search the forum, there you will find some threads about it
For 2) see http://joomlacode.org/gf/project/jquery_1_5/  (well, it's for MySQL at the moment, but multi-DB by design)



Plamen