Lunetikk's IT Wiki

User Tools

Site Tools

You are here: start » linux » ubuntu » spotifymusictodatabase
Trace:
linux:ubuntu:spotifymusictodatabase

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
linux:ubuntu:spotifymusictodatabase [2021/12/29 00:48] – lunetikklinux:ubuntu:spotifymusictodatabase [2023/01/18 15:17] – lunetikk
Line 4: Line 4:
 ===== Database ===== ===== Database =====
  
-{{:linux:ubuntu:pasted:20211229-004224.png?500}}+{{:linux:ubuntu:pasted:20230118-151359.png?800}}
  
 <code mysql spotifymusic.sql> <code mysql spotifymusic.sql>
 +
 CREATE TABLE `album` ( CREATE TABLE `album` (
   `artist_id` smallint(5) NOT NULL DEFAULT '0',   `artist_id` smallint(5) NOT NULL DEFAULT '0',
-  `album_id` smallint(5) NOT NULL AUTO_INCREMENT,+  `album_id` smallint(5) NOT NULL,
   `album_name` char(128) DEFAULT NULL,   `album_name` char(128) DEFAULT NULL,
   `album_cover` varchar(1000) DEFAULT NULL,   `album_cover` varchar(1000) DEFAULT NULL,
Line 15: Line 16:
   `album_releasedate` date DEFAULT NULL,   `album_releasedate` date DEFAULT NULL,
   `album_tracknumber` smallint(5) DEFAULT NULL,   `album_tracknumber` smallint(5) DEFAULT NULL,
-  `album_spotifyurl` varchar(128) DEFAULT NULL, +  `album_spotifyurl` varchar(128) DEFAULT NULL
-  PRIMARY KEY (album_id)+
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
 CREATE TABLE `artist` ( CREATE TABLE `artist` (
-  `artist_id` smallint(5) NOT NULL AUTO_INCREMENT,+  `artist_id` smallint(5) NOT NULL,
   `artist_name` char(128) DEFAULT NULL,   `artist_name` char(128) DEFAULT NULL,
-  `artist_spotifyurl` varchar(128) DEFAULT NULL, +  `artist_spotifyurl` varchar(128) DEFAULT NULL 
-  PRIMARY KEY (artist_id)+) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 + 
 +CREATE TABLE `connectalbumtrack` ( 
 +  `album_id` smallint(10) NOT NULL, 
 +  `track_id` smallint(10) NOT NULL 
 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 + 
 +CREATE TABLE `connectartisttrack` ( 
 +  `artist_id` smallint(10) NOT NULL, 
 +  `track_id` smallint(10) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
Line 34: Line 43:
  
 CREATE TABLE `track` ( CREATE TABLE `track` (
-  `track_id` smallint(5) NOT NULL AUTO_INCREMENT, 
   `artist_id` smallint(5) NOT NULL DEFAULT '0',   `artist_id` smallint(5) NOT NULL DEFAULT '0',
   `album_id` smallint(5) NOT NULL DEFAULT '0',   `album_id` smallint(5) NOT NULL DEFAULT '0',
 +  `track_id` smallint(5) NOT NULL,
   `track_name` char(255) DEFAULT NULL,   `track_name` char(255) DEFAULT NULL,
   `track_popularity` int(20) DEFAULT NULL,   `track_popularity` int(20) DEFAULT NULL,
   `track_tracknumber` smallint(5) DEFAULT NULL,   `track_tracknumber` smallint(5) DEFAULT NULL,
   `track_previewurl` varchar(128) DEFAULT NULL,   `track_previewurl` varchar(128) DEFAULT NULL,
-  `track_spotifyurl` varchar(128) DEFAULT NULL, +  `track_spotifyurl` varchar(128) DEFAULT NULL
-  PRIMARY KEY (track_id)+
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-<code>+ 
 +ALTER TABLE `album` 
 +  ADD PRIMARY KEY (`album_id`), 
 +  ADD KEY `album_id` (`album_id`), 
 +  ADD KEY `artist_id` (`artist_id`); 
 + 
 +ALTER TABLE `artist` 
 +  ADD PRIMARY KEY (`artist_id`), 
 +  ADD UNIQUE KEY `artist_id_2` (`artist_id`), 
 +  ADD KEY `artist_id` (`artist_id`), 
 +  ADD KEY `artist_id_3` (`artist_id`); 
 + 
 +ALTER TABLE `connectalbumtrack` 
 +  ADD KEY `album_id` (`album_id`), 
 +  ADD KEY `track_id` (`track_id`); 
 + 
 +ALTER TABLE `connectartisttrack` 
 +  ADD KEY `artist_id` (`artist_id`), 
 +  ADD KEY `track_id` (`track_id`); 
 + 
 +ALTER TABLE `played` 
 +  ADD KEY `artist_id` (`artist_id`), 
 +  ADD KEY `album_id` (`album_id`), 
 +  ADD KEY `track_id` (`track_id`); 
 + 
 +ALTER TABLE `track` 
 +  ADD PRIMARY KEY (`track_id`), 
 +  ADD KEY `track_id` (`track_id`), 
 +  ADD KEY `artist_id` (`artist_id`), 
 +  ADD KEY `album_id` (`album_id`); 
 + 
 +ALTER TABLE `album` 
 +  MODIFY `album_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55; 
 + 
 +ALTER TABLE `artist` 
 +  MODIFY `artist_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=71; 
 + 
 +ALTER TABLE `track` 
 +  MODIFY `track_id` smallint(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=69; 
 + 
 +ALTER TABLE `album` 
 +  ADD CONSTRAINT `album_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`); 
 + 
 +ALTER TABLE `connectalbumtrack` 
 +  ADD CONSTRAINT `connectalbumtrack_ibfk_1` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`), 
 +  ADD CONSTRAINT `connectalbumtrack_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`); 
 + 
 +ALTER TABLE `connectartisttrack` 
 +  ADD CONSTRAINT `connectartisttrack_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`), 
 +  ADD CONSTRAINT `connectartisttrack_ibfk_2` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`); 
 + 
 +ALTER TABLE `played` 
 +  ADD CONSTRAINT `played_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`), 
 +  ADD CONSTRAINT `played_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`), 
 +  ADD CONSTRAINT `played_ibfk_3` FOREIGN KEY (`track_id`) REFERENCES `track` (`track_id`); 
 + 
 +ALTER TABLE `track` 
 +  ADD CONSTRAINT `track_ibfk_1` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`), 
 +  ADD CONSTRAINT `track_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `album` (`album_id`); 
 + 
 +</code>
linux/ubuntu/spotifymusictodatabase.txt · Last modified: 2023/01/18 16:01 by lunetikk

Page Tools

Impressum Datenschutz
Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki