sql_queries.py

# DROP TABLES

songplay_table_drop = "DROP TABLE IF EXISTS songplays"
user_table_drop = "DROP TABLE IF EXISTS users"
song_table_drop = "DROP TABLE IF EXISTS songs"
artist_table_drop = "DROP TABLE IF EXISTS artists"
time_table_drop = "DROP TABLE IF EXISTS time"

# CREATE TABLES

user_table_create = ("""
                        CREATE TABLE IF NOT EXISTS users (
                            userId VARCHAR PRIMARY KEY, firstName VARCHAR,
                            lastName VARCHAR, gender VARCHAR, level VARCHAR
                        );
""")

song_table_create = ("""
                        CREATE TABLE IF NOT EXISTS songs (
                            song_id VARCHAR PRIMARY KEY, title VARCHAR NOT NULL,
                            artist_id VARCHAR NOT NULL, year INT, duration float
                        );
""")

artist_table_create = ("""
                            CREATE TABLE IF NOT EXISTS artists (
                                artist_id VARCHAR PRIMARY KEY, name VARCHAR,
                                location VARCHAR, latitude VARCHAR,
                                longitude VARCHAR
                            );
""")

time_table_create = ("""
                        CREATE TABLE IF NOT EXISTS time (
                            start_time BIGINT PRIMARY KEY, hour INT, day INT,
                            weekofyear INT, month INT, year INT, weekday INT
                        );
""")

# TODO: add this: songplays_id SERIAL,
songplay_table_create = ("""
                            CREATE TABLE IF NOT EXISTS songplays (
                                start_time BIGINT, userId VARCHAR,
                                level VARCHAR, song_id VARCHAR,
                                artist_id VARCHAR, session_id INT NOT NULL,
                                location VARCHAR, user_agent VARCHAR,
                                PRIMARY KEY (start_time, userId)
                            );
""")

# INSERT RECORDS

songplay_table_insert = ("""
                            INSERT INTO songplays (
                                start_time, userId, level, song_id, artist_id,
                                session_id, location, user_agent
                            ) VALUES (
                                %s, %s, %s, %s, %s, %s, %s, %s
                            ) ON CONFLICT (start_time, userId)
                            DO NOTHING;
""")

user_table_insert = ("""
                        INSERT INTO users (
                            userId, firstName, lastName, gender, level
                        ) VALUES (
                            %s, %s, %s, %s, %s
                        ) ON CONFLICT (userId)
                        DO UPDATE SET level=EXCLUDED.level;
""")

song_table_insert = ("""
                        INSERT INTO songs (
                            song_id, title, artist_id, year, duration
                        ) VALUES (
                            %s, %s, %s, %s, %s
                        ) ON CONFLICT (song_id)
                        DO UPDATE SET
                            year=EXCLUDED.year,
                            duration=EXCLUDED.duration;
""")

artist_table_insert = ("""
                            INSERT INTO artists (
                                artist_id, name, location, latitude, longitude
                            ) VALUES (
                                %s, %s, %s, %s, %s
                            ) ON CONFLICT (artist_id)
                            DO UPDATE SET
                                name=EXCLUDED.name,
                                location=EXCLUDED.location,
                                latitude=EXCLUDED.latitude,
                                longitude=EXCLUDED.longitude;
""")

time_table_insert = ("""
                        INSERT INTO time (
                            start_time, hour, day, weekofyear,
                            month, year, weekday
                        ) VALUES (
                            %s, %s, %s, %s, %s, %s, %s
                        ) ON CONFLICT (start_time)
                        DO UPDATE SET
                            start_time=EXCLUDED.start_time,
                            hour=EXCLUDED.hour,
                            day=EXCLUDED.day,
                            weekofyear=EXCLUDED.weekofyear,
                            month=EXCLUDED.month,
                            year=EXCLUDED.year,
                            weekday=EXCLUDED.weekday;
""")

# FIND SONGS

song_select = ("""SELECT songs.song_id, artists.artist_id FROM songs, artists
WHERE songs.title = %s
AND artists.name = %s
AND songs.duration= %s;""")

sample_query = ("""
SELECT * FROM songplays
WHERE song_id IS NOT NULL
AND artist_id IS NOT NULL;""")

# QUERY LISTS

create_table_queries = [songplay_table_create, user_table_create,
                        song_table_create, artist_table_create,
                        time_table_create]

drop_table_queries = [songplay_table_drop, user_table_drop, song_table_drop,
                      artist_table_drop, time_table_drop]