Dynamicaly adding table prefixes to mysql queries

[rudibr]

Hello

 

I recently have been working on a rather large php project, very poorly coded and with over 10000 queries along the code.

 

I was asked to add table prefixing to the related mysql database. Ive been trying to figure out how it would be best to proceed.

 

Its not viable for me to manually add a prefix constant to every query, so I was wondering if maybe, someone coded a regex based function/class that would parse a query and possibly return the table names.

 

I might as well take upon this task myself if I cant find anything like that.

 

If anyone have any ideas or new ideas on how could I go about dynamically prefixing table names, Id be very much appreciated.

 

Thanks

[effigy]

Can you explain further and give some examples? I'm not sure what you're prefixing exactly... are you adding aliases?

[rudibr]

I have to prefix the existing mysql table names in that project.

This is easy enough, but adding the new prefixed names to all queries in the php files is not.

 

So, ideally, Id like to parse every query that goes trough the code, detect the table names in it, add the prefix and rewrite the query.

Detecting table names from queries (beforing commiting them) is of course troublesome, with so many different combinations possible.

 

for an example, I would get the following query, treat it with this 'table name detection method' :

 

"SELECT c.*, a.*, b.sistema as ownarea, b.sis_id as ownarea_cod

FROM configusercall as c, sistemas as a, sistemas as b

WHERE c.conf_opentoarea = a.sis_id and c.conf_ownarea = b.sis_id";

 

and I would get a query as a result, with added prefix to the table names :

 

"SELECT c.*, a.*, b.sistema as ownarea, b.sis_id as ownarea_cod

FROM PREFIX_configusercall as c, PREFIX_sistemas as a, PREFIX_sistemas as b

WHERE c.conf_opentoarea = a.sis_id and c.conf_ownarea = b.sis_id";

[effigy]

As always, back up your files before testing.

 

<pre>
<?php
echo $data = <<<DATA
"SELECT c.*, a.*, b.sistema as ownarea, b.sis_id as ownarea_cod
FROM configusercall as c, sistemas as a, sistemas as b
WHERE c.conf_opentoarea = a.sis_id and c.conf_ownarea = b.sis_id";
DATA;

echo '<hr>';

function prefix_tbl ($matches) {
	$pieces = preg_split('/(?<=FROM)|(?=WHERE)/', $matches[0]);
	### There should only be one FROM and one WHERE, thus resulting in
	### three parts, the tables being in the middle.
	$pieces[1] = preg_replace('/(?<=\s)(?=\w+ as )/i', 'PREFIX_', $pieces[1]);
	return join('', $pieces);
}

