If you have a already installed PostGreSQL based Drupal site without prefixes, export the database to a SQL dump with INSERTs and execute prefix.sh on it, not all table names are prefixed correctly.
First of all this is due to the fact that PostGreSQL exports some specific table names with quotes since the words are reserved, f.e. "roles". prefix.sh currently converts them to myprefix_"roles".
Also, PostGreSQL dumps use the SQL statement "ALTER TABLE ONLY <db_table>" instead of "ALTER TABLE <db_table>" to build constraints which currently leads to "ALTER TABLE myprefix_ONLY <db_table>". These statements actually only apply to PostGreSQL dumps, so it should be OK to add "ONLY" to prefix.sh in CVS.
Furthermore, PostGreSQL dumps include SQL statements to build database sequences. These do not affect other databases, but should be converted by prefix.sh, too.
I'm attaching a patch to prefix.sh which solves all stated problems here. For the first topic of my list it made sense to me to change the invokation of prefix.sh from
prefix.sh prefix dumpfile > outfile
into
prefix.sh prefix dumpfile outfile
This is because the wrong table names (i.e. like myprefix_"roles") can only be searched & replaced when the prefix replacement already took place.
| Comment | File | Size | Author |
|---|---|---|---|
| #8 | prefix.sh_.patch | 1.65 KB | sun |
| #6 | prefix.sh_1.patch | 1.62 KB | smk-ka |
| #2 | prefix.sh_0.patch | 1.72 KB | sun |
| prefix-pgsql-export.patch | 1.66 KB | sun |
Comments
Comment #1
Cvbge commentedHi,
prefix.sh is intended to work with schema files provided by drupal, not with sql dumps. It should also work with both mysql and postgres database schemes...
I think it is impossibile to do it easily/cleanly, probably it'd be better to write separate script just for that [or just use schemas...]
Comment #2
sunAdded deletion of COMMENT, REVOKE and GRANT statements, which are included in PostGreSQL dumps. Import of dumps with these statements may fail since the actual importing database user does not have the required permissions for altering the database description or permissions. New patch attached.
@Cvbge: My extensions to
prefix.shdo not affect the functionality to work with schema files provided by drupal. They do not contain any of the added statements. In addition, MySQL dumps do not contain these statements. So it should be safe to patchprefix.shfor HEAD.Comment #3
sammys commentedprefix.sh has been removed from the repository. I'm not sure if it means the script won't exist in 4.8+. I've modified the version to reflect this change. We'll patch 4.7 once this is tested. I'll review it as soon as I can.
--
Sammy Spets
Synerger
http://www.synerger.com
Comment #4
sammys commentedThis patch no longer applies to 4.7 branch. Please update.
Comment #5
magico commentedI don't think this will enter the 4.7.x-dev branch...
Anyway reroll a patch for the most recent file version
Comment #6
smk-ka commentedUpdated patch against 4.7.x-dev. Please test.
--
Stefan Kudwien
www.unleashedmind.com
Comment #7
Jaza commentedTake out the syntax change (i.e. changing
file1 > file2tofile1 file2), and this is a bug fix, not a feature request. This should still be able to get into the 4.7 branch.Comment #8
sunIdentical patch without third argument attached.
Comment #9
sunprefix.sh no more exists. However, this modified version is still very useful for anyone in the need to convert table prefixes of an exported database in front of an import. Where could it be stored / committed?
Comment #10
gábor hojtsyContribs/tricks could possibly be a place to host this. I am won't fixing this, because it should not take our attention in the D6 RTBC queue.