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. |
|
Posts: 8601
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).
Posts: 23
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.
Posts: 8601
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..
Posts: 23
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 />
(postgres7): CREATE TABLE gallery2.g2_AccessMap(
g_accessListId INTEGER NOT NULL,
g_userOrGroupId INTEGER NOT NULL,
g_permission BIT(32) NOT NULL
)
<hr />
2007-08-23 13:34:20 [TMP_SESSION_ID_DI_NOISSES_PMT] <hr />
(postgres7): ALTER TABLE gallery2.g2_AccessMap ADD PRIMARY KEY (g_accessListId,
g_userOrGroupId)
<hr />
2007-08-23 13:34:20 [TMP_SESSION_ID_DI_NOISSES_PMT] postgres7 error: [-1: ERROR:
syntax error at or near "." at character 22] in EXECUTE("CREATE INDEX gallery2
.g2_AccessMap_83732 ON gallery2.g2_AccessMap(g_accessListId)")
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:
Just thought I'd pass this along.
Posts: 8601
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.
Posts: 23
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.
At the top of the "Install Plugins" page results I get:
Then I get:
and in the debug output the first error is:
After that, all additional commands are aborted because the transaction is aborted.
Posts: 8601
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.
Posts: 23
After the connection is made, you can set the search_path to only include the schema desired, ie.
set search_path TO gallery2;. But that is not sql code; it is a command in psql, so I am not sure it would work in a statement sent through php.I saw that. I even edited it to change
schemaname not in ( 'pg_catalog','information_schema')toschemaname = 'gallery2'I hard coded it to test it, but that still did not work. I'm not familiar enough with the gallery2 internals to know what it is looking for.Posts: 8601
I suggest trying this next:
Try installing with just 'g2_' as table prefix. Before installing add this code in modules/core/classes/GalleryStorage/PostgreSqlStorage.class:
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.
Posts: 23
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.
Posts: 8601
great!
(unit tests are included only in the developer package)
Posts: 23
I surmised that. I downloaded the full package. I'm getting the developer now.
Posts: 8601
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::convertIntToBitsThe 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!
Posts: 8601
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.
Posts: 23
I give that a try. It will have to wait until tomorrow though. I have a splitting headache and am headed for bed.
Posts: 23
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.
Posts: 32509
Please send all FC related notes (like the selinux stuff) to the maintainer of the FC gallery2 packages.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 23
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
PHP version: php-5.1.6
Webserver: Apache 2.2.4
Database: PostgreSQL 8.1.9
Activated toolkits: NetPbm, ImageMagick, Dcraw, ffmpeg, GD
Operating system: Fedora Core 6
Browser: Firefox 2.0.0.6
Posts: 8601
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.