Web_BLS_RCUAction_Server/docs/temporary_project.sql

/*
 Navicat Premium Dump SQL

 Source Server         : FnOS 109
 Source Server Type    : PostgreSQL
 Source Server Version : 150004 (150004)
 Source Host           : 10.8.8.109:5433
 Source Catalog        : log_platform
 Source Schema         : temporary_project

 Target Server Type    : PostgreSQL
 Target Server Version : 150004 (150004)
 File Encoding         : 65001

 Date: 02/02/2026 14:28:41
*/


-- ----------------------------
-- Table structure for hotels
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."hotels";
CREATE TABLE "temporary_project"."hotels" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "hotel_id" int4 NOT NULL,
  "hotel_name" varchar(255) COLLATE "pg_catalog"."default",
  "id" int4
)
;

-- ----------------------------
-- Table structure for loops_default
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."loops_default";
CREATE TABLE "temporary_project"."loops_default" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "id" int4 NOT NULL,
  "loop_name" varchar(255) COLLATE "pg_catalog"."default",
  "room_type_id" int4 NOT NULL,
  "loop_address" varchar(255) COLLATE "pg_catalog"."default",
  "loop_type" varchar(50) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Table structure for room_type
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."room_type";
CREATE TABLE "temporary_project"."room_type" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "id" int4 NOT NULL,
  "room_type_name" varchar(255) COLLATE "pg_catalog"."default",
  "hotel_id" int4
)
;

-- ----------------------------
-- Table structure for rooms_default
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."rooms_default";
CREATE TABLE "temporary_project"."rooms_default" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "hotel_id" int4 NOT NULL,
  "room_id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "room_type_id" int4,
  "device_id" varchar(50) COLLATE "pg_catalog"."default",
  "mac" varchar(50) COLLATE "pg_catalog"."default",
  "id" int4
)
;

-- ----------------------------
-- Table structure for loops
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."loops";
CREATE TABLE "temporary_project"."loops" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "id" int4 NOT NULL,
  "loop_name" varchar(255) COLLATE "pg_catalog"."default",
  "room_type_id" int4 NOT NULL,
  "loop_address" varchar(255) COLLATE "pg_catalog"."default",
  "loop_type" varchar(50) COLLATE "pg_catalog"."default"
)
PARTITION BY  (
)
;

-- ----------------------------
-- Table structure for rooms
-- ----------------------------
DROP TABLE IF EXISTS "temporary_project"."rooms";
CREATE TABLE "temporary_project"."rooms" (
  "guid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "hotel_id" int4 NOT NULL,
  "room_id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "room_type_id" int4,
  "device_id" varchar(50) COLLATE "pg_catalog"."default",
  "mac" varchar(50) COLLATE "pg_catalog"."default",
  "id" int4
)
PARTITION BY LIST (
  "hotel_id" "pg_catalog"."int4_ops"
)
;
ALTER TABLE "temporary_project"."rooms" ATTACH PARTITION "temporary_project"."rooms_default" DEFAULT;

-- ----------------------------
-- Indexes structure for table hotels
-- ----------------------------
CREATE INDEX "idx_hotels_hotel_id" ON "temporary_project"."hotels" USING btree (
  "hotel_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_hotels_hotel_name" ON "temporary_project"."hotels" USING btree (
  "hotel_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_hotels_id" ON "temporary_project"."hotels" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table hotels
-- ----------------------------
ALTER TABLE "temporary_project"."hotels" ADD CONSTRAINT "hotels_pkey" PRIMARY KEY ("hotel_id", "guid");

-- ----------------------------
-- Indexes structure for table loops_default
-- ----------------------------
CREATE INDEX "loops_default_id_idx" ON "temporary_project"."loops_default" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "loops_default_loop_address_idx" ON "temporary_project"."loops_default" USING btree (
  "loop_address" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "loops_default_loop_name_idx" ON "temporary_project"."loops_default" USING btree (
  "loop_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "loops_default_loop_type_idx" ON "temporary_project"."loops_default" USING btree (
  "loop_type" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "loops_default_room_type_id_idx" ON "temporary_project"."loops_default" USING btree (
  "room_type_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table loops_default
-- ----------------------------
ALTER TABLE "temporary_project"."loops_default" ADD CONSTRAINT "loops_default_pkey" PRIMARY KEY ("guid", "id", "room_type_id");

-- ----------------------------
-- Indexes structure for table room_type
-- ----------------------------
CREATE INDEX "idx_room_type_hotel_id" ON "temporary_project"."room_type" USING btree (
  "hotel_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_room_type_id" ON "temporary_project"."room_type" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_room_type_name" ON "temporary_project"."room_type" USING btree (
  "room_type_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table room_type
-- ----------------------------
ALTER TABLE "temporary_project"."room_type" ADD CONSTRAINT "room_type_pkey" PRIMARY KEY ("guid", "id");

-- ----------------------------
-- Indexes structure for table rooms_default
-- ----------------------------
CREATE INDEX "rooms_default_device_id_idx" ON "temporary_project"."rooms_default" USING btree (
  "device_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "rooms_default_hotel_id_idx" ON "temporary_project"."rooms_default" USING btree (
  "hotel_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "rooms_default_id_idx" ON "temporary_project"."rooms_default" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "rooms_default_mac_idx" ON "temporary_project"."rooms_default" USING btree (
  "mac" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table rooms_default
-- ----------------------------
ALTER TABLE "temporary_project"."rooms_default" ADD CONSTRAINT "rooms_default_pkey" PRIMARY KEY ("guid", "hotel_id", "room_id");

-- ----------------------------
-- Indexes structure for table loops
-- ----------------------------
CREATE INDEX "idx_loops_address" ON "temporary_project"."loops" USING btree (
  "loop_address" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_loops_id" ON "temporary_project"."loops" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_loops_name" ON "temporary_project"."loops" USING btree (
  "loop_name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_loops_room_type_id" ON "temporary_project"."loops" USING btree (
  "room_type_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_loops_type" ON "temporary_project"."loops" USING btree (
  "loop_type" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table loops
-- ----------------------------
ALTER TABLE "temporary_project"."loops" ADD CONSTRAINT "loops_pkey" PRIMARY KEY ("guid", "id", "room_type_id");

-- ----------------------------
-- Indexes structure for table rooms
-- ----------------------------
CREATE INDEX "idx_rooms_device_id" ON "temporary_project"."rooms" USING btree (
  "device_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_rooms_hotel_id" ON "temporary_project"."rooms" USING btree (
  "hotel_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_rooms_id" ON "temporary_project"."rooms" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_rooms_mac" ON "temporary_project"."rooms" USING btree (
  "mac" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table rooms
-- ----------------------------
ALTER TABLE "temporary_project"."rooms" ADD CONSTRAINT "rooms_pkey" PRIMARY KEY ("guid", "hotel_id", "room_id");