By mSale on

I want to add profix for all tables,e.g:dr_
How do I do?
Thanks.

Comments

micha_1977’s picture

micha_1977 commented

you mean the sql tables ?

before you create the drupal sql tables, open /database/database.mysql and add the prefix to all tables

-micha
work in progress with Drupal 4.6: langmi.de

arden@thank-you.ca’s picture

arden@thank-you.ca commented

You can grab your editor search and replace. Better though I think to instantiate everything you like at the first site as a base template. Consider saving yourself some time that way if your doing any amount of replication.

starkos’s picture

starkos commented

Open database.mysql in a text editor, then:

Replace: "CREATE TABLE " (don't forget the space!)
With: "CREATE TABLE prefix_"

Replace: "INSERT INTO "
With: "INSERT INTO prefix_"

Replace: "REPLACE "
With: REPLACE prefix_"

Then edit settings.php and set $db_prefix='prefix_'

skor’s picture

skor commented

Do all drupal modules support the use of $db_prefix?

mauriziopinotti’s picture

mauriziopinotti commented

as long as u include table names in {curly brackets}

---------------
pinottinet.com |||the open source portal|||

---------------

Mobimentum ||| let your apps run, be in Mobimentum |||

mauriziopinotti’s picture

mauriziopinotti commented

here is the PHP code snippet to change the db prefix:

<form action="" method="get">
 Old prefix: <input type="text" name="old" /> <br />
 New prefix: <input type="text" name="new" /> <br />
 <input type="submit" value="Rename" />
</form>
<?php
 
 global $db_prefix;

 if ($old = $_REQUEST['old'] || $new = $_REQUEST['new']) {
  $result = db_query("SHOW TABLES");
  while ($r = db_fetch_array($result)) {
   $table_old = current($r);
   $table_new = $new.str_replace('^'.$old, '', $table_old);
   db_query("RENAME TABLE {$table_old} TO {$table_new}");
   print "{$table_old} &mdash;&gt; {$table_new} <br />";
  }

  $db_prefix = $new;
 }

?>

all you need to do is:
1. copy and paste the code in a PHP page within Drupal
2. save and execute the page
3. change the settings.php according to the new database prefix

my 2 cents

---------------
pinottinet.com |||the open source portal|||

---------------

Mobimentum ||| let your apps run, be in Mobimentum |||

jonathan_hunt’s picture

jonathan_hunt commented

After renaming the tables, you need to visit "$new . sequences" table and "UPDATE $new sequences SET name = CONCAT($new, name)"; nb. this is rough pseudo code as it doesn't take removing the old prefix (if any) from the sequence names.

reed.richards’s picture

reed.richards commented

Had some problems with my own modules when using this script, which could be worth noting. I had written something like

SELECT {n.title} t, f.* FROM node AS n INNER JOIN {content_type_foretag} f ON f.nid = n.nid WHERE n.status = 1 ORDER BY n.title

With this you will get problems in Drupal though the code is correct SQL. In the SELECT part you should always write

SELECT n.title AS t

and in FROM part you should always write

FROM {node} n

This solves the no table or column problem.

FunkMonkey’s picture

FunkMonkey commented

Sawheeeeeet! Thanks for that solution. Saved me a bunch of time.

jonathan_hunt's comment is correct for Drupal 5 and earlier I believe. Drupal 6 does not have a sequences table so I don't think you need to worry about it.

shopgirl_11’s picture

shopgirl_11 commented

Hi there,

I just wanted to know if the instructions above also apply on drupal 6.x? If so, where should i locate database.mysql? which PHP file i need to change on drupal and add the script? Sorry, im a complete newbie on drupal and not quite sure how to get db_prefix working.

Thanks.

dooug’s picture

dooug commented

I used a PHP snippet based off the advice above in Drupal 6.x. I executed the code with the 'Execute PHP Code' functionality of the Devel module. And then changed my settings.php file to use the new prefix. Worked perfectly.

note, take out the <?php ?> tags to use the code in the Devel module. And change 'old_prefix' and 'new_prefix' as needed.


global $db_prefix;
print $db_prefix;


if ($old = 'old_prefix' || $new = 'new_prefix') { 
  $result = db_query("SHOW TABLES");  
  while ($r = db_fetch_array($result)) {
    $table_old = current($r);
    $table_new = $new.str_replace('^'.$old, '', $table_old);
    db_query("RENAME TABLE {$table_old} TO {$table_new}");
    print "{$table_old} -> {$table_new}";
  }
  $db_prefix = $new; 
}
bloke_zero’s picture

bloke_zero commented

that worked perfectly for me

http://modelagnostic.co.uk/

nicholas.alipaz’s picture

nicholas.alipaz commented

If your tables had drup_ as the prefix and you wanted to remove it from all of them then this should do the trick from the command line as the root mysql user.

SELECT GROUP_CONCAT(CONCAT("RENAME TABLE ", table_name, " TO ", SUBSTRING(table_name, 6)) SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "YOURTABLE" AND table_name LIKE "drup_%";

This will result in a one line string that is formated like
RENAME TABLE drup_access TO access;RENAME TABLE drup_accesslog TO accesslog;RENAME TABLE drup_actions TO actions;...etc...RENAME TABLE drup_watchdog TO watchdog;

if your resulting query is truncated and you don't see all the tables then you may need to run this first: set group_concat_max_len=100000;

Then just copy the string, change to your table and paste your sql statement to change all tables.

sethfisher’s picture

sethfisher commented

table_schema = "YOURDATABASE" not table.

Thanks, worked great for me in D7.

5t4rdu5t’s picture

5t4rdu5t commented

I just needed to add a "drupal_" prefix to all tables in drupal 6, so issued:

SET group_concat_max_len=100000;

SELECT GROUP_CONCAT(CONCAT("RENAME TABLE ", table_name, " TO drupal_", table_name) SEPARATOR ";") 
FROM information_schema.tables 
WHERE table_schema = "MYDATABASENAME";

Copied, pasted and executed the result. Then edited settings.php 

$db_prefix = 'drupal_';

And worked like charm.

Thanks guys!

enjaygee’s picture

enjaygee commented

The following worked for me in postgresql to remove a "drupal_" prefix from all tables. Can be quickly modified to change the prefix instead by using concat().

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename like 'drupal_%'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(row.tablename) || ' RENAME TO ' || quote_ident(right(row.tablename,length(row.tablename)-7))  || ';';
    END LOOP;
END;
$$;

also needed to update the sequences:

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT relname FROM pg_class WHERE relkind = 'S' AND relname like 'drupal_%'
    LOOP
        EXECUTE 'ALTER SEQUENCE ' || quote_ident(row.relname) || ' RENAME TO ' || quote_ident(right(row.relname,length(row.relname)-7))  || ';';
    END LOOP;
END;
$$;
goose2000’s picture

goose2000 commented

Thank you guys, this D6 snipet net worked great for me.