mkdir -p /home/$(whoami)/tmp
cd /home/$(whoami)/tmp
git clone https://github.com/patzaw/TKCat.git
cd TKCat/ClickHouse/docker-init

cat > Dockerfile << 'EOF'
FROM clickhouse/clickhouse-server:26.9.19
COPY users.xml /users.xml
COPY default-user.xml /default-user.xml
COPY remove_default_user.yaml /remove_default_user.yaml
COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh
EOF

cat > launch-tkcat-instance.sh << 'EOF'
#!/bin/bash

##################################################################
## Customize parameters
IMAGE_BASE_NAME="tkcat"
TKCAT_INSTANCE="tkcat_test"
TKCAT_VERSION="2026-10-17"
TKCAT_ADMIN="admin"

## Don't forget to create a password for the TKCAT_ADMIN user
## In R: ?TKCat::change_chTKCat_password()

TKCAT_NAT_PORT=9103
TKCAT_HTTP_PORT=9113

TKCAT_HOSTDIR=/home/$(whoami)/tmp

######################
## Automatic parameters
IMAGE=${IMAGE_BASE_NAME}:${TKCAT_VERSION}

CONTAINER=${TKCAT_INSTANCE}_${TKCAT_VERSION}
TKCAT_HOME=${TKCAT_HOSTDIR}/${CONTAINER}
TKCAT_DATA=${TKCAT_HOME}/data
TKCAT_LOG=${TKCAT_HOME}/log

if [ -e "$TKCAT_HOME" ]; then
    echo "File $TKCAT_HOME already exists. Exiting without doing anything."
    exit 1
fi

######################
docker build -t ${IMAGE} .

######################
mkdir -p ${TKCAT_HOME}
chmod a+rwx ${TKCAT_HOME}
mkdir -p ${TKCAT_DATA}
chmod a+rwx ${TKCAT_DATA}
mkdir -p ${TKCAT_LOG}
chmod a+rwx ${TKCAT_LOG}

######################
docker run -d \
    --name ${CONTAINER} \
    --ulimit nofile=262144:262144 \
    --publish=${TKCAT_NAT_PORT}:9000 \
    --publish=${TKCAT_HTTP_PORT}:8123 \
    --volume ${TKCAT_DATA}:/var/lib/clickhouse \
    --volume ${TKCAT_LOG}:/var/log/clickhouse-server \
    -e TKCAT_INSTANCE="${TKCAT_INSTANCE}" \
    -e TKCAT_VERSION="${TKCAT_VERSION}" \
    -e TKCAT_ADMIN="${TKCAT_ADMIN}" \
    --restart=always \
    ${IMAGE}
EOF
chmod +x launch-tkcat-instance.sh

/bin/bash launch-tkcat-instance.sh

library(TKCat)
# Connect to your ClickHouse instance with the custom ports
k <- chTKCat(
   host = "localhost",
   port = 9113L, # Using the custom HTTP port
   user = "admin", # The admin user created by the docker-init script
   password = "", # No password after initialization !!
   drv = ClickHouseHTTP::ClickHouseHTTP()
)

# Change the admin password (you will be prompted for the new password)
change_chTKCat_password(k, "admin")
db_reconnect(k)

# Add additional users with appropriate permissions
# Create a regular user with read-only access
create_chTKCat_user(k, login = "read_only_user", contact = NA, admin = FALSE)

# Grant access to specific MDBs (after creating them)
# add_chMDB_user(k, mdb = "HPO", login = "read_only_user", admin = FALSE)

hpo <- get_MDB(k_readonly, "HPO")
plot(data_model(hpo))

# Get first 10 phenotypes
get_query(
   hpo,
   query = "SELECT * FROM HPO_hp LIMIT 10"
)

# Search for abnormalities
get_query(
   hpo,
   query = "SELECT * FROM HPO_hp WHERE lower(name) LIKE '%abnorm%'"
)

clinvar <- get_MDB(k_readonly, "ClinVar")

