booktest/

README

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:

Database (directory)
Microsoft SQL Server (mssql)
MySQL (mysql)
Oracle (oracle)
PostgreSQL (postgres)
SQLite3 (sqlite3)

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, a script that uses 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:

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

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:

$ 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:

# 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 booktest. 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 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:

$ cat postgres/config
DB=postgres://booktest:booktest@localhost/booktest

$ echo 'postgres://myuser:mypass@remotehost:1112/database?sslmode=disable' > postgres/config

Please see the contrib/ directory for helper scripts that can assist with creating the various databases.

Passing options to gen.sh

gen.sh will pass any additional command-line options to usql, xo, and the built booktest-<database> executable. At the moment, the most useful option to pass is -v, which will enable verbose output for xo, usql and the built booktest-<database> executable:

$ ./gen.sh -v

Example Output

The following is the example non-verbose output from gen.sh:

$ gen.sh
------------------------------------------------------
mssql='mssql://booktest:booktest@localhost/booktest'

usql mssql://booktest:booktest@localhost/booktest -f mssql/schema.sql
DROP
DROP
CREATE
CREATE
CREATE
CREATE

xo mssql://booktest:booktest@localhost/booktest -o mssql/models

xo mssql://booktest:booktest@localhost/booktest -o mssql/models < mssql/custom-query.xo.sql

go build -o booktest-mssql ./mssql/

./booktest-mssql
Book 1: my book title available: 06 Mar 17 13:28 +0000
Book 1 author: Unknown Master
---------
Tag search results:
Book 2: 'changed second title', Author: 'Unknown Master', ISBN: '2' Tags: 'cool disastor'
Book 3: 'the third book', Author: 'Unknown Master', ISBN: '3' Tags: 'cool'

select * from books;
  book_id | author_id | isbn |        title         | year |          available           |     tags
+---------+-----------+------+----------------------+------+------------------------------+---------------+
        1 |         1 |    1 | my book title        | 2016 | 2017-03-06T13:28:59.6196184Z |
        2 |         1 |    2 | changed second title | 2016 | 2017-03-06T13:28:59.6196184Z | cool disastor
        3 |         1 |    3 | the third book       | 2001 | 2017-03-06T13:28:59.6196184Z | cool
(3 rows)


------------------------------------------------------
mysql='mysql://booktest:booktest@localhost/booktest?parseTime=true&sql_mode=ansi'

usql mysql://booktest:booktest@localhost/booktest?parseTime=true&sql_mode=ansi -f mysql/schema.sql
SET
DROP
DROP
DROP
SET
CREATE
CREATE
CREATE
CREATE
CREATE

xo mysql://booktest:booktest@localhost/booktest?parseTime=true&sql_mode=ansi -o mysql/models

xo mysql://booktest:booktest@localhost/booktest?parseTime=true&sql_mode=ansi -o mysql/models < mysql/custom-query.xo.sql

go build -o booktest-mysql ./mysql/

./booktest-mysql
Book 1 (FICTION): my book title available: 06 Mar 17 06:29 +0000
Book 1 author: Unknown Master
---------
Tag search results:
Book 2: 'changed second title', Author: 'Unknown Master', ISBN: '2' Tags: 'cool disastor'
Book 3: 'the third book', Author: 'Unknown Master', ISBN: '3' Tags: 'cool'
SayHello response: hello john

select * from books;
  book_id | author_id | isbn | book_type |        title         | year |         available         |     tags
+---------+-----------+------+-----------+----------------------+------+---------------------------+---------------+
        1 |         1 |    1 | FICTION   | my book title        | 2016 | 2017-03-06T06:29:01+07:00 |
        2 |         1 |    2 | FICTION   | changed second title | 2016 | 2017-03-06T06:29:01+07:00 | cool disastor
        3 |         1 |    3 | FICTION   | the third book       | 2001 | 2017-03-06T06:29:01+07:00 | cool
(3 rows)


------------------------------------------------------
oracle='oracle://booktest:booktest@localhost/xe.oracle.docker'

sourcing oracle/pre
DROP
DROP
DROP
DROP

usql oracle://booktest:booktest@localhost/xe.oracle.docker -f oracle/schema.sql
CREATE
CREATE
CREATE
CREATE

