So I have a series of SQL scripts that contribute two cds' worth of information to a database called myTubes.db that contains a cds and songs table.
If I were to open the myTunes.db and then open the schema, it would look like:
C:\sqlite>sqlite3 myTunes.db
sqlite> .schema
CREATE TABLE cds(
cd_id text primary key not null,
title text NOT NULL,
artist text NOT NULL,
producer text default NULL,
year integer,
contributer text
);
CREATE TABLE songs(
song_id integer primary key not null,
title text NOT NULL,
composer text NOT NULL,
cd_id text NOT NULL,
track integer NOT NULL,
contributer text
);
Basically I'm trying to write a new script called myTunes.sql that would read all those individual scripts and create one database with all the cds and songs combined. Thus my script will have to read the individual scripts and I'll end up with a single database with all the data.
All of the given scripts look something like this (there are hundreds of songs and cds but I shortened it for this post):
myTunes_Sarah.sql:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cds(
cd_id text primary key not null, --cd unique id
title text NOT NULL, --title of CD
artist text NOT NULL, --artist whose CD it is or "various artists"
producer text default NULL,
year integer,
contributer text --student number who contirbuted the data
);
INSERT INTO cds VALUES('101058116CD1','Soundboy Rock','Groove Armada','Groove Armada',2007,'101058116');
INSERT INTO cds VALUES('101058116CD2','When It Falls','Zero 7','Zero 7',2004,'101058116');
INSERT INTO cds VALUES('101030700CD1','F8','Five Finger Death Punch','Kevin Churko',2020,'101030700');
INSERT INTO cds VALUES('101030700CD2','Evolution','Disturbed','Kevin Churko',2018,'101030700');
INSERT INTO cds VALUES('101116702CD1','Thriller','Michael Jackson','Quincy Jones',1982,'101116702');
CREATE TABLE songs(
song_id integer primary key not null, --auto incrementing key
title text NOT NULL, --title of song
composer text NOT NULL, --person or persons who wrote the song
cd_id text NOT NULL, --cd this song appears on
track integer NOT NULL, --track number of the song
contributer text --student number who contirbuted the data
);
INSERT INTO songs VALUES(1,'Over My Dead Body','Anthony Palman, Chantal Kreviazuk, 40, Drake','101113450CD1',1,'101113450');
INSERT INTO songs VALUES(2,'Shot for Me','40, Drake, The Weeknd, Rainer Millar, Blanchaer','101113450CD1',2,'101113450');
INSERT INTO songs VALUES(3,'Headlines','40, Boi-1da, Drake','101113450CD1',3,'101113450');
INSERT INTO songs VALUES(4,'Crew Love(ft. The Weeknd)','Illangelo, The Weeknd, 40, Drake, Anthony Palman','101113450CD1',4,'101113450');
INSERT INTO songs VALUES(5,'Take Care(ft. Rihanna)','Jamie xx, Drake, 40, Anthony Palman','101113450CD1',5,'101113450');
COMMIT;
myTunes_Bobby.sql:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cds(
cd_id text primary key not null, --cd unique id
title text NOT NULL, --title of CD
artist text NOT NULL, --artist whose CD it is or "various artists"
producer text default NULL,
year integer,
contributer text --student number who contirbuted the data
);
INSERT INTO cds VALUES('101041125_AR_Album','Abbey Road','Michael Jackson','Quincy Jones, Michael Jackson',1982,'101041125');
INSERT INTO cds VALUES('101041125_TMIL_Album','The Man I Love','Frank Sinatra','Milt Gabler',1957,'101041125');
INSERT INTO cds VALUES('101168617CD1','Rare(Target Exclusive)','Selena Gomez','Albin Nedler',2020,'101168617');
INSERT INTO cds VALUES('101168617CD2','Hybrid Theory','Linkin Park','Don Gilmore',2000,'101168617');
CREATE TABLE songs(
song_id integer primary key not null, --auto incrementing key
title text NOT NULL, --title of song
composer text NOT NULL, --person or persons who wrote the song
cd_id text NOT NULL, --cd this song appears on
track integer NOT NULL, --track number of the song
contributer text --student number who contirbuted the data
);
INSERT INTO songs VALUES(1,'Still Rolling Stones','Lauren Daigle','101104857A1',1,'101104857');
INSERT INTO songs VALUES(2,'Rescue','Lauren Daigle','101104857A1',2,'101104857');
INSERT INTO songs VALUES(3,'This Girl','Lauren Daigle','101104857A1',3,'101104857');
INSERT INTO songs VALUES(4,'Your Wings','Lauren Daigle','101104857A1',4,'101104857');
COMMIT;
I'm confused as to how I would achieve this without cutting and pasting the information from these files and then ignoring the files in my script. The myTunes.sql script should work even if new content is added to the other given sql scripts.
When my script has run, the database should consist of ONLY a cds table and a songs table. There is not allowed to be any old or temporary tables that result from my script running. Basically, my script can create other tables but must drop them before it completes, and my script would also have multiple .read commands to read the given scripts.
I tried searching similar problems online and I believe the main strategy for solving these problems would involve creating and altering and dropping tables using SQL commands like this:
insert into table1 (col1, col2) select (colA,colB) from table 2;
I attempted do something like:
myTunes.sql:
.read myTunes_Sarah.sql
.read myTunes_Bobby.sql
CREATE TABLE if not exists mycds(
cd_id text primary key not null, --cd unique id
title text NOT NULL, --title of CD
artist text NOT NULL, --artist whose CD it is or "various artists"
producer text default NULL,
year integer,
contributer text --student number who contirbuted the data
);
insert into cds (cd_id, title, artist, producer, year, contributer) select (cd_id, title, artist, producer, year, contributer) from mycds;
CREATE TABLE if not exists mysongs(
song_id integer primary key not null, --auto incrementing key
title text NOT NULL, --title of song
composer text NOT NULL, --person or persons who wrote the song
cd_id text NOT NULL, --cd this song appears on
track integer NOT NULL, --track number of the song
contributer text --student number who contirbuted the data
);
insert into songs (song_id, title, composer, cd_id, track, contributer) select (song_id, title, composer, cd_id, track, contributer) from mysongs;
DROP TABLE mycds;
DROP TABLE mysongs;
but I keep getting numerous errors like the following below (with the UNIQUE constraint error for songs.song_id repeating hundreds of times):
Error: near line 3: table cds already exists
Error: near line 56: table songs already exists
Error: near line 3: table cds already exists
Error: near line 65: table songs already exists
Error: near line 73: UNIQUE constraint failed: songs.song_id
Error: near line 15: row value misused
Error: near line 26: row value misused
Any help or resources or a push in the right direction would be appreciated. I was told this problem is quite simple but I'm having trouble figuring out how to achieve this. I feel like I'm also overthinking things.