Here is my sql script:
-- Create an in memory database for the tools rental project.
-- This is expected to be replaced with an actual database
--
-- Create Schema for use
CREATE SCHEMA IF NOT EXISTS Tools_R_Us_Schema;
USE Tools_R_Us_Schema;
-- Create the tool type table for listing the tool types
CREATE TABLE IF NOT EXISTS Tool_Type (
type varchar(20) NOT NULL PRIMARY KEY
);
-- Create the vendor brands table for listing the brands
CREATE TABLE IF NOT EXISTS Vendors (
brand varchar(30) NOT NULL PRIMARY KEY
);
-- Create the tools table for listing the tools
CREATE TABLE IF NOT EXISTS Tools (
code varchar(10) NOT NULL PRIMARY KEY,
type varchar(20) NOT NULL REFERENCES Tool_Type(type),
brand varchar(30) NOT NULL REFERENCES Vendors(brand)
);
-- Create the charge table based on tool type
CREATE TABLE IF NOT EXISTS Tools_Charges (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
type varchar(20) NOT NULL REFERENCES Tool_Type(type),
daily_charge decimal(5,2),
weekday_charge bit,
weekend_charge bit,
holiday_charge bit
);
-- Create the holiday day falls on table
CREATE TABLE IF NOT EXISTS Holiday_Days_Of_Week (
day_of_the_week varchar(15) NOT NULL PRIMARY KEY
);
-- Create the allowed frequencies of holiday table
CREATE TABLE IF NOT EXISTS Holiday_Frequency (
frequency varchar(20) NOT NULL PRIMARY KEY
);
-- Create the holidays table for checking if it is a holiday
CREATE TABLE IF NOT EXISTS Holidays (
name varchar(20) NOT NULL PRIMARY KEY,
holiday_month int,
holiday_day int,
day_of_the_week varchar(15) REFERENCES Holiday_Days_Of_Week(day_of_the_week),
frequency varchar(20) REFERENCES Holiday_Frequency(frequency)
);
-- Create the list of valid statuses for rental
CREATE TABLE IF NOT EXISTS Tool_Status (
status varchar(10) NOT NULL PRIMARY KEY
);
-- Create Rental table for tracking when a tool is rented
CREATE TABLE IF NOT EXISTS Tool_Rentals (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
code varchar(10) NOT NULL REFERENCES Tools(code),
type varchar(20) NOT NULL REFERENCES Tool_Type(type),
brand varchar(30) NOT NULL REFERENCES Vendors(brand),
rental_days int NOT NULL,
checkout_date date NOT NULL,
due_date date,
charge_days int NOT NULL,
due decimal(10,2) NOT NULL,
daily_charge decimal(5,2) NOT NULL,
pre_discount_charge decimal(10,2) NOT NULL,
discount_percent decimal(3,3),
discount_amount decimal(10,2),
final_charge decimal(10,2) NOT NULL,
status varchar(10) NOT NULL REFERENCES Tool_Status(status)
);
-- Populate the tool types
INSERT INTO Tool_Type (type)
VALUES ('ChainSaw'),
('JackHammer'),
('Ladder');
-- Populate the vendor brands table
INSERT INTO Vendors (brand)
VALUES ('Stihl'),
('Werner'),
('DeWalt'),
('Ridgid');
-- Populate the Tools table
INSERT INTO Tools (code, type, brand)
VALUES ('CHNS', 'ChainSaw', 'Stihl'),
('LADW', 'Ladder', 'Werner'),
('JAKD', 'JackHammer', 'DeWalt'),
('JAKR', 'JackHammer', 'Ridgid');
-- Populate the charge table
INSERT INTO Tools_Charges (type, daily_charge, weekday_charge, weekend_charge, holiday_charge)
VALUES ('Ladder', 1.99, 1, 1, 0),
('ChainSaw', 1.49, 1, 0, 1),
('JackHammer', 2.99, 1, 0, 0);
-- Populate the days of the week a holiday can fall on
INSERT INTO Holiday_Days_Of_Week (day_of_the_week)
VALUES ('Monday');
-- Populate the frequencies for holidays allowed
INSERT INTO Holiday_Frequency (frequency)
VALUES ('First');
-- Populate the holidays table
INSERT INTO Holidays (name, holiday_month, holiday_day, day_of_the_week, frequency)
VALUES ('Independence Day', 7, 4, null, null),
('Labor Day', null, null, 'Monday', 'First');
-- Populate the valid statuses for rentals
INSERT INTO Tool_Status (status)
VALUES ('ACTIVE'),
('CLOSED');
Here is the error:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TYPE" not found; SQL statement:
Then the trace has:
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #14 of file [/Users/paqpaq/Dropbox/WORK/CF/pa1024/build/resources/main/data.sql]: INSERT INTO Tools (code, type, brand) VALUES ('CHNS', 'ChainSaw', 'Stihl'), ('LADW', 'Ladder', 'Werner'), ('JAKD', 'JackHammer', 'DeWalt'), ('JAKR', 'JackHammer', 'Ridgid')
I get an error on inserting into Tools or Tools_Charges that is can not find column "type". I have tried changing the name and that does not fix the issue. I have run this through a sql validator and it validates. So I am at a loss at what is wrong.
On the off chance it makes a difference, this is in Intellij setup for an H2 Database. And it was working and then stopped and the only thing I changed was on Tool_Type I added the underscore because it was oddly adding it in the H2 Console.
Thoughts?
You need to sign in to view this answers