Great progress on more types / constraints

[TonyGravagno]

The following code was generated using details in the AST, which we can now save with recent mods. 🤩

First, new types are processed. Note the constraints.

SQL

CREATE TABLE constraints01 (
    -- Integer Types with Constraints
    SmallInt11 SMALLINT CHECK (SmallInt11 > 0),                      -- Positive values only
    SmallInt12 SMALLINT CHECK (SmallInt12 BETWEEN 1 AND 100),        -- Specific range
    Int11 INT CHECK (Int11 % 5 = 0),                                 -- Multiples of 5
    Int12 INT CHECK (Int12 <> 0),                                    -- Non-zero values
    MediumInt11 MEDIUMINT CHECK (MediumInt11 IN (10, 20, 30)),       -- ENUM-like behavior for integers

    -- Decimal Types with Constraints
    Decimal11 DECIMAL(10, 2) CHECK (Decimal11 >= 0 AND Decimal11 <= 1000.00), -- Range for financial values
    Decimal12 DECIMAL(5, 2) CHECK (ROUND(Decimal12, 2) = Decimal12),          -- Fixed two decimal places

    -- Boolean and Bit Type Constraints
    Boolean11 BOOLEAN CHECK (Boolean11 IN (0, 1)),                  -- Standard boolean behavior
    Bit11 BIT(8) CHECK (Bit11 = b'00001111'),                       -- Specific bit pattern

    -- Date and Time Types with Constraints
    Date11 DATE CHECK (Date11 > '2024-01-01'),                      -- Date in the future
    Time11 TIME CHECK (Time11 BETWEEN '08:00:00' AND '18:00:00'),   -- Business hours
    -- DateTime11 DATETIME CHECK (DateTime11 <= NOW()),                -- Past or present datetime
    Timestamp11 TIMESTAMP DEFAULT NOW()                 -- Current timestamp as default
      CHECK (Timestamp11 >= '2024-01-01 00:00:00'),                 -- Start from a specific datetime

    -- String Types with Constraints
    Varchar11 VARCHAR(100) CHECK (CHAR_LENGTH(Varchar11) >= 10),    -- Minimum string length
    Char11 CHAR(5) CHECK (Char11 IN ('HELLO', 'WORLD')),            -- Specific set of allowed values
    TinyText11 TINYTEXT CHECK (CHAR_LENGTH(TinyText11) <= 255),     -- TinyText within maximum length
    Text11 TEXT CHECK (Text11 LIKE 'Hello%'),                       -- Text pattern matching
    MediumText11 MEDIUMTEXT CHECK (CHAR_LENGTH(MediumText11) <= 10000), -- Limiting length for MediumText
    LongText11 LONGTEXT CHECK (CHAR_LENGTH(LongText11) >= 1000 AND CHAR_LENGTH(LongText11) <= 5000), -- LongText within range

    -- JSON Type with Constraints
    Json11 JSON CHECK (JSON_VALID(Json11)),                         -- Ensuring valid JSON format

    -- Set and Enum Type Constraints
    Enum11 ENUM('small', 'medium', 'large') DEFAULT 'medium' CHECK (Enum11 IN ('small', 'medium', 'large')), -- Enum type with default
    Set11 SET('a', 'b', 'c') CHECK (Set11 <> '')                   -- Set type ensuring non-empty value
);

Here is the new Zod schema.

Zod

