-- phpMyAdmin SQL Dump
-- version 4.9.5
-- https://www.phpmyadmin.net/
--
-- Hôte : db.dmz.dhvlab.fo
-- Généré le : lun. 20 déc. 2021 à 16:11
-- Version du serveur :  5.5.65-MariaDB
-- Version de PHP : 7.3.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de données : `labuser_jdesiderio`
--

-- --------------------------------------------------------

--
-- Structure de la table `Queries`
--

CREATE TABLE `Queries` (
  `id_query` int(3) NOT NULL COMMENT 'ID of the query',
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'name of the query',
  `query` varchar(10000) COLLATE utf8_unicode_ci NOT NULL COMMENT 'whole query to copy-paste',
  `comment` varchar(300) COLLATE utf8_unicode_ci NOT NULL COMMENT 'short description of the query''s aim'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='contains a list of useful queries to treat the data + the queries used on the website.';

--
-- Déchargement des données de la table `Queries`
--

INSERT INTO `Queries` (`id_query`, `name`, `query`, `comment`) VALUES
(1, 'lines/page', 'SELECT page, COUNT(DISTINCT line) AS nr_lines FROM Subtokens WHERE id_ms=1\r\nGROUP BY page', 'Counts number of lines per page in ms X'),
(2, 'sentences/chapter', 'SELECT chapter, COUNT(DISTINCT sentence) AS nr_sentences FROM Subtokens WHERE id_ms=1\r\nGROUP BY chapter', 'Counts the number of sentences per chapter'),
(4, 'full tokens', 'SELECT \r\n	a.id_item,\r\n	a.id_ms,\r\n	a.chapter,\r\n	GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n	GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n	UNION\r\nSELECT \r\n 	b.id_item,\r\n 	b.id_ms,\r\n 	b.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token	\r\n	UNION\r\nSELECT \r\n 	c.id_item,\r\n 	c.id_ms,\r\n 	c.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n	UNION\r\nSELECT \r\n 	d.id_item,\r\n 	d.id_ms,\r\n 	d.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n	UNION\r\nSELECT \r\n 	e.id_item,\r\n 	e.id_ms,\r\n 	e.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n	UNION\r\nSELECT \r\n 	f.id_item,\r\n 	f.id_ms,\r\n 	f.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token\r\n\r\n/* add UNION of new mss when more are implemented */', 'Lists full tokens > keep unioning new mss for it to work.'),
(5, 'tokens/chapter', 'SELECT id_ms, chapter, COUNT(DISTINCT id_token) AS token_nr FROM Subtokens WHERE id_ms=1\r\nGROUP BY chapter\r\nUNION\r\nSELECT id_ms, chapter, COUNT(DISTINCT id_token) AS token_nr FROM Subtokens WHERE id_ms=2\r\nGROUP BY chapter\r\nUNION\r\nSELECT id_ms, chapter, COUNT(DISTINCT id_token) AS token_nr FROM Subtokens WHERE id_ms=3\r\nGROUP BY chapter', 'Counts number of words per chapter'),
(7, 'find lines=/=sentences', 'SELECT `a`.`id`,`a`.`COL 1`,`a`.`COL 2`,`a`.`COL 3`,`b`.`COL 4`,`b`.`COL 5`,`b`.`COL 6`,`b`.`COL 7`,`b`.`COL 8`, `a`.`COL 9`, `b`.`COL 9`, `b`.`id`\r\nFROM `XLine` a\r\nRIGHT JOIN `XSent` b\r\nON `a`.`COL 9`=`b`.`COL 9` AND `a`.`id`=`b`.`id`', 'see if I can make this better and actually correct automatically'),
(9, 'variant ms priority', 'SELECT \r\n	id_edition, \r\n	sentence, \r\n	ms1 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n	UNION \r\nSELECT \r\n	id_edition,\r\n	sentence, \r\n	ms2 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms3 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms53 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms20 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms21 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition\r\n\r\n/* add UNION of new mss when more are implemented */', 'selection of the ids of the best token from each manuscript'),
(10, 'select edited tokens', 'SELECT \r\n	sq2.id_edition, \r\n	sq1.id_ms,\r\n	sq1.id_item,\r\n	sq1.chapter, \r\n	sq2.sentence, \r\n	sq1.token,\r\n	sq1.exp_token, \r\n	sq2.problem \r\nFROM \r\n/* sq1 = full_tokens */\r\n(SELECT \r\n	a.id_item,\r\n	a.id_ms,\r\n	a.chapter,\r\n	GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n	GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n	UNION\r\nSELECT \r\n 	b.id_item,\r\n 	b.id_ms,\r\n 	b.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token	\r\n	UNION\r\nSELECT \r\n 	c.id_item,\r\n 	c.id_ms,\r\n 	c.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n	UNION\r\nSELECT \r\n 	d.id_item,\r\n 	d.id_ms,\r\n 	d.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n	UNION\r\nSELECT \r\n 	e.id_item,\r\n 	e.id_ms,\r\n 	e.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n	UNION\r\nSELECT \r\n 	f.id_item,\r\n 	f.id_ms,\r\n 	f.chapter,\r\n 	GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n 	GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1\r\n	RIGHT JOIN \r\n/* sq2 = priority_variants */\r\n(SELECT \r\n	id_edition, \r\n	sentence, \r\n	ms1 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n	UNION \r\nSELECT \r\n	id_edition,\r\n	sentence, \r\n	ms2 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms3 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms53 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms20 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\n	UNION\r\nSELECT \r\n	id_edition, \r\n	sentence, \r\n	ms21 AS id_item,\r\n	problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item)\r\nORDER BY sq2.id_edition', 'join the selection of ids from query 9 with the corresponding tokens'),
(11, 'Edition with corrections', 'SET group_concat_max_len = 10000;\r\n\r\nSELECT\r\n  chapter,\r\n  sentence,\r\n  GROUP_CONCAT(ed_with_conj ORDER BY id_edition SEPARATOR \' \') AS edition_sent\r\nFROM\r\n/* sq4 = link edited token with the editor\'s corrections when no variant is viable */\r\n(SELECT\r\n  sq3.id_edition,\r\n  sq3.id_ms,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\n  g.correction AS token_jd,\r\nCASE						\r\nWHEN g.correction IS NOT NULL THEN CONCAT(g.correction, \'<note>\', g.editor, \'</note>\')\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_ms, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.token,\r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nGROUP BY sq4.sentence', 'from subtokens to the edition organized by sentence, with the replacement of my own conjectures for the tokens that are not viable in the mss. '),
(13, 'ms variants', 'SELECT \r\n  sq10.id_edition,\r\n  sq10.ms1,\r\n  sq10.N,\r\n  sq10.ms2,\r\n  sq10.P,\r\n  sq10.ms3,\r\n  sq10.M,\r\n  sq10.ms53,\r\n  sq10.W,\r\n  sq10.ms20,\r\n  sq10.L,\r\n  sq10.ms21,\r\n  sq11.token21 AS Li\r\nFROM\r\n  /*sq10 = links full tokens from Ms1 Ms2 Ms3 Ms53 Ms20 with AlignmentBeda */\r\n  (SELECT \r\n    sq8.id_edition,\r\n    sq8.ms1,\r\n    sq8.N,\r\n    sq8.ms2,\r\n    sq8.P,\r\n    sq8.ms3,\r\n    sq8.M,\r\n    sq8.ms53,\r\n    sq8.W,\r\n    sq8.ms20,\r\n    sq9.token20 AS L,\r\n    sq8.ms21\r\n  FROM\r\n    /*sq8 = links full tokens from Ms1 Ms2 Ms3 Ms53 with AlignmentBeda */\r\n    (SELECT \r\n      sq6.id_edition,\r\n      sq6.ms1,\r\n      sq6.N,\r\n      sq6.ms2,\r\n      sq6.P,\r\n      sq6.ms3,\r\n      sq6.M,\r\n      sq6.ms53,\r\n      sq7.token53 AS W,\r\n      sq6.ms20,\r\n      sq6.ms21\r\n    FROM\r\n    /*sq6 = links full tokens from Ms1 Ms2 Ms3 with AlignmentBeda */\r\n      (SELECT \r\n        sq4.id_edition,\r\n        sq4.ms1,\r\n        sq4.N,\r\n        sq4.ms2,\r\n        sq4.P,\r\n        sq4.ms3,\r\n        sq5.token3 AS M,\r\n        sq4.ms53,\r\n        sq4.ms20,\r\n        sq4.ms21\r\n      FROM\r\n        /*sq4 = links full tokens from Ms1 and Ms2 with AlignmentBeda */\r\n        (SELECT \r\n          sq2.id_edition,\r\n          sq2.ms1,\r\n          sq2.N,\r\n          sq2.ms2,\r\n          sq3.token2 AS P,\r\n          sq2.ms3,\r\n          sq2.ms53,\r\n          sq2.ms20,\r\n          sq2.ms21\r\n        FROM\r\n          /*sq2 = links full tokens from Ms1 with AlignmentBeda */\r\n          (SELECT \r\n            d.id_edition,\r\n            d.ms1,\r\n            sq1.token1 AS N,\r\n            d.ms2,\r\n            d.ms3,\r\n            d.ms53,\r\n            d.ms20,\r\n            d.ms21\r\n          FROM AlignmentBeda d\r\n            LEFT JOIN \r\n            /*sq1 = full tokens from Ms 1*/\r\n            (SELECT \r\n              a.id_item, \r\n              a.id_token, \r\n              GROUP_CONCAT(a.subtoken ORDER BY a.id_item SEPARATOR \'\') AS beta_token1,\r\n              GROUP_CONCAT(a.symbols_abbr ORDER BY a.id_item SEPARATOR \'\') AS token1\r\n            FROM SubtokenMss a \r\n            WHERE a.id_ms=1\r\n            GROUP BY a.id_token) sq1\r\n          ON d.ms1=sq1.id_item) sq2\r\n              LEFT JOIN \r\n              /*sq3 = full tokens from Ms 2*/\r\n              (SELECT \r\n                b.id_item, \r\n                b.id_token, \r\n                GROUP_CONCAT(b.subtoken ORDER BY b.id_item SEPARATOR \'\') AS beta_token2,\r\n                GROUP_CONCAT(b.symbols_abbr ORDER BY b.id_item SEPARATOR \'\') AS token2\r\n              FROM SubtokenMss b \r\n              WHERE b.id_ms=2\r\n              GROUP BY b.id_token) sq3\r\n            ON sq2.ms2=sq3.id_item) sq4\r\n                LEFT JOIN\r\n                /*sq5 = full tokens from Ms 3*/\r\n                (SELECT \r\n                  c.id_item, \r\n                  c.id_token, \r\n                  GROUP_CONCAT(c.subtoken ORDER BY c.id_item SEPARATOR \'\') AS beta_token3,\r\n                  GROUP_CONCAT(c.symbols_abbr ORDER BY c.id_item SEPARATOR \'\') AS token3\r\n                FROM SubtokenMss c \r\n                WHERE c.id_ms=3\r\n                GROUP BY c.id_token) sq5\r\n              ON sq4.ms3=sq5.id_item) sq6\r\n                  LEFT JOIN\r\n                  /*sq7 = full tokens from Ms 53*/\r\n                  (SELECT \r\n                    e.id_item, \r\n                    e.id_token, \r\n                    GROUP_CONCAT(e.subtoken ORDER BY e.id_item SEPARATOR \'\') AS beta_token53,\r\n                    GROUP_CONCAT(e.symbols_abbr ORDER BY e.id_item SEPARATOR \'\') AS token53\r\n                    FROM SubtokenMss e \r\n                  WHERE e.id_ms=53\r\n                  GROUP BY e.id_token) sq7\r\n                ON sq6.ms53=sq7.id_item)sq8\r\n                    LEFT JOIN\r\n                     /*sq9 = full tokens from Ms 20*/\r\n                    (SELECT \r\n                      f.id_item, \r\n                      f.id_token, \r\n                      GROUP_CONCAT(f.subtoken ORDER BY f.id_item SEPARATOR \'\') AS beta_token20,\r\n                      GROUP_CONCAT(f.symbols_abbr ORDER BY f.id_item SEPARATOR \'\') AS token20\r\n                    FROM SubtokenMss f \r\n                    WHERE f.id_ms=20\r\n                    GROUP BY f.id_token) sq9\r\n                  ON sq8.ms20=sq9.id_item) sq10\r\n                      LEFT JOIN\r\n                      /*sq11 = full tokens from Ms 21*/\r\n                      (SELECT \r\n                        g.id_item, \r\n                        g.id_token, \r\n                        GROUP_CONCAT(g.subtoken ORDER BY g.id_item SEPARATOR \'\') AS beta_token21,\r\n                        GROUP_CONCAT(g.symbols_abbr ORDER BY g.id_item SEPARATOR \'\') AS token21\r\n                        FROM SubtokenMss g  \r\n                      WHERE g.id_ms=21\r\n                      GROUP BY g.id_token) sq11\r\n                    ON sq10.ms21=sq11.id_item\r\n', 'align the tokens from each ms on the same id_edition.'),
(14, 'Edition with app. crit. in note', 'SET group_concat_max_len = 50000;\r\n\r\nSELECT          \r\n sq27.id_edition,\r\n sq27.chapter,\r\n sq27.sentence,\r\n GROUP_CONCAT(sq27.token_appcrit ORDER BY sq27.id_edition SEPARATOR \' \') AS text\r\nFROM\r\n/*sq27 = coalesce the edited tokens with the apparatus criticus written as a <note> */\r\n(SELECT\r\n  sq26.id_edition,\r\n  sq26.chapter,\r\n  sq26.sentence,\r\n  CONCAT(sq26.ed_with_conj, \r\n    \'<note>\', \'N \',COALESCE(sq26.N,\'-\'), \r\n    \', P \',COALESCE(sq26.P,\'-\'),\r\n    \', M \',COALESCE(sq26.M,\'-\'),\r\n    \', W \',COALESCE(sq26.W,\'-\'),\r\n    \', L \',COALESCE(sq26.L,\'-\'),\r\n    \', Li \',COALESCE(sq26.Li,\'-\'),\r\n    \'</note>\') AS token_appcrit\r\nFROM\r\n/*sq26 = aligns the abbreviated tokens from each manuscript with the full edited tokens on id_edition */\r\n(SELECT \r\n  sq25.id_edition,\r\n  sq25.N,\r\n  sq25.P,\r\n  sq25.M,\r\n  sq25.W,\r\n  sq25.L,\r\n  sq25.Li,\r\n  sq4.chapter,\r\n  sq4.sentence,\r\n  sq4.ed_with_conj\r\nFROM\r\n(SELECT \r\n  sq10.id_edition,\r\n  sq10.ms1,\r\n  sq10.N,\r\n  sq10.ms2,\r\n  sq10.P,\r\n  sq10.ms3,\r\n  sq10.M,\r\n  sq10.ms53,\r\n  sq10.W,\r\n  sq10.ms20,\r\n  sq10.L,\r\n  sq10.ms21,\r\n  sq11.token21 AS Li\r\nFROM\r\n  /*sq10 = links full tokens from Ms1 Ms2 Ms3 Ms53 Ms20 with AlignmentBeda */\r\n  (SELECT \r\n    sq8.id_edition,\r\n    sq8.ms1,\r\n    sq8.N,\r\n    sq8.ms2,\r\n    sq8.P,\r\n    sq8.ms3,\r\n    sq8.M,\r\n    sq8.ms53,\r\n    sq8.W,\r\n    sq8.ms20,\r\n    sq9.token20 AS L,\r\n    sq8.ms21\r\n  FROM\r\n    /*sq8 = links full tokens from Ms1 Ms2 Ms3 Ms53 with AlignmentBeda */\r\n    (SELECT \r\n      sq6.id_edition,\r\n      sq6.ms1,\r\n      sq6.N,\r\n      sq6.ms2,\r\n      sq6.P,\r\n      sq6.ms3,\r\n      sq6.M,\r\n      sq6.ms53,\r\n      sq7.token53 AS W,\r\n      sq6.ms20,\r\n      sq6.ms21\r\n    FROM\r\n    /*sq6 = links full tokens from Ms1 Ms2 Ms3 with AlignmentBeda */\r\n      (SELECT \r\n        sq4.id_edition,\r\n        sq4.ms1,\r\n        sq4.N,\r\n        sq4.ms2,\r\n        sq4.P,\r\n        sq4.ms3,\r\n        sq5.token3 AS M,\r\n        sq4.ms53,\r\n        sq4.ms20,\r\n        sq4.ms21\r\n      FROM\r\n        /*sq4 = links full tokens from Ms1 and Ms2 with AlignmentBeda */\r\n        (SELECT \r\n          sq2.id_edition,\r\n          sq2.ms1,\r\n          sq2.N,\r\n          sq2.ms2,\r\n          sq3.token2 AS P,\r\n          sq2.ms3,\r\n          sq2.ms53,\r\n          sq2.ms20,\r\n          sq2.ms21\r\n        FROM\r\n          /*sq2 = links full tokens from Ms1 with AlignmentBeda */\r\n          (SELECT \r\n            d.id_edition,\r\n            d.ms1,\r\n            sq1.token1 AS N,\r\n            d.ms2,\r\n            d.ms3,\r\n            d.ms53,\r\n            d.ms20,\r\n            d.ms21\r\n          FROM AlignmentBeda d\r\n            LEFT JOIN \r\n            /*sq1 = full tokens from Ms 1*/\r\n            (SELECT \r\n              a.id_item, \r\n              a.id_token, \r\n              GROUP_CONCAT(a.subtoken ORDER BY a.id_item SEPARATOR \'\') AS beta_token1,\r\n              GROUP_CONCAT(a.symbols_abbr ORDER BY a.id_item SEPARATOR \'\') AS token1\r\n            FROM SubtokenMss a \r\n            WHERE a.id_ms=1\r\n            GROUP BY a.id_token) sq1\r\n          ON d.ms1=sq1.id_item) sq2\r\n              LEFT JOIN \r\n              /*sq3 = full tokens from Ms 2*/\r\n              (SELECT \r\n                b.id_item, \r\n                b.id_token, \r\n                GROUP_CONCAT(b.subtoken ORDER BY b.id_item SEPARATOR \'\') AS beta_token2,\r\n                GROUP_CONCAT(b.symbols_abbr ORDER BY b.id_item SEPARATOR \'\') AS token2\r\n              FROM SubtokenMss b \r\n              WHERE b.id_ms=2\r\n              GROUP BY b.id_token) sq3\r\n            ON sq2.ms2=sq3.id_item) sq4\r\n                LEFT JOIN\r\n                /*sq5 = full tokens from Ms 3*/\r\n                (SELECT \r\n                  c.id_item, \r\n                  c.id_token, \r\n                  GROUP_CONCAT(c.subtoken ORDER BY c.id_item SEPARATOR \'\') AS beta_token3,\r\n                  GROUP_CONCAT(c.symbols_abbr ORDER BY c.id_item SEPARATOR \'\') AS token3\r\n                FROM SubtokenMss c \r\n                WHERE c.id_ms=3\r\n                GROUP BY c.id_token) sq5\r\n              ON sq4.ms3=sq5.id_item) sq6\r\n                  LEFT JOIN\r\n                  /*sq7 = full tokens from Ms 53*/\r\n                  (SELECT \r\n                    e.id_item, \r\n                    e.id_token, \r\n                    GROUP_CONCAT(e.subtoken ORDER BY e.id_item SEPARATOR \'\') AS beta_token53,\r\n                    GROUP_CONCAT(e.symbols_abbr ORDER BY e.id_item SEPARATOR \'\') AS token53\r\n                    FROM SubtokenMss e \r\n                  WHERE e.id_ms=53\r\n                  GROUP BY e.id_token) sq7\r\n                ON sq6.ms53=sq7.id_item)sq8\r\n                    LEFT JOIN\r\n                     /*sq9 = full tokens from Ms 20*/\r\n                    (SELECT \r\n                      f.id_item, \r\n                      f.id_token, \r\n                      GROUP_CONCAT(f.subtoken ORDER BY f.id_item SEPARATOR \'\') AS beta_token20,\r\n                      GROUP_CONCAT(f.symbols_abbr ORDER BY f.id_item SEPARATOR \'\') AS token20\r\n                    FROM SubtokenMss f \r\n                    WHERE f.id_ms=20\r\n                    GROUP BY f.id_token) sq9\r\n                  ON sq8.ms20=sq9.id_item) sq10\r\n                      LEFT JOIN\r\n                      /*sq11 = full tokens from Ms 21*/\r\n                      (SELECT \r\n                        g.id_item, \r\n                        g.id_token, \r\n                        GROUP_CONCAT(g.subtoken ORDER BY g.id_item SEPARATOR \'\') AS beta_token21,\r\n                        GROUP_CONCAT(g.symbols_abbr ORDER BY g.id_item SEPARATOR \'\') AS token21\r\n                        FROM SubtokenMss g  \r\n                      WHERE g.id_ms=21\r\n                      GROUP BY g.id_token) sq11\r\n                    ON sq10.ms21=sq11.id_item) sq25\r\n\r\nJOIN \r\n\r\n/* sq4 = link edited token with the editor\'s corrections when no variant is viable */\r\n(SELECT\r\n  sq3.id_edition,\r\n  sq3.id_ms,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\n  g.correction AS token_jd,\r\nCASE            \r\nWHEN g.correction IS NOT NULL THEN CONCAT(g.correction, \'<note>\', g.editor, \'</note>\')\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_ms, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.token,\r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nUSING(id_edition)\r\nORDER BY sq25.id_edition) sq26\r\nORDER BY sq26.id_edition) sq27\r\nGROUP BY sq27.sentence\r\nORDER BY sq27.id_edition', '-- this concatenates from subtokens to sentences, including in notes both my conjectures and the mss variants.'),
(15, 'select dinstinct toponym names', 'SELECT\r\n	sq4.id_edition, \r\n	sq4.chapter, \r\n	sq4.sentence,\r\n	GROUP_CONCAT(DISTINCT sq4.exp_token ORDER BY sq4.topo_name_nr SEPARATOR \' \') AS topo_name,\r\n	sq4.topo_nr,\r\n	sq4.id_toponym,\r\n	tt.en_name\r\nFROM\r\n/* sq4 = links the edited token with the AlignmentToponyms table */\r\n(SELECT\r\n	sq3.id_edition, \r\n	sq3.chapter, \r\n	sq3.sentence, \r\n	sq3.exp_token, \r\n	sq3.topo_nr,\r\n	t.id_toponym,\r\n	t.topo_name_nr\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition,\r\n   sq2.topo_nr,\r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n topo_nr,\r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n topo_nr,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n topo_nr,\r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n topo_nr,\r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n topo_nr,\r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition,\r\n topo_nr,\r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\nLEFT JOIN AlignmentToponyms t \r\nUSING(topo_nr)) sq4\r\nLEFT JOIN ToponymsList tt\r\nUSING(id_toponym)\r\nWHERE sq4.topo_nr!=0\r\nGROUP BY sq4.id_toponym\r\nORDER BY sq4.id_toponym', 'selection of all the toponyms in the text, concatenated to form full names, ordered by id_toponym. Pb : all distinct in one cell, I need to seperate them. Maybe through count distinct or sth'),
(16, 'join text with ToponymsList', 'SELECT\r\n	sq4.id_edition, \r\n	sq4.chapter, \r\n	sq4.sentence, \r\n	sq4.exp_token, \r\n	sq4.topo_nr,\r\n	sq4.id_toponym,\r\n	sq4.topo_name_nr,\r\n	tt.en_name\r\nFROM\r\n/* sq4 = links the edited token with the AlignmentToponyms table */\r\n(SELECT\r\n	sq3.id_edition, \r\n	sq3.chapter, \r\n	sq3.sentence, \r\n	sq3.exp_token, \r\n	sq3.topo_nr,\r\n	t.id_toponym,\r\n	t.topo_name_nr\r\nFROM\r\n/* sq3 = edited tokens */\r\n(SELECT \r\n	sq2.id_edition, \r\n	sq1.chapter, \r\n	sq2.sentence, \r\n	sq1.exp_token, \r\n	sq2.topo_nr\r\nFROM \r\n/* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item)\r\nORDER BY sq2.id_edition) sq3\r\nLEFT JOIN AlignmentToponyms t \r\nUSING(topo_nr)) sq4\r\nLEFT JOIN ToponymsList tt\r\nUSING(id_toponym)\r\nORDER BY sq4.id_edition', 'selection of all the toponyms in the text joined with Toponymslist'),
(18, 'Visualisation transcription mss', 'SELECT \r\n	id_item, \r\n	page, \r\n	line, \r\n	GROUP_CONCAT(symbols_abbr order by pos_in_line SEPARATOR \' \') \r\nFROM SubtokenMss \r\nWHERE id_ms=1 AND (symbols_abbr!=\'.\' AND symbols_abbr!=\',\' AND symbols_abbr!=\':\' AND symbols_abbr!=\';\')\r\nGROUP BY line', 'shows mss transcription by line and page'),
(19, 'app. crit. / lemma', '/* to get the app. crit. all set up for the html page : \"lemme: MS names, MS name alternative\", one word a line; grouped by sentence */\r\n\r\nSELECT\r\n  sq26.id_edition,\r\n  sq26.chapter,\r\n  sq26.sentence,\r\n  CONCAT(sq26.ed_with_conj, \r\n    \': \', \'N \',COALESCE(sq26.N,\'-\'), \r\n    \', P \',COALESCE(sq26.P,\'-\'),\r\n    \', M \',COALESCE(sq26.M,\'-\'),\r\n    \', W \',COALESCE(sq26.W,\'-\'),\r\n    \', L \',COALESCE(sq26.L,\'-\'),\r\n    \', Li \',COALESCE(sq26.Li,\'-\'),\r\n    \'<br />\') AS token_appcrit\r\nFROM\r\n/*sq26 = aligns the abbreviated tokens from each manuscript with the full edited tokens on id_edition */\r\n(SELECT \r\n  sq25.id_edition,\r\n  sq25.N,\r\n  sq25.P,\r\n  sq25.M,\r\n  sq25.W,\r\n  sq25.L,\r\n  sq25.Li,\r\n  sq4.chapter,\r\n  sq4.sentence,\r\n  sq4.ed_with_conj\r\nFROM\r\n(SELECT \r\n  sq10.id_edition,\r\n  sq10.ms1,\r\n  sq10.N,\r\n  sq10.ms2,\r\n  sq10.P,\r\n  sq10.ms3,\r\n  sq10.M,\r\n  sq10.ms53,\r\n  sq10.W,\r\n  sq10.ms20,\r\n  sq10.L,\r\n  sq10.ms21,\r\n  sq11.token21 AS Li\r\nFROM\r\n  /*sq10 = links full tokens from Ms1 Ms2 Ms3 Ms53 Ms20 with AlignmentBeda */\r\n  (SELECT \r\n    sq8.id_edition,\r\n    sq8.ms1,\r\n    sq8.N,\r\n    sq8.ms2,\r\n    sq8.P,\r\n    sq8.ms3,\r\n    sq8.M,\r\n    sq8.ms53,\r\n    sq8.W,\r\n    sq8.ms20,\r\n    sq9.token20 AS L,\r\n    sq8.ms21\r\n  FROM\r\n    /*sq8 = links full tokens from Ms1 Ms2 Ms3 Ms53 with AlignmentBeda */\r\n    (SELECT \r\n      sq6.id_edition,\r\n      sq6.ms1,\r\n      sq6.N,\r\n      sq6.ms2,\r\n      sq6.P,\r\n      sq6.ms3,\r\n      sq6.M,\r\n      sq6.ms53,\r\n      sq7.token53 AS W,\r\n      sq6.ms20,\r\n      sq6.ms21\r\n    FROM\r\n    /*sq6 = links full tokens from Ms1 Ms2 Ms3 with AlignmentBeda */\r\n      (SELECT \r\n        sq4.id_edition,\r\n        sq4.ms1,\r\n        sq4.N,\r\n        sq4.ms2,\r\n        sq4.P,\r\n        sq4.ms3,\r\n        sq5.token3 AS M,\r\n        sq4.ms53,\r\n        sq4.ms20,\r\n        sq4.ms21\r\n      FROM\r\n        /*sq4 = links full tokens from Ms1 and Ms2 with AlignmentBeda */\r\n        (SELECT \r\n          sq2.id_edition,\r\n          sq2.ms1,\r\n          sq2.N,\r\n          sq2.ms2,\r\n          sq3.token2 AS P,\r\n          sq2.ms3,\r\n          sq2.ms53,\r\n          sq2.ms20,\r\n          sq2.ms21\r\n        FROM\r\n          /*sq2 = links full tokens from Ms1 with AlignmentBeda */\r\n          (SELECT \r\n            d.id_edition,\r\n            d.ms1,\r\n            sq1.token1 AS N,\r\n            d.ms2,\r\n            d.ms3,\r\n            d.ms53,\r\n            d.ms20,\r\n            d.ms21\r\n          FROM AlignmentBeda d\r\n            LEFT JOIN \r\n            /*sq1 = full tokens from Ms 1*/\r\n            (SELECT \r\n              a.id_item, \r\n              a.id_token, \r\n              GROUP_CONCAT(a.subtoken ORDER BY a.id_item SEPARATOR \'\') AS beta_token1,\r\n              GROUP_CONCAT(a.symbols_abbr ORDER BY a.id_item SEPARATOR \'\') AS token1\r\n            FROM SubtokenMss a \r\n            WHERE a.id_ms=1\r\n            GROUP BY a.id_token) sq1\r\n          ON d.ms1=sq1.id_item) sq2\r\n              LEFT JOIN \r\n              /*sq3 = full tokens from Ms 2*/\r\n              (SELECT \r\n                b.id_item, \r\n                b.id_token, \r\n                GROUP_CONCAT(b.subtoken ORDER BY b.id_item SEPARATOR \'\') AS beta_token2,\r\n                GROUP_CONCAT(b.symbols_abbr ORDER BY b.id_item SEPARATOR \'\') AS token2\r\n              FROM SubtokenMss b \r\n              WHERE b.id_ms=2\r\n              GROUP BY b.id_token) sq3\r\n            ON sq2.ms2=sq3.id_item) sq4\r\n                LEFT JOIN\r\n                /*sq5 = full tokens from Ms 3*/\r\n                (SELECT \r\n                  c.id_item, \r\n                  c.id_token, \r\n                  GROUP_CONCAT(c.subtoken ORDER BY c.id_item SEPARATOR \'\') AS beta_token3,\r\n                  GROUP_CONCAT(c.symbols_abbr ORDER BY c.id_item SEPARATOR \'\') AS token3\r\n                FROM SubtokenMss c \r\n                WHERE c.id_ms=3\r\n                GROUP BY c.id_token) sq5\r\n              ON sq4.ms3=sq5.id_item) sq6\r\n                  LEFT JOIN\r\n                  /*sq7 = full tokens from Ms 53*/\r\n                  (SELECT \r\n                    e.id_item, \r\n                    e.id_token, \r\n                    GROUP_CONCAT(e.subtoken ORDER BY e.id_item SEPARATOR \'\') AS beta_token53,\r\n                    GROUP_CONCAT(e.symbols_abbr ORDER BY e.id_item SEPARATOR \'\') AS token53\r\n                    FROM SubtokenMss e \r\n                  WHERE e.id_ms=53\r\n                  GROUP BY e.id_token) sq7\r\n                ON sq6.ms53=sq7.id_item)sq8\r\n                    LEFT JOIN\r\n                     /*sq9 = full tokens from Ms 20*/\r\n                    (SELECT \r\n                      f.id_item, \r\n                      f.id_token, \r\n                      GROUP_CONCAT(f.subtoken ORDER BY f.id_item SEPARATOR \'\') AS beta_token20,\r\n                      GROUP_CONCAT(f.symbols_abbr ORDER BY f.id_item SEPARATOR \'\') AS token20\r\n                    FROM SubtokenMss f \r\n                    WHERE f.id_ms=20\r\n                    GROUP BY f.id_token) sq9\r\n                  ON sq8.ms20=sq9.id_item) sq10\r\n                      LEFT JOIN\r\n                      /*sq11 = full tokens from Ms 21*/\r\n                      (SELECT \r\n                        g.id_item, \r\n                        g.id_token, \r\n                        GROUP_CONCAT(g.subtoken ORDER BY g.id_item SEPARATOR \'\') AS beta_token21,\r\n                        GROUP_CONCAT(g.symbols_abbr ORDER BY g.id_item SEPARATOR \'\') AS token21\r\n                        FROM SubtokenMss g  \r\n                      WHERE g.id_ms=21\r\n                      GROUP BY g.id_token) sq11\r\n                    ON sq10.ms21=sq11.id_item) sq25\r\n\r\nJOIN \r\n\r\n/* sq4 = link edited token with the editor\'s corrections when no variant is viable */\r\n(SELECT\r\n  sq3.id_edition,\r\n  sq3.id_ms,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\n  g.correction AS token_jd,\r\nCASE            \r\nWHEN g.correction IS NOT NULL THEN CONCAT(g.correction, \'<note>\', g.editor, \'</note>\')\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_ms, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.token,\r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nUSING(id_edition)\r\nORDER BY sq25.id_edition) sq26\r\nORDER BY sq26.id_edition', 'gives each lemma with the mss variants (x: N, P, ...)'),
(20, 'create table Edition', 'SELECT\r\n  sq3.id_edition,\r\n  sq3.id_ms,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\n  sq3.problem,\r\n  sq3.topo_nr,\r\nCASE						\r\nWHEN g.correction IS NOT NULL THEN g.correction\r\nELSE sq3.exp_token\r\nEND AS edited_token\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_ms, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence,\r\n   sq2.problem,\r\n   sq2.topo_nr,\r\n   sq1.token,\r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY a.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY b.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY c.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY d.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY e.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(subtoken ORDER BY f.id_item SEPARATOR \'\') AS token,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition', ''),
(21, 'bold toponyms ', 'SELECT \r\n sq1.id_toponym,\r\n sq1.topo_occurrence,\r\n sq1.topo_nr,\r\n sq1.chapter,\r\n sq1.sentence,\r\n sq1.edited_token,\r\n sq2.edition_sent\r\n FROM\r\n (SELECT * FROM Edition ed\r\n RIGHT JOIN AlignmentToponyms t\r\n USING(topo_nr)\r\n WHERE t.topo_name_nr=1\r\n ORDER BY t.id_toponym, t.topo_nr) sq1\r\n \r\n LEFT JOIN \r\n                                \r\n (SELECT\r\n sq3.chapter,\r\n sq3.sentence,\r\n GROUP_CONCAT(sq3.tokens ORDER BY sq3.id_edition SEPARATOR \' \') AS edition_sent\r\n FROM \r\n (SELECT \r\ned.id_edition,\r\ned.id_ms,\r\ned.chapter,\r\ned.sentence,\r\ned.topo_nr,\r\nCASE WHEN alt.id_toponym = \'\". $id_topo .\"\' THEN CONCAT(\'<b>\', ed.edited_token, \'</b>\')\r\nELSE ed.edited_token\r\nEND AS tokens,\r\nalt.id_toponym\r\nFROM Edition ed \r\nLEFT JOIN AlignmentToponyms alt \r\nUSING(topo_nr)\r\nORDER BY id_edition) sq3\r\nGROUP BY sq3.sentence) sq2\r\nUSING(sentence)\r\nWHERE id_toponym = \'\". $id_topo .\"\'\r\nORDER BY sq1.id_toponym, sq2.sentence', 'Highlighs the toponyms in bold in the index. Problem: very slow on the website'),
(22, 'bold ALL toponyms', 'SELECT \r\n sq1.id_toponym,\r\n sq1.topo_occurrence,\r\n sq1.topo_nr,\r\n sq1.chapter,\r\n sq1.sentence,\r\n sq1.edited_token,\r\n sq2.edition_sent\r\n FROM\r\n (SELECT * FROM Edition ed\r\n RIGHT JOIN AlignmentToponyms t\r\n USING(topo_nr)\r\n WHERE t.topo_name_nr=1\r\n ORDER BY t.id_toponym, t.topo_nr) sq1\r\n \r\n LEFT JOIN \r\n                                \r\n (SELECT\r\n  chapter,\r\n   sentence,\r\n GROUP_CONCAT(highlighted_token ORDER BY id_edition SEPARATOR \' \') AS edition_sent\r\n  FROM \r\n(SELECT * FROM\r\n (SELECT\r\n id_edition,\r\n id_ms,\r\n chapter,   \r\n sentence,\r\n problem,\r\n topo_nr,\r\n CONCAT(\'<b>\', edited_token, \'</b>\') AS highlighted_token\r\nFROM Edition\r\nWHERE topo_nr!=0\r\nUNION \r\nSELECT * FROM Edition\r\nWHERE topo_nr=0\r\nORDER BY id_edition) sq3\r\nLEFT JOIN AlignmentToponyms t\r\nUSING(topo_nr)) sq4\r\n\r\nGROUP BY sq4.sentence) sq2\r\nUSING(sentence)\r\nWHERE id_toponym = \'\". $id_topo .\"\'\r\nORDER BY sq1.id_toponym, sq2.sentence', 'Highlights in bold all the toponyms in the text, to show in the index. not faster than 21 and worse result ...'),
(23, 'Set Max lenght', 'SET GROUP_CONCAT_MAX_LEN=100000', 'WP site: query1 dls-text');
INSERT INTO `Queries` (`id_query`, `name`, `query`, `comment`) VALUES
(24, 'Edited text linked with app. crit.', 'SELECT \r\n  sq5.id_edition,\r\n  sq5.chapter,\r\n  sq5.sentence,\r\n  GROUP_CONCAT(sq5.lemmaApp ORDER BY sq5.id_edition SEPARATOR \'\') AS text\r\nFROM \r\n(SELECT\r\n  sq4.id_edition,\r\n  sq4.chapter,\r\n  sq4.sentence,\r\n  sq4.ed_with_conj,\r\n  CONCAT(\'<a class=\\\"lemmaApp\\\" id=\\\"l\', sq4.id_edition, \'\\\" target=\\\"frameAppCrit\\\" href=\\\"https://www.bedadls.gwi.uni-muenchen.de/appcrit#el\', sq4.id_edition-2, \'\\\"> \', sq4.ed_with_conj, \'</a>\') AS lemmaApp\r\nFROM \r\n(SELECT\r\n  sq3.id_edition,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\nCASE                        \r\nWHEN g.correction IS NOT NULL THEN g.correction\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4) sq5\r\nGROUP BY sq5.chapter', 'WP site: query2 dls-text (APPCRIT: select text org / chapter, each word linked to app. crit.)'),
(25, 'Sentences linked to sources', 'SELECT * FROM \r\n(SELECT\r\n  sq4.chapter,\r\n  sq4.sentence,\r\n  GROUP_CONCAT(sq4.ed_with_conj ORDER BY sq4.id_edition SEPARATOR \' \') AS edition_sent\r\nFROM\r\n(SELECT  \r\n  sq3.id_edition,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\nCASE                        \r\nWHEN g.correction IS NOT NULL THEN g.correction\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.chapter,\r\n  a.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.chapter,\r\n  b.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.chapter,\r\n  c.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.chapter,\r\n  d.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.chapter,\r\n  e.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.chapter,\r\n  f.id_item,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nGROUP BY sq4.sentence) sq5\r\nLEFT JOIN \r\n(SELECT\r\n  GROUP_CONCAT(sq9.links1 ORDER BY sq9.id_reference SEPARATOR \'; \') AS links,\r\n  sq9.sentence\r\nFROM\r\n(SELECT \r\n CONCAT(sq8.refs, \r\n    COALESCE(sq8.link1,\'\'), \r\n    COALESCE(sq8.linkcsel,\'\'),\r\n    COALESCE(sq8.linkpl,\'\')) AS links1,\r\n sq8.sentence,\r\n sq8.id_reference\r\nFROM\r\n(SELECT \r\n  CONCAT(c.short_name, \', \', sq7.short_title, \' \', sq7.reference, \' \',sq7.printed_ref, \': \') AS refs,\r\n  CASE WHEN sq7.link IS NOT NULL THEN \r\n  CONCAT(\'<a target=\\\"_blank\\\" href=\\\"\', sq7.link, \'\\\">Source</a> \') \r\n  ELSE sq7.link\r\n  END AS link1,\r\n  CASE WHEN sq7.link_csel IS NOT NULL THEN \r\n  CONCAT(\'<a target=\\\"_blank\\\" href=\\\"\', sq7.link_csel, \'\\\">CSEL</a> \')\r\n  ELSE sq7.link_csel\r\n  END AS linkcsel,\r\n  CASE WHEN sq7.link_pl IS NOT NULL THEN \r\n  CONCAT(\'<a target=\\\"_blank\\\" href=\\\"\', sq7.link_pl, \'\\\">PL</a>\') \r\n  ELSE sq7.link_pl\r\n  END AS linkpl,\r\n  sq7.sentence, \r\n  sq7.id_reference \r\nFROM\r\n(SELECT * FROM SourceReferences a\r\nLEFT JOIN SourceTexts b \r\nUSING(id_text)) sq7\r\nLEFT JOIN SourceAuthors c\r\nUSING(id_author)\r\nORDER BY sentence) sq8) sq9\r\nGROUP BY sq9.sentence) sq6\r\nUSING(sentence)', 'WP site: query4 dls-text (SOURCES: select sentences linked with their sources)'),
(26, 'Text linked with toponyms', 'SELECT\r\n  sq6.id_edition, \r\n  sq6.chapter, \r\n  sq6.sentence,\r\n  GROUP_CONCAT(sq6.text_topo ORDER BY sq6.id_edition SEPARATOR \' \') AS topotext\r\nFROM\r\n(SELECT\r\n  sq5.id_edition, \r\n  sq5.chapter, \r\n  sq5.sentence,\r\n  CASE            \r\n  WHEN sq5.id_toponym IS NOT NULL THEN CONCAT(\'<a id=\\\"tnr\', sq5.topo_nr, \'\\\" target=\\\"frameTopo\\\" href=\\\"https://www.bedadls.gwi.uni-muenchen.de/topolist#t\', sq5.id_toponym, \'\\\">\', sq5.ed_with_conj, \'</a>\')\r\n  ELSE sq5.ed_with_conj\r\n  END AS text_topo\r\nFROM\r\n/* sq5 = links the edited token with the AlignmentToponyms table */\r\n(SELECT\r\n  sq4.id_edition, \r\n  sq4.chapter, \r\n  sq4.sentence, \r\n  sq4.ed_with_conj, \r\n  sq4.topo_nr,\r\n  t.id_toponym\r\nFROM\r\n/* sq4 = edited tokens with corrections */\r\n(SELECT \r\n  sq3.id_edition, \r\n  sq3.chapter, \r\n  sq3.sentence, \r\n  CASE            \r\n  WHEN g.correction IS NOT NULL THEN g.correction\r\n  ELSE sq3.exp_token\r\n  END AS ed_with_conj,\r\n  sq3.topo_nr\r\nFROM  \r\n/* sq3 = edited tokens */\r\n(SELECT \r\n  sq2.id_edition, \r\n  sq1.chapter, \r\n  sq2.sentence, \r\n  sq1.exp_token, \r\n  sq2.topo_nr\r\nFROM \r\n/* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\n UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  topo_nr\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item)\r\nORDER BY sq2.id_edition) sq3\r\nLEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nLEFT JOIN AlignmentToponyms t \r\nUSING(topo_nr)) sq5\r\nORDER BY sq5.id_edition) sq6\r\nGROUP BY sq6.chapter', 'WP site: query5 dls-text (TOPONYMS: Select text org / chapter with toponyms linked to index of toponyms)'),
(27, 'text with translation', 'SELECT \r\n  sq5.chapter,\r\n  sq5.sentence,\r\n  sq5.edition_sent,\r\n  t.text_fr\r\nFROM \r\n(SELECT\r\n  sq4.chapter,\r\n  sq4.sentence,\r\n  GROUP_CONCAT(sq4.ed_with_conj ORDER BY sq4.id_edition SEPARATOR \' \') AS edition_sent\r\nFROM\r\n(SELECT  \r\n  sq3.id_edition,\r\n  sq3.chapter,\r\n  sq3.sentence,\r\nCASE                        \r\nWHEN g.correction IS NOT NULL THEN g.correction\r\nELSE sq3.exp_token\r\nEND AS ed_with_conj\r\nFROM\r\n  /* sq3 = select_edited_tokens */\r\n(SELECT \r\n   sq2.id_edition, \r\n   sq1.id_item, \r\n   sq1.chapter,\r\n   sq2.sentence, \r\n   sq1.exp_token\r\nFROM \r\n  /* sq1 = full_tokens */\r\n(SELECT \r\n  a.id_item,\r\n  a.id_ms,\r\n  a.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss a\r\nWHERE id_ms=1\r\nGROUP BY a.id_token\r\n  UNION\r\nSELECT \r\n  b.id_item,\r\n  b.id_ms,\r\n  b.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss b\r\nWHERE id_ms=2\r\nGROUP BY b.id_token \r\n  UNION\r\nSELECT \r\n  c.id_item,\r\n  c.id_ms,\r\n  c.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss c\r\nWHERE id_ms=3\r\nGROUP BY c.id_token\r\n  UNION\r\nSELECT \r\n  d.id_item,\r\n  d.id_ms,\r\n  d.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss d\r\nWHERE id_ms=53\r\nGROUP BY d.id_token\r\n  UNION\r\nSELECT \r\n  e.id_item,\r\n  e.id_ms,\r\n  e.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss e\r\nWHERE id_ms=20\r\nGROUP BY e.id_token\r\n  UNION\r\nSELECT \r\n  f.id_item,\r\n  f.id_ms,\r\n  f.chapter,\r\n  GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS exp_token\r\nFROM SubtokenMss f\r\nWHERE id_ms=21\r\nGROUP BY f.id_token) sq1 \r\n  RIGHT JOIN\r\n/* sq2 = priority_variants */ \r\n(SELECT \r\n  id_edition, \r\n  sentence, \r\n  ms1 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 1 AND ms1!=0\r\n  UNION \r\nSELECT \r\n  id_edition,\r\n  sentence, \r\n  ms2 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 2 AND ms2!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms3 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 3 AND ms3!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms53 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 53 AND ms53!=0\r\n  UNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms20 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 20 AND ms20!=0\r\nUNION\r\nSELECT \r\n  id_edition, \r\n  sentence, \r\n  ms21 AS id_item,\r\n  problem\r\nFROM AlignmentBeda AS edition \r\nWHERE priority = 21 AND ms21!=0\r\nORDER BY id_edition) sq2\r\nUSING(id_item) \r\nORDER BY sq2.id_edition) sq3\r\n    LEFT JOIN EditionCorrections g\r\nUSING(id_edition)\r\nORDER BY sq3.id_edition) sq4\r\nGROUP BY sq4.sentence) sq5\r\nJOIN Translation t\r\nUSING(sentence)', 'WP site: query7 dls-text (TRANSLATION: select Latin text and French translation, org / sentence)'),
(28, 'Table variants (expanded tokens)', 'SELECT \r\n  sq13.id_edition, \r\n  sq13.chapter, \r\n  sq13.sentence, \r\n  CASE            \r\nWHEN h.correction IS NOT NULL THEN CONCAT(\'<a class=\\\"lemmaApp\\\" target=\\\"_parent\\\" id=\\\"\', sq13.id_edition, \'\\\" href=\\\"https://www.bedadls.gwi.uni-muenchen.de/dls-text/?appCrit=Apparatus#l\', sq13.id_edition-30, \'\\\"> \', h.correction, \'</a>\')\r\nELSE CONCAT(\'<a class=\\\"lemmaApp\\\" target=\\\"_parent\\\" id=\\\"\', sq13.id_edition, \'\\\" href=\\\"https://www.bedadls.gwi.uni-muenchen.de/dls-text/?appCrit=Apparatus#l\', sq13.id_edition-30, \'\\\"> \', sq13.ed_lemma, \'</a>\')\r\nEND AS ed_corr_lemma,\r\n  sq13.N,\r\n  sq13.P,\r\n  sq13.M,\r\n  sq13.W,\r\n  sq13.L,\r\n  sq13.Li,\r\n  h.correction AS corr_JD\r\nFROM\r\n(SELECT * \r\nFROM\r\n  (SELECT \r\n    sq2.id_edition, \r\n    sq1.chapter, \r\n    sq2.sentence, \r\n    sq1.ed_lemma, \r\n    sq2.problem \r\n  FROM \r\n    /* sq1 = full_tokens */\r\n    (SELECT \r\n      a.id_item,\r\n      a.id_ms,\r\n      a.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss a\r\n    WHERE id_ms=1\r\n    GROUP BY a.id_token\r\n      UNION\r\n    SELECT \r\n      b.id_item,\r\n      b.id_ms,\r\n      b.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss b\r\n    WHERE id_ms=2\r\n    GROUP BY b.id_token \r\n      UNION\r\n    SELECT \r\n      c.id_item,\r\n      c.id_ms,\r\n      c.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss c\r\n    WHERE id_ms=3\r\n    GROUP BY c.id_token\r\n      UNION\r\n    SELECT \r\n      d.id_item,\r\n      d.id_ms,\r\n      d.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss d\r\n    WHERE id_ms=53\r\n    GROUP BY d.id_token\r\n      UNION\r\n    SELECT \r\n      e.id_item,\r\n      e.id_ms,\r\n      e.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss e\r\n    WHERE id_ms=20\r\n    GROUP BY e.id_token\r\n      UNION\r\n    SELECT \r\n      f.id_item,\r\n      f.id_ms,\r\n      f.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss f\r\n    WHERE id_ms=21\r\n    GROUP BY f.id_token) sq1\r\n  RIGHT JOIN \r\n    /* sq2 = priority_variants */\r\n    (SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms1 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 1\r\n      UNION \r\n    SELECT \r\n      id_edition,\r\n      sentence, \r\n      ms2 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 2\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms3 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 3\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms53 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 53\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms20 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 20\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms21 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 21\r\n    ORDER BY id_edition) sq2\r\n    USING(id_item)\r\n  ORDER BY sq2.id_edition) sq3\r\n\r\nJOIN \r\n\r\n(SELECT \r\n  sq10.id_edition,\r\n  sq10.ms1,\r\n  sq10.N,\r\n  sq10.ms2,\r\n  sq10.P,\r\n  sq10.ms3,\r\n  sq10.M,\r\n  sq10.ms53,\r\n  sq10.W,\r\n  sq10.ms20,\r\n  sq10.L,\r\n  sq10.ms21,\r\n  sq11.token21 AS Li\r\nFROM\r\n  /*sq10 = links full tokens from Ms1 Ms2 Ms3 Ms53 Ms20 with AlignmentBeda */\r\n  (SELECT \r\n    sq8.id_edition,\r\n    sq8.ms1,\r\n    sq8.N,\r\n    sq8.ms2,\r\n    sq8.P,\r\n    sq8.ms3,\r\n    sq8.M,\r\n    sq8.ms53,\r\n    sq8.W,\r\n    sq8.ms20,\r\n    sq9.token20 AS L,\r\n    sq8.ms21\r\n  FROM\r\n    /*sq8 = links full tokens from Ms1 Ms2 Ms3 Ms53 with AlignmentBeda */\r\n    (SELECT \r\n      sq6.id_edition,\r\n      sq6.ms1,\r\n      sq6.N,\r\n      sq6.ms2,\r\n      sq6.P,\r\n      sq6.ms3,\r\n      sq6.M,\r\n      sq6.ms53,\r\n      sq7.token53 AS W,\r\n      sq6.ms20,\r\n      sq6.ms21\r\n    FROM\r\n    /*sq6 = links full tokens from Ms1 Ms2 Ms3 with AlignmentBeda */\r\n      (SELECT \r\n        sq4.id_edition,\r\n        sq4.ms1,\r\n        sq4.N,\r\n        sq4.ms2,\r\n        sq4.P,\r\n        sq4.ms3,\r\n        sq5.token3 AS M,\r\n        sq4.ms53,\r\n        sq4.ms20,\r\n        sq4.ms21\r\n      FROM\r\n        /*sq4 = links full tokens from Ms1 and Ms2 with AlignmentBeda */\r\n        (SELECT \r\n          sq2.id_edition,\r\n          sq2.ms1,\r\n          sq2.N,\r\n          sq2.ms2,\r\n          sq3.token2 AS P,\r\n          sq2.ms3,\r\n          sq2.ms53,\r\n          sq2.ms20,\r\n          sq2.ms21\r\n        FROM\r\n          /*sq2 = links full tokens from Ms1 with AlignmentBeda */\r\n          (SELECT \r\n            d.id_edition,\r\n            d.ms1,\r\n            sq1.token1 AS N,\r\n            d.ms2,\r\n            d.ms3,\r\n            d.ms53,\r\n            d.ms20,\r\n            d.ms21\r\n          FROM AlignmentBeda d\r\n            LEFT JOIN \r\n            /*sq1 = full tokens from Ms 1*/\r\n            (SELECT \r\n              a.id_item, \r\n              a.id_token, \r\n              GROUP_CONCAT(a.expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS token1\r\n            FROM SubtokenMss a \r\n            WHERE a.id_ms=1\r\n            GROUP BY a.id_token) sq1\r\n          ON d.ms1=sq1.id_item) sq2\r\n              LEFT JOIN \r\n              /*sq3 = full tokens from Ms 2*/\r\n              (SELECT \r\n                b.id_item, \r\n                b.id_token, \r\n                GROUP_CONCAT(b.expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS token2\r\n              FROM SubtokenMss b \r\n              WHERE b.id_ms=2\r\n              GROUP BY b.id_token) sq3\r\n            ON sq2.ms2=sq3.id_item) sq4\r\n                LEFT JOIN\r\n                /*sq5 = full tokens from Ms 3*/\r\n                (SELECT \r\n                  c.id_item, \r\n                  c.id_token, \r\n                  GROUP_CONCAT(c.expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS token3\r\n                FROM SubtokenMss c \r\n                WHERE c.id_ms=3\r\n                GROUP BY c.id_token) sq5\r\n              ON sq4.ms3=sq5.id_item) sq6\r\n                  LEFT JOIN\r\n                  /*sq7 = full tokens from Ms 53*/\r\n                  (SELECT \r\n                    e.id_item, \r\n                    e.id_token, \r\n                    GROUP_CONCAT(e.expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS token53\r\n                    FROM SubtokenMss e \r\n                  WHERE e.id_ms=53\r\n                  GROUP BY e.id_token) sq7\r\n                ON sq6.ms53=sq7.id_item)sq8\r\n                    LEFT JOIN\r\n                     /*sq9 = full tokens from Ms 20*/\r\n                    (SELECT \r\n                      f.id_item, \r\n                      f.id_token, \r\n                      GROUP_CONCAT(f.expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS token20\r\n                    FROM SubtokenMss f \r\n                    WHERE f.id_ms=20\r\n                    GROUP BY f.id_token) sq9\r\n                  ON sq8.ms20=sq9.id_item) sq10\r\n                      LEFT JOIN\r\n                      /*sq11 = full tokens from Ms 21*/\r\n                      (SELECT \r\n                        g.id_item, \r\n                        g.id_token, \r\n                        GROUP_CONCAT(g.expand_abbr ORDER BY g.id_item SEPARATOR \'\') AS token21\r\n                        FROM SubtokenMss g  \r\n                      WHERE g.id_ms=21\r\n                      GROUP BY g.id_token) sq11\r\n                    ON sq10.ms21=sq11.id_item) sq12\r\n  USING(id_edition)) sq13\r\n\r\n  LEFT JOIN EditionCorrections h\r\n  USING(id_edition)', 'WP site: query3 appcrit (table with the mss variants (expanded)) '),
(29, 'Index toponyms - alphabetical order', 'SELECT * FROM ToponymsList ORDER BY lat_name', 'WP site: query6 topolist (TOPONYMS: select the index of toponyms)'),
(30, 'modal Toponyms 1: index linked to sentences', 'SELECT \r\n  sq1.id_toponym,\r\n  sq1.topo_occurrence,\r\n  sq1.topo_nr,\r\n  sq1.chapter,\r\n  sq1.sentence,\r\n  sq1.edited_token,\r\n  sq2.edition_sent\r\nFROM\r\n(SELECT * FROM Edition ed\r\nRIGHT JOIN AlignmentToponyms t\r\nUSING(topo_nr)\r\nWHERE t.topo_name_nr=1\r\nORDER BY t.id_toponym, t.topo_nr) sq1\r\n                                \r\nLEFT JOIN \r\n                                \r\n(SELECT\r\n  chapter,\r\n  sentence,\r\n  GROUP_CONCAT(edited_token ORDER BY id_edition SEPARATOR \' \') AS edition_sent\r\nFROM Edition ed\r\nGROUP BY ed.sentence) sq2\r\nUSING(sentence)\r\nWHERE id_toponym = \'\". $id_topo .\"\'\r\nORDER BY sq1.id_toponym, sq2.sentence', 'WP site: query6 toponyms (MODAL get sentences where toponym is cited)'),
(31, 'table variants - symbols', 'SELECT \r\n  sq13.id_edition, \r\n  sq13.chapter, \r\n  sq13.sentence, \r\n  CASE            \r\nWHEN h.correction IS NOT NULL THEN h.correction\r\nELSE sq13.ed_lemma\r\nEND AS ed_corr_lemma, \r\n  sq13.N,\r\n  sq13.P,\r\n  sq13.M,\r\n  sq13.W,\r\n  sq13.L,\r\n  sq13.Li,\r\n  h.correction AS corr_JD\r\nFROM\r\n(SELECT * \r\nFROM\r\n  (SELECT \r\n    sq2.id_edition, \r\n    sq1.chapter, \r\n    sq2.sentence, \r\n    sq1.ed_lemma, \r\n    sq2.problem \r\n  FROM \r\n    /* sq1 = full_tokens */\r\n    (SELECT \r\n      a.id_item,\r\n      a.id_ms,\r\n      a.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY a.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss a\r\n    WHERE id_ms=1\r\n    GROUP BY a.id_token\r\n      UNION\r\n    SELECT \r\n      b.id_item,\r\n      b.id_ms,\r\n      b.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY b.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss b\r\n    WHERE id_ms=2\r\n    GROUP BY b.id_token \r\n      UNION\r\n    SELECT \r\n      c.id_item,\r\n      c.id_ms,\r\n      c.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY c.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss c\r\n    WHERE id_ms=3\r\n    GROUP BY c.id_token\r\n      UNION\r\n    SELECT \r\n      d.id_item,\r\n      d.id_ms,\r\n      d.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY d.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss d\r\n    WHERE id_ms=53\r\n    GROUP BY d.id_token\r\n      UNION\r\n    SELECT \r\n      e.id_item,\r\n      e.id_ms,\r\n      e.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY e.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss e\r\n    WHERE id_ms=20\r\n    GROUP BY e.id_token\r\n      UNION\r\n    SELECT \r\n      f.id_item,\r\n      f.id_ms,\r\n      f.chapter,\r\n      GROUP_CONCAT(expand_abbr ORDER BY f.id_item SEPARATOR \'\') AS ed_lemma\r\n    FROM SubtokenMss f\r\n    WHERE id_ms=21\r\n    GROUP BY f.id_token) sq1\r\n  RIGHT JOIN \r\n    /* sq2 = priority_variants */\r\n    (SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms1 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 1\r\n      UNION \r\n    SELECT \r\n      id_edition,\r\n      sentence, \r\n      ms2 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 2\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms3 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 3\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms53 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 53\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms20 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 20\r\n      UNION\r\n    SELECT \r\n      id_edition, \r\n      sentence, \r\n      ms21 AS id_item,\r\n      problem\r\n    FROM AlignmentBeda AS edition \r\n    WHERE priority = 21\r\n    ORDER BY id_edition) sq2\r\n    USING(id_item)\r\n  ORDER BY sq2.id_edition) sq3\r\n\r\nJOIN \r\n\r\n(SELECT \r\n  sq10.id_edition,\r\n  sq10.ms1,\r\n  sq10.N,\r\n  sq10.ms2,\r\n  sq10.P,\r\n  sq10.ms3,\r\n  sq10.M,\r\n  sq10.ms53,\r\n  sq10.W,\r\n  sq10.ms20,\r\n  sq10.L,\r\n  sq10.ms21,\r\n  sq11.token21 AS Li\r\nFROM\r\n  /*sq10 = links full tokens from Ms1 Ms2 Ms3 Ms53 Ms20 with AlignmentBeda */\r\n  (SELECT \r\n    sq8.id_edition,\r\n    sq8.ms1,\r\n    sq8.N,\r\n    sq8.ms2,\r\n    sq8.P,\r\n    sq8.ms3,\r\n    sq8.M,\r\n    sq8.ms53,\r\n    sq8.W,\r\n    sq8.ms20,\r\n    sq9.token20 AS L,\r\n    sq8.ms21\r\n  FROM\r\n    /*sq8 = links full tokens from Ms1 Ms2 Ms3 Ms53 with AlignmentBeda */\r\n    (SELECT \r\n      sq6.id_edition,\r\n      sq6.ms1,\r\n      sq6.N,\r\n      sq6.ms2,\r\n      sq6.P,\r\n      sq6.ms3,\r\n      sq6.M,\r\n      sq6.ms53,\r\n      sq7.token53 AS W,\r\n      sq6.ms20,\r\n      sq6.ms21\r\n    FROM\r\n    /*sq6 = links full tokens from Ms1 Ms2 Ms3 with AlignmentBeda */\r\n      (SELECT \r\n        sq4.id_edition,\r\n        sq4.ms1,\r\n        sq4.N,\r\n        sq4.ms2,\r\n        sq4.P,\r\n        sq4.ms3,\r\n        sq5.token3 AS M,\r\n        sq4.ms53,\r\n        sq4.ms20,\r\n        sq4.ms21\r\n      FROM\r\n        /*sq4 = links full tokens from Ms1 and Ms2 with AlignmentBeda */\r\n        (SELECT \r\n          sq2.id_edition,\r\n          sq2.ms1,\r\n          sq2.N,\r\n          sq2.ms2,\r\n          sq3.token2 AS P,\r\n          sq2.ms3,\r\n          sq2.ms53,\r\n          sq2.ms20,\r\n          sq2.ms21\r\n        FROM\r\n          /*sq2 = links full tokens from Ms1 with AlignmentBeda */\r\n          (SELECT \r\n            d.id_edition,\r\n            d.ms1,\r\n            sq1.token1 AS N,\r\n            d.ms2,\r\n            d.ms3,\r\n            d.ms53,\r\n            d.ms20,\r\n            d.ms21\r\n          FROM AlignmentBeda d\r\n            LEFT JOIN \r\n            /*sq1 = full tokens from Ms 1*/\r\n            (SELECT \r\n              a.id_item, \r\n              a.id_token, \r\n              GROUP_CONCAT(a.symbols_abbr ORDER BY a.id_item SEPARATOR \'\') AS token1\r\n            FROM SubtokenMss a \r\n            WHERE a.id_ms=1\r\n            GROUP BY a.id_token) sq1\r\n          ON d.ms1=sq1.id_item) sq2\r\n              LEFT JOIN \r\n              /*sq3 = full tokens from Ms 2*/\r\n              (SELECT \r\n                b.id_item, \r\n                b.id_token, \r\n                GROUP_CONCAT(b.symbols_abbr ORDER BY b.id_item SEPARATOR \'\') AS token2\r\n              FROM SubtokenMss b \r\n              WHERE b.id_ms=2\r\n              GROUP BY b.id_token) sq3\r\n            ON sq2.ms2=sq3.id_item) sq4\r\n                LEFT JOIN\r\n                /*sq5 = full tokens from Ms 3*/\r\n                (SELECT \r\n                  c.id_item, \r\n                  c.id_token, \r\n                  GROUP_CONCAT(c.symbols_abbr ORDER BY c.id_item SEPARATOR \'\') AS token3\r\n                FROM SubtokenMss c \r\n                WHERE c.id_ms=3\r\n                GROUP BY c.id_token) sq5\r\n              ON sq4.ms3=sq5.id_item) sq6\r\n                  LEFT JOIN\r\n                  /*sq7 = full tokens from Ms 53*/\r\n                  (SELECT \r\n                    e.id_item, \r\n                    e.id_token, \r\n                    GROUP_CONCAT(e.symbols_abbr ORDER BY e.id_item SEPARATOR \'\') AS token53\r\n                    FROM SubtokenMss e \r\n                  WHERE e.id_ms=53\r\n                  GROUP BY e.id_token) sq7\r\n                ON sq6.ms53=sq7.id_item)sq8\r\n                    LEFT JOIN\r\n                     /*sq9 = full tokens from Ms 20*/\r\n                    (SELECT \r\n                      f.id_item, \r\n                      f.id_token, \r\n                      GROUP_CONCAT(f.symbols_abbr ORDER BY f.id_item SEPARATOR \'\') AS token20\r\n                    FROM SubtokenMss f \r\n                    WHERE f.id_ms=20\r\n                    GROUP BY f.id_token) sq9\r\n                  ON sq8.ms20=sq9.id_item) sq10\r\n                      LEFT JOIN\r\n                      /*sq11 = full tokens from Ms 21*/\r\n                      (SELECT \r\n                        g.id_item, \r\n                        g.id_token, \r\n                        GROUP_CONCAT(g.symbols_abbr ORDER BY g.id_item SEPARATOR \'\') AS token21\r\n                        FROM SubtokenMss g  \r\n                      WHERE g.id_ms=21\r\n                      GROUP BY g.id_token) sq11\r\n                    ON sq10.ms21=sq11.id_item) sq12\r\n  USING(id_edition)) sq13\r\n\r\n  LEFT JOIN EditionCorrections h\r\n  USING(id_edition)', 'WP site: query2 manuscripts (table variants - symbols)'),
(32, 'Sigla codicum', 'SELECT * FROM SiglaCodicum ORDER BY siglum', 'WP site: query1 manuscripts (sigla codicum)'),
(33, 'transcription ms line by line', 'SELECT * FROM\r\n(SELECT \r\n    page AS page1, \r\n    line AS line1, \r\n    GROUP_CONCAT(symbols_abbr order by pos_in_line SEPARATOR \' \') AS line_text1\r\nFROM SubtokenMss a\r\nWHERE id_ms=\'\".$ms_nr.\"\' AND (symbols_abbr!=\'.\' AND symbols_abbr!=\',\' AND symbols_abbr!=\':\' AND symbols_abbr!=\';\') \r\nGROUP BY line) sq1\r\n\r\nLEFT JOIN\r\n\r\n(SELECT \r\n    page AS page2, \r\n    line AS line2, \r\n    GROUP_CONCAT(symbols_abbr order by pos_in_line SEPARATOR \' \') AS line_text2\r\nFROM SubtokenMss b\r\nWHERE id_ms=\'\".$ms_nr2.\"\' AND (symbols_abbr!=\'.\' AND symbols_abbr!=\',\' AND symbols_abbr!=\':\' AND symbols_abbr!=\';\') \r\nGROUP BY line) sq2\r\nON sq1.line1 = sq2.line2\r\n\r\nUNION ALL\r\n\r\nSELECT * FROM\r\n(SELECT \r\n    page AS page1, \r\n    line AS line1, \r\n    GROUP_CONCAT(symbols_abbr order by pos_in_line SEPARATOR \' \') AS line_text1\r\nFROM SubtokenMss a\r\nWHERE id_ms=\'\".$ms_nr.\"\' AND (symbols_abbr!=\'.\' AND symbols_abbr!=\',\' AND symbols_abbr!=\':\' AND symbols_abbr!=\';\') \r\nGROUP BY line) sq1\r\n\r\nRIGHT JOIN\r\n\r\n(SELECT \r\n    page AS page2, \r\n    line AS line2, \r\n    GROUP_CONCAT(symbols_abbr order by pos_in_line SEPARATOR \' \') AS line_text2\r\nFROM SubtokenMss b\r\nWHERE id_ms=\'\".$ms_nr2.\"\' AND (symbols_abbr!=\'.\' AND symbols_abbr!=\',\' AND symbols_abbr!=\':\' AND symbols_abbr!=\';\') \r\nGROUP BY line) sq2\r\nON sq1.line1 = sq2.line2\r\nWHERE sq1.line1 IS NULL', 'WP site: query2 transcriptions (gives an idea of the organisation of the text in each manuscript)');

--
-- Index pour les tables déchargées
--

--
-- Index pour la table `Queries`
--
ALTER TABLE `Queries`
  ADD PRIMARY KEY (`id_query`);

--
-- AUTO_INCREMENT pour les tables déchargées
--

--
-- AUTO_INCREMENT pour la table `Queries`
--
ALTER TABLE `Queries`
  MODIFY `id_query` int(3) NOT NULL AUTO_INCREMENT COMMENT 'ID of the query', AUTO_INCREMENT=34;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
