-- Registry Relational Schema, version 2013-03-05

-- Marco Molinaro, INAF-OATs IA2

-- NOTE: 
-- introduces the non standard resource.harvested_from field
-- includes example inplementations for User Defined Functions: ivo_hashlist_has, ivo_hasword, ivo_nocasematch


CREATE SCHEMA IF NOT EXISTS `rr` DEFAULT CHARACTER SET latin1 ;
USE `rr` ;

-- -----------------------------------------------------
-- Table `rr`.`resource`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`resource` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'Unambiguous reference to the resource conforming to the IVOA standard for identifiers' ,
  `res_type` VARCHAR(32) NOT NULL COMMENT 'Resource type (something like vs:datacollection, vs:catalogservice, etc)' ,
  `created` DATETIME NOT NULL DEFAULT '1970-01-01T00:00:01.0' COMMENT 'The UTC date and time this resource metadata description was created. This timestamp must not be in the future. This time is not required to be accurate; it should be at least accurate to the day. Any insignificant time fields should be set to zero.' ,
  `short_name` VARCHAR(16) NULL COMMENT 'A short name or abbreviation given to something. This name will be used where brief annotations for the resource name are required. Applications may use to refer to this resource in a compact display. One word or a few letters is recommended. No more than sixteen characters are allowed.' ,
  `res_title` VARCHAR(256) NOT NULL COMMENT 'The full name given to the resource' ,
  `updated` DATETIME NOT NULL DEFAULT '1970-01-01T00:00:01.0' COMMENT 'The UTC date this resource metadata description was last updated. This timestamp must not be in the future. This time is not required to be accurate; it should be at least accurate to the day. Any insignificant time fields should be set to zero.' ,
  `content_level` VARCHAR(256) NULL DEFAULT '' COMMENT 'hashlist - Description of the content level or intended audience' ,
  `res_description` TEXT NOT NULL COMMENT 'An account of the nature of the resource.' ,
  `reference_url` VARCHAR(256) NOT NULL COMMENT 'URL pointing to a human-readable document describing this resource.' ,
  `creator_seq` TEXT NULL DEFAULT NULL COMMENT 'The creator(s) of the resource in the order given by the resource record author.' ,
  `content_type` VARCHAR(256) NULL DEFAULT '' COMMENT 'hashlist - Nature or genre of the content of the resource' ,
  `source_format` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The format of source_value. Recognized values include \"bibcode\", referring to a standard astronomical bibcode (http://cdsweb.u-strasbg.fr/simbad/refcode.html).' ,
  `source_value` VARCHAR(128) NULL DEFAULT NULL COMMENT 'A bibliographic reference from which the present resource is derived or extracted.' ,
  `version` VARCHAR(128) NULL DEFAULT NULL COMMENT 'Label associated with creation or availablilty of a version of a resource.' ,
  `region_of_regard` FLOAT NULL DEFAULT NULL COMMENT 'A single numeric value representing the angle, given in decimal degrees, by which a positional query against this resource should be \"blurred\" in order to get an appropriate match.' ,
  `waveband` VARCHAR(64) NULL DEFAULT NULL COMMENT 'hashlist - A hash-separated list of regions of the electro-magnetic spectrum that the resource\'s spectral coverage overlaps with.' ,
  `rights` VARCHAR(32) NULL DEFAULT NULL COMMENT 'hashlist - Information about rights held in and over the resource.' ,
  `harvested_from` VARCHAR(256) NULL DEFAULT NULL COMMENT 'non std - ivo-id of the registry the resource is managed by' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'The resources, i.e., services, data collections, organizations, etc., present in this registry.';

ALTER TABLE `rr`.`resource` ADD PRIMARY KEY (`ivoid`) ;


-- -----------------------------------------------------
-- Table `rr`.`capability`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`capability` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `cap_index` SMALLINT(6) NOT NULL COMMENT 'Running number of this capability within the resource.' ,
  `cap_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The type of capability covered here.' ,
  `cap_description` VARCHAR(256) NULL DEFAULT NULL COMMENT 'A human-readable description of what this capability provides as part of the over-all service' ,
  `standard_id` VARCHAR(256) NULL DEFAULT NULL COMMENT 'A URI for a standard this capability conforms to.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Pieces of behaviour of a resource';

ALTER TABLE `rr`.`capability` ADD PRIMARY KEY (`ivoid`, `cap_index`) ;
ALTER TABLE `rr`.`capability` 
    ADD CONSTRAINT `capability_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `capability_cap_type` ON `rr`.`capability` (`cap_type` ASC) ;
