How to access tables in other schema in korma?

Question

In SQL, accessing tables in other schema is simple:

select * 
from other_schema.t
where ...

How can I do this in korma? What I actually to do is to access information_schema.tables table. So defining another db by defdb wouldn't be helpful.

I've tried to define the entity, however, failed.

(defentity information_schema.tables)

Answer 1

I've got to know that there is a way to specify the base table when defining an entity. When specifying the base table, it allows to set the schema with ..

(defentity tables
  (table :information_schema.tables))

This works fine for accessing information_schema.tables table, without defining another db.

Answer 2

You should be able to do this by defining another db. I can create a db like this:

CREATE database my_db;
USE my_db;
CREATE TABLE stuff (
  things VARCHAR(255)
);
INSERT INTO stuff (things) VALUES ("some things");

Now I define two Korma databases and entities, and query them:

(defdb my-db (mysql {:host "localhost" 
                     :port 3306 
                     :db "my_db"
                     :user "root"
                     :password nil}))

(defdb information-schema (mysql {:host "localhost" 
                                  :port 3306 
                                  :db "information_schema" 
                                  :user "root" 
                                  :password nil}))


(defentity stuff)

(defentity information-schema)

(select stuff
        (database my-db))

;; => ({:things "some things"})

(select TABLES 
        (database information-schema) 
        (fields :TABLE_SCHEMA :TABLE_NAME) 
        (where {:TABLE_SCHEMA "my_db"}))

;; => ({:TABLE_NAME "stuff", :TABLE_SCHEMA "my_db"})