artsite.sql

---- Primary Entities

-- You search by these
create table tag (
	id serial primary key,
	name varchar not null,
	unique (name)
);

-- The primary post type
create table file (
	id serial primary key,
	sha256 bytea not null,
	ext varchar not null,
	unique (sha256)
);

-- These tags apply to the situation.  They're called "scene tags" in the ui, and they should be rarely used.
create table file_tag (
	file_id integer references file (id) on delete cascade,
	tag_id integer references tag (id) on delete cascade,
	primary key (file_id, tag_id)
);


-- Characters and objects.  Also artists/commissioners are things.
create table thing (
	id serial primary key,
	name varchar not null
	unique (name)
);

-- Things can have tags
create table thing_tag (
	thing_id integer references thing (id) on delete cascade,
	tag_id integer references tag (id) on delete cascade,
	primary key (thing_id, tag_id)
);

-- Represents a thing in a picture.  Can be a generic, anonymous thing though, if the thing_id is null.
create table appearance (
	id serial primary key,
	file_id integer not null references file (id) on delete cascade,
	thing_id integer references thing (id) on delete cascade,
);

-- Apply tags to an appearance.  If the appearance has a thing_id,
-- it should inherit those tags in addition to these.
-- You can also apply anti-tags here to counter-act the usual appearance.
create table appearance_tag (
	appearance_id integer references appearance (id) on delete cascade,
	tag_id integer references tag (id) on delete cascade,
	is_anti_tag boolean not null default 'f',
	primary key (appearance_id, tag_id)
);

-- Tracking artists / commissioners / etc

create table file_artist (
	file_id integer not null references file (id) on delete cascade,
	thing_id integer not null references thing (id) on delete cascade,
)

create table file_commissoner (
	file_id integer not null references file (id) on delete cascade,
	thing_id integer not null references thing (id) on delete cascade,
)

find_appearances.sql

select file.*
from file
join appearance on appearance.file_id = file.id
where appearance.id in
(
    select appearance_id from
    (
        select
        appearance_id,
        count(*) as tag_count
        from (
            -- thing tags --
            select
            appearance.id as appearance_id,
            tag.name as tag_name
            from appearance
            join thing on appearance.thing_id = thing.id
            join thing_tag on thing_tag.thing_id = thing.id
            join tag on thing_tag.tag_id = tag.id

            union

            -- thing name --
            select
            appearance.id as appearance_id,
            thing.name as tag_name
            from appearance
            join thing on appearance.thing_id = thing.id

            union

            -- appearance tags --
            select
            appearance.id as appearance_id,
            tag.name as tag_name
            from appearance
            join appearance_tag on appearance_tag.appearance_id = appearance.id
            join tag on appearance_tag.tag_id = tag.id
            where appearance_tag.negative = 'f'

            except

            -- negative appearance tags --
            select
            appearance.id as appearance_id,
            tag.name as tag_name
            from appearance
            join appearance_tag on appearance_tag.appearance_id = appearance.id
            join tag on appearance_tag.tag_id = tag.id
            where appearance_tag.negative = 't'
        ) as calculated_appearance_tag
        where tag_name in :tags
        group by appearance_id
    ) as appearance_match
    where appearance_match.tag_count = :tag_count
)

general_search.sql

select file.*
from file
where file.id in
(
    select
    file_id
    from (
        -- thing name --
        select
        file.id as file_id,
        thing.name as tag_name
        from file
        join appearance on appearance.file_id = file.id
        join thing on appearance.thing_id = thing.id

        union

        -- thing tags --
        select
        file.id as file_id,
        tag.name as tag_name
        from file
        join appearance on appearance.file_id = file.id
        join thing on appearance.thing_id = thing.id
        join thing_tag on thing_tag.thing_id = thing.id
        join tag on thing_tag.tag_id = tag.id

        union

        -- appearance tags --
        select
        file.id as file_id,
        tag.name as tag_name
        from file
        join appearance on appearance.file_id = file.id
        join appearance_tag on appearance_tag.appearance_id = appearance.id
        join tag on appearance_tag.tag_id = tag.id
        where appearance_tag.negative = 'f'

        except

        -- negative appearance tags --
        select
        file.id as file_id,
        tag.name as tag_name
        from file
        join appearance on appearance.file_id = file.id
        join appearance_tag on appearance_tag.appearance_id = appearance.id
        join tag on appearance_tag.tag_id = tag.id
        where appearance_tag.negative = 't'
    ) as calculated_file_tag
    where tag_name in :tags
    group by file_id
    having count(*) = :tag_count
)

multiple_appearances.sql

with calculated_appearance_tag as (
    -- thing tags --
    select
    appearance.id as appearance_id,
    tag.name as tag_name
    from appearance
    join thing on appearance.thing_id = thing.id
    join thing_tag on thing_tag.thing_id = thing.id
    join tag on thing_tag.tag_id = tag.id

    union

    -- thing name --
    select
    appearance.id as appearance_id,
    thing.name as tag_name
    from appearance
    join thing on appearance.thing_id = thing.id

    union

    -- appearance tags --
    select
    appearance.id as appearance_id,
    tag.name as tag_name
    from appearance
    join appearance_tag on appearance_tag.appearance_id = appearance.id
    join tag on appearance_tag.tag_id = tag.id
    where appearance_tag.negative = 'f'

    except

    -- negative appearance tags --
    select
    appearance.id as appearance_id,
    tag.name as tag_name
    from appearance
    join appearance_tag on appearance_tag.appearance_id = appearance.id
    join tag on appearance_tag.tag_id = tag.id
    where appearance_tag.negative = 't'
)
select file.*
from file
where file.id in
(
    select
    appearance.file_id as file_id
    from
    (
        -- repeat this stanza for each appearance described in the search --
        select
        appearance_id
        from calculated_appearance_tag
        where tag_name in ('one','two')
        group by appearance_id
        having count(*) = 2

        -- join by union --
        union

        -- this is a second stanza --
        select
        appearance_id
        from calculated_appearance_tag
        where tag_name in ('three','four','five')
        group by appearance_id
        having count(*) = 3
    ) as appearance_match
    join appearance on appearance_match.appearance_id = appearance.id
    group by appearance.file_id
    having count(*) = 2
)