xo oracle://booktest:booktest@localhost/xe.oracle.docker -o oracle/models

xo oracle://booktest:booktest@localhost/xe.oracle.docker -o oracle/models < oracle/custom-query.xo.sql

go build -o booktest-oracle ./oracle/

./booktest-oracle
Book 1: my book title available: 06 Mar 17 13:29 +0700
Book 1 author: Unknown Master
---------
Tag search results:
Book 2: 'changed second title', Author: 'Unknown Master', ISBN: '2' Tags: 'cool disastor'
Book 3: 'the third book', Author: 'Unknown Master', ISBN: '3' Tags: 'cool'

select * from books;
  book_id | author_id | isbn |        title         | year |            available             |     tags
+---------+-----------+------+----------------------+------+----------------------------------+---------------+
        1 |         1 |    1 | my book title        | 2016 | 2017-03-06T13:29:04.239998+07:00 | empty
        2 |         1 |    2 | changed second title | 2016 | 2017-03-06T13:29:04.239998+07:00 | cool disastor
        3 |         1 |    3 | the third book       | 2001 | 2017-03-06T13:29:04.239998+07:00 | cool
(3 rows)


------------------------------------------------------
postgres='postgres://booktest:booktest@localhost/booktest'

usql postgres://booktest:booktest@localhost/booktest -f postgres/schema.sql
DROP
DROP
DROP
DROP
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE

xo postgres://booktest:booktest@localhost/booktest -o postgres/models

xo postgres://booktest:booktest@localhost/booktest -o postgres/models < postgres/custom-query.xo.sql

go build -o booktest-postgres ./postgres/

./booktest-postgres
Book 1 (FICTION): my book title available: 06 Mar 17 13:29 +0700
Book 1 author: Unknown Master
---------
Tag search results:
Book 4: 'never ever gonna finish, a quatrain', Author: 'Unknown Master', ISBN: 'NEW ISBN' Tags: '[someother]'
Book 3: 'the third book', Author: 'Unknown Master', ISBN: '3' Tags: '[cool]'
Book 2: 'changed second title', Author: 'Unknown Master', ISBN: '2' Tags: '[cool disastor]'
SayHello response: hello john

select * from books;
  book_id | author_id | isbn | booktype |        title         | year |            available             |      tags
+---------+-----------+------+----------+----------------------+------+----------------------------------+-----------------+
        1 |         1 |    1 | FICTION  | my book title        | 2016 | 2017-03-06T13:29:05.512355+07:00 | {}
        2 |         1 |    2 | FICTION  | changed second title | 2016 | 2017-03-06T13:29:05.512355+07:00 | {cool,disastor}
        3 |         1 |    3 | FICTION  | the third book       | 2001 | 2017-03-06T13:29:05.512355+07:00 | {cool}
(3 rows)


------------------------------------------------------
sqlite3='file:booktest.sqlite3?loc=auto'

usql file:booktest.sqlite3?loc=auto -f sqlite3/schema.sql
PRAGMA
DROP
DROP
CREATE
CREATE
CREATE
CREATE

xo file:booktest.sqlite3?loc=auto -o sqlite3/models

xo file:booktest.sqlite3?loc=auto -o sqlite3/models < sqlite3/custom-query.xo.sql

go build -o booktest-sqlite3 ./sqlite3/

./booktest-sqlite3
Book 1: my book title available: 2017-03-06 13:29:06.850318274 +0700 WIB
Book 1 author: Unknown Master
---------
Tag search results:
Book 2: 'changed second title', Author: 'Unknown Master', ISBN: '2' Tags: 'cool disastor'
Book 3: 'the third book', Author: 'Unknown Master', ISBN: '3' Tags: 'cool'

select * from books;
  book_id | author_id | isbn |        title         | year |              available              |     tags
+---------+-----------+------+----------------------+------+-------------------------------------+---------------+
        1 |         1 |    1 | my book title        | 2016 | 2017-03-06T13:29:06.850318274+07:00 |
        2 |         1 |    2 | changed second title | 2016 | 2017-03-06T13:29:06.850318274+07:00 | cool disastor
        3 |         1 |    3 | the third book       | 2001 | 2017-03-06T13:29:06.850318274+07:00 | cool
(3 rows)