Can I create an alias for a database ?

hi, i got a question,

We have a site that needs to be frozen every year for a snapshot (older
versions need to be available)

we do that by 'linking' the www directory to the year directory

can the same be done with mysql ?
for example if we have the databases
0506
0607
0708

can we alias 0708 to 'current' ?
so that "SELECT * from current.whatever" would be the same as "SELECT *
FROM 0708.whatever" ?

Thx in advance :-)

--
Arjen
http://www.hondenpage.com

== Quote from Floortje (lala@zingmaarmetmijmee.enel)'s article
> hi, i got a question,
> We have a site that needs to be frozen every year for a snapshot (older
> versions need to be available)
> we do that by 'linking' the www directory to the year directory
> can the same be done with mysql ?
> for example if we have the databases
> 0506
> 0607
> 0708
> can we alias 0708 to 'current' ?
> so that "SELECT * from current.whatever" would be the same as "SELECT *
> FROM 0708.whatever" ?
> Thx in advance :-)

I've never read that anywhere and am not sure if you can do this in MySQL.


--
POST BY: lark with PHP News Reader

On 11 Jun, 19:36, lark <ham...@sbcglobal.net> wrote:
> == Quote from Floortje (l...@zingmaarmetmijmee.enel)'s article
>
> > hi, i got a question,
> > We have a site that needs to be frozen every year for a snapshot (older
> > versions need to be available)
> > we do that by 'linking' the www directory to the year directory
> > can the same be done with mysql ?
> > for example if we have the databases
> > 0506
> > 0607
> > 0708
> > can we alias 0708 to 'current' ?
> > so that "SELECT * from current.whatever" would be the same as "SELECT *
> > FROM 0708.whatever" ?
> > Thx in advance :-)
>
> I've never read that anywhere and am not sure if you can do this in MySQL.
>
> --
> POST BY: lark with PHP News Reader

I've no idea either. Fancy that.

strawberry wrote:
>>> 0506
>>> 0607
>>> 0708
>>> can we alias 0708 to 'current' ?
>>> so that "SELECT * from current.whatever" would be the same as "SELECT *
>>> FROM 0708.whatever" ?
>>> Thx in advance :-)
>> I've never read that anywhere and am not sure if you can do this in MySQL.
>>
>> --
>> POST BY: lark with PHP News Reader
>
> I've no idea either. Fancy that.

Couldn't find it myself either ... a well it's open source :-)

--
Arjen

www.arjenkarel.nl

"Floortje" <lala@zingmaarmetmijmee.enel> schreef in bericht
news:466d8026$0$19837$dbd49001@news.wanadoo.nl...
> hi, i got a question,
>
> We have a site that needs to be frozen every year for a snapshot (older
> versions need to be available)
>
> we do that by 'linking' the www directory to the year directory
>
> can the same be done with mysql ?
> for example if we have the databases
> 0506
> 0607
> 0708
>
> can we alias 0708 to 'current' ?
> so that "SELECT * from current.whatever" would be the same as "SELECT *
> FROM 0708.whatever" ?
>
> Thx in advance :-)
>
> --
> Arjen
> http://www.hondenpage.com

i think you can do it with the 'ln' command:
# cd /var/lib
# ln -s 0708 current

after this
mysql> use 0708; show tables
and
mysql> use current; show tables
should look the same...

> can we alias 0708 to 'current' ?
> so that "SELECT * from current.whatever" would be the same as "SELECT *
> FROM 0708.whatever" ?

I think it depends on what you want with the database. If it is just
selects that you want, you can create views. (You can create a view that
takes its data from another database.) If you want inserts, updates and
such things, you might take a look at the FEDERATED storage engine
(http://dev.mysql.com/doc/refman/5.1/...ge-engine.html).
Not really meant for operations on the same physical server, but I do
not think anybody will stop you to connect to localhost. You need to
have a fairly recent MySQL server though.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Luuk wrote:
> i think you can do it with the 'ln' command:
> # cd /var/lib
> # ln -s 0708 current
>
> after this
> mysql> use 0708; show tables
> and
> mysql> use current; show tables
> should look the same...
>
Cool ... ive set up a testing server on a virtual machine and copied the
enitre site. Ill start testing this solution !!

Thx

--
Arjen
www.hondenpage.com

Willem Bogaerts wrote:
>> can we alias 0708 to 'current' ?
>> so that "SELECT * from current.whatever" would be the same as "SELECT *
>> FROM 0708.whatever" ?
>
> I think it depends on what you want with the database. If it is just
> selects that you want, you can create views. (You can create a view that
> takes its data from another database.) If you want inserts, updates and
> such things, you might take a look at the FEDERATED storage engine
> (http://dev.mysql.com/doc/refman/5.1/...ge-engine.html).
> Not really meant for operations on the same physical server, but I do
> not think anybody will stop you to connect to localhost. You need to
> have a fairly recent MySQL server though.
>
> Best regards,

It's not simple selects. For me it's not really a problem since the
database name is stored in the config class (it's a mostly php site) but
others have not been so consequent :-(

Im afraid the mysql server isn't the latest version. Ive been pushing
towards a newer version anyway .. maybe this will help.

Imm gonna test this solution anyway just as luuk's solution

Thx 4 ur input !

--
Arjen
www.arjenkarel.nl

>We have a site that needs to be frozen every year for a snapshot (older
>versions need to be available)
>
>we do that by 'linking' the www directory to the year directory
>
>can the same be done with mysql ?

I have used symlinks to database directories to relocate databases
(primarily because of disk space issues) with MyISAM tables. This
works OK.

>for example if we have the databases
>0506
>0607
>0708
>
>can we alias 0708 to 'current' ?
>so that "SELECT * from current.whatever" would be the same as "SELECT *
>FROM 0708.whatever" ?

A possible problem here is that MySQL doesn't know that `current.whatever`
and `0708.whatever` are the same table. This is an issue for deadlock
detection and locking. It is probably not a problem if tables in both
current and 0708 are used read-only.

INSERT INTO current.whatever (SELECT * FROM `0708.whatever`);

If these were references to the same table, MySQL would disallow
copying a table to itself. Since it doesn't realize it if you make
aliases with the symlink, it might see copied rows as new data to
copy, and (assuming the table has no unique indexes) keep copying
forever (the query would not finish until you run out of disk space,
or it's killed).