lilToonのシェーダーリソースのDB（SQLite3）を作成するスクリプト

compare_original_custom.sql

SELECT
  i.base_name,
  c.base_name AS base_name_custom,
  sm.member_name,
  i.file_name AS file_name_internal,
  c.file_name AS file_name_container,
  i.shader_name,
  c.shader_name_fmt,
  i.tagval_render_type,
  c.tagval_render_type AS tagval_render_type_custom,
  i.tagval_queue,
  c.tagval_queue AS tagval_queue_custom,
  i.lil_render,
  c.lil_render AS lil_render_custom,
  i.sub_shader_brp,
  c.sub_shader_brp AS sub_shader_brp_custom,
  i.sub_shader_urp,
  c.sub_shader_urp AS sub_shader_urp_custom,
  i.sub_shader_hdrp,
  c.sub_shader_hdrp AS sub_shader_hdrp_custom
FROM
  m_lilinternal i
  LEFT JOIN m_shader_manager sm
    ON sm.shader_name = i.shader_name
  LEFT JOIN m_lilcontainer c
    ON c.base_name = i.base_name
  LEFT JOIN m_shader_manager_custom smc
    ON smc.shader_name_fmt = c.shader_name_fmt
WHERE
  i.tagval_render_type IS DISTINCT FROM c.tagval_render_type
  OR i.tagval_queue IS DISTINCT FROM c.tagval_queue
  OR i.lil_render IS DISTINCT FROM c.lil_render
  OR i.sub_shader_brp IS DISTINCT FROM c.sub_shader_brp
  OR i.sub_shader_urp IS DISTINCT FROM c.sub_shader_urp
  OR i.sub_shader_hdrp IS DISTINCT FROM c.sub_shader_hdrp
ORDER BY
  i.base_name
;

create_liltoon_db.bash

#!/bin/bash

set -eu

dir_script="$(dirname $0)"
cd "${dir_script}"

sqlite3=sqlite3
dbfile=lilToon.db

dir_lilinternal='/tmp/lilToon/Assets/lilToon/BaseShaderResources'
dir_lilcontainer='/tmp/Template/Shaders'
dir_resource='/tmp/lilToonResource'
dir_csv='./csv'

shader_manager="${dir_resource}/lilToon/Assets/lilToon/Editor/lilShaderManager.cs"
custom_inspector="${dir_resource}/Template/Editor/CustomInspector.cs"

tbl_lilinternal='m_lilinternal'
tbl_lilcontainer='m_lilcontainer'
tbl_shader_manager='m_shader_manager'
tbl_shader_manager_custom='m_shader_manager_custom'

csv_lilinternal="${dir_csv}/${tbl_lilinternal}.csv"
csv_lilcontainer="${dir_csv}/${tbl_lilcontainer}.csv"
csv_shader_manager="${dir_csv}/${tbl_shader_manager}.csv"
csv_shader_manager_custom="${dir_csv}/${tbl_shader_manager_custom}.csv"


##
## Download files.
##
mkdir -p "${dir_resource}"

if [ ! -d "${dir_resource}/lilToon" ]; then
  git clone 'https://github.com/lilxyzw/lilToon.git' "${dir_resource}/lilToon"
fi

if [ ! -d "${dir_resource}/Template" ]; then
  curl -L -o "${dir_resource}/Template.zip" 'https://github.com/lilxyzw/lilToon/raw/Document/files/Template.zip'
  unzip -d "${dir_resource}" "${dir_resource}/Template.zip"
fi


##
## Create CSV files
##
mkdir -p "${dir_csv}"

awk_script=$(cat << '__EOF__'
{
  if (match($0, /(Shader|lilPassShaderName|lilSubShader(B|LW|U|HD)RP)\s+"([^"]+)"/, g)) {
    d[g[1]] = g[3];
  }
  if (match($0, /"RenderType"\s*=\s*"([^"]+)"/, g)) {
    d["RenderType"] = g[1];
  }
  if (match($0, /"Queue"\s*=\s*"([^"]+)"/, g)) {
    d["Queue"] = g[1];
  }
  if (match($0, /#\s*define\s+LIL_RENDER\s+(\S+)/, g)) {
    d["lilRender"] = g[1];
  }
}

