数据库系统设计介绍

系统需求分析及定义

在这个个人音乐数据库系统中，最主要的功能是

批量解析音乐文件，获取音乐标签信息
根据路径播放音乐
编辑音乐文件信息
编辑专辑信息
编辑艺术家信息
实现歌单、音乐、专辑自定义排序
由于无法解析出多位艺术家，部分信息需要手动添加
……

所有的需求可以概括为对各个实体的增、删、查、改功能，具体的实现在下文介绍

数据需求分析及定义

由于主要面向个人资料的管理，并且不提供云音乐的播放，因此采用本地化更容易的sqlite作为数据库系统。

由于 sqlite中数据类型的特殊性，因此部分数据长度、类型等限制需要在客户端软件完成。

这里数据库内存在的实体：

people
song
album
picture (未来可能会更新为file, 通过file_type进行区分，主要保存音乐、图片文件的路径信息)
playlist
folder
genre

通过一下表组织关系模型

credit: 与people表相关，通过 credit_as 区分不同身份的艺术家
credit_with_album, credit_with_song: 艺术家与专辑、歌曲的关系组织
song_has_genre: 连结 song 与 genre 的表
playlist_has_song: 连结 song 与 playlist 的表

关系模型可以极大地降低数据冗余

数据模型设计

View

由于 credit 表的存在，使得查询歌手、作曲和填词人比较麻烦，因此创建view来加快操作。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


BEGIN TRANSACTION;

DROP VIEW IF EXISTS "artist";
CREATE VIEW IF NOT EXISTS "artist" AS
SELECT p.id            AS id,
       p.name          AS name,
       p.name_for_sort AS name_for_sort,
       p.picture_id    AS picture_id,
       c.id            AS credit_id
FROM people p
       INNER JOIN credit c ON p.id = c.people_id
WHERE credit_as = 'A';

DROP VIEW IF EXISTS "album_artist";
CREATE VIEW IF NOT EXISTS "album_artist" AS
SELECT credit.id            AS id,
       people.name          AS name,
       credit.name_for_sort AS name_for_sort,
       people.picture_id    AS picture_id
FROM people
       INNER JOIN credit ON people.id = credit.people_id
WHERE credit_as = 'M';

DROP VIEW IF EXISTS "composer";
CREATE VIEW IF NOT EXISTS "composer" AS
SELECT credit.id            AS id,
       people.name          AS name,
       credit.name_for_sort AS name_for_sort,
       people.picture_id    AS picture_id
FROM people
       INNER JOIN credit ON people.id = credit.people_id
WHERE credit_as = 'C';

COMMIT;

SQL介绍

Insert

添加新歌曲

1
2
3


INSERT OR IGNORE INTO song 
(name, file_path, picture_id, album_id, year, bpm, sampleRate, bitRate, mpeg_version, mpeg_layer, channels, comments, size, length, track_order) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

添加新图片

1
2
3


INSERT OR IGNORE INTO picture 
(path) 
VALUES (?)

添加新曲风

1
2
3
4
5
6
7
8
9


INSERT OR IGNORE INTO song_has_genre 
(song_id, genre_id, "order") 
VALUES (?, ?, ?)

INSERT INTO song_has_genre 
(song_id, genre_id, "order")
VALUES (?, ?, (SELECT count(song_id) cnt 
               FROM song_has_genre 
               WHERE song_id = ?))

添加一首歌（的多个）艺术家

1
2
3
4
5
6
7
8
9


INSERT OR IGNORE INTO credit_with_song 
(credit_id, song_id, "order") 
VALUES (?, ?, ?)

INSERT INTO credit_with_song 
(credit_id, song_id, "order")
VALUES (?, ?, (SELECT count(song_id) cnt 
               FROM credit_with_song 
               WHERE song_id = ?))

添加一份专辑（的多个）艺术家

1
2
3
4
5
6
7
8
9


INSERT OR IGNORE INTO credit_with_album 
(credit_id, album_id, "order") 
VALUES (?, ?, ?)

INSERT INTO credit_with_album 
(credit_id, album_id, "order")
VALUES (?, ?, (SELECT count(album_id) cnt 
               FROM credit_with_album 
               WHERE album_id = ?))

添加新的艺术家类型

1
2
3


INSERT OR IGNORE INTO credit 
(people_id, credit_as) 
VALUES (?, ?)

添加新的艺术家

1
2
3


INSERT INTO people 
(name, picture_id) 
VALUES (?, ?)

添加新的曲目风格

1
2
3


INSERT OR IGNORE INTO genre 
(name) 
VALUES (?)

添加新的专辑

1
2
3


INSERT OR IGNORE INTO album 
(name, picture_id, track_total_number) 
VALUES (?, ?, ?)

添加新的歌单

1
2
3


INSERT INTO playlist 
(name, folder_id) 
VALUES (?, 1)

向已有歌单中添加已有歌曲

1
2
3
4
5
6


INSERT INTO playlist_has_song 
(playlist_id, song_id, "order")
VALUES (?, ?, (SELECT count(playlist_id) cnt 
               FROM playlist_has_song 
               WHERE playlist_id = ?))
—— 这里可以使用trigger优化操作

为歌曲增加新的风格*

1

INSERT INTO song_has_genre (song_id, genre_id) VALUES (?, ?)

Update

对歌曲进行评分

1
2
3
4


UPDATE song 
    SET rating = 2, 
        date_modified = (datetime(CURRENT_TIMESTAMP, 'localtime')) 
WHERE id = 1

修改歌曲排序用的名字*

1
2
3
4


UPDATE song 
    SET name_for_sort = ?,
    date_modified = (datetime(CURRENT_TIMESTAMP, 'localtime')) 
WHERE id = ?

对专辑进行评分*

1

UPDATE album SET rating = ? WHERE id = ?

修改专辑排序用的名字*

1

UPDATE album SET name_for_sort = ? WHERE id = ?

修改艺术家排序用的名字*

1

UPDATE people SET name_for_sort = ? WHERE id = ?

DELETE

删除歌单

1

DELETE FROM playlist WHERE id = ?

从歌单中删除歌曲

1

DELETE FROM playlist_has_song WHERE song_id = ? AND playlist_id = ?

删除歌曲*

1

DELETE FROM song WHERE id = ?

同时执行删除文件

删除歌曲现有风格

1

DELETE FROM song_has_genre WHERE song_id = ?

SELETE

辅助插入歌曲查询

查询歌曲

1

SELECT id FROM song WHERE name = ?

查询艺术家credit_id

1
2
3
4
5
6
7
8


SELECT id FROM credit WHERE people_id = ? AND credit_as = ?

SELECT c.id 
FROM credit c 
WHERE credit_as = ? 
AND people_id = (SELECT p.id
                 FROM people p
                 WHERE name = ?)

查询艺术家

1

SELECT id FROM people WHERE name = ?

查询曲风编号

1

SELECT id FROM genre WHERE name = ?

查询专辑

1

SELECT id FROM album WHERE name = ?

详细信息查询

专辑

SELECT id, name, picture_id
FROM album
ORDER BY CASE
           WHEN name_for_sort IS NOT NULL
             THEN name_for_sort
           ELSE name END
LIMIT 5 offset ?

歌曲

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


SELECT s.id,
       s.name,
       s.length,
       CAST(((s.length / 60) || ':' || (s.length % 60)) AS TEXT) AS length_in_minute,
       a.name                                                    AS album_name,
       g.name                                                    AS genre_name,
       s.rating,
       s.file_path,
       s.picture_id
FROM song s
       JOIN (SELECT name, id FROM album) a ON a.id = s.album_id
       JOIN (SELECT song_id, genre_id, "order" AS genre_id FROM song_has_genre) sg ON s.id = song_id
       JOIN (SELECT name, id FROM genre) g ON g.id = sg.genre_id
ORDER BY CASE
           WHEN s.name_for_sort IS NOT NULL
             THEN s.name_for_sort
           ELSE s.name
           END
LIMIT 10 OFFSET ?;

目录管理

1
2
3
4
5
6
7
8
9


-- File.separator
SELECT artist.name AS artist_name,
       a.name AS album_name,
       s.name AS song_name,
       artist.name || '\\' || a.name || '\\' || s.name || '.mp3' AS path
FROM artist
  INNER JOIN credit_with_song
  INNER JOIN song s on credit_with_song.song_id = s.id
  INNER JOIN album a on s.album_id = a.id;

播放列表

1

SELECT id, name FROM playlist

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


WITH songInPlaylist AS (SELECT playlist_id, song_id, "order" order_no
                        FROM playlist_has_song
                        WHERE playlist_id = ?
                        ORDER BY order_no)
SELECT s.id,
       s.name,
       s.length,
       CAST(((s.length / 60) || ':' || (s.length % 60)) AS TEXT) AS length_in_minute,
       a.name                                                    AS album_name,
       g.name                                                    AS genre_name,
       s.rating,
       s.file_path,
       s.picture_id
FROM songInPlaylist sp
       INNER JOIN song s ON sp.song_id = s.id
       INNER JOIN (SELECT name, id FROM album) a ON a.id = s.album_id
       INNER JOIN (SELECT sg.song_id, genre_id, "order" AS genre_id FROM song_has_genre sg) sg
            ON s.id = sg.song_id
       INNER JOIN (SELECT name, id FROM genre) g ON g.id = sg.genre_id
ORDER BY sp.order_no
LIMIT 10 OFFSET ?;

自定义快速生成歌单

评分范围
添加日期范围
修改日期范围
歌曲风格
歌曲时长

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


SELECT s.id,
       s.name,
       s.length,
       CAST(((s.length / 60) || ':' || (s.length % 60)) AS TEXT) AS length_in_minute,
       a.name                                                    AS album_name,
       g.name                                                    AS genre_name,
       s.rating,
       s.file_path,
       s.picture_id
FROM song s
        INNER JOIN (SELECT name, id FROM album) a ON a.id = s.album_id
        INNER JOIN (SELECT song_id, genre_id, "order" AS genre_id FROM song_has_genre) sg ON s.id = song_id
        INNER JOIN (SELECT name, id FROM genre) g ON g.id = sg.genre_id
        WHERE rating BETWEEN 0 AND 1
          AND date_added BETWEEN '2019-05-28 13:52:43' AND '2019-05-28 13:52:43'
          AND date_modified BETWEEN '2019-05-28 13:52:43' AND '2019-05-28 13:52:43'
          AND genre_name IN ('Blues')
          AND length BETWEEN 200 AND 210
ORDER BY CASE
           WHEN s.name_for_sort IS NOT NULL
             THEN s.name_for_sort
           ELSE s.name
           END;

艺术家

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


SELECT id, name
FROM artist a
ORDER BY CASE
           WHEN a.name_for_sort IS NOT NULL
             THEN a.name_for_sort
           ELSE a.name
           END
LIMIT 5 OFFSET ?;

SELECT id, name
FROM composer a
ORDER BY CASE
           WHEN a.name_for_sort IS NOT NULL
             THEN a.name_for_sort
           ELSE a.name
           END
LIMIT 5 OFFSET ?;

播放路径

1

SELECT file_path FROM song WHERE id = 56;

查询某个艺术家的歌曲

1
2
3
4
5
6


WITH singer AS (SELECT * FROM artist WHERE id = ?)
SELECT song_id, s.name AS song_name
FROM singer
       INNER JOIN credit_with_song ON singer.credit_id = credit_with_song.credit_id
       INNER JOIN song s on credit_with_song.song_id = s.id
LIMIT 10 OFFSET ?

查询某个专辑的歌曲

1

SELECT id, name, file_path FROM song WHERE album_id = 2

重要模块

DB_Connector
连接数据库
设置是否自动提交
断开连接
DB_Reader
执行所有的查找命令
使用单独的连接，未来可以与数据库写入进行多线程操作
DB_Writer
执行所有的写入操作
使用单独的连接，未来可以与数据库读取进行多线程操作
Menu_*: 与用户进行交互的各个菜单
File_add、Parser_*：对音乐文件的批量解析

优化

批量读取优化

通过设置 batch_size，使得在批量读取时不必每次读取都向数据库提交更改，从而大大提交效率。

实际测试中，在批量添加87首歌曲的情况下，未使用 batch_size 耗时约 35s，使用后耗时仅 1.571秒

（仅进行了一次测试，但足以说明效率的提升）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


private void getFileName(String dirPath) {
    connector.setAutoCommit(false);   // 35 - 1.571
    File file = new File(dirPath);    //获取其file对象
    func(file);
    connector.setAutoCommit(true);
}
private void func(File file) {
    File[] fs = file.listFiles();
    int cnt = 0;
    for (File f : fs) {
      if (f.isDirectory()) {
        func(f);
      }
      if (f.isFile()) {
        String fileName = f.getName();
        String filePath = f.getPath();
        if (filePath.endsWith("mp3")) {
          System.out.println(fileName);
          parseMP3(filePath);
          cnt++;
        }
      }
      if (cnt == batch_size) {
        connector.commit();
        cnt = 0;
      }

    }
    if (cnt > 0) {
      connector.commit();
    }
  }

添加音乐时的优化

这里仅展示其中一份代码，原理相同

对于类似 song 这样存在外键约束的实体，需要先获取外键数据，这里主要有一下几种方案：

使用 INSERT OR IGNORE，之后执行 SELECT

首先执行SELECT，判断是否有结果，若无则执行INSERT，再执行SELECT

这里虽然方案一看起来操作少，实际测试中发现，在数据已经存在的情况下， INSERT OR IGNORE 的耗时较大。

并且考虑到艺术家信息大概率已经存在于数据库中，并且可能存在多次读取同一个资料库的情况，

因此采用方案二优化耗时。

（此处由于需要改动的代码较多，因此未做实际的测试比较，仅通过控制台比较了多次单个方案一和二的耗时）

搜索功能优化

用户在搜索时可能出现无法记住准确全名的情况，因此需要进行模糊搜素；为了模糊搜索的速度，使用全文索引来提升速度：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


CREATE VIRTUAL TABLE English_FTS USING fts5(id,name,table_type);
 
CREATE TRIGGER FST_En_song AFTER INSERT ON song
 BEGIN
 INSERT INTO English_FTS (id,name,table_type) VALUES (new.id,new.name,'S');
 END;
 
CREATE TRIGGER FST_En_album AFTER INSERT ON album
  BEGIN
  INSERT INTO English_FTS (id,name,table_type) VALUES (new.id,new.name,'A');
  END;
  
CREATE TRIGGER FST_En_credit AFTER INSERT ON people
    BEGIN
    INSERT INTO English_FTS (id,name,table_type) VALUES (new.id,new.name,'P');
    END;

虚表的创建是为了全文索引服务，与整体的表结构无关；

trigger是为了在每次insert的时候将元组的id以及用于搜索的name加入表English_FTS，以便搜索使用；

虚表中搜索：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


WITH sel_song AS
    (SELECT * FROM song
               LEFT JOIN (SELECT id
                     FROM English_FTS
                     WHERE (table_type = 'S')
                     AND (name match ?)
    )
)
SELECT * -- 这里的属性与前面选择歌曲相同，为了控制篇幅使用*代替
FROM sel_song
       left JOIN credit_song ON sel_song.id = credit_song.song_id
       left JOIN album ON sel_song.album_id = album.id
       left JOIN picture ON sel_song.picture_id = picture.id
where (credit_as = 'A');

但是由于FTS标准中缺乏中文分词器，而大部分中文分词器（如微信使用的mmicu，icu等）的导入在java环境下非常困难，中文的搜索只好使用

1

SELECT FROM song WHERE name LIKE ?

部分操作演示

搜索

播放列表

艺术家

播放演示

[vplayer url="https://image-hosting.zhangjc.tech/ghost/content/images/2019/05/1647886151.mp4" /]

音乐数据库系统设计及主要操作介绍