<commit_before>-- $Id: netarchivesuite_init.sql 1414 2010-05-31 15:52:06Z ngiraud $
-- $Revision: 1414 $
-- $Date: 2010-05-31 17:52:06 +0200 (Mon, 31 May 2010) $
-- $Author: ngiraud $

-- PostgreSQL creation scripts
-- presupposes PostgresSQL 8.3+
-- tested on PostgreSQL 8.4.1-1 (Ubuntu 9.10)

-- Note: it is recommended by DBAs to stores indices in a separate tablespace
-- This script assumes that a tablespace 'tsindex' has been created for this
-- purpose. In order to create a tablespace, the following procedure should be
-- used (tested on Ubuntu 9.10):
--
-- identify the data directory (cf. /etc/postgresql/8.4/main//postgresql.conf)
-- execute the following commands :
--      PG_DATA=/var/lib/postgresql/8.4/main
--      sudo mkdir $PG_DATA/tsindex
--      sudo chown postgres:postgres $PG_DATA/tsindex
-- start psql : psql -U postgres -W
-- execute the query :
--      create tablespace tsindex location '/var/lib/postgresql/8.4/main/tsindex';

--
-- How to use:
-- psql -U <user name> -W [DB name] < netarchivesuite_init.sql

CREATE SCHEMA netarchivesuite;

SET search_path TO netarchivesuite;

--------------------------------------------------------------------------------
-- Create user for application and add privileges
--------------------------------------------------------------------------------

CREATE ROLE netarchivesuite PASSWORD '' LOGIN;
GRANT USAGE ON SCHEMA netarchivesuite TO netarchivesuite;

-- *****************************************************************************
-- Area: Basics
-- *****************************************************************************

CREATE TABLE schemaversions (
    tablename varchar(100) NOT NULL,
    version int NOT NULL
);

INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'domains', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'configurations', 5);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'seedlists', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'passwords', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'ownerinfo', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'historyinfo', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'config_passwords', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'config_seedlists', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'harvestdefinitions', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'partialharvests', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'fullharvests', 4);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'harvest_configs', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'schedules', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'ordertemplates', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'jobs', 6);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'job_configs', 1);
INSERT INTO schemaversions (tablename, version )
    VALUES ( 'global_crawler_trap_lists', 1);
INSERT INTO schemaversions (tablename, version )
    VALUES ( 'global_crawler_trap_expressions', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'runningjobshistory', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'runningmobsmonitor', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'frontierreportmonitor', 1);


GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE schemaversions TO netarchivesuite;

-- *****************************************************************************
-- Area: Domains
-- *****************************************************************************

CREATE TABLE domains (
    domain_id bigint NOT NULL PRIMARY KEY,
    name varchar(300) NOT NULL UNIQUE,
    comments varchar(30000),
    defaultconfig bigint NOT NULL,
    crawlertraps varchar(1000),
    edition bigint NOT NULL,
    alias bigint,
    lastaliasupdate timestamp
);

CREATE SEQUENCE domains_id_seq OWNED BY domains.domain_id;
ALTER TABLE domains ALTER COLUMN domain_id SET DEFAULT NEXTVAL('domains_id_seq');

CREATE INDEX domainnameid on domains(name) TABLESPACE tsindex;
CREATE INDEX aliasindex on domains(alias) TABLESPACE tsindex;

GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE domains TO netarchivesuite;
GRANT USAGE ON SEQUENCE domains_id_seq TO netarchivesuite;

-- -----------------------------------------------------------------------------<commit_msg>[FROM NICOLAS@BNF]<commit_after>-- $Id: netarchivesuite_init.sql 1414 2010-05-31 15:52:06Z ngiraud $
-- $Revision: 1414 $
-- $Date: 2010-05-31 17:52:06 +0200 (Mon, 31 May 2010) $
-- $Author: ngiraud $

-- PostgreSQL creation scripts
-- presupposes PostgresSQL 8.3+
-- tested on PostgreSQL 8.4.1-1 (Ubuntu 9.10)

-- Note: it is recommended by DBAs to stores indices in a separate tablespace
-- This script assumes that a tablespace 'tsindex' has been created for this
-- purpose. In order to create a tablespace, the following procedure should be
-- used (tested on Ubuntu 9.10):
--
-- identify the data directory (cf. /etc/postgresql/8.4/main//postgresql.conf)
-- execute the following commands :
--      PG_DATA=/var/lib/postgresql/8.4/main
--      sudo mkdir $PG_DATA/tsindex
--      sudo chown postgres:postgres $PG_DATA/tsindex
-- start psql : psql -U postgres -W
-- execute the query :
--      create tablespace tsindex location '/var/lib/postgresql/8.4/main/tsindex';

--
-- How to use:
-- psql -U <user name> -W [DB name] < netarchivesuite_init.sql

CREATE SCHEMA netarchivesuite;

SET search_path TO netarchivesuite;

--------------------------------------------------------------------------------
-- Create user for application and add privileges
--------------------------------------------------------------------------------

CREATE ROLE netarchivesuite PASSWORD '' LOGIN;
GRANT USAGE ON SCHEMA netarchivesuite TO netarchivesuite;

-- *****************************************************************************
-- Area: Basics
-- *****************************************************************************

CREATE TABLE schemaversions (
    tablename varchar(100) NOT NULL,
    version int NOT NULL
);

INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'domains', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'configurations', 5);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'seedlists', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'passwords', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'ownerinfo', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'historyinfo', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'config_passwords', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'config_seedlists', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'harvestdefinitions', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'partialharvests', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'fullharvests', 4);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'harvest_configs', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'schedules', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'ordertemplates', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'jobs', 6);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'job_configs', 1);
INSERT INTO schemaversions (tablename, version )
    VALUES ( 'global_crawler_trap_lists', 1);
INSERT INTO schemaversions (tablename, version )
    VALUES ( 'global_crawler_trap_expressions', 1);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'runningjobshistory', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'runningjobsmonitor', 2);
INSERT INTO schemaversions ( tablename, version )
    VALUES ( 'frontierreportmonitor', 1);


GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE schemaversions TO netarchivesuite;

-- *****************************************************************************
-- Area: Domains
-- *****************************************************************************

CREATE TABLE domains (
    domain_id bigint NOT NULL PRIMARY KEY,
    name varchar(300) NOT NULL UNIQUE,
    comments varchar(30000),
    defaultconfig bigint NOT NULL,
    crawlertraps varchar(1000),
    edition bigint NOT NULL,
    alias bigint,
    lastaliasupdate timestamp
);

CREATE SEQUENCE domains_id_seq OWNED BY domains.domain_id;
ALTER TABLE domains ALTER COLUMN domain_id SET DEFAULT NEXTVAL('domains_id_seq');

CREATE INDEX domainnameid on domains(name) TABLESPACE tsindex;
CREATE INDEX aliasindex on domains(alias) TABLESPACE tsindex;

GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE domains TO netarchivesuite;
GRANT USAGE ON SEQUENCE domains_id_seq TO netarchivesuite;

-- -----------------------------------------------------------------------------
{'fraction_non_alphanumeric': 0.20781370709634697, 'fraction_numerical': 0.02207261891623441, 'mean_word_length': 3.446516192345437, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4142311', 'n_tokens_mistral': 2771, 'n_tokens_neox': 2502, 'n_words': 811}