CyberX8/CyberX8_RT/Config/DBModel.sql

create or replace function update_db_model() returns void as 
$$ 
begin 
 
 ------------------------------------------------------------------------------------------------
 --
    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'carrier_data') then 
 
			CREATE TABLE "carrier_data"
			(
				"guid" text NOT NULL,
				"load_time" timestamp without time zone,
				"unload_time" timestamp without time zone,
				"rfid" text,
				"lot_id" text,
				"product_category" text,
				"station" text,
				CONSTRAINT "carrier_data_pkey" PRIMARY KEY ("guid" )
			)
			WITH (
			OIDS=FALSE
			);
			ALTER TABLE "carrier_data"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "carrier_data" TO postgres; 
    end if; 

 ------------------------------------------------------------------------------------------------
 --
	if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'wafer_data') then 
 
			CREATE TABLE "wafer_data"
			(
				"guid" text NOT NULL,
				"create_time" timestamp without time zone,
				"delete_time" timestamp without time zone,
				"carrier_data_guid" text,
				"create_station" text,
				"create_slot" text,
				"process_data_guid" text,
				"wafer_id" text,
				"lasermarker1" text,
				"lasermarker2" text,
				"lasermarker3" text,
				"t7code1" text,
				"t7code2" text,
				"t7code3" text,
				"pj_data_guid" text,
				"lot_data_guid" text,
				CONSTRAINT "wafer_data_pkey" PRIMARY KEY ("guid" )
			)
			WITH (
			OIDS=FALSE
			);
			ALTER TABLE "wafer_data"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "wafer_data" TO postgres; 
    end if; 
 ------------------------------------------------------------------------------------------------
 --	
	 if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'event_data') then 
 
					CREATE TABLE "event_data"
				(
				  "gid" serial NOT NULL,
				  "event_id" integer,
				  "event_enum" text,
				  "type" text,
				  "source" text,
				  "description" text,
				  "level" text,
				  "occur_time" timestamp without time zone,
				  CONSTRAINT "event_data_pkey" PRIMARY KEY ("gid" )
				)
					WITH (
					OIDS=FALSE
					);
					--ALTER TABLE "EventManager"
					--OWNER TO postgres;
					GRANT ALL ON TABLE "event_data" TO postgres;
					GRANT SELECT ON TABLE "event_data" TO postgres;

					CREATE INDEX "event_data_occur_time_event_id_idx"
					ON "event_data"
					USING btree
					("occur_time" , "event_id" );
    end if; 

 ------------------------------------------------------------------------------------------------
 --	
	    if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'wafer_move_history') then 
 
					CREATE TABLE "wafer_move_history"
					(
					  "gid" serial NOT NULL,
					  "wafer_data_guid" text,
					  "arrive_time" timestamp without time zone,
					  "station" text,
					  "slot" text,
					  "status" text,
					  CONSTRAINT "wafer_move_history_pkey" PRIMARY KEY ("gid" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "wafer_move_history"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "wafer_move_history" TO postgres;
 
    end if; 
 ------------------------------------------------------------------------------------------------
 --	
	    if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'process_data') then 
 
					CREATE TABLE "process_data"
					(
					  "guid" text NOT NULL,
					  "process_begin_time" timestamp without time zone,
					  "process_end_time" timestamp without time zone,
					  "recipe_name" text,
					  "chuck_name" text,
					  "dechuck_name" text,
					  "process_status" text,
					   "wafer_data_guid" text,
					   "process_in" text,
					  CONSTRAINT "process_data_pkey" PRIMARY KEY ("guid" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "process_data"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "process_data" TO postgres;
 
    end if; 

	 ------------------------------------------------------------------------------------------------
 --	
	    if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'stats_data') then 
 
					CREATE TABLE "stats_data"
					(
					  "name" text,
					  "value" integer,
					  "total" integer,
					  "description" text,
					  "last_update_time" timestamp without time zone,
					  "last_reset_time" timestamp without time zone,
					  "last_total_reset_time" timestamp without time zone,		
					  "is_visible" boolean,
					  "enable_alarm" boolean,
					  "alarm_value" integer,
					  CONSTRAINT "stats_data_pkey" PRIMARY KEY ("name" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "stats_data"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "stats_data" TO postgres;
 
    end if; 

	 ------------------------------------------------------------------------------------------------
 --	
	    if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'leak_check_data') then 
 
					CREATE TABLE "leak_check_data"
					(
						"guid" text NOT NULL,
						"operate_time" timestamp without time zone,
						"status" text,
						"leak_rate" real,
						"start_pressure" real,
						"stop_pressure" real,
						"mode" text,
						"leak_check_time" integer,
					  CONSTRAINT "leak_check_data_pkey" PRIMARY KEY ("guid" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "leak_check_data"
					OWNER TO postgres;
					GRANT ALL ON TABLE "leak_check_data" TO postgres;
					GRANT SELECT ON TABLE "leak_check_data" TO postgres;
 
    end if; 
 ------------------------------------------------------------------------------------------------
 --
    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'cj_data') then 
 
			CREATE TABLE cj_data
			(
				"guid" text NOT NULL,
				"start_time" timestamp without time zone,
				"end_time" timestamp without time zone,
				"carrier_data_guid" text,
				"name" text,
				"input_port" text,
				"output_port" text,
				CONSTRAINT "cj_data_pkey" PRIMARY KEY ("guid" )
			)
			WITH (
			OIDS=FALSE
			);
			ALTER TABLE "cj_data"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "cj_data" TO postgres; 
    end if; 
 ------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------
 --

    if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'pj_data') then 
 
			CREATE TABLE pj_data
			(
				"guid" text NOT NULL,
				"start_time" timestamp without time zone,
				"end_time" timestamp without time zone,
				"carrier_data_guid" text,
				"cj_data_guid" text,
				"name" text,
				"input_port" text,
				"output_port" text,
				"total_wafer_count" integer,
				"abort_wafer_count" integer,
				"unprocessed_wafer_count" integer,
				CONSTRAINT "pj_data_pkey" PRIMARY KEY ("guid" )
			)
			WITH (
			OIDS=FALSE
			);
			ALTER TABLE "pj_data"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "pj_data" TO postgres; 
    end if; 
------------------------------------------------------------------------------------------------
 --	
	 if not exists(select * from information_schema.tables  
	        where  
	            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
	            and table_name = 'offset_data') then 
	 
				CREATE TABLE "offset_data"
				(
					"guid" text NOT NULL,
					"source_module" text NOT NULL,
					"source_slot" integer NOT NULL,
					"destination_module" text NOT NULL,
					"destination_slot" integer NOT NULL,
					"origin_module" text NOT NULL,
					"origin_slot" integer NOT NULL,
					"arm_position" text NOT NULL,
					"arm_pan" text NOT NULL,
					"offset_x" real NOT NULL,
					"offset_y" real NOT NULL,
					"offset_d" real NOT NULL,
					"start_time" timestamp without time zone,
					"end_time" timestamp without time zone,
					CONSTRAINT "offset_data_pkey" PRIMARY KEY ("guid","start_time","end_time")
				)
				WITH (
				OIDS=FALSE
				);
				ALTER TABLE "offset_data"
				OWNER TO postgres;
				GRANT SELECT ON TABLE "offset_data" TO postgres; 
	    end if; 
 ------------------------------------------------------------------------------------------------
 --	
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'stats_data_rf_pump') then 
 
					CREATE TABLE "stats_data_rf_pump"
					(
					  "name" text,
					  "description" text,
					  "last_pm_time" timestamp without time zone,	
					  "from_last_pm" real,
					  "total" real,
					  "pm_interval" real,
					  "enable_alarm" boolean,
					  CONSTRAINT "stats_data_rf_pump_pkey" PRIMARY KEY ("name" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "stats_data_rf_pump"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "stats_data_rf_pump" TO postgres;
 
    end if; 

 ------------------------------------------------------------------------------------------------
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'wafer_holder') then 

					CREATE TABLE "wafer_holder"
					(
					  "id" text,
					  "buffer_id" integer,
					  "crs_type" text,
					  "wafer_size" integer,
					  "enabled" boolean,
					  "chemistry" text,
					  "crsa_id" text,
					  "crsa_total_uses" integer,
					  "crsa_pad_uses" integer,
					  "crsa_seal_uses" integer,
					  "crsb_id" text,
					  "crsb_total_uses" integer,
					  "crsb_pad_uses" integer,
					  "crsb_seal_uses" integer,
					  "total_dummy_wafers" integer,
					  "total_product_wafers" integer,
					  "current_location" text,
					  "wafera_id" text,
					  "wafera_type" integer,
					  "waferb_id" text,
					  "waferb_type" integer,
					  "status" integer,
					  "create_time" timestamp without time zone,
					  "update_time" timestamp without time zone,	
					  CONSTRAINT "wafer_holder_pkey" PRIMARY KEY ("id" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "wafer_holder"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "wafer_holder" TO postgres;

	end if; 
	
	if not exists(select * from information_schema.columns 
		where table_catalog = CURRENT_CATALOG  and table_schema = CURRENT_SCHEMA  and table_name = 'wafer_holder' and column_name = 'total_uses') then

		Alter TABLE "wafer_holder" ADD column "total_uses" INTEGER default 0;
	end if;
------------------------------------------------------------------------------------------------
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'metal_usage') then 

					CREATE TABLE "metal_usage"
					(
					  "metal_name" text,
					  "total_usage" real,
					  "anode_a_usage" real,
					  "anode_b_usage" real,
					  "membrance_a_usage" real,
					  "membrance_b_usage" real,
					  "total_wafers" integer,
					  "anode_a_wafers" integer,
					  "anode_b_wafers" integer,
					  "create_time" timestamp without time zone,
					  "update_time" timestamp without time zone,	
					  CONSTRAINT "metal_usage_pkey" PRIMARY KEY ("metal_name" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "metal_usage"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "metal_usage" TO postgres;

	end if; 

------------------------------------------------------------------------------------------------
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'reservoir_usage') then 

					CREATE TABLE "reservoir_usage"
					(
					  "reservoir_name" text,
					  "total_usage" real,
					  "bath_usage" real,
					  "anode_usage" real,
					  "membrance_usage" real,
					  "total_wafers" integer,
					  "bath_usage_days" integer,
					  "bath_reset_time" timestamp without time zone,
					  "create_time" timestamp without time zone,
					  "update_time" timestamp without time zone,	
					  CONSTRAINT "reservoir_usage_pkey" PRIMARY KEY ("reservoir_name" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "reservoir_usage"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "reservoir_usage" TO postgres;

	end if; 
	if not exists(select * from information_schema.columns 
		where table_catalog = CURRENT_CATALOG  and table_schema = CURRENT_SCHEMA  and table_name = 'reservoir_usage' and column_name = 'cmm_membrance_usage') then

		Alter TABLE "reservoir_usage" ADD column cmm_membrance_usage REAL default 0;
	end if;
	if not exists(select * from information_schema.columns 
		where table_catalog = CURRENT_CATALOG  and table_schema = CURRENT_SCHEMA  and table_name = 'reservoir_usage' and column_name = 'cmm_anode_usage') then

		Alter TABLE "reservoir_usage" ADD column cmm_anode_usage REAL default 0;
	end if;
 ------------------------------------------------------------------------------------------------
if not exists(select * from information_schema.tables  
		where  
		table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
		and table_name = 'wafer_holder_history') then 

			CREATE TABLE "wafer_holder_history"
			(
			  "gid" serial NOT NULL,
			  "wafer_holder_guid" text,
			  "operation" text,
			  "operation_time" timestamp without time zone,
			  "station" text,
			  CONSTRAINT "wafer_holder_history_pkey" PRIMARY KEY ("gid" )
			)
			WITH (
			  OIDS=FALSE
			);
			ALTER TABLE "wafer_holder_history"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "wafer_holder_history" TO postgres;

end if; 
------------------------------------------------------------------------------------------------
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'alarm_list') then 

					CREATE TABLE "alarm_list"
					(
					  "gid" serial NOT NULL,
					  "module_name" text,
					  "module_state" text,
					  "module_step" integer,
					  "module_cmd" integer,
					  "alarm_msg" text,
					  "data_item" text,
					  "alarm_type" integer,
					  "create_time" timestamp without time zone,
					  "update_time" timestamp without time zone,	
					  CONSTRAINT "alarm_list_pkey" PRIMARY KEY (gid )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "alarm_list"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "alarm_list" TO postgres;

	end if; 
	------------------------------------------------------------------------------------------------
 if not exists(select * from information_schema.tables  
				where  
				table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
				and table_name = 'alarm_list_history') then 

					CREATE TABLE "alarm_list_history"
					(
					  "gid" serial NOT NULL,
					  "module_name" text,
					  "module_state" text,
					  "module_step" integer,
					  "module_cmd" integer,
					  "alarm_msg" text,
					  "data_item" text,
					  "alarm_type" integer,
					  "alarm_operation" text,
					  "create_time" timestamp without time zone,
					  "update_time" timestamp without time zone,	
					  CONSTRAINT "alarm_list_history_pkey" PRIMARY KEY ("gid" )
					)
					WITH (
					  OIDS=FALSE
					);
					ALTER TABLE "alarm_list_history"
					OWNER TO postgres;
					GRANT SELECT ON TABLE "alarm_list_history" TO postgres;

	end if; 
------------------------------------------------------------------------------------------------
if not exists(select * from information_schema.tables  
        where  
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA 
            and table_name = 'user') then 
 
			CREATE TABLE "user"
			(
				"No" serial PRIMARY KEY,
				"UserName" VARCHAR(100),
				"Role" VARCHAR(100),
				"Password" VARCHAR(100),
				"Notes" text
			)
			WITH (
			OIDS=FALSE
			);
			ALTER TABLE "user"
			OWNER TO postgres;
			GRANT SELECT ON TABLE "user" TO postgres; 

    end if;
-------------------------------------------------------------------------------------------------
end; 
$$ 
language 'plpgsql'; 
 
select update_db_model(); 

CREATE OR REPLACE FUNCTION batch_delete_tables(text)
RETURNS int AS
$$
DECLARE
    r RECORD;
    count int;
BEGIN
    count := 0;
FOR r IN SELECT tablename FROM pg_tables where tablename like $1 || '%'  LOOP
    RAISE NOTICE 'tablename: %', r.tablename;
    EXECUTE 'DROP TABLE "' || r.tablename || '" CASCADE';
    count := count + 1;
END LOOP;
RETURN count;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;