Navigation

← Previous Changeset
Next Changeset →

Changeset 9542

Timestamp:

05/09/08 12:31:09 (1 week ago)

Author:

dbs

Message:

Make all foreign keys deferrable, so we can DELETE and INSERT inside a transaction without constraints hobbling us

Location:

trunk/Open-ILS/src/sql/Pg

Files:

: 11 modified

005.schema.actors.sql (modified) (14 diffs)
006.schema.permissions.sql (modified) (6 diffs)
011.schema.authority.sql (modified) (1 diff)
012.schema.vandelay.sql (modified) (7 diffs)
080.schema.money.sql (modified) (2 diffs)
090.schema.action.sql (modified) (12 diffs)
1.2.1-1.2.2-upgrade-db.sql (modified) (1 diff)
100.circ_matrix.sql (modified) (4 diffs)
110.hold_matrix.sql (modified) (2 diffs)
300.schema.staged_search.sql (modified) (1 diff)
reporter-schema.sql (modified) (3 diffs)

Legend:

: Unmodified
: Added
: Removed

trunk/Open-ILS/src/sql/Pg/005.schema.actors.sql

r9243	r9542
33	33	email TEXT,
34	34	passwd TEXT NOT NULL,
35		standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id),
36		ident_type INT NOT NULL REFERENCES config.identification_type (id),
	35	standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
	36	ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
37	37	ident_value TEXT,
38		ident_type2 INT REFERENCES config.identification_type (id),
	38	ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
39	39	ident_value2 TEXT,
40		net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id),
	40	net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
