-- 
-- mysql -u root -p < db.schema.eprintstats.sql
--

/*
* Load schema for eprintstats project.
* 
*/

drop database if exists eprintstats;
create database eprintstats;
use eprintstats;

/* See end for permissions statements */

DROP TABLE IF EXISTS view;
CREATE TABLE view (
	id int(10) unsigned NOT NULL auto_increment,
	uniquebits varchar(200) , /* UNIQUE? to avoid multiple inputs */
	archive_name varchar(50) NOT NULL, /* mysql archive name unimelb,alia etc */
	ip varchar(100) NOT NULL, /* ip of request */ 
	request_date date NOT NULL,
	archiveid int(10) NOT NULL, /* Id of requested archive */
	country_code varchar(4), /* 2 letter iso3166 country code */
	country_name varchar(100), /* Country name iso3166 */
	view_type ENUM("abstract","download"),
	eprint_name varchar(200),
	PRIMARY KEY (id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS lastproc;
CREATE TABLE lastproc (
	id int(10) unsigned NOT NULL auto_increment,
	timeinsert timestamp,
	lastproc varchar(100),
	PRIMARY KEY (id)
) TYPE=MyISAM;


/* Add db user and password. */
/* make sure the user/pass matches the inc.vars.es.php file and the 2 scripts in the scripts directory. */
grant all on eprintstats.* to eprintstatspriv identified by 'AuldGrizzel';
grant select on eprintstats.* to eprintstatspub identified by 'AuldGrizzel';
flush privileges;


/* NJS 2009-04-29
   Add indexes to improve query performance. These may become an issue if
   hit rates are very high (=> many INSERTs).
*/

-- This seems to be the most effective (greatest impact).
create index archiveid_idx on view ( archiveid );