test_0_next_patch.sql

-- USE _caosdb_schema_unit_tests;


-- REMOVE SQLITE datatype
DELETE FROM entities WHERE id = 50;

ALTER TABLE files ADD UNIQUE (`path`);

ALTER TABLE files ADD COLUMN IF NOT EXISTS (
    mimetype VARBINARY(255) DEFAULT NULL,
    fileStorage VARBINARY(255) NOT NULL DEFAULT "DEFAULT",
    storageId VARBINARY(255) DEFAULT NULL
);


ALTER TABLE entities MODIFY COLUMN `role` enum('RECORDTYPE','RECORD','FILE','DOMAIN','PROPERTY','DATATYPE','ROLE','QUERYTEMPLATE', 'DIRECTORY') COLLATE utf8_unicode_ci NOT NULL;

INSERT IGNORE INTO entities (id, name, description, role, acl) VALUES (9, "DIRECTORY", "The directory role.", "ROLE", 0);

UPDATE files SET storageId=path;

ALTER TABLE files MODIFY COLUMN storageId VARBINARY(255) NOT NULL;

DROP PROCEDURE IF EXISTS _create_dirs;
DELIMITER //
CREATE PROCEDURE _create_dirs()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE _file_id INT UNSIGNED DEFAULT NULL;
    DECLARE _path VARCHAR(255) DEFAULT NULL;
    DECLARE dir_id INT UNSIGNED DEFAULT NULL;
    DECLARE dir_path VARCHAR(255) DEFAULT "";
    DECLARE dir_exists BOOLEAN DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT file_id, path FROM files;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;

    loop1: LOOP
        FETCH cur INTO _file_id, _path;
        IF done THEN
            LEAVE loop1;
        END IF;

        -- TODO
        loop2: LOOP
            SELECT REGEXP_SUBSTR(_path, CONCAT("(?U)^", dir_path, ".*/" )) INTO dir_path;
            IF dir_path = "" THEN
                SET dir_exists = FALSE;
                SET done = FALSE;
                LEAVE loop2;
            END IF;
            SELECT TRUE INTO dir_exists FROM files WHERE path = LEFT(dir_path, CHAR_LENGTH(dir_path)-1);
            IF dir_exists IS TRUE THEN
                SET dir_exists = FALSE;
                ITERATE loop2;
            END IF;
            INSERT INTO entities (name, description, role, acl) VALUES (NULL, NULL, "DIRECTORY", 0);
            SET dir_id = LAST_INSERT_ID();
            INSERT INTO files (file_id, path, size, hash, checked_timestamp, mimetype, fileStorage, storageId) VALUES (dir_id, LEFT(dir_path, CHAR_LENGTH(dir_path)-1), 0, NULL, 0, "inode/directory", "DEFAULT", LEFT(dir_path, CHAR_LENGTH(dir_path)-1));
        END LOOP;

    END LOOP;

    CLOSE cur;

END //

DELIMITER ;

CALL _create_dirs();