Get violated constraint in code

[ilyakooo0]

Is there a way to extract the constraint that was violated in haskell code? Something like ConstraintViolation from postgresql-simple.

[echatav]

There's not much high level functionality for inspecting squeal exceptions like that. At a low level you can look at the the sqlStateCode in the PQState of any PQException.

[ilyakooo0]

Yeah, looking into the postgresql-simple implementation turns out that they just parse the Postgres return string and it is pretty trivial to implement but would most likely require importing a parsing library (although it could be done without one).

[ilyakooo0]

The hardest thing is correctly parsing escaped characters (\).

[echatav]

base has a decent parsing library

http://hackage.haskell.org/package/base-4.12.0.0/docs/Text-ParserCombinators-ReadP.html

[ilyakooo0]

The parser in base seems to be specialized to Strings. It shouldn't be too horrible, but I am not sure converting between representations is desirable.

[adfretlink]

I do have a custom parser in on of my work projects for some violations, but it's hard to have one robust enough to qualify to be part of a library. Issues include:

• SQL error messages can be localized.
• Affected columns and values are typically between single quotes, with double quotes to escape which makes it hard to make sure that no specific string could be unparseable.

Here is my code (using Parsec, sorry !):

extractConstraintViolationCauses :: ByteString -> Maybe (Text, Text)
extractConstraintViolationCauses =
  let bracketed = between (char '(') (char ')') (many (noneOf ")"))
      constraintsAndValues = (,) <$> (bracketed <* string "=") <*> bracketed
      skipTill p = manyTill anyChar (Parsec.try $ lookAhead p)
      extractFromError = skipTill constraintsAndValues *> constraintsAndValues
  in either (const Nothing) (pure . join bimap pack) . parse extractFromError "SQL error message"

[ilyakooo0]

I think the functionality provided in postgresql-simple is useful and robust enough to be in a library. They don't provide an interface for every imaginable error. They give you the ability to extract specific errors: https://hackage.haskell.org/package/postgresql-simple-0.6.2/docs/src/Database.PostgreSQL.Simple.Errors.html#ConstraintViolation

I think is is immensely useful for providing proper errors to users of an API when checking a constraint violation in haskell would be close to impossible. (Range overlaps for example)

[ilyakooo0]

And extracting the name of a violated constraint seems to be as easy as extracting the string between unescaped double quotes.

[ilyakooo0]

Haven't tested this whatsoever.

untilQuote :: ReadP String
untilQuote = do
  soFar <- munch (\c -> c /= '"' || c /= '\\')
  look >>= \case
    "\\" -> (soFar <>) <$> untilQuote
    _ -> return soFar

parseQuoted :: ReadP String
parseQuoted = do
  void untilQuote
  void $ char '"'
  untilQuote

parseQuotedDouble :: ReadP (String, String)
parseQuotedDouble = do
  a <- parseQuoted
  void $ char '"'
  b <- parseQuoted
  return (a, b)

data ConstraintViolation
  = -- | The field is a column name
    NotNullViolation String
  | -- | Table name and name of violated constraint
    ForeignKeyViolation String String
  | -- | Name of violated constraint
    UniqueViolation String
  | -- | Relation name (usually table), constraint name
    CheckViolation String String
  | -- | Name of the exclusion violation constraint
    ExclusionViolation String
  deriving (Show, Eq, Ord, Typeable, Exception)

extractConstraintViolation :: SquealException -> Maybe ConstraintViolation
extractConstraintViolation (PQException pqState) = do
  code <- sqlStateCode pqState
  msg <- unpack <$> sqlErrorMessage pqState
  case code of
    "23502" -> NotNullViolation <$> parseMaybe parseQuoted msg
    "23503" -> uncurry ForeignKeyViolation <$> parseMaybe parseQuotedDouble msg
    "23505" -> UniqueViolation <$> parseMaybe parseQuoted msg
    "23514" -> uncurry CheckViolation <$> parseMaybe parseQuotedDouble msg
    "23P01" -> ExclusionViolation <$> parseMaybe parseQuoted msg
    _ -> Nothing
extractConstraintViolation _ = Nothing

parseMaybe :: ReadP a -> String -> Maybe a
parseMaybe r = fmap fst . listToMaybe . readP_to_S r

[echatav]

In case you haven't already seen, here are all error codes in Postgres. Should we just make a giant sum-of-sums type to turn the error code into? We don't have to do things exactly how Postgres-simple does. Seems like the simplest thing is

data PGStateCode
  = PGSuccessfulCompletion
  | PGWarning PGWarning
  | PGNoData PGNoData
  | PGNotYetComplete PGNotYetComplete
  | PGConnectionException PGConnectionException
  | ..
  | PGIntegrityContraintViolation PGIntegrityContraintViolation
  | ..

data PGIntegrityConstraintViolation
  = IntegrityConstraintViolation
  | RestrictViolation
  | NotNullViolation
  | ForeignKeyViolation
  | UniqueViolation
  | CheckViolation
  | ExclusionViolation

Then parse the state code ByteString into a PGStateCode. Then we could provide restricted handlers for particular classes, similar to try/catch/handleSqueal but handling say PGIntegrityConstraintViolations and otherwise rethrowing. Or is the idea that you want to be able to look at which column caused the unique violation?

[echatav]

Another thing I'd like to do is get rid of the Maybes in squeal exceptions. They seem to come up when there's a connection problem and give some terribly unhelpful error messages.

[echatav]

Added patterns like CheckViolation msg to match on check constraint violations and UniqueViolation msg to match on unique constraint violations. Should be a model for any other particular error a user might want to write a pattern for themselves. Done in #209