More SQL Changes Soon

Going to be implementing a few more changes to the MySQL code and structure within the PHP script. Long term, This will avoid duplicate entries with stations and have a much better structure inside the database. Task will take me around 2+ weeks to implement with how the database is right now and require lots of internal code changes to utilise the new methods.

Database format right now looks something like this:

CREATE TABLE IF NOT EXISTS sc_stations (
    StationIndex INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ShoutCastID INT UNSIGNED,
    GenreID INT UNSIGNED,
    StationName VARCHAR(128),
    StationLogo VARCHAR(128),
    MediaType VARCHAR(32),
    Bitrate INT UNSIGNED,
    StreamURL_1 VARCHAR(128),
    StreamURL_2 VARCHAR(128),
    StreamURL_3 VARCHAR(128),
    StreamURL_4 VARCHAR(128),
    StreamURL_5 VARCHAR(128),
    CurrentTrack VARCHAR (256),
    ListenersCount INT UNSIGNED,
    MaxListeners INT UNSIGNED,
    LastTrackUpdateTime BIGINT,
    LastModifiedTime BIGINT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

Using this structure I was storing the same ShoutCastID multiple times because the GenreID was different (This was a bad method and my first attempt at SQL), the new method will be 100% times better and look something like this:

CREATE TABLE IF NOT EXISTS sc_stations (
    StationIndex INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ShoutCastID INT UNSIGNED,
    StationName VARCHAR(128),
    StationLogo VARCHAR(128),
    MediaType VARCHAR(32),
    Bitrate INT UNSIGNED,
    CurrentTrack VARCHAR (256),
    ListenersCount INT UNSIGNED,
    MaxListeners INT UNSIGNED,
    LastTrackUpdateTime BIGINT,
    LastModifiedTime BIGINT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS sc_stationgenres (
    StationGenreIndex INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ShoutCastID INT UNSIGNED,
    GenreID INT UNSIGNED
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS sc_stationstreams (
    StationStreamIndex INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ShoutCastID INT UNSIGNED,
    StreamURL VARCHAR (128),
    GeoBlocked TINYINT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

Utilising this format we can use JOIN and get the GenreIDs for a specific station matching the ShoutCastID, and only have to store the station entry once.

Plan is to get this update done before the updates on Sunday, either this week or next (Hopefully). One last thing, each station that has a potential for being geo-blocked will be prefixed with a (*), which covers mostly Radionomy streams.