<commit_before># xo "booktest" examples

This directory contains the `xo` "booktest" examples which serve to demonstrate
an "end-to-end" use of `xo` from schema to practical use of "models" generated
by `xo` in a Go application. These examples are also used by the `xo`
developers to compare generated code for between databases and revisions.

Each subdirectory contains a single, practically identical example for each
supported database, demonstrating how to write and use a schema/custom query
for each, and the `xo` generated "models" for each. Each subdirectory contains
`<database>/schema.sql`, representing a basic "authors" and "books" schema
relationship, and in turn serving to demonstrate how how the various schema
column types correspond to their generated Go equivalents in the
`<database>/models/` directory.

Please note that the "booktest" examples are meant only to demonstrate how to
hit the ground running with `xo`, and are not meant to be an exhaustive
demonstration of `xo`'s feature support for each database.

## Examples

The following databases have a "booktest" example:

* [Microsoft SQL Server](mssql/)
* [MySQL](mysql/)
* [Oracle](oracle/)
* [PostgreSQL](postgres/)
* [SQLite3](sqlite3/)

Each database's directory contains the following:

| File                  | Description                                                     | Required |
|-----------------------|-----------------------------------------------------------------|----------|
| `schema.sql`          | database schema                                                 | yes      |
| `custom-query.xo.sql` | custom xo query                                                 | yes      |
| `models`              | output directory for generated code                             | yes      |
| `main.go`             | example Go code demonstrating use of generated code in `models` | yes      |
| `config`              | used by `gen.sh` to configure the database to use               | yes      |
| `skip`                | when present, `gen.sh` will skip the directory                  | no       |
| `pre`                 | when present, `gen.sh` will source the script                   | no       |

## Generating database models with gen.sh

