
# asciiplot

asciiplot is a toy library for producing ASCII scatterplots from PostgreSQL queries.
Please note that it is not indended for serious use.

### Usage

```sql
select
  scatter(
    val::numeric, --x
    val::numeric, -- y
    'stonks!', -- title
    15, -- height
    50 --width
  )
from
  generate_series(1,10) vals(val)

/*
                   stonks!
----------------------------------------------
|                                       *
|
|                                  *
|                              *
|
|                          *
|
|                     *
|                 *
|
|            *
|
|        *
|   *
*/
```




# index_advisor

`index_advisor` is an extension for recommending indexes to improve query performance.

## Installation

Note:

`hypopg` is a dependency of index_advisor.
Dependency resolution is currently under development.
In the future it will not be necessary to manually create dependencies.


```sql
select dbdev.install('olirice@index_advisor');
create extension if not exists hypopg;
create extension "olirice@index_advisor" version '0.2.0';
```

Features:
- Supports generic parameters e.g. `$1`, `$2`
- Supports materialized views
- Identifies tables/columns obfuscaed by views


## API

#### Description
For a given *query*, searches for a set of SQL DDL `create index` statements that improve the query's execution time;

#### Signature
```sql
index_advisor(query text)
returns
    table  (
        startup_cost_before jsonb,
        startup_cost_after jsonb,
        total_cost_before jsonb,
        total_cost_after jsonb,
        index_statements text[],
        errors text[]
    )
```

## Usage

For a minimal example, the `index_advisor` function can be given a single table query with a filter on an unindexed column.

```sql
create extension if not exists index_advisor cascade;

create table book(
  id int primary key,
  title text not null
);

select
    *
from
  index_advisor('select book.id from book where title = $1');

 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                   | errors
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},| {}
(1 row)
```

More complex queries may generate additional suggested indexes

```sql
create extension if not exists index_advisor cascade;

create table author(
    id serial primary key,
    name text not null
);

create table publisher(
    id serial primary key,
    name text not null,
    corporate_address text
);

create table book(
    id serial primary key,
    author_id int not null references author(id),
    publisher_id int not null references publisher(id),
    title text
);

create table review(
    id serial primary key,
    book_id int references book(id),
    body text not null
);

select
    *
from
    index_advisor('
        select
            book.id,
            book.title,
            publisher.name as publisher_name,
            author.name as author_name,
            review.body review_body
        from
            book
            join publisher
                on book.publisher_id = publisher.id
            join author
                on book.author_id = author.id
            join review
                on book.id = review.book_id
        where
            author.id = $1
            and publisher.id = $2
    ');

 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                         | errors
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+--------
 27.26               | 12.77              | 68.48             | 42.37            | {"CREATE INDEX ON public.book USING btree (author_id)",   | {}
                                                                                    "CREATE INDEX ON public.book USING btree (publisher_id)",
                                                                                    "CREATE INDEX ON public.review USING btree (book_id)"}
(3 rows)
```




# read_once

A Supabase application for sending messages that can only be read once

Features:
- messages can only be read one time
- messages are not logged in PostgreSQL write-ahead-log (WAL)

## Installation

`pg_cron` is a dependency of `read_once`.
Dependency resolution is currently under development.
In the near future it will not be necessary to manually create dependencies.

To expose the `send_message` and `read_message` functions over HTTP, install the extension in a schema that is on the search_path.


For example:
```sql
select dbdev.install('olirice@read_once');
create extension if not exists pg_cron;
create extension "olirice@read_once"
    schema public
    version '0.3.1';
```


## HTTP API

### Create a Message

```sh
curl -X POST https://<PROJECT_REF>.supabase.co/rest/v1/rpc/send_message \
    -H 'apiKey: <API_KEY>' \
    -H 'Content-Type: application/json'
    --data-raw '{"contents": "hello, dbdev!"}

# Returns: "2989156b-2356-4543-9d1b-19dfb8ec3268"
```

### Read a Message

```sh
curl -X https://<PROJECT_REF>.supabase.co/rest/v1/rpc/read_message
    -H 'apiKey: <API_KEY>' \
    -H 'Content-Type: application/json' \
    --data-raw '{"id": "2989156b-2356-4543-9d1b-19dfb8ec3268"}

# Returns: "hello, dbdev!"
```


## SQL API

### Create a Message

```sql
-- Creates a new messages and returns its unique id
create or replace function send_message(
    contents text
)
    returns uuid
```

### Read a Message

```sql
-- Read a message by its id
create or replace function read_message(
    id uuid
)
    returns text
```


olirice

Packages