--
-- Tigase Unified Archive Component - Extension of implementation of Message Archiving component for Tigase XMPP Server
-- Copyright (C) 2015 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

-- LOAD FILE: database/derby-unified-archive-2.1.0.sql;

-- LOAD FILE: database/derby-message-archiving-3.0.0.sql;

-- QUERY START:
create table tig_ua_file_metadata (
	stable_id varchar(36) not null,
	owner_id bigint not null,
	url_hash varchar(40) not null,
	url varchar(2048) not null,
	name varchar(255),
	description varchar(1024),
	size bigint,
	media_type varchar(50),


	primary key (owner_id, stable_id, url_hash),
	foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
	foreign key (owner_id) references tig_ma_jids (jid_id)
);
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FileMetadata_AddItem(ownerJid varchar(2049), stableId varchar(36), urlHash varchar(40), url varchar(2048), mediaType varchar(50), "name" varchar(255), "description" varchar(1024), size bigint)
    PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.fm_AddItem';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FileMetadata_QueryItems(domain varchar(1024), ownerJid varchar(2049), buddyJid varchar(2049), "after" timestamp, "before" timestamp, "contains" varchar(100), "mediaTypeContains" varchar(50), "smallerThan" bigint, "biggerThan" bigint, "limit" int, "offset" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.fm_QueryItems';
-- QUERY END:


-- QUERY START:
create procedure Tig_UA_FileMetadata_QueryItemsCount(domain varchar(1024), ownerJid varchar(2049), buddyJid varchar(2049), "after" timestamp, "before" timestamp, "contains" varchar(100), "mediaTypeContains" varchar(50), "smallerThan" bigint, "biggerThan" bigint, "beforeStableId" varchar(36))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
    DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.fm_QueryItemsCount';
-- QUERY END:

-- QUERY START:
call TigSetComponentVersion('unified-archive', '3.0.0');
-- QUERY END:
