Database metadata: add support for primary and foreign keys

[Wenox]

Database metadata: add support for primary and foreign keys

[Wenox]

init_audiodb_psql.txt:

CREATE TABLE artists (                                                     
    id serial primary key,
    name varchar(40) NOT NULL,
	age integer
);

CREATE TABLE albums (
    name text NOT NULL,
    artist_id int NOT NULL,
    PRIMARY KEY(name),
    FOREIGN KEY(artist_id) REFERENCES artists(id)
);


CREATE SEQUENCE songs_sequence
  start 101
  increment 2;
  
CREATE TABLE songs (
    id integer DEFAULT nextval('songs_sequence'),
    song_name text NOT NULL,
    album_name text NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(album_name) REFERENCES albums(name)
);


INSERT INTO artists (name, age) VALUES ('Kanye West', 44);
INSERT INTO artists (name, age) VALUES ('Michael Jackson', NULL);
INSERT INTO artists (name, age) VALUES ('Otis Redding', 26);


INSERT INTO albums (name, artist_id) VALUES ('My Beautiful Dark Twisted Fantasy', 1);
INSERT INTO albums (name, artist_id) VALUES ('Donda', 1);
INSERT INTO albums (name, artist_id) VALUES ('Thriller', 2);
INSERT INTO albums (name, artist_id) VALUES ('Bad', 2);
INSERT INTO albums (name, artist_id) VALUES ('Pain in My Heart', 3);


INSERT INTO songs (song_name, album_name) VALUES ('Devil in a New Dress', 'My Beautiful Dark Twisted Fantasy');
INSERT INTO songs (song_name, album_name) VALUES ('Moon', 'Donda');
INSERT INTO songs (song_name, album_name) VALUES ('Come to Life', 'Donda');
INSERT INTO songs (song_name, album_name) VALUES ('Heaven and Hell', 'Donda');
INSERT INTO songs (song_name, album_name) VALUES ('Beat It', 'Thriller');
INSERT INTO songs (song_name, album_name) VALUES ('Billie Jean', 'Thriller');
INSERT INTO songs (song_name, album_name) VALUES ('Louie Louie', 'Pain in My Heart');

[Wenox]

Creating audiodb.dump from the above init_audiodb_psql.txt:

dropdb -U postgres audiodb
createdb -U postgres -T template0 audiodb
psql -U postgres -d audiodb -a -f "C:\Users\wenox\Desktop\init_audiodb_psql.txt"
pg_dump -U postgres -v -Fc audiodb > audiodb.dump

Restoring dump:

createdb -U postgres -T template0 audiodb2
pg_restore -U postgres -d audiodb2 -v audiodb.dump