Ejemplo n.º 1
0
	def _handle_event(self, event):
		try:
			if hasattr(event, "src_path") and event.src_path and check_file_is_in_directory(event.src_path, self.root_directory):
				if _is_bad_extension(event.src_path):
					pass
				elif not os.path.isdir(event.src_path):
					log.debug("scan_event", "%s src_path for file %s" % (event.event_type, event.src_path))
					if _is_image(event.src_path) and (event.event_type == 'deleted' or event.event_type == 'moved'):
						pass
					else:
						self._handle_file(event.src_path)
				else:
					log.debug("scan_event", "%s src_path for dir %s" % (event.event_type, event.src_path))
					self._handle_directory(event.src_path)

			if hasattr(event, "dest_path") and event.dest_path and check_file_is_in_directory(event.dest_path, self.root_directory):
				if _is_bad_extension(event.dest_path):
					pass
				elif not os.path.isdir(event.dest_path):
					log.debug("scan_event", "%s dest_path for file %s" % (event.event_type, event.dest_path))
					if _is_image(event.dest_path) and (event.event_type == 'deleted'):
						pass
					else:
						self._handle_file(event.dest_path)
				else:
					log.debug("scan_event", "%s dest_path for dir %s" % (event.event_type, event.dest_path))
					self._handle_directory(event.dest_path)
		except Exception as xception:
			_add_scan_error(self.root_directory, xception)
			log.critical("scan_event", "Exception occurred - reconnecting to the database just in case.")
			db.close()
			db.connect()
Ejemplo n.º 2
0
def _on_zmq(messages):
    global votes_by
    global last_vote_by

    for message in messages:
        try:
            message = json.loads(message)
        except Exception as e:
            log.exception("zeromq", "Error decoding ZeroMQ message.", e)
            return

        if not "action" in message or not message["action"]:
            log.critical("zeromq", "No action received from ZeroMQ.")

        try:
            if message["action"] == "result_sync":
                sessions[message["sid"]].send_to_user(
                    message["user_id"], message["uuid_exclusion"], message["data"]
                )
            elif message["action"] == "live_voting":
                sessions[message["sid"]].send_to_all(
                    message["uuid_exclusion"], message["data"]
                )
                delay_live_vote_removal(message["sid"])
            elif message["action"] == "delayed_live_voting":
                if not delayed_live_vote_timers[message["sid"]]:
                    delay_live_vote(message)
                delayed_live_vote[message["sid"]] = message
            elif message["action"] == "update_all":
                delay_live_vote_removal(message["sid"])
                rainwave.playlist.update_num_songs()
                rainwave.playlist.prepare_cooldown_algorithm(message["sid"])
                cache.update_local_cache_for_sid(message["sid"])
                sessions[message["sid"]].update_all(message["sid"])
                votes_by = {}
                last_vote_by = {}
            elif message["action"] == "update_ip":
                for sid in sessions:
                    sessions[sid].update_ip_address(message["ip"])
            elif message["action"] == "update_listen_key":
                for sid in sessions:
                    sessions[sid].update_listen_key(message["listen_key"])
            elif message["action"] == "update_user":
                for sid in sessions:
                    sessions[sid].update_user(message["user_id"])
            elif message["action"] == "update_dj":
                sessions[message["sid"]].update_dj()
            elif message["action"] == "ping":
                log.debug("zeromq", "Pong")
            elif message["action"] == "vote_by":
                votes_by[message["by"]] = (
                    votes_by[message["by"]] + 1 if message["by"] in votes_by else 1
                )
                last_vote_by[message["by"]] = timestamp()
        except Exception as e:
            log.exception(
                "zeromq", "Error handling Zero MQ action '%s'" % message["action"], e
            )
            return
Ejemplo n.º 3
0
	def disable(self):
		if not self.id:
			log.critical("song_disable", "Tried to disable a song without a song ID.")
			return
		log.info("song_disable", "Disabling ID %s / file %s" % (self.id, self.filename))
		db.c.update("UPDATE r4_songs SET song_verified = FALSE WHERE song_id = %s", (self.id,))
		db.c.update("UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s", (self.id,))
		if self.albums:
			for metadata in self.albums:
				metadata.reconcile_sids()
Ejemplo n.º 4
0
def _on_zmq(messages):
	global votes_by
	global last_vote_by

	for message in messages:
		try:
			message = json.loads(message)
		except Exception as e:
			log.exception("zeromq", "Error decoding ZeroMQ message.", e)
			return

		if not 'action' in message or not message['action']:
			log.critical("zeromq", "No action received from ZeroMQ.")

		try:
			if message['action'] == "result_sync":
				sessions[message['sid']].send_to_user(message['user_id'], message['uuid_exclusion'], message['data'])
			elif message['action'] == "live_voting":
				sessions[message['sid']].send_to_all(message['uuid_exclusion'], message['data'])
				delay_live_vote_removal(message['sid'])
			elif message['action'] == "delayed_live_voting":
				if not delayed_live_vote_timers[message['sid']]:
					delay_live_vote(message)
				delayed_live_vote[message['sid']] = message
			elif message['action'] == "update_all":
				delay_live_vote_removal(message['sid'])
				rainwave.playlist.update_num_songs()
				rainwave.playlist.prepare_cooldown_algorithm(message['sid'])
				cache.update_local_cache_for_sid(message['sid'])
				sessions[message['sid']].update_all(message['sid'])
				votes_by = {}
				last_vote_by = {}
			elif message['action'] == "update_ip":
				for sid in sessions:
					sessions[sid].update_ip_address(message['ip'])
			elif message['action'] == "update_listen_key":
				for sid in sessions:
					sessions[sid].update_listen_key(message['listen_key'])
			elif message['action'] == "update_user":
				for sid in sessions:
					sessions[sid].update_user(message['user_id'])
			elif message['action'] == "update_dj":
				sessions[message['sid']].update_dj()
			elif message['action'] == "ping":
				log.debug("zeromq", "Pong")
			elif message['action'] == "vote_by":
				votes_by[message['by']] = votes_by[message['by']] + 1 if message['by'] in votes_by else 1
				last_vote_by[message['by']] = timestamp()
		except Exception as e:
			log.exception("zeromq", "Error handling Zero MQ action '%s'" % message['action'], e)
			return
Ejemplo n.º 5
0
    def _handle_event(self, event):
        try:
            if hasattr(event, "src_path"
                       ) and event.src_path and check_file_is_in_directory(
                           event.src_path, self.root_directory):
                if _is_bad_extension(event.src_path):
                    pass
                elif not os.path.isdir(event.src_path):
                    log.debug(
                        "scan_event", "%s src_path for file %s" %
                        (event.event_type, event.src_path))
                    if _is_image(event.src_path) and (
                            event.event_type == 'deleted'
                            or event.event_type == 'moved'):
                        pass
                    else:
                        self._handle_file(event.src_path)
                else:
                    log.debug(
                        "scan_event", "%s src_path for dir %s" %
                        (event.event_type, event.src_path))
                    self._handle_directory(event.src_path)

            if hasattr(event, "dest_path"
                       ) and event.dest_path and check_file_is_in_directory(
                           event.dest_path, self.root_directory):
                if _is_bad_extension(event.dest_path):
                    pass
                elif not os.path.isdir(event.dest_path):
                    log.debug(
                        "scan_event", "%s dest_path for file %s" %
                        (event.event_type, event.dest_path))
                    if _is_image(event.dest_path) and (event.event_type
                                                       == 'deleted'):
                        pass
                    else:
                        self._handle_file(event.dest_path)
                else:
                    log.debug(
                        "scan_event", "%s dest_path for dir %s" %
                        (event.event_type, event.dest_path))
                    self._handle_directory(event.dest_path)
        except Exception as xception:
            _add_scan_error(self.root_directory, xception)
            log.critical(
                "scan_event",
                "Exception occurred - reconnecting to the database just in case."
            )
            db.close()
            db.connect()
Ejemplo n.º 6
0
	def disable(self):
		if not self.id:
			log.critical("song_disable", "Tried to disable a song without a song ID.")
			return
		log.info("song_disable", "Disabling ID %s / file %s" % (self.id, self.filename))
		db.c.update("UPDATE r4_songs SET song_verified = FALSE WHERE song_id = %s", (self.id,))
		db.c.update("UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s", (self.id,))
		db.c.update("DELETE FROM r4_request_store WHERE song_id = %s", (self.id,))
		if self.albums:
			for metadata in self.albums:
				metadata.reconcile_sids()
		if self.groups:
			for metadata in self.groups:
				metadata.reconcile_sids()
Ejemplo n.º 7
0
Archivo: db.py Proyecto: Reani/rainwave
def open():
	global connection
	global c
	global c_old

	if c or c_old:
		close()

	type = config.get("db_type")
	name = config.get("db_name")
	host = config.get("db_host")
	port = config.get("db_port")
	user = config.get("db_user")
	password = config.get("db_password")

	if type == "postgres":
		psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
		psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
		base_connstr = "sslmode=disable "
		if host:
			base_connstr += "host=%s " % host
		if port:
			base_connstr += "port=%s " % port
		if user:
			base_connstr += "user=%s " % user
		if password:
			base_connstr += "password=%s " % password
		connection = psycopg2.connect(base_connstr + ("dbname=%s" % name))
		connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
		connection.autocommit = True
		c = connection.cursor(cursor_factory=PostgresCursor)

		if config.has("db_USE_LIVE_R3") and config.get("db_USE_LIVE_R3"):
			c_old = None
			connection = psycopg2.connect(base_connstr + "dbname=rainwave")
			connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
			connection.autocommit = True
			c_old = connection.cursor(cursor_factory=PostgresCursor)
		else:
			c_old = c
	elif type == "sqlite":
		log.debug("dbopen", "Opening SQLite DB %s" % name)
		c = SQLiteCursor(name)
		c_old = c
	else:
		log.critical("dbopen", "Invalid DB type %s!" % type)
		return False

	return True
