Table modification in install.php for com_Biblestudy

[tfuller]

In order to provide an upgrade path for my component I need to test and then modify tables created by my component.

I think the best way is to use the install.php file that is invoked after the component install routine is completed.

I can load the db object and do a SELECT query to find out the status of a field that tells me the schema version of my component's db BUT when I try to UPDATE the row it doesn't work.

Eventually given the test of the schema I will then do a conditional ALTER table command, etc - but I need to know if there is something I'm doing wrong or if the $database object prevents me from doing anything but SELECT.

Here is my code:

Code: Select all

$database	= & JFactory::getDBO();
		$query = 'SELECT schemaVersion FROM #__bsms_schemaVersion';
		$database->setQuery( $query );
		$schema_version = $database->loadObjectList();
		$schema_version = $schema_version[0];
		echo 'The database schema for this version of Bible Study is: '.$schema_version->schemaVersion.'<br>';
		if ($schema_version->schemaVersion == 502) 
			{
			$query = 'UPDATE #__bsms_schemaVersion SET schemaVersion="503" WHERE id="1"';
			$database->setQuery($query); 
			$query = 'SELECT schemaVersion FROM #__bsms_schemaVersion';
			$database->setQuery( $query );
			$schema_version3 = $database->loadObjectList();
			$schema_version3 = $schema_version3[0];
			echo 'The database schema for this version of Bible Study is now: '.$schema_version3->schemaVersion.'<br>';
			}

When I run the install the install.sql creates the table schemaVersion and inserts the row 502 into id 1. The install.php accurately reads the row as 502, but then after running the UPDATE lines the next $query3 returns the same value of 502 - no UPDATE occurred. BTW - removing the IF statements does not change how it runs.

Is there a better way to upgrade components that have to have their db's updated?

Thanks!

Tom

[tfuller]

To answer my own question - I found the way by looking at the FireBoard component:

Code: Select all

<?php $database->setQuery ("SELECT schemaVersion FROM #__bsms_schemaVersion WHERE id='1'");
		$schema_version = $database->loadResult();
		echo 'The database schema for this version of Bible Study is: '.$schema_version.'<br>';
		if ($schema_version == 502) 
			{
			$database->setQuery ("UPDATE `#__bsms_schemaVersion` SET `schemaVersion` = '503';");
			$database->query();
			$database->setQuery ("SELECT schemaVersion FROM #__bsms_schemaVersion");
			$schema_version3 = $database->loadResult();
			echo 'The database schema for this version of Bible Study is now: '.$schema_version3.'<br>';
			}
?>

The difference is 1. using the `fieldhere` around the query and following the query line with

Code: Select all

<?php $database->query();?>

Notice also I used loadResult which basically gives you back the first row rather than loadObjectlist which you then have to parse.