END {
  printf( \
    "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"\n", \
    bname, \
    fname, \
    d["Shader"], \
    d["RenderType"], \
    d["Queue"], \
    d["lilRender"], \
    d["lilPassShaderName"], \
    d["lilSubShaderBRP"], \
    d["lilSubShaderLWRP"], \
    d["lilSubShaderURP"], \
    d["lilSubShaderHDRP"]);
}
__EOF__
)

if [ ! -f "${csv_lilinternal}" ]; then
  echo "Create ${csv_lilinternal}"
  for fpath in "${dir_lilinternal}"/*.lilinternal; do
    fname="${fpath##*/}"
    bname="${fname%.*}"
    awk -v "bname=${bname}" -v "fname=${fname}" "${awk_script}" "${fpath}"
  done > "${csv_lilinternal}"
fi

if [ ! -f "${csv_lilcontainer}" ]; then
  echo "Create ${csv_lilcontainer}"
  for fpath in ${dir_lilcontainer}/*.lilcontainer; do
    fname="${fpath##*/}"
    bname="${fname%.*}"
    awk -v "bname=${bname}" -v "fname=${fname}" "${awk_script}" "${fpath}"
  done > "${csv_lilcontainer}"
fi

if [ ! -f "${csv_shader_manager}" ]; then
  echo "Create ${csv_shader_manager}"
  grep -v 'public' "${shader_manager}" \
    | sed -n -E 's/^ *([^ ]*) *= *Shader\.Find\("(.*)"\);/"\1","\2"/p' > ${csv_shader_manager}
fi
if [ ! -f "${csv_shader_manager_custom}" ]; then
  echo "Create ${csv_shader_manager_custom}"
  grep -e 'shaderName' "${custom_inspector}" \
    | sed -n -E 's/^ *([^ ]*) *= *Shader\.Find\(("([^"]*)" \+ )?shaderName \+ "([^"]*)"\);/"\1","\3\*LIL_SHADER_NAME\*\4"/p' > ${csv_shader_manager_custom}
fi


##
## Create database and tables.
##
echo "Create database: ${dbfile}"
"${sqlite3}" ${dbfile} << __EOF__
DROP TABLE IF EXISTS ${tbl_lilinternal};
CREATE TABLE ${tbl_lilinternal} (
  base_name TEXT NOT NULL,  /* File name without extension */
  file_name TEXT NOT NULL,  /* File name (*.lilinternal) */
  shader_name TEXT NOT NULL,  /* Shader name */
  tagval_render_type TEXT,  /* Tag value of "RenderType" */
  tagval_queue TEXT,  /* Tag value of "Queue" */
  lil_render INTEGER,  /* Value of LIL_RENDER */
  pass_shader_name TEXT,  /* Value of lilPassShaderName */
  sub_shader_brp TEXT,  /* Value of lilSubShaderBRP */
  sub_shader_lwrp TEXT,  /* Value of lilSubShaderLWRP */
  sub_shader_urp TEXT,  /* Value of lilSubShaderURP */
  sub_shader_hdrp TEXT,  /* Value of lilSubShaderHDRP */
  PRIMARY KEY (base_name)
) WITHOUT ROWID, STRICT;
CREATE INDEX ${tbl_lilinternal}_idx01 ON ${tbl_lilinternal} (shader_name);

