Duplicating contents of DB within same DB with different prefix

[Istvan Hubay Cebrian]

Hi everyone,

I revert to this mailing list to ask for help accomplishing a specific task. I'll try to explain as best I can.

Say I have a DB named... well... "database1", this db has the following tables:

pt_table1
pt_table2
pt_table3

What I want to do is copy each of the tables (exactly as they are, including primary keys, data, etc) but at the same time change the prefix to something else (ie: eng_) so that I would end up with:

pt_table1
pt_table2
pt_table3
en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
en_table3 (exact copy of pt_table3, including data PK, FK, etc..)

Anyway of accomplishing this? Ideally without having to state each of the tables names. And also that this would work so that you could only duplicate tables that have a certain prefix, so: duplicate only tables that start with "pt_" and change prefix to "es_" for example.

Any help or point in the right direction will be much apreciated!

Thanks,
Istvan Cebrian

[Chris McKeever-2]

On 2/22/07, Istvan Hubay Cebrian <ihc.pub@...> wrote:
>
> Hi everyone,

>
> Say I have a DB named... well... "database1", this db has the following
> tables:

> What I want to do is copy each of the tables (exactly as they are, including
> primary keys, data, etc) but at the same time change the prefix to something
> else (ie: eng_) so that I would end up with:
>
>
> Anyway of accomplishing this? Ideally without having to state each of the
> tables names. And also that this would work so that you could only duplicate
> tables that have a certain prefix, so: duplicate only tables that start with
> "pt_" and change prefix to "es_" for example.
>

a suggestion - could you replicate to another server and not worry
about the prefix?  or is having a different prefix a requirement (as
opposed to a separate server or even database)



>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Martijn Tonies]

Hi,

> I revert to this mailing list to ask for help accomplishing a specific
task.

> I'll try to explain as best I can.
>
> Say I have a DB named... well... "database1", this db has the following
> tables:
>
> pt_table1
> pt_table2
> pt_table3
>
> What I want to do is copy each of the tables (exactly as they are,

including
> primary keys, data, etc) but at the same time change the prefix to
something

> else (ie: eng_) so that I would end up with:
>
> pt_table1
> pt_table2
> pt_table3
> en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
> en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
> en_table3 (exact copy of pt_table3, including data PK, FK, etc..)
>
> Anyway of accomplishing this? Ideally without having to state each of the
> tables names. And also that this would work so that you could only

duplicate
> tables that have a certain prefix, so: duplicate only tables that start
with
> "pt_" and change prefix to "es_" for example.
>
> Any help or point in the right direction will be much apreciated!

It seems you're trying to create something multi-lingual, would that
be correct?

If so, why not design proper metadata so you don't have to copy
tables AND modify your queries.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Istvan Hubay Cebrian]

Chris McKeever-2 wrote:

On 2/22/07, Istvan Hubay Cebrian <[e-mail]> wrote:
>
> Hi everyone,

>
> Say I have a DB named... well... "database1", this db has the following
> tables:

> What I want to do is copy each of the tables (exactly as they are, including
> primary keys, data, etc) but at the same time change the prefix to something
> else (ie: eng_) so that I would end up with:
>
>
> Anyway of accomplishing this? Ideally without having to state each of the
> tables names. And also that this would work so that you could only duplicate
> tables that have a certain prefix, so: duplicate only tables that start with
> "pt_" and change prefix to "es_" for example.
>

a suggestion - could you replicate to another server and not worry
about the prefix?  or is having a different prefix a requirement (as
opposed to a separate server or even database)

----

I could do as you sugested, in fact what I am currently doing is individual databases for each (in this case) language. But this method bothers me somewhat since I am now developing a website which has 6 languages which would mean 6 databases. I would rather simply have all the tables in the same db but each language with a different prefix.

The reason I would rather simply duplicate and change prefix is that I develop the site in full for one language then simply duplicate the database and change texts, which saves me allot of work since, templates, style sheets, etc are all stored in the db.


>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=[e-mail]

[Istvan Hubay Cebrian]

Martijn Tonies wrote:

Hi,

> I revert to this mailing list to ask for help accomplishing a specific
task.
> I'll try to explain as best I can.
>
> Say I have a DB named... well... "database1", this db has the following
> tables:
>
> pt_table1
> pt_table2
> pt_table3
>
> What I want to do is copy each of the tables (exactly as they are,
including
> primary keys, data, etc) but at the same time change the prefix to
something
> else (ie: eng_) so that I would end up with:
>
> pt_table1
> pt_table2
> pt_table3
> en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
> en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
> en_table3 (exact copy of pt_table3, including data PK, FK, etc..)
>
> Anyway of accomplishing this? Ideally without having to state each of the
> tables names. And also that this would work so that you could only
duplicate
> tables that have a certain prefix, so: duplicate only tables that start
with
> "pt_" and change prefix to "es_" for example.
>
> Any help or point in the right direction will be much apreciated!

It seems you're trying to create something multi-lingual, would that
be correct?

If so, why not design proper metadata so you don't have to copy
tables AND modify your queries.

---

Correct this is for a multilingual website using an extremely simplified "CMS".

The reason I am not designing proper metadata is due to the fact I am using a CMS not programmed by myself, therefore I would rather not have to alter its inner core, also, i gathered this would probably be the simplest way of accomplishing what I want without having to use a db for each language (which is what I am doing right now).

[Martijn Tonies]

> >> Anyway of accomplishing this? Ideally without having to state each of
the

