/*
 * Copyright (C) 2016 Red Hat, Inc.
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; If not, see <http://www.gnu.org/licenses/>.
 *
 * Author: Gris Ge <fge@redhat.com>
 */

#ifndef _SIMC_DB_TABLE_INIT_H_
#define _SIMC_DB_TABLE_INIT_H

#include "db.h"

#define _LSM_ACCESS_GROUP_INIT_TYPE_ISCSI_WWPN_MIXED_STR "7"
#define _LSM_ACCESS_GROUP_INIT_TYPE_UNKNOWN_STR          "0"

static const char *_TABLE_INIT =
    "PRAGMA foreign_keys = ON;\n"
    "CREATE TABLE " _DB_TABLE_SYS " (\n"
    "    id TEXT PRIMARY KEY,\n"
    "    name TEXT NOT NULL,\n"
    "    status INTEGER NOT NULL,\n"
    "    status_info TEXT,\n"
    "    read_cache_pct INTEGER,\n"
    "    version TEXT NOT NULL);\n"
    "CREATE TABLE " _DB_TABLE_TGTS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    port_type INTEGER NOT NULL,\n"
    "    service_address TEXT NOT NULL,\n"
    "    network_address TEXT NOT NULL,\n"
    "    physical_address TEXT NOT NULL,\n"
    "    physical_name TEXT NOT NULL);\n"
    "CREATE TABLE pools (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    name TEXT UNIQUE NOT NULL,\n"
    "    status INTEGER NOT NULL,\n"
    "    status_info TEXT,\n"
    "    element_type INTEGER NOT NULL,\n"
    "    unsupported_actions INTEGER,\n"
    "    raid_type INTEGER NOT NULL,\n"
    "    parent_pool_id INTEGER,\n"
    /* ^ Indicate this pool is allocated from other pool */
    "    member_type INTEGER,\n"
    "    strip_size INTEGER,\n"
    "    total_space LONG);\n"
    /* ^ total_space here is only for sub-pool (pool from pool) */
    "CREATE TABLE disks (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    total_space LONG NOT NULL,\n"
    "    disk_type INTEGER NOT NULL,\n"
    "    status INTEGER NOT NULL,\n"
    "    disk_prefix TEXT NOT NULL,\n"
    "    location TEXT NOT NULL,\n"
    "    owner_pool_id INTEGER,\n"
    /* ^ Indicate this disk is used to assemble a pool */
    "    role TEXT,\n"
    "    vpd83 TEXT,\n"
    "    rpm INTEGER,\n"
    "    link_type INTEGER,\n"
    "    FOREIGN KEY(owner_pool_id)\n"
    "    REFERENCES pools(id) ON DELETE SET DEFAULT);\n"
    "CREATE TABLE " _DB_TABLE_VOLS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    vpd83 TEXT NOT NULL,\n"
    "    name TEXT UNIQUE NOT NULL,\n"
    "    total_space LONG NOT NULL,\n"
    "    consumed_size LONG NOT NULL,\n"
    /* ^ Reserved for future thinp support. */
    "    admin_state INTEGER,\n"
    "    is_hw_raid_vol INTEGER,\n"
    /* ^ Once its volume deleted, pool will be delete also.
     *   For HW RAID simulation only.
     */
    "    write_cache_policy INTEGER NOT NULL,\n"
    "    read_cache_policy INTEGER NOT NULL,\n"
    "    phy_disk_cache INTEGER NOT NULL,\n"
    "    pool_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(pool_id)\n"
    "    REFERENCES pools(id) ON DELETE CASCADE);\n"
    "CREATE TABLE ags (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    name TEXT UNIQUE NOT NULL);\n"
    "CREATE TABLE " _DB_TABLE_INITS " (\n"
    "    id TEXT UNIQUE NOT NULL,\n"
    "    init_type INTEGER NOT NULL,\n"
    "    owner_ag_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(owner_ag_id)\n"
    "    REFERENCES ags(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_VOL_MASKS " (\n"
    "    vol_id INTEGER NOT NULL,\n"
    "    ag_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(vol_id) REFERENCES volumes(id) ON DELETE CASCADE,\n"
    "    FOREIGN KEY(ag_id) REFERENCES ags(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_VOL_REPS " (\n"
    "    rep_type INTEGER,\n"
    "    src_vol_id INTEGER NOT NULL,\n"
    "    dst_vol_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(src_vol_id)\n"
    "    REFERENCES volumes(id) ON DELETE CASCADE,\n"
    "    FOREIGN KEY(dst_vol_id)\n"
    "    REFERENCES volumes(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_FSS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    name TEXT UNIQUE NOT NULL,\n"
    "    total_space LONG NOT NULL,\n"
    "    consumed_size LONG NOT NULL,\n"
    "    free_space LONG,\n"
    "    pool_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(pool_id)\n"
    "    REFERENCES pools(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_FS_SNAPS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    name TEXT UNIQUE NOT NULL,\n"
    "    fs_id INTEGER NOT NULL,\n"
    "    timestamp LONG NOT NULL,\n"
    "    FOREIGN KEY(fs_id)\n"
    "    REFERENCES fss(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_FS_CLONES " (\n"
    "    src_fs_id INTEGER NOT NULL,\n"
    "    dst_fs_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(src_fs_id)\n"
    "    REFERENCES fss(id) ON DELETE CASCADE,\n"
    "    FOREIGN KEY(dst_fs_id)\n"
    "    REFERENCES fss(id) ON DELETE CASCADE);\n"
    "CREATE TABLE " _DB_TABLE_NFS_EXPS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    fs_id INTEGER NOT NULL,\n"
    "    exp_path TEXT UNIQUE NOT NULL,\n"
    "    auth_type TEXT,\n"
    "    anon_uid INTEGER,\n"
    "    anon_gid INTEGER,\n"
    "    options TEXT,\n"
    "    FOREIGN KEY(fs_id)\n"
    "    REFERENCES fss(id) ON DELETE CASCADE);\n"
    "CREATE TABLE exp_root_hosts (\n"
    "    host TEXT NOT NULL,\n"
    "    exp_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(exp_id)\n"
    "    REFERENCES exps(id) ON DELETE CASCADE);\n"
    "CREATE TABLE exp_rw_hosts (\n"
    "    host TEXT NOT NULL,\n"
    "    exp_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(exp_id)\n"
    "    REFERENCES exps(id) ON DELETE CASCADE);\n"
    "CREATE TABLE exp_ro_hosts (\n"
    "    host TEXT NOT NULL,\n"
    "    exp_id INTEGER NOT NULL,\n"
    "    FOREIGN KEY(exp_id)\n"
    "    REFERENCES exps(id) ON DELETE CASCADE);\n"
    "CREATE TABLE jobs (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    duration REAL NOT NULL,\n"
    "    timestamp TEXT NOT NULL,\n"
    "    data_type INTEGER,\n"
    "    data_id INTEGER);\n"
    "CREATE TABLE " _DB_TABLE_BATS " (\n"
    "    id INTEGER PRIMARY KEY,\n"
    "    name TEXT NOT NULL,\n"
    "    type INTEGER NOT NULL,\n"
    "    status INTEGER NOT NULL);\n"
    /* Create views */
    "CREATE VIEW " _DB_TABLE_POOLS_VIEW " AS\n"
    "    SELECT\n"
    "        pool0.id,\n"
    "            'POOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || pool0.id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_pool_id,\n"
    "        pool0.name,\n"
    "        pool0.status,\n"
    "        pool0.status_info,\n"
    "        pool0.element_type,\n"
    "        pool0.unsupported_actions,\n"
    "        pool0.raid_type,\n"
    "        pool0.member_type,\n"
    "        pool0.parent_pool_id,\n"
    "            'POOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || pool0.parent_pool_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        parent_lsm_pool_id,\n"
    "        pool0.strip_size,\n"
    "        pool1.total_space total_space,\n"
    "        pool1.total_space -\n"
    "        pool2.vol_consumed_size  -\n"
    "        pool3.fs_consumed_size -\n"
    "        pool4.sub_pool_consumed_size free_space,\n"
    "        pool1.data_disk_count,\n"
    "        pool5.disk_count\n"
    "    FROM\n"
    "        pools pool0\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    pool.id,\n"
    "                        ifnull(pool.total_space,\n"
    "                            ifnull(SUM(disk.total_space), 0))\n"
    "                    total_space,\n"
    "                    COUNT(disk.id) data_disk_count\n"
    "                FROM pools pool\n"
    "                    LEFT JOIN disks disk\n"
    "                        ON pool.id = disk.owner_pool_id AND\n"
    "                            disk.role = 'DATA'\n"
    "                GROUP BY\n"
    "                    pool.id\n"
    "            ) pool1 ON pool0.id = pool1.id\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    pool.id,\n"
    "                        ifnull(SUM(volume.consumed_size), 0)\n"
    "                    vol_consumed_size\n"
    "                FROM pools pool\n"
    "                    LEFT JOIN volumes volume\n"
    "                        ON volume.pool_id = pool.id\n"
    "                GROUP BY\n"
    "                    pool.id\n"
    "            ) pool2 ON pool0.id = pool2.id\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    pool.id,\n"
    "                        ifnull(SUM(fs.consumed_size), 0)\n"
    "                    fs_consumed_size\n"
    "                FROM pools pool\n"
    "                    LEFT JOIN fss fs\n"
    "                        ON fs.pool_id = pool.id\n"
    "                GROUP BY\n"
    "                    pool.id\n"
    "            ) pool3 ON pool0.id = pool3.id\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    pool.id,\n"
    "                        ifnull(SUM(sub_pool.total_space), 0)\n"
    "                    sub_pool_consumed_size\n"
    "                FROM pools pool\n"
    "                    LEFT JOIN pools sub_pool\n"
    "                        ON sub_pool.parent_pool_id = pool.id\n"
    "                GROUP BY\n"
    "                    pool.id\n"
    "            ) pool4 ON pool0.id = pool4.id\n"
    "            LEFT JOIN (\n"
    "            SELECT\n"
    "                pool.id,\n"
    "                COUNT(disk.id) disk_count\n"
    "            FROM pools pool\n"
    "                LEFT JOIN disks disk\n"
    "                    ON pool.id = disk.owner_pool_id\n"
    "            GROUP BY\n"
    "                pool.id\n"
    "            ) pool5 ON pool0.id = pool5.id\n"
    "    GROUP BY\n"
    "         pool0.id;\n"
    "CREATE VIEW " _DB_TABLE_TGTS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'TGT_PORT_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_tgt_id,\n"
    "        port_type,\n"
    "        service_address,\n"
    "        network_address,\n"
    "        physical_address,\n"
    "        physical_name\n"
    "    FROM\n"
    "       " _DB_TABLE_TGTS ";\n"
    "CREATE VIEW " _DB_TABLE_DISKS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'DISK_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_disk_id,\n"
    "            disk_prefix || '_' || id\n"
    "        name,\n"
    "        total_space,\n"
    "        disk_type,\n"
    "        role,\n"
    "        status,\n"
    "        vpd83,\n"
    "        rpm,\n"
    "        link_type,\n"
    "        location,\n"
    "        owner_pool_id\n"
    "    FROM\n"
    "        disks;\n"
    "CREATE VIEW " _DB_TABLE_VOLS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'VOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_vol_id,\n"
    "        vpd83,\n"
    "        name,\n"
    "        total_space,\n"
    "        consumed_size,\n"
    "        admin_state,\n"
    "        is_hw_raid_vol,\n"
    "        write_cache_policy,\n"
    "        read_cache_policy,\n"
    "        phy_disk_cache,\n"
    "        pool_id,\n"
    "            'POOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || pool_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_pool_id\n"
    "    FROM\n"
    "        volumes;\n"
    "CREATE VIEW " _DB_TABLE_FSS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'FS_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_fs_id,\n"
    "        name,\n"
    "        total_space,\n"
    "        consumed_size,\n"
    "        free_space,\n"
    "        pool_id,\n"
    "            'POOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || pool_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_pool_id\n"
    "    FROM\n"
    "        " _DB_TABLE_FSS " ;\n"
    "CREATE VIEW " _DB_TABLE_BATS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'BAT_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_bat_id,\n"
    "        name,\n"
    "        type,\n"
    "        status\n"
    "    FROM\n"
    "        " _DB_TABLE_BATS " ;\n"
    "CREATE VIEW " _DB_TABLE_FS_SNAPS_VIEW " AS\n"
    "    SELECT\n"
    "        id,\n"
    "            'FS_SNAP_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_fs_snap_id,\n"
    "        name,\n"
    "        timestamp,\n"
    "        fs_id,\n"
    "            'FS_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || fs_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_fs_id\n"
    "    FROM\n"
    "        " _DB_TABLE_FS_SNAPS " ;\n"
    "CREATE VIEW " _DB_TABLE_VOLS_VIEW_BY_AG " AS\n"
    "    SELECT\n"
    "        vol.id,\n"
    "            'VOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || vol.id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_vol_id,\n"
    "        vol.vpd83,\n"
    "        vol.name,\n"
    "        vol.total_space,\n"
    "        vol.consumed_size,\n"
    "        vol.pool_id,\n"
    "            'POOL_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || vol.pool_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_pool_id,\n"
    "        vol.admin_state,\n"
    "        vol.is_hw_raid_vol,\n"
    "        vol_mask.ag_id ag_id,\n"
    "        vol.write_cache_policy,\n"
    "        vol.read_cache_policy,\n"
    "        vol.phy_disk_cache\n"
    "    FROM\n"
    "        volumes vol\n"
    "            LEFT JOIN " _DB_TABLE_VOL_MASKS " vol_mask\n"
    "                ON vol_mask.vol_id = vol.id;\n"
    "CREATE VIEW " _DB_TABLE_AGS_VIEW " AS\n"
    "    SELECT\n"
    "        ag.id,\n"
    "            'AG_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || ag.id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_ag_id,\n"
    "        ag.name,\n"
    "            CASE\n"
    "                WHEN count(DISTINCT init.init_type) = 1\n"
    "                    THEN init.init_type\n"
    "                WHEN count(DISTINCT init.init_type) = 2\n"
    "                    THEN " _LSM_ACCESS_GROUP_INIT_TYPE_ISCSI_WWPN_MIXED_STR
    "\n"
    "                ELSE " _LSM_ACCESS_GROUP_INIT_TYPE_UNKNOWN_STR "\n"
    "            END\n"
    "        init_type,\n"
    "        group_concat(init.id, '" _DB_LIST_SPLITTER "') init_ids_str\n"
    "    FROM\n"
    "        ags ag\n"
    "            LEFT JOIN " _DB_TABLE_INITS " init\n"
    "                ON ag.id = init.owner_ag_id\n"
    "    GROUP BY\n"
    "        ag.id\n"
    "    ORDER BY\n"
    "        init.init_type;\n"
    "CREATE VIEW " _DB_TABLE_AGS_VIEW_BY_VOL " AS\n"
    "    SELECT\n"
    "        ag_new.id,\n"
    "            'AG_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || ag_new.id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_ag_id,\n"
    "        ag_new.name,\n"
    "        ag_new.init_type,\n"
    "        ag_new.init_ids_str,\n"
    "        vol_mask.vol_id vol_id\n"
    "    FROM\n"
    "        (\n"
    "            SELECT\n"
    "                ag.id,\n"
    "                ag.name,\n"
    "                    CASE\n"
    "                        WHEN count(DISTINCT init.init_type) = 1\n"
    "                            THEN init.init_type\n"
    "                        WHEN count(DISTINCT init.init_type) = 2\n"
    "                            "
    "THEN " _LSM_ACCESS_GROUP_INIT_TYPE_ISCSI_WWPN_MIXED_STR "\n"
    "                        ELSE " _LSM_ACCESS_GROUP_INIT_TYPE_UNKNOWN_STR "\n"
    "                    END\n"
    "                init_type,\n"
    "                group_concat(init.id, '" _DB_LIST_SPLITTER
    "') init_ids_str\n"
    "            FROM\n"
    "                ags ag\n"
    "                    LEFT JOIN " _DB_TABLE_INITS " init\n"
    "                        ON ag.id = init.owner_ag_id\n"
    "            GROUP BY\n"
    "                ag.id\n"
    "            ORDER BY\n"
    "                init.init_type\n"
    "        ) ag_new\n"
    "            LEFT JOIN " _DB_TABLE_VOL_MASKS " vol_mask\n"
    "                ON vol_mask.ag_id = ag_new.id;\n"
    "CREATE VIEW " _DB_TABLE_NFS_EXPS_VIEW " AS\n"
    "    SELECT\n"
    "        exp.id,\n"
    "            'EXP_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || exp.id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_exp_id,\n"
    "        exp.fs_id,\n"
    "            'FS_ID_' || \n"
    "                SUBSTR('" _DB_ID_PADDING "' || exp.fs_id, \n"
    "                       -" _DB_ID_FMT_LEN_STR ", " _DB_ID_FMT_LEN_STR ")\n"
    "        lsm_fs_id,\n"
    "        exp.exp_path,\n"
    "        exp.auth_type,\n"
    "        exp.anon_uid,\n"
    "        exp.anon_gid,\n"
    "        exp.options,\n"
    "        exp2.exp_root_hosts_str,\n"
    "        exp3.exp_rw_hosts_str,\n"
    "        exp4.exp_ro_hosts_str\n"
    "    FROM\n"
    "        exps exp\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    exp_t2.id,\n"
    "                        group_concat(\n"
    "                            exp_root_host.host, '" _DB_LIST_SPLITTER "')\n"
    "                    exp_root_hosts_str\n"
    "                FROM\n"
    "                    exps exp_t2\n"
    "                    LEFT JOIN exp_root_hosts exp_root_host\n"
    "                        ON exp_t2.id = exp_root_host.exp_id\n"
    "                GROUP BY\n"
    "                    exp_t2.id\n"
    "            ) exp2\n"
    "                ON exp.id = exp2.id\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    exp_t3.id,\n"
    "                        group_concat(\n"
    "                            exp_rw_host.host, '" _DB_LIST_SPLITTER "')\n"
    "                    exp_rw_hosts_str\n"
    "                FROM\n"
    "                    exps exp_t3\n"
    "                    LEFT JOIN exp_rw_hosts exp_rw_host\n"
    "                        ON exp_t3.id = exp_rw_host.exp_id\n"
    "                GROUP BY\n"
    "                    exp_t3.id\n"
    "            ) exp3\n"
    "                ON exp.id = exp3.id\n"
    "            LEFT JOIN (\n"
    "                SELECT\n"
    "                    exp_t4.id,\n"
    "                        group_concat(\n"
    "                            exp_ro_host.host, '" _DB_LIST_SPLITTER "')\n"
    "                    exp_ro_hosts_str\n"
    "                FROM\n"
    "                    exps exp_t4\n"
    "                    LEFT JOIN exp_ro_hosts exp_ro_host\n"
    "                        ON exp_t4.id = exp_ro_host.exp_id\n"
    "                GROUP BY\n"
    "                    exp_t4.id\n"
    "            ) exp4\n"
    "                ON exp.id = exp4.id\n"
    "    GROUP BY\n"
    "        exp.id;\n";

#endif /* End of _SIMC_DB_TABLE_INIT_H_ */