CREATE INDEX `capability_standard_id` ON `rr`.`capability` (`standard_id` ASC) ;
CREATE INDEX `capability_ivoid_resource` ON `rr`.`capability` (`ivoid` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`interface`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`interface` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `cap_index` SMALLINT(6) NULL COMMENT 'The index of the parent capability; may be NULL for interfaces in resource (e.g., StandardsRegExt)' ,
  `intf_index` SMALLINT(6) NOT NULL COMMENT 'A running number for the interfaces of a capability.' ,
  `intf_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The type of the interface (WebBrowser, ParamHTTP, etc).' ,
  `intf_role` VARCHAR(32) NULL DEFAULT NULL COMMENT 'A tag name the identifies the role the interface plays in the particular capability. If the value is equal to \"std\" or begins with \"std:\", then the interface refers to a standard interface defined by the standard referred to by the capability\'s standardID attribute.' ,
  `std_version` VARCHAR(16) NULL DEFAULT NULL COMMENT 'The version of a standard interface specification that this interface complies with. When the interface is provided in the context of a Capability element, then the standard being refered to is the one identified by the Capability\'s standardID element.' ,
  `query_type` VARCHAR(4) NULL DEFAULT NULL COMMENT 'The type of HTTP request, either GET or POST.' ,
  `result_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The MIME type of a document returned in the HTTP response.' ,
  `wsdl_url` VARCHAR(256) NULL DEFAULT NULL COMMENT 'The location of the WSDL that describes this Web Service. If not provided, the location is assumed to be the accessURL with \'?wsdl\' appended.' ,
  `url_use` VARCHAR(4) NOT NULL COMMENT 'A flag indicating whether this should be interpreted as a base URL, a full URL, or a URL to a directory that will produce a listing of files.' ,
  `access_url` VARCHAR(256) NOT NULL COMMENT 'The URL at which the interface is found.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Information of access modes of a capability';

ALTER TABLE `rr`.`interface` ADD PRIMARY KEY (`ivoid`, `intf_index`) ;
ALTER TABLE `rr`.`interface` 
    ADD CONSTRAINT `interface_ivoid_resource` FOREIGN KEY (`ivoid`) REFERENCES `rr`.`resource` (`ivoid` )
	ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `interface_intf_type` ON `rr`.`interface` (`intf_type` ASC) ;
CREATE INDEX `interface_ivoidcapidx_capability` ON `rr`.`interface` (`ivoid` ASC, `cap_index` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`intf_param`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`intf_param` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `intf_index` SMALLINT(6) NOT NULL COMMENT 'A running number for the interfaces of a capability.' ,
  `description` TEXT NULL DEFAULT NULL COMMENT 'A free-text description of the column\'s contents' ,
  `name` VARCHAR(64) NOT NULL COMMENT 'The name of the column' ,
  `ucd` VARCHAR(128) NULL DEFAULT NULL COMMENT 'A unified content descriptor that describes the scientific content of the parameter.' ,
  `unit` VARCHAR(128) NULL DEFAULT NULL COMMENT 'The unit associated with all values in the column.' ,
  `utype` VARCHAR(128) NULL DEFAULT NULL COMMENT 'An identifier for a role in a data model that the data in this column represents.' ,
  `std` SMALLINT(6) NOT NULL COMMENT 'If 1, the meaning and use of this parameter is reserved and defined by a standard model. If 0, it represents a database-specific parameter that effectively extends beyond the standard.' ,
  `datatype` VARCHAR(32) NOT NULL COMMENT 'The type of the data contained in the column.' ,
  `extended_schema` VARCHAR(32) NULL DEFAULT NULL COMMENT 'An identifier for the schema that the value given by the extended attribute is drawn from.' ,
  `extended_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The data value represented by this type can be interpreted as of a custom type identified by the value of this attribute.' ,
  `use_param` VARCHAR(16) NULL DEFAULT NULL COMMENT 'An indication of whether this parameter is required to be provided for the application or service to work properly.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Input paramteres of services';

ALTER TABLE `rr`.`intf_param`
    ADD CONSTRAINT `intf_param_couple_interface` FOREIGN KEY (`ivoid` , `intf_index` ) REFERENCES `rr`.`interface` (`ivoid` , `intf_index` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `intf_param_name` ON `rr`.`intf_param` (`name` ASC) ;
CREATE INDEX `intf_param_ucd` ON `rr`.`intf_param` (`ucd` ASC) ;
CREATE INDEX `intf_param_utype` ON `rr`.`intf_param` (`utype` ASC) ;
CREATE INDEX `intf_param_couple_interface` ON `rr`.`intf_param` (`ivoid` ASC, `intf_index` ASC) ;
CREATE FULLTEXT INDEX `intf_param_description` ON `rr`.`intf_param` (`description` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`relationship`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`relationship` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `relationship_type` VARCHAR(32) NOT NULL COMMENT 'The named type of relationship; this can be mirror-of, service-for, served-by, derived-from, related-to, or something user-defined.' ,
  `related_id` VARCHAR(256) NOT NULL COMMENT 'The URI form of the IVOA identifier for the resource refered to.' ,
  `related_name` VARCHAR(256) NOT NULL COMMENT 'The name of resource that this resource is related to.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Relationships between resources, e.g., mirroring, derivation, but also providing access to data within a resource.';

ALTER TABLE `rr`.`relationship` ADD CONSTRAINT `relationship_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `relationship_rel_id` ON `rr`.`relationship` (`related_id` ASC) ;
CREATE INDEX `relationship_ivoid_resource` ON `rr`.`relationship` (`ivoid` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`res_date`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`res_date` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `date_value` DATETIME NOT NULL COMMENT 'A date associated with an event in the life cycle of the resource.' ,
  `value_role` VARCHAR(32) NULL DEFAULT NULL COMMENT 'A string indicating what the date refers to.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'A date associated with an event in the life cycle of the resource. This could be creation or update. The role column can be used to clarify.';

ALTER TABLE `rr`.`res_date` ADD CONSTRAINT `res_date_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `res_date_ivoid_resource` ON `rr`.`res_date` (`ivoid` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`res_detail`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`res_detail` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `cap_index` SMALLINT(6) NULL DEFAULT NULL COMMENT 'The index of the parent capability; if NULL the utype-value pair describes a member of the entire resource.' ,
  `detail_utype` VARCHAR(128) NOT NULL COMMENT 'The utype of the member' ,
  `detail_value` VARCHAR(256) NOT NULL COMMENT '(atomic) value of the member' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Utype-value pairs for members of resource or capability and their derivations that are less used and/or from VOResource extensions. The pairs refer to a resource if cap_index is NULL, to the referenced capability otherwise.';

ALTER TABLE `rr`.`res_detail` ADD CONSTRAINT `res_detail_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `res_detail_utype` ON `rr`.`res_detail` (`detail_utype` ASC) ;
CREATE INDEX `res_detail_value` ON `rr`.`res_detail` (`detail_value` ASC) ;
CREATE INDEX `res_detail_ivoid_resource` ON `rr`.`res_detail` (`ivoid` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`res_role`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`res_role` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `role_name` TEXT NOT NULL COMMENT 'The real-world name or title of a person or organization' ,
  `role_ivoid` VARCHAR(256) NULL COMMENT 'An IVOA identifier of a person or organization' ,
  `address` VARCHAR(256) NULL DEFAULT NULL COMMENT 'A mailing address for a person or organization' ,
  `email` VARCHAR(128) NULL DEFAULT NULL COMMENT 'An email address the entity can be reached at' ,
  `telephone` VARCHAR(32) NULL COMMENT 'A telephone number the entity can be reached at' ,
  `logo` VARCHAR(256) NULL COMMENT 'URL pointing to a graphical logo, which may be used to help identify the entity' ,
  `base_role` VARCHAR(64) NOT NULL COMMENT 'The role played by this entity; this is one of contact, publisher, and creator' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Entities, i.e., persons or organizations, operating on resources: creators, contacts, publishers.';

ALTER TABLE `rr`.`res_role` ADD CONSTRAINT `res_role_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `ivoid_resource_ivoid` ON `rr`.`res_role` (`ivoid` ASC) ;
CREATE FULLTEXT INDEX `res_role_name` ON `rr`.`res_role` (`role_name` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`res_schema`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`res_schema` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `schema_index` SMALLINT(6) NOT NULL COMMENT 'A running number for the res_schema rows belonging to a resource.' ,
  `schema_description` TEXT NULL COMMENT 'A free text description of the tableset explaining in general how all of the tables are related.' ,
  `schema_name` VARCHAR(32) NOT NULL COMMENT 'A name for the set of tables.' ,
  `schema_title` VARCHAR(256) NULL COMMENT 'A descriptive, human-interpretable name for the table set.' ,
  `schema_utype` VARCHAR(64) NULL DEFAULT NULL COMMENT 'An identifier for a concept in a data model that the data in this schema as a whole represent.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Sets of tables related to resources.';

ALTER TABLE `rr`.`res_schema` ADD PRIMARY KEY (`ivoid`, `schema_index`) ;
ALTER TABLE `rr`.`res_schema` ADD CONSTRAINT `res_schema_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `res_schema_ivoid_resource` ON `rr`.`res_schema` (`ivoid` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`res_table`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`res_table` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `schema_index` SMALLINT(6) NULL COMMENT 'Index of the schema this table belongs to, if it belongs to a schema (otherwise NULL).' ,
  `table_description` TEXT NULL DEFAULT NULL COMMENT 'A free-text description of the table\'s contents' ,
  `table_name` VARCHAR(64) NOT NULL COMMENT 'The fully qualified name of the table. This name should include all catalog or schema prefixes needed to distinguish it in a query.' ,
  `table_index` INT(11) NOT NULL COMMENT 'An artificial counter for the tables belonging to a resource' ,
  `table_title` VARCHAR(256) NULL COMMENT 'A descriptive, human-interpretable name for the table' ,
  `table_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'A name for the role this table plays. Recognized values include \"output\", indicating this table is output from a query; \"base_table\", indicating a table whose records represent the main subjects of its schema; and \"view\", indicating that the table represents a useful combination or subset of other tables. Other values are allowed.' ,
  `table_utype` VARCHAR(64) NULL COMMENT 'An identifier for a concept in a data model that the data in this table as a whole represent.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = '(Relational) tables that are part of schemata or resources.';

ALTER TABLE `rr`.`res_table` ADD PRIMARY KEY (`ivoid`, `table_index`) ;
ALTER TABLE `rr`.`res_table` ADD CONSTRAINT `res_table_ivoidschemaidx_res_schema` FOREIGN KEY (`ivoid` , `schema_index` ) REFERENCES `rr`.`res_schema` (`ivoid` , `schema_index` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `res_table_ivoidschemaidx_res_schema` ON `rr`.`res_table` (`ivoid` ASC, `schema_index` ASC) ;
CREATE FULLTEXT INDEX `res_table_description` ON `rr`.`res_table` (`table_description` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`subject`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`subject` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `subject` TEXT NOT NULL COMMENT 'Topics, object types, or other descriptive keywords about the resource.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Topics, object types, or other descriptive keywords about the resource.';

ALTER TABLE `rr`.`subject` ADD CONSTRAINT `subject_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `subject_ivoid_resource` ON `rr`.`subject` (`ivoid` ASC) ;
CREATE FULLTEXT INDEX `subject_subject` ON `rr`.`subject` (`subject` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`table_column`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`table_column` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `table_index` INT(11) NOT NULL COMMENT 'An artificial counter for the tables belonging to a resource' ,
  `description` TEXT NULL DEFAULT NULL COMMENT 'A free-text description of the column\'s contents' ,
  `name` VARCHAR(64) NOT NULL COMMENT 'The name of the column' ,
  `ucd` VARCHAR(128) NULL DEFAULT NULL COMMENT 'A unified content descriptor that describes the scientific content of the parameter.' ,
  `unit` VARCHAR(128) NULL DEFAULT NULL COMMENT 'The unit associated with all values in the column.' ,
  `utype` VARCHAR(128) NULL DEFAULT NULL COMMENT 'An identifier for a role in a data model that the data in this column represents.' ,
  `flag` VARCHAR(32) NULL DEFAULT NULL COMMENT 'hash-separated keywords representing traits of the column. Recognized values include \"indexed\", \"primary\", and \"nullable\".' ,
  `std` SMALLINT(6) NOT NULL COMMENT 'If 1, the meaning and use of this parameter is reserved and defined by a standard model. If 0, it represents a database-specific parameter that effectively extends beyond the standard.' ,
  `datatype` VARCHAR(32) NOT NULL COMMENT 'The type of the data contained in the column.' ,
  `extended_schema` VARCHAR(32) NULL DEFAULT NULL COMMENT 'An identifier for the schema that the value given by the extended attribute is drawn from.' ,
  `extended_type` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The data value represented by this type can be interpreted as of a custom type identified by the value of this attribute.' ,
  `arraysize` VARCHAR(16) NULL DEFAULT NULL COMMENT 'The shape of the array that constitutes the value.' ,
  `delim` VARCHAR(1) NULL DEFAULT NULL COMMENT 'The string that is used to delimit elements of an array value when arraysize is not \'1\'.' ,
  `type_system` VARCHAR(32) NULL DEFAULT NULL COMMENT 'The type system used, as a QName with a canonical prefix; this will ususally be one of vs:SimpleDataType, vs:VOTableType, and vs:TAPType.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Metadata on columns of tables pertaining to resources.';

ALTER TABLE `rr`.`table_column` ADD CONSTRAINT `table_column_ivoidtableidx_res_table` FOREIGN KEY (`ivoid` , `table_index` ) REFERENCES `rr`.`res_table` (`ivoid` , `table_index` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `table_column_name` ON `rr`.`table_column` (`name` ASC) ;
CREATE INDEX `table_column_ucd` ON `rr`.`table_column` (`ucd` ASC) ;
CREATE INDEX `table_column_utype` ON `rr`.`table_column` (`utype` ASC) ;
CREATE INDEX `table_column_ivoidtableidx_res_table` ON `rr`.`table_column` (`ivoid` ASC, `table_index` ASC) ;
CREATE FULLTEXT INDEX `table_column_description` ON `rr`.`table_column` (`description` ASC) ;


-- -----------------------------------------------------
-- Table `rr`.`validation`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `rr`.`validation` (
  `ivoid` VARCHAR(256) NOT NULL COMMENT 'The parent resource.' ,
  `validated_by` VARCHAR(256) NOT NULL COMMENT 'The IVOA ID of the registry or organisation that assigned the validation level.' ,
  `level` SMALLINT(6) NOT NULL COMMENT 'A numeric grade describing the quality of the resource description, when applicable, to be used to indicate the confidence an end-user can put in the resource as part of a VO application or research study.' ,
  `cap_index` SMALLINT(6) NULL DEFAULT NULL COMMENT 'If non-NULL, the validation only refers to the the capability referenced here.' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'Validation levels for resources and capabilities.';

ALTER TABLE `rr`.`validation` ADD CONSTRAINT `validation_ivoid_resource` FOREIGN KEY (`ivoid` ) REFERENCES `rr`.`resource` (`ivoid` )
    ON DELETE NO ACTION ON UPDATE NO ACTION ;
CREATE INDEX `validation_ivoid_resource` ON `rr`.`validation` (`ivoid` ASC) ;

USE `rr` ;

-- -----------------------------------------------------
-- function ivo_hashlist_has
-- -----------------------------------------------------

USE `rr`;
DROP function IF EXISTS `rr`.`ivo_hashlist_has`;

DELIMITER $$
USE `rr`$$
CREATE FUNCTION `ivo_hashlist_has`(hashlist TEXT, item TEXT) RETURNS INT(11)
BEGIN
	DECLARE has INTEGER;
	DECLARE pattern TEXT;
	SET pattern = CONCAT('[#]?',LOWER(item),'[#]?');
	SELECT LOWER(hashlist) REGEXP pattern INTO HAS;
RETURN has;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function ivo_hasword
-- -----------------------------------------------------

USE `rr`;
DROP function IF EXISTS `rr`.`ivo_hasword`;

DELIMITER $$
USE `rr`$$
CREATE FUNCTION `ivo_hasword`(sentence TEXT, word TEXT) RETURNS INT(11)
BEGIN
	DECLARE has INTEGER;
	DECLARE pattern TEXT;
	SET pattern = CONCAT('[[:<:]]',LOWER(word),'[[:>:]]');
	SELECT LOWER(sentence) REGEXP pattern INTO HAS;
RETURN has;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function ivo_nocasematch
-- -----------------------------------------------------

USE `rr`;
DROP function IF EXISTS `rr`.`ivo_nocasematch`;

DELIMITER $$
USE `rr`$$
CREATE FUNCTION `ivo_nocasematch`(token TEXT, pattern TEXT) RETURNS INT(11)
BEGIN
	DECLARE result INTEGER;
	SELECT token LIKE pattern INTO RESULT;
RETURN result;
END$$

DELIMITER ;


