PostgreSQL and schemas
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Sun, 2007-08-19 07:05
|
|
I wanted to use a new schema in a PostgreSQL 8.1.9 data base for Gallery 2.2.2. I could find no way to specify using a schema. I tried using the schema with a dot in front of the table name prefix (gallery2.g2_) in the install, but the installation reported that the dot was an illegal character. I tried to manually edit the config.php file to add the schema prefix to tablePrefix, but then the installation failed on the next step. So I reverted the file back without the schema prefix. Is there a way to use data base schemas? If not, it would be a nice feature for those databases that have them. |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Tue, 2007-08-21 02:53
|
|
dunno if this works, but worth a shot: use "schemaName." as the database prefix (as you did before, if the installer rejects this, set it manually in config.php). |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Tue, 2007-08-21 21:20
|
|
I looked in config.php. There is not a property named anything like database prefix. There is a "database" property, but changing that would cause the system to try to connect to a nonexistent database. The logical place was tablePrefix. But, as I posted, that did not work. Database table in postgreSQL in schemas can bet address schema.table , so the tablePrefix would serve if it would let a dot be an authorized character. Ideally, there would be a schemaName property that would be prepended to the tablePrefix property and include a dot if schemaName was set. I guess this is just a wish for now. |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Tue, 2007-08-21 21:32
|
|
eek, sorry.. yes, I meant table prefix. You can edit the preg_match in install/steps/DatabaseSetupStep.class to make the UI allow a . character. Need more detail on what went wrong afterwards to guess what is going on... if the underlying systems (ADOdb and PHP) allow this, seems like it should work for G2.. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Fri, 2007-08-24 17:55
|
|
Well that started to work but fails when the first index is attempted to be created. The transaction fails and nothing is created in the data base schema. When the index is created there is a syntax error. Here are three entires from the log file. The first creates the table, the second adds the primary key, and the third is the error from the attempt to creates the index on the table. 2007-08-23 13:34:20 [TMP_SESSION_ID_DI_NOISSES_PMT] <hr /> 2007-08-23 13:34:20 [TMP_SESSION_ID_DI_NOISSES_PMT] <hr /> 2007-08-23 13:34:20 [TMP_SESSION_ID_DI_NOISSES_PMT] postgres7 error: [-1: ERROR: You cannot specify the schema of the index since that is implied with the table schema on which the index is created. The same does not apply to dropping an index. I edited lib/adodb/drivers/adodb-postgres64.inc.php like this to take care of the syntax problem for indexes. --- /<original directories>/gallery2/lib/adodb/drivers/adodb-postgres64.inc.php 2006-12-27 22:55:35.000000000 -0500
+++ /<edited directories>/lib/adodb/drivers/adodb-postgres64.inc.php 2007-08-24 01:59:08.000000000 -0400
@@ -716,6 +716,29 @@
function _query($sql,$inputarr)
{
$this->_errorMsg = false;
+ $sqlParts = explode(" ", $sql);
+ if (count($sqlParts) > 2 &&
+ $sqlParts[0] == "CREATE" &&
+ $sqlParts[1] == "INDEX")
+ {
+ $indexNameParts = explode(".", $sqlParts[2]);
+ if (count($indexNameParts) == 2)
+ {
+ $sqlParts[2] = $indexNameParts[1];
+ $sql = implode(" ", $sqlParts);
+ }
+ }
+ else if (count($sqlParts) > 3 &&
+ $sqlParts[0] == "CREATE" &&
+ $sqlParts[2] == "INDEX")
+ {
+ $indexNameParts = explode(".", $sqlParts[3]);
+ if (count($indexNameParts) == 2)
+ {
+ $sqlParts[3] = $indexNameParts[1];
+ $sql = implode(" ", $sqlParts);
+ }
+ }
if ($inputarr) {
/*
It appears that PREPARE/EXECUTE is slower for many queries.
There are still additional difficulties installing the addon modules, but this edit gets the core installed. I am working on the other module installation now. I'll pass that on after I solve it. I also noticed that gallery2 uses pg_exec(). This call is now pg_query in php. This from the php page: Quote:
Note: This function used to be called pg_exec(). pg_exec() is still available for compatibility reasons, but users are encouraged to use the newer name. Just thought I'd pass this along. |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Fri, 2007-08-24 19:40
|
|
so CREATE INDEX is the only syntax you found so far with a problem? good news. if you get a test install going, try activating tons of modules and then running the unit tests (visit lib/tools/phpunit/index.php in the install).. this will exercise the db and maybe expose any other problems. re: pg_exec.. this is in the underlying ADOdb library, not gallery. we avoid changing that code unless needed (so we'll certainly change it if pg_exec ever gets removed!) thanks for the tip. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Fri, 2007-08-24 20:29
|
|
OK. Now I am stumped. Any modules other than core I try to install fail. They all fail trying to do exactly the same thing: creating the sequence gallery2.g2_SequenceLock because it already exits. I've fumbled around in the code, but have not found what causes this. Help would be appreciated. Here are the errors. I only tried installing six modules to cut down on output since it is the same for all modules. Quote:
Notice: Undefined index: Schema in /var/www/gallery2base/modules/core/classes/GalleryStorage/GalleryStorageExtras.class on line 1248 Then I get: Quote:
Unable to install the Dcraw module Quote:
Error (ERROR_STORAGE_FAILURE) and in the debug output the first error is: Quote:
Loading plugin dcraw After that, all additional commands are aborted because the transaction is aborted. Quote:
Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Fri, 2007-08-24 20:50
|
|
To avoid the warnings at the top you need to get the ADOdb "MetaTables" function working. Gallery is not providing any SQL here with the schema name in it.. does postgres have a concept of "selected" or "active" schema for a db connection? MetaTables in lib/adodb/drivers/adodb-postgres64.inc.php does a select on "pg_tables".. not sure how you'd point this at the right schema unless you can select an active schema that applies to future queries. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Fri, 2007-08-24 21:17
|
|
mindless wrote:
To avoid the warnings at the top you need to get the ADOdb "MetaTables" function working. Gallery is not providing any SQL here with the schema name in it.. does postgres have a concept of "selected" or "active" schema for a db connection? After the connection is made, you can set the search_path to only include the schema desired, ie. mindless wrote:
MetaTables in lib/adodb/drivers/adodb-postgres64.inc.php does a select on "pg_tables".. not sure how you'd point this at the right schema unless you can select an active schema that applies to future queries. I saw that. I even edited it to change |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Fri, 2007-08-24 22:29
|
|
I suggest trying this next: function _setConnectionSettings(&$db) {
$this->_traceStart();
$db->execute('set search_path to gallery2');
$this->_traceStop();
}
Here we are hardcoding the schema name, but if you get things working we can look into making that a parameter entered in the installer. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Sat, 2007-08-25 03:39
|
|
Eureka! That worked. All went like it was installing into a database all its own. It will be nice when that is a parameter in the installer. I'll be glad to test. Having the ability to use schema will make database management, backup, and restore much easier. Oracle uses schema as well. MySql treats databases as schema from what I have experienced without separate schema in each database. I installed almost all the plugin modules. The only ones I did not install were the commerce plugins, migration, url rewrite, rss, Nokia Image Upload, Picasa, Publish XP, WebCam, WebDAV, and the themes other than Matrix. I may install some of the themes later. The ones I installed installed without a hitch. I could not find lib/tools/phpunit/index.php anywhere in the installation. |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Sat, 2007-08-25 04:16
|
|
great! (unit tests are included only in the developer package) |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Sat, 2007-08-25 07:15
|
|
I surmised that. I downloaded the full package. I'm getting the developer now. |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Mon, 2007-08-27 23:22
|
|
Ok, got some code to test... Index: install/config.php-template
===================================================================
--- install/config.php-template (revision 16960)
+++ install/config.php-template (working copy)
@@ -103,6 +103,7 @@
$storeConfig['database'] = 'gallery2';
$storeConfig['username'] = '';
$storeConfig['password'] = '';
+$storeConfig['schema'] = '';
$storeConfig['tablePrefix'] = 'g2_';
$storeConfig['columnPrefix'] = 'g_';
$storeConfig['usePersistentConnections'] = false;
Index: install/steps/DatabaseSetupStep.class
===================================================================
--- install/steps/DatabaseSetupStep.class (revision 16960)
+++ install/steps/DatabaseSetupStep.class (working copy)
@@ -41,6 +41,7 @@
$this->_config['username'] = 'root';
$this->_config['password'] = '';
$this->_config['database'] = 'gallery2';
+ $this->_config['schema'] = '';
$this->_config['tablePrefix'] = 'g2_';
$this->_config['columnPrefix'] = 'g_';
$firstConfig = true;
@@ -50,7 +51,7 @@
$templateData['password'] = strlen($this->_config['password']) ? '******' : '';
if (!empty($_POST['action']) && $_POST['action'] == 'save') {
- foreach (array('type', 'hostname', 'username', 'database',
+ foreach (array('type', 'hostname', 'username', 'database', 'schema',
'tablePrefix', 'columnPrefix') as $key) {
$this->_config[$key] = $this->sanitize($_POST[$key]);
}
@@ -163,6 +164,9 @@
$templateData['errors'][] =
_('Unable to connect to database with the information provided.');
}
+ if ($dbPlatformType == 'postgres7' && !empty($this->_config['schema'])) {
+ $this->_db->Execute('set search_path to ' . $this->_config['schema']);
+ }
}
}
Index: install/steps/CreateConfigFileStep.class
===================================================================
--- install/steps/CreateConfigFileStep.class (revision 16960)
+++ install/steps/CreateConfigFileStep.class (working copy)
@@ -156,7 +156,7 @@
}
foreach (array('type', 'hostname', 'database', 'username',
- 'password', 'tablePrefix', 'columnPrefix') as $key) {
+ 'password', 'schema', 'tablePrefix', 'columnPrefix') as $key) {
if (preg_match("/^.storeConfig\['$key'\] = '.*';/", $line)) {
$newStoreConfig[$key] = strtr($newStoreConfig[$key], $addslashes);
$line = "\$storeConfig['$key'] = '$newStoreConfig[$key]';\n";
Index: install/templates/DatabaseSetupRequest.html
===================================================================
--- install/templates/DatabaseSetupRequest.html (revision 16960)
+++ install/templates/DatabaseSetupRequest.html (working copy)
@@ -16,7 +16,7 @@
</td>
<td>
- <select name="type">
+ <select name="type" onchange="dbTypeChange(this)">
<?php foreach ($templateData['dbList'] as $key => $value): ?>
<option value="<?php print $key ?>"
<?php if (!empty($templateData['dbSelected'][$key])): ?>
@@ -25,6 +25,15 @@
><?php print $value ?></option>
<?php endforeach; ?>
</select>
+ <script type="text/javascript">
+ // <![CDATA[
+ function dbTypeChange(sel) {
+ var type = sel.options[sel.selectedIndex].value;
+ document.getElementById('dbSchema').style.display =
+ (type == 'postgres7') ? '' : 'none';
+ }
+ // ]]>
+ </script>
</td>
</tr>
<?php if (!empty($templateData['error']['phpDbMissing'])): ?>
@@ -68,6 +77,14 @@
value="<?php print $templateData['config']['database'] ?>" />
</td>
</tr>
+ <tr id="dbSchema" style="display:none">
+ <td class="label"><?php print _('DB Schema:') ?></td>
+ <td>
+ <input name="schema" type="text" size="30"
+ value="<?php print $templateData['config']['schema'] ?>" />
+ <?php print _('(optional)') ?>
+ </td>
+ </tr>
<tr>
<td class="label"><?php print _('Table Prefix:') ?></td>
<td>
Index: modules/core/classes/GalleryStorage.class
===================================================================
--- modules/core/classes/GalleryStorage.class (revision 16960)
+++ modules/core/classes/GalleryStorage.class (working copy)
@@ -132,6 +132,13 @@
var $_isTransactional;
/**
+ * Database schema to use (not used by all db types)
+ * @var string
+ * @access protected
+ */
+ var $_schema;
+
+ /**
* A string to prepend to table names
* @var string
* @access protected
@@ -186,6 +193,7 @@
$this->_usePersistentConnections = !isset($config['usePersistentConnections'])
|| !empty($config['usePersistentConnections']);
+ $this->_schema = isset($config['schema']) ? $config['schema'] : '';
$this->_tablePrefix = isset($config['tablePrefix']) ? $config['tablePrefix']
: DATABASE_TABLE_PREFIX;
$this->_columnPrefix = isset($config['columnPrefix']) ? $config['columnPrefix']
Index: modules/core/classes/GalleryStorage/PostgreSqlStorage.class
===================================================================
--- modules/core/classes/GalleryStorage/PostgreSqlStorage.class (revision 16960)
+++ modules/core/classes/GalleryStorage/PostgreSqlStorage.class (working copy)
@@ -111,7 +111,18 @@
}
return null;
- }
+ }
+
+ /**
+ * @see GalleryStorage::_setConnectionSettings
+ */
+ function _setConnectionSettings(&$db) {
+ if (!empty($this->_schema)) {
+ $this->_traceStart();
+ $db->Execute('set search_path to ' . $this->_schema);
+ $this->_traceStop();
+ }
+ }
/**
* @see GalleryStorage::convertIntToBits
The diffs are against current svn, but you might be able to manually apply the changes to G2.2.2. If you can put this code in place and test out a new install, I'd appreciate it! |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Tue, 2007-08-28 00:03
|
|
Better alternative to adding the patch above: checkout from svn now (or get tomorrow's nightly snapshot) and try installing with that. This code has been added, so it will go in G2.3.. testing the current codebase would be great. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Tue, 2007-08-28 00:48
|
|
I give that a try. It will have to wait until tomorrow though. I have a splitting headache and am headed for bed. |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Wed, 2007-08-29 00:30
|
|
I thought I would get to this tonight, but had minor surgery this afternoon and am just not really up to it. I'll try to give it a go tomorrow. On another subject, I installed gallery2 on Fedora Core 6 with selinux enabled. The Gallery2 selinux installation is not quite right. Are you interested in the policy I created for Gallery2? Since I have many of the plugins installed, I will probably run into more selinux AVCs that minimal installations. The policy I created has quite a few rules in it. I am part of the FC/selinux community so I will be forwarding the policy to them as well. |
|
|
valiant
Joined: 2003-01-04
Posts: 32509 |
Posted: Wed, 2007-08-29 01:10
|
|
Please send all FC related notes (like the selinux stuff) to the maintainer of the FC gallery2 packages. -------------- |
|
|
jrg345
Joined: 2007-08-18
Posts: 23 |
Posted: Fri, 2007-08-31 18:05
|
|
The installation from the SVN repository worked until it got to install plugins. I selected all of the plugins just to let it try everything. The plugin install stopped at the watermark plugin. I de-installed and tried it a second time to check. Stopped at the same place, watermark plugin, and that step stopped without any feedback as to what plugins installed.. Went back to the install plugins step during the second install attempt and it showed only a few plugins not installed. They were still selected. I proceeded and they installed and the feedback of successful installation of those plugins displayed and the script finished normally. Sorry this took me so long to get to. There are some selinux issues. Gallery2 and especially the watermark plugin wants to access some contexts like unlabeled_t, var_t, httpd_tmp_t that raise some flags in the selinux camp. Gallery version: 2.2. SVN |
|
|
mindless
Joined: 2004-01-04
Posts: 8601 |
Posted: Fri, 2007-08-31 20:31
|
|
Ok, I setup a schema and ran through the installer... everything installed ok. So I think the schema stuff is working.. so I guess the watermark issue is selinux related, as you suggest above. |
|
