OpenSim-Test/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations

:VERSION 1         # -------------------------------

begin;

CREATE TABLE classifieds (
  "classifieduuid" char(36) NOT NULL,
  "creatoruuid" char(36) NOT NULL,
  "creationdate" integer NOT NULL,
  "expirationdate" integer NOT NULL,
  "category" varchar(20) NOT NULL,
  "name" varchar(255) NOT NULL,
  "description" text NOT NULL,
  "parceluuid" char(36) NOT NULL,
  "parentestate" integer NOT NULL,
  "snapshotuuid" char(36) NOT NULL,
  "simname" varchar(255) NOT NULL,
  "posglobal" varchar(255) NOT NULL,
  "parcelname" varchar(255) NOT NULL,
  "classifiedflags" integer NOT NULL,
  "priceforlisting" integer NOT NULL,
  constraint classifiedspk PRIMARY KEY ("classifieduuid")
);


CREATE TABLE usernotes (
  "useruuid" varchar(36) NOT NULL,
  "targetuuid" varchar(36) NOT NULL,
  "notes" text NOT NULL,
  constraint usernoteuk UNIQUE ("useruuid","targetuuid")
);


CREATE TABLE userpicks (
  "pickuuid" varchar(36) NOT NULL,
  "creatoruuid" varchar(36) NOT NULL,
  "toppick" boolean NOT NULL,
  "parceluuid" varchar(36) NOT NULL,
  "name" varchar(255) NOT NULL,
  "description" text NOT NULL,
  "snapshotuuid" varchar(36) NOT NULL,
  "user" varchar(255) NOT NULL,
  "originalname" varchar(255) NOT NULL,
  "simname" varchar(255) NOT NULL,
  "posglobal" varchar(255) NOT NULL,
  "sortorder" integer NOT NULL,
  "enabled" boolean NOT NULL,
  PRIMARY KEY ("pickuuid")
);


CREATE TABLE userprofile (
  "useruuid" varchar(36) NOT NULL,
  "profilePartner" varchar(36) NOT NULL,
  "profileAllowPublish" bytea NOT NULL,
  "profileMaturePublish" bytea NOT NULL,
  "profileURL" varchar(255) NOT NULL,
  "profileWantToMask" integer NOT NULL,
  "profileWantToText" text NOT NULL,
  "profileSkillsMask" integer NOT NULL,
  "profileSkillsText" text NOT NULL,
  "profileLanguages" text NOT NULL,
  "profileImage" varchar(36) NOT NULL,
  "profileAboutText" text NOT NULL,
  "profileFirstImage" varchar(36) NOT NULL,
  "profileFirstText" text NOT NULL,
  PRIMARY KEY ("useruuid")
);

commit;

:VERSION 2         # -------------------------------
 
begin;
CREATE TABLE userdata (
    "UserId" char(36) NOT NULL,
    "TagId" varchar(64) NOT NULL,
    "DataKey" varchar(255),
    "DataVal" varchar(255),
    PRIMARY KEY ("UserId","TagId")
);

commit;

:VERSION 3        # -------------------------------
begin;
CREATE TABLE usersettings (
    "useruuid" char(36) NOT NULL,
    "imviaemail" bytea NOT NULL,
    "visible" bytea NOT NULL,
    PRIMARY KEY ("useruuid")
);
commit;

:VERSION 4

BEGIN;

-- Classifieds
ALTER TABLE classifieds DROP CONSTRAINT classifiedspk;
ALTER TABLE classifieds ALTER COLUMN classifieduuid SET DATA TYPE uuid using classifieduuid::uuid;
ALTER TABLE classifieds ALTER COLUMN creatoruuid SET DATA TYPE uuid using creatoruuid::uuid;
ALTER TABLE classifieds ALTER COLUMN parceluuid SET DATA TYPE uuid using parceluuid::uuid;
ALTER TABLE classifieds ALTER COLUMN snapshotuuid SET DATA TYPE uuid using snapshotuuid::uuid;
ALTER TABLE classifieds ADD CONSTRAINT classifiedspk PRIMARY KEY (classifieduuid);

-- Notes
ALTER TABLE usernotes DROP CONSTRAINT usernoteuk;
ALTER TABLE usernotes ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
ALTER TABLE usernotes ALTER COLUMN targetuuid SET DATA TYPE uuid USING targetuuid::uuid;
ALTER TABLE usernotes ADD CONSTRAINT usernoteuk UNIQUE (useruuid,targetuuid);


-- Userpicks
ALTER TABLE userpicks DROP CONSTRAINT userpicks_pkey;
ALTER TABLE userpicks ALTER COLUMN pickuuid SET DATA TYPE uuid USING pickuuid::uuid;
ALTER TABLE userpicks ALTER COLUMN creatoruuid SET DATA TYPE uuid USING creatoruuid::uuid;
ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
ALTER TABLE userpicks ADD PRIMARY KEY (pickuuid);

-- Userprofile
ALTER TABLE userprofile DROP CONSTRAINT userprofile_pkey;
ALTER TABLE userprofile ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
ALTER TABLE userprofile ALTER COLUMN "profilePartner" SET DATA TYPE uuid USING "profilePartner"::uuid;
-- Force column conversions
ALTER TABLE userprofile ALTER COLUMN "profileAllowPublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
ALTER TABLE userprofile ALTER COLUMN "profileMaturePublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
ALTER TABLE userprofile ALTER COLUMN "profileImage" SET DATA TYPE uuid USING "profileImage"::uuid;
ALTER TABLE userprofile ALTER COLUMN "profileFirstImage" SET DATA TYPE uuid USING "profileFirstImage"::uuid;
ALTER TABLE userprofile ADD PRIMARY KEY (useruuid);

-- Userdata
ALTER TABLE userdata DROP CONSTRAINT userdata_pkey;
ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
ALTER TABLE userdata ADD PRIMARY KEY ("UserId","TagId");


-- Usersettings
ALTER TABLE usersettings DROP CONSTRAINT usersettings_pkey;
ALTER TABLE usersettings ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
ALTER TABLE usersettings ALTER COLUMN visible SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
ALTER TABLE usersettings ADD COLUMN email varchar(254) NOT NULL;
ALTER TABLE usersettings ADD PRIMARY KEY (useruuid);

COMMIT;


:VERSION 5         # -------------------------------

BEGIN;

ALTER TABLE usersettings ALTER COLUMN imviaemail SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;

COMMIT;

:VERSION 6         # -------------------------------

BEGIN TRANSACTION;

ALTER TABLE userpicks ADD "gatekeeper" varchar(255) COLLATE "default";

COMMIT;