
-- 
-- MirrorBrain Database scheme for PostgreSQL
-- 

-- before PL/pgSQL functions can be used, the languages needs to be "installed"
-- in the database. This is done with:
-- 
-- createlang plpgsql <dbname>

-- --------------------------------------------------------
BEGIN;
-- --------------------------------------------------------


CREATE TABLE "version" (
        "id" serial NOT NULL PRIMARY KEY,
        "component" text NOT NULL,
        "major" INTEGER NOT NULL,
        "minor" INTEGER NOT NULL,
        "patchlevel" INTEGER NOT NULL
);

-- --------------------------------------------------------


CREATE TABLE "filearr" (
        "id" serial NOT NULL PRIMARY KEY,
        "path" varchar(512) UNIQUE NOT NULL,
        "mirrors" smallint[]
);

-- --------------------------------------------------------


CREATE TABLE "hash" (
        "file_id" INTEGER REFERENCES filearr PRIMARY KEY,
        "mtime" INTEGER NOT NULL,
        "size" BIGINT NOT NULL,
        "md5"    BYTEA NOT NULL,
        "sha1"   BYTEA NOT NULL,
        "sha256" BYTEA NOT NULL,
        "sha1piecesize" INTEGER NOT NULL,
        "sha1pieces" BYTEA NOT NULL,
        "btih"   BYTEA NOT NULL,
        "pgp" TEXT NOT NULL,
        "zblocksize" SMALLINT NOT NULL,
        "zhashlens" VARCHAR(8),
        "zsums" BYTEA NOT NULL
);

