k1low / tbls Goto Github PK
View Code? Open in Web Editor NEWtbls is a CI-Friendly tool for document a database, written in Go.
License: MIT License
tbls is a CI-Friendly tool for document a database, written in Go.
License: MIT License
If you add a unique attribute and a foreign key constraint at the same time, the foreign key constraint information will be lost.
If you check category_info.md, sql is correct below.
CREATE TABLE `category_info` (
`id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`group_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `category_info_category_id_unique` (`category_id`),
CONSTRAINT `category_info_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
If you check the constraints, foreign will be treated as UNIQUE.
Name | Type | Definition |
---|---|---|
category_info_category_id_foreign | UNIQUE | UNIQUE KEY category_info_category_id_foreign (category_id) |
category_info_category_id_unique | UNIQUE | UNIQUE KEY category_info_category_id_unique (category_id) |
PRIMARY | PRIMARY KEY | PRIMARY KEY (id) |
I want it to be recognized as a foreign key.
| Name | Type | Definition |
| ---- | ---- | ---------- |
- | category_info_category_id_foreign | UNIQUE | UNIQUE KEY category_info_category_id_foreign (category_id) |
+ | category_info_category_id_foreign | FOREIGN KEY | FOREIGN KEY (category_id) REFERENCES categories (id)
| category_info_category_id_unique | UNIQUE | UNIQUE KEY category_info_category_id_unique (category_id) |
| PRIMARY | PRIMARY KEY | PRIMARY KEY (id) |
No error message.
Reproduce with the data defined in the repository below.
https://github.com/togana/example-tbls
Execute the following to generate a document.
$ docker-compose up -d mysql
$ sleep 5
$ docker-compose run --rm tbls
tbls version
use docker image tag latest.
$ docker image ls k1low/tbls
REPOSITORY TAG IMAGE ID CREATED SIZE
k1low/tbls latest 303cdf6d0395 2 months ago 39.4MB
$ docker image ls mysql:5.7
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 cd3ed0dfff7e 7 months ago 437MB
macOS Calalina
zsh
DEBUG=1
)v1.17.1 linux build is maybe broken
RE build
Version of Go is difference
tbls version
It's me again O_0, and thanks for your quick response.
And could you add the comment value when the output file is puml or png file,
something like ddl2plantuml
https://clickhouse.yandex/ is a blazing fast OLAP analytics columnar database, it would be really helpful if your great tool will support Clickhouse
there is golang driver https://github.com/ClickHouse/clickhouse-go
Postgres supports COMMENT ON
on just about anything, including INDEX
.
Please include any comments on indexes in the Indexes table.
Thanks for this very interesting work/project. It checks many of the right boxes for my ER-Diagram needs (including CI pipeline and markdown generation features). Further, it works as expected for conventional databases that use the default "dbo" schema for tables.
Was able to successfully connect to a Microsoft SQL server, but the first table in the database failed with the error:
"Not found table '{TableNameHere}'"
I expected that the database documentation would be generated.
The missing table makes use of a custom database namespace other than "dbo" (e.g. "appname_dbo"). It seems reasonable that I would need to indicate a database schema configuration when custom, but I don't know if/how to do that. The source code seems to mention "dbo" by name, but it is unclear to me if I can change the defaultSchemaName
via the tbls.yml
file.
tbls version
Ubuntu bionic
, macOS Calalina
, WSL2
, etc )bash
, zsh
, poworshell
, etc )DEBUG=1
)github.com/k1LoW/tbls/schema.(*Schema).FindTableByName
/Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:139
github.com/k1LoW/tbls/drivers/mssql.(*Mssql).Analyze
/Users/k1low/src/github.com/k1LoW/tbls/drivers/mssql/mssql.go:423
github.com/k1LoW/tbls/datasource.Analyze
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:111
github.com/k1LoW/tbls/cmd.glob..func5
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:73
github.com/spf13/cobra.(*Command).execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/k1LoW/tbls/cmd.Execute
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:184
main.main
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
/usr/local/Cellar/go/1.13.7/libexec/src/runtime/proc.go:203
runtime.goexit
/usr/local/Cellar/go/1.13.7/libexec/src/runtime/asm_amd64.s:1357
dbn: mssql://un:pwd@hostname:port/dbname
How to read documents use gitbook?I want to deploy the documents on my own server use gitbook.
And If I have many databases. How could I generate a document like this:
on the left is database memu. right is tables in the selected database?
Below is what I get now,and it's not working with gitbook.
Thanks!
Docs won't generate
Docs should be generated.
DEBUG=1 tbls doc
malformed version: 10.4.10-MariaDB-1:10.4.10+maria~bionic-log:
github.com/aquasecurity/go-version/pkg/version.Parse
/Users/k1low/go/pkg/mod/github.com/aquasecurity/[email protected]/pkg/version/version.go:44
Looks like the reason is that that library (github.com/aquasecurity/go-version
) has been updated.
1.47.0
MariaDB 10.4.10
Ubuntu bionic
zsh
Suppose you have tables A, B, and C.
both A and B point to table C.
If we specify to generate docs only for tables A and B, the link in theese pointing to non existing table C documentation will be generated.
Should not create a link to avoid user errors when browsing generated output.
A switch not to generate links at all can do the job as well.
comments:
- table: A
tableComment: table A
- table: B
tableComment: table B
desc: 'My description'
docPath: doc/schema
dsn: postgres://user:pass@localhost:5432/dbname?sslmode=disable
er:
skip: true
include:
- A
- B
name: Db overview
comments:
-
table: a
comment: xxxxxxxxxxxxxxxxxxxxx
columnComments:
order_no: xxxxxxxx
detail_no: xxxxxxxxx
amazon_item_id: xxxxxxxx
divided_delivery_charge: xxxxxxxxx
divided_cod_fee: xxxxxxxxx
registration_date: xxxxxxxxx
registration_id: xxxxxxxxx
update_date: xxxxxxxxx
update_id: xxxxxxxxx
-
table: b
comment: xxxxxxxxx
columnComments:
order_no: xxxxxxxxx
store_code: xxxxxxxxx
course: xxxxxxxxx
registration_date: xxxxxxxxx
registration_id: xxxxxxxxx
update_date: xxxxxxxxx
update_id: xxxxxxxxx
以上のようにymlファイルを記述しましたがコメントが追加されません
しかしテーブルを1つだけ指定した場合は追加されます
2個目の-(ハイフン)を無くしても追加されませんでした
> CREATE DATABASE test;
> USE test;
> CREATE TABLE table1 (productCode INT PRIMARY KEY);
> CREATE TABLE table2 (productCode INT, FOREIGN KEY(`productCode`) REFERENCES table1(`productCode`));
> SHOW CREATE TABLE table1;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
`productCode` int(11) NOT NULL,
PRIMARY KEY (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
> SHOW CREATE TABLE table2;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
`productCode` int(11) DEFAULT NULL,
KEY `productCode` (`productCode`),
CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `table1` (`productcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Then,
$ tbls doc mysql://user:[email protected]:3306/test ./dbdoc
not found column 'productcode' on table 'table1'
As you see, MySQL converts productCode
into productcode
in forein key constraint. This is OK in MySQL because
column names are case-insensitive. However tbls compares column names in case-sensitive manner. This is the cause of the error.
Column name comparison should be case-insensitive in MySQL.
$ DEBUG=1 tbls doc mysql://[email protected]:3306/test ./dbdoc
not found column 'productcode' on table 'table1'
github.com/k1LoW/tbls/schema.(*Table).FindColumnByName
/Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:149
github.com/k1LoW/tbls/drivers/mysql.(*Mysql).Analyze
/Users/k1low/src/github.com/k1LoW/tbls/drivers/mysql/mysql.go:362
github.com/k1LoW/tbls/datasource.Analyze
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:111
github.com/k1LoW/tbls/cmd.glob..func5
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:73
github.com/spf13/cobra.(*Command).execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/k1LoW/tbls/cmd.Execute
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:184
main.main
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
/usr/local/Cellar/go/1.13.7/libexec/src/runtime/proc.go:203
runtime.goexit
/usr/local/Cellar/go/1.13.7/libexec/src/runtime/asm_amd64.s:1357
after I tried to use tbls doc
with postgre, the error occurred.
get this fixed.
here it is:
tbls doc
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x41b8fb2]
goroutine 1 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/database/sql/sql.go:3063 +0x72
database/sql.(*Rows).Close(0x0, 0x5109c4c, 0x3d0)
/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/database/sql/sql.go:3059 +0x33
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze(0xc00039da80, 0xc000377570, 0x5399d20, 0xc000449ee0)
/Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:141 +0x28bb
github.com/k1LoW/tbls/datasource.Analyze(0xc000036640, 0x49, 0x0, 0x0, 0x0, 0x0)
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:111 +0x80b
github.com/k1LoW/tbls/cmd.glob..func5(0x5cfd1e0, 0x5d7c5a8, 0x0, 0x0)
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:73 +0x1b0
github.com/spf13/cobra.(*Command).execute(0x5cfd1e0, 0x5d7c5a8, 0x0, 0x0, 0x5cfd1e0, 0x5d7c5a8)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:846 +0x2aa
github.com/spf13/cobra.(*Command).ExecuteC(0x5cfd9c0, 0x4, 0x5d7c5a8, 0x0)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:950 +0x349
github.com/spf13/cobra.(*Command).Execute(...)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/k1LoW/tbls/cmd.Execute()
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:184 +0x86
main.main()
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33 +0x20
I get the same error when using other postgresql databases.
tbls version
1.43.0
Database version
psql (PostgreSQL) 9.2.24
OS
macOS Calalina
Shell
zsh
Stacktrace or error message from tbls ( If you can, set DEBUG=1
)
DEBUG=1 tbls doc
and the same error message.
Config ( .tbls.yml )
dsn: postgres://user:pw@host:5432/db?sslmode=disable
The embedded templates are great to get you started with tbls doc
but depending on the DB or the project it might fall short in some aspects.
It would be very helpful to be possible to specify a personalized template at execution time and inside the tbls.yml config file.
E.g.:
# tbls.yml
er:
format: svg
comment: true
distance: 2
font: M+
template: "path/to/dot/template"
$tbls out -t plantuml -template "path/to/puml/template" -o schema.puml
It would be great if tbls
supported the Oracle database.
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x41f7cf6]
goroutine 1 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
/Users/k1low/.anyenv/envs/goenv/versions/1.12.5/src/database/sql/sql.go:2976 +0x66
database/sql.(*Rows).Close(0x0, 0x48c2a0c, 0x172)
/Users/k1low/.anyenv/envs/goenv/versions/1.12.5/src/database/sql/sql.go:2972 +0x33
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze(0xc0003397c0, 0xc0000dd4f0, 0x498d880, 0xc0003b6f40)
/Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:131 +0x2f8a
github.com/k1LoW/tbls/datasource.Analyze(0x7ffeefbfe421, 0xa0, 0x0, 0x0, 0x0)
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:74 +0x683
github.com/k1LoW/tbls/cmd.glob..func2(0x4e5dbc0, 0xc0001f7460, 0x1, 0x1)
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:72 +0x111
github.com/spf13/cobra.(*Command).execute(0x4e5dbc0, 0xc0001f7430, 0x1, 0x1, 0x4e5dbc0, 0xc0001f7430)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:766 +0x2ae
github.com/spf13/cobra.(*Command).ExecuteC(0x4e5d4a0, 0x0, 0x0, 0x45eabea)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:850 +0x2fc
github.com/spf13/cobra.(*Command).Execute(...)
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:800
github.com/k1LoW/tbls/cmd.Execute()
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:59 +0x2e
main.main()
/Users/k1low/src/github.com/k1LoW/tbls/main.go:32 +0x20
$ tbls version
1.15.1
$ go version
go version go1.12.5 darwin/amd64
Please let me know if there's any other information I can provide to help debugging!
mysqlのドキュメントを生成しようとするとエラーが出てしまいます。
対処方法などありましたら、教えていただけないでしょうか。
# cat /etc/redhat-release
CentOS release 6.6 (Final)
# mysql --version
mysql Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using EditLine wrapper
# go version
go version go1.9.6 linux/amd64
# tbls version
0.9.3
# tbls doc mysql://db_user:password@localhost:3306/cs_production ./dbdoc
sql: Scan error on column index 1: unsupported Scan, storing driver.Value type <nil> into type *string
link #36 (comment)
# cat /etc/redhat-release
CentOS release 6.9 (Final)
# mysql --version
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
# go version
go version go1.9.6 linux/amd64
# go get -u github.com/k1LoW/tbls
# DEBUG=1 tbls doc mysql://user:password@localhost:3306/tbls_mysql57 ./dbdoc
panic: runtime error: index out of range
goroutine 1 [running]:
github.com/k1LoW/tbls/db.Analyze(0x7ffdb65f48b3, 0x46, 0x0, 0x0, 0x0)
/root/go/src/github.com/k1LoW/tbls/db/db.go:28 +0x453
github.com/k1LoW/tbls/cmd.glob..func4(0xaa7620, 0xc420118e40, 0x2, 0x2)
/root/go/src/github.com/k1LoW/tbls/cmd/doc.go:56 +0x65
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).execute(0xaa7620, 0xc420118e00, 0x2, 0x2, 0xaa7620, 0xc420118e00)
/root/go/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:766 +0x2c1
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).ExecuteC(0xaa7ae0, 0xc42001c0b8, 0x0, 0xc42004ff48)
/root/go/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:852 +0x334
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).Execute(0xaa7ae0, 0x0, 0x0)
/root/go/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:800 +0x2b
github.com/k1LoW/tbls/cmd.Execute()
/root/go/src/github.com/k1LoW/tbls/cmd/root.go:53 +0x2d
main.main()
/root/go/src/github.com/k1LoW/tbls/main.go:34 +0x20
In Postgres, a Materialized View is basically a cached copy of a query, and has a lot in common with views and tables. They are treated just like tables when querying, and just like views in that they have an underlying query that produced the data.
However, they are not showing up in the tbls
documentation, because they are neither a view, nor a table, and the current information schema query doesn't include materialized views anyway (they are listed in the pg_catalog.pg_class
table but not in information_schema.tables
).
Current materialized views can be listed with the pg_catalog.pg_matviews
view. The following version of the tableRows
query adds in materialized views:
SELECT DISTINCT cls.oid AS oid, cls.relname AS table_name, tbl.table_type AS table_type, tbl.table_schema AS table_schema
FROM pg_catalog.pg_class cls
INNER JOIN pg_namespace ns ON cls.relnamespace = ns.oid
INNER JOIN (SELECT table_name, table_type, table_schema
FROM information_schema.tables
WHERE table_schema != 'pg_catalog' AND table_schema != 'information_schema'
AND table_catalog = $1
UNION SELECT matviewname as table_name, 'MATERIALIZED VIEW' as table_type, schemaname as table_schema
FROM pg_catalog.pg_matviews
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'
) tbl ON cls.relname = tbl.table_name AND ns.nspname = tbl.table_schema
ORDER BY oid;
If we then update the view definition handling to use the pg_catalog.pg_get_viewdef()
function (which works for both views and materialized views) the code can also handle MATERIALIZED VIEWs. Note: pg_get_viewdef()
takes an OID, but currently the OID is stored as a string, this should really be a uint64
!
This is a rough sketch of how this could work:
// (materialized) view definition
if tableType == "VIEW" || tableType == "MATERIALIZED VIEW" {
viewDefRows, err := p.db.Query(`SELECT pg_catalog.pg_get_viewdef($1);`, tableOid)
defer viewDefRows.Close()
if err != nil {
return errors.WithStack(err)
}
for viewDefRows.Next() {
var tableDef sql.NullString
err := viewDefRows.Scan(&tableDef)
if err != nil {
return errors.WithStack(err)
}
table.Def = fmt.Sprintf("CREATE %s %s AS (\n%s\n)", tableType, tableName, strings.TrimRight(tableDef.String, ";"))
}
}
As an aside: the same feature request could apply to Oracle and SQL Server, which also support materialized views. I just don't know how they treat these types in their system catalog and if tbls
already handles them.
Feature request:
We use the knex library for migrations.
We'd like to be able to add table names to the tbls config to be ignored from the doc's and diagrams.
They are two tables knex_migrations
and knex_migrations_lock
.
They seem to generated slightly different schemas everytime we add a new migration version, which is adding unnecessary overhead to our PRs.
It would be nice to be able to exclude these tables, by name, in the tbls config file.
I'm dealing with slow connection to a remote database; running tbls lint
or tbls doc
over an ssh tunnel is painfully slow. However, running tbls
on the remote server and then outputting to JSON back over the SSH connection is way better:
alias rtbls="scp .tbls.yml server:/tmp && ssh server 'cd /tmp && tbls out -t json' > /tmp/tbls.json"
rtbls && tbls lint json:///tmp/tbls.json
However, I'm defining additional relations:
entries in my configuration:
# additional implied relationships
relations:
- table: foo
columns:
- bar_type
parentTable: bar_types
parentColumns:
- bar_type_name
def: bar_type validation
- table: bar_types
columns:
- spamparams
parentTable: spam_definitions
parentColumns:
- name
def: validation of `spam?ham=...` parameters
lint:
duplicateRelations:
enabled: true
These are reflected in the exported JSON file in the "relations"
structure with "virtual": true
set.
When you then run lint
with the same configuration file and the JSON file as the DSN, warnings are generated for duplicate relationships:
public.foo: duplicate relations. [public.foo -> public.bar_types]
public.bar_types: duplicate relations. [public.foo -> public.spam_definitions]
When generating ER diagrams, two dotted lines and labels are drawn, per virtual relationship.
Virtual relationships present in the configuration file and in a JSON dump should be merged, and so not generate a warning or a duplicate line.
Workaround is to remove the "virtual": true
relationships first; using jq
as a filter:
"... && ssh server '...' | jq 'del(.relations[] | select(.virtual == true))' > /tmp/tbls.json"
This problem may also apply to comments defined in the configuration. I did not test this.
tbls version
: 1.35.0bash
while running tbls doc $DSN $DIR
generate the docs
err: not found column 'res_company_ldap."user"'
the column already exists, the DB is an odoo DB and working fine
tbls version
latestDEBUG=1
)not found column 'res_company_ldap."user"' github.com/k1LoW/tbls/schema.(*Table).FindColumnByName /Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:174 github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze /Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:304 github.com/k1LoW/tbls/datasource.Analyze /Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:83 github.com/k1LoW/tbls/cmd.glob..func4 /Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:72 github.com/spf13/cobra.(*Command).execute /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:830 github.com/spf13/cobra.(*Command).ExecuteC /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:914 github.com/spf13/cobra.(*Command).Execute /Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:864 github.com/k1LoW/tbls/cmd.Execute /Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:59 main.main /Users/k1low/src/github.com/k1LoW/tbls/main.go:33 runtime.main /Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/proc.go:203 runtime.goexit /Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/asm_amd64.s:1357
Currently, the README.md
file lists all tables with their full table comment.
I'm putting a lot of info in some of my table descriptions, including SQL code blocks. These render rather badly in the comment column of the central Tables table, which treats markdown just a little different.
Perhaps the Tables table should only use the first paragraph of each table description.
E.g. the description
Foo widgets
Tracks Foo widget metadata as the frobnar twiddles with the status and inserts more
information.Because there may be redundant, duplicated rows with different timestamps in this table,
please use a window query to consolidate adjacent rows:-- Some elaborate sample query
would only take the text up to the first double newline and so the comment in the Tables comments column becomes:
Foo widgets
and only the detail page shows the remainder.
Previous Title:
[Question] Which property is read for MSSQL server comments?
First of all, great tool.
I am using this tool to document my MSSQL server and would like to know which property is used to generate the comment data. I have tried adding the widely popular MS_Description
(set from SMSS description) property to tables and columns but the generated output contains empty cells for Comments column and Description header under table.
I see no options in configuration for specifying the property. Am I doing something wrong?
Any help is appreciated.
Version: v1.16.0
System: Ubuntu 18.04
add ER
config into .tbls.yml
, but it still generates png file ??
er:
# Skip generation of ER diagram
# Default is false
skip: false
# ER diagram format
# Default is `png`
format: svg
ps: tbls is a great tool !!!
Feature Request. Hi, could you add a feature, that allows adding a comment to the not existing field in the table? For example, there is a service (f.e. superset) usage that extends the table with some custom fields or metrics, and I want them to be documented. Thanks in advance.
Feature request
Dear all,
Is it possible to add( bc I didn't find a such option) an option to mask the table or a column name using * , %, _
, etc? Or check some conditions likewhere:
to identify the list of affected objects when the exclude list is specified.
Thanks in advance.
tbls doesn't manage the md files after creation
A new md file should be created after creation a corresponding table in the database and then running tbls doc
output files already exists
Am I right that tbls doc
should update the md files indocPath
after running? So if we create/ delete a new object in the database, the related md file should appear/desappear?
As I see, now it creates the files in a proper way only the first time. Next, even though the table was deleted from the database, the file remains unchanged. And similary, if I create a new table, I could see the changes only in tbls diff
When I run tbls doc
again to commit them, the local files aren't changed and I don't see any new files related to the created table.
tbls version
1.24.1DEBUG=1
)https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-features.html
↑公式ドキュメントに記載の通り、AWS Redshiftにはトリガーがありません。
そのため、直近のバージョンのtblsを実行するとpanicが発生するようです。
$ tbls version
dev
$ DEBUG=1 tbls doc --force --er-format svg
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x41e5af6]
goroutine 1 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
/usr/local/opt/go/libexec/src/database/sql/sql.go:2955 +0x66
database/sql.(*Rows).Close(0x0, 0xc0002d9880, 0x2)
/usr/local/opt/go/libexec/src/database/sql/sql.go:2951 +0x33
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze(0x49d6558, 0xc0000e6180, 0xc0000a31d0, 0x468ee00, 0xc00035bc00)
/Users/watarukura/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:158 +0x233f
github.com/k1LoW/tbls/datasource.Analyze(0xc0000b6360, 0x5c, 0x0, 0x0, 0x0)
/Users/watarukura/src/github.com/k1LoW/tbls/datasource/datasource.go:62 +0x29d
github.com/k1LoW/tbls/cmd.glob..func2(0x49ad220, 0xc000089c20, 0x0, 0x3)
/Users/watarukura/src/github.com/k1LoW/tbls/cmd/doc.go:72 +0x110
github.com/spf13/cobra.(*Command).execute(0x49ad220, 0xc000089bf0, 0x3, 0x3, 0x49ad220, 0xc000089bf0)
/Users/watarukura/src/github.com/spf13/cobra/command.go:766 +0x2cc
github.com/spf13/cobra.(*Command).ExecuteC(0x49ad940, 0xc0000f3f88, 0x4007b30, 0xc00007a058)
/Users/watarukura/src/github.com/spf13/cobra/command.go:850 +0x2f8
github.com/spf13/cobra.(*Command).Execute(0x49ad940, 0x0, 0x0)
/Users/watarukura/src/github.com/spf13/cobra/command.go:800 +0x2b
github.com/k1LoW/tbls/cmd.Execute()
/Users/watarukura/src/github.com/k1LoW/tbls/cmd/root.go:59 +0x2d
main.main()
/Users/watarukura/src/github.com/K1LoW/tbls/main.go:32 +0x20
/Users/watarukura/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:158
// constraint
constraintRows, err := db.Query(`
SELECT
pc.conname AS name,
(CASE WHEN contype='t' THEN pg_get_triggerdef((SELECT oid FROM pg_trigger WHERE tgconstraint = pc.oid LIMIT 1))
ELSE pg_get_constraintdef(pc.oid)
END) AS def,
contype AS type
FROM pg_constraint AS pc
LEFT JOIN pg_stat_user_tables AS ps ON ps.relid = pc.conrelid
WHERE ps.relname = $1
AND ps.schemaname = $2
ORDER BY pc.conrelid, pc.conindid, pc.conname`, tableName, tableSchema)
defer constraintRows.Close()
if err != nil {
return errors.WithStack(err)
}
SELECT oid FROM pg_trigger WHERE tgconstraint = pc.oid LIMIT 1;
[42703][500310] [Amazon](500310) Invalid operation: column "tgconstraint" does not exist in pg_trigger; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: column "tgconstraint" does not exist in pg_trigger;
Error: not found table dummy_table
このテーブルは hoge
という schema
に属しています。
上記箇所で t.Name
は schema name が考慮されておらず name
は考慮されているため不一致となるようです。
t.Name -> dummy_table
name -> hoge.dummy_table
Hi @k1LoW,
It's a followup of #158
the tbls lint
returns the full object name (schema_name.table_name[.column_name]) for the objects except the public schema. It just omits the public
word in the output.
That's why it's impossible to filter the public objects out just using
Exclude:
- public.*
I just wonder, is it difficult to add this one into code?
thanks in advance.
Markdownをそのまま見た場合、テーブルの出力内容が整形されておらず非常に見にくいため、
整形して出力するようにしてほしい
現在の状態
## Columns
| Name | Type | Default | Nullable | Children | Parents | Comment |
| ---- | ---- | ------- | -------- | -------- | ------- | ------- |
| id | int(11) | | false | | | |
| name | varchar(10) | | false | | | |
期待する結果
## Columns
| Name | Type | Default | Nullable | Children | Parents | Comment |
| ---- | ----------- | ------- | -------- | -------- | ------- | ------- |
| id | int(11) | | false | | | |
| name | varchar(10) | | false | | | |
I want to try this app on my SQL Server databases. So I tried to follow the readme.md instruction. I never before used Docker. But why not to use it.
I installed docker desktop for Windows => OK
I called docker pull k1low/tbls:latest
in a command line => OK, something happened, something was downloaded, But I don't understand what happens when I call this command. A short explanation in the readme would be helpful.
Add .tbls.yml ( or tbls.yml ) file to your repository.
My understanding: I should create an empty git repository and in this repo I should create the file?
I created an empty git repository on the place where all my repos are located. But Docker will not know this place.
Run
tbls doc
If I run in the folder where I created the repository the "tbls" is not found.
if I run this where I called the docker pull k1low/tbls:latest
then there is no information where the repository is located.
So please explain in the documentation a little bit, how I should the Docker tell which repository to use.
BTW, when I want to document MS SQL Server, did tbls only support user+password or also integrated security? If yes, how?
$ tbls doc
not found table 'public.topology'
You can reproduce with docker image mdillon/postgis:9.5
I expected a generated documentation.
not found table 'public.topology'
PostGIS is enabled and manages the topology
schema. This is the output of \dt
:
foobar> \dt
+----------+-------------------+--------+--------+
| Schema | Name | Type | Owner |
|----------+-------------------+--------+--------|
| public | migration | table | dbuser |
| public | spatial_ref_sys | table | dbuser |
| public | subscriber | table | dbuser |
| public | user | table | dbuser |
| public | user_role | table | dbuser |
| public | users_homes | table | dbuser |
| public | users_roles | table | dbuser |
| public | users_tokens | table | dbuser |
| topology | layer | table | dbuser |
| topology | topology | table | dbuser |
+----------+-------------------+--------+--------+
tbls version
: 1.28.2USE_GEOS=1 USE_PROJ=1 USE_STATS=1
.tbls.yml
:---
name: foobar
dsn: postgres://dbuser:dbpass@localhost:5432/foobar?sslmode=disable
docPath: docs/schema
$ DEBUG=1 tbls doc
not found table 'public.topology'
github.com/k1LoW/tbls/schema.(*Schema).FindTableByName
/Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:110
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze
/Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:337
github.com/k1LoW/tbls/datasource.Analyze
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:85
github.com/k1LoW/tbls/cmd.glob..func4
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:69
github.com/spf13/cobra.(*Command).execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:844
github.com/spf13/cobra.(*Command).ExecuteC
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:945
github.com/spf13/cobra.(*Command).Execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:885
github.com/k1LoW/tbls/cmd.Execute
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:59
main.main
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
/Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/proc.go:203
runtime.goexit
/Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/asm_amd64.s:1357
error pq: SSL is not enabled on the server
connect
pq: SSL is not enabled on the server
how to disable ssl in connection ?
tbls version
DEBUG=1
)テーブルのコメントに改行が含まれている場合、個々の md は問題ないですが README.md の表が崩れるようです。 markdown で出力するコメント部分が <br>
対応されると良いのかなと思いました。
COMMENT ON TABLE schemaX.test_table IS 'title xxx.
Description xxx.';
# schemaX.test_table
## Description
title xxx.
Description xxx.
## Tables
| Name | Columns | Comment | Type |
| ---- | ------- | ------- | ---- |
| [schemaX.test_table](schemaX.test_table.md) | 1 | title xxx.
Description xxx. | BASE TABLE |
When executing tbls doc
on a PostgreSQL database the program stops and outputs error malformed version: 12.3,
doc program should complete and show output filename
The PostgreSQL version that tbls obtains has the comma appended for some reason. I'm pretty sure the error might be with the go database/sql
package, but in any case this might be resolved by slicing off the comma from the string returned at https://github.com/k1LoW/tbls/blob/master/drivers/postgres/postgres.go#L348.
1.48.0
PostgreSQL 12.3
powershell
I'd like to request support for cross-schema relations in the ER diagram. Our use case would only be virtual relations (as they're not real foreign keys) but having them shown would be very useful.
I think just having the external schema(s) added to the diagram as a group with related tables inside it and only fields of interest would be sufficient.
Bonus: If the SVG generated clickable areas to navigate to schema or table documentation that'd be epic 👌 Probably a separate feature request though, haha.
Generating the docs with a clean DB or with a DB that has some data gives different results, specifically in tables with AUTO_INCREMENT.
The following diff is form a docs generate from a clean/empty DB and vs. docs generated from a DB with data.
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+ ) ENGINE=InnoDB AUTO_INCREMENT=[Redacted by tbls] DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
No difference in the generated docs regardless of the state of the DB.
Would be nice to have a flag to prevent the AUTO_INCREMENT=[...]
to be shown, on the same line of #240.
More than happy to send a PR!
tbls version
: 1.44.0Ubuntu bionic
, macOS Calalina
, WSL2
, etc ): Linuxbash
, zsh
, poworshell
, etc )DEBUG=1
)tbls doc sq://./data.db # => near ".": syntax error
Tried different paths - absolute, relative with dot and without.
No errors
near ".": syntax error
github.com/k1LoW/tbls/drivers/sqlite.(*Sqlite).Analyze
/Users/k1low/src/github.com/k1LoW/tbls/drivers/sqlite/sqlite.go:251
github.com/k1LoW/tbls/datasource.Analyze
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:85
github.com/k1LoW/tbls/cmd.glob..func4
/Users/k1low/src/github.com/k1LoW/tbls/cmd/doc.go:69
github.com/spf13/cobra.(*Command).execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:844
github.com/spf13/cobra.(*Command).ExecuteC
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:945
github.com/spf13/cobra.(*Command).Execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:885
github.com/k1LoW/tbls/cmd.Execute
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:59
main.main
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
/Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/proc.go:203
runtime.goexit
/Users/k1low/.anyenv/envs/goenv/versions/1.13.6/src/runtime/asm_amd64.s:1357
tbls version
v1.29.2Feature request:
A lint option that requires all tables to have a specified column(s).
We are constantly forgetting to include a modified_at
column, and would like tbls lint to help us remember.
We'd also like to exclude some tables from this lint just like the other lint options.
Hello,
is there any plan to support MS SQL Server in the future?
Postgres supports COMMENT ON
on just about anything, including TRIGGER
.
Please include any comments on triggers in the Triggers table.
ref: #212
like Terraform
terraform {
required_version = "~> 0.11"
}
Hi All.
I have failed to overwrite commit author 🙇
I'll fix it.
tbls doc postgres://user:[email protected]:5439/dbname ./redshift
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x112f0db]
goroutine 1 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
/usr/local/opt/go/libexec/src/database/sql/sql.go:2907 +0x6b
database/sql.(*Rows).Close(0x0, 0xc420195840, 0x1)
/usr/local/opt/go/libexec/src/database/sql/sql.go:2903 +0x33
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze(0x16ad940, 0xc4200b19a0, 0xc42009a900, 0x14995c0, 0xc4200acfc0)
/Users/watarukura/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:28 +0x26cf
github.com/k1LoW/tbls/db.Analyze(0x7ffeefbffa51, 0x5a, 0x0, 0x0, 0x0)
/Users/watarukura/src/github.com/k1LoW/tbls/db/db.go:47 +0x296
github.com/k1LoW/tbls/cmd.glob..func4(0x168a680, 0xc420134d60, 0x2, 0x2)
/Users/watarukura/src/github.com/k1LoW/tbls/cmd/doc.go:53 +0x79
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).execute(0x168a680, 0xc420134d20, 0x2, 0x2, 0x168a680, 0xc420134d20)
/Users/watarukura/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:766 +0x2c1
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).ExecuteC(0x168ab40, 0xc420057f68, 0x136c5d7, 0x144f631)
/Users/watarukura/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:852 +0x30a
github.com/k1LoW/tbls/vendor/github.com/spf13/cobra.(*Command).Execute(0x168ab40, 0xc42007c058, 0x0)
/Users/watarukura/src/github.com/k1LoW/tbls/vendor/github.com/spf13/cobra/command.go:800 +0x2b
github.com/k1LoW/tbls/cmd.Execute()
/Users/watarukura/src/github.com/k1LoW/tbls/cmd/root.go:49 +0x2d
main.main()
/Users/watarukura/src/github.com/k1LoW/tbls/main.go:33 +0x20
Analyze で実行している下記SQLがエラーを返すようです。
dbname=#
SELECT CONCAT(table_schema, '."', table_name, '"')::regclass::oid AS oid, table_name, table_type
FROM information_schema.tables
WHERE table_schema != 'pg_catalog' AND table_schema != 'information_schema'
AND table_catalog = 'dbname'
ORDER BY oid;
ERROR: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
dbname=#
I'm testing tbls against a schema with a reserved name (time
) as well as with a hyphenated table name:
CREATE SCHEMA time;
CREATE TABLE time.bar (
id int IDENTITY(1, 1) PRIMARY KEY
);
CREATE TABLE time."hyphenated-table" (
id int IDENTITY(1, 1) PRIMARY KEY
);
CREATE TABLE time.referencing (
id int IDENTITY(1, 1) PRIMARY KEY,
bar_id int NOT NULL,
ht_id int NOT NULL,
CONSTRAINT referencing_bar_id FOREIGN KEY(bar_id) REFERENCES time.bar(id),
CONSTRAINT referencing_ht_id FOREIGN KEY(ht_id) REFERENCES time."hyphenated-table"(id)
);
While foreign keys are not enforced by Redshift, the query optimiser does use them so it makes sense to put these in.
The above schema breaks tbls' relations parsing, because it doesn't account for the quoting that AWS redshift puts on both the schema and on the second table, when querying constraints:
select pg_get_constraintdef(oid) as def from pg_constraint
where conrelid = 'time.referencing'::regclass and contype = 'f';
def
-----------------------------------------------------
FOREIGN KEY (bar_id) REFERENCES "time".bar(id)
FOREIGN KEY (ht_id) REFERENCES "time"."hyphenated-table"(id)
(2 rows)
Note the "time"
quoting in the response, and the separate quotes around hyphenated-table
.
This causes tbls to be unhappy, because the postgres driver only strips quotes from the start and end of the string
With DEBUG=1
we thus see:
not found table 'time".bar'
github.com/k1LoW/tbls/schema.(*Schema).FindTableByName
/Users/k1low/src/github.com/k1LoW/tbls/schema/schema.go:136
github.com/k1LoW/tbls/drivers/postgres.(*Postgres).Analyze
/Users/k1low/src/github.com/k1LoW/tbls/drivers/postgres/postgres.go:359
github.com/k1LoW/tbls/datasource.Analyze
/Users/k1low/src/github.com/k1LoW/tbls/datasource/datasource.go:93
github.com/k1LoW/tbls/cmd.glob..func6
/Users/k1low/src/github.com/k1LoW/tbls/cmd/out.go:80
github.com/spf13/cobra.(*Command).execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:846
github.com/spf13/cobra.(*Command).ExecuteC
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:950
github.com/spf13/cobra.(*Command).Execute
/Users/k1low/go/pkg/mod/github.com/spf13/[email protected]/command.go:887
github.com/k1LoW/tbls/cmd.Execute
/Users/k1low/src/github.com/k1LoW/tbls/cmd/root.go:153
main.main
/Users/k1low/src/github.com/k1LoW/tbls/main.go:33
runtime.main
/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/runtime/proc.go:203
runtime.goexit
/Users/k1low/.anyenv/envs/goenv/versions/1.13.10/src/runtime/asm_amd64.s:1357
Perhaps a more sophisticated quote removal routine is needed?
tbls version
: 1.35.0A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.