I am modifying a C# app to loop through a bunch of SQL files and replacing a view name with its corresponding table name.
For example, a view named "Item" should be replaced with a table named "Item_mst".
I have a mapping file that the app looks at to determine which tables to replace and looks like this:
VIEWNAME1,TABLENAME1_MST
VIEWNAME2,TABLENAME2_MST
The problem is, there are a lot of scenarios to look for, I have described them below with an example SQL file.
Select
i.item <<Matches the Item table but is actually the name of the column, no replace
,dbo.item.u_m <<Matches the Item table with DBO., replace
,lot <<Matches the lot table but is actually the name of a column, no replace
,(Select top 1 cost from itemcost) as Cost <<itemcost is a table, replace
,(Select top 1 cost2 from dbo.itemcost) as Cost2 <<matches the itemcost table with dbo., replace
From
dbo.item i <<matches the item table with dbo.,replace
cross join itemlot <<matches the itemlot table, replace
As you can see, there are many different scenarios, and I am having a hard time writing a regex/find and replace algorithm to catch every scenario reliably.
I am matching dbo.tablename and replacing those easy enough, it is the remaining scenarios I am having troubles with. Are there any REGEX experts out there that have any ideas?
Here is a code snippet:
//Replace all matched views with table names
for (int j = 0; j < viewCount.Count(); j++)
{
//replace for "."
curFileText = curFileText.Replace(".", "XXXPERIODXXX");
curFileText = Regex.Replace(curFileText, "dboXXXPERIODXXX" + ViewTables[0, j] + "XXXPERIODXXX", "dboXXXPERIODXXX" + ViewTables[1, j] + "XXXPERIODXXX", RegexOptions.IgnoreCase);
curFileText = curFileText.Replace("XXXPERIODXXX", ".");
//replace for "newline"
curFileText = curFileText.Replace(System.Environment.NewLine, "XXXNEWLINEXXX");
curFileText = Regex.Replace(curFileText, ViewTables[0, j] + "XXXNEWLINEXXX", ViewTables[1, j] + "XXXNEWLINEXXX", RegexOptions.IgnoreCase);
curFileText = curFileText.Replace("XXXNEWLINEXXX", System.Environment.NewLine);
//Fix .column_mst spots
curFileText = curFileText.Replace("dbo.", "XXXDBODOTXXX");
curFileText = curFileText.Replace(".", "XXXDOTXXX");
curFileText = Regex.Replace(curFileText, "XXXDOTXXX" + ViewTables[1, j], "XXXDOTXXX" + ViewTables[0, j], RegexOptions.IgnoreCase);
curFileText = curFileText.Replace("XXXDOTXXX", ".");
curFileText = curFileText.Replace("XXXDBODOTXXX", "dbo.");
//replace for " "
curFileText = curFileText.Replace(" ", "XXXSPACEXXX");
curFileText = Regex.Replace(curFileText, "dbo." + ViewTables[0, j] + "XXXSPACEXXX", "dbo." + ViewTables[1, j] + "XXXSPACEXXX", RegexOptions.IgnoreCase);
curFileText = curFileText.Replace("XXXSPACEXXX", " ");
}
Aucun commentaire:
Enregistrer un commentaire