issue_comments: 1255332217

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/sqlite-utils/issues/358#issuecomment-1255332217 https://api.github.com/repos/simonw/sqlite-utils/issues/358 1255332217 IC_kwDOCGYnMM5K0tl5 9599 2022-09-22T17:27:34Z 2022-09-22T17:27:34Z OWNER

I've been thinking about this more recently.

I think the first place to explore these will be in the create-table command (and underlying APIs).

Relevant docs: https://www.sqlite.org/lang_createtable.html#check_constraints

A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. The expression of a CHECK constraint may not contain a subquery.

Something like this:

sqlite-utils create-table data.db entries id integer title text tags text --pk id --check tags:json

Where --check tags:json uses a pre-baked recipe for using the SQLite JSON function to check that the content is valid JSON and reject it otherwise.

Then can bundle a bunch of other pre-baked recipes, but also support the following:

--check 'x > 3' --check 'length(phone) >= 10'

The besign reason for the column:recipe format here is to reuse --check for both pre-defined recipes that affect a single column AND for freeform expressions that get added to the end of the table.

Detecting column name:recipe with a regex feels safe to me.

 
{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 1082651698