Exemplo n.º 1
0
        public string GetSqlForDatatype(SchemaField schemaField, string sqlbase, string errorLevel)
        {
            string sql = "";

            switch (schemaField.DataType)
            {
            case DataType.Boolean:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISBOOL(" + schemaField.Name + ")");
                break;

            case DataType.@byte:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISBYTE(" + schemaField.Name + ")");
                break;

            case DataType.@char:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISCHAR(" + schemaField.Name + ")");
                break;

            case DataType.DateTime:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISDATETIME(" + schemaField.Name + ", '" + schemaField.Constraints.DatePattern + "') ");
                break;

            case DataType.@decimal:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISDECIMAL(" + schemaField.Name + ")");
                break;

            case DataType.@double:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISDOUBLE(" + schemaField.Name + ")");
                break;

            case DataType.@float:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISFLOAT(" + schemaField.Name + ")");
                break;

            case DataType.@int:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISINT(" + schemaField.Name + ")");
                break;

            case DataType.@long:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISLONG(" + schemaField.Name + ")");
                break;

            case DataType.@sbyte:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISSBYTE(" + schemaField.Name + ")");
                break;

            case DataType.@short:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISSHORT(" + schemaField.Name + ")");
                break;

            case DataType.TimeSpan:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISTIMESPAN(" + schemaField.Name + ")");
                break;

            case DataType.@uint:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISUINT(" + schemaField.Name + ")");
                break;

            case DataType.@ulong:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISULONG(" + schemaField.Name + ")");
                break;

            case DataType.URI:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISURI(" + schemaField.Name + ")");
                break;

            case DataType.@ushort:
                sql = String.Format(sqlbase, "'Invalid Datatype'", errorLevel, "WHERE NOT ISUSHORT(" + schemaField.Name + ")");
                break;

            default:
                // object or string
                break;
                //NPS_TODO all other datatypes
            }
            return(sql);
        }
Exemplo n.º 2
0
        public void ValidateFields(SchemaField schemaField, string tableName, IResultWriter writer)
        {
            var sqlbase = @"INSERT INTO " + tableName + "_Errors" +
                          @" SELECT {0}, '" + schemaField.Name + @"', {1},*
                             FROM " + tableName + @" 
                             {2} LIMIT " + vOptions.QueryErrorLimit + ";";
            string sql = "";

            if (schemaField.Constraints.Required)
            {
                sql = String.Format(sqlbase, "'Required Field'", esettings.RequiredErrorLevel.SingleQuote(), " WHERE " + schemaField.Name + " = '' OR " + schemaField.Name + " IS NULL");
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Required Field check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Required Field check for " + schemaField.Name);
            }

            if (schemaField.DataType != DataType.@string &&
                (schemaField.Constraints.Required || (esettings.ErrorOnUnrequiredWithBadDatatype && !schemaField.Constraints.Required)))
            {
                sql = GetSqlForDatatype(schemaField, sqlbase, esettings.DatatypeErrorLevel.SingleQuote());
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Datatype check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Datatype check for " + schemaField.Name);
            }

            if (!double.IsNaN(schemaField.Constraints.Maximum))
            {
                sql = String.Format(sqlbase, "'Maximum Value'", esettings.MaximumErrorLevel.SingleQuote(), "WHERE COMPARE(" + schemaField.Name + @", '>', " + schemaField.Constraints.Maximum + @", '" + schemaField.DataType.ToString() + "')");
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Maximum Value check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Maximum Value check for " + schemaField.Name);
            }
            if (!double.IsNaN(schemaField.Constraints.Minimum))
            {
                sql = String.Format(sqlbase, "'Minimum Value'", esettings.MinimumErrorLevel.SingleQuote(), "WHERE COMPARE(" + schemaField.Name + @", '<', " + schemaField.Constraints.Minimum + @", '" + schemaField.DataType.ToString() + "')");
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Minimum Value check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Minimum Value check for " + schemaField.Name);
            }
            if (schemaField.Constraints.MinLength > -1)
            {
                sql = String.Format(sqlbase, "'Minimum Length'", esettings.MinLengthErrorLevel.SingleQuote(), "WHERE LENGTH(" + schemaField.Name + @") < " + schemaField.Constraints.MinLength);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Minimum Length check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Minimum Length check for " + schemaField.Name);
            }
            if (schemaField.Constraints.MaxLength > -1)
            {
                sql = String.Format(sqlbase, "'Maximum Length'", esettings.MaxLengthErrorLevel.SingleQuote(), "WHERE LENGTH(" + schemaField.Name + @") > " + schemaField.Constraints.MaxLength);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Maximum Length check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Maximum Length check for " + schemaField.Name);
            }
            if ((schemaField.Constraints.Pattern != null) && (schemaField.Constraints.Pattern != String.Empty))
            {
                sql = String.Format(sqlbase, "'Invalid Value(pattern)'", esettings.PatternErrorLevel.SingleQuote(), "WHERE '" + schemaField.Constraints.Pattern + @"' NOT REGEXP " + schemaField.Name);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Pattern check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Pattern check for " + schemaField.Name);
            }
            if (schemaField.Constraints.Enum != null && schemaField.Constraints.Enum.Length > 0)
            {
                var values = "(" + String.Join(",", schemaField.Constraints.Enum.ToList().Select(x => "'" + x + "'")) + ")";
                sql = String.Format(sqlbase, "'Enum Valid Values'", esettings.MaxLengthErrorLevel.SingleQuote(), "WHERE " + schemaField.Name + @" NOT IN " + values);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Enum Valid Values check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Enum Valid Values check for " + schemaField.Name);
            }
            if (schemaField.Constraints.Unique)
            {
                // Unique speed hack
                sql = @"INSERT INTO " + tableName + "_Errors" +
                      @" SELECT 'Unique', '" + schemaField.Name + @"'," + esettings.UniqueErrorLevel.SingleQuote() + @" ,*
                             FROM " + tableName + @" 
                             WHERE rowid IN (SELECT rowid FROM " + tableName + @" GROUP BY " + schemaField.Name + "HAVING COUNT(*) > 1;";
                writer.WriteVerbose("[" + DateTime.Now + "] " + "Start Unique check for " + schemaField.Name);
                SqliteStatus = db.ExecuteQuery(sql, Validate, ErrorCount);
                writer.WriteVerbose("[" + DateTime.Now + "] " + "End Unique check for " + schemaField.Name);
            }
        }