gistfile1.clj

(ns mysql-test
  (:require [clojure.java.jdbc :as jdbc])
  (:use [edu.bc.bio.sequtils.files]))




(def mysql-ds
  {:classname "com.mysql.jdbc.Driver"
   :subprotocol "mysql"
   :subname "//127.0.0.1:3306/biosql"
   :user user
   :password password})

(defn sql-query [stmt & {:keys [f p] :or {f identity p false}}]
  (let [q (partial jdbc/query mysql-ds)]
    (when p (println stmt))
    (cond (string? stmt) (q [stmt])
          (vector? stmt) (q stmt)
          :else "Invalid query")))

(def genome-counts
     "select count(*) from
      (select distinct tx.taxon_id
              from bioentry as be,
                   taxon as tx,
                   ancestor as an
              where be.taxon_id=tx.taxon_id
              and tx.ncbi_taxon_id=an.ncbi_taxon_id
              and be.name regexp \"^NC\"
              and be.description not regexp \"plasmid\"
              and an.ancestors regexp \"/taxon/\") as foo")

(def query-operon
  "SELECT be.bioentry_id, be.name, tx.taxon_id, tx.ncbi_taxon_id, opl.*
   FROM taxon as tx, bioentry as be, ancestor as an, operon as op, operon_loc as opl
   WHERE tx.taxon_id=be.taxon_id
     AND tx.ncbi_taxon_id=an.ncbi_taxon_id
     and an.ancestors REGEXP 'Gamma*'
     and op.bioentry_id=be.bioentry_id
     and be.description not REGEXP \"plasmid\"
     and op.operon_id=opl.operon_id
   LIMIT 3;")