> >> tables names. And also that this would work so that you could only
> > duplicate
> >> tables that have a certain prefix, so: duplicate only tables that start
> > with
> >> "pt_" and change prefix to "es_" for example.
> >>
> >> Any help or point in the right direction will be much apreciated!
> >
> > It seems you're trying to create something multi-lingual, would that
> > be correct?
> >
> > If so, why not design proper metadata so you don't have to copy
> > tables AND modify your queries.
> >
> ---
>
> Correct this is for a multilingual website using an extremely simplified
> "CMS".
>
> The reason I am not designing proper metadata is due to the fact I am

using
> a CMS not programmed by myself, therefore I would rather not have to alter
> its inner core, also, i gathered this would probably be the simplest way
of
> accomplishing what I want without having to use a db for each language
> (which is what I am doing right now).

Well, except for having to change the metadata for each additional language.

Sounds very easy ;)

Wouldn't it be a nice start to use the script that creates these tables and
do
a search/replace on them? If you need this multiple times (for more
languages)
in the future, why not create a script that has absolutely no errors in
replacing, eg: %LANG% that will be replaced by es_ or whatever, to avoid
errors.




Martijn Tonies
(who thinks multi-language with changing metadata is not the best way to go)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Istvan Hubay Cebrian]

Martijn Tonies wrote:

> >> Anyway of accomplishing this? Ideally without having to state each of
the
> >> tables names. And also that this would work so that you could only
> > duplicate
> >> tables that have a certain prefix, so: duplicate only tables that start
> > with
> >> "pt_" and change prefix to "es_" for example.
> >>
> >> Any help or point in the right direction will be much apreciated!
> >
> > It seems you're trying to create something multi-lingual, would that
> > be correct?
> >
> > If so, why not design proper metadata so you don't have to copy
> > tables AND modify your queries.
> >
> ---
>
> Correct this is for a multilingual website using an extremely simplified
> "CMS".
>
> The reason I am not designing proper metadata is due to the fact I am
using
> a CMS not programmed by myself, therefore I would rather not have to alter
> its inner core, also, i gathered this would probably be the simplest way
of
> accomplishing what I want without having to use a db for each language
> (which is what I am doing right now).

Well, except for having to change the metadata for each additional language.

Sounds very easy ;)

Wouldn't it be a nice start to use the script that creates these tables and
do
a search/replace on them? If you need this multiple times (for more
languages)
in the future, why not create a script that has absolutely no errors in
replacing, eg: %LANG% that will be replaced by es_ or whatever, to avoid
errors.

The script I use to create the tables and insert the data already allows for the use of a prefix. Meaning that I could create the exact same tables inside the same database with different prefixes. This, however, is not ideal since when I develop a website, I initially develop it fully in one language, then I simply duplicate the database and change the text, this method saves me allot of time since all texts, stylesheets, templates, etc, etc are copied.

If I where to use the script again and create the initial tables with a new prefix, I would have to go through the whole process of developing the site (obviously easier than initial devl. but still time consuming). Therefore I figured the easiest way would be to simply duplicate the tables and change the prefixes.

I am now thinking that probably the best way to accomplish this is through a PHP script. In any case i'd like to thank you for your prompt replies, this little brainstorming has definitely given me some ideas.

Thanks,
Istvan H Cebrian

[Michael Dykman-2]

This should do what you are asking for (not that I approve of your
linguistic appraoch ;-))


CREATE TABLE PREFIX_pt_table1 LIKE pt_table1;
INSERT INTO PREFIX_pt_table1 SELECT * from pt_table1;


On 2/22/07, Istvan Hubay Cebrian <ihc.pub@...> wrote:

>
> Hi everyone,
>
> I revert to this mailing list to ask for help accomplishing a specific task.
> I'll try to explain as best I can.
>
> Say I have a DB named... well... "database1", this db has the following
> tables:
>
> pt_table1
> pt_table2
> pt_table3
>
> What I want to do is copy each of the tables (exactly as they are, including
> primary keys, data, etc) but at the same time change the prefix to something
> else (ie: eng_) so that I would end up with:
>
> pt_table1
> pt_table2
> pt_table3
> en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
> en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
> en_table3 (exact copy of pt_table3, including data PK, FK, etc..)
>
> Anyway of accomplishing this? Ideally without having to state each of the
> tables names. And also that this would work so that you could only duplicate
> tables that have a certain prefix, so: duplicate only tables that start with
> "pt_" and change prefix to "es_" for example.
>
> Any help or point in the right direction will be much apreciated!
>
> Thanks,
> Istvan Cebrian
>
> --
> View this message in context: http://www.nabble.com/Duplicating-contents-of-DB-within-same-DB-with-different-prefix-tf3272642.html#a9099573
> Sent from the MySQL - General mailing list archive at Nabble.com.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdykman@...
>
>

--
 - michael dykman
 - mdykman@...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...

[Istvan Hubay Cebrian]

Istvan Hubay Cebrian wrote:

Hi everyone,

I revert to this mailing list to ask for help accomplishing a specific task. I'll try to explain as best I can.

Say I have a DB named... well... "database1", this db has the following tables:

pt_table1
pt_table2
pt_table3

What I want to do is copy each of the tables (exactly as they are, including primary keys, data, etc) but at the same time change the prefix to something else (ie: eng_) so that I would end up with:

pt_table1
pt_table2
pt_table3
en_table1 (exact copy of pt_table1, including data PK, FK, etc..)
en_table2 (exact copy of pt_table2, including data PK, FK, etc..)
en_table3 (exact copy of pt_table3, including data PK, FK, etc..)

Anyway of accomplishing this? Ideally without having to state each of the tables names. And also that this would work so that you could only duplicate tables that have a certain prefix, so: duplicate only tables that start with "pt_" and change prefix to "es_" for example.

Any help or point in the right direction will be much apreciated!

Thanks,
Istvan Cebrian

Well, i've managed. Ended up creating a little PHP script that takes care of it. Don't know why I didnt think of this from the beginning. Anyway, thanks Martijn & Chris.