JournalList/init.sql at main · brownwolf1355/JournalList · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*
 * init.sql - JournalList SQLite webcrawl sqlite3 init script
 *
 * Copyright (c) 2021 Brown Wolf Consulting LLC
 * License: Creative Commons Attribution-NonCommercial-ShareAlike license. See: https://creativecommons.org/
 *
 * Create intermediate tables: list and count by attribute.
 *
 */
CREATE TABLE member_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE belongto_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE control_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE controlledby_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE vendor_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE customer_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE jlctrl_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE trust_files ("srcurl" TEXT);
CREATE TABLE missctrlby_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
/*
 * Create output tables: symmetric links, list of associations, publishers, vendors, and controlled urls, and statistics.
 *
 */
CREATE TABLE symmetric_list ("srcurl1" TEXT, "attr1" TEXT, "refurl1" TEXT, "srcurl2" TEXT, "attr2" TEXT, "refurl2" TEXT);
CREATE TABLE asymmetric_list ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE associations_list ("srcurl" TEXT);
CREATE TABLE publishers_list ("srcurl" TEXT);
CREATE TABLE vendors_list ("srcurl" TEXT);
CREATE TABLE controlled_list ("srcurl" TEXT);
CREATE TABLE control_dups ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE controlledby_dups ("srcurl" TEXT, "attr" TEXT, "refurl" TEXT);
CREATE TABLE temp_list ("srcurl" TEXT);
create table stats ("title" TEXT, "count" INTEGER);
/*
 * Set mode and turn headers on.
 *
 */
.mode csv
.headers on
/*
 * Import known errors
 *
 */
.import known_err.csv known_err