oracle 使用正则表达式解析 tnsnames.ora
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3549863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing tnsnames.ora using regex
提问by Welton v3.59
I am attempting to pull some information from my tnsnames file using regex. I started with the following pattern:
我正在尝试使用正则表达式从我的 tnsnames 文件中提取一些信息。我从以下模式开始：
MYSCHEMA *? = *?[\W\w\S\s]*\(HOST *?= *?(?<host>\w+\s?)\)\s?\(PORT *?= *?(?<port>\d+)\s?\)[\W\w\S\s]*\(SERVICE_NAME *?= *?(?<servicename>\w+)\s?\)
MYSCHEMA *? = *?[\W\w\S\s]*\(HOST *?= *?(?<host>\w+\s?)\)\s?\(PORT *?= *?(?<port>\d+)\s?\)[\W\w\S\s]*\(SERVICE_NAME *?= *?(?<servicename>\w+)\s?\)
which worked fine when MYSCHEMA was the only schema in the file, but when there are other schemas listed after MYSCHEMA it matches all the way to the last schema.
当 MYSCHEMA 是文件中唯一的模式时，它工作正常，但是当在 MYSCHEMA 之后列出其他模式时，它会一直匹配到最后一个模式。
I have since created a new pattern:
从那以后，我创建了一个新模式：
MYSCHEMA *=\s*\(DESCRIPTION =\s*\(ADDRESS *= *\(PROTOCOL *= *TCP\)\(HOST *= *(?<host>\w+)\)\(PORT *= *(?<port>\d+)\)\)\s*\(CONNECT_DATA *=\s*(?<serverdedicated>\(SERVER *= *DEDICATED\))\s*\(SERVICE_NAME *= *(?<servicename>[\w\.]+) *\)\s*\)\s*\)
MYSCHEMA *=\s*\(DESCRIPTION =\s*\(ADDRESS *= *\(PROTOCOL *= *TCP\)\(HOST *= *(?<host>\w+)\)\(PORT *= *(?<port>\d+)\)\)\s*\(CONNECT_DATA *=\s*(?<serverdedicated>\(SERVER *= *DEDICATED\))\s*\(SERVICE_NAME *= *(?<servicename>[\w\.]+) *\)\s*\)\s*\)
This pattern matches MYSCHEMA only, but I had to add every element that appeared in MYSCHEMA entry, and it won't match MYOTHERSCHEMA if it does not contain all the same elements.
这个模式只匹配 MYSCHEMA，但我必须添加出现在 MYSCHEMA 条目中的每个元素，如果它不包含所有相同的元素，它就不会匹配 MYOTHERSCHEMA。
Ideally, I'd like a pattern that matches MYSCHEMA entry only, and captures HOST, PORT and SERVICE NAME, and optionally (SERVER = DEDICATED) (which I didn't have in the first pattern) to named groups.
理想情况下，我想要一个仅匹配 MYSCHEMA 条目的模式，并将 HOST、PORT 和 SERVICE NAME 以及可选的 (SERVER = DEDICATED)（我在第一个模式中没有）捕获到命名组。
Below is the sample tnsnames that I've been using for testing:
以下是我一直用于测试的示例 tnsnames：
SOMESCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = REMOTEHOST)(PORT = 1234))
)
(CONNECT_DATA = (SERVICE_NAME = REMOTE)
)
)
MYSCHEMA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1234))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSERVICE.LOCAL )
)
)
MYOTHERSCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1234))
)
(CONNECT_DATA =
(SERVICE_NAME = MYSERVICE.REMOTE)
)
)
SOMEOTHERSCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1234))
)
(CONNECT_DATA =
(SERVICE_NAME = LOCAL)
)
)
采纳答案by Welton v3.59
Well, since I haven't found a compelling answer to this issue (no offense @Mikael Svenson), I have just stuck with the second pattern listed in my question. It is sufficient for the time being, as the tnsnames.ora file always follows that exact pattern within our organization. Should the tnsnames.ora file format change, I will most likely adopt a parser.
好吧，由于我还没有找到对此问题的令人信服的答案（无意冒犯@Mikael Svenson），所以我一直坚持我的问题中列出的第二种模式。暂时足够了，因为 tnsnames.ora 文件在我们的组织中始终遵循该确切模式。如果 tnsnames.ora 文件格式发生变化，我很可能会采用解析器。
回答by Mikael Svenson
This should do it, using balanced groups. And modify the switch/case for your needs.
这应该做到这一点，使用平衡的组。并根据您的需要修改开关/外壳。
class TnsRegex
{
public void Test()
{
Regex reTns = new Regex(_pattern, RegexOptions.Singleline | RegexOptions.IgnorePatternWhitespace);
MatchCollection matchCollection = reTns.Matches(_text);
foreach (Match match in matchCollection)
{
foreach (Capture capture in match.Groups["Settings"].Captures)
{
string[] setting = capture.Value.Split(new[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
string key = setting[0].Trim();
string val = setting[1].Trim();
if (val.Contains("(")) continue;
switch (key)
{
case "HOST":
break;
case "PORT":
break;
case "SERVICE_NAME":
break;
case "SERVER":
break;
}
Console.WriteLine(key + ":" + val);
}
}
}
string _pattern = @"
MYSCHEMA\s+=\s+\(
[^\(\)]*
(
(
(?<Open>\()
[^\(\)]*
)+
(
(?<Settings-Open>\))
[^\(\)]*
)+
)*
(?(Open)(?!))
\)";
string _text = @"
MYSCHEMA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1234))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSERVICE.LOCAL )
)
)
SOMESCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = REMOTEHOST)(PORT = 1234))
)
(CONNECT_DATA = (SERVICE_NAME = REMOTE)
)
)
";
}
回答by HyTC
The following regex will parse out individual TNS entries, and all you have to do is parse each result how you see fit for the names / values
以下正则表达式将解析出单独的 TNS 条目，您所要做的就是解析您认为适合名称/值的每个结果
([\w-]+)\s*=(?:\s|.)+?\)\s*\)\s*\)\s*(?=[\w\-])
([\w-]+)\s*=(?:\s|.)+?\)\s*\)\s*\)\s*(?=[\w\-])
Example: https://regexr.com/3r2vn
回答by Stuart Smith
I tried the above answers and none of them worked for me ...
我尝试了上述答案，但没有一个对我有用......
[DebuggerDisplay("Name {Name} Host:{Host} ServiceName:{ServiceName} Port:{Port}")]
public class TnsEntry
{
public string Name { get; set; }
public string Host { get; set; }
public string Port { get; set; }
public string ServiceName { get; set; }
}
public class TnsNamesFileParser
{
public string TNSNamesContents { get; set; }
public TnsNamesFileParser()
{
}
public TnsNamesFileParser(string locationAndNameOfTnsNamesFile)
{
TNSNamesContents = System.IO.File.ReadAllText(locationAndNameOfTnsNamesFile);
}
public List<TnsEntry> Parse()
{
return Parse(TNSNamesContents);
}
public List<TnsEntry> Parse(string TNSNamesContents)
{
string TNSPattern = @"([\w -] +)\s *= (?:\s |.) +?\)\s *\)\s *\)\s * ((?=[\w\-])|(?=$))";
Regex reTns = new Regex(TNSPattern, RegexOptions.Singleline | RegexOptions.IgnorePatternWhitespace);
MatchCollection matchCollection = reTns.Matches(TNSNamesContents);
var TnsEntries = new List<TnsEntry>();
foreach (Match match in matchCollection)
{
var tnsEntry = new TnsEntry();
string matchedValue = match.Value.Trim();
tnsEntry.Name = Regex.Match(matchedValue, @"^([^\s]+)", RegexOptions.IgnoreCase)?.Value.Trim();
tnsEntry.Host = Regex.Match(matchedValue, "(?<=HOST.+=) ([^)]*)", RegexOptions.IgnoreCase)?.Value.Trim();
tnsEntry.Port = Regex.Match(matchedValue, "(?<=PORT.+=) ([^)]*)", RegexOptions.IgnoreCase)?.Value.Trim();
tnsEntry.ServiceName = Regex.Match(matchedValue, "(?<=SERVICE_NAME.+=) ([^)]*)", RegexOptions.IgnoreCase)?.Value;
TnsEntries.Add(tnsEntry);
}
return TnsEntries;
}
}
//Test Code:
string testdata =@"
SOMESCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = REMOTEHOST)(PORT = 1234))
)
(CONNECT_DATA = (SERVICE_NAME = REMOTE)
)
)
MYSCHEMA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1234))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSERVICE.LOCAL )
)
)
MYOTHERSCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1234))
)
(CONNECT_DATA =
(SERVICE_NAME = MYSERVICE.REMOTE)
)
)
SOMEOTHERSCHEMA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1234))
)
(CONNECT_DATA =
(SERVICE_NAME = LOCAL)
)
)";
[Test]
public void ParseTNSFileEntries()
{
var tnsNamesFileParser = new TnsNamesFileParser();
var entries = tnsNamesFileParser.Parse(testdata);
}
回答by Alcides Martínez
This expression parse the schema with one address on address_list, etc. Hope this helps.
此表达式使用 address_list 上的一个地址解析架构，等等。希望这会有所帮助。
--begin (?>((?[\n][\s][^(][\w_.]+)[\s]=[\s]))(?>(?[\n\s(]DESCRIPTION[\s=\s](?>(?[\n\s(]*ADDRESS_LIST[\s=\s]*[\n\s(]ADDRESS[\s=\s](?([\n\s(]COMMUNITY)([\n\s(]COMMUNITY[\s=\s](?[\w.)]+))|())[\s\n](?([\n\s(]PROTOCOL)([\n\s(]PROTOCOL[\s=\s](?[\w.)]+))|())[\s\n](?([\n\s(]HOST)([\n\s(]HOST[\s=\s](?[\w.)]+))|())[\s\n](?([\n\s(]PORT)([\n\s(]PORT[\s=\s](?[\w.)]+))|())[\s\n](?())())|())))[\s\n](?>(?[\n][\s][(]CONNECT_DATA\s*[=]\s*[\n](?([(]SID\s[=]\s*)(([(]SID\s*[=]\s*(?[\w.]+)\s*[)]))|())[\s\n](?([(]SERVER\s[=]\s*)(([(]SERVER\s*[=]\s*(?[\w.]+)\s*[)]))|())[\s\n]*(?([(]SERVICE_NAME\s*[=]\s*)(([(]SERVICE_NAME\s*[=]\s*(?[\w.]+)\s*[)]))|())[\s\n](?())())|())))[\s\n](?())())|()))) *--end
--begin (?>((?[\n][\s][^(][\w_.]+)[\s] =[\s]))(?>(?[\n\s( ]说明[\s=\s](?>(?[\n\s(]*ADDRESS_LIST[\s=\s]*[\n\s(] ADDRESS[\s=\s](?([ \n\s(]COMMUNITY)([\n\s(]COMMUNITY[\s=\s] (?[\w.)]+))|())[\s\n](?([\ n\s(]PROTOCOL)([\n\s(]PROTOCOL[\s=\s] (?[\w.)]+))|())[\s\n](?([\n \s(]HOST)([\n\s(]HOST[\s=\s] (?[\w.)]+))|())[\s\n](?([\n\ s(]PORT)([\n\s(]PORT[\s=\s] (?[\w.)]+))|())[\s\n](?())()) |())))[\s\n] (?>(?[\n][\s][(]CONNECT_DATA\s*[=]\s*[\n] (?([(]SID\ s[=]\s*)(([(]SID\s*[=]\s*(?[\w.]+)\s*[)]))|())[\s\n] (?([(]SERVER\s[=]\s*)(([(]SERVER\s*[=]\s*(?[\w.]+)\s*[)]))|())[\s\n]* (?([(]SERVICE_NAME\s*[=]\s*)(([(]SERVICE_NAME\s*[=]\s*(?[\w.]+)\s*[)]))| ())[\s\n] (?())())|())))[\s\n](?())())|()))) *--end
Undoubtedly, the problem is the multiple that is in the form of writing that file. As you say the file must be unique, this is solved by using the TNS_ADMIN variable.
毫无疑问，问题在于写入该文件的形式的倍数。正如您所说的文件必须是唯一的，这是通过使用 TNS_ADMIN 变量来解决的。
