Hack: change Database prefix to domain name

Discussion in 'Tips/Tricks/Mods' started by nilsk, Mar 22, 2007.

  1. nilsk

    nilsk New Member

    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:

    Code:
    // 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:

    Code:
                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:

    Code:
              // 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:

    Code:
                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:

    Code:
        // 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
     
    Last edited: Mar 22, 2007
  2. dimitar

    dimitar New Member

    Hi,

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

    Regards
     
  3. nilsk

    nilsk New Member

    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
     
  4. dimitar

    dimitar New Member

    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...
     
  5. nilsk

    nilsk New Member

    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..
     
  6. hamerr

    hamerr New Member

    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
     
  7. nilsk

    nilsk New Member

    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...
     
  8. toolswizard

    toolswizard Member

    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.
     

Share This Page