Ejemplo n.º 8
0
def get_random_song_ignore_all(sid):
	"""
	Fetches the most stale song (longest time since it's been played) in the db,
	ignoring all availability and election block rules.
	"""
	sql_query = "FROM r4_song_sid WHERE r4_song_sid.sid = %s AND song_exists = TRUE "
	num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
	offset = 0
	if num_available == 0:
		log.critical("song_select", "No songs exist.")
		log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query %  (sid,)))
		raise Exception("Could not find any songs to play.")
	else:
		offset = random.randint(1, num_available) - 1
		song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
		return Song.load_from_id(song_id, sid)
Ejemplo n.º 9
0
def get_random_song_ignore_all(sid):
	"""
	Fetches the most stale song (longest time since it's been played) in the db,
	ignoring all availability and election block rules.
	"""
	sql_query = "FROM r4_song_sid WHERE r4_song_sid.sid = %s AND song_exists = TRUE "
	num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
	offset = 0
	if num_available == 0:
		log.critical("song_select", "No songs exist.")
		log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query %  (sid,)))
		raise Exception("Could not find any songs to play.")
	else:
		offset = random.randint(1, num_available) - 1
		song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
		return Song.load_from_id(song_id, sid)
Ejemplo n.º 10
0
def close():
	global connection
	global c

	if c:
		# forgot to commit?  too bad.
		if c.in_tx:
			log.critical("txopen", "Forgot to close a transaction!  Rolling back!")
			c.rollback()
		c.close()
	if connection:
		connection.close()
	c = None
	connection = None

	return True
Ejemplo n.º 11
0
	def start(self):
		stations = list(config.station_ids)
		if not hasattr(os, "fork"):
			if len(stations) > 1:
				log.critical("server", "*** WARNING: CANNOT RUN BACKEND FOR MORE THAN 1 STATION ON WINDOWS ***")
			zeromq.init_proxy()
			self._import_cron_modules()
			self._listen(stations[0])
		else:
			tornado.process.fork_processes(len(stations))

			task_id = tornado.process.task_id()
			if task_id == 0:
				zeromq.init_proxy()
				self._import_cron_modules()
			if task_id != None:
				self._listen(stations[task_id])
Ejemplo n.º 12
0
Archivo: db.py Proyecto: nv043/rainwave
def close():
    global connection
    global c

    if c:
        # forgot to commit?  too bad.
        if c.in_tx:
            log.critical("txopen",
                         "Forgot to close a transaction!  Rolling back!")
            c.rollback()
        c.close()
    if connection:
        connection.close()
    c = None
    connection = None

    return True
Ejemplo n.º 13
0
    def start(self):
        stations = list(config.station_ids)
        if not hasattr(os, "fork"):
            if len(stations) > 1:
                log.critical(
                    "server",
                    "*** WARNING: CANNOT RUN BACKEND FOR MORE THAN 1 STATION ON WINDOWS ***"
                )
            zeromq.init_proxy()
            self._import_cron_modules()
            self._listen(stations[0])
        else:
            tornado.process.fork_processes(len(stations))

            task_id = tornado.process.task_id()
            if task_id == 0:
                zeromq.init_proxy()
                self._import_cron_modules()
            if task_id != None:
                self._listen(stations[task_id])
Ejemplo n.º 14
0
Archivo: db.py Proyecto: nv043/rainwave
 def execute(self, query, params=None):
     if self.print_next:
         self.print_next = False
         if params:
             print self._convert_pg_query(query, True) % params
         else:
             print self._convert_pg_query(query, True)
     query = self._convert_pg_query(query)
     # If the query can't be done or properly to SQLite,
     # silently drop it.  This is mostly for table creation, things like foreign keys.
     if not query:
         log.critical("sqlite",
                      "Query has not been made SQLite friendly: %s" % query)
         raise Exception("Query has not been made SQLite friendly: %s" %
                         query)
     try:
         if params:
             self.cur.execute(query, params)
         else:
             self.cur.execute(query)
     except Exception as e:
         log.critical("sqlite", query)
         log.critical("sqlite", repr(params))
         log.exception("sqlite", "Failed query.", e)
         raise
     self.rowcount = self.cur.rowcount
     self.con.commit()
Ejemplo n.º 15
0
	def execute(self, query, params = None):
		if self.print_next:
			self.print_next = False
			if params:
				print self._convert_pg_query(query, True) % params
			else:
				print self._convert_pg_query(query, True)
		query = self._convert_pg_query(query)
		# If the query can't be done or properly to SQLite,
		# silently drop it.  This is mostly for table creation, things like foreign keys.
		if not query:
			log.critical("sqlite", "Query has not been made SQLite friendly: %s" % query)
			raise Exception("Query has not been made SQLite friendly: %s" % query)
		try:
			if params:
				self.cur.execute(query, params)
			else:
				self.cur.execute(query)
		except Exception as e:
			log.critical("sqlite", query)
			log.critical("sqlite", repr(params))
			log.exception("sqlite", "Failed query.", e)
			raise
		self.rowcount = self.cur.rowcount
		self.con.commit()
