Регистрация | Войти
Lisp — программируемый язык программирования
pinc
Автор: turtle - 2012-03-21T14:19:42.000000+04:00
(ns ru.bazon.betan.persistence
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.contrib.generic.math-functions :as math]
)

  (:gen-class)
)


(System/setProperty "derby.stream.error.field" "java.lang.System.err")

(def db-version 4)

(def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"
           :subprotocol "derby"
           :subname "./bet-an-db/"
           :create true}
)


(def bet-params [[:score1 :int "Счёт1"]
                 [:score2 :int "Счёт2"]
                 [:time :int "Время"]
                 [:p_1 :double "1"]
                 [:p_x :double "X"]
                 [:p_2 :double "2"]
                 [:p_1x :double "1X"]
                 [:p_12 :double "12"]
                 [:p_x2 :double "X2"]
                 [:p_fora1 :double "Фора1"]
                 [:p_koeff1 :double "Коэфф1"]
                 [:p_fora2 :double "Фора2"]
                 [:p_koeff2 :double "Коэфф2"]
                 [:p_total :double "Тотал"]
                 [:p_bol :double "БОЛ"]
                 [:p_men :double "МЕН"]
                 [:timeus :bigint]]
)


;;;

(defmacro within-db [& body]
  `(jdbc/with-connection
     db
     (jdbc/transaction
       ~@body
)
)
)


(defmacro exceptionable [& body]
  `(try
     ~@body
     (catch Exception e#
       (println e#)
)
)
)


;;; Main functions

(defn find-version
  ""
  [name]
  (within-db
    (jdbc/with-query-results rs ["select * from versions where name=?" name]
                             (first rs)
)
)
)


(defn persist-version
  ""
  [name version]
  (within-db
    (jdbc/update-or-insert-values
      :versions
      ["name=?" name]
      {:name name :version version}
)
)
)


(defn find-sport
  ""
  [sportid]
  (within-db
    (jdbc/with-query-results rs ["select * from sports where sportid=?" sportid]
                             (first rs)
)
)
)


(defn persist-sport
  ""
  [sportid name]
  (within-db
    (jdbc/update-or-insert-values
      :sports
      ["sportid=?" sportid]
      {:sportid sportid :name name}
)
)
)


(defn find-event
  ""
  [eventid]
  (within-db
    (jdbc/with-query-results rs ["select * from events where eventid=?" eventid]
                             (first rs)
)
)
)


(defn find-event-time-range
  ""
  [eventid]
  (within-db
    (jdbc/with-query-results rs ["select min(time), max(time) from factorevents where eventid=?" eventid]
                             (let [result (first rs)]
                               [(get result :1) (get result :2)]
)
)
)
)


(defn full-event?
  ""
  [eventid]
  (let [[min-time max-time] (find-event-time-range eventid)]
    (if (and min-time max-time)
      (if (and (= min-time 0)
               (> max-time (* 90 60))
)

        true
        false
)

      false
)
)
)


(defn find-event-dates
  ""
  []
  (within-db
    (jdbc/with-query-results rs ["select distinct begindate from events order by begindate desc"]
                             (into [] rs)
)
)
)


(defn find-events
  ""
  []
  (within-db
    (jdbc/with-query-results rs ["select * from events order by eventid desc"]
                             (into [] rs)
)
)
)


(defn find-events-by-begindate
  ""
  [begindate]
  (within-db
    (jdbc/with-query-results rs ["select * from events where begindate=? order by eventid desc" begindate]
                             (into [] rs)
)
)
)


(defn persist-event
  ""
  [eventid sportid name]
  (let [existing-event (find-event eventid)
        begin-date (new java.sql.Date
                        (.getTime (if existing-event
                                    (get existing-event :begindate)
                                    (new java.util.Date)
)
)
)
]
    (within-db
      (jdbc/update-or-insert-values
        :events
        ["eventid=?" eventid]
        {:eventid eventid :sportid sportid :name name :begindate begin-date}
)
)
)
)


(defn delete-event
  ""
  [eventid]
  (within-db
    (jdbc/delete-rows :events ["eventid=?" eventid])
)
)


(defn find-factorevents-by-event-id
  ""
  [eventid]
  (within-db
    (jdbc/with-query-results rs ["select * from factorevents where eventid=? order by timeus" eventid]
                             (into [] rs)
)
)
)


(defn find-factorevent-by-eventid-and-timeus
  ""
  [eventid timeus]
  (within-db
    (jdbc/with-query-results rs ["select * from factorevents where eventid=? and timeus=?" eventid timeus]
                             (first rs)
)
)
)


(defn factor-event-zeroes
  ""
  [factor-event]
  (reduce
    (fn [x y] (and x y))
    (for [[pname] (remove (fn [[x]] (or (= x :timeus) (= x :time))) bet-params)]
      (let [pvalue (get factor-event pname)]
        (if pvalue
          (or (> pvalue 0)
              (< pvalue 0)
)

          nil
)
)
)
)
)


(defn persist-factorevent
  ""
  [{eventid :eventid timeus :timeus :as factorevent}]
  (let [corrected-factorevent (if timeus factorevent (assoc factorevent :timeus 0))]
    ;(println (str "corrected-factorevent" timeus))
   (if (not (factor-event-zeroes corrected-factorevent))
      (within-db
        (jdbc/update-or-insert-values
          :factorevents
          ["eventid=? and timeus=?" eventid timeus]
          (dissoc corrected-factorevent :id)
)
)
)
)
)


(defn delete-factorevents
  ""
  [eventid]
  (within-db
    (jdbc/delete-rows :factorevents ["eventid=?" eventid])
)
)


(defn find-accuracyprofiles
  ""
  []
  (within-db
    (jdbc/with-query-results rs ["select * from accuracyprofiles"]
                             (into [] rs)
)
)
)


(defn compute-accuracyprofile-count
  ""
  []
  (within-db
    (jdbc/with-query-results rs ["select accuracyprofileid, sum (count) sumcount from footballgoalevents group by accuracyprofileid"]
                             (into [] rs)
)
)
)


(defn persist-accuracyprofile
  ""
  [accuracyprofile]
  (within-db
    (jdbc/update-or-insert-values
      :accuracyprofiles
      ["id=?" (get accuracyprofile :id)]
      (into {}
            `(~@(for [param# bet-params]
                  (let [pkey# (first param#)]
                    [pkey# (get accuracyprofile pkey# -1)]
)
)
)
)
)
)
)


(defn find-footballgoalevent
  ""
  [accuracyprofile normalized-event]
  (within-db
    (jdbc/with-query-results rs
                             (vec `(~(apply
                                       str
                                       `("select * from footballgoalevents"
                                          " where accuracyprofileid=?"
                                          ~@(for [param# bet-params]
                                              (str " and " (name (first param#)) "=? ")
)
)
)

                                     ~@`(~(get accuracyprofile :id)
                                          ~@(for [param# bet-params]
                                              (get normalized-event (first param#) -1)
)
)
)
)

                             (first rs)
)
)
)


(defn count-footballgoalevents
  ""
  [accuracyprofile normalized-event]
  (within-db
    (jdbc/with-query-results rs
                             (vec `(~(apply
                                       str
                                       `("select sum(count) as count from footballgoalevents"
                                          " where accuracyprofileid=?"
                                          ~@(for [param# bet-params]
                                              (str " and " (name (first param#)) "=? ")
)
)
)

                                     ~@`(~(get accuracyprofile :id)
                                          ~@(for [param# bet-params]
                                              (get normalized-event (first param#) -1)
)
)
)
)

                             (get (first rs) :count)
)
)
)


(defn normalize-event
  ""
  [accuracyprofile event]
  (into {}
        `(~@(for [param# bet-params]
              (let [pkey# (first param#)
                    pval# (get accuracyprofile pkey# -1)]
                [pkey# (if (= pval# -1) -1 (int (math/floor (/ (get event pkey#) pval#))))]
)
)
)
)
)


(defn add-to-footballgoalevents
  ""
  [accuracyprofile event]
  (let [normalized-event (normalize-event accuracyprofile event)
        footballgoalevent-0 (find-footballgoalevent accuracyprofile normalized-event)
        footballgoalevent-1 (if footballgoalevent-0 footballgoalevent-0 {})
        footballgoalevent (into {}
          `(
             [:accuracyprofileid ~(get accuracyprofile :id)]
             [:count ~(inc (get footballgoalevent-1 :count 0))]
             ~@(for [param# bet-params]
                 (let [pkey# (first param#)]
                   [pkey# (get normalized-event pkey# -1)]
)
)
)
)
]
    (within-db
      (jdbc/update-or-insert-values
        :footballgoalevents
        ["id=?" (get footballgoalevent-1 :id)]
        footballgoalevent
)
)
)
)


(defn find-processingevent
  ""
  [eventid]
  (within-db
    (jdbc/with-query-results rs ["select * from processingevents where eventid=?" eventid]
                             (first rs)
)
)
)


(defn find-processingevents
  ""
  []
  (within-db
    (jdbc/with-query-results rs ["select * from processingevents order by eventid"]
                             (into [] rs)
)
)
)


(defn persist-processingevent
  ""
  [eventid]
  (within-db
    (jdbc/update-or-insert-values
      :processingevents
      ["eventid=?" eventid]
      {:eventid eventid}
)
)
)


(defn delete-processingevent
  ""
  [eventid]
  (within-db
    (jdbc/delete-rows :processingevents ["eventid=?" eventid])
)
)


;;; ddl options

(defn create-table-versions
  ""
  []
  (jdbc/create-table
    :versions
    [:name "varchar(255)" "PRIMARY KEY"]
    [:version :int]
)
)


(defn create-table-sports
  ""
  []
  (jdbc/create-table
    :sports
    [:sportid :int "PRIMARY KEY"]
    [:name "varchar(255)"]
)
)


(defn create-table-events
  ""
  []
  (jdbc/create-table
    :events
    [:eventid :int "PRIMARY KEY"]
    [:sportid :int]
    [:name "varchar(255)"]
    [:begindate :date]
)
)


(defn create-table-factorevents
  ""
  []
  (apply
    jdbc/create-table
    `(:factorevents
       [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]
       [:eventid :int]
       ~@(for [[cname# ctype#] bet-params]
           [cname# ctype#]
)
)
)
)


(defn create-table-accuracyprofiles
  ""
  []
  (apply
    jdbc/create-table
    `(:accuracyprofiles
       [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]
       ~@(for [param# bet-params]
           [(first param#) (second param#)]
)
)
)
)


(defn create-table-footballgoalevents
  ""
  []
  (apply
    jdbc/create-table
    `(:footballgoalevents
       [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]
       [:accuracyprofileid :int]
       [:count :int]
       ~@(for [param# bet-params]
           [(first param#) :int]
)
)
)
)


(defn create-table-processingevents
  ""
  []
  (jdbc/create-table
    :processingevents
    [:eventid :int "PRIMARY KEY"]
)
)


(defn today
  ""
  []
  (quot (.getTime (new java.util.Date)) 86400000)
)


(defn create-database
  ""
  []
  (within-db
    (create-table-versions)
    (create-table-sports)
    (create-table-events)
    (create-table-factorevents)
    (create-table-accuracyprofiles)
    (create-table-footballgoalevents)
    (create-table-processingevents)
)

  (persist-accuracyprofile {:p_total 0.1 :p_bol 0.1 :p_men 0.1})
  (persist-version "app" db-version)
  (persist-version "da" (today))
)


(defn clear-database
  ""
  []
  (within-db
    (exceptionable (jdbc/drop-table :versions))
    (exceptionable (jdbc/drop-table :sports))
    (exceptionable (jdbc/drop-table :events))
    (exceptionable (jdbc/drop-table :factorevents))
    (exceptionable (jdbc/drop-table :accuracyprofiles))
    (exceptionable (jdbc/drop-table :footballgoalevents))
    (exceptionable (jdbc/drop-table :processingevents))
)
)


(defn reinitialize-database
  ""
  []
  (println "Reinitializing database.")
  (clear-database)
  (create-database)
)


(defn clear-inconsistent-events
  ""
  []
  (let [events (find-events)]
    (doseq [{eventid :eventid} events]
      (when (not (full-event? eventid))
        (println (str "Removing inconsistent event " eventid))
        (delete-factorevents eventid)
        (delete-event eventid)
)
)
)
)
@2009-2013 lisper.ru