mcs/class/System.Data.Linq/src/DbLinq.Oracle/OracleSchemaLoader.Constraints.cs

   1 #region MIT license\r
   2 // \r
   3 // MIT license\r
   4 //\r
   5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne\r
   6 // \r
   7 // Permission is hereby granted, free of charge, to any person obtaining a copy\r
   8 // of this software and associated documentation files (the "Software"), to deal\r
   9 // in the Software without restriction, including without limitation the rights\r
  10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell\r
  11 // copies of the Software, and to permit persons to whom the Software is\r
  12 // furnished to do so, subject to the following conditions:\r
  13 // \r
  14 // The above copyright notice and this permission notice shall be included in\r
  15 // all copies or substantial portions of the Software.\r
  16 // \r
  17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\r
  18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\r
  19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\r
  20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\r
  21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\r
  22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN\r
  23 // THE SOFTWARE.\r
  24 // \r
  25 #endregion\r
  26 using System;\r
  27 using System.Collections.Generic;\r
  28 using System.Data;\r
  29 using System.Linq;\r
  30 using System.Text;\r
  31 using System.Text.RegularExpressions;\r
  32 using DbLinq.Util;\r
  33 \r
  34 namespace DbLinq.Oracle\r
  35 {\r
  36     partial class OracleSchemaLoader\r
  37     {\r
  38         protected class DataConstraint\r
  39         {\r
  40             public string TableSchema;\r
  41             public string ConstraintName;\r
  42             public string TableName;\r
  43             public List<string> ColumnNames = new List<string>();\r
  44             public string ColumnNameList { get { return string.Join(",", ColumnNames.ToArray()); } }\r
  45             public string ConstraintType;\r
  46             public string ReverseConstraintName;\r
  47             public string Expression;\r
  48 \r
  49             public override string ToString()\r
  50             {\r
  51                 return "User_Constraint  " + TableName + "." + ColumnNameList;\r
  52             }\r
  53         }\r
  54 \r
  55         private static Regex TriggerMatch1 = new Regex(@".*SELECT\s+(?<exp>\S+.*)\s+INTO\s+\:new.(?<col>\S+)\s+FROM\s+DUAL.*",\r
  56             RegexOptions.Compiled | RegexOptions.IgnoreCase);\r
  57 \r
  58         protected bool MatchTrigger(Regex regex, string fullText, out string expression, out string column)\r
  59         {\r
  60             var match = regex.Match(fullText);\r
  61             if (match.Success)\r
  62             {\r
  63                 expression = match.Groups["exp"].Value;\r
  64                 column = match.Groups["col"].Value;\r
  65                 return true;\r
  66             }\r
  67             expression = null;\r
  68             column = null;\r
  69             return false;\r
  70         }\r
  71 \r
  72         protected virtual DataConstraint ReadTrigger(IDataReader rdr)\r
  73         {\r
  74             var constraint = new DataConstraint();\r
  75             int field = 0;\r
  76             constraint.ConstraintName = rdr.GetAsString(field++);\r
  77             constraint.TableSchema = rdr.GetAsString(field++);\r
  78             constraint.TableName = rdr.GetAsString(field++);\r
  79             constraint.ConstraintType = "T";\r
  80             string body = rdr.GetAsString(field++);\r
  81             //BEGIN\r
  82             //   IF (:new."EmployeeID" IS NULL) THEN\r
  83             //        SELECT Employees_seq.NEXTVAL INTO :new."EmployeeID" FROM DUAL;\r
  84             //   END IF;\r
  85             //END;\r
  86             string expression, column;\r
  87             if (MatchTrigger(TriggerMatch1, body, out expression, out column))\r
  88             {\r
  89                 constraint.ColumnNames.Add(column.Trim('"'));\r
  90                 constraint.Expression = expression;\r
  91             }\r
  92             return constraint;\r
  93         }\r
  94 \r
  95         protected virtual List<DataConstraint> ReadConstraints(IDbConnection conn, string db)\r
  96         {\r
  97             var constraints = new List<DataConstraint>();\r
  98 \r
  99             string sql = @"\r
 100 SELECT UCC.owner, UCC.constraint_name, UCC.table_name, UC.constraint_type, UC.R_constraint_name, UCC.column_name, UCC.position\r
 101 FROM all_cons_columns UCC, all_constraints UC\r
 102 WHERE UCC.constraint_name=UC.constraint_name\r
 103 AND UCC.table_name=UC.table_name\r
 104 AND UCC.owner=UC.owner\r
 105 AND UCC.TABLE_NAME NOT LIKE '%$%' AND UCC.TABLE_NAME NOT LIKE 'LOGMNR%' AND UCC.TABLE_NAME NOT IN ('HELP','SQLPLUS_PRODUCT_PROFILE')\r
 106 AND UC.CONSTRAINT_TYPE!='C'\r
 107 and lower(UCC.owner) = :owner";\r
 108 \r
 109             constraints.AddRange(DataCommand.Find(conn, sql, ":owner", db.ToLower(),\r
 110                     r => new\r
 111                     {\r
 112                         Key = new\r
 113                         {\r
 114                             Owner = r.GetString(0),\r
 115                             ConName = r.GetString(1),\r
 116                             TableName = r.GetString(2),\r
 117                             ConType = r.GetString(3),\r
 118                             RevCconName = r.GetAsString(4)\r
 119                         },\r
 120                         Value = new\r
 121                         {\r
 122                             ColName = r.GetString(5),\r
 123                             ColPos = r.GetInt32(6)\r
 124                         }\r
 125                     })\r
 126                 .GroupBy(r => r.Key, r => r.Value, (r, rs) => new DataConstraint\r
 127                 {\r
 128                     TableSchema = r.Owner,\r
 129                     ConstraintName = r.ConName,\r
 130                     TableName = r.TableName,\r
 131                     ConstraintType = r.ConType,\r
 132                     ReverseConstraintName = r.RevCconName,\r
 133                     ColumnNames = rs.OrderBy(t => t.ColPos).Select(t => t.ColName).ToList()\r
 134                 }));\r
 135 \r
 136             string sql2 =\r
 137                 @"\r
 138 select t.TRIGGER_NAME, t.TABLE_OWNER, t.TABLE_NAME, t.TRIGGER_BODY from ALL_TRIGGERS t\r
 139 where t.status = 'ENABLED'\r
 140  and t.TRIGGERING_EVENT = 'INSERT'\r
 141  and t.TRIGGER_TYPE='BEFORE EACH ROW'\r
 142  and lower(t.owner) = :owner";\r
 143 \r
 144             constraints.AddRange(DataCommand.Find<DataConstraint>(conn, sql2, ":owner", db.ToLower(), ReadTrigger));\r
 145             return constraints;\r
 146         }\r
 147     }\r
 148 }\r