| Postgres: Intstalling in non-public schema? [message #35999] |
Tue, 27 February 2007 14:54  |
Drezil
Messages: 7
Registered: February 2007
Karma: 0
|
Junior Member |
|
|
I'm glad, that I finally found a Forum serving my needs - and it supports postgres 
I have a simple Question:
I got a project running which uses postgres. To that project i got a main database with the default schema containing user-relevant stuff and some other schemas each with another instance of nearly the same db-structure.
Now i want to install the forum into a blank created schema (which should(!) be no problem).
In fact (as far as i can see) the fudforum sticks to the default ("public") schema.
Theoretically this is no problem.
for those not familar with the postgres database - schema - table -model here is a short introduction:
postgres features so called "shemas" which are simply said seperated databases within one datebase with 1 single difference:
you can reference between these instances.
For example you plan different instances of the same kind (different worlds in games and so on). Then you can go and say: Well, one user can play in more than one world.
In sql this in rather simple broken down:
select w1.name, w2.name from world1.user w1, world2.user w2, account.user a where w1.uid = a.uid and w2.uid = a.uid;
which gives you the name of the same user in different worlds.
(just an example. I know that this is no "good" sql.)
i think that fudforum has also somhow implemented user-ids. The nice part is: I want to write some kind of "access" to that schema so i can use the forum-data in my application.
in the best case this ends in something like:
select t.topic from forum.threads t join account.user u on (t.uid=u.forumuid) where u.uid=...
so i can easily acces the data an create more interaction between the users (we all know the barrier to register in an application-forum - in my experience not half of my users even registered in my old forum)
long story short:
is it possible to install fudforum into postgres schema-system?
if not: can this be treated as a feature-request and moved into the appropriate forum?
the realisation and the access of the db-layer needs only a few changes. For instance if you could allow "board." as an table-prefix everything should work fine (in sql this would just change select * from tbl_table to select * from board.table).
for now i will try to get it working in an isolated datebase and then dump, edit and reimport the structure to the schema i want it to be. if necessary i'll update some of the code (which should be only minimal changes)
edit:
a minor note: fudforum is not compatible with the "php5-strict-standards". it'll generate notices on E_ALL | E_STRICT (in php6 E_STRICT should become standard in E_ALL - until now you have to explicit say it in the php.ini)
it's not a great deal but you'll get something like
PHP-ERROR:
Strict Standards: Creating default object from empty value in /var/www/forum/theme/default/index.php on line 102
PHP-ERROR:
Strict Standards: Creating default object from empty value in /var/www/forum/theme/default/index.php on line 264
on a fresh installation.
[Updated on: Tue, 27 February 2007 15:31] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Postgres: Intstalling in non-public schema? [message #36018 is a reply to message #35999] |
Wed, 28 February 2007 11:04  |
Drezil
Messages: 7
Registered: February 2007
Karma: 0
|
Junior Member |
|
|
yup.. worked fine - as far as i can see..
what i did in detail (for those who plan the same thing):
1. install ff in an empty database with your designated prefix (i.e. fud26_)(can't be omitted due to internal stuff i think)
2. dump the structure with all data and edit it "manually" and replace "fud26_" with "forum.fud26_" (if you ward the forum schema)
keep in mind not to replace the indexes/constraints ("index fud26_bla" must be kept that way. dont replace it to "index forum.fud26_bla")
3. restore the changed db-structure into your schema.
4. edit the ff-files.
you have to replace all occourences of "fud26_" with "forum.fud26_" in /var/www/forum (or whereever your forum-root may be) and in /var/FUDforum/... (by default). (i did it manually by downloading the whole bunch ind changing it with a tool in windows .. )
this litte line should work too (being in one of the 2 given dirs):
find -type f -exec sed -e 's/fud26_/forum.fud26_/' -i {} \;
(not tested - be warned )
5. adjust the GLOBALS.php to your needs (correct database, "forum.fud26_" as table-prefix) and enjoy, you're done.
all in all its a quite dirty solution.. the next time (if update failes for whetever reason) i'll change the installer so that it works. (i think it has something to do with the database-structure.. it seemed that all table-information (including indecies!) got the given table-prefix.. and because index schema.table doesn't work this could be the point why just adjusting the 2 regexes in the installer didn't work.)
edit: seems like there are some more difficulties, because ff creates tables dynamically (which is not a problem) with indices (wich is more a problem ) leading to invalid sql (create index foo.bar is not allowed..).
--
as far as i can see (at this point) you made a bunch of sql-templates. I sum up, what i've understood of your forum:
1. sql is in sql-templates.
2. at runtime all expressions {EXPRESSION} are replaced by str_replace and the resulting query is executed.
I don't know how consistent jou followed this approach. If this is the case every time than this should help:
replace "{SQL_TABLE_PREFIX}" with "{SQL_TABLE_SCHEMA}{SQL_TABLE_PREFIX}" in all files.
the elegant way is then to replace all replacements with
str_replace('{SQL_TABLE_SCHEMA}tv_1', '.$GLOBALS['DBHOST_TBL_SCHEMA'].', $v)
str_replace('{SQL_TABLE_PREFIX}tv_1', $view_tbl, $v)
and add such a global var so that there is an extra option for the schema etc.
the quick&dirty nasty way is:
add the global as in the example before and change also the replacements to:
str_replace('{SQL_TABLE_SCHEMA}{SQL_TABLE_PREFIX}tv_1', $view_tbl, $v)
change all indices in the sql-templates so the create proper sql. then you get stuck with some {SQL_TABLE_PREFIX} not be replaced (the ones you just edited) so just add for this few cases an extra-replacement.
note: the quick& dirty-solution is really quick & dirty ..
can you give (maybe link or sth.) me a brief intro to the concept of design of the fudforum, so i can edit it for myself and send you a "corrected" version?
if you want to discuss this with me in detail (for example if jou want to include this feature for later versions or tell me that this is just way too complicated and i should go for myself) then feel free to contact me
irc: #mission-unknown @ irc.gamesurge.net (talk to Drezil, the channel-owner )
icq: pm me
jabber: pm me
mail: pm me
note: i live in germany (+6h to default-forum-time)
[Updated on: Thu, 01 March 2007 12:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Postgres: Intstalling in non-public schema? [message #36075 is a reply to message #35999] |
Fri, 02 March 2007 17:33 |
Drezil
Messages: 7
Registered: February 2007
Karma: 0
|
Junior Member |
|
|
time went on .. and i hacked a bit for myself.
i changed the installer.
now you are explicitly asked to which schema the forum should be installed. if you leave it blank the default (public) schema is used - so its keeps being compatible with all other dbs (sqlite, mysql).
i also changed all sql-templates according to that. mostly they look like:
CREATE TABLE {SQL_SCHEMA}{SQL_TABLE_PREFIX} *blah*
according to that some php-files needed to be changed and a new global var $DBHOST_SCHEMA was introduced (and written by the installer script).
all works fine. even table creation and stuff so i think its nearly bug-free and could be added to the cvs.
the changes are huge. the modified installer is appended. the other stuff can be downloaded here.
installation instruction (until its included in the default installer):
1. download files as normally
2. open install.php in your browser and set file-permissions accordingly (if asked)
3. enter data on the first page & press next.
4. STOP before going to step 2!
5. extract the hacked files and overwrite them in your non-web-dir (default: /var/FUDforum/)
6. overwrite the install.php with the appended version.
7. refresh your browser. a new field "schema" should appear
8. continue installation as normal.
9. you're done.
Note: i got a minor problem. maybe its somewhere in the archive .. i can't figure it out ..
i got a typo. open your index.php and search for $DBHOST['DBHOST_SCHEMA'] and change it to $GLOBALS['DBHOST_SCHEMA']
maybe this is fixed, maybe not. i don't know due to the dynamic generation of the index.php.
NOTE (IMPORTANT!):
due to the circumstance that the admin-interface is coded in another way you havt to replace all occourences of
"$DBHOST_TBL_PREFIX" with "$DBHOST_SCHEMA.$DBHOST_TBL_PREFIX"
and
"$GLOBALS['DBHOST_TBL_PREFIX']" with "$GLOBALS['DBHOST_SCHEMA'].$GLOBALS['DBHOST_TBL_PREFIX']".
DISCLAIMER: Like always: NO WARRENTY
-
Attachment: install.php
(Size: 47.32KB, Downloaded 1093 times)
[Updated on: Fri, 02 March 2007 18:29] Report message to a moderator |
|
|
|
|
|
| Re: Postgres: Intstalling in non-public schema? [message #36104 is a reply to message #35999] |
Sun, 04 March 2007 22:54 |
Drezil
Messages: 7
Registered: February 2007
Karma: 0
|
Junior Member |
|
|
thats exactly the part i added. diff the installer i appended und the other files in the archive to figure out what i changed explicitly.
its not much of a change, but it had to be made in nearly every single file. contact me by pm here so we can arrange a chat-session (and then i explain what i've done in detail).
ps: that "bugfix" i've given in my last post was for "my version" of the forum
[Updated on: Sun, 04 March 2007 22:55] Report message to a moderator |
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]