41	41	photo_url TEXT,
42	42	prefix TEXT,
…	…
135	135	CREATE TABLE actor.usr_note (
136	136	id BIGSERIAL PRIMARY KEY,
137		usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
138		creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
	137	usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	138	creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
139	139	create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
140	140	pub BOOL NOT NULL DEFAULT FALSE,
…	…
146	146	CREATE TABLE actor.usr_standing_penalty (
147	147	id SERIAL PRIMARY KEY,
148		usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
	148	usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
149	149	penalty_type TEXT NOT NULL
150	150	);
…	…
174	174	CREATE TABLE actor.usr_setting (
175	175	id BIGSERIAL PRIMARY KEY,
176		usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
	176	usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
177	177	name TEXT NOT NULL,
178	178	value TEXT NOT NULL,
…	…
307	307	CREATE TABLE actor.card (
308	308	id SERIAL PRIMARY KEY,
309		usr INT NOT NULL REFERENCES actor.usr (id),
	309	usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
310	310	barcode TEXT NOT NULL UNIQUE,
311	311	active BOOL NOT NULL DEFAULT TRUE
…	…
345	345	opac_label TEXT NOT NULL,
346	346	depth INT NOT NULL,
347		parent INT REFERENCES actor.org_unit_type (id),
	347	parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
348	348	can_have_vols BOOL NOT NULL DEFAULT TRUE,
349	349	can_have_users BOOL NOT NULL DEFAULT TRUE
…	…
353	353	CREATE TABLE actor.org_unit (
354	354	id SERIAL PRIMARY KEY,
355		parent_ou INT REFERENCES actor.org_unit (id),
356		ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
	355	parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
	356	ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
357	357	ill_address INT,
358	358	holds_address INT,
…	…
379	379	CREATE TABLE actor.org_lasso_map (
380	380	id SERIAL PRIMARY KEY,
381		lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE,
382		org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE
	381	lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	382	org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
383	383	);
384	384	CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
…	…
394	394
395	395	CREATE TABLE actor.hours_of_operation (
396		id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE,
	396	id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
397	397	dow_0_open TIME NOT NULL DEFAULT '09:00',
398	398	dow_0_close TIME NOT NULL DEFAULT '17:00',
…	…
413	413	CREATE TABLE actor.org_unit_closed (
414	414	id SERIAL PRIMARY KEY,
415		org_unit INT NOT NULL REFERENCES actor.org_unit (id),
	415	org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
416	416	close_start TIMESTAMP WITH TIME ZONE NOT NULL,
417	417	close_end TIMESTAMP WITH TIME ZONE NOT NULL,
…	…
423	423	id SERIAL PRIMARY KEY,
424	424	name TEXT NOT NULL UNIQUE,
425		owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
	425	owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
426	426	);
427	427
428	428	CREATE TABLE actor.usr_org_unit_opt_in (
429	429	id SERIAL PRIMARY KEY,
430		org_unit INT NOT NULL REFERENCES actor.org_unit (id),
431		usr INT NOT NULL REFERENCES actor.usr (id),
432		staff INT NOT NULL REFERENCES actor.usr (id),
	430	org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
	431	usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	432	staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
433	433	opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
434		opt_in_ws INT NOT NULL REFERENCES actor.workstation (id),
	434	opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
435	435	CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
436	436	);
…	…
438	438	CREATE TABLE actor.org_unit_setting (
439	439	id BIGSERIAL PRIMARY KEY,
440		org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
	440	org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
441	441	name TEXT NOT NULL,
442	442	value TEXT NOT NULL,
…	…
475	475	within_city_limits BOOL NOT NULL DEFAULT TRUE,
476	476	address_type TEXT NOT NULL DEFAULT 'MAILING',
477		usr INT NOT NULL REFERENCES actor.usr (id),
	477	usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
478	478	street1 TEXT NOT NULL,
479	479	street2 TEXT,
…	…
499	499	valid BOOL NOT NULL DEFAULT TRUE,
500	500	address_type TEXT NOT NULL DEFAULT 'MAILING',
501		org_unit INT NOT NULL REFERENCES actor.org_unit (id),
	501	org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
502	502	street1 TEXT NOT NULL,
503	503	street2 TEXT,

trunk/Open-ILS/src/sql/Pg/006.schema.permissions.sql

r8690	r9542
14	14	id SERIAL PRIMARY KEY,
15	15	name TEXT NOT NULL UNIQUE,
16		parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT,
	16	parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
17	17	usergroup BOOL NOT NULL DEFAULT TRUE,
18	18	perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
…	…
24	24	CREATE TABLE permission.grp_perm_map (
25	25	id SERIAL PRIMARY KEY,
26		grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
27		perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
	26	grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	27	perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
28	28	depth INT NOT NULL,
29	29	grantable BOOL NOT NULL DEFAULT FALSE,
…	…
33	33	CREATE TABLE permission.usr_perm_map (
34	34	id SERIAL PRIMARY KEY,
35		usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
36		perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
	35	usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	36	perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
37	37	depth INT NOT NULL,
38	38	grantable BOOL NOT NULL DEFAULT FALSE,
…	…
42	42	CREATE TABLE permission.usr_object_perm_map (
43	43	id SERIAL PRIMARY KEY,
44		usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
45		perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
	44	usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	45	perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
46	46	object_type TEXT NOT NULL,
47	47	object_id TEXT NOT NULL,
…	…
54	54	CREATE TABLE permission.usr_grp_map (
55	55	id SERIAL PRIMARY KEY,
56		usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
57		grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
	56	usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	57	grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
58	58	CONSTRAINT usr_grp_once UNIQUE (usr,grp)
59	59	);
…	…
94	94	CREATE TABLE permission.usr_work_ou_map (
95	95	id SERIAL PRIMARY KEY,
96		usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
97		work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
	96	usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	97	work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
98	98	CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
99	99	);

trunk/Open-ILS/src/sql/Pg/011.schema.authority.sql

r4794	r9542
24	24	CREATE TABLE authority.record_note (
25	25	id BIGSERIAL PRIMARY KEY,
26		record BIGINT NOT NULL REFERENCES authority.record_entry (id),
	26	record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
27	27	value TEXT NOT NULL,
28	28	creator INT NOT NULL DEFAULT 1,

trunk/Open-ILS/src/sql/Pg/012.schema.vandelay.sql

r9445	r9542
7	7	CREATE TABLE vandelay.queue (
8	8	id BIGSERIAL PRIMARY KEY,
9		owner INT NOT NULL REFERENCES actor.usr (id),
	9	owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
10	10	name TEXT NOT NULL,
11	11	complete BOOL NOT NULL DEFAULT FALSE,
…	…
58	58
59	59	CREATE TABLE vandelay.queued_bib_record (
60		queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE,
61		bib_source INT REFERENCES config.bib_source (id),
62		imported_as INT REFERENCES biblio.record_entry (id)
	60	queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	61	bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
	62	imported_as INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
63	63	) INHERITS (vandelay.queued_record);
64	64	ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
…	…
66	66	CREATE TABLE vandelay.queued_bib_record_attr (
67	67	id BIGSERIAL PRIMARY KEY,
68		record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id),
69		field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id),
	68	record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
	69	field INT NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
70	70	attr_value TEXT NOT NULL
71	71	);
…	…
74	74	id BIGSERIAL PRIMARY KEY,
75	75	field_type TEXT NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
76		matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE,
77		queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE,
78		eg_record BIGINT REFERENCES biblio.record_entry (id)
	76	matched_attr INT REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	77	queued_record BIGINT REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	78	eg_record BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
79	79	);
80	80
…	…
187	187
188	188	CREATE TABLE vandelay.queued_authority_record (
189		queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE,
190		imported_as INT REFERENCES authority.record_entry (id)
	189	queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	190	imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
191	191	) INHERITS (vandelay.queued_record);
192	192	ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
…	…
194	194	CREATE TABLE vandelay.queued_authority_record_attr (
195	195	id BIGSERIAL PRIMARY KEY,
196		record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id),
197		field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id),
	196	record BIGINT NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
	197	field INT NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
198	198	attr_value TEXT NOT NULL
199	199	);
…	…
201	201	CREATE TABLE vandelay.authority_match (
202	202	id BIGSERIAL PRIMARY KEY,
203		matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE,
204		queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE,
205		eg_record BIGINT REFERENCES authority.record_entry (id)
	203	matched_attr INT REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	204	queued_record BIGINT REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	205	eg_record BIGINT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
206	206	);
207	207

trunk/Open-ILS/src/sql/Pg/080.schema.money.sql

r7945	r9542
7	7	CREATE TABLE money.collections_tracker (
8	8	id BIGSERIAL PRIMARY KEY,
9		usr INT NOT NULL REFERENCES actor.usr (id), -- actor.usr.id
10		collector INT NOT NULL REFERENCES actor.usr (id),
11		location INT NOT NULL REFERENCES actor.org_unit (id),
	9	usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, -- actor.usr.id
	10	collector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	11	location INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
12	12	enter_time TIMESTAMP WITH TIME ZONE
13	13	);
…	…
325	325
326	326	CREATE TABLE money.bnm_desk_payment (
327		cash_drawer INT REFERENCES actor.workstation (id)
	327	cash_drawer INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED
328	328	) INHERITS (money.bnm_payment);
329	329	ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);

trunk/Open-ILS/src/sql/Pg/090.schema.action.sql

r8506	r9542
7	7	CREATE TABLE action.in_house_use (
8	8	id SERIAL PRIMARY KEY,
9		item BIGINT NOT NULL REFERENCES asset.copy (id),
10		staff INT NOT NULL REFERENCES actor.usr (id),
11		org_unit INT NOT NULL REFERENCES actor.org_unit (id),
	9	item BIGINT NOT NULL REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED,
	10	staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	11	org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
12	12	use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
13	13	);
…	…
15	15	CREATE TABLE action.non_cataloged_circulation (
16	16	id SERIAL PRIMARY KEY,
17		patron INT NOT NULL REFERENCES actor.usr (id),
18		staff INT NOT NULL REFERENCES actor.usr (id),
19		circ_lib INT NOT NULL REFERENCES actor.org_unit (id),
20		item_type INT NOT NULL REFERENCES config.non_cataloged_type (id),
	17	patron INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	18	staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	19	circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
	20	item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED,
21	21	circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
22	22	);
…	…
24	24	CREATE TABLE action.non_cat_in_house_use (
25	25	id SERIAL PRIMARY KEY,
26		item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id),
27		staff INT NOT NULL REFERENCES actor.usr (id),
28		org_unit INT NOT NULL REFERENCES actor.org_unit (id),
	26	item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED,
	27	staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	28	org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
29	29	use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
30	30	);
…	…
32	32	CREATE TABLE action.survey (
33	33	id SERIAL PRIMARY KEY,
34		owner INT NOT NULL REFERENCES actor.org_unit (id),
	34	owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
35	35	start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
36	36	end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
…	…
46	46	CREATE TABLE action.survey_question (
47	47	id SERIAL PRIMARY KEY,
48		survey INT NOT NULL REFERENCES action.survey,
	48	survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
49	49	question TEXT NOT NULL
50	50	);
…	…
52	52	CREATE TABLE action.survey_answer (
53	53	id SERIAL PRIMARY KEY,
54		question INT NOT NULL REFERENCES action.survey_question,
	54	question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
55	55	answer TEXT NOT NULL
56	56	);
…	…
62	62	response_group_id INT,
63	63	usr INT, -- REFERENCES actor.usr
64		survey INT NOT NULL REFERENCES action.survey,
65		question INT NOT NULL REFERENCES action.survey_question,
66		answer INT NOT NULL REFERENCES action.survey_answer,
	64	survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
	65	question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
	66	answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
67	67	answer_date TIMESTAMP WITH TIME ZONE,
68	68	effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
…	…
168	168	cancel_time TIMESTAMP WITH TIME ZONE,
169	169	target BIGINT NOT NULL, -- see hold_type
170		current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
171		fulfillment_staff INT REFERENCES actor.usr (id),
172		fulfillment_lib INT REFERENCES actor.org_unit (id),
173		request_lib INT NOT NULL REFERENCES actor.org_unit (id),
174		requestor INT NOT NULL REFERENCES actor.usr (id),
175		usr INT NOT NULL REFERENCES actor.usr (id),
	170	current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
	171	fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	172	fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
	173	request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
	174	requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
	175	usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
176	176	selection_ou INT NOT NULL,
177	177	selection_depth INT NOT NULL DEFAULT 0,
178		pickup_lib INT NOT NULL REFERENCES actor.org_unit,
	178	pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
179	179	hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')),
180	180	holdable_formats TEXT,
…	…
194	194	CREATE TABLE action.hold_notification (
195	195	id SERIAL PRIMARY KEY,
196		hold INT NOT NULL REFERENCES action.hold_request (id),
197		notify_staff INT REFERENCES actor.usr (id),
	196	hold INT NOT NULL REFERENCES action.hold_request (id) DEFERRABLE INITIALLY DEFERRED,
	197	notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
198	198	notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
199	199	method TEXT NOT NULL, -- email address or phone number
…	…
204	204	CREATE TABLE action.hold_copy_map (
205	205	id SERIAL PRIMARY KEY,
206		hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
207		target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
	206	hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
	207	target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
208	208	CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
209	209	);
…	…
215	215	source_send_time TIMESTAMP WITH TIME ZONE,
216	216	dest_recv_time TIMESTAMP WITH TIME ZONE,
217		target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
218		source INT NOT NULL REFERENCES actor.org_unit (id),
219		dest &nb