mirror of
https://github.com/philomena-dev/philomena.git
synced 2024-11-27 05:37:59 +01:00
47 lines
2 KiB
Makefile
47 lines
2 KiB
Makefile
DATABASE ?= philomena
|
|
OPENSEARCH_URL ?= http://localhost:9200/
|
|
ELASTICDUMP ?= elasticdump
|
|
.ONESHELL:
|
|
|
|
all: import_es
|
|
|
|
import_es: dump_jsonl
|
|
$(ELASTICDUMP) --input=filters.jsonl --output=$OPENSEARCH_URL --output-index=filters --limit 10000 --retryAttempts=5 --type=data --transform="doc._source = Object.assign({},doc); doc._id = doc.id"
|
|
|
|
dump_jsonl: metadata creators
|
|
psql $(DATABASE) -v ON_ERROR_STOP=1 -c 'copy (select temp_filters.jsonb_object_agg(object) from temp_filters.filter_search_json group by filter_id) to stdout;' > filters.jsonl
|
|
psql $(DATABASE) -v ON_ERROR_STOP=1 -c 'drop schema temp_filters cascade;'
|
|
sed -i filters.jsonl -e 's/\\\\/\\/g'
|
|
|
|
metadata: filter_search_json
|
|
psql $(DATABASE) -v ON_ERROR_STOP=1 <<-SQL
|
|
insert into temp_filters.filter_search_json (filter_id, object) select f.id, jsonb_build_object(
|
|
'id', f.id,
|
|
'created_at', f.created_at,
|
|
'user_id', f.user_id,
|
|
'public', f.public or f.system,
|
|
'system', f.system,
|
|
'name', lower(f.name),
|
|
'description', f.description,
|
|
'spoilered_count', array_length(f.spoilered_tag_ids, 1),
|
|
'hidden_count', array_length(f.hidden_tag_ids, 1),
|
|
'spoilered_tag_ids', f.spoilered_tag_ids,
|
|
'hidden_tag_ids', f.hidden_tag_ids,
|
|
'spoilered_complex_str', lower(f.spoilered_complex_str),
|
|
'hidden_complex_str', lower(f.hidden_complex_str),
|
|
'user_count', f.user_count
|
|
) from filters f;
|
|
SQL
|
|
|
|
creators: filter_search_json
|
|
psql $(DATABASE) -v ON_ERROR_STOP=1 <<-SQL
|
|
insert into temp_filters.filter_search_json (filter_id, object) select f.id, jsonb_build_object('creator', lower(u.name)) from filters f left join users u on f.user_id=u.id;
|
|
SQL
|
|
|
|
filter_search_json:
|
|
psql $(DATABASE) -v ON_ERROR_STOP=1 <<-SQL
|
|
drop schema if exists temp_filters cascade;
|
|
create schema temp_filters;
|
|
create unlogged table temp_filters.filter_search_json (filter_id bigint not null, object jsonb not null);
|
|
create or replace aggregate temp_filters.jsonb_object_agg(jsonb) (sfunc = 'jsonb_concat', stype = jsonb, initcond='{}');
|
|
SQL
|