const constraints01Schema = z.object({
  SmallInt11: z.number().min(0, { message: "SmallInt11 must be unsigned." }).min(0, { message: "SmallInt11 must be >= 0." }).refine((val) => countDigits(val) <= 6, { message: "SmallInt11 must have at most 6 digits." }).refine((val) => val > 0, { message: "SmallInt11 must be greater than 0." }),
  SmallInt12: z.number().min(0, { message: "SmallInt12 must be unsigned." }).min(0, { message: "SmallInt12 must be >= 0." }).refine((val) => countDigits(val) <= 6, { message: "SmallInt12 must have at most 6 digits." }).min(1, { message: "SmallInt12 must be >= 1." }).max(100, { message: "SmallInt12 must be <= 100." }),
  Int11: z.number().min(0, { message: "Int11 must be unsigned." }).min(0, { message: "Int11 must be >= 0." }).refine((val) => countDigits(val) <= 11, { message: "Int11 must have at most 11 digits." }).refine((val) => val % 5 === 0, { message: "Int11 must be a multiple of 5." }),
  Int12: z.number().min(0, { message: "Int12 must be unsigned." }).min(0, { message: "Int12 must be >= 0." }).refine((val) => countDigits(val) <= 11, { message: "Int12 must have at most 11 digits." }).refine((val) => val !== 0, { message: "Int12 must not be equal to 0." }),
  MediumInt11: z.number().min(0, { message: "MediumInt11 must be unsigned." }).min(0, { message: "MediumInt11 must be >= 0." }).refine((val) => countDigits(val) <= 9, { message: "MediumInt11 must have at most 9 digits." }).refine((val) => [10, 20, 30].includes(val), { message: "MediumInt11 must be one of [10, 20, 30]." }),
  Decimal11: z.string(),
  Decimal12: z.string(),
  Boolean11: z.boolean().refine((val) => val === true || val === false, { message: "Boolean11 must be a boolean." }),
  Bit11: z.string().regex(/^00001111$/, { message: "Bit11 must match the pattern 00001111." }),
  Date11: z.string().refine((val) => new Date(val) > new Date("2024-01-01"), { message: "Date11 must be after 2024-01-01." }),
  Time11: z.string().refine((val) => {
    const dateVal = new Date(val);
    return dateVal >= new Date("08:00:00") && dateVal <= new Date("18:00:00");
  }, { message: "Time11 must be between 08:00:00 and 18:00:00." }),
  DateTime11: z.string().refine((val) => new Date(val) <= new Date(), { message: "DateTime11 must be in the past or present." }),
  Timestamp11: z.string(),
  Varchar11: z.string().max(100, { message: "Varchar11 must have at most 100 characters." }).refine((val) => val.length >= 10, { message: "Varchar11 must have at least 10 characters." }).nullable(),
  Char11: z.enum(['HELLO', 'WORLD']).refine((val) => ['HELLO', 'WORLD'].includes(val), { message: "Char11 must be one of [HELLO, WORLD]." }).nullable(),
  TinyText11: z.string().max(255, { message: "TinyText11 must have at most 255 characters." }).refine((val) => val.length <= 255, { message: "TinyText11 must have at most 255 characters." }).nullable(),
  Text11: z.string().regex(/^Hello%$/, { message: "Text11 must match the pattern Hello%." }).nullable(),
  MediumText11: z.string().max(16777215, { message: "MediumText11 must have at most 16777215 characters." }).refine((val) => val.length <= 10000, { message: "MediumText11 must have at most 10000 characters." }).nullable(),
  LongText11: z.string().refine((val) => {
    const dateVal = new Date(val);
    return dateVal >= new Date("2024-01-01 00:00:00");
  }, { message: "LongText11 must be between 2024-01-01 00:00:00 and today." }).nullable(),
  Json11: z.string().refine((val) => {
    try {
      JSON.parse(val);
      return true;
    } catch {
      return false;
    }
  }, { message: "Json11 must be valid JSON." }).nullable(),
  Enum11: z.enum(['small', 'medium', 'large']).refine((val) => ['small', 'medium', 'large'].includes(val), { message: "Enum11 must be one of [small, medium, large]." }).nullable(),
  Set11: z.string().refine((val) => {
    const items = val.split(',');
    return items.every(item => ['a', 'b', 'c'].includes(item));
  }, { message: "Set11 must be a subset of [a, b, c]." }).refine((val) => val !== '', { message: "Set11 must not be empty." }).nullable(),
});

There are many faults that will be fixed soon.

1. decimal type shows as string but it's actually handled.
2. redundant refinements
3. missing/incorrect min/max values based on data type, like for SmallInt, calculated, just not output.
4. defaults are available, not yet processed.
   Other types are handled, just not in this table.

I'm anxious to continue on this and to make frequent updates to the repo. Enhancements will include:

• abstraction of common functionality into external collections
• support for comparing fields like startDate<=endDate
• message centralization / named message templates
• i18n