I was not able to reproduce it. I arrived at the following decoded schema and
query:
CREATE TABLE version (
major_version INTEGER NOT NULL PRIMARY KEY,
minor_version INTEGER NOT NULL,
paradox_upload TEXT,
converted TIMESTAMP NOT NULL,
created TIMESTAMP NOT NULL
);
CREATE TABLE mem_class (
class_name VARCHAR(16) NOT NULL PRIMARY KEY,
description VARCHAR(32) NOT NULL UNIQUE,
base_subscription INTEGER NOT NULL,
discount INTEGER NOT NULL,
renewal_period INTEGER NOT NULL,
joining_fee INTEGER NOT NULL,
votes INTEGER NOT NULL,
renewal_notice INTEGER NOT NULL CHECK(renewal_notice in (0, 1)),
is_active INTEGER NOT NULL CHECK(is_active in (0, 1)),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_dates (
renewal_date DATE NOT NULL PRIMARY KEY,
discount_end_date DATE NOT NULL,
membership_ceases DATE NOT NULL,
joiner_rollfwd_date DATE NOT NULL,
last_renewal_date DATE NOT NULL,
next_renewal_date DATE NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE pmt_method (
pmt_method VARCHAR(16) NOT NULL PRIMARY KEY,
method_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_source (
source VARCHAR(32) NOT NULL PRIMARY KEY,
source_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE nonren_reason (
reason VARCHAR(64) NOT NULL PRIMARY KEY,
reason_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone_type (
phone_type VARCHAR(16) NOT NULL PRIMARY KEY,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE role_type (
role_type VARCHAR(32) NOT NULL PRIMARY KEY,
role_code VARCHAR(1) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE county (
county VARCHAR(32) NOT NULL PRIMARY KEY,
county_code VARCHAR(2) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE adj_county (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
adj_county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT adj_county_key UNIQUE (county, adj_county)
);
CREATE TABLE country (
country VARCHAR(32) NOT NULL PRIMARY KEY,
country_code VARCHAR(2) NOT NULL UNIQUE,
calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE region (
region VARCHAR(32) NOT NULL PRIMARY KEY,
region_code VARCHAR(2) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE in_region (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
region VARCHAR(32) NOT NULL REFERENCES region (region) ON UPDATE RESTRICT
ON DELETE RESTRICT,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT in_region_key UNIQUE (region, county, country)
);
CREATE TABLE post_area (
outward_code VARCHAR(4) NOT NULL PRIMARY KEY,
area_name VARCHAR(32) NOT NULL UNIQUE,
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
easting INTEGER NOT NULL,
northing INTEGER NOT NULL,
grid_ref CHARACTER(8) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE person (
person_id INTEGER NOT NULL PRIMARY KEY,
active INTEGER NOT NULL CHECK(active in (0, 1)),
title VARCHAR(20) NOT NULL,
initials VARCHAR(30) NOT NULL,
forename VARCHAR(40) NOT NULL,
surname VARCHAR(40) NOT NULL,
created TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE member (
member_no VARCHAR(6) NOT NULL PRIMARY KEY,
primary_person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
address_1 VARCHAR(30) NOT NULL,
address_2 VARCHAR(30) NOT NULL,
address_3 VARCHAR(30) NOT NULL,
post_town VARCHAR(24) NOT NULL,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
post_code VARCHAR(16) NOT NULL,
country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE
RESTRICT ON DELETE RESTRICT,
share_info CHAR(8) NOT NULL,
joined DATE NOT NULL,
non_renewal VARCHAR(64) NOT NULL,
source VARCHAR(32) NOT NULL,
note VARCHAR(256) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pers (
person_id INTEGER NOT NULL PRIMARY KEY REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,
member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pmts (
payment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE
RESTRICT ON DELETE RESTRICT,
class_name VARCHAR(16) NOT NULL REFERENCES mem_class (class_name) ON UPDATE
RESTRICT ON DELETE RESTRICT,
renewed DATE NOT NULL,
expires DATE NOT NULL,
pmt_method VARCHAR(16) NOT NULL REFERENCES pmt_method (pmt_method) ON
UPDATE RESTRICT ON DELETE RESTRICT,
pmt_ref VARCHAR(18) NOT NULL,
gift_aid INTEGER NOT NULL CHECK(gift_aid in (0, 1)),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone (
phone_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
number VARCHAR(14) NOT NULL CHECK(number REGEXP '^[0-9 ]*$'),
phone_type VARCHAR(16) NOT NULL REFERENCES phone_type (phone_type) ON
UPDATE RESTRICT ON DELETE RESTRICT,
comment VARCHAR(64) NOT NULL,
date_assigned TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT phone_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE email (
email_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
address VARCHAR(254) NOT NULL,
comment VARCHAR(64) NOT NULL,
date_assigned TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT email_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE roles (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
role VARCHAR(32) NOT NULL REFERENCES role_type (role_type) ON UPDATE
RESTRICT ON DELETE RESTRICT,
expires DATE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT roles_key UNIQUE (person_id, role)
);
CREATE TABLE reps_area (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
area VARCHAR(32) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT reps_area_key UNIQUE (person_id, area)
);
CREATE TABLE sel_pers (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
reason VARCHAR(32) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE INDEX mem_pers_mem_no_index ON mem_pers (member_no);
CREATE INDEX mem_pmts_mem_no_index ON mem_pmts (member_no);
CREATE INDEX last_change_index ON mem_pmts (last_changed);
CREATE INDEX email_assigned_index ON email (date_assigned);
CREATE INDEX phone_assigned_index ON phone (date_assigned);
CREATE INDEX sel_pers_index ON sel_pers (person_id);
CREATE VIEW current_member_status
AS SELECT latest.payment_id, latest.member_no, latest.class_name,
latest.renewed,
latest.expires, latest.pmt_method, latest.pmt_ref, latest.gift_aid,
latest.change_reason, latest.last_changed
FROM mem_pmts latest
WHERE NOT EXISTS (SELECT 'x' FROM mem_pmts even_later
WHERE even_later.member_no = latest.member_no
AND even_later.last_changed > latest.last_changed);
And the query was:
select m.member_no, p.title, p.initials, p.forename, p.surname,
m.address_1, m.address_2, m.address_3, m.post_town, m.county,
m.post_code, m.country, s.class_name, m.joined, s.renewed,
s.expires, s.pmt_method, m.non_renewal, m.share_info,
m.last_changed, p.person_id, r.region
from person p join mem_pers mp on p.person_id = mp.person_id
join member m on mp.member_no = m.member_no
join current_member_status s on m.member_no = s.member_no
left join in_region r on m.county = r.county
join mem_dates d on s.expires > d.renewal_date where m.non_renewal = ''
and p.person_id in
(200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077);
Plan is generated, no crash, but no data so the generated VDBE code did not
actually run ...
sqlite> .eqp on
sqlite> select m.member_no, p.title, p.initials, p.forename, p.surname,
...> m.address_1, m.address_2, m.address_3, m.post_town, m.county,
...> m.post_code, m.country, s.class_name, m.joined, s.renewed,
...> s.expires, s.pmt_method, m.non_renewal, m.share_info,
...> m.last_changed, p.person_id, r.region
...> from person p join mem_pers mp on p.person_id = mp.person_id
...> join member m on mp.member_no = m.member_no
...> join current_member_status s on m.member_no = s.member_no
...> left join in_region r on m.county = r.county
...> join mem_dates d on s.expires > d.renewal_date where m.non_renewal =
''
...> and p.person_id in
...>
(200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077)
...> ;
QUERY PLAN
|--SEARCH TABLE mem_pers AS mp USING INTEGER PRIMARY KEY (rowid=?) (~26 rows)
|--SEARCH TABLE person AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE member AS m USING INDEX sqlite_autoindex_member_1 (member_no=?)
(~1 row)
|--SEARCH TABLE mem_pmts AS latest USING INDEX mem_pmts_mem_no_index
(member_no=?) (~8 rows)
|--CORRELATED SCALAR SUBQUERY 1
| `--SEARCH TABLE mem_pmts AS even_later USING INDEX mem_pmts_mem_no_index
(member_no=?) (~9 rows)
|--SEARCH TABLE in_region AS r USING AUTOMATIC COVERING INDEX (county=?) (~20
rows)
`--SEARCH TABLE mem_dates AS d USING COVERING INDEX
sqlite_autoindex_mem_dates_1 (renewal_date<?) (~262144 rows)
sqlite> .exit
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
>Sent: Wednesday, 6 March, 2019 04:31
>To: SQLite mailing list
>Subject: Re: [sqlite] Segmentation fault running a query
>
>On 3/6/19, Peter Hardman <pe...@somborneshetlands.co.uk> wrote:
>>
>> The schema (of a very much cut down database) is attached.
>
>I translated the attached schema into SQL and appended the query. I
>uploaded the result to https://sqlite.org/tmp/peter-hardman-001.txt
>
>I have run the resulting script through every version of SQLite I
>have
>at hand, and none of them given any trouble. I also ran them under
>Valgrind. No problems detected.
>
>Can anybody else reproduce the problem?
>
>--
>D. Richard Hipp
>d...@sqlite.org
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CREATE TABLE version (
major_version INTEGER NOT NULL PRIMARY KEY,
minor_version INTEGER NOT NULL,
paradox_upload TEXT,
converted TIMESTAMP NOT NULL,
created TIMESTAMP NOT NULL
);
CREATE TABLE mem_class (
class_name VARCHAR(16) NOT NULL PRIMARY KEY,
description VARCHAR(32) NOT NULL UNIQUE,
base_subscription INTEGER NOT NULL,
discount INTEGER NOT NULL,
renewal_period INTEGER NOT NULL,
joining_fee INTEGER NOT NULL,
votes INTEGER NOT NULL,
renewal_notice INTEGER NOT NULL CHECK(renewal_notice in (0, 1)),
is_active INTEGER NOT NULL CHECK(is_active in (0, 1)),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_dates (
renewal_date DATE NOT NULL PRIMARY KEY,
discount_end_date DATE NOT NULL,
membership_ceases DATE NOT NULL,
joiner_rollfwd_date DATE NOT NULL,
last_renewal_date DATE NOT NULL,
next_renewal_date DATE NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE pmt_method (
pmt_method VARCHAR(16) NOT NULL PRIMARY KEY,
method_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_source (
source VARCHAR(32) NOT NULL PRIMARY KEY,
source_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE nonren_reason (
reason VARCHAR(64) NOT NULL PRIMARY KEY,
reason_code VARCHAR(8) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone_type (
phone_type VARCHAR(16) NOT NULL PRIMARY KEY,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE role_type (
role_type VARCHAR(32) NOT NULL PRIMARY KEY,
role_code VARCHAR(1) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE county (
county VARCHAR(32) NOT NULL PRIMARY KEY,
county_code VARCHAR(2) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE adj_county (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
adj_county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT adj_county_key UNIQUE (county, adj_county)
);
CREATE TABLE country (
country VARCHAR(32) NOT NULL PRIMARY KEY,
country_code VARCHAR(2) NOT NULL UNIQUE,
calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE region (
region VARCHAR(32) NOT NULL PRIMARY KEY,
region_code VARCHAR(2) NOT NULL UNIQUE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE in_region (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
region VARCHAR(32) NOT NULL REFERENCES region (region) ON UPDATE RESTRICT
ON DELETE RESTRICT,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT in_region_key UNIQUE (region, county, country)
);
CREATE TABLE post_area (
outward_code VARCHAR(4) NOT NULL PRIMARY KEY,
area_name VARCHAR(32) NOT NULL UNIQUE,
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
easting INTEGER NOT NULL,
northing INTEGER NOT NULL,
grid_ref CHARACTER(8) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE person (
person_id INTEGER NOT NULL PRIMARY KEY,
active INTEGER NOT NULL CHECK(active in (0, 1)),
title VARCHAR(20) NOT NULL,
initials VARCHAR(30) NOT NULL,
forename VARCHAR(40) NOT NULL,
surname VARCHAR(40) NOT NULL,
created TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE member (
member_no VARCHAR(6) NOT NULL PRIMARY KEY,
primary_person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
address_1 VARCHAR(30) NOT NULL,
address_2 VARCHAR(30) NOT NULL,
address_3 VARCHAR(30) NOT NULL,
post_town VARCHAR(24) NOT NULL,
county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT
ON DELETE RESTRICT,
post_code VARCHAR(16) NOT NULL,
country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE
RESTRICT ON DELETE RESTRICT,
share_info CHAR(8) NOT NULL,
joined DATE NOT NULL,
non_renewal VARCHAR(64) NOT NULL,
source VARCHAR(32) NOT NULL,
note VARCHAR(256) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pers (
person_id INTEGER NOT NULL PRIMARY KEY REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,
member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE
RESTRICT ON DELETE RESTRICT,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pmts (
payment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE
RESTRICT ON DELETE RESTRICT,
class_name VARCHAR(16) NOT NULL REFERENCES mem_class (class_name) ON UPDATE
RESTRICT ON DELETE RESTRICT,
renewed DATE NOT NULL,
expires DATE NOT NULL,
pmt_method VARCHAR(16) NOT NULL REFERENCES pmt_method (pmt_method) ON
UPDATE RESTRICT ON DELETE RESTRICT,
pmt_ref VARCHAR(18) NOT NULL,
gift_aid INTEGER NOT NULL CHECK(gift_aid in (0, 1)),
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone (
phone_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
number VARCHAR(14) NOT NULL CHECK(number REGEXP '^[0-9 ]*$'),
phone_type VARCHAR(16) NOT NULL REFERENCES phone_type (phone_type) ON
UPDATE RESTRICT ON DELETE RESTRICT,
comment VARCHAR(64) NOT NULL,
date_assigned TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT phone_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE email (
email_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
address VARCHAR(254) NOT NULL,
comment VARCHAR(64) NOT NULL,
date_assigned TIMESTAMP NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT email_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE roles (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
role VARCHAR(32) NOT NULL REFERENCES role_type (role_type) ON UPDATE
RESTRICT ON DELETE RESTRICT,
expires DATE,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT roles_key UNIQUE (person_id, role)
);
CREATE TABLE reps_area (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
area VARCHAR(32) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL,
CONSTRAINT reps_area_key UNIQUE (person_id, area)
);
CREATE TABLE sel_pers (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT
ON DELETE RESTRICT,
reason VARCHAR(32) NOT NULL,
change_reason VARCHAR(32) NOT NULL,
last_changed TIMESTAMP NOT NULL
);
CREATE INDEX mem_pers_mem_no_index ON mem_pers (member_no);
CREATE INDEX mem_pmts_mem_no_index ON mem_pmts (member_no);
CREATE INDEX last_change_index ON mem_pmts (last_changed);
CREATE INDEX email_assigned_index ON email (date_assigned);
CREATE INDEX phone_assigned_index ON phone (date_assigned);
CREATE INDEX sel_pers_index ON sel_pers (person_id);
CREATE VIEW current_member_status
AS SELECT latest.payment_id, latest.member_no, latest.class_name,
latest.renewed,
latest.expires, latest.pmt_method, latest.pmt_ref, latest.gift_aid,
latest.change_reason, latest.last_changed
FROM mem_pmts latest
WHERE NOT EXISTS (SELECT 'x' FROM mem_pmts even_later
WHERE even_later.member_no = latest.member_no
AND even_later.last_changed > latest.last_changed);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
