;;; pg-sumodb.el --- Load Sumo database into Postgres

;; Copyright (C) 2000 by Free Software Foundation, Inc.

;; Author: Steven Baur <steve@zaizen.m17n.org>
;; Keywords: data

;; This file is part of XEmacs.

;; XEmacs is free software; you can redistribute it and/or modify it
;; under the terms of the GNU General Public License as published by
;; the Free Software Foundation; either version 2, or (at your option)
;; any later version.

;; XEmacs is distributed in the hope that it will be useful, but
;; WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
;; General Public License for more details.

;; You should have received a copy of the GNU General Public License
;; along with XEmacs; see the file COPYING.  If not, write to the Free
;; Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
;; 02111-1307, USA.

;;; Synched up with: Not in FSF

;;; Commentary:

;; This file loads Sumo tournament results into a Postgres Database.
;; Download the data from http://www.scgroup.com/sumo/ into a directory and
;; then do `M-x pg-sumodb-load'.

;;; Code:

(require 'pg-utils)

(defconst pg-sumodb-data-files '(("Kimarite.dat" . pg-sumodb-kimarite)
				 ("allbasho.dat" . pg-sumodb-allbasho)
				 ("rankwins.dat" . pg-sumodb-rankwins)
				 ("allrslt.dat" . pg-sumodb-allrslt))
  "Sumo data files to load.")

(defun pg-sumodb-allrslt (P file)
  "Load the all-results data file."
  (condition-case nil
      (pq-exec P "DROP TABLE all_results;")
    (t nil))
  (pq-exec P "BEGIN;")
  (unwind-protect
      (progn
	(pq-exec P (concat "CREATE TABLE all_results "
			   "(rikishi text,"
			   " shikona text,"
			   " basho int,"
			   " result int,"
			   " kimarite int,"
			   " day int);"))
	(with-temp-buffer
	  (insert-file-contents file)
	  (let ((pg-utils-default-connection P))
	    (pg-utils-do-copy-in "all_results" (current-buffer) ":"))))
    (pq-exec P "END;")))

(defun pg-sumodb-rankwins (P file)
  "Load the rank & wins data file."
  (condition-case nil
      (pq-exec P "DROP TABLE rank_wins;")
    (t nil))
  (pq-exec P "BEGIN;")
  (unwind-protect
      (progn
	(pq-exec P (concat "CREATE TABLE rank_wins "
			   "(shikona text,"
			   " basho int,"
			   " rank char(4),"
			   " wins int,"
			   " losses int);"))
	(with-temp-buffer
	  (insert-file-contents file)
	  (let ((pg-utils-default-connection P))
	    (pg-utils-do-copy-in "rank_wins" (current-buffer) ":"))))
    (pq-exec P "END;")))

(defun pg-sumodb-allbasho (P file)
  "Load the basho data"
  (condition-case nil
      (pq-exec P "DROP TABLE allbasho;")
    (t nil))
  (pq-exec P "BEGIN;")
  (unwind-protect
      (progn
	(pq-exec P (concat "CREATE TABLE allbasho "
			   "(number int PRIMARY KEY,"
			   " year int,"
			   " location text);"))
	(with-temp-buffer
	  (insert-file-contents-literally file)
	  (goto-char (point-min))
	  (let ((i 0))
	    (while (< (point) (point-max))
	      (beginning-of-line)
	      (insert (format "%d " i))
	      (forward-line)
	      (incf i)))
	  (let ((pg-utils-default-connection P))
	    (pg-utils-do-copy-in "allbasho" (current-buffer) " "))))
    (pq-exec P "END;")))

(defun pg-sumodb-kimarite (P file)
  "Load the kimarite data"
  (condition-case nil
      (pq-exec P "DROP TABLE kimarite;")
    (t nil))
  (pq-exec P "BEGIN;")
  (unwind-protect
      (progn
	(pq-exec P (concat "CREATE TABLE kimarite "
			   "(number int PRIMARY KEY,"
			   " reading text,"
			   " kimarite text);"))
	(with-temp-buffer
	  (insert-file-contents-literally file)
	  (goto-char (point-min))
	  (let ((i 0))
	    (while (< (point) (point-max))
	      (beginning-of-line)
	      (insert (format "%d " i))
	      (forward-line)
	      (incf i)))
	  (let ((pg-utils-default-connection P))
	    (pg-utils-do-copy-in "kimarite" (current-buffer) " "))))
    (pq-exec P "END;")))

(defun pg-sumodb-load (conninfo where)
  "Load the sumo database."
  (interactive "sConnect to: \nsData file directory: ")
  (require 'postgresql)
  (let ((temp pg-sumodb-data-files)
	value db)
    (setq db (pq-connectdb conninfo))
    (unwind-protect
	(progn
	  (while (setq value (pop temp))
	    (message "Processing %s ..." (car value))
	    (funcall (cdr value) db (expand-file-name (car value)
						      where)))
	  (message "Done."))
      (pq-finish db))))

(provide 'pg-sumodb)

;;; pg-sumodb.el ends here