• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

A quick SQL problem

Diamond

Illuminator
D
Joined
Jun 2, 2003
Messages
4,729
We're going to morph the JREFwiki into the SkepticWiki in the near future, but I have a little problem...

When I created the wiki, I gave the tables a jref_ prefix.

I now want to change the prefix on all tables to skep_

Does anyone know if there's a wildcard-type command in SQL that allows this in one fail swoop?
 
As far as I know there isn't anything like this in SQL languages, however, most databases provide a way to get to the metadata.

For example, with SQL Server you could write a script like that using the sysobjects table:

-- Gets the name of all tables starting with "jref."
select name from sysobjects where name like 'jref%' and type='U'

What database are you using? MySQL?
 
Diamond said:
We're going to morph the JREFwiki into the SkepticWiki in the near future, but I have a little problem...

When I created the wiki, I gave the tables a jref_ prefix.

I now want to change the prefix on all tables to skep_

Does anyone know if there's a wildcard-type command in SQL that allows this in one fail swoop?
Click to expand...

For MySQL I'd probably export the whole database and the tables, use a text editor to modify the table definitions, create a new database, import the new file into that database. Then you have 2 databases, old and new, that you can experiment with.

Probably have to do some tweaking to make sure the user used in first database has permissions in second database.
 
As a previous poster said you can probably write an SQL statement that updates metadata. I think you would have to use cursors to iterate the rows and make the changes but it should be possible.

However - what about your application? Does it not refer to the original table names? Will you have to trawl through application code and change it all?
 
Diamond said:
We're going to morph the JREFwiki into the SkepticWiki in the near future, but I have a little problem...

When I created the wiki, I gave the tables a jref_ prefix.

I now want to change the prefix on all tables to skep_

Does anyone know if there's a wildcard-type command in SQL that allows this in one fail swoop?
Click to expand...

I don't understand the problem.

Table names are usually only used in SQL queries, not in databases themselves, unless someone is doing a "clever" kludge. The queries are usually somewhere other than the database, again, unless someone is doing a "clever" kludge or making use of stored procedures. With stored procedures, say in Oracle, a simple PL/SQL program should do the trick, and the queries can be handled with Perl.

I suspect, though, that this isn't Oracle but something like MySQL.

Could you please explain better? I'd be willing to volunteer my time to help sort this out.
 
This is MySQL.

The prefix "jref_" is used in a single php config file into a global variable which is then passed to all of the others.

The ideal would be to change the table prefixes without having to reload the permissions.

The hard way would be to rename them by hand.
 
Diamond said:
This is MySQL.

The prefix "jref_" is used in a single php config file into a global variable which is then passed to all of the others.

The ideal would be to change the table prefixes without having to reload the permissions.

The hard way would be to rename them by hand.
Click to expand...

OK. Then this is inherently outside the realm of SQL per se.

My immediate thought of a solution would be to use a crawler written in Perl. I would tend to prefer one that created copies of all the tables with the new prefix and set the permissions on those. Then change the config file and see if it works. If it does, get rid of all the old tables.

This is the safest way of doing this I can think of without more information, and it also has the advantage that the site only needs to be taken down for the duration of the single change and testing. The copying can be done whilst the site is up, but it should be read-only for that period of time.

I know more about Oracle than I do about MySQL, and in Oracle I'd use a PL/SQL crawler on the all_tables table to produce a datafile that I'd munge in Perl and use to produce a script that could be fed to SQL Plus.

I'm still willing to volunteer to help. PM me if you're interested.
 
From the manual "You can use standard system commands to back up, rename, move, delete, and copy tables that are managed by the MyISAM or ISAM storage engines. For example, to rename a MyISAM table, rename the .MYD, .MYI, and .frm files to which the table corresponds."

Othewise you can do an ALTER TABLE RENAME.

Caution: I'm not a mysql guy, I'm more familiar with DB/2
 
You must log in or register to reply here.

Back
Top Bottom