### Find all SQL SELECT statements.
echo $data = preg_replace_callback('/
	(["\']) ### Start of quoted SQL.
	SELECT ### Only do SELECT statements.
	.+? ### The rest of the SQL.
	\1; ### Closing quote + ;
/sx', 'prefix_tbl', $data);
?>
</pre>

[rudibr]

Thanks a lot for your help effigy.

 

Your code works great, but there will be too many other different combinations I would have to deal it (i.e., joins, multiple where's, unions (multiple from's), grouped table names without spaces (from sistemas,areas,etc 'instead of' from sistemas, areas, etc).

 

I would not expect or ask for you or anyone else to just handle me the whole thing, but your code is a good starting point for me, if I decide to go by this path.

[roopurt18]

Perhaps the better question is why do you have to prefix the table names?  Maybe you can get around having to do that.

 

However, if you really want to set accomplish what you're after, you will have to parse each query yourself.  This is akin to writing your own compiler in that you need to need to create a grammar (which matches MySQL's grammar exactly), create a tokenizer, and create a parser.  There is a lot more to take into account than just the table names between FROM and WHERE.  You can have joins, sub-queries, and tables that haven't been aliased within the query.  The only way to do this accurately is to create a proper parser. 

 

It's in your best interest to not have to do this.

[rudibr]

I wish I didnt have to, indeed. This piece of software is intended to be integrated into a completly different framework wich has its own modules and directives. Part of this butcher work is to prefix the tables so its easily identified in the final database (about 200 tables long).

 

I think I might just manually edit every query and add the ".PREFIX." to them. Its just an insanely boring work and very easily prone to error when considering the sheer number of queries.

 

But thanks a lot anyway for the help and suggestions.

[effigy]

What do some of the more advanced queries look like?

Do they all use uppercase for SQL keywords?

Do you know all of the table names--are there many?

[rudibr]

not all queries use uppercase for the reserved words (its a messy system wich has been trough 3 developers).

I do know the table names (70 tables).

 

there is a wide range of different approachs these guys chose for creating their queries.

 

One of the ugliest ones is this (I believe its formatted to fit some type of logging before commiting):

$QRY["full_detail_ini"] = "select c.comp_dell_express_code as dell_c, c.comp_dell_service_tag as dell_t,c.comp_inv as etiqueta, c.comp_sn as serial, c.comp_nome as nome, ".

"\n\tc.comp_nf as nota, inst.inst_nome as instituicao, inst.inst_cod as cod_inst, ".

"\n\tc.comp_coment as comentario, c.comp_valor as valor, c.comp_data as data_cadastro, ".

"\n\tc.comp_data_compra as data_compra, c.comp_ccusto as ccusto, c.comp_situac as situacao, ".

"\n\tc.comp_local as tipo_local, loc.loc_reitoria as reitoria_cod, reit.reit_nome as reitoria, ".

"\n\tc.comp_mb as tipo_mb, c.comp_proc as tipo_proc, ".

"\n\tc.comp_tipo_equip as tipo, c.comp_memo as tipo_memo, c.comp_video as tipo_video, ".

"\n\tc.comp_modelohd as tipo_hd, c.comp_modem as tipo_modem, c.comp_cdrom as tipo_cdrom, ".

"\n\tc.comp_dvd as tipo_dvd, c.comp_grav as tipo_grav, c.comp_resolucao as tipo_resol, ".

"\n\tc.comp_polegada as tipo_pole, c.comp_tipo_imp as tipo_imp, c.comp_assist as assistencia_cod, ".

"\n\tequip.tipo_nome as equipamento, c.comp_rede as tipo_rede, c.comp_som as tipo_som, ".

"\n\tt.tipo_imp_nome as impressora, loc.local, ".

 

"\n\tproc.mdit_fabricante as fabricante_proc, proc.mdit_desc as processador, ".

"\n\tproc.mdit_desc_capacidade as clock, proc.mdit_cod as cod_processador, ".

"\n\tproc.mdit_sufixo as proc_sufixo, ".

"\n\thd.mdit_fabricante as fabricante_hd, hd.mdit_desc as hd, hd.mdit_desc_capacidade as hd_capacidade, ".

"\n\thd.mdit_cod as cod_hd, ".

"\n\thd.mdit_sufixo as hd_sufixo, ".

"\n\tvid.mdit_fabricante as fabricante_video, vid.mdit_desc as video, vid.mdit_cod as cod_video, ".

"\n\tred.mdit_fabricante as rede_fabricante, red.mdit_desc as rede, red.mdit_cod as cod_rede, ".

"\n\tmd.mdit_fabricante as fabricante_modem, md.mdit_desc as modem, md.mdit_cod as cod_modem, ".

"\n\tcd.mdit_fabricante as fabricante_cdrom, cd.mdit_desc as cdrom, cd.mdit_cod as cod_cdrom, ".

"\n\tgrav.mdit_fabricante as fabricante_gravador, grav.mdit_desc as gravador, grav.mdit_cod as cod_gravador, ".

"\n\tdvd.mdit_fabricante as fabricante_dvd, dvd.mdit_desc as dvd, dvd.mdit_cod as cod_dvd, ".

"\n\tmb.mdit_fabricante as fabricante_mb, mb.mdit_desc as mb, mb.mdit_cod as cod_mb, ".

"\n\tmemo.mdit_desc_capacidade as memoria, memo.mdit_cod as cod_memoria, memo.mdit_sufixo as memo_sufixo, ".

"\n\tsom.mdit_fabricante as fabricante_som, som.mdit_desc as som, som.mdit_cod as cod_som, ".

 

"\n\tfab.fab_nome as fab_nome, fab.fab_cod as fab_cod, fo.forn_cod as fornecedor_cod, ".

"\n\tfo.forn_nome as fornecedor_nome, model.marc_cod as modelo_cod, model.marc_nome as modelo, ".

"\n\tpol.pole_cod as polegada_cod, pol.pole_nome as polegada_nome, ".

"\n\tres.resol_cod as resolucao_cod, res.resol_nome as resol_nome, ".

"\n\tsit.situac_cod as situac_cod, sit.situac_nome as situac_nome, sit.situac_destaque as situac_destaque, ".

 

"\n\ttmp.tempo_meses as tempo, tmp.tempo_cod as tempo_cod, ".

"\n\ttp.tipo_garant_nome as tipo_garantia, tp.tipo_garant_cod as garantia_cod, ".

 

"\n\tdate_add(c.comp_data_compra, interval tmp.tempo_meses month)as vencimento, ".

"\n\tsoft.soft_desc as software, soft.soft_versao as versao, ".

"\n\tassist.assist_desc as assistencia ".

 

"\nFROM ((((((((((((((((((((((((equipamentos as c left join tipo_imp as t on ".

"\n\tt.tipo_imp_cod = c.comp_tipo_imp) ".

"\n\tleft join polegada as pol on c.comp_polegada = pol.pole_cod) ".

"\n\tleft join resolucao as res on c.comp_resolucao = res.resol_cod) ".

"\n\tleft join fabricantes as fab on fab.fab_cod = c.comp_fab) ".

"\n\tleft join fornecedores as fo on fo.forn_cod = c.comp_fornecedor) ".

"\n\tleft join situacao as sit on sit.situac_cod = c.comp_situac) ".

"\n\tleft join tempo_garantia as tmp on tmp.tempo_cod =c.comp_garant_meses) ".

"\n\tleft join tipo_garantia as tp on tp.tipo_garant_cod = c.comp_tipo_garant) ".

 

"\n\tleft join assistencia as assist on assist.assist_cod = c.comp_assist) ".

 

"\n\tleft join modelos_itens as proc on proc.mdit_cod = c.comp_proc) ".

"\n\tleft join modelos_itens as hd on hd.mdit_cod = c.comp_modelohd) ".

"\n\tleft join modelos_itens as vid on vid.mdit_cod = c.comp_video) ".

"\n\tleft join modelos_itens as red on red.mdit_cod = c.comp_rede) ".

"\n\tleft join modelos_itens as md on md.mdit_cod = c.comp_modem) ".

"\n\tleft join modelos_itens as cd on cd.mdit_cod = c.comp_cdrom) ".

"\n\tleft join modelos_itens as grav on grav.mdit_cod = c.comp_grav) ".

"\n\tleft join modelos_itens as dvd on dvd.mdit_cod = c.comp_dvd) ".

"\n\tleft join modelos_itens as mb on mb.mdit_cod = c.comp_mb) ".

"\n\tleft join modelos_itens as memo on memo.mdit_cod = c.comp_memo) ".

"\n\tleft join modelos_itens as som on som.mdit_cod = c.comp_som) ".

 

"\n\tleft join hw_sw as hw on hw.hws_hw_cod = c.comp_inv and hw.hws_hw_inst = c.comp_inst) ".

"\n\tleft join softwares as soft on soft.soft_cod = hw.hws_sw_cod) ".

 

"\n\tleft join localizacao as loc on loc.loc_id = c.comp_local) ".

"\n\tleft join reitorias as reit on reit.reit_cod = loc.loc_id), ".

 

"\n\tinstituicao as inst, marcas_comp as model, tipo_equip as equip ".

"\nWHERE ".

"\n\t(c.comp_inst = inst.inst_cod) and (c.comp_marca = model.marc_cod) and ".

"\n\t(c.comp_tipo_equip = equip.tipo_cod) ";

 

and them, a different programmer create a query like this :

 

$QRY["vencimentos"] = "SELECT count(*) AS quantidade,

date_add(date_format(comp_data_compra, '%Y-%m-%d') , INTERVAL tempo_meses MONTH) AS vencimento,

marc_nome AS modelo, fab_nome AS fabricante, tipo_nome AS tipo

FROM equipamentos, tempo_garantia, marcas_comp, fabricantes, tipo_equip

WHERE date_add(comp_data_compra, INTERVAL tempo_meses MONTH) >= curdate()

AND comp_garant_meses = tempo_cod AND comp_tipo_equip NOT IN (5)

AND comp_marca = marc_cod AND comp_fab = fab_cod AND comp_tipo_equip = tipo_cod

AND (date_format(curdate() , '%Y') = date_format(date_add(comp_data_compra, INTERVAL tempo_meses MONTH) , '%Y')

OR date_format(curdate() , '%Y' )+3>= date_format(date_add(comp_data_compra, INTERVAL tempo_meses MONTH) , '%Y' ))

GROUP BY vencimento, modelo

ORDER BY vencimento, modelo";

 

 

[effigy]

Perhaps you could find the SQL strings, then make the additions based on the table names?

[roopurt18]

Part of this butcher work is to prefix the tables so its easily identified in the final database (about 200 tables long).

 

I can understand why someone might want to do this, but it really is a superficial thing.  Unless there is a technical restriction, who cares if the table is called users or old_system_users.