-- For conveniency, this view provides the binary columns from the "hash" table
-- also encoded in hex
--
-- Note on binary data (bytea) column.
-- PostgreSQL escapes binary (bytea) data on output. But hex encoding is more
-- efficient (it results in shorter strings, and thus less data to transfer
-- over the wire, and it's also faster). The escape format doesn't make sense
-- for a new application (which we are).
-- On the other hand, storage in bytea is as compact as it can be, which is good.
-- The hex encoding function in PostgreSQL seems to be fast.
CREATE VIEW hexhash AS 
  SELECT file_id, mtime, size, 
         md5,
         encode(md5, 'hex') AS md5hex, 
         sha1,
         encode(sha1, 'hex') AS sha1hex, 
         sha256,
         encode(sha256, 'hex') AS sha256hex, 
         sha1piecesize, 
         sha1pieces,
         encode(sha1pieces, 'hex') AS sha1pieceshex,
         btih,
         encode(btih, 'hex') AS btihhex, 
         pgp,
         zblocksize,
         zhashlens,
         zsums,
         encode(zsums, 'hex') AS zsumshex
  FROM hash;

-- --------------------------------------------------------


CREATE TABLE "server" (
        "id" serial NOT NULL PRIMARY KEY,
        "identifier" varchar(64) NOT NULL UNIQUE,
        "baseurl"       varchar(128) NOT NULL,
        "baseurl_ftp"   varchar(128) NOT NULL,
        "baseurl_rsync" varchar(128) NOT NULL,
        "enabled"        boolean NOT NULL,
        "status_baseurl" boolean NOT NULL,
        "region"  varchar(2) NOT NULL,
        "country" varchar(2) NOT NULL,
        "asn" integer NOT NULL,
        "prefix" varchar(18) NOT NULL,
        "ipv6_only" boolean NOT NULL default 'f',
        "score" smallint NOT NULL,
        "scan_fpm" integer NOT NULL,
        "last_scan" timestamp with time zone NULL,
        "comment" text NOT NULL,
        "operator_name" varchar(128) NOT NULL,
        "operator_url" varchar(128) NOT NULL,
        "public_notes" varchar(512) NOT NULL,
        "admin"       varchar(128) NOT NULL,
        "admin_email" varchar(128) NOT NULL,
        "lat" numeric(6, 3) NULL,
        "lng" numeric(6, 3) NULL,
        "country_only" boolean NOT NULL,
        "region_only" boolean NOT NULL,
        "as_only" boolean NOT NULL,
        "prefix_only" boolean NOT NULL,
        "other_countries" varchar(512) NOT NULL,
        "file_maxsize" integer NOT NULL default 0
);

CREATE INDEX "server_enabled_status_baseurl_score_key" ON "server" (
        "enabled", "status_baseurl", "score"
);

-- --------------------------------------------------------


CREATE TABLE "marker" (
        "id" serial NOT NULL PRIMARY KEY,
        "subtree_name" varchar(128) NOT NULL,
        "markers" varchar(512) NOT NULL
);

-- --------------------------------------------------------

CREATE TABLE "country" (
        "id" serial NOT NULL PRIMARY KEY,
        "code" varchar(2) NOT NULL,
        "name" varchar(64) NOT NULL
);

CREATE TABLE "region" (
        "id" serial NOT NULL PRIMARY KEY,
        "code" varchar(2) NOT NULL,
        "name" varchar(64) NOT NULL
);





-- add a mirror to the list of mirrors where a file was seen
CREATE OR REPLACE FUNCTION mirr_add_byid(arg_serverid integer, arg_fileid integer) RETURNS integer AS $$
DECLARE
    arr smallint[];
BEGIN
    SELECT INTO arr mirrors FROM filearr WHERE id = arg_fileid;
    IF arg_serverid = ANY(arr) THEN
        RAISE DEBUG 'already there -- nothing to do';
        RETURN 0;
    ELSE
        arr := array_append(arr, arg_serverid::smallint);
        RAISE DEBUG 'arr: %', arr;
        update filearr set mirrors = arr where id = arg_fileid;
        return 1;
    END IF;
END;
$$ LANGUAGE plpgsql;


-- remove a mirror from the list of mirrors where a file was seen
CREATE OR REPLACE FUNCTION mirr_del_byid(arg_serverid integer, arg_fileid integer) RETURNS integer AS $$
DECLARE
    arr smallint[];
BEGIN
    SELECT INTO arr mirrors FROM filearr WHERE id = arg_fileid;

    IF NOT arg_serverid = ANY(arr) THEN
        -- it's not there - nothing to do
        RAISE DEBUG 'not there -- nothing to do';
        RETURN 0;
    ELSE
        arr := ARRAY(
                    SELECT arr[i] 
                    FROM generate_series(array_lower(arr, 1), array_upper(arr, 1)) 
                    AS i 
                    WHERE arr[i] <> arg_serverid
                );
        RAISE DEBUG 'arr: %', arr;
        -- update the array in the table
        -- if arr is empty, we could actually remove the row instead, thus deleting the file
        UPDATE filearr 
            SET mirrors = arr WHERE id = arg_fileid;
        RETURN 1;
    END IF;
END;
$$ LANGUAGE plpgsql;


-- check whether a given mirror is known to have a file (id)
CREATE OR REPLACE FUNCTION mirr_hasfile_byid(arg_serverid integer, arg_fileid integer) RETURNS boolean AS $$
DECLARE
    result integer;
BEGIN
    SELECT INTO result 1 FROM filearr WHERE id = arg_fileid AND arg_serverid = ANY(mirrors);
    IF result > 0 THEN
        RETURN true;
    END IF;
    RETURN false;
END;
$$ LANGUAGE plpgsql;

-- check whether a given mirror is known to have a file (name)
CREATE OR REPLACE FUNCTION mirr_hasfile_byname(arg_serverid integer, arg_path text) RETURNS boolean AS $$
DECLARE
    result integer;
BEGIN
    SELECT INTO result 1 FROM filearr WHERE path = arg_path AND arg_serverid = ANY(mirrors);
    IF result > 0 THEN
        RETURN true;
    END IF;
    RETURN false;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION mirr_add_bypath(arg_serverid integer, arg_path text) RETURNS integer AS $$
DECLARE
    fileid integer;
    arr smallint[];
BEGIN
    SELECT INTO fileid, arr
        id, mirrors FROM filearr WHERE path = arg_path;

    -- There are three cases to handle, and we want to handle each of them
    -- with the minimal effort.
    -- In any case, we return a file id in the end.
    IF arg_serverid = ANY(arr) THEN
        RAISE DEBUG 'nothing to do';
    ELSIF fileid IS NULL THEN
        RAISE DEBUG 'creating entry for new file.';
        INSERT INTO filearr (path, mirrors) VALUES (arg_path, ARRAY[arg_serverid]);
        fileid := currval('filearr_id_seq');
    ELSE
        RAISE DEBUG 'update existing file entry (id: %)', fileid;
        arr := array_append(arr, arg_serverid::smallint);
        update filearr set mirrors = arr where id = fileid;
    END IF;

    RETURN fileid;
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'file % was just inserted by somebody else', arg_path;
        -- just update it by calling ourselves again
        SELECT into fileid mirr_add_bypath(arg_serverid, arg_path);
        RETURN fileid;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION mirr_get_name(integer) RETURNS text AS '
    SELECT identifier FROM server WHERE id=$1
' LANGUAGE SQL;


CREATE OR REPLACE FUNCTION mirr_get_name(ids smallint[]) RETURNS text[] AS $$
DECLARE
    names text[];
    -- i integer;
BEGIN
    names := ARRAY(
                  select mirr_get_name(cast(ids[i] AS integer)) from generate_series(array_lower(ids, 1), array_upper(ids, 1)) as i
                  );
    RETURN names;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION mirr_get_nfiles(integer) RETURNS bigint AS '
    SELECT count(*) FROM filearr WHERE $1 = ANY(mirrors)
' LANGUAGE SQL;

CREATE OR REPLACE FUNCTION mirr_get_nfiles(text) RETURNS bigint AS '
    SELECT count(*) FROM filearr WHERE (SELECT id from server where identifier = $1) = ANY(mirrors)
' LANGUAGE SQL;


-- --------------------------------------------------------
COMMIT;
-- --------------------------------------------------------

-- vim: ft=sql ai ts=4 sw=4 smarttab expandtab
