Hack: change Database prefix to domain name

[nilsk]

Hi!


As I have seen that many people (including me) would like to change the prefix of database names in ISPConfig but no one posted how to change the scripts to do so, I decided to find out myself...

the following hack changes the db prefix from web[ID]_ to domainname_ (with dots and dashes replaced by underscore). I applied it to version 2.2.11.

MySQL accepts 64 chars for a database name and only 16 chars for a username. so the prefixes - especially those for the usernames - get truncated so that if you have domain myexample.info the prefix becomes myexam_info_uXXX. this way we are limited to 999 db users for a domain, which is acceptable for me. of course there might be prefix collisions, I have not tested those cases!

you have to change only one file: /home/admispconfig/ispconfig/lib/classes/ispconfig_isp_datenbank.lib.php

in the beginning, about at line 32, add three functions:

// HACK BY nils
// truncates domainname if domain_db999 would be longer than 64 chars
function truncate($string, $len){
        if (strlen($string)>$len){
                $dot = strrpos($string, ".");
                return substr($string, 0, $len-(strlen($string)-$dot) ).substr($string, $dot);
        }else{
                return $string;
        }
}
function prep_db_prefix($domain){
        return ereg_replace('[-.]', '_', truncate($domain, 58) );
}
// truncates domainname if domain_u999 would be longer than 16 chars
function prep_user_prefix($domain){
        return ereg_replace('[-.]', '_', truncate($domain, 11) );
}
// END HACK BY nils

around line 88 change so that it reads:

            foreach($db_ids as $db_id){
              // HACK BY nils
              //$db_nr[] = str_replace('web'.$web["doc_id"].'_db', '', $db_id["datenbankname"]);
              $db_nr[] = str_replace(prep_db_prefix($web["web_domain"]) .'_db', '', $db_id["datenbankname"]);
              // END HACK BY nils
            }

around line 98 it should read:

          // HACK BY nils
          //$doc->deck[0]->elements[0]->value = '<table width="100%"><tr><td class="normal" align="left" width="31%"><b>'.$go_api->lng("Datenbankname").':</b></td><td class="t2" align="left" width="69%">web'.$web["doc_id"].'_db'.$new_db_id.'</td></tr></table>';
          //$doc->deck[0]->elements[1]->value = '<table width="100%"><tr><td class="normal" align="left" width="31%"><b>'.$go_api->lng("Datenbankuser").':</b></td><td class="t2" align="left" width="69%">web'.$web["doc_id"].'_u'.$new_db_id.'</td></tr></table>';
          $doc->deck[0]->elements[0]->value = '<table width="100%"><tr><td class="normal" align="left" width="31%"><b>'.$go_api->lng("Datenbankname").':</b></td><td class="t2" align="left" width="69%">'.prep_db_prefix($web["web_domain"]).'_db'.$new_db_id.'</td></tr></table>';
          $doc->deck[0]->elements[1]->value = '<table width="100%"><tr><td class="normal" align="left" width="31%"><b>'.$go_api->lng("Datenbankuser").':</b></td><td class="t2" align="left" width="69%">'.prep_user_prefix($web["web_domain"]).'_u'.$new_db_id.'</td></tr></table>';
          // END HACK BY nils

around line 131 change to:

            foreach($db_ids as $db_id){
              // HACK BY nils
              //$db_nr[] = str_replace('web'.$web["doc_id"].'_db', '', $db_id["datenbankname"]);
              $db_nr[] = str_replace(prep_db_prefix($web["web_domain"]) .'_db', '', $db_id["datenbankname"]);
              // END HACK BY nils
            }

and finally around line 141 change to:

    // HACK BY nils
    //$datenbankname = 'web'.$web_doc_id.'_db'.$new_db_id;
    //$datenbankuser = 'web'.$web_doc_id.'_u'.$new_db_id;
    $datenbankname = prep_db_prefix($web["web_domain"]).'_db'.$new_db_id;
    $datenbankuser = prep_user_prefix($web["web_domain"]).'_u'.$new_db_id;
    // END HACK BY nils

It works for me so far but I have not tested it extensively. So use at your own risk! Hope it helps anyone...

Nils

EDITED: forgot to replace dashes in domain names as well...

EDITED: found another bug: mysql accepts only 16 chars for a username

[dimitar]

Hi,

do you think it will work, when "empty"(no prefix) is used...

Regards

[nilsk]

Originally Posted by dimitar

Hi,

do you think it will work, when "empty"(no prefix) is used...

Regards

I'm not sure what you mean by using an empty prefix because the hack automatically uses the domain name as prefix and the domain name is always set. There just is no option to use an empty prefix...

Nils

[dimitar]

i mean, just without any prefix... - NO PREFIX - I will try to move a complete PLESK system to ISPConfig and I need the databases whithout prefixes...

[nilsk]

you mean you want your very own database names (like they exist in PLESK) instead of <whatever_prefix>_db<xxx>? thats just not what my modification does nor what ISPConfig is coded to do. So you would need to make your own hack which unfortunately involves a little more than mine because you would need a new field for the database name in the new database form...

I hope i understood it correctly now..

[hamerr]

And what if I want the db-name to be just like the login ID to the system insted of web_ID or domain name

[nilsk]

Originally Posted by hamerr

And what if I want the db-name to be just like the login ID to the system insted of web_ID or domain name

which login ID? that of the admin user of the web? what if there is no admin user yet?

anyway, it would require quite a change to my mod because as far as I know there is no username available where I hook into the script. So, one would need to query for the admin user first...

[toolswizard]

For reasons beyond my control, I had to move a site to another server. Although I built a new server for this, my database is remote, and would be shared between the two web servers. This poses a problem for me since the ISPConfig database is seperate and would cause duplication of database names.

This solution will only work for a single database per site, and I need more. But it did show me where I can change the work "web" to somethng else to keep it unique. Either the actual host name or site.

Thanks for posting this solution, you saved me days of work.