FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum » FUDforum Installation Issues » Postgres: Intstalling in non-public schema?
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Postgres: Intstalling in non-public schema? [message #35999] Tue, 27 February 2007 09:54 Go to next message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
I'm glad, that I finally found a Forum serving my needs - and it supports postgres Wink

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 10:31]

Report message to a moderator

Re: Postgres: Intstalling in non-public schema? [message #36005 is a reply to message #35999] Tue, 27 February 2007 20:05 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
The database prefix can be changed inside GLOBALS.php post install or you can adjust the prefix validator inside install.php to allow "." through.

FUDforum Core Developer
Re: Postgres: Intstalling in non-public schema? [message #36014 is a reply to message #35999] Wed, 28 February 2007 04:49 Go to previous messageGo to next message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
i thought of that too, but i faced some difficulties.
first of all i got a bunch of errors after changing the regExp inside the install.php. so i installed it into an empty database, dumped the structure and reimported it into the right destination.
but it seems that some querys are hard-coded inside the php-files (i assume they are dynamically generated during install..).
i', going to change them and try it again. if this fails, i think i'll take another look at the install.php to figure out what is necessary to get things working Wink
Re: Postgres: Intstalling in non-public schema? [message #36018 is a reply to message #35999] Wed, 28 February 2007 06:04 Go to previous messageGo to next message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 .. Wink )
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 Wink )
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 Wink ) 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 Wink )
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 07:52]

Report message to a moderator

Re: Postgres: Intstalling in non-public schema? [message #36032 is a reply to message #36018] Wed, 28 February 2007 19:05 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Glad to hear you got it working and thanks for providing details on how other people can repeat this accomplishment.

FUDforum Core Developer
Re: Postgres: Intstalling in non-public schema? [message #36040 is a reply to message #35999] Wed, 28 February 2007 19:17 Go to previous messageGo to next message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
but as i said: it does work only as long as no new tables are created.
i see you creating temorary (view?) tables when creating a new sub-forum. this will NOT work!
thats why i made this huge comment after the -- on my last post.
i'll see what i can do tomorrow to solve this.. for now its bed-time (1:15 ... Wink )
Re: Postgres: Intstalling in non-public schema? [message #36075 is a reply to message #35999] Fri, 02 March 2007 12:33 Go to previous messageGo to next message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 Wink
  • Attachment: install.php
    (Size: 47.32KB, Downloaded 428 times)

[Updated on: Fri, 02 March 2007 13:29]

Report message to a moderator

Re: Postgres: Intstalling in non-public schema? [message #36099 is a reply to message #36075] Sun, 04 March 2007 12:55 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Can you tell me what part of code (line would be ideal) you spotted DBHOST_SCHEMA I did a quick grep on standard fud sources and couldn't find it.

FUDforum Core Developer
Re: Postgres: Intstalling in non-public schema? [message #36104 is a reply to message #35999] Sun, 04 March 2007 17:54 Go to previous message
Drezil is currently offline  Drezil   Germany
Messages: 7
Registered: February 2007
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Wink
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 17:55]

Report message to a moderator

Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Two more issues...
Next Topic: Upgrade Errors
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Sep 20 22:27:07 EDT 2018

Total time taken to generate the page: 0.00691 seconds