Talk:Database configuration

From refbase

Table names

My mistake. I just discovered those settings, but when I wrote my note I was sure I had spotted a SQL statement in the code during a grep which used a naked table name directly. I changed my version of db.inc.php as shown below. MaxEnt 17:18, 18 March 2007 (CET)

        $pfx = "rb_";
        $tableAuth = $pfx."auth"; // ->  'auth'
        $tableDepends = $pfx."depends"; // -> 'depends'
        $tableFormats = $pfx."formats"; // -> 'formats'
        $tableLanguages = $pfx."languages"; // -> 'languages'
        $tableQueries = $pfx."queries"; // -> 'queries'
        $tableRefs = $pfx."refs"; // -> 'refs'
        $tableStyles = $pfx."styles"; // -> 'styles'
        $tableTypes = $pfx."types"; // -> 'types'
        $tableUserData = $pfx."user_data"; // -> 'user_data'
        $tableUserFormats = $pfx."user_formats"; // -> 'user_formats'
        $tableUserOptions = $pfx."user_options"; // -> 'user_options'
        $tableUserPermissions = $pfx."user_permissions"; // -> 'user_permissions'
        $tableUserStyles = $pfx."user_styles"; // -> 'user_styles'
        $tableUserTypes = $pfx."user_types"; // -> 'user_types'
        $tableUsers = $pfx."users"; // -> 'users'
I just noticed your comment that install.sql and update.sql also contain raw table names. It would be a good idea to add a comment to db.inc.php that these files must also be updated if you edit below the dotted line. MaxEnt 17:26, 18 March 2007 (CET)
I agree that it would be helpful to add a note in db.inc.php about renaming table names in install.sql & update.sql. AFAIK, the raw SQL files cannot contain variable names. So, there are only two options to improve the current situation:
1) Process the install/update SQL files from within a PHP script during installation to add the user's table name prefix. However, this requires write permissions which almost always causes problems during installation.
2) Get rid of the dependence on install.sql & update.sql and execute all SQL commands via the install/update PHP scripts. We do this already for updates (update.php) and this has proven quite successful. So the same changes should be applied to install.php, and we should of course add an option to set a custom table name prefix.
Rather than edit the install.sql file (which might have been tricky) I just ran the install.sql unmodified (after ensuring there were no table conflicts) and then tacked on a SQL atomic, concurrent rename after the fact:
use refbase_db;
rename table 
  auth TO rb_auth,
  deleted TO rb_deleted,
  depends TO rb_depends,
  formats TO rb_formats,
  languages TO rb_languages,
  queries TO rb_queries,
  refs TO rb_refs,
  styles TO rb_styles,
  types TO rb_types,
  user_data TO rb_user_data,
  user_formats TO rb_user_formats,
  user_options TO rb_user_options,
  user_permissions TO rb_user_permissions,
  user_styles TO rb_user_styles,
  user_types TO rb_user_types,
  users TO rb_users;
I really appreciate your comments & edits in our wiki, however it would be helpful if you'd first use our forums or mailing lists in case you're not sure about how something works. We'll be very happy to answer your questions there! This will also help other users who may have the same problem since the "visibility" of our forums and mailing lists is much better than "talk" pages in the wiki. Of course, if you're clear about how something works and it's not sufficiently covered in the wiki (or not covered at all), you're very much welcome to add the information to the wiki. Thanks for your understanding. Matthias 17:52, 18 March 2007 (CET)
I keep all my own tech notes in a personal wiki, so I find I work most quickly in that capacity. I also maintain a public wiki, so I understand that talk pages are typically robot excluded. I was using the talk pages to marshall my thoughts before putting too many lies into the article space.
The one outright mistake I made I was on my way back to correct when I saw your edits. The mistake was somewhat subtle. I did a recursive grep for sql-like syntax and saw in the grep output sql statements with absolute table names, without realizing that this output did not come from a PHP script, but one of the *.sql files.
Some of the extra page links you have added in response to my edits strike me as helpful already. I've come to realize already that the documentation here is quite good, it's just that some of the exceptions to the standard scenario could be pointed out earlier in the sequence. My perspective is that forums are good for resolving problems, a good wiki is better for averting them in the first place. MaxEnt 23:08, 18 March 2007 (CET)

Table prefix bug

Done for today, I'll file a proper bug report tomorrow.

From includes/include.inc.php:

$query = "SELECT allow_add, allow_edit, allow_delete, allow_download, allow_upload, allow_details_view, allow_print_view, allow_browse_view, allow_sql_search, allow_user_groups, allow_user_queries, allow_rss_feeds, allow_import, allow_export, allow_cite, allow_batch_import, allow_batch_export, allow_modify_options FROM " . $pfx . $permissionType . "_permissions WHERE " . $permissionType . "_id = " . quote_smart($user_OR_groupID);

My addition of $pfx was required to work with my prefixed table names. The main database now comes up and displays records, so the bulk of it appears to be working. MaxEnt 23:50, 18 March 2007 (CET)

Thanks for the bug report! I've fixed this issue in the refbase development branch and I've replied in more detail to your post in the refbase Help forum. Matthias 14:57, 19 March 2007 (CET)