JDatabase add/check the existence of columns?

[Mike (pearl-group.com)]

Upon installation, I need to have my component check if four columns exist in the database table.  If not, it will need to create them.  Normally I'd do this with sql statements, but am hesitant because 2.5 is the start of multiple database support.  

Is there some way, through JDatabase to check for the existence of a column and create a new column?

Thanks!

Mike

[84.le0n]

Hi Mike,
you can use getTableColumns($table, $typeOnly = true) .
The params are
* @param string $table The name of the database table.
* @param boolean $typeOnly True (default) to only return field types.
and it returns
* @return array An array of fields by table.
It can throws a JDatabaseException.

Result array's keys are column name and the values are column types.

Bye,
Eng. Gabriele Pongelli.

[Mike (pearl-group.com)]

Thank you, that helps a bit.

There's still half of the issue remaining.  getTableFields lets me know if the column exists, but how can I create it?  I've done it (below) using a sql query, but am concerned that this may not work with databases other than MySQL.  Multiple database support is part of Joomla 11.4 / 2.5, no?

Thanks!  

Mike

=====================================================

$db =& JFactory::getDbo();

$fields = $db->getTableFields("#__my_table");

if(!in_array("my_column",$fields)

{

    // Add the column using SQL query -- WILL THIS CAUSE PROBLEMS WITH NON MYSQL DATABASES?

    $tableName = $db->getPrefix() . "my_table";

    $query = "ALTER TABLE " . $db->nameQuote($table) . " ADD " . $db->nameQuote('my_column') . " INT NOT NULL";

    $db->setQuery($query);

    $db->query();

}

[84.le0n]

Actually no "ALTER TABLE" query is present for JDatabaseQuery, so it's
not possible to modify tables.
That code works for MySQL but, at first glance, I think it will not
work for PostgreSQL.
Actually you can check which database are you using with $db->name
property if it's equal to 'mysql' .

Postgresql's working query is
ALTER TABLE "<<table_name>>" ADD COLUMN "<<col_name>>" TYPE bigint NOT NULL

Bye,
Eng. Gabriele Pongelli

[Sid Sudhi]

I think - JDatabaseQuery and its sub classes need to be modified to support Alter table construct.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/NwiO1GN19gUJ.

To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

[Mike (pearl-group.com)]

Thanks.... shame, but thanks!

[Mike (pearl-group.com)]

Good thought, I'm with you.  I tossed it up in the IdeaPool.  Please vote for it:   http://ideas.joomla.org/forums/84261-joomla-idea-pool/suggestions/2478455-jdatabase-altertable-

[84.le0n]

@Sudhi
surely that changes is needed ;)

Bye,
Eng. Gabriele Pongelli.

[Mark Dexter]

The SQL scripts that are called during installation and un-install are
specific for each database type, so these can be db specific. Mark

> --
> You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

[Sid Sudhi]

Yes Mark. JDatabaseQuery being the parent class - needs to have the definition and the subclasses for each db should have the actual implementation unless impl is common for ex - mysqli and mysql or sqlserver and sqlazure.

Or you suggesting to have these in JDatabase and the children DB classes?

thanks

sudhi

[Mike Pearl]

Yes, but that means that every extension developer would need to know, write and test against every supported database.  Isn't that the purpose of a database extraction layer?

[JCR - Lab]

Le 30/12/2011 00:36, Mike Pearl a �crit :

> Yes, but that means that every extension developer would need to know,
> write and test against every supported database. Isn't that the
> purpose of a database extraction layer?

Yes, database abstraction layer means some abastract data description
convention. Usually this means describing database with some XML layer.

[Michael Babker]

You could use a script file to do all of your updates and use JDatabaseQuery.  Both methods have pros and cons, but ultimately, it'd be up to you.

[Sam Moffatt]

