database.Query

Behavior

{
  "name": "database.Query",
  "version": "1.0.0-draft.1",
  "rules": [
    "from names a declared Collection and an optional parenthesized projection. Field and reference names must match that Collection. Query objects are not SQL strings.",
    "A query projection with no explicit fields selects non-reference Collection fields. first defaults to id only and returns at most one Record. Order explicitly when the choice matters.",
    "Reference projections enumerate their selected fields. Nested references use nested projections. Reference paths use dot; object-property paths use slash. A bare reference is not a loaded projection. Projections do not fetch extra fields on demand.",
    "Reference reads do not automatically exclude a deleted target. Express the intended active-record conditions explicitly. Reference arrays are not hydrated as linked Records.",
    "Scalar condition values are parameter-bound equality. Structured conditions declare op/value or fn/value. Null checks use nil/nnil rather than scalar null. Object equality requires an explicit eq predicate.",
    "and/or recursively combine conditions. in/nin compare a scalar field to a nonempty candidate array. btw/nbtw use two inclusive bounds. like/ilike use percent and underscore wildcards; there is no undocumented escape option.",
    "overlap, contains and containedBy require a declared array field and matching item types. They do not hydrate Record references.",
    "Filtering precedes grouping. Nonaggregate selected fields belong in groupBy. Aggregate expressions retain their expression as the loaded key, with no AS alias. count counts matching source records, not grouped rows. Aggregate calculations over one returned page describe only that page.",
    "orderBy keys are evaluated in declared order. priority places its single string value first and does not alphabetically rank other values. Direction does not reverse a priority. No null-placement option is defined.",
    "Offset paging starts at zero and uses bounded page counts. Add an identity tie-breaker when stable ordering is required. A cursor visits only the returned page; it does not fetch all rows or establish a frozen snapshot.",
    "distinct compares the complete selected row, not a selected subset and not grouped totals.",
    "Calendar field functions and snippets use database calendar time. For a caller-selected timezone compute explicit bounds with Dates. Calendar behavior does not infer a timezone Input.",
    "Read locks target the base row. Lock types are forUpdate, forNoKeyUpdate, forShare and forKeyShare. Optional qualifiers are noWait and skipLocked. skipLocked may omit existing matching rows. Locks last until transaction completion.",
    "For individual decisions use first, assign or remove fields and save. Bulk update uses from, where and nonempty set; it cannot target joined records or rely on projections, ordering or page counts to bound writes. Omitting its filter targets all base records.",
    "Named snippets use only their declared integer arguments, never caller-supplied expressions. A snippet is a field condition, not a method or projection function."
  ],
  "operators": [
    "eq",
    "neq",
    "gt",
    "gte",
    "lt",
    "lte",
    "nil",
    "nnil",
    "in",
    "nin",
    "btw",
    "nbtw",
    "like",
    "ilike",
    "overlap",
    "contains",
    "containedBy"
  ],
  "snippets": [
    "BeforeNow",
    "AfterNow",
    "ThisMinute",
    "ThisHour",
    "Today",
    "ThisWeek",
    "ThisMonth",
    "ThisYear",
    "LastNMinutes",
    "LastNHours",
    "LastNDays",
    "LastNWeeks",
    "LastNMonths",
    "LastNYears",
    "NamedHour",
    "NamedDayOfWeek",
    "NamedDay",
    "NamedMonth",
    "NamedYear",
    "StartOfDay",
    "EndOfDay",
    "LastMonth",
    "WeekToDate",
    "MonthToDate",
    "QuarterToDate",
    "YearToDate"
  ],
  "fieldFunctions": [
    "count",
    "ucount",
    "sum",
    "avg",
    "usum",
    "uavg",
    "min",
    "max",
    "std_dev",
    "std_dev_pop",
    "var_samp",
    "var_pop",
    "minute",
    "hour",
    "day",
    "week",
    "month",
    "quarter",
    "year"
  ],
  "conditions": "Scalar fields use bound equality; objects use an explicit op/value predicate. and/or recursively combine filters. nil/nnil test absence. in/nin require nonempty arrays; btw/nbtw require two typed bounds. Array containment requires a declared array field. Snippet arguments are validated integers, never arbitrary expressions.",
  "projection": "A Collection name may be followed by a parenthesized comma-separated field selection. A reference selection is its name followed by nested selected fields. field:function selects a supported expression and remains its output key. Reference paths use dot; object-property paths use slash. No SQL, AS, HAVING, raw expression or arbitrary function interface is exposed.",
  "aggregation": "All nonaggregate selected expressions belong in groupBy. Filtering precedes grouping; paging limits returned rows, not the underlying aggregation. Counts are integers. Other aggregates may be null. count counts matching source records, not groups.",
  "updates": "Use first, field changes/remove and save for individual record decisions. update is bulk-only in purpose; it cannot rely on projection, order or paging to bound changes. Joined target updates and joined-field filters are unsupported. Saving never creates a commit boundary.",
  "snippetSemantics": "Current periods use database calendar time. LastN selects previous N complete periods and excludes the current one. BeforeNow/AfterNow are strict. NamedHour selects an hour of today; NamedDayOfWeek starts Monday in the current week; NamedDay is in the current month; NamedMonth is in the current year. StartOfDay has no upper bound, EndOfDay no lower bound. LastMonth is the previous complete month. ToDate spans period start through now.",
  "functionTypes": {
    "count": {
      "input": "core.data",
      "result": "core.integer"
    },
    "ucount": {
      "input": "core.data",
      "result": "core.integer",
      "distinct": true
    },
    "sum": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.numeric"
      }
    },
    "avg": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      }
    },
    "usum": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.numeric"
      },
      "distinct": true
    },
    "uavg": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      },
      "distinct": true
    },
    "min": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.numeric"
      }
    },
    "max": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.numeric"
      }
    },
    "std_dev": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      }
    },
    "std_dev_pop": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      }
    },
    "var_samp": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      }
    },
    "var_pop": {
      "input": "core.numeric",
      "result": {
        "nullable": "core.decimal"
      }
    },
    "minute": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "hour": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "day": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "week": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "month": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "quarter": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    },
    "year": {
      "input": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "result": {
        "oneOf": [
          "core.date",
          "core.datetime"
        ]
      },
      "purpose": "Truncate to the start of the named period.",
      "constraints": [
        "The result has the same temporal type as its input field."
      ]
    }
  }
}

Search AISDL

Search the paper, references and contracts.