Posted by boombatower on April 30, 2009 at 2:08am
5 followers
Jump to:
| Project: | Drupal core |
| Version: | 8.x-dev |
| Component: | database system |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Issue Summary
It would be very useful to have a table duplication/copy method available.
A prime usecase is #323477: Increase simpletest speed by running on a simplified profile.
The key thing to watch out for is the users table which has a uid = 0 which causes problems with auto incrementing.
Comments
#1
Here is an excerpt from how I accomplished this in the other patch. (This is not intended to be finished code, but at the time I had no better way to do it)
<?php
function simpletest_environment_copy_data($base, $prefix) {
$destination_tables = db_find_tables($prefix . '%');
foreach ($destination_tables as $destination_table) {
$table = str_replace($prefix, '', $destination_table);
$source_table = $base . $table;
db_query('TRUNCATE TABLE ' . $destination_table);
if ($table == 'users') {
// Due to uid 0 the data connot be copied like the rest of tables.
db_query('INSERT INTO ' . $destination_table . ' SELECT * FROM ' . $source_table . ' WHERE uid = 0');
db_query('UPDATE ' . $destination_table . ' SET uid = uid - 1');
db_query('INSERT INTO ' . $destination_table . ' SELECT * FROM ' . $source_table . ' WHERE uid > 0');
continue;
}
db_query('INSERT INTO ' . $destination_table . ' SELECT * FROM ' . $source_table);
}
}
?>
This function assumes the tables schema has been copied. We probably should have either two methods or parameterize it to allow for the creation of just the schema and or data as well. In most cases we should just be able to create schema using schema API...so maybe just data?
#2
Copying a whole table might be useful (it certainly is), but I think we should rather implement "whole schema" (in the Schema API sense) copying. This is the only way we could significantly speed-up tests on SQLite (by simply copying the whole database file).
#3
Table-level cloning is also useful if we ever get around to implementing optimized databases for selected tables, such as system or the registry tables. For that, we'd need a clean "clone this table from this connection to this connection" operation.
#4
For a number of cases in SimpleTest I need a clone table that copies data (preferably not schema as well), but I should write it differently. The main two things I am trying to accomplish are:
1) Tuning simpletest by creating skeleton databases and re-using them. (schema only initially)
2) Copying from a base database, could be a plain drupal install, or backed up version of live site for configuration testing (data only).
Currently I do all schema creation using the built in schema handling code (and seems sufficient), only time you may want raw copy of schema is if you do not know what the schema of the table is, which is not the problem in my case.
So if we could get a consistent copy data function that would be great.
In general, copy database functionality would work, but would be less tuned (depending on implementation) then only copying data. If we do things like copy database files I have no idea, but if done in queries then I would assume data only would be faster.
As a note: the code I have works, but as suggested (and I thought about) it should be in db layer.
#5
More robust connection handling will have to wait, sadly.
#6
subscribe
#7
Database duplication on Postgres (in a controlled environment) can be optimised using template DBs.
See http://www.postgresql.org/docs/8.3/interactive/manage-ag-templatedbs.html
Every DB in postgres is actually based on a template, but the default template is empty.