donut-party / dbxray Goto Github PK

Sorry to make this an issue - it isn't really - it's just that on the page of https://donut.party - there's a link to dbxray - but the actual url goes to datapotato. Someone needs to tweak the site to go to dbxray instead :)

Hello!

Really cool package!I have been trying it out with DB2 and noticed something that maybe is a bug.

When I run dbxray on a table with foreign keys i get and error that unconnected-tables tries to do set/difference on a Con.
And from some quick debugging that seems to be the case. table-names is converted to a set but connected-tables is not.

Or am I missing something? :)

Request

Currently, only the default schema ("public" in Postgres, which is what I use) is queried for table information. I would like it if all schemas were processed.

Thoughts

It looks like :schema-adapter is threaded through most of the main functions, so I'm not sure if this would be easy or not. For Postgres only, you could change prep and get-tables like this to get the full list of tables into a map grouped by the schema:

(defn prep
  "returns metadata needed to construct xray"
  [conn & [adapter-opts]]
  (let [metadata (.getMetaData conn)
        dbtype   (dbx/database-product-name metadata)
        schemas
        (jdbc/execute!
          conn
          ["SELECT table_schema
           FROM information_schema.tables
           WHERE table_type = 'BASE TABLE'
           AND table_schema NOT IN ('pg_catalog', 'information_schema')
           GROUP BY table_schema;"])
        dbmd     {:metadata     metadata
                  :dbtype       dbtype
                  :catalog      (-> metadata .getConnection .getCatalog)
                  :schemas schemas
                  :include-raw? (:include-raw? adapter-opts)}]
    (assoc dbmd :dbadapter (merge (dbx/adapter dbmd) adapter-opts))))

(defn get-tables
  [{:keys [metadata catalog schemas]}]
  (binding [njdf/*datafy-failure* :omit]
    (->> schemas
         (keep :tables/table_schema)
         (mapcat #(-> metadata
                      (.getTables catalog % nil (into-array ["TABLE"]))
                      (dbx/datafy-result-set)))
         (group-by :table_schem))))

and then maybe switch out each of the getX functions to mapcat over each of the pairs of schema to list of tables. This is of course compounded by how each database implements all this shit differently 😭 so my apologies for heaping annoying work onto your plate.

Thanks so much!

currently if the table foo exists in multiple databaes, then dbxray merges the tables' metadata. it should distinguish databases

Hi, I'm messing around with this library a bit, looks like it can be very useful!

I notice table-order (well actually, dep/depend) freaks out when it encounters a circular foreign key. Examples:

• a table contains a foreign key to itself
• table A has a foreign key to a column on table B, and vice versa

As a quick test I altered table-order to include a filter as follows:

(defn- table-order
  "used to create an omap for tables"
  [xray]
  (let [deps (table-deps xray)
        deps-set (set deps)]
    (->> deps
         (filter (fn without-circular-dependencies [[table-name dep]]
                   (let [flipped [dep table-name]]
                     (nil? (get deps-set flipped)))))
         (reduce (fn reduce-fn [g [table-name dep]]
                   (dep/depend g table-name dep))
                 (dep/graph))
         (dep/topo-sort))))

This prevents the error when calling the xray function, but I can't say for sure it still does what it's supposed to be doing correctly, or that it doesn't cause some other issue.
In my case, I run into the following error when trying to call plumatic-schema on the result:

Execution error (NullPointerException) at donut.dbxray.generate.plumatic-schema/column-spec (form-init12429061481132598728.clj:41).
Cannot invoke "clojure.lang.IFn.invoke(Object, Object)" because "column_type" is null

I'm not sure this is related, though.

Oh, one minor nitpick: the name xray is used as an input variable in multiple functions, but xray is a function name as well.
It may make things a bit easier on the eye if a different name is used for the inputs, or maybe the function can be named ->xray, e.g.

example: have the xray capture a varchar's max number of characters, then in generated schemas include predicates that limit the size of the generated string

I really like schema.rb as a documentation method and want to use dbxray as a way to replicate it. The current output of dbxray is extremely detailed and verbose (specifically the :raw part of each entry).

I would like to get a less detailed output somehow, one that retains the basics (column name and type and uniqueness and defaults etc), and maybe puts foreign keys or indexes or constraints in a single line or a two entry map (which columns, title).

Thank you so much, this tool is very very cool.