From 9aa649b8450e4ebab1abb0b46d9104397e3e7965 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 26 Jun 2006 23:24:18 +0000
Subject: [PATCH] removing "char" from the schema...

git-svn-id: svn://svn.open-ils.org/ILS/trunk@4794 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.schema.config.sql    |  4 +--
 Open-ILS/src/sql/Pg/011.schema.authority.sql | 35 +++++++++++++++++---
 Open-ILS/src/sql/Pg/030.schema.metabib.sql   | 24 +++++++-------
 Open-ILS/src/sql/Pg/090.schema.action.sql    |  2 +-
 Open-ILS/src/sql/Pg/900.audit-tables.sql     |  2 +-
 5 files changed, 46 insertions(+), 21 deletions(-)

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index f97ab38934..fad77482ab 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -451,7 +451,7 @@ INSERT INTO config.net_access_level (name) VALUES ('Full');
 INSERT INTO config.net_access_level (name) VALUES ('None');
 
 CREATE TABLE config.audience_map (
-	code		"char"	PRIMARY KEY,
+	code		TEXT	PRIMARY KEY,
 	value		TEXT	NOT NULL,
 	description	TEXT
 );
@@ -470,7 +470,7 @@ j	Juvenile	The item is intended for children and young people, approximate ages
 
 
 CREATE TABLE config.lit_form_map (
-	code		"char"	PRIMARY KEY,
+	code		TEXT	PRIMARY KEY,
 	value		TEXT	NOT NULL,
 	description	TEXT
 );
diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql
index cc64c08fb5..89e87e1c4f 100644
--- a/Open-ILS/src/sql/Pg/011.schema.authority.sql
+++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql
@@ -37,8 +37,8 @@ CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor
 CREATE TABLE authority.rec_descriptor (
 	id		BIGSERIAL PRIMARY KEY,
 	record		BIGINT,
-	record_status	"char",
-	char_encoding	"char"
+	record_status	TEXT,
+	char_encoding	TEXT
 );
 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
 
@@ -46,9 +46,9 @@ CREATE TABLE authority.full_rec (
 	id		BIGSERIAL	PRIMARY KEY,
 	record		BIGINT		NOT NULL,
 	tag		CHAR(3)		NOT NULL,
-	ind1		"char",
-	ind2		"char",
-	subfield	"char",
+	ind1		TEXT,
+	ind2		TEXT,
+	subfield	TEXT,
 	value		TEXT		NOT NULL,
 	index_vector	tsvector	NOT NULL
 );
@@ -60,4 +60,29 @@ CREATE TRIGGER authority_full_rec_fti_trigger
 
 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
 
+CREATE OR REPLACE VIEW authority.tracing_links AS
+	SELECT	main.record AS record,
+		main.id AS main_id,
+		main.tag AS main_tag,
+		main.value AS main_value,
+		substr(link.value,1,1) AS relationship,
+		substr(link.value,2,1) AS use_restriction,
+		substr(link.value,3,1) AS deprecation,
+		substr(link.value,4,1) AS display_restriction,
+		link_value.id AS link_id,
+		link_value.tag AS link_tag,
+		link_value.value AS link_value
+	  FROM	authority.full_rec main
+		JOIN authority.full_rec link
+			ON (	link.record = main.record
+				AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
+				AND link.subfield = 'w' )
+		JOIN authority.full_rec link_value
+			ON (	link_value.record = main.record
+				AND link_value.tag = link.tag
+				AND link_value.subfield = 'a' )
+	  WHERE	main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
+		AND main.subfield = 'a';
+
+
 COMMIT;
diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
index 046878121e..c61bcfefa3 100644
--- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql
+++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
@@ -85,15 +85,15 @@ CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field
 CREATE TABLE metabib.rec_descriptor (
 	id		BIGSERIAL PRIMARY KEY,
 	record		BIGINT,
-	item_type	"char",
-	item_form	"char",
-	bib_level	"char",
-	control_type	"char",
-	char_encoding	"char",
-	enc_level	"char",
-	audience	"char",
-	lit_form	"char",
-	type_mat	"char",
+	item_type	TEXT,
+	item_form	TEXT,
+	bib_level	TEXT,
+	control_type	TEXT,
+	char_encoding	TEXT,
+	enc_level	TEXT,
+	audience	TEXT,
+	lit_form	TEXT,
+	type_mat	TEXT,
 	cat_form	TEXT,
 	pub_status	TEXT,
 	item_lang	TEXT
@@ -119,9 +119,9 @@ CREATE TABLE metabib.full_rec (
 	id		BIGSERIAL	PRIMARY KEY,
 	record		BIGINT		NOT NULL,
 	tag		CHAR(3)		NOT NULL,
-	ind1		"char",
-	ind2		"char",
-	subfield	"char",
+	ind1		TEXT,
+	ind2		TEXT,
+	subfield	TEXT,
 	value		TEXT		NOT NULL,
 	index_vector	tsvector	NOT NULL
 );
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 3a3c95f1a2..617264645f 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -163,7 +163,7 @@ CREATE TABLE action.hold_request (
 	selection_ou		INT				NOT NULL,
 	selection_depth		INT				NOT NULL DEFAULT 0,
 	pickup_lib		INT				NOT NULL REFERENCES actor.org_unit,
-	hold_type		"char"				NOT NULL CHECK (hold_type IN ('M','T','V','C')),
+	hold_type		TEXT				NOT NULL CHECK (hold_type IN ('M','T','V','C')),
 	holdable_formats	TEXT,
 	phone_notify		TEXT,
 	email_notify		BOOL				NOT NULL DEFAULT TRUE
diff --git a/Open-ILS/src/sql/Pg/900.audit-tables.sql b/Open-ILS/src/sql/Pg/900.audit-tables.sql
index 5cac260ca5..e1c0d6cbdd 100644
--- a/Open-ILS/src/sql/Pg/900.audit-tables.sql
+++ b/Open-ILS/src/sql/Pg/900.audit-tables.sql
@@ -9,7 +9,7 @@ BEGIN
 	EXECUTE $$
 			CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
 				audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
-				audit_action	"char"				NOT NULL,
+				audit_action	TEXT				NOT NULL,
 				LIKE $$ || sch || $$.$$ || tbl || $$
 			);
 	$$;
-- 
2.43.2