This directory contains [`gen.sh`](gen.sh), a script that uses
[usql](https://github.com/knq/usql) to execute `<database>/schema.sql` against
the database URL found in `<database>/config`.

`gen.sh` then uses `xo` to generate each databases' `<database>/models`
directory, and then runs `go build -o booktest-<database> <database>/` to build
the example usage code for that database, and in turn executes the built
`booktest-<database>` executable.

Finally, `gen.sh` uses `usql` to display the rows from the "books" table.

### Running

`gen.sh` will try to use the `xo` executable in the root repository path,
otherwise `gen.sh` will attempt to use the `xo` executable on the system
`$PATH`. The `usql` executable must be available on `$PATH`.

If you have not already installed `usql`, you may install it in the usual Go
fashion:

```sh
$ go get -u github.com/knq/usql

# install with oracle support
$ go get -tags oracle -u github.com/knq/usql
```

### Skipping databases

If you would like `gen.sh` to skip a database, simply create a file named
`<database>/skip`:

```sh
# skip mysql
$ touch mysql/skip

# run gen.sh
$ ./gen.sh
```

### Configuring databases

Each database needs to be configured to run on `localhost` with its default
port. Additionally, each database needs a user named `booktest` with password
`<PASSWORD>`. With the exception of Oracle and SQLite3 databases, the `gen.sh`
script also expects the database to be named `booktest` and owned by the
`booktest` user.

Oracle will connect to the service name `xe.oracle.docker`, which is the
default service name used by the Docker [sath89/oracle-12c](https://hub.docker.com/r/sath89/oracle-12c/)
image. SQLite3 will use the regular file `booktest.sqlite3`.

If you would like to modify an individual database's configuration, please edit
the `<database>/config` and change the `DB` variable to suit your environment:

```sh
$ cat postgres/config<commit_msg>Finishing up documentation changes on examples/booktest/README.md<commit_after># xo "booktest" examples

This directory contains the `xo` "booktest" examples which serve to demonstrate
an "end-to-end" use of `xo` from schema to practical use of "models" generated
by `xo` in a Go application. These examples are also used by the `xo`
developers to compare generated code for/between databases and template
revisions.

Contained in this directory is a subdirectory for each supported `<database>`
by `xo`:

<center>

| Database (directory)                   |
|:--------------------------------------:|
| [Microsoft SQL Server (mssql)](mssql/) |
| [MySQL (mysql)](mysql/)                |
| [Oracle (oracle)](oracle/)             |
| [PostgreSQL (postgres)](postgres/)     |
| [SQLite3 (sqlite3)](sqlite3/)          |

</center>

Each `<database>` directory contains a single, practically identical example
for each supported database demonstrating how to write/use a schema and custom
query with `xo`, and the resulting generated "models" (available in
`<database>/models/`) for each.

Each subdirectory contains `<database>/schema.sql`, representing a basic
"authors" and "books" schema relationship, and `<database>/custom-query.xo.sql`,
demonstrating how to write a custom query for the database that can be
used with `xo.`

Please compare the `<database>/schema.sql` and the generated files in
`<database>/models/` to see how the schema column types map to their Go
equivalents as generated by `xo`.

Please note that the "booktest" examples are meant only to demonstrate how to
hit the ground running with `xo`, and are not meant to be an exhaustive
demonstration of `xo`'s feature support for each database.

## Generating database models with gen.sh

This directory contains [`gen.sh`](gen.sh), a script that uses
[usql](https://github.com/knq/usql) to execute `<database>/schema.sql` against
the database URL found in `<database>/config`.

`gen.sh` then uses `xo` to generate each databases' `<database>/models`
directory, and then runs `go build -o booktest-<database> <database>/` to build
the example usage code for that database, and in turn executes the built
`booktest-<database>` executable.

Finally, `gen.sh` uses `usql` to display the rows from the "books" table.

### Subdirectory layout

Each `<database>/` subdirectory contains the following:

<center>

| File                  | Description                                                     | Required |
|-----------------------|-----------------------------------------------------------------|----------|
| `schema.sql`          | database schema                                                 | yes      |
| `custom-query.xo.sql` | custom xo query                                                 | yes      |
| `models`              | output directory for generated code                             | yes      |
| `main.go`             | example Go code demonstrating use of generated code in `models` | yes      |
| `config`              | used by `gen.sh` to configure the database to use               | yes      |
| `skip`                | when present, `gen.sh` will skip the directory                  | no       |
| `pre`                 | when present, `gen.sh` will source the script                   | no       |

</center>

### Running gen.sh

`gen.sh` will try to use the `xo` executable in the root repository path,
otherwise `gen.sh` will attempt to use the `xo` executable on the system
`$PATH`. The `usql` executable must be available on `$PATH`.

If you have not already installed `usql`, you may install it in the usual Go
fashion:

```sh
$ go get -u github.com/knq/usql

# install with oracle support
$ go get -tags oracle -u github.com/knq/usql
```

### Skipping databases

When running `gen.sh`, and you need to skip one of the subdirectories simply
create a file named `<database>/skip` to tell `gen.sh` to skip that database:

```sh
# skip mysql
$ touch mysql/skip

# run gen.sh
$ ./gen.sh
```

### Configuring databases

Each database needs to be configured to listen on `localhost` with its default
port. Additionally, each database needs a user named `booktest` with password
`<PASSWORD>`. With the exception of Oracle and SQLite3 databases, the `gen.sh`
script also expects the database to be named `booktest` and owned by the
`booktest` user.

Oracle will connect to the service name `xe.oracle.docker`, which is the
default service name used by the Docker [sath89/oracle-12c](https://hub.docker.com/r/sath89/oracle-12c/)
image. SQLite3 will use the regular file `booktest.sqlite3`.

If you need to modify an individual database's configuration, you can change
the `DB` variable in `<database>/config` in order to suit your environment:

```sh
$ cat postgres/config
{'fraction_non_alphanumeric': 0.1190742397137746, 'fraction_numerical': 0.003018783542039356, 'mean_word_length': 2.6810699588477367, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '212603', 'n_tokens_mistral': 2437, 'n_tokens_neox': 2319, 'n_words': 1058}