Ejemplo n.º 16
0
Archivo: db.py Proyecto: nv043/rainwave
def connect():
    global connection
    global c

    if c:
        return True

    dbtype = config.get("db_type")
    name = config.get("db_name")
    host = config.get("db_host")
    port = config.get("db_port")
    user = config.get("db_user")
    password = config.get("db_password")

    if dbtype == "postgres":
        psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
        psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
        base_connstr = "sslmode=disable "
        if host:
            base_connstr += "host=%s " % host
        if port:
            base_connstr += "port=%s " % port
        if user:
            base_connstr += "user=%s " % user
        if password:
            base_connstr += "password=%s " % password
        connection = psycopg2.connect(base_connstr + ("dbname=%s" % name))
        connection.set_isolation_level(
            psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        connection.autocommit = True
        c = connection.cursor(cursor_factory=PostgresCursor)
    elif dbtype == "sqlite":
        log.debug("dbopen", "Opening SQLite DB %s" % name)
        c = SQLiteCursor(name)
    else:
        log.critical("dbopen", "Invalid DB type %s!" % dbtype)
        return False

    return True
Ejemplo n.º 17
0
def open():
	global connection
	global c
	
	if c:
		close()
	
	type = config.get("db_type")
	name = config.get("db_name")
	host = config.get("db_host")
	port = config.get("db_port")
	user = config.get("db_user")
	password = config.get("db_password")
	
	if type == "postgres":
		psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
		psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
		connstr = "sslmode=disable dbname=%s" % name
		if host:
			connstr += "host=%s " % host
		if port:
			connstr += "port=%s " % port
		if user:
			connstr += "user=%s " % user
		if password:
			connstr += "password=%s " % password
		connection = psycopg2.connect(connstr)
		connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
		connection.autocommit = True
		c = self.con.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
	elif type == "sqlite":
		log.debug("dbopen", "Opening SQLite DB %s" % name)
		c = SQLiteCursor(name)
	else:
		log.critical("dbopen", "Invalid DB type %s!" % type)
		return False

	return True
Ejemplo n.º 18
0
def create_tables():
	if c.is_postgres:
		c.start_transaction()

	if config.get("standalone_mode"):
		_create_test_tables()

	c.update(" \
		CREATE TABLE r4_albums ( \
			album_id				SERIAL		PRIMARY KEY, \
			album_name				TEXT		, \
			album_name_searchable	TEXT 		NOT NULL, \
			album_year				SMALLINT, \
			album_added_on				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) \
		)")

	c.update(" \
		CREATE TABLE r4_songs ( \
			song_id						SERIAL		PRIMARY KEY, \
			album_id 					INTEGER, \
			song_origin_sid				SMALLINT	NOT NULL, \
			song_verified				BOOLEAN		DEFAULT TRUE, \
			song_scanned				BOOLEAN		DEFAULT TRUE, \
			song_filename				TEXT		, \
			song_title					TEXT		, \
			song_title_searchable		TEXT		NOT NULL, \
			song_artist_tag				TEXT		, \
			song_url					TEXT		, \
			song_link_text				TEXT		, \
			song_length					SMALLINT	, \
			song_track_number			SMALLINT	, \
			song_disc_number			SMALLINT	, \
			song_year				SMALLINT	, \
			song_added_on				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			song_rating					REAL		DEFAULT 0, \
			song_rating_count			INTEGER		DEFAULT 0, \
			song_fave_count				INTEGER		DEFAULT 0, \
			song_request_count			INT			DEFAULT 0, \
			song_cool_multiply			REAL		DEFAULT 1, \
			song_cool_override			INTEGER		, \
			song_file_mtime				INTEGER		, \
			song_replay_gain			TEXT 		, \
			song_vote_count				INTEGER		DEFAULT 0, \
			song_votes_seen				INTEGER		DEFAULT 0, \
			song_vote_share				REAL 		, \
			song_artist_parseable		TEXT \
 		)")
	c.create_idx("r4_songs", "song_verified")
	c.create_idx("r4_songs", "song_rating")
	c.create_idx("r4_songs", "song_request_count")
	c.create_null_fk("r4_songs", "r4_albums", "album_id")

	c.update(" \
		CREATE TABLE r4_song_sid ( \
			song_id						INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			song_cool					BOOLEAN		DEFAULT FALSE, \
			song_cool_end				INTEGER		DEFAULT 0, \
			song_elec_appearances		INTEGER		DEFAULT 0, \
			song_elec_last				INTEGER		DEFAULT 0, \
			song_elec_blocked			BOOLEAN 	DEFAULT FALSE, \
			song_elec_blocked_num		SMALLINT	DEFAULT 0, \
			song_elec_blocked_by		TEXT		, \
			song_played_last			INTEGER		, \
			song_exists					BOOLEAN		DEFAULT TRUE, \
			song_request_only			BOOLEAN		DEFAULT FALSE, \
			song_request_only_end		INTEGER		DEFAULT 0 \
			PRIMARY KEY (song_id, sid) \
		)")
	# c.create_idx("r4_song_sid", "song_id")	# handled by create_delete_fk
	c.create_idx("r4_song_sid", "sid")
	c.create_idx("r4_song_sid", "song_cool")
	c.create_idx("r4_song_sid", "song_elec_blocked")
	c.create_idx("r4_song_sid", "song_exists")
	c.create_idx("r4_song_sid", "song_request_only")
	c.create_delete_fk("r4_song_sid", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_song_ratings ( \
			song_id					INTEGER		NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			song_rating_user			REAL		, \
			song_rated_at				INTEGER		, \
			song_rated_at_rank			INTEGER		, \
			song_rated_at_count			INTEGER		, \
			song_fave				BOOLEAN, \
			PRIMARY KEY (user_id, song_id) \
		)")
	# c.create_idx("r4_song_ratings", "user_id", "song_id") Should be handled by primary key
	c.create_idx("r4_song_ratings", "song_fave")
	c.create_delete_fk("r4_song_ratings", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_ratings", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_album_sid ( \
			album_exists				BOOLEAN		DEFAULT TRUE, \
			album_id					INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			album_song_count			SMALLINT	DEFAULT 0, \
			album_played_last			INTEGER		DEFAULT 0, \
			album_requests_pending		BOOLEAN, \
			album_cool					BOOLEAN		DEFAULT FALSE, \
			album_cool_multiply			REAL		DEFAULT 1, \
			album_cool_override			INTEGER		, \
			album_cool_lowest			INTEGER		DEFAULT 0, \
			album_updated				INTEGER		DEFAULT 0, \
			album_elec_last				INTEGER		DEFAULT 0, \
			album_rating				REAL		NOT NULL DEFAULT 0, \
			album_rating_count			INTEGER		DEFAULT 0, \
			album_request_count			INTEGER		DEFAULT 0, \
			album_fave_count			INTEGER		DEFAULT 0, \
			album_vote_count			INTEGER		DEFAULT 0, \
			album_votes_seen			INTEGER		DEFAULT 0, \
			album_vote_share			REAL 		\
			PRIMARY KEY (album_id, sid) \
		)")
	c.create_idx("r4_album_sid", "album_rating")
	c.create_idx("r4_album_sid", "album_request_count")
	c.create_idx("r4_album_sid", "album_exists")
	c.create_idx("r4_album_sid", "sid")
	c.create_idx("r4_album_sid", "album_requests_pending")
	c.create_delete_fk("r4_album_sid", "r4_albums", "album_id")

	c.update(" \
		CREATE TABLE r4_album_ratings ( \
			album_id				INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			album_rating_user		REAL		, \
			album_fave				BOOLEAN, \
			album_rating_complete	BOOLEAN		DEFAULT FALSE \
		)")
	# 			PRIMARY KEY (user_id, album_id, sid) \
	c.create_idx("r4_album_ratings", "user_id", "album_id", "sid") 	#Should be handled by primary key.
	c.create_idx("r4_album_ratings", "album_id", "sid")
	c.create_idx("r4_album_ratings", "album_fave")
	c.create_idx("r4_album_ratings", "album_fave", "sid")
	c.create_delete_fk("r4_album_ratings", "r4_albums", "album_id", create_idx=False)
	c.create_delete_fk("r4_album_ratings", "phpbb_users", "user_id", create_idx=False)

	c.update(" \
		CREATE TABLE r4_artists		( \
			artist_id				SERIAL		PRIMARY KEY, \
			artist_name				TEXT		, \
			artist_name_searchable	TEXT 		NOT NULL \
		)")

	c.update(" \
		CREATE TABLE r4_song_artist	( \
			song_id					INTEGER		NOT NULL, \
			artist_id				INTEGER		NOT NULL, \
			artist_order			SMALLINT    DEFAULT 0, \
			artist_is_tag			BOOLEAN		DEFAULT TRUE \
			PRIMARY KEY (artist_id, song_id) \
		)")
	# c.create_idx("r4_song_artist", "song_id")		# handled by create_delete_fk
	# c.create_idx("r4_song_artist", "artist_id")
	c.create_delete_fk("r4_song_artist", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_artist", "r4_artists", "artist_id")

	c.update(" \
		CREATE TABLE r4_groups ( \
			group_id				SERIAL		PRIMARY KEY, \
			group_name				TEXT		, \
			group_name_searchable	TEXT 		NOT NULL, \
			group_elec_block		SMALLINT, \
			group_cool_time			SMALLINT	DEFAULT 900 \
		)")

	c.update(" \
		CREATE TABLE r4_song_group ( \
			song_id					INTEGER		NOT NULL, \
			group_id				INTEGER		NOT NULL, \
			group_is_tag			BOOLEAN		DEFAULT TRUE \
			PRIMARY KEY (group_id, song_id) \
		)")
	# c.create_idx("r4_song_group", "song_id")		# handled by create_delete_fk
	# c.create_idx("r4_song_group", "group_id")
	c.create_delete_fk("r4_song_group", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_group", "r4_groups", "group_id")

	c.update(" \
		CREATE TABLE r4_schedule ( \
			sched_id				SERIAL		PRIMARY KEY, \
			sched_start				INTEGER		, \
			sched_start_actual		INTEGER		, \
			sched_end				INTEGER		, \
			sched_end_actual		INTEGER		, \
			sched_type				TEXT		, \
			sched_name				TEXT		, \
			sched_url				TEXT 		, \
			sched_dj_user_id        INT         , \
			sid						SMALLINT	NOT NULL, \
			sched_public			BOOLEAN		DEFAULT TRUE, \
			sched_timed				BOOLEAN		DEFAULT TRUE, \
			sched_in_progress		BOOLEAN		DEFAULT FALSE, \
			sched_used				BOOLEAN		DEFAULT FALSE, \
			sched_use_crossfade		BOOLEAN		DEFAULT TRUE, \
			sched_use_tag_suffix	BOOLEAN		DEFAULT TRUE, \
			sched_creator_user_id	INT \
		)")
	c.create_idx("r4_schedule", "sched_used")
	c.create_idx("r4_schedule", "sched_in_progress")
	c.create_idx("r4_schedule", "sched_public")
	c.create_idx("r4_schedule", "sched_start_actual")
	c.create_delete_fk("r4_schedule", "phpbb_users", "sched_dj_user_id", foreign_key="user_id", create_idx=False)

	c.update(" \
		CREATE TABLE r4_elections ( \
			elec_id					INTEGER		PRIMARY KEY, \
			elec_used				BOOLEAN		DEFAULT FALSE, \
			elec_in_progress		BOOLEAN		DEFAULT FALSE, \
			elec_start_actual		INTEGER		, \
			elec_type				TEXT		, \
			elec_priority			BOOLEAN		DEFAULT FALSE, \
			sid						SMALLINT	NOT NULL, \
			sched_id 				INT 		 \
		)")
	if c.is_postgres:
		c.update("ALTER TABLE r4_elections ALTER COLUMN elec_id SET DEFAULT nextval('r4_schedule_sched_id_seq')")
	c.create_idx("r4_elections", "elec_id")
	c.create_idx("r4_elections", "elec_used")
	c.create_idx("r4_elections", "sid")
	c.create_delete_fk("r4_elections", "r4_schedule", "sched_id")

	c.update(" \
		CREATE TABLE r4_election_entries ( \
			entry_id				SERIAL		PRIMARY KEY, \
			song_id					INTEGER		NOT NULL, \
			elec_id					INTEGER		NOT NULL, \
			entry_type				SMALLINT	DEFAULT 2, \
			entry_position			SMALLINT	, \
			entry_votes				SMALLINT	DEFAULT 0 \
		)")
	# c.create_idx("r4_election_entries", "song_id")	# handled by create_delete_fk
	# c.create_idx("r4_election_entries", "elec_id")
	c.create_delete_fk("r4_election_entries", "r4_songs", "song_id")
	c.create_delete_fk("r4_election_entries", "r4_elections", "elec_id")

	c.update(" \
		CREATE TABLE r4_one_ups ( \
			one_up_id				INTEGER		NOT NULL, \
			sched_id				INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			one_up_order			SMALLINT	, \
			one_up_used				BOOLEAN		DEFAULT FALSE, \
			one_up_queued			BOOLEAN		DEFAULT FALSE, \
			one_up_sid				SMALLINT	NOT NULL \
		)")
	if c.is_postgres:
		c.update("ALTER TABLE r4_one_ups ALTER COLUMN one_up_id SET DEFAULT nextval('r4_schedule_sched_id_seq')")
	# c.create_idx("r4_one_ups", "sched_id")		# handled by create_delete_fk
	# c.create_idx("r4_one_ups", "song_id")
	c.create_delete_fk("r4_one_ups", "r4_schedule", "sched_id")
	c.create_delete_fk("r4_one_ups", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_listeners ( \
			listener_id				SERIAL		PRIMARY KEY, \
			sid						SMALLINT	NOT NULL, \
			listener_ip				TEXT		, \
			listener_relay			TEXT		, \
			listener_agent			TEXT		, \
			listener_icecast_id		INTEGER		NOT NULL, \
			listener_lock			BOOLEAN		DEFAULT FALSE, \
			listener_lock_sid		SMALLINT	, \
			listener_lock_counter	SMALLINT	DEFAULT 0, \
			listener_purge			BOOLEAN		DEFAULT FALSE, \
			listener_voted_entry	INTEGER		, \
			user_id					INTEGER		DEFAULT 1 \
		)")
	c.create_idx("r4_listeners", "sid")
	# c.create_idx("r4_listeners", "user_id")		# handled by create_delete_fk
	c.create_delete_fk("r4_listeners", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_listener_counts ( \
			lc_time					INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			lc_guests				SMALLINT	, \
			lc_users				SMALLINT	, \
			lc_users_active				SMALLINT	, \
			lc_guests_active			SMALLINT	\
		)")
	c.create_idx("r4_listener_counts", "lc_time")
	c.create_idx("r4_listener_counts", "sid")

	c.update(" \
		CREATE TABLE r4_donations ( \
			donation_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		, \
			donation_amount			REAL		, \
			donation_message		TEXT		, \
			donation_private		BOOLEAN		DEFAULT TRUE \
		)")

	c.update(" \
		CREATE TABLE r4_request_store ( \
			reqstor_id				SERIAL		PRIMARY KEY, \
			reqstor_order			SMALLINT	DEFAULT 32766, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL \
		)")
	# c.create_idx("r4_request_store", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_request_store", "song_id")
	c.create_delete_fk("r4_request_store", "phpbb_users", "user_id")
	c.create_delete_fk("r4_request_store", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_request_line ( \
			user_id					INTEGER		NOT NULL, \
			sid					SMALLINT	NOT NULL, \
			line_wait_start				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			line_expiry_tune_in			INTEGER		, \
			line_expiry_election			INTEGER \
		)")
	# c.create_idx("r4_request_line", "user_id")		# handled by create_delete_fk
	c.create_idx("r4_request_line", "sid")
	c.create_idx("r4_request_line", "line_wait_start")
	c.create_delete_fk("r4_request_line", "phpbb_users", "user_id")
	if c.is_postgres:
		c.update("ALTER TABLE r4_request_line ADD CONSTRAINT unique_user_id UNIQUE (user_id)")

	c.update(" \
		CREATE TABLE r4_request_history ( \
			request_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			request_fulfilled_at			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			request_wait_time			INTEGER		, \
			request_line_size			INTEGER		, \
			request_at_count			INTEGER		, \
			sid                         SMALLINT    \
		)")
	# c.create_idx("r4_request_history", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_request_history", "song_id")
	c.create_delete_fk("r4_request_history", "r4_songs", "song_id")
	c.create_delete_fk("r4_request_history", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_vote_history ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank			INTEGER		, \
			vote_at_count			INTEGER		, \
			entry_id				INTEGER		, \
			sid  					SMALLINT \
		)")
	# c.create_idx("r4_vote_history", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_vote_history", "song_id")
	# c.create_idx("r4_vote_history", "entry_id")
	c.create_idx("r4_vote_history", "sid")
	c.create_null_fk("r4_vote_history", "r4_election_entries", "entry_id")
	c.create_null_fk("r4_vote_history", "r4_elections", "elec_id")
	c.create_delete_fk("r4_vote_history", "r4_songs", "song_id")
	c.create_delete_fk("r4_vote_history", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_vote_history_archived ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank				INTEGER		, \
			vote_at_count				INTEGER		, \
			entry_id				INTEGER		\
		)")
	c.create_null_fk("r4_vote_history_archived", "r4_election_entries", "entry_id")
	c.create_null_fk("r4_vote_history_archived", "r4_elections", "elec_id")
	c.create_null_fk("r4_vote_history_archived", "r4_songs", "song_id")
	c.create_delete_fk("r4_vote_history_archived", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_api_keys ( \
			api_id					SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			api_ip					TEXT		, \
			api_key					VARCHAR(10) , \
			api_expiry				INTEGER		\
		)")
	# c.create_idx("r4_api_keys", "user_id")		# handled by create_delete_fk
	c.create_idx("r4_api_keys", "api_ip")
	c.create_delete_fk("r4_api_keys", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_song_history ( \
			songhist_id				SERIAL		PRIMARY KEY, \
			songhist_time			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			song_id					INTEGER		NOT NULL \
		)")
	c.create_idx("r4_song_history", "sid")
	c.create_delete_fk("r4_song_history", "r4_songs", "song_id")

	try:
		c.update(" \
			CREATE TABLE r4_pref_storage ( \
				user_id 				INT 		, \
				ip_address 				TEXT 		, \
				prefs 					JSONB \
			)")
		c.create_delete_fk("r4_pref_storage", "phpbb_users", "user_id")
	except:
		log.critical("init_db", "Could not create r4_pref_storage - feature requires Pg 9.4 or higher.  See README.")

	if config.get("standalone_mode"):
		_fill_test_tables()

	c.commit()
Ejemplo n.º 19
0
def create_tables():
	if config.get("standalone_mode"):
		_create_test_tables()

	trgrm_exists = c.fetch_var("SELECT extname FROM pg_extension WHERE extname = 'pg_trgm'")
	if not trgrm_exists or not trgrm_exists == "pg_trgm":
		try:
			c.update("CREATE EXTENSION pg_trgm")
		except:
			print "Could not create trigram extension."
			print "Please run 'CREATE EXTENSION pg_trgm;' as a superuser on the database."
			print "You may also need to install the Postgres Contributions package. (postgres-contrib)"
			raise

	# From: https://wiki.postgresql.org/wiki/First_%28aggregate%29
	# Used in rainwave/playlist.py
	first_exists = c.fetch_var("SELECT proname FROM pg_proc WHERE proname = 'first' AND proisagg")
	if not first_exists or first_exists != "first":
		c.update("""
			-- Create a function that always returns the first non-NULL item
			CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
			RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
			        SELECT $1;
			$$;

			-- And then wrap an aggregate around it
			CREATE AGGREGATE public.FIRST (
			        sfunc    = public.first_agg,
			        basetype = anyelement,
			        stype    = anyelement
			);
		""")

	last_exists = c.fetch_var("SELECT proname FROM pg_proc WHERE proname = 'last' AND proisagg")
	if not last_exists or last_exists != "last":
		c.update("""
			-- Create a function that always returns the last non-NULL item
			CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
			RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
			        SELECT $2;
			$$;

			-- And then wrap an aggregate around it
			CREATE AGGREGATE public.LAST (
			        sfunc    = public.last_agg,
			        basetype = anyelement,
			        stype    = anyelement
			);
		""")

	c.update(" \
		CREATE TABLE r4_albums ( \
			album_id				SERIAL		PRIMARY KEY, \
			album_name				TEXT		, \
			album_name_searchable	TEXT 		NOT NULL, \
			album_year				SMALLINT, \
			album_added_on			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) \
		)")
	if c.is_postgres:
		c.update("CREATE INDEX album_name_trgm_gin ON r4_albums USING GIN(album_name_searchable gin_trgm_ops)")

	c.update(" \
		CREATE TABLE r4_songs ( \
			song_id						SERIAL		PRIMARY KEY, \
			album_id 					INTEGER, \
			song_origin_sid				SMALLINT	NOT NULL, \
			song_verified				BOOLEAN		DEFAULT TRUE, \
			song_scanned				BOOLEAN		DEFAULT TRUE, \
			song_filename				TEXT		, \
			song_title					TEXT		, \
			song_title_searchable		TEXT		NOT NULL, \
			song_artist_tag				TEXT		, \
			song_url					TEXT		, \
			song_link_text				TEXT		, \
			song_length					SMALLINT	, \
			song_track_number			SMALLINT	, \
			song_disc_number			SMALLINT	, \
			song_year				SMALLINT	, \
			song_added_on				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			song_rating					REAL		DEFAULT 0, \
			song_rating_count			INTEGER		DEFAULT 0, \
			song_fave_count				INTEGER		DEFAULT 0, \
			song_request_count			INT			DEFAULT 0, \
			song_cool_multiply			REAL		DEFAULT 1, \
			song_cool_override			INTEGER		, \
			song_file_mtime				INTEGER		, \
			song_replay_gain			TEXT 		, \
			song_vote_count				INTEGER		DEFAULT 0, \
			song_votes_seen				INTEGER		DEFAULT 0, \
			song_vote_share				REAL 		, \
			song_artist_parseable		TEXT \
 		)")
	c.create_idx("r4_songs", "song_verified")
	c.create_idx("r4_songs", "song_rating")
	c.create_idx("r4_songs", "song_request_count")
	c.create_null_fk("r4_songs", "r4_albums", "album_id")
	if c.is_postgres:
		c.update("CREATE INDEX song_title_trgm_gin ON r4_songs USING GIN(song_title_searchable gin_trgm_ops)")

	c.update(" \
		CREATE TABLE r4_song_sid ( \
			song_id						INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			song_cool					BOOLEAN		DEFAULT FALSE, \
			song_cool_end				INTEGER		DEFAULT 0, \
			song_elec_appearances		INTEGER		DEFAULT 0, \
			song_elec_last				INTEGER		DEFAULT 0, \
			song_elec_blocked			BOOLEAN 	DEFAULT FALSE, \
			song_elec_blocked_num		SMALLINT	DEFAULT 0, \
			song_elec_blocked_by		TEXT		, \
			song_played_last			INTEGER		, \
			song_exists					BOOLEAN		DEFAULT TRUE, \
			song_request_only			BOOLEAN		DEFAULT FALSE, \
			song_request_only_end		INTEGER		DEFAULT 0, \
			PRIMARY KEY (song_id, sid) \
		)")
	# c.create_idx("r4_song_sid", "song_id")	# handled by create_delete_fk
	c.create_idx("r4_song_sid", "sid")
	c.create_idx("r4_song_sid", "song_cool")
	c.create_idx("r4_song_sid", "song_elec_blocked")
	c.create_idx("r4_song_sid", "song_exists")
	c.create_idx("r4_song_sid", "song_request_only")
	c.create_delete_fk("r4_song_sid", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_song_ratings ( \
			song_id					INTEGER		NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			song_rating_user			REAL		, \
			song_rated_at				INTEGER		, \
			song_rated_at_rank			INTEGER		, \
			song_rated_at_count			INTEGER		, \
			song_fave				BOOLEAN, \
			PRIMARY KEY (user_id, song_id) \
		)")
	# c.create_idx("r4_song_ratings", "user_id", "song_id") Should be handled by primary key
	c.create_idx("r4_song_ratings", "song_fave")
	c.create_delete_fk("r4_song_ratings", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_ratings", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_album_sid ( \
			album_exists				BOOLEAN		DEFAULT TRUE, \
			album_id					INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			album_song_count			SMALLINT	DEFAULT 0, \
			album_played_last			INTEGER		DEFAULT 0, \
			album_requests_pending		BOOLEAN, \
			album_cool					BOOLEAN		DEFAULT FALSE, \
			album_cool_multiply			REAL		DEFAULT 1, \
			album_cool_override			INTEGER		, \
			album_cool_lowest			INTEGER		DEFAULT 0, \
			album_updated				INTEGER		DEFAULT 0, \
			album_elec_last				INTEGER		DEFAULT 0, \
			album_rating				REAL		NOT NULL DEFAULT 0, \
			album_rating_count			INTEGER		DEFAULT 0, \
			album_request_count			INTEGER		DEFAULT 0, \
			album_fave_count			INTEGER		DEFAULT 0, \
			album_vote_count			INTEGER		DEFAULT 0, \
			album_votes_seen			INTEGER		DEFAULT 0, \
			album_vote_share			REAL 		,\
			album_newest_song_time		INTEGER		DEFAULT 0, \
			PRIMARY KEY (album_id, sid) \
		)")
	c.create_idx("r4_album_sid", "album_rating")
	c.create_idx("r4_album_sid", "album_request_count")
	c.create_idx("r4_album_sid", "album_exists")
	c.create_idx("r4_album_sid", "sid")
	c.create_idx("r4_album_sid", "album_requests_pending")
	c.create_idx("r4_album_sid", "album_exists", "sid")
	c.create_delete_fk("r4_album_sid", "r4_albums", "album_id")

	c.update(" \
		CREATE TABLE r4_album_ratings ( \
			album_id				INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			album_rating_user		REAL		, \
			album_fave				BOOLEAN, \
			album_rating_complete	BOOLEAN		DEFAULT FALSE \
		)")
	# 			PRIMARY KEY (user_id, album_id, sid) \
	c.create_idx("r4_album_ratings", "user_id", "album_id", "sid") 	#Should be handled by primary key.
	c.create_idx("r4_album_ratings", "album_id", "sid")
	c.create_idx("r4_album_ratings", "album_fave")
	c.create_idx("r4_album_ratings", "album_fave", "sid")
	c.create_delete_fk("r4_album_ratings", "r4_albums", "album_id", create_idx=False)
	c.create_delete_fk("r4_album_ratings", "phpbb_users", "user_id", create_idx=False)

	c.update(" \
		CREATE TABLE r4_artists		( \
			artist_id				SERIAL		PRIMARY KEY, \
			artist_name				TEXT		, \
			artist_name_searchable	TEXT 		NOT NULL \
		)")
	if c.is_postgres:
		c.update("CREATE INDEX artist_name_trgm_gin ON r4_artists USING GIN(artist_name_searchable gin_trgm_ops)")

	c.update(" \
		CREATE TABLE r4_song_artist	( \
			song_id					INTEGER		NOT NULL, \
			artist_id				INTEGER		NOT NULL, \
			artist_order			SMALLINT    DEFAULT 0, \
			artist_is_tag			BOOLEAN		DEFAULT TRUE, \
			PRIMARY KEY (artist_id, song_id) \
		)")
	# c.create_idx("r4_song_artist", "song_id")		# handled by create_delete_fk
	# c.create_idx("r4_song_artist", "artist_id")
	c.create_delete_fk("r4_song_artist", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_artist", "r4_artists", "artist_id")

	c.update(" \
		CREATE TABLE r4_groups ( \
			group_id				SERIAL		PRIMARY KEY, \
			group_name				TEXT		, \
			group_name_searchable	TEXT 		NOT NULL, \
			group_elec_block		SMALLINT, \
			group_cool_time			SMALLINT	DEFAULT 900 \
		)")

	c.update(" \
		CREATE TABLE r4_song_group ( \
			song_id					INTEGER		NOT NULL, \
			group_id				INTEGER		NOT NULL, \
			group_is_tag			BOOLEAN		DEFAULT TRUE, \
			PRIMARY KEY (group_id, song_id) \
		)")
	# c.create_idx("r4_song_group", "song_id")		# handled by create_delete_fk
	# c.create_idx("r4_song_group", "group_id")
	c.create_delete_fk("r4_song_group", "r4_songs", "song_id")
	c.create_delete_fk("r4_song_group", "r4_groups", "group_id")

	_create_group_sid_table()

	c.update(" \
		CREATE TABLE r4_schedule ( \
			sched_id				SERIAL		PRIMARY KEY, \
			sched_start				INTEGER		, \
			sched_start_actual		INTEGER		, \
			sched_end				INTEGER		, \
			sched_end_actual		INTEGER		, \
			sched_type				TEXT		, \
			sched_name				TEXT		, \
			sched_url				TEXT 		, \
			sched_dj_user_id        INT         , \
			sid						SMALLINT	NOT NULL, \
			sched_public			BOOLEAN		DEFAULT TRUE, \
			sched_timed				BOOLEAN		DEFAULT TRUE, \
			sched_in_progress		BOOLEAN		DEFAULT FALSE, \
			sched_used				BOOLEAN		DEFAULT FALSE, \
			sched_use_crossfade		BOOLEAN		DEFAULT TRUE, \
			sched_use_tag_suffix	BOOLEAN		DEFAULT TRUE, \
			sched_creator_user_id	INT \
		)")
	c.create_idx("r4_schedule", "sched_used")
	c.create_idx("r4_schedule", "sched_in_progress")
	c.create_idx("r4_schedule", "sched_public")
	c.create_idx("r4_schedule", "sched_start_actual")
	c.create_delete_fk("r4_schedule", "phpbb_users", "sched_dj_user_id", foreign_key="user_id", create_idx=False)

	c.update(" \
		CREATE TABLE r4_elections ( \
			elec_id					INTEGER		PRIMARY KEY, \
			elec_used				BOOLEAN		DEFAULT FALSE, \
			elec_in_progress		BOOLEAN		DEFAULT FALSE, \
			elec_start_actual		INTEGER		, \
			elec_type				TEXT		, \
			elec_priority			BOOLEAN		DEFAULT FALSE, \
			sid						SMALLINT	NOT NULL, \
			sched_id 				INT 		 \
		)")
	if c.is_postgres:
		c.update("ALTER TABLE r4_elections ALTER COLUMN elec_id SET DEFAULT nextval('r4_schedule_sched_id_seq')")
	c.create_idx("r4_elections", "elec_id")
	c.create_idx("r4_elections", "elec_used")
	c.create_idx("r4_elections", "sid")
	c.create_delete_fk("r4_elections", "r4_schedule", "sched_id")

	c.update(" \
		CREATE TABLE r4_election_entries ( \
			entry_id				SERIAL		PRIMARY KEY, \
			song_id					INTEGER		NOT NULL, \
			elec_id					INTEGER		NOT NULL, \
			entry_type				SMALLINT	DEFAULT 2, \
			entry_position			SMALLINT	, \
			entry_votes				SMALLINT	DEFAULT 0 \
		)")
	# c.create_idx("r4_election_entries", "song_id")	# handled by create_delete_fk
	# c.create_idx("r4_election_entries", "elec_id")
	c.create_delete_fk("r4_election_entries", "r4_songs", "song_id")
	c.create_delete_fk("r4_election_entries", "r4_elections", "elec_id")

	c.update(" \
		CREATE TABLE r4_one_ups ( \
			one_up_id				INTEGER		NOT NULL, \
			sched_id				INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			one_up_order			SMALLINT	, \
			one_up_used				BOOLEAN		DEFAULT FALSE, \
			one_up_queued			BOOLEAN		DEFAULT FALSE, \
			one_up_sid				SMALLINT	NOT NULL \
		)")
	if c.is_postgres:
		c.update("ALTER TABLE r4_one_ups ALTER COLUMN one_up_id SET DEFAULT nextval('r4_schedule_sched_id_seq')")
	# c.create_idx("r4_one_ups", "sched_id")		# handled by create_delete_fk
	# c.create_idx("r4_one_ups", "song_id")
	c.create_delete_fk("r4_one_ups", "r4_schedule", "sched_id")
	c.create_delete_fk("r4_one_ups", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_listeners ( \
			listener_id				SERIAL		PRIMARY KEY, \
			sid						SMALLINT	NOT NULL, \
			listener_ip				TEXT		, \
			listener_relay			TEXT		, \
			listener_agent			TEXT		, \
			listener_icecast_id		INTEGER		NOT NULL, \
			listener_lock			BOOLEAN		DEFAULT FALSE, \
			listener_lock_sid		SMALLINT	, \
			listener_lock_counter	SMALLINT	DEFAULT 0, \
			listener_purge			BOOLEAN		DEFAULT FALSE, \
			listener_voted_entry	INTEGER		, \
			user_id					INTEGER		DEFAULT 1 \
		)")
	c.create_idx("r4_listeners", "sid")
	# c.create_idx("r4_listeners", "user_id")		# handled by create_delete_fk
	c.create_delete_fk("r4_listeners", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_listener_counts ( \
			lc_time					INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			lc_guests				SMALLINT	, \
			lc_users				SMALLINT	, \
			lc_users_active				SMALLINT	, \
			lc_guests_active			SMALLINT	\
		)")
	c.create_idx("r4_listener_counts", "lc_time")
	c.create_idx("r4_listener_counts", "sid")

	c.update(" \
		CREATE TABLE r4_donations ( \
			donation_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		, \
			donation_amount			REAL		, \
			donation_message		TEXT		, \
			donation_private		BOOLEAN		DEFAULT TRUE \
		)")

	c.update(" \
		CREATE TABLE r4_request_store ( \
			reqstor_id				SERIAL		PRIMARY KEY, \
			reqstor_order			SMALLINT	DEFAULT 32766, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL \
		)")
	# c.create_idx("r4_request_store", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_request_store", "song_id")
	c.create_delete_fk("r4_request_store", "phpbb_users", "user_id")
	c.create_delete_fk("r4_request_store", "r4_songs", "song_id")

	c.update(" \
		CREATE TABLE r4_request_line ( \
			user_id					INTEGER		NOT NULL, \
			sid					SMALLINT	NOT NULL, \
			line_wait_start				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			line_expiry_tune_in			INTEGER		, \
			line_expiry_election			INTEGER , \
			line_has_had_valid              BOOLEAN DEFAULT FALSE \
		)")
	# c.create_idx("r4_request_line", "user_id")		# handled by create_delete_fk
	c.create_idx("r4_request_line", "sid")
	c.create_idx("r4_request_line", "line_wait_start")
	c.create_delete_fk("r4_request_line", "phpbb_users", "user_id")
	if c.is_postgres:
		c.update("ALTER TABLE r4_request_line ADD CONSTRAINT unique_user_id UNIQUE (user_id)")

	c.update(" \
		CREATE TABLE r4_request_history ( \
			request_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			request_fulfilled_at			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			request_wait_time			INTEGER		, \
			request_line_size			INTEGER		, \
			request_at_count			INTEGER		, \
			sid                         SMALLINT    \
		)")
	# c.create_idx("r4_request_history", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_request_history", "song_id")
	c.create_delete_fk("r4_request_history", "r4_songs", "song_id")
	c.create_delete_fk("r4_request_history", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_vote_history ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank			INTEGER		, \
			vote_at_count			INTEGER		, \
			entry_id				INTEGER		, \
			sid  					SMALLINT \
		)")
	# c.create_idx("r4_vote_history", "user_id")		# handled by create_delete_fk
	# c.create_idx("r4_vote_history", "song_id")
	# c.create_idx("r4_vote_history", "entry_id")
	c.create_idx("r4_vote_history", "sid")
	c.create_null_fk("r4_vote_history", "r4_election_entries", "entry_id")
	c.create_null_fk("r4_vote_history", "r4_elections", "elec_id")
	c.create_delete_fk("r4_vote_history", "r4_songs", "song_id")
	c.create_delete_fk("r4_vote_history", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_vote_history_archived ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank				INTEGER		, \
			vote_at_count				INTEGER		, \
			entry_id				INTEGER		\
		)")
	c.create_null_fk("r4_vote_history_archived", "r4_election_entries", "entry_id")
	c.create_null_fk("r4_vote_history_archived", "r4_elections", "elec_id")
	c.create_null_fk("r4_vote_history_archived", "r4_songs", "song_id")
	c.create_delete_fk("r4_vote_history_archived", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_api_keys ( \
			api_id					SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			api_ip					TEXT		, \
			api_key					VARCHAR(10) , \
			api_expiry				INTEGER		\
		)")
	# c.create_idx("r4_api_keys", "user_id")		# handled by create_delete_fk
	c.create_idx("r4_api_keys", "api_ip")
	c.create_delete_fk("r4_api_keys", "phpbb_users", "user_id")

	c.update(" \
		CREATE TABLE r4_song_history ( \
			songhist_id				SERIAL		PRIMARY KEY, \
			songhist_time			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			song_id					INTEGER		NOT NULL \
		)")
	c.create_idx("r4_song_history", "sid")
	c.create_delete_fk("r4_song_history", "r4_songs", "song_id")

	try:
		c.update(" \
			CREATE TABLE r4_pref_storage ( \
				user_id 				INT 		, \
				ip_address 				TEXT 		, \
				prefs 					JSONB \
			)")
		c.create_delete_fk("r4_pref_storage", "phpbb_users", "user_id")
	except:
		log.critical("init_db", "Could not create r4_pref_storage - feature requires Pg 9.4 or higher.  See README.")

	if config.get("standalone_mode"):
		_fill_test_tables()

	c.commit()
Ejemplo n.º 20
0
Archivo: db.py Proyecto: nv043/rainwave
def create_tables():
    if config.get("standalone_mode"):
        _create_test_tables()

    trgrm_exists = c.fetch_var(
        "SELECT extname FROM pg_extension WHERE extname = 'pg_trgm'")
    if not trgrm_exists or not trgrm_exists == "pg_trgm":
        try:
            c.update("CREATE EXTENSION pg_trgm")
        except:
            print "Could not create trigram extension."
            print "Please run 'CREATE EXTENSION pg_trgm;' as a superuser on the database."
            print "You may also need to install the Postgres Contributions package. (postgres-contrib)"
            raise

    # From: https://wiki.postgresql.org/wiki/First_%28aggregate%29
    # Used in rainwave/playlist.py
    first_exists = c.fetch_var(
        "SELECT proname FROM pg_proc WHERE proname = 'first' AND proisagg")
    if not first_exists or first_exists != "first":
        c.update("""
			-- Create a function that always returns the first non-NULL item
			CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
			RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
			        SELECT $1;
			$$;

			-- And then wrap an aggregate around it
			CREATE AGGREGATE public.FIRST (
			        sfunc    = public.first_agg,
			        basetype = anyelement,
			        stype    = anyelement
			);
		""")

    last_exists = c.fetch_var(
        "SELECT proname FROM pg_proc WHERE proname = 'last' AND proisagg")
    if not last_exists or last_exists != "last":
        c.update("""
			-- Create a function that always returns the last non-NULL item
			CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
			RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
			        SELECT $2;
			$$;

			-- And then wrap an aggregate around it
			CREATE AGGREGATE public.LAST (
			        sfunc    = public.last_agg,
			        basetype = anyelement,
			        stype    = anyelement
			);
		""")

    c.update(" \
		CREATE TABLE r4_albums ( \
			album_id				SERIAL		PRIMARY KEY, \
			album_name				TEXT		, \
			album_name_searchable	TEXT 		NOT NULL, \
			album_year				SMALLINT, \
			album_added_on			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) \
		)")
    if c.is_postgres:
        c.update(
            "CREATE INDEX album_name_trgm_gin ON r4_albums USING GIN(album_name_searchable gin_trgm_ops)"
        )

    c.update(" \
		CREATE TABLE r4_songs ( \
			song_id						SERIAL		PRIMARY KEY, \
			album_id 					INTEGER, \
			song_origin_sid				SMALLINT	NOT NULL, \
			song_verified				BOOLEAN		DEFAULT TRUE, \
			song_scanned				BOOLEAN		DEFAULT TRUE, \
			song_filename				TEXT		, \
			song_title					TEXT		, \
			song_title_searchable		TEXT		NOT NULL, \
			song_artist_tag				TEXT		, \
			song_url					TEXT		, \
			song_link_text				TEXT		, \
			song_length					SMALLINT	, \
			song_track_number			SMALLINT	, \
			song_disc_number			SMALLINT	, \
			song_year				SMALLINT	, \
			song_added_on				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			song_rating					REAL		DEFAULT 0, \
			song_rating_count			INTEGER		DEFAULT 0, \
			song_fave_count				INTEGER		DEFAULT 0, \
			song_request_count			INT			DEFAULT 0, \
			song_cool_multiply			REAL		DEFAULT 1, \
			song_cool_override			INTEGER		, \
			song_file_mtime				INTEGER		, \
			song_replay_gain			TEXT 		, \
			song_vote_count				INTEGER		DEFAULT 0, \
			song_votes_seen				INTEGER		DEFAULT 0, \
			song_vote_share				REAL 		, \
			song_artist_parseable		TEXT \
 		)")
    c.create_idx("r4_songs", "song_verified")
    c.create_idx("r4_songs", "song_rating")
    c.create_idx("r4_songs", "song_request_count")
    c.create_null_fk("r4_songs", "r4_albums", "album_id")
    if c.is_postgres:
        c.update(
            "CREATE INDEX song_title_trgm_gin ON r4_songs USING GIN(song_title_searchable gin_trgm_ops)"
        )

    c.update(" \
		CREATE TABLE r4_song_sid ( \
			song_id						INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			song_cool					BOOLEAN		DEFAULT FALSE, \
			song_cool_end				INTEGER		DEFAULT 0, \
			song_elec_appearances		INTEGER		DEFAULT 0, \
			song_elec_last				INTEGER		DEFAULT 0, \
			song_elec_blocked			BOOLEAN 	DEFAULT FALSE, \
			song_elec_blocked_num		SMALLINT	DEFAULT 0, \
			song_elec_blocked_by		TEXT		, \
			song_played_last			INTEGER		, \
			song_exists					BOOLEAN		DEFAULT TRUE, \
			song_request_only			BOOLEAN		DEFAULT FALSE, \
			song_request_only_end		INTEGER		DEFAULT 0, \
			PRIMARY KEY (song_id, sid) \
		)")
    # c.create_idx("r4_song_sid", "song_id")	# handled by create_delete_fk
    c.create_idx("r4_song_sid", "sid")
    c.create_idx("r4_song_sid", "song_cool")
    c.create_idx("r4_song_sid", "song_elec_blocked")
    c.create_idx("r4_song_sid", "song_exists")
    c.create_idx("r4_song_sid", "song_request_only")
    c.create_delete_fk("r4_song_sid", "r4_songs", "song_id")

    c.update(" \
		CREATE TABLE r4_song_ratings ( \
			song_id					INTEGER		NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			song_rating_user			REAL		, \
			song_rated_at				INTEGER		, \
			song_rated_at_rank			INTEGER		, \
			song_rated_at_count			INTEGER		, \
			song_fave				BOOLEAN, \
			PRIMARY KEY (user_id, song_id) \
		)")
    # c.create_idx("r4_song_ratings", "user_id", "song_id") Should be handled by primary key
    c.create_idx("r4_song_ratings", "song_fave")
    c.create_delete_fk("r4_song_ratings", "r4_songs", "song_id")
    c.create_delete_fk("r4_song_ratings", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_album_sid ( \
			album_exists				BOOLEAN		DEFAULT TRUE, \
			album_id					INTEGER		NOT NULL, \
			sid							SMALLINT	NOT NULL, \
			album_song_count			SMALLINT	DEFAULT 0, \
			album_played_last			INTEGER		DEFAULT 0, \
			album_requests_pending		BOOLEAN, \
			album_cool					BOOLEAN		DEFAULT FALSE, \
			album_cool_multiply			REAL		DEFAULT 1, \
			album_cool_override			INTEGER		, \
			album_cool_lowest			INTEGER		DEFAULT 0, \
			album_updated				INTEGER		DEFAULT 0, \
			album_elec_last				INTEGER		DEFAULT 0, \
			album_rating				REAL		NOT NULL DEFAULT 0, \
			album_rating_count			INTEGER		DEFAULT 0, \
			album_request_count			INTEGER		DEFAULT 0, \
			album_fave_count			INTEGER		DEFAULT 0, \
			album_vote_count			INTEGER		DEFAULT 0, \
			album_votes_seen			INTEGER		DEFAULT 0, \
			album_vote_share			REAL 		,\
			album_newest_song_time		INTEGER		DEFAULT 0, \
			PRIMARY KEY (album_id, sid) \
		)")
    c.create_idx("r4_album_sid", "album_rating")
    c.create_idx("r4_album_sid", "album_request_count")
    c.create_idx("r4_album_sid", "album_exists")
    c.create_idx("r4_album_sid", "sid")
    c.create_idx("r4_album_sid", "album_requests_pending")
    c.create_idx("r4_album_sid", "album_exists", "sid")
    c.create_delete_fk("r4_album_sid", "r4_albums", "album_id")

    c.update(" \
		CREATE TABLE r4_album_ratings ( \
			album_id				INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL, \
			user_id					INTEGER		NOT NULL, \
			album_rating_user		REAL		, \
			album_fave				BOOLEAN, \
			album_rating_complete	BOOLEAN		DEFAULT FALSE \
		)")
    # 			PRIMARY KEY (user_id, album_id, sid) \
    c.create_idx("r4_album_ratings", "user_id", "album_id",
                 "sid")  #Should be handled by primary key.
    c.create_idx("r4_album_ratings", "album_id", "sid")
    c.create_idx("r4_album_ratings", "album_fave")
    c.create_idx("r4_album_ratings", "album_fave", "sid")
    c.create_delete_fk("r4_album_ratings",
                       "r4_albums",
                       "album_id",
                       create_idx=False)
    c.create_delete_fk("r4_album_ratings",
                       "phpbb_users",
                       "user_id",
                       create_idx=False)

    c.update(" \
		CREATE TABLE r4_artists		( \
			artist_id				SERIAL		PRIMARY KEY, \
			artist_name				TEXT		, \
			artist_name_searchable	TEXT 		NOT NULL \
		)")
    if c.is_postgres:
        c.update(
            "CREATE INDEX artist_name_trgm_gin ON r4_artists USING GIN(artist_name_searchable gin_trgm_ops)"
        )

    c.update(" \
		CREATE TABLE r4_song_artist	( \
			song_id					INTEGER		NOT NULL, \
			artist_id				INTEGER		NOT NULL, \
			artist_order			SMALLINT    DEFAULT 0, \
			artist_is_tag			BOOLEAN		DEFAULT TRUE, \
			PRIMARY KEY (artist_id, song_id) \
		)")
    # c.create_idx("r4_song_artist", "song_id")		# handled by create_delete_fk
    # c.create_idx("r4_song_artist", "artist_id")
    c.create_delete_fk("r4_song_artist", "r4_songs", "song_id")
    c.create_delete_fk("r4_song_artist", "r4_artists", "artist_id")

    c.update(" \
		CREATE TABLE r4_groups ( \
			group_id				SERIAL		PRIMARY KEY, \
			group_name				TEXT		, \
			group_name_searchable	TEXT 		NOT NULL, \
			group_elec_block		SMALLINT, \
			group_cool_time			SMALLINT	DEFAULT 900 \
		)")

    c.update(" \
		CREATE TABLE r4_song_group ( \
			song_id					INTEGER		NOT NULL, \
			group_id				INTEGER		NOT NULL, \
			group_is_tag			BOOLEAN		DEFAULT TRUE, \
			PRIMARY KEY (group_id, song_id) \
		)")
    # c.create_idx("r4_song_group", "song_id")		# handled by create_delete_fk
    # c.create_idx("r4_song_group", "group_id")
    c.create_delete_fk("r4_song_group", "r4_songs", "song_id")
    c.create_delete_fk("r4_song_group", "r4_groups", "group_id")

    _create_group_sid_table()

    c.update(" \
		CREATE TABLE r4_schedule ( \
			sched_id				SERIAL		PRIMARY KEY, \
			sched_start				INTEGER		, \
			sched_start_actual		INTEGER		, \
			sched_end				INTEGER		, \
			sched_end_actual		INTEGER		, \
			sched_type				TEXT		, \
			sched_name				TEXT		, \
			sched_url				TEXT 		, \
			sched_dj_user_id        INT         , \
			sid						SMALLINT	NOT NULL, \
			sched_public			BOOLEAN		DEFAULT TRUE, \
			sched_timed				BOOLEAN		DEFAULT TRUE, \
			sched_in_progress		BOOLEAN		DEFAULT FALSE, \
			sched_used				BOOLEAN		DEFAULT FALSE, \
			sched_use_crossfade		BOOLEAN		DEFAULT TRUE, \
			sched_use_tag_suffix	BOOLEAN		DEFAULT TRUE, \
			sched_creator_user_id	INT \
		)")
    c.create_idx("r4_schedule", "sched_used")
    c.create_idx("r4_schedule", "sched_in_progress")
    c.create_idx("r4_schedule", "sched_public")
    c.create_idx("r4_schedule", "sched_start_actual")
    c.create_delete_fk("r4_schedule",
                       "phpbb_users",
                       "sched_dj_user_id",
                       foreign_key="user_id",
                       create_idx=False)

    c.update(" \
		CREATE TABLE r4_elections ( \
			elec_id					INTEGER		PRIMARY KEY, \
			elec_used				BOOLEAN		DEFAULT FALSE, \
			elec_in_progress		BOOLEAN		DEFAULT FALSE, \
			elec_start_actual		INTEGER		, \
			elec_type				TEXT		, \
			elec_priority			BOOLEAN		DEFAULT FALSE, \
			sid						SMALLINT	NOT NULL, \
			sched_id 				INT 		 \
		)")
    if c.is_postgres:
        c.update(
            "ALTER TABLE r4_elections ALTER COLUMN elec_id SET DEFAULT nextval('r4_schedule_sched_id_seq')"
        )
    c.create_idx("r4_elections", "elec_id")
    c.create_idx("r4_elections", "elec_used")
    c.create_idx("r4_elections", "sid")
    c.create_delete_fk("r4_elections", "r4_schedule", "sched_id")

    c.update(" \
		CREATE TABLE r4_election_entries ( \
			entry_id				SERIAL		PRIMARY KEY, \
			song_id					INTEGER		NOT NULL, \
			elec_id					INTEGER		NOT NULL, \
			entry_type				SMALLINT	DEFAULT 2, \
			entry_position			SMALLINT	, \
			entry_votes				SMALLINT	DEFAULT 0 \
		)")
    # c.create_idx("r4_election_entries", "song_id")	# handled by create_delete_fk
    # c.create_idx("r4_election_entries", "elec_id")
    c.create_delete_fk("r4_election_entries", "r4_songs", "song_id")
    c.create_delete_fk("r4_election_entries", "r4_elections", "elec_id")

    c.update(" \
		CREATE TABLE r4_one_ups ( \
			one_up_id				INTEGER		NOT NULL, \
			sched_id				INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			one_up_order			SMALLINT	, \
			one_up_used				BOOLEAN		DEFAULT FALSE, \
			one_up_queued			BOOLEAN		DEFAULT FALSE, \
			one_up_sid				SMALLINT	NOT NULL \
		)")
    if c.is_postgres:
        c.update(
            "ALTER TABLE r4_one_ups ALTER COLUMN one_up_id SET DEFAULT nextval('r4_schedule_sched_id_seq')"
        )
    # c.create_idx("r4_one_ups", "sched_id")		# handled by create_delete_fk
    # c.create_idx("r4_one_ups", "song_id")
    c.create_delete_fk("r4_one_ups", "r4_schedule", "sched_id")
    c.create_delete_fk("r4_one_ups", "r4_songs", "song_id")

    c.update(" \
		CREATE TABLE r4_listeners ( \
			listener_id				SERIAL		PRIMARY KEY, \
			sid						SMALLINT	NOT NULL, \
			listener_ip				TEXT		, \
			listener_relay			TEXT		, \
			listener_agent			TEXT		, \
			listener_icecast_id		INTEGER		NOT NULL, \
			listener_lock			BOOLEAN		DEFAULT FALSE, \
			listener_lock_sid		SMALLINT	, \
			listener_lock_counter	SMALLINT	DEFAULT 0, \
			listener_purge			BOOLEAN		DEFAULT FALSE, \
			listener_voted_entry	INTEGER		, \
			user_id					INTEGER		DEFAULT 1 \
		)")
    c.create_idx("r4_listeners", "sid")
    # c.create_idx("r4_listeners", "user_id")		# handled by create_delete_fk
    c.create_delete_fk("r4_listeners", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_listener_counts ( \
			lc_time					INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			lc_guests				SMALLINT	, \
			lc_users				SMALLINT	, \
			lc_users_active				SMALLINT	, \
			lc_guests_active			SMALLINT	\
		)")
    c.create_idx("r4_listener_counts", "lc_time")
    c.create_idx("r4_listener_counts", "sid")

    c.update(" \
		CREATE TABLE r4_donations ( \
			donation_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		, \
			donation_amount			REAL		, \
			donation_message		TEXT		, \
			donation_private		BOOLEAN		DEFAULT TRUE \
		)")

    c.update(" \
		CREATE TABLE r4_request_store ( \
			reqstor_id				SERIAL		PRIMARY KEY, \
			reqstor_order			SMALLINT	DEFAULT 32766, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			sid 					SMALLINT	NOT NULL \
		)")
    # c.create_idx("r4_request_store", "user_id")		# handled by create_delete_fk
    # c.create_idx("r4_request_store", "song_id")
    c.create_delete_fk("r4_request_store", "phpbb_users", "user_id")
    c.create_delete_fk("r4_request_store", "r4_songs", "song_id")

    c.update(" \
		CREATE TABLE r4_request_line ( \
			user_id					INTEGER		NOT NULL, \
			sid					SMALLINT	NOT NULL, \
			line_wait_start				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			line_expiry_tune_in			INTEGER		, \
			line_expiry_election			INTEGER , \
			line_has_had_valid              BOOLEAN DEFAULT FALSE \
		)")
    # c.create_idx("r4_request_line", "user_id")		# handled by create_delete_fk
    c.create_idx("r4_request_line", "sid")
    c.create_idx("r4_request_line", "line_wait_start")
    c.create_delete_fk("r4_request_line", "phpbb_users", "user_id")
    if c.is_postgres:
        c.update(
            "ALTER TABLE r4_request_line ADD CONSTRAINT unique_user_id UNIQUE (user_id)"
        )

    c.update(" \
		CREATE TABLE r4_request_history ( \
			request_id				SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			request_fulfilled_at			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			request_wait_time			INTEGER		, \
			request_line_size			INTEGER		, \
			request_at_count			INTEGER		, \
			sid                         SMALLINT    \
		)")
    # c.create_idx("r4_request_history", "user_id")		# handled by create_delete_fk
    # c.create_idx("r4_request_history", "song_id")
    c.create_delete_fk("r4_request_history", "r4_songs", "song_id")
    c.create_delete_fk("r4_request_history", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_vote_history ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank			INTEGER		, \
			vote_at_count			INTEGER		, \
			entry_id				INTEGER		, \
			sid  					SMALLINT \
		)")
    # c.create_idx("r4_vote_history", "user_id")		# handled by create_delete_fk
    # c.create_idx("r4_vote_history", "song_id")
    # c.create_idx("r4_vote_history", "entry_id")
    c.create_idx("r4_vote_history", "sid")
    c.create_null_fk("r4_vote_history", "r4_election_entries", "entry_id")
    c.create_null_fk("r4_vote_history", "r4_elections", "elec_id")
    c.create_delete_fk("r4_vote_history", "r4_songs", "song_id")
    c.create_delete_fk("r4_vote_history", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_vote_history_archived ( \
			vote_id					SERIAL		PRIMARY KEY, \
			vote_time				INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			elec_id					INTEGER		, \
			user_id					INTEGER		NOT NULL, \
			song_id					INTEGER		NOT NULL, \
			vote_at_rank				INTEGER		, \
			vote_at_count				INTEGER		, \
			entry_id				INTEGER		\
		)")
    c.create_null_fk("r4_vote_history_archived", "r4_election_entries",
                     "entry_id")
    c.create_null_fk("r4_vote_history_archived", "r4_elections", "elec_id")
    c.create_null_fk("r4_vote_history_archived", "r4_songs", "song_id")
    c.create_delete_fk("r4_vote_history_archived", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_api_keys ( \
			api_id					SERIAL		PRIMARY KEY, \
			user_id					INTEGER		NOT NULL, \
			api_ip					TEXT		, \
			api_key					VARCHAR(10) , \
			api_expiry				INTEGER		\
		)")
    # c.create_idx("r4_api_keys", "user_id")		# handled by create_delete_fk
    c.create_idx("r4_api_keys", "api_ip")
    c.create_delete_fk("r4_api_keys", "phpbb_users", "user_id")

    c.update(" \
		CREATE TABLE r4_song_history ( \
			songhist_id				SERIAL		PRIMARY KEY, \
			songhist_time			INTEGER		DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), \
			sid						SMALLINT	NOT NULL, \
			song_id					INTEGER		NOT NULL \
		)")
    c.create_idx("r4_song_history", "sid")
    c.create_delete_fk("r4_song_history", "r4_songs", "song_id")

    try:
        c.update(" \
			CREATE TABLE r4_pref_storage ( \
				user_id 				INT 		, \
				ip_address 				TEXT 		, \
				prefs 					JSONB \
			)")
        c.create_delete_fk("r4_pref_storage", "phpbb_users", "user_id")
    except:
        log.critical(
            "init_db",
            "Could not create r4_pref_storage - feature requires Pg 9.4 or higher.  See README."
        )

    if config.get("standalone_mode"):
        _fill_test_tables()

    c.commit()