# Get the PIK3R2 gene
get_query(
   clinvar,
   query = "SELECT * FROM ClinVar_entrezNames WHERE symbol = 'PIK3R2'"
)

# Get pathogenic variants
get_query(
   clinvar,
   query = paste(
      "SELECT * FROM ClinVar_ReferenceClinVarAssertion ",
      "WHERE lower(clinicalSignificance) LIKE '%pathogen%' ",
      "LIMIT 10"
   )
)

chembl <- get_MDB(k_readonly, "CHEMBL")

# Get first 10 drug targets
get_query(
   chembl,
   query = "SELECT * FROM CHEMBL_component_sequence LIMIT 10"
)

# Search for a molecule
get_query(
   chembl,
   query = "SELECT * FROM CHEMBL_molecule_dictionary WHERE pref_name = 'DOCETAXEL'"
)

# Get diseases from HPO and associated traits from ClinVar
# (assuming they share condition identifiers)
get_query(
   hpo,
   query = paste(
      "SELECT h.* FROM HPO_diseases h ",
      "WHERE h.id IN (SELECT DISTINCT id FROM `ClinVar`.ClinVar_traitCref) ",
      "LIMIT 10"
   )
)

# Connect as admin
k_admin <- chTKCat(host = "localhost", port = 9113, user = "admin")

# Create a dedicated MCP user
create_chTKCat_user(
  k_admin,
  login = "mcp_user",
  password = "XXXX",
  contact = NA,
  admin = FALSE
)

# All users have read access to public MDBs
# Give access to public MDBs (or specific private MDBs)
# Access to private MDBs can be given as follows
add_chMDB_user(k_admin, mdb = "HPO", login = "mcp_user", admin = FALSE)

# Basic start (using the custom port 9113 for our ClickHouse instance)
TKCAT_HOST=localhost TKCAT_PORT=9113 TKCAT_USER=mcp_user TKCAT_PASSWORD=XXXX \
   Rscript -e "TKCat::chTKCat_mcp_server()"

# With custom parameters
Rscript -e "TKCat::chTKCat_mcp_server(maxRows = 500, maxExecutionTime = 60)"

# Instance 1 (using the custom port 9113 for our ClickHouse instance)
TKCAT_HOST=localhost TKCAT_PORT=9113 TKCAT_MCP_PORT=8801 \
   Rscript -e "TKCat::chTKCat_mcp_http_server()"

# Instance 2
TKCAT_HOST=localhost TKCAT_PORT=9113 TKCAT_MCP_PORT=8802 \
   Rscript -e "TKCat::chTKCat_mcp_http_server()"

curl -u mcp_user:XXXX http://localhost:8801/mcp \
   -H "Content-Type: application/json" \
   -H "Accept: application/json, text/event-stream" \
   -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"list_mdbs","arguments":{}}}'

[Unit]
Description=chTKCat MCP server (port %i)
After=network-online.target
Wants=network-online.target

[Service]
User=tkcat-mcp
Group=tkcat-mcp
Environment=TKCAT_HOST=localhost
Environment=TKCAT_PORT=9113
Environment=TKCAT_MCP_HOST=127.0.0.1
Environment=TKCAT_MCP_PORT=%i
EnvironmentFile=-/etc/tkcat-mcp.env
ExecStart=/usr/local/bin/Rscript -e "TKCat::chTKCat_mcp_http_server()"
Restart=on-failure
RestartSec=5
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=read-only

[Install]
WantedBy=multi-user.target

sudo useradd --system --no-create-home --shell /usr/sbin/nologin tkcat-mcp
sudo systemctl daemon-reload
sudo systemctl enable --now tkcat-mcp@8801 tkcat-mcp@8802 tkcat-mcp@8803 tkcat-mcp@8804

sudo nginx -t && sudo systemctl reload nginx

"mcpServers": {
   "tkcat": {
      "type": "remote",
      "url": "https://tkcat.example.com/tkcat_mcp",
      "headers": {
         "Authorization": "Basic amRvZTp4eHh4eHg="
      },
      "transport": "http"
   }
}