Maksim Kaitmazian authored 1 year ago and Dmitry Ivanov committed 10 months ago

The backend module implements the logic for handling postgres messages.
Note that this commit doesn't bring anything new: it just moves picodata's
handlers into pgproto.

Arseniy Volynets authored 11 months ago and Denis Smirnov committed 10 months ago

- update sbroad submodule to commit
with support of UNION operator
- add integration tests for global
tables (sharded tables were tested
in cartridge tests)
- Usage:

`select a from t union select b from t2`

Fedor Telnov authored 11 months ago and Denis Smirnov committed 10 months ago

It syncs API with new sbroad's one.
For more context refer to sbroad!424

Arseniy Volynets authored 11 months ago and Denis Smirnov committed 11 months ago

- update sbroad submodule with support of
datetime type and new `to_date` builtin
function.
- initialize sbroad builtin functions in
init common. Currently there is only one
builtin function `to_date`
- add integration tests for datetime types
- update tarantool module to commit with
fixes for datetime deserialization

We have recently replaced String with SmolStr in sbroad.
As a result, we need to make corresponding changes in
picodata as it uses sbroad-core for cluster SQL.

BREAKING CHANGE: "name" index in the _pico_index has changed:
previously it guarantees uniqueness for a pair of (space_id,
index_name). Now index name must be unique among the cluster.
So, all the global indexes for _pico tables were renamed.

This commit introduces:
 * statement and portal storages;
 * statements with parameter oids;
 * portal encoding;
 * interactive portals;

Portal encoding defines in which format (binary or text) columns
are returned.

Interactive portals allow to limit the number of rows returned from
execute method. Other rows can be extracted by calling execute again
on the same portal.

- add new ddl opcode for renaming procedure
- update sbroad submodule to get sql support
- syntax:

alter procedure "foo" rename to "bar" option(timeout=3)
alter procedure "foo"(int, int) rename to "bar"

- Update sbroad submodule to commit,
with refactored tracing api: tracer
can be provided by library user (us).
- Use tracer that creates traces under
admin, because traces are created randomly
for 1% of the queries and the user may not
have the privileges to create/modify tracing
tables
- Move tracing related logic to picodata repo.
In particular, now all tables storing tracing
- Add sql tests that were removed from sbroad
during refactoring
- added extra parameter 'query_id' to pico.sql
lua api. 'query_id' is used to identify
each query in tracing tables, if not specified
hash function on query text is used.
- changed signature of pico.sql to:
pico.sql(pattern[, params, options)
options={'traceable'=boolean,'query_id'=string}

Current commit introduces
- _pico_routine replicated table
- procedure creation with SQL API

- update sbroad submodule to commit with
support of except for global tables
- add integration tests for except with
global tbls

- update sbroad submodule to commit
supporting union all with global tbls
- add integration tests

Dmitry Ivanov authored 1 year ago and Yaroslav Dynnikov committed 1 year ago

Implemented:

- features:
    * FFI wrappers for `say.{h,c}`
    * safe logger object on top of FFI definitions
    * unified tlog/audit definitions for formatting
    * fixed a needless allocation in tlog (new root per each line)

- events:
    * create_user
    * drop_user
    * create_role
    * drop_role
    * change_password
    * grant_privilege
    * revoke_privilege
    * create_table
    * drop_table
    * grant_role
    * revoke_role

Todo:

- features:
    * unique id generation
    * security subject (who executed the action)
    * audit configuration (via `pico.audit` function)

- well-defined events:
    * create_database (universe)
    * change_config
    * change_current_grade

- special events (local to each node):
    * auth_ok / auth_fail
    * access_denied (still not sure if we absolutely need that)
    * audit_rotate

- Unavailable (haven't been defined yet):
    * rename_user
    * create_procedure
    * drop_procedure

Example:

```
$ cat /tmp/audit.log
2023-11-10 12:07:19.457 [15735] main/103/interactive I> audit log is ready, title: initialize_audit
2023-11-10 12:07:19.457 [15735] main/103/interactive I> database is starting, title: startup
2023-11-10 12:07:19.818 [15741] main/103/interactive I> audit log is ready, title: initialize_audit
2023-11-10 12:07:19.818 [15741] main/103/interactive I> database is starting, title: startup
2023-11-10 12:07:34.910 [15741] main/111/raft_main_loop storage.rs:2809 W> created user `KEK`, auth_type: chap-sha1, title: create_user
2023-11-10 12:07:52.869 [15741] main/111/raft_main_loop storage.rs:2861 W> created role `mgr`, title: create_role
2023-11-10 12:07:55.602 [15741] main/111/raft_main_loop storage.rs:2861 W> created role `kek`, title: create_role
2023-11-10 12:07:57.594 [15741] main/111/raft_main_loop storage.rs:2906 W> granted role `mgr` to role `kek`, title: grant_role
2023-11-10 12:08:08.482 [15741] main/111/raft_main_loop storage.rs:2913 W> granted privilege execute on universe `` to role `kek`, title: grant_privilege
```

BREAKING CHANGE!:
- pico.trace() function was removed. Use pico.sql() for tracing;
- opentelemetry tables __SBROAD_STAT and __SBROAD_QUERY were renamed
  into _SQL_STAT and _SQL_QUERY tables.

Introduce enrypoints for extended PG protocol:

- pg_bind: bind parameters to portal (IR) by descriptor;
- pg_close: remove portal from the storage by descriptor;
- pg_describe: get the portal metadata and type by descriptor;
- pg_execute: execute portal by descriptor;
- pg_parse: parse an SQL pattern into IR (reusing LRU),
  save IR into portal storage and return the descriptor;
- pg_portals: get a list of portal descriptors visible
  to the current user;

Co-authored-by: Kaitmazian Maksim <m.kaitmazian@picodata.io>

feat: add tests for tables format and parameters len, fix bucket_id calculation, add support of is_nullable sql metadata

Previous version of the SQL module hierarchy was overcomplicated.
Current commit simplifies it, so SQL has only two submodules:
router and storage.

The reason of this refactoring is that we need Picodata specific
API rather then the API provided by tarantool module. For example,
table creation needs access to the clusterwide API for sharded and
global spaces.

Denis Smirnov authored 1 year ago and Yaroslav Dynnikov committed 1 year ago

INSERT command in the SQL library (sbroad) was refactored. Now
we don't need a custom SQL function to calculate bucket ids. So,
remove redundant functions from picodata SQL module.

Now it is possible to make the second argument (query parameters)
optional. I.e. now we support 'pico.sql([[select * from t]])' as
well as 'pico.sql([[select * from t where a = ?]], {1})'.