/docs/about/compatibility.md
https://github.com/tathougies/beam · Markdown · 423 lines · 418 code · 5 blank · 0 comment · 0 complexity · f7b8664f958dcdf1a716efa4a442b0c5 MD5 · raw file
Large files are truncated click here to view the full file
- Beam strives to cover the full breadth of the relevant SQL
- standards. In general, if there is something in a SQL standard that is
- not implemented in a generic manner in `beam-core`, feel free to file
- an issue requesting support. There are some features that beam
- purposefully omits because no major RDBMS implements them. For
- example, database-level assertions are not supported in any of the
- default beam backends, and thus are not supported by `beam-core`. If
- you have a need for these features, feel free to file an issue. Be
- sure to motivate your use case with examples and a testing strategy.
- The relevant SQL standards are SQL-92, SQL:1999, SQL:2003, SQL:2008,
- and SQL:2011. Because not all the standards are not publicly
- accessible, I've done my best to piece together features from various
- documents available online. I believe I've covered most of the common
- cases, but there may be pieces of functionality that are missing. File
- an issue if this is the case.
- The table below summarizes the features defined in each SQL standard and beam's
- support for them. FULL means beam supports everything in that feature. NONE
- means that there is no support for that feature, and none planned. N/A means
- that the feature only applies to RDBMSs, not the SQL language. WONTFIX means
- that the feature has been considered and willfully ignored. UNKNOWN means not
- enough investigation has gone into the feature to make a determination. TODO
- means the feature has not been implemented yet, but an implementation is
- planned.
- !!! tip "Tip"
- The 'TODO' items are a great way to contribute to beam!
- | Feature | Status | Notes |
- | :-------------------------------------------------------------- | :------: | :---------------------------------------------------------------------------------------- |
- | **B011 Embedded Ada** | NONE | |
- | **B012 Embedded C** | NONE | |
- | **B013 Embedded COBOL** | NONE | |
- | **B014 Embedded FORTRAN** | NONE | |
- | **B015 Embedded MUMPS** | NONE | |
- | **B016 Embedded Pascal** | NONE | |
- | **B017 Embedded PL/I** | NONE | |
- | **B021 Direct SQL** | NONE | |
- | **B031 Basic dynamic SQL** | NONE | |
- | **B032 Extended dynamic SQL** | NONE | |
- | **B033 Untyped SQL-invoked function arguments** | NONE | |
- | **B034 Dynamic specification of cursor attributes** | NONE | |
- | **B035 Non-extended descriptor names** | NONE | |
- | **B051 Enhanced execution rights** | NONE | |
- | **B111 Module language Ada** | NONE | |
- | **B112 Module language C** | NONE | |
- | **B113 Module language COBOL** | NONE | |
- | **B114 Module language Fortran** | NONE | |
- | **B115 Module language MUMPS** | NONE | |
- | **B116 Module language Pascal** | NONE | |
- | **B117 Module language PL/I** | NONE | |
- | **B121 Routine language Ada** | NONE | |
- | **B122 Routine language C** | NONE | |
- | **B123 Routine language COBOL** | NONE | |
- | **B124 Routine language Fortran** | NONE | |
- | **B125 Routine language MUMPS** | NONE | |
- | **B126 Routine language Pascal** | NONE | |
- | **B127 Routine language PL/I** | NONE | |
- | **B128 Routine language SQL** | NONE | |
- | **B211 Module language Ada: VARCHAR and NUMERIC support** | NONE | |
- | **B221 Routine language Ada: VARCHAR and NUMERIC support** | NONE | |
- | **E011 - Numeric data types** | | |
- | E011-01 INTEGER and SMALLINT data types | FULL | Use `Int32` for `INTEGER`, `Int16` for `SMALLINT` |
- | E011-02 REAL, DOUBLE PRECISION, FLOAT | FULL | Use `Double` and `Float` |
- | E011-03 DECIMAL and NUMERIC data types | FULL | Use `Scientific`. You can provide the database precision using `beam-migrate` |
- | E011-04 Arithmetic operators | FULL | Use the `Num` instance for `QGenExpr` |
- | E011-05 Numeric comparison | FULL | Use the `.` suffixed operators (i.e., `==.`, `/=.`, `<.`, etc) |
- | E011-06 Implicit casting among numeric data types | WONTFIX | Beam never implicitly casts. Use `cast_` |
- | **E021 Character string types** | | |
- | E021-01 CHARACTER data type | FULL | Use `Text`. Use `beam-migrate` to specify width |
- | E021-02 CHARACTER VARYING data type | FULL | Use `Text`. Use `beam-migrate` to specify width. |
- | E021-03 Character literals | FULL | Use `val_` |
- | E021-04 CHARACTER\_LENGTH function | FULL | Use `charLength_` |
- | E021-05 OCTET\_LENGTH function | FULL | Use `octetLength_` |
- | E021-06 SUBSTRING function | TODO | |
- | E021-07 Character concatenation | FULL | Use `concat_` |
- | E021-08 UPPER and LOWER functions | FULL | Use `upper_` and `lower_` |
- | E021-09 TRIM function | PARTIAL | Use `trim_`. Full support may be provided on backends that implement it |
- | E021-10 Implicit casting among string types | WONTFIX | Beam never implicitly casts. Use `cast_` |
- | E021-11 POSITION function | FULL | Use `position_` |
- | E021-12 Character comparison | FULL | Use comparison operators (See E011-05) |
- | **E031 Identifiers** | | |
- | E031-01 Delimited identifiers | TODO | Find out more |
- | E021-02 Lower case identifiers | TODO | |
- | E021-03 Trailing underscore | N/A | Beam will use whatever column names you specify |
- | **E051 Basic query specification** | | |
- | E051-01 SELECT DISTINCT | FULL | Use `nub_` |
- | E051-02 GROUP BY clause | FULL | See `aggregate_` or read the [section on aggregates](../user-guide/queries/aggregates.md) |
- | E051-04 GROUP BY can contain columns not in SELECT | TODO | Unsure how this applies to beam in particular |
- | E051-05 Select list items can be renamed | N/A | Beam uses this feature internally, the user never needs it |
- | E051-06 HAVING clause | FULL | `guard_` and `filter_` are appropriately converted to `HAVING` when allowed |
- | E051-07 Qualified * in select list | N/A | Beam handles projections instead |
- | E051-08 Correlation names in FROM | TODO | Unsure how this applies to beam |
- | E051-09 Rename columns in the FROM clause | NONE | Beam doesn't need this |
- | **E061 Basic predicates and search conditions** | | |
- | E061-01 Comparison predicate | FULL | Use the comparison operators (see E011-05) |
- | E061-02 BETWEEN predicate | FULL | Use `between_` |
- | E061-03 IN predicate with list of values | FULL | Use `in_` |
- | E061-04 LIKE predicate | FULL | Use `like_` |
- | E061-05 LIKE predicate ESCAPE clause | TODO | Unsure how this would apply |
- | E061-06 NULL predicate | FULL | Use `isNothing_` and `isJust_` |
- | E061-07 Quantified comparison predicate | FULL | Use one of the *quantified comparison operators* (`==*.`, `/=*.`, `<*.`, `>*.`, `<=*.`, `>=*.`) |
- | E051-08 EXISTS predicate | FULL | Use `exists_` |
- | E061-09 Subqueries in comparison predicate | FULL | Use `subquery_` as usual |
- | E061-11 Subqueries in IN predicate | FULL | |
- | E061-12 Subqueries in quantified comparison predicate | FULL | |
- | E061-13 Correlated subqueries | FULL | Use `subquery_` |
- | E061-14 Search condition | FULL | Construct `QGenExprs` with type `Bool` |
- | **E071 Basic query expressions** | | |
- | E071-01 UNION DISTINCT table operator | FULL | Use `union_` |
- | E071-02 UNION ALL table operator | FULL | Use `unionAll_` |
- | E071-03 EXCEPT DISTINCT table operator | FULL | Use `except_` |
- | E071-05 Columns combined via operators need not have same type | WONTFIX | Beam is strongly typed |
- | E071-06 Table operators in subqueries | FULL | Supported for backends that support it |
- | **E081 Basic privileges** | NONE | Database security is not beam's focus. `beam-migrate` may expose this in the future |
- | **E091 Set functions** | | |
- | E091-01 AVG | FULL | Use `avg_` or `avgOver_` |
- | E091-02 COUNT | FULL | Use `countAll_`, `countAllOver_`, `count_`, or `countOver_` |
- | E091-03 MAX | FULL | Use `max_` or `maxOver_` |
- | E091-04 MIN | FULL | Use `min_` or `minOver_` |
- | E091-05 SUM | FULL | Use `sum_` or `sumOver_` |
- | E091-06 ALL quantifier | FULL | Use the `*Over_` functions with the `allInGroupExplicitly_` quantifier |
- | E091-07 DISTINCT quantifier | FULL | Use the `*Over_` functions with the `distinctInGroup_` quantifier |
- | **E101 Basic data manipulation** | | |
- | E101-01 INSERT statement | FULL | Use `insert` and `SqlInsert` |
- | E101-03 Searched UPDATE | FULL | Use `update` and `SqlUpdate` |
- | E101-04 Searched DELETE | FULL | Use `delete` and `SqlDelete` |
- | **E111 Single row SELECT statement** | FULL | Use `select` as expected |
- | **E121 Basic cursor support** | NONE | Use the backends explicitly |
- | **E131 Null value support** | PARTIAL | Use `Maybe` column types, `Nullable`, and the `just_`, `nothing_`, and `maybe_` functions |
- | **E141 Basic integrity constraints** | | Implemented in `beam-migrate` |
- | E141-01 NOT NULL constraints | FULL | Use `notNull_` |
- | E141-02 UNIQUE constraints of NOT NULL columns | TODO | |
- | E141-03 PRIMARY KEY constraints | FULL | Instantiate `Table` with the correct `PrimaryKey` |
- | E141-04 Basic FOREIGN KEY constraints | TODO | You can embed the `PrimaryKey` of the relation directly. |
- | E141-06 CHECK constraints | TODO | |
- | E141-07 Column defaults | FULL | Use `default_` from `beam-migrate` |
- | E141-08 NOT NULL inferred on PRIMARY KEY | N/A | |
- | E141-10 Names in a foreign key can be specified in any order | N/A | |
- | **E151 Transaction support** | None | Use the backend functions explicitly |
- | **E152 SET TRANSACTION statement** | N/A | |
- | **E153 Updatable queries with subqueries** | TODO | Not a common feature, but would be trivial to support |
- | **E161 SQL comments with double minus** | N/A | |
- | **E171 SQLSTATE support** | N/A | |
- | **E182 Host language binding** | N/A | |
- | **F031 Basic schema manipulation** | | |
- | F031-01 CREATE TABLE for persistent base tables | FULL | Use `createTable_` in `beam-migrate` |
- | F031-02 CREATE VIEW statement | TODO | |
- | F031-03 GRANT statement | TODO | |
- | F031-04 ALTER TABLE statement: ADD COLUMN clause | TODO | |
- | F031-13 DROP TABLE statement: RESTRICT clause | TODO | |
- | F031-16 DROP VIEW statement: RESTRICT clause | TODO | |
- | F031-19 REVOKE statement: RESTRICT clause | NONE | See note for E081 |
- | **F032 CASCADE drop behavior** | TODO | Would be in `beam-migrate` |
- | **F033 ALTER TABLE statement: DROP COLUMN clause** | TODO | |
- | **F034 Extended REVOKE statement** | NONE | |
- | **F041 Basic joined table** | | |
- | F041-01 Inner join | FULL | Use the [monadic join interface](../user-guide/queries/relationships.md) |
- | F041-02 INNER keyword | N/A | The `INNER` keyword is just syntactic sugar. The regular joins do what you want. |
- | F041-03 LEFT OUTER JOIN | FULL | Use `leftJoin_` |
- | F041-04 RIGHT OUTER JOIN | PARTIAL | Supported in backend syntaxes, not exposed. Can always be written using LEFT OUTER JOIN |
- | F041-05 Outer joins can be nested | FULL | `outerJoin_` can be nested arbitrarily |
- | F041-07 The inner table in outer join can be used in inner join | TODO | How does this apply to us? |
- | F041-08 All comparison operators in JOIN | FULL | Arbitrary `QGenExpr`s are supported. |
- | **F051 Basic date and time** | | |
- | F051-01 DATE data type | FULL | Use `Day` from `Data.Time` and `val_` |
- | F051-02 TIME data type | FULL | Use `TimeOfDay` from `Data.Time` and `val_` |
- | F051-03 TIMESTAMP datatype | FULL | Use `LocalTime` from `Data.Time` and `val_`. Precision can be specified in `beam-migrate` |
- | F051-04 Comparison predicate on time types | FULL | Use comparison operatiors (See E011-05) |
- | F051-05 Explicit cast between date-time types and string | TODO | |
- | F051-06 CURRENT\_DATE | TODO | |
- | F051-07 LOCALTIME | TODO | |
- | F051-08 LOCALTIMESTAMP | TODO | |
- | **F081 UNION and EXCEPT in views** | FULL | Views can use any query |
- | **F111 Isolation levels other than SERIALIZABLE** | NONE | Use backends |
- | **F121 Basic diagnostics mangement** | NONE | Use backends |
- | **F122 Extended diagnostics management** | NONE | Use backends |
- | **F123 All diagnostics** | NONE | Use backends |
- | **F131 Grouped operations** | TODO | Depends on grouped views |
- | **F171 Multiple schemas per user** | N/A | Depends on backend |
- | **F191 Referential delete actions** | TODO | |
- | **F181 Multiple module support** | N/A | |
- | **F200 TRUNCATE TABLE statement** | TODO | May be added in the future |
- | **F201 CAST function** | FULL | See `cast_` |
- | **F202 TRUNCATE TABLE: identity column restart option** | TODO | Depends on F200 |
- | **F221 Explicit defaults** | FULL | Use `default_` and `insertExpressions` when inserting |
- | **F222 INSERT statement: DEFAULT VALUES clause** | TODO | |
- | **F251 Domain support** | PARTIAL | Use `DomainTypeEntity` |
- | **F261 CASE expression** | | |
- | F261-01 Simple CASE | TODO | Use searched case (see F261-02) |
- | F261-02 Searched CASE | FULL | Use `if_`, `then_`, and `else_` |
- | F261-03 NULLIF | FULL | Use `nullIf_` |
- | F261-04 COALESCE | FULL | Use `coalesce_` |
- | **F262 Extended CASE expression** | WONTFIX | Beam allows any expression in a `WHEN` condition |
- | **F263 Comma-separater predicates in simple CASE expression** | WONTFIX | Unnecessary |
- | **F271 Compound character literals** | N/A | This is syntactic sugar |
- | **F281 LIKE enhancements** | FULL | Supported in backends that support this |
- | **F291 UNIQUE predicate** | FULL | Use `unique_` |
- | **F301 CORRESPONDING in query expressions** | N/A | Beam set functions work based off the query result type, not the column name |
- | **F302 INTERSECT table operator** | FULL | Use `intersect_` |
- | F302-01 INTERSECT DISTINCT table operator | FULL | Use `intersect_` |
- | F302-02 INTERSET ALL table operator | FULL | Use `intersectAll_` |
- | **F304 EXCEPT ALL table operator** | FULL | Use `exceptAll_` |
- | **F311 Schema definition statement** | TODO | Would be in `beam-migrate` |
- | **F312 MERGE statement** | TODO | |
- | **F313 Enhanced MERGE statement** | TODO | |
- | **F314 MERGE statement with DELETE branch** | TODO | |
- | **F321 User authorization** | N/A | |
- | **F361 Subprogram support** | N/A | |
- | **F381 Extended schema manipulation** | TODO | |
- | **F382 Alter column data type** | TODO | |
- | **F384 Drop identity property clause** | TODO | |
- | **F385 Drop column generation expression clause** | TODO | |
- | **F386 Set identity column generation clause** | TODO | |
- | **F391 Long identifiers** | FULL | Supported in backends that support it |
- | **F392 Unicode escapes in identifiers** | TODO | Unsure how this applies |
- | **F393 Unicode escapes in literals** | TODO | Unsure how this applies |
- | **F394 Optional normal form specification** | N/A | |
- | **F401 Extended joined table** | FULL | Full outer join using `outerJoin_`. Natural join is not needed. A cross join is generated automatically when there are no join conditions. |
- | **F402 Named column joins for LOBs, arrays, and multisets** | PARTIAL | Supported in backends that support it |
- | **F403 Partitioned join tables** | TODO | |
- | **F411 Time zone specification** | TODO | |
- | **F421 National character** | FULL | Supported in `beam-migrate` as a data type for `Text` |
- | **F431 Read-only scrollable cursors** | N/A | Use the underlying backend |
- | **F441 Extended set function support** | TODO | |
- | **F442 Mixed column references in set functions** | TODO | Unsure how this would work with beam |
- | **F451 Character set definition** | TODO | Likely would go in `beam-migrate` |
- | **F461 Named character sets** | TODO | See F451 |
- | **F491 Constraint management** | TODO | |
- | **F492 Optional table constraint enforcement** | TODO | |
- | **F521 Assertions** | TODO | |
- | **F531 Temporary tables** | TODO | |
- | **F481 Expanded NULL predicate** | FULL | Supported in backends that support it |
- | **F555 Enhanced seconds precision** | TODO | |
- | **F561 Full value expressions** | TODO | |
- | **F571 Truth value tests** | TODO | |
- | **F591 Derived tables** | TODO | |
- | **F611 Indicator data types** | TODO | |
- | **F641 Row and table constructors** | PARTIAL | Use `row_` (TODO) |
- | **F651 Catalog name qualifiers** | TODO | |
- | **F661 Simple tables** | TODO | |
- | **F671 Subqueries in CHECK constraints** | TODO | Planned with E141-06 |
- | **F672 Retrospective CHECK constraints** | TODO | Would require temporal DB support |
- | **F690 Collation support** | PARTIAL | `beam-migrate` supports some collation features |
- | **F692 Enhanced collation support** | TODO | |
- | **F693 SQL-session and client module collations** | TODO | |
- | **F695 Translation support** | TODO | |
- | **F701 Referential update actions** | TODO | |
- | **F7…