403 lines
7.4 KiB
Python
403 lines
7.4 KiB
Python
import flask
|
|
import sqlite3
|
|
import csv
|
|
import datetime
|
|
|
|
app = flask.Flask('songbook')
|
|
|
|
# BLANK PAGE
|
|
|
|
# @app.route('/')
|
|
# def page():
|
|
|
|
# db = openbook()
|
|
|
|
# return flask.render_template('.jinja',
|
|
# )
|
|
|
|
# HOMEPAGE
|
|
|
|
def openbook():
|
|
return sqlite3.connect("file:songbook.sqlite?mode=ro", uri=True)
|
|
|
|
@app.route('/')
|
|
def homepage():
|
|
|
|
|
|
|
|
return flask.render_template('home.jinja')
|
|
|
|
# SEARCH RESULTS
|
|
|
|
@app.route('/search')
|
|
def searchpage():
|
|
|
|
db = openbook()
|
|
|
|
searchargs = flask.request.args['q']
|
|
|
|
if searchargs:
|
|
searchresult = db.execute('''
|
|
select
|
|
'song', id, name, NULL, instr(lower(name),lower(?))
|
|
from
|
|
song
|
|
where
|
|
name
|
|
like
|
|
'%' || ? || '%'
|
|
union all
|
|
select
|
|
'album', id, name, code, instr(lower(name),lower(?))
|
|
from
|
|
album
|
|
where
|
|
name
|
|
like
|
|
'%' || ? || '%'
|
|
union all
|
|
select
|
|
'motif', id, name, NULL, instr(lower(name),lower(?))
|
|
from
|
|
motif
|
|
where
|
|
name
|
|
like
|
|
'%' || ? || '%'
|
|
order by
|
|
5
|
|
limit
|
|
10
|
|
''',
|
|
(searchargs,)*6
|
|
).fetchall()
|
|
|
|
else:
|
|
searchresult = []
|
|
|
|
return flask.render_template('searchresults.jinja',
|
|
searchresult=searchresult
|
|
)
|
|
|
|
@app.route('/suggestsearch')
|
|
def suggestsearchpage():
|
|
|
|
db = openbook()
|
|
|
|
searchargs = flask.request.args['q']
|
|
|
|
if searchargs:
|
|
searchresult = db.execute('''
|
|
select
|
|
'song', id, name, instr(lower(name),lower(?))
|
|
from
|
|
song
|
|
where
|
|
name
|
|
like
|
|
'%' || ? || '%'
|
|
union all
|
|
select
|
|
'motif', id, name, instr(lower(name),lower(?))
|
|
from
|
|
motif
|
|
where
|
|
name
|
|
like
|
|
'%' || ? || '%'
|
|
order by
|
|
4
|
|
limit
|
|
10
|
|
''',
|
|
(searchargs,)*4
|
|
).fetchall()
|
|
|
|
else:
|
|
searchresult = []
|
|
|
|
return flask.render_template('suggestsearchresults.jinja',
|
|
searchresult=searchresult
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
# SONG INDEX
|
|
|
|
@app.route('/song')
|
|
def song_redirect():
|
|
return flask.redirect("/index", code=308)
|
|
|
|
@app.route('/index')
|
|
def songindex():
|
|
|
|
db = openbook()
|
|
|
|
album_info = db.execute('''
|
|
select
|
|
id, name, date, code
|
|
from
|
|
album
|
|
order by
|
|
id
|
|
''').fetchall()
|
|
|
|
song_info = db.execute('''
|
|
select
|
|
song_album.song_id, album_id, (select code from album where id = (select min(album_id) from song_album where song_album.song_id=song.id)) as earliest_album, track, song.name, song.name_jp, count(clip.song_id)
|
|
from
|
|
song_album
|
|
join
|
|
song
|
|
on
|
|
song_album.song_id=song.id
|
|
left join
|
|
clip on clip.song_id=song.id
|
|
group by
|
|
song_album.song_id, album_id, track, song.name, song.name_jp
|
|
order by
|
|
album_id,
|
|
track
|
|
''').fetchall()
|
|
|
|
return flask.render_template('songindex.jinja',
|
|
album_info=album_info,
|
|
song_info=song_info
|
|
)
|
|
|
|
# MOTIF INDEX
|
|
|
|
@app.route('/motif')
|
|
def motifindex():
|
|
|
|
db = openbook()
|
|
|
|
category_info = db.execute('''
|
|
select
|
|
category.id, category.name
|
|
from
|
|
category
|
|
join
|
|
motif
|
|
on
|
|
category.id=motif.category
|
|
group by
|
|
category.id, category.name
|
|
order by
|
|
category.id
|
|
''').fetchall()
|
|
|
|
category_info.append(
|
|
('', 'Misc.')
|
|
)
|
|
|
|
motif_info = db.execute('''
|
|
select
|
|
id, name, category, count(clip.motif_id)
|
|
from
|
|
motif
|
|
left join
|
|
clip
|
|
on
|
|
clip.motif_id=motif.id
|
|
group by
|
|
id, name, category
|
|
order by
|
|
min(clip.song_id)
|
|
''').fetchall()
|
|
|
|
return flask.render_template('motifindex.jinja',
|
|
category_info = category_info,
|
|
motif_info = motif_info
|
|
)
|
|
|
|
# MOTIF PAGES
|
|
|
|
@app.route('/motif/<int:id>')
|
|
def motifpage(id):
|
|
|
|
db = openbook()
|
|
|
|
# query motif
|
|
|
|
name, = db.execute('''
|
|
select
|
|
name
|
|
from
|
|
motif
|
|
where
|
|
id = ?
|
|
''',
|
|
(id,)
|
|
).fetchone()
|
|
|
|
# query clips
|
|
|
|
clip_info = db.execute('''
|
|
select
|
|
song_id, motif_id, song.name, feature, song.location
|
|
from
|
|
clip
|
|
join
|
|
song
|
|
on
|
|
clip.song_id=song.id
|
|
where
|
|
motif_id = ?
|
|
order by
|
|
feature desc,
|
|
song_id
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
album_info = db.execute('''
|
|
select
|
|
clip.song_id, album.code
|
|
from
|
|
clip
|
|
join
|
|
song_album
|
|
on
|
|
clip.song_id=song_album.song_id
|
|
join
|
|
album
|
|
on
|
|
song_album.album_id=album.id
|
|
where
|
|
clip.motif_id = ?
|
|
order by
|
|
song_album.album_id
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
return flask.render_template('motif.jinja',
|
|
name=name,
|
|
clip_info=clip_info,
|
|
album_info=album_info
|
|
)
|
|
|
|
# SONG PAGES
|
|
|
|
@app.route('/song/<int:id>')
|
|
def songpage(id):
|
|
|
|
db = openbook()
|
|
|
|
# query song name
|
|
|
|
song_info = db.execute('''
|
|
select
|
|
name, name_jp, location
|
|
from
|
|
song
|
|
where
|
|
id = ?
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
# query album info
|
|
|
|
album_info = db.execute('''
|
|
select
|
|
album_id, track, album.name, album.code
|
|
from
|
|
song_album
|
|
join
|
|
album
|
|
on
|
|
song_album.album_id=album.id
|
|
where
|
|
song_id = ?
|
|
order by
|
|
album_id
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
# query artist info
|
|
|
|
artist_info = db.execute('''
|
|
select
|
|
artist.name, artist.name_rm, credit.name
|
|
from
|
|
song_artist
|
|
join
|
|
artist
|
|
on
|
|
song_artist.artist_id=artist.id
|
|
join
|
|
credit
|
|
on
|
|
song_artist.credit_id=credit.id
|
|
where
|
|
song_id = ?
|
|
order by
|
|
credit_id,
|
|
artist.name_rm
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
# query clip info
|
|
|
|
clip_info = db.execute('''
|
|
select
|
|
song_id,
|
|
motif_id,
|
|
motif.name,
|
|
(select song_id from clip where motif_id = motif.id and feature == 1) as feature_clip,
|
|
(select name from song where (select song_id from clip where motif_id = motif.id and feature == 1) = song.id) as feature_name
|
|
from
|
|
clip
|
|
join
|
|
motif
|
|
on
|
|
clip.motif_id=motif.id
|
|
where
|
|
song_id = ?
|
|
order by
|
|
start_ms
|
|
''',
|
|
(id,)
|
|
).fetchall()
|
|
|
|
return flask.render_template('song.jinja',
|
|
song_info=song_info,
|
|
id=id,
|
|
album_info=album_info,
|
|
clip_info=clip_info,
|
|
artist_info=artist_info
|
|
)
|
|
|
|
@app.route('/suggest')
|
|
def suggestpage():
|
|
|
|
return flask.render_template('suggest.jinja',
|
|
)
|
|
|
|
@app.route('/sent', methods=['POST'])
|
|
def sentpage():
|
|
|
|
with open ('suggestions.csv', 'a', newline='') as suggestlog:
|
|
logwriter = csv.writer(suggestlog)
|
|
logwriter.writerow([
|
|
datetime.datetime.now().isoformat(),
|
|
flask.request.form['connection-0-desc'][:4],
|
|
flask.request.form['connection-0-id'][:4],
|
|
flask.request.form['connection-1-desc'][:4],
|
|
flask.request.form['connection-1-id'][:4],
|
|
flask.request.form['suggest-description'][:10_000],
|
|
])
|
|
return flask.render_template('sent.jinja',
|
|
)
|
|
|
|
@app.route('/credits')
|
|
def creditpage():
|
|
|
|
return flask.render_template('credits.jinja',
|
|
) |