If every database system implemented the standard SQL, didn't change
things slightly, introduce it's own vendor specific additions, didn't
implement partial features or didn't subtly change the way the exact
same commands worked this would be true. The simple reality is that
true DB abstraction ends up in performance hell where you generate the
slowest but most compatible queries. Of course you end up creating a
pseudo language (Hibernate's HQL/Java JPQL) or essentially build an
object-relational mapping tool (e.g. JPA) to get around this as well
which starts to feel like a kludge (we sort of have half of that
anyway, one part of it is JDatabaseQuery and JTable serves as a
primitive ORM).

I'm yet to meet a database abstraction layer that works completely
without testing on all platforms. Be it due to quirks in the
abstraction layer itself, people putting minor incompatible issues
into queries (columnname as alias) or pure incompatibilities in the
standard.

I think it is rather telling that ADOdb has a compilable extension
that it can alternatively use to get reasonable performance. I feel
that the Joomla! approach strikes a reasonable balance however you
cannot completely rely upon abstraction without testing. In a sense
Joomla! forces you in a way to do this testing (you could certainly do
it blindly but again I've seen many cases where it should "just work"
and doesn't).

Further quirks in individual databases behaviour mean that optimising
an application often means changing behaviour to meet where you're
targeting. A simple example is MySQL will rewrite any subquery for an
"IN" statement (e.g. WHERE id IN (SELECT user_id FROM #__userfilter
WHERE active = 1)) into a correlated query. The effect is that for
every row in your outer query, the subquery is executed resulting in
MxN performance. This means that you end up with a situation where
doing the subquery first and then doing an implode will, particularly
with two large datasets, result in a significant performance increase.
Many other database engines will happily optimise away this inner
query properly and may even pull it's result from the DB's indexes
given the columns in questions are indexed (Oracle I know behaves this
way). However in rewriting it to be more efficient (sometimes by
orders of magnitude, I took one query that took 5 minutes down to two
queries in ~2 seconds), your other database engines that can
internally cache that inner query lose a performance boost because
each query looks slightly differently because now you're filling in
the contents of the IN with constants not the subquery. And don't get
me started having to debug XML descriptions for database schemas, I'm
yet to see any decent tools that provide clear errors unlike feeding
your favourite DB engine a query (which while far from clear itself in
many cases is usually better than the confusion I've seen from XML DB
schemas). Even within a database system you can't rely on features
existing, MySQL's MyISAM doesn't provide relational integrity while
InnoDB (and almost everything else) does provide this (can't wait for
InnoDB to be the default thus hopefully causing ISAM, itself nearly 50
years old, to disappear at least from popular MySQL use).

tl;dr: even with abstractions you still need to test because you can't
assume your assumptions are correct; see law of leaky abstractions[1]

Sam Moffatt
http://pasamio.id.au

[1] http://www.joelonsoftware.com/articles/LeakyAbstractions.html
(even has an SQL example)

</rant>?

[Mike Pearl]

Michael,

Earlier in this thread Gabriele pointed out that JDatabaseQuery does not offer a way to ALTER TABLE, and that's the issue here.

Mike

[Mike Pearl]

Sam,

You've given an excellent discussion of the complexities of true multi-database support.  At the same time Joomla is promising support for multiple databases in very short order.  Personally, I can't afford licenses for Oracle and SQL Server to test my extensions.  So without opening a huge can of worms, is it really too difficult to add support for adding and removing a column from every supported database?

Mike

[Jeremy Wilken]

Mike,

You have to also consider the value of what you are trying to do. First, why are you having to change tables, and if you can plan ahead maybe you can avoid it in the future. Secondly, it may not be worth setting up your extension for mutli-database. This is a sad truth, as many of us are unable to test and therefore support commercial databases. At this point in time, it appears that unless somebody with commercial databases is willing to offer more assistance that we are faced with supporting selected databases in our extensions. Not ideal no, but at this juncture its not possible to use anything Joomla offers to alter tables across multiple databases without having a more robust library (which Sam goes into good detail about).

While I would also like to be able to support all databases, I think you have to consider if its possible for your project and limit your extensions to the ones you can support for the time being. It might occur that a more robust layer is developed, but as has been pointed out, its not there and if you can't test it then its hard to support it.

Thats just my opinion, I won't be offering support in things I develop unless I'm able to coordinate with someone to test things for me on other database servers.

Jeremy

[Sid Sudhi]

Good points from Sam/Jeremy. Now with multi db support - may be on extensions directory - we introduce a new flag/icon supporting multidb (SQLServer/Azure/Oracle/mysql).

For folks interested to test on other commercial databases - no need any licensing.

SQLServer Express and Oracle Express are completely free and can be setup on your local (windows machines) Of course Oracle can be installed and configured on Linux. 

A google on sqlserver express will lead you to download SQLServer 2008 R2 Express. Then  all you need is SQLServer drivers for PHP (dlls) which needs to be copied to ext folder under php, an entry in php.ini and restart of Apache/IIS should get you up and running with PHP/SQLServer Express.

For Oracle - download the Oracle express and similar configuration would get you up and running in few hours.

One of the earliest versions of CMS (In midst of 1.7 development) we had tested CMS with Oracle, but not lately. So end-end seamless functionality might not be supported yet for Oracle from CMS standpoint.

I am willing to help as time permits if any extensions need to be tested on sqlserver.

JFYI:  We even ported Joomla Ad Agency to SQLServer recently and is completely available for free. However I cannot share the code since it is a commercial edition. I have reached out to the developer, but no response from them. 

Thanks

Sudhi

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/ByrS4iubZPYJ.

[Sam Moffatt]

To address the main question, that JDatabaseQuery doesn't offer an
ALTER option at present: someone just needs to write the code for it
and submit a pull request. Could be you, could be Gabriele, could be
Sudhi or it could be Jeremy. Could be me though at the present
juncture I have enough things I'm busy breaking (plus where would I
find time to write such extensive rants?). Someone just needs to match
the style already reasonably obviously laid out and add it.

So TBQH bothering with Oracle is likely a bit much unless you're in
that environment, though testing in an Oracle like environment is not
that hard. You can configure MySQL to behave in an Oracle like mode
[1] and also PostgreSQL a reasonable level of Oracle compatibility as
well. If you can get your code to work fine in MySQL's Oracle mode and
normal PostgreSQL, supporting Oracle proper isn't going to be a
concern and I'd wait for someone to ask for it first ("Oracle version
available on request, please contact developer for details").

However both Oracle and MSSQL are freely available in their cut down
form. Oracle offers Oracle Database Express Edition 11g [2] and
Microsoft offers a copy of SQL Server Express for free [3] though I
suggest using WebPI to install it [4] as it'll also give you an easy
way to install the SQLSRV PHP drivers if you can tolerate using IIS.

Cheers,

Sam Moffatt
http://pasamio.id.au

Tip of the day: install the Oracle Express Edition in a VM and never
your desktop.

[1] http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_oracle
[2] http://www.oracle.com/technetwork/database/express-edition/overview/index.html
[3] http://www.microsoft.com/sqlserver/en/us/editions/express.aspx
[4] http://www.microsoft.com/web/platform/database.aspx

> --
> You received this message because you are subscribed to the Google Groups
> "Joomla! General Development" group.

> To view this discussion on the web, visit

> https://groups.google.com/d/msg/joomla-dev-general/-/pUWQkO4WvykJ.

[Sid Sudhi]

Sam,

will try to get to db stuff shortly -- i have tons of todo things to support finder stuff function well on sqlserver.. right now -- there are some tests failing for finders.

[Sam Moffatt]

No pressure, anyone can write the code to make it happen however
someone needs to write the code. Doesn't have to be you, you just
replied :p

Cheers,

Sam Moffatt
http://pasamio.id.au

[84.le0n]

Hi all,

@ Mike Pearl
actually an extension developer has to create each .sql file for
installing and uninstalling phase, for each database he/she wants
support, and yes, it's not a good method for a developer who, in a
multi database environment, has to know syntax and make tests for all
databases.

As I've said, there's no ALTER TABLE nor CREATE TABLE or similar
creation query inside JDatabaseQuery, so actually it's only possible
to send these commands inside extern .sql files.

Actually there are some ways to address ALTER/CREATE TABLE capability to 3pd:
1) let them write each sql files for each database, this solution is
in use but not meet really a database abstraction layer
1.1) write some document on the wiki to explain how to translate
common query to all database syntax (CREATE, ALTER, DROP, INSERT) -->
short term solution

2) add these common query to JDatabaseQuery and develop them inside
platform so 3pd can execute queries inside preflight/install/update
method of installer script class
http://docs.joomla.org/Developing_a_Model-View-Controller_(MVC)_Component_for_Joomla!1.7_-_Part_15
--> med term solution, we've to develop them; 3pd can follow this wiki
page to add JDatabaseQuery behavior for all databases and also
maintain sql files for MySQL (unique database type explained in
manifest file to use sql file).

3) inside manifest file add some new xml definition about creation and
altering tables, somewhere translated to JDatabaseQuery during install
phase
--> long term solution, we've to develop same as 2) and add
xml<->JDatabaseQuery translation handling .

I think that a good starting point is 2) , and you?

About subqueries, actual working example is this
http://docs.joomla.org/How_to_use_the_database_classes_in_your_script#Subqueries
but it works having at least two separate JDatabaseQuery objects.

Some times ago I've thought about ALTER TABLE / CREATE , putting them
in my PostgreSQL driver but this add too much extra code not requested
in my driver and not yet discussed.

Bye,
Eng. Gabriele Pongelli.

[Mike Pearl]

Jeremy,

This is a good point.  At the same time, the extension I'm now building is an upgrade to an older version which adds new functionality and requires a few additional columns.  My alternative would be to create a new table then move existing data to the new table.  In either case, I'm out of luck because JDatabaseQuery doesn't support creating or altering tables.

Mike

[Mike Pearl]

Gabriele,

I agree that's a great starting point - #2 would be especially helpful.

I also suspect that commercial database vendors (e.g., Oracle & Microsoft) would be willing to develop Joomla code in support of their products.  Has anyone contacted them about this?

Mike

[Andrew Eddie]

I actually have started to address this issue with the db importer and exporter classes. Only done them for MySQL at present. Designed them so I could easily diff and upgrade schema between component upgrades without having to worry about individual deltas.

Regards,
Andrew Eddie

[84.le0n]

Hi Andrew,
I'm watching that classes, what's them main task?
Are you realizing them to address solution 3) of my list ?

Bye,
Eng. Gabriele Pongelli.