Vlad Seryakov ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Segmentation fault when changing view
Long Description
When i dropped column quantity in view package_tree_view, recreated this view, but
didn't recreated the view package_packages_tree_view and ran SQL statement below in
Example, the server died with segfault.
Earlier i remember it complaints about missing cache object but didn; crashed.
Thank you
Database schema:
CREATE TABLE usage_rates (
rate_id VARCHAR(16) NOT NULL CHECK(rate_id != ''),
rate_name VARCHAR(64) NOT NULL,
description VARCHAR(255) NULL,
CONSTRAINT usage_rates_pk PRIMARY KEY(rate_id),
CONSTRAINT usage_rates_un UNIQUE(rate_name)
);
CREATE TABLE prices (
price_id INTEGER NOT NULL CHECK(price_id > 0),
install_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
periodic_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
usage_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
usage_rate VARCHAR(16) NULL
CONSTRAINT service_usage_rate_fk REFERENCES usage_rates(rate_id),
termination_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
CONSTRAINT prices_pk PRIMARY KEY(price_id)
);
CREATE TABLE service_status (
status_id VARCHAR(16) NOT NULL CHECK(status_id != ''),
status_name VARCHAR(64) NOT NULL,
precedence SMALLINT NOT NULL,
description VARCHAR(255) NULL,
CONSTRAINT service_status_pk PRIMARY KEY(status_id),
CONSTRAINT service_status_un UNIQUE(status_name)
);
CREATE TABLE services (
service_id INTEGER NOT NULL CHECK(service_id > 0),
service_name VARCHAR(64) NOT NULL,
service_status VARCHAR(16) NOT NULL
CONSTRAINT service_status_fk REFERENCES service_status(status_id),
service_owner INTEGER NULL
CONSTRAINT service_owner_fk REFERENCES services(service_id),
description VARCHAR(255) NULL,
path VARCHAR(255) NULL,
CONSTRAINT services_pk PRIMARY KEY(service_id),
CONSTRAINT service_un UNIQUE(service_name),
CONSTRAINT service_owner_ck CHECK(service_id != service_owner)
);
CREATE TABLE packages (
package_id INTEGER NOT NULL CHECK(package_id > 0),
package_name VARCHAR(64) NOT NULL,
package_status VARCHAR(16) NOT NULL
CONSTRAINT package_status_fk REFERENCES service_status(status_id),
start_date DATETIME NOT NULL,
stop_date DATETIME NOT NULL,
description VARCHAR(255) NULL,
install_price NUMERIC(5,2) NULL,
periodic_price NUMERIC(5,2) NULL,
termination_price NUMERIC(5,2) NULL,
CONSTRAINT packages_pk PRIMARY KEY(package_id),
CONSTRAINT packages_un UNIQUE(package_name)
);
CREATE TABLE package_services (
package_id INTEGER NOT NULL REFERENCES packages(package_id),
service_id INTEGER NOT NULL REFERENCES services(service_id),
quantity SMALLINT DEFAULT 1 NOT NULL,
description VARCHAR(255) NULL,
CONSTRAINT package_servies_pk PRIMARY KEY(package_id,service_id)
);
CREATE TABLE package_packages (
package_id INTEGER NOT NULL
CONSTRAINT packages_pkg_fk REFERENCES packages(package_id),
package_owner INTEGER NOT NULL
CONSTRAINT packages_pkg_owner_fk REFERENCES packages(package_id),
price_id INTEGER NOT NULL
CONSTRAINT packages_price_fk REFERENCES prices(price_id),
CONSTRAINT packages_pkg_pk PRIMARY KEY(package_id,package_owner),
CONSTRAINT packages_pkg_ck CHECK(package_id != package_owner)
);
CREATE TABLE package_tree (
path VARCHAR(255) NOT NULL,
id INTEGER NOT NULL
CONSTRAINT packages_tree_id_fk REFERENCES packages(package_id),
owner INTEGER NULL
CONSTRAINT packages_tree_o_fk REFERENCES packages(package_id),
tree_level INTEGER NOT NULL,
leaf_node CHAR(1) DEFAULT 'N' NOT NULL
CONSTRAINT packages_leaf_ck CHECK(leaf_node IN ('Y','N')),
path2 VARCHAR(255) NOT NULL,
CONSTRAINT packages_tree_pk PRIMARY KEY(path)
);
DROP VIEW packages_tree_view;
CREATE VIEW packages_tree_view AS
SELECT p.package_id,
p.package_name,
p.package_status,
status_name,
ps.service_id,
ps.quantity,
COALESCE(p.description,ps.description) AS description,
t.path,
t.owner,
t.tree_level,
t.leaf_node,
s.service_name,
s.service_status
FROM packages p,
service_status,
package_tree t
LEFT OUTER JOIN package_services ps ON t.id=ps.package_id
LEFT OUTER JOIN services s ON ps.service_id=s.service_id
WHERE t.id=p.package_id AND
p.package_status=status_id;
DROP VIEW package_packages_tree_view;
CREATE VIEW package_packages_tree_view AS
SELECT pv.*,
pr.price_id,
pr.install_price,
pr.periodic_price,
pr.usage_price,
pr.termination_price
FROM packages_tree_view pv
LEFT OUTER JOIN package_packages pp
ON pv.package_id=pp.package_id AND
pv.owner=pp.package_owner
LEFT OUTER JOIN prices pr ON pr.price_id=pp.price_id;
INSERT INTO service_status (status_id,status_name,precedence,description)
