135 lines
5.8 KiB
Python
Executable File
135 lines
5.8 KiB
Python
Executable File
#!/usr/bin/python3
|
|
|
|
# CREATE MUSIC DATABASE AND EPISODE TABLES --------------------------
|
|
|
|
import sys, os, datetime, fnmatch, glob, random, time, pathlib, re
|
|
from datetime import timedelta
|
|
from os.path import join
|
|
from tinytag import TinyTag
|
|
from random import shuffle
|
|
import sqlite3, json
|
|
import uuid
|
|
#import pypika # sql query builder
|
|
from pypika import Query, Table, Field, Column
|
|
|
|
music_library_path = "/home/rob/uho/music/"
|
|
conn = sqlite3.connect("database/uho_music.db")
|
|
|
|
#TODO fix getting bandcamp urls into db
|
|
labelnames = [("sploh", "https://sploh.bandcamp.com"), ("terraformer"), ("pharamafabric") ]
|
|
|
|
# todo find album with no album name
|
|
mus_lib = Query \
|
|
.create_table("MUSIC_LIBRARY") \
|
|
.columns(
|
|
Column("id", "VARCHAR(32)", nullable=False),
|
|
Column("label", "VARCHAR(100)", nullable=True),
|
|
Column("album", "VARCHAR(100)", nullable=True),
|
|
Column("track", "VARCHAR(200)", nullable=True),
|
|
Column("artist", "VARCHAR(120)", nullable=True),
|
|
Column("genre", "VARCHAR(120)", nullable=True),
|
|
Column("trackdur", "FLOAT", nullable=True),
|
|
Column("year", "INT", nullable=True),
|
|
Column("url", "VARCHAR(120)", nullable=True),
|
|
Column("path", "VARCHAR(120)", nullable=False),
|
|
Column("lastplay", "DATETINE", nullable=True),
|
|
Column("comment", "VARCHAR(120)", nullable=True))\
|
|
.unique("id") \
|
|
.primary_key("id")
|
|
|
|
def database_create(conn):
|
|
# the MUSIC LIBRARY TABLE
|
|
#TODO UNIQUE constraint prevents duplicats but what if tracks are in another locaton?fixme
|
|
conn.execute(str(mus_lib))
|
|
print('''MUSIC LIBRARY Table created successfully''');
|
|
|
|
print('''
|
|
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⣀⣠⠤⠤⣄⣀⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
|
|
⠀⠀⠀⠀⠀⠀⠀⢀⣤⠾⠛⠉⠀⠀⠀⠀⠀⠀⠉⠛⠷⣤⡀⠀⠀⠀⠀⠀⠀⠀
|
|
⠀⠀⠀⠀⠀⠀⠐⠟⠁⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠈⠻⠂⠀⠀⠀⠀⠀⠀
|
|
⠀⠀⠀⠀⣰⡟⠁⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠈⢻⣆⠀⠀⠀⠀
|
|
⠀⠀⠀⣰⠏⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠹⣆⠀⠀⠀
|
|
⠀⠀⢰⡏⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢹⡆⠀⠀
|
|
⠀⠀⣾⠀⠀⠀⠀⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⠀⠀⠀⠀⣷⠀⠀
|
|
⠀⢰⡇⠀⣰⣶⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣶⣆⠀⢸⡆⠀
|
|
⠀⢈⠁⢠⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⡄⠈⡁⠀
|
|
⠀⢸⡇⢸⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⡇⢸⡇⠀
|
|
⠀⠈⠁⠸⣿⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⣿⠇⠈⠁⠀
|
|
⠀⠀⠀⠀⠻⣿⠀⣿⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⠀⣿⠟⠀⠀⠀⠀
|
|
⠀⠀⠀⠀⠀⠀⠀⠛⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠛⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
|
|
''');
|
|
#TODO meta idea about uho and soundwave in the above ascii
|
|
def database_create_episodes_table(conn):
|
|
# the show database
|
|
|
|
q = Query \
|
|
.create_table("EPISODES") \
|
|
.columns(
|
|
Column("id", "INT", nullable=True),
|
|
Column("episode", "INT", nullable=True),
|
|
Column("track_number", "INT", nullable=True),
|
|
Column("date", "VARCHAR(120)", nullable=True),
|
|
Column("album", "VARCHAR(200)", nullable=True),
|
|
Column("track", "VARCHAR(120)", nullable=True),
|
|
Column("artist", "VARCHAR(120)", nullable=True),
|
|
Column("trackdur", "REAL", nullable=True),
|
|
Column("genre", "VARCHAR(120)", nullable=True),
|
|
Column("year", 'DATETIME', nullable=True),
|
|
Column("path", "VARCHAR(120)", nullable=False),
|
|
Column("label", "VARCHAR(120)", nullable=False),
|
|
Column("comment", "VARCHAR(120)", nullable=False),
|
|
Column("episode_total_dur", "VARCHAR(120)", nullable=False),
|
|
Column("episode_artists", "VARCHAR(120)", nullable=False),
|
|
Column("something_else", "VARCHAR(120)", nullable=False))\
|
|
.unique("id") \
|
|
.primary_key("id")
|
|
|
|
conn.execute(str(q))
|
|
print("EPISODES Table created successfully");
|
|
|
|
def mk_db_entry(conn):
|
|
print("ADDING TRACKS TO DATABASE > > > one moment please! ");
|
|
label_url = "https://fixme.bandcamp.com/"
|
|
|
|
for subdir, dirs, files in os.walk(music_library_path):
|
|
|
|
for file in files:
|
|
for m in [".flac", ".FLAC", ".mp3"]: # get audio files
|
|
if m in file:
|
|
filepath = pathlib.Path(subdir + "/" +file)
|
|
label = filepath.parts[5]
|
|
# get LABEL etc name from path
|
|
# TODO THIS will break when path changes - make relative to cwd
|
|
track = TinyTag.get(os.path.join(subdir, file))# get metadata from file
|
|
cursor = conn.cursor()
|
|
mus_lib = Table('MUSIC_LIBRARY')
|
|
id = str(uuid.uuid4())
|
|
q = mus_lib.insert(id, label, track.album, track.title, track.artist, \
|
|
track.genre, track.duration, track.year, \
|
|
label_url, str(filepath), 1970-1-1, track.comment)
|
|
cursor.execute(str(q));
|
|
conn.commit()
|
|
|
|
def count_tracks(conn):
|
|
query = f"SELECT COUNT(*) FROM MUSIC_LIBRARY"
|
|
cursor = conn.cursor()
|
|
cursor.execute(query)
|
|
result = cursor.fetchone()
|
|
row_count = result[0]
|
|
print(f'''\n WHOOP! THE MUSIC LIBRARY CONTAINS {row_count} TRACKS!!
|
|
--------------------------------------------''')
|
|
|
|
def main():
|
|
|
|
database_create(conn)
|
|
database_create_episodes_table(conn)
|
|
mk_db_entry(conn)
|
|
count_tracks(conn)
|
|
|
|
if __name__ == "__main__":
|
|
main()
|
|
|
|
|
|
|
|
|