DROP TABLE IF EXISTS ${tbl_lilcontainer};
CREATE TABLE ${tbl_lilcontainer} (
  base_name TEXT NOT NULL,  /* File name without extension */
  file_name TEXT NOT NULL,  /* File name (*.lilcontainer) */
  shader_name_fmt TEXT NOT NULL,  /* Shader name format */
  tagval_render_type TEXT,  /* Tag value of "RenderType" */
  tagval_queue TEXT,  /* Tag value of "Queue" */
  lil_render INTEGER,  /* Value of LIL_RENDER */
  pass_shader_name_fmt TEXT,  /* Value of lilPassShaderName */
  sub_shader_brp TEXT,  /* Value of lilSubShaderBRP */
  sub_shader_lwrp TEXT,  /* Value of lilSubShaderLWRP */
  sub_shader_urp TEXT,  /* Value of lilSubShaderURP */
  sub_shader_hdrp TEXT,  /* Value of lilSubShaderHDRP */
  PRIMARY KEY (base_name)
) WITHOUT ROWID, STRICT;
CREATE INDEX ${tbl_lilcontainer}_idx01 ON ${tbl_lilcontainer} (shader_name_fmt);

DROP TABLE IF EXISTS ${tbl_shader_manager};
CREATE TABLE ${tbl_shader_manager} (
  member_name TEXT NOT NULL,  /* Member name in lilShaderManager */
  shader_name TEXT NOT NULL,  /* Shader name */
  PRIMARY KEY (member_name),
  FOREIGN KEY (shader_name) REFERENCES ${tbl_lilinternal} (shader_name)
) WITHOUT ROWID, STRICT;
CREATE INDEX ${tbl_shader_manager}_idx01 ON ${tbl_shader_manager} (shader_name);

DROP TABLE IF EXISTS ${tbl_shader_manager_custom};
CREATE TABLE ${tbl_shader_manager_custom} (
  member_name TEXT NOT NULL,  /* Member name in lilShaderManager */
  shader_name_fmt TEXT NOT NULL,  /* Shader name format */
  PRIMARY KEY (member_name),
  FOREIGN KEY (shader_name_fmt) REFERENCES ${tbl_lilinternal} (shader_name_fmt)
) WITHOUT ROWID, STRICT;
CREATE INDEX ${tbl_shader_manager_custom}_idx01 ON ${tbl_shader_manager_custom} (shader_name_fmt);
__EOF__


##
## Import CSV files.
##
for fpath in "${dir_csv}"/*.csv; do
  fname="${fpath##*/}"
  tblname="${fname%.*}"

  echo ">>> Start import ${fpath} to ${tblname}"

  cols=$("${sqlite3}" "${dbfile}" "SELECT STRING_AGG(name, ', ' ORDER BY cid) AS concat_cols FROM pragma_table_info('${tblname}');")
  cols_for_create=$("${sqlite3}" "${dbfile}" "SELECT STRING_AGG(name || ' TEXT', ', ' ORDER BY cid) AS concat_cols FROM pragma_table_info('${tblname}');")
  cols_for_select=$("${sqlite3}" "${dbfile}" << __EOF__
SELECT
  STRING_AGG(
    CASE
      WHEN should_cast THEN 'CAST(NULLIF(' || name || ', '''') AS ' || type || ')'
      WHEN is_notnull THEN name
      ELSE 'NULLIF(' || name || ', '''')'
      END || ' AS ' || name,
    ', ' ORDER BY cid) AS concat_cols_for_select
FROM
  (
    SELECT
      cid,
      name,
      type,
      type LIKE '%INT%' OR (type NOT LIKE '%TEXT%' AND type NOT LIKE '%CHAR%' AND type NOT LIKE '%CLOB%') AS should_cast,
      "notnull" = 1 OR pk = 1 AS is_notnull
    FROM
      pragma_table_info('${tblname}')
  )
;
__EOF__
)

  "${sqlite3}" "${dbfile}" << __EOF__
.bail on
.echo on
ATTACH DATABASE ':memory:' AS imp;

BEGIN;

CREATE TABLE imp.${tblname} (${cols_for_create});
.changes on
.import --csv --schema imp '${fpath}' ${tblname}
INSERT INTO ${tblname} (${cols}) SELECT ${cols_for_select} FROM imp.${tblname};

COMMIT;

REINDEX;
VACUUM;
ANALYZE;
__EOF__

  echo "<<< Finish import ${fpath} to ${tblname}"
done