Q100617： Flix 6 的 MySQL 查询示例

Ivo

更新 : 2024年08月20日 16:38

创建 : 2021年05月05日 21:35

概括

本文适用于Flix管理员。它提供了一些示例，说明如何直接向 MySQL 进行查询以收集有关Flix的信息。

使用名称获取演出 ID

在此示例中，您可以使用节目名称获取项目的节目 ID。

mysql> SELECT
    `shows`.`show_id`,
    `shows`.`title`
FROM
    `shows`
WHERE
    `shows`.`title` LIKE '% ? %';
+---------+-----------------+
| show_id |           title |
+---------+-----------------+
|       2 | the little bird |
+---------+-----------------+

从名称中获取序列 ID

在此示例中，您可以使用序列名称获取序列 ID。

mysql> SELECT
    `sequence`.`id`,
    `sequence`.`description`
FROM
    `sequence`
WHERE
    `sequence`.`description` LIKE '% ? %';
+----+-------------+
| id | description |
+----+-------------+
|  2 |     a place |
+----+-------------+

查找面板资源的文件路径

在此示例中，您可以找到特定资产在存储设备上的文件路径。

mysql> SELECT
    `vPanel_asset_ref`.`panel_id` AS PanelID,
    `vPanel_asset_ref`.`vPanel_id` AS PanelRevision,
    `media_object`.`ref` AS Ref,
    CONCAT('<asset_dir>/', `media_object`.`id`, '_', `media_object`.`filename`) AS FilePath
FROM
    `media_object`
LEFT JOIN
    `asset` ON `asset`.`asset_id` = `media_object`.`asset_id`
LEFT JOIN
    `vPanel_asset_ref` ON `vPanel_asset_ref`.`asset_id` = `asset`.`asset_id`
WHERE
    `vPanel_asset_ref`.`show_id` = ? and
    `vPanel_asset_ref`.`sequence_id` = ? and
    `vPanel_asset_ref`.`panel_id` = ? ;
+---------+---------------+-----------+------------------------------+
| PanelID | PanelRevision |       Ref |                     FilePath |
+---------+---------------+-----------+------------------------------+
|       1 |             1 |   artwork | <asset_dir>/3_test.0491.jpeg |
|       1 |             1 | thumbnail | <asset_dir>/16_079043250.png |
|       1 |             1 |    scaled | <asset_dir>/20_019628328.png |
|       1 |             1 |   fullres | <asset_dir>/25_552125904.png |
+---------+---------------+-----------+------------------------------+

显示用户列表（不包括系统用户）

在此示例中，您可以获得可以使用Flix用户列表。如果您使用 LDAP 或 OAuth 身份验证，则有权访问Flix的人员列表可能不完整。在这种情况下，您将获得之前登录Flix的用户列表。

mysql> SELECT
    `user`.`id`,
    `user`.`username`,
    `user`.`is_admin`,
    `user`.`type`,
    `user`.`email`,
    `user`.`deleted`
FROM
    `user`
WHERE
    `user`.`is_system` = 0;
+----+----------+----------+------+------------------------+---------+
| id | username | is_admin | type |                  email | deleted |
+----+----------+----------+------+------------------------+---------+
|  1 |    admin |        1 | flix | flix -admin@foundry.com |       0 |
+----+----------+----------+------+------------------------+---------+

查找使用特定文件名的面板 ID

在此示例中，您可以找到特定文件的面板 ID。

mysql> SELECT
    `shows`.`title` AS ShowTitle,
    `sequence`.`description` AS SequenceTitle,
    `panel`.`panel_id` AS PanelID
FROM
    `media_object`
LEFT JOIN
    `asset` ON `asset`.`asset_id` = `media_object`.`asset_id`
LEFT JOIN
    `shows` ON `shows`.`show_id` = `asset`.`show_id`
LEFT JOIN
    `vPanel_asset_ref` ON `vPanel_asset_ref`.`asset_id` = `asset`.`asset_id`
LEFT JOIN
    `vPanel` ON `vPanel`.`show_id` = `vPanel_asset_ref`.`show_id` AND
    `vPanel`.`sequence_id` = `vPanel_asset_ref`.`sequence_id` AND
    `vPanel`.`panel_id` = `vPanel_asset_ref`.`panel_id`
LEFT JOIN
    `sequence` ON `sequence`.`id` = `vPanel`.`sequence_id` AND
    `sequence`.`id` = `vPanel`.`sequence_id`
LEFT JOIN
    `panel` ON `panel`.`panel_id` = `vPanel`.`panel_id`
WHERE
    `media_object`.`id` = SUBSTRING_INDEX(' ? ', '_', 1) AND
    `media_object`.`filename` = SUBSTRING_INDEX(' ? ', '_', -1)
GROUP BY
    ShowTitle, SequenceTitle, PanelID;
+------------+---------------+---------+
|  ShowTitle | SequenceTitle | PanelID |
+------------+---------------+---------+
|      show1 |     seq_qwery |       1 |
| other show |          asdf |       1 |
+------------+---------------+---------+

获取人类可读的服务器列表

在此示例中，您可以获得所有Flix服务器的列表。它也可以在Flix客户端的“管理控制台”->“服务器”下找到。

mysql> SELECT
    HEX(`server`.`server_id`) AS serverIdent,
    INET_NTOA(`server`.`host_ip`) AS IP,
    `server`.`port`,
    `server`.`rpc_port`,
    `server`.`running`,
    `server`.`start_date`,
    `server`.`hostname`
FROM
    `server`;
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+
|                      serverIdent |           IP | port | rpc_port | running |          start_date |        hostname |
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+
| F22A1072B6754BCDB78477EDCD81F8FD | 192.168.1.67 | 8080 |     9876 |       1 | 2021-02-01 13:18:29 | flx.foundry.com |
+----------------------------------+--------------+------+----------+---------+---------------------+-----------------+

查找面板 ID 的Flix 5 元数据

在此示例中，您可以找到Flix 6 为从Flix 5 迁移的面板保留哪些数据

mysql> SELECT
    `vpanel`.`panel_id`,
    `vpanel`.`data`
FROM
    `vpanel`
WHERE
    `show_id` = ? AND
    `sequence_id` = ? AND
    `vpanel`.`panel_id` = ? ;
+----------+----------------------------------------------------------+
| panel_id |                                                     data |
+----------+----------------------------------------------------------+
|        1 |    {"flix5_panel_id": "31", "flix5_panel_revision": "1"} |
|        1 |    {"flix5_panel_id": "31", "flix5_panel_revision": "2"} |
|        1 |    {"flix5_panel_id": "31", "flix5_panel_revision": "3"} |
+----------+----------------------------------------------------------+

从Flix 5 元数据中查找面板修订

当想要查找Flix 5 面板的Flix 6 对应项时，此搜索非常有用

mysql> SELECT
    `vPanel`.`show_id`,
    `vPanel`.`sequence_id`,
    `vPanel`.`panel_id`,
    `vPanel`.`panel_revision`
FROM
    `vPanel`
JOIN
    `sequence` ON `sequence`.`id` = `vPanel`.`sequence_id`
WHERE
    `vPanel`.`data`->"$.flix5_panel_id" = " ? " AND    
`vPanel`.`data`->"$.flix5_panel_revision" = " ? " AND
`sequence`.`tracking_code` = " ? ";
+---------+-------------+----------+----------------+
| show_id | sequence_id | panel_id | panel_revision |
+---------+-------------+----------+----------------+
|        7|          97 |      236 |              1 |
+---------+-------------+----------+----------------+