Print table info from all schemas

[NoahTheDuke]

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!

[flyingmachine]

This is great! I made a lot of assumptions when putting this together just to get something working, hoping I'd get feedback like this. I've been in conference prep mode for my talk for reclojure tomorrow, but hope to get to this in the next few days.

Thank you!

[NoahTheDuke]

Ayy good luck with your talk! Can't wait to watch it when the vod goes up.

[flyingmachine]

Ok here's what I'm thinking: you can call xray like (xray conn {:schema-pattern "schema-x"}) for each of your schemas, and that should override the default. Would that work for your use case?

Perhaps I could introduce another function that would do this for you, and then return a map of {"schema-x" xray-x, "schema-y" xray-y} (hopefully that makes sense). I don't have enough experience with this to know what would be most useful unfortunately