| 1 |
27955
|
claudio.at
|
--
|
| 2 |
|
|
-- sqoopQL database dump
|
| 3 |
|
|
--
|
| 4 |
42734
|
eri.katsar
|
|
| 5 |
27955
|
claudio.at
|
SET statement_timeout = 0;
|
| 6 |
|
|
SET client_encoding = 'UTF8';
|
| 7 |
|
|
SET standard_conforming_strings = on;
|
| 8 |
|
|
SET check_function_bodies = false;
|
| 9 |
|
|
SET client_min_messages = warning;
|
| 10 |
|
|
|
| 11 |
|
|
--
|
| 12 |
|
|
-- Name: shadow; Type: SCHEMA; Schema: -; Owner: sqoop
|
| 13 |
|
|
--
|
| 14 |
|
|
|
| 15 |
45523
|
tsampikos.
|
DROP SCHEMA IF EXISTS backup CASCADE;
|
| 16 |
|
|
|
| 17 |
27955
|
claudio.at
|
--
|
| 18 |
|
|
-- Name: shadow; Type: SCHEMA; Schema: -; Owner: sqoop
|
| 19 |
|
|
--
|
| 20 |
|
|
DROP SCHEMA IF EXISTS shadow CASCADE;
|
| 21 |
|
|
CREATE SCHEMA shadow;
|
| 22 |
|
|
|
| 23 |
|
|
--ALTER SCHEMA shadow OWNER TO sqoop;
|
| 24 |
|
|
|
| 25 |
|
|
SET search_path = shadow, pg_catalog;
|
| 26 |
|
|
|
| 27 |
|
|
|
| 28 |
|
|
--
|
| 29 |
|
|
-- Name: concept; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
|
| 30 |
|
|
--
|
| 31 |
|
|
|
| 32 |
|
|
CREATE TABLE shadow.concept (
|
| 33 |
|
|
id text NOT NULL,
|
| 34 |
30977
|
eri.katsar
|
name text ,
|
| 35 |
|
|
category text
|
| 36 |
27955
|
claudio.at
|
);
|
| 37 |
|
|
|
| 38 |
|
|
|
| 39 |
42734
|
eri.katsar
|
|
| 40 |
27955
|
claudio.at
|
--
|
| 41 |
|
|
-- Name: datasource; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 42 |
|
|
--
|
| 43 |
|
|
|
| 44 |
|
|
CREATE TABLE shadow.datasource (
|
| 45 |
|
|
id text NOT NULL,
|
| 46 |
30977
|
eri.katsar
|
name text ,
|
| 47 |
27955
|
claudio.at
|
type text,
|
| 48 |
|
|
compatibility text,
|
| 49 |
55644
|
antonis.le
|
latitude text,
|
| 50 |
|
|
longitude text,
|
| 51 |
27955
|
claudio.at
|
dateofvalidation text,
|
| 52 |
|
|
yearofvalidation integer,
|
| 53 |
42734
|
eri.katsar
|
harvested TEXT DEFAULT 'false',
|
| 54 |
54431
|
tsampikos.
|
piwik_id integer
|
| 55 |
27955
|
claudio.at
|
);
|
| 56 |
|
|
|
| 57 |
|
|
--
|
| 58 |
|
|
-- Name: datasource_languages; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 59 |
|
|
--
|
| 60 |
|
|
|
| 61 |
32831
|
eri.katsar
|
CREATE TABLE shadow.datasource_languages (
|
| 62 |
27955
|
claudio.at
|
id text NOT NULL,
|
| 63 |
42734
|
eri.katsar
|
language text
|
| 64 |
27955
|
claudio.at
|
);
|
| 65 |
|
|
|
| 66 |
54431
|
tsampikos.
|
--
|
| 67 |
|
|
-- Name: datasource_websites; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 68 |
|
|
--
|
| 69 |
42734
|
eri.katsar
|
|
| 70 |
54431
|
tsampikos.
|
CREATE TABLE shadow.datasource_websites (
|
| 71 |
|
|
id text NOT NULL,
|
| 72 |
|
|
website text
|
| 73 |
|
|
);
|
| 74 |
|
|
|
| 75 |
27955
|
claudio.at
|
--
|
| 76 |
|
|
-- Name: datasource_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 77 |
|
|
--
|
| 78 |
|
|
|
| 79 |
29265
|
eri.katsar
|
--
|
| 80 |
27955
|
claudio.at
|
CREATE TABLE shadow.datasource_organizations (
|
| 81 |
|
|
id text NOT NULL,
|
| 82 |
|
|
organization text NOT NULL
|
| 83 |
|
|
);
|
| 84 |
|
|
|
| 85 |
|
|
|
| 86 |
42734
|
eri.katsar
|
|
| 87 |
27955
|
claudio.at
|
--
|
| 88 |
|
|
-- Name: result_datasources; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 89 |
|
|
--
|
| 90 |
|
|
|
| 91 |
|
|
CREATE TABLE shadow.result_datasources (
|
| 92 |
|
|
id text NOT NULL,
|
| 93 |
|
|
datasource text NOT NULL
|
| 94 |
|
|
);
|
| 95 |
|
|
|
| 96 |
|
|
|
| 97 |
42734
|
eri.katsar
|
|
| 98 |
27955
|
claudio.at
|
--
|
| 99 |
|
|
-- Name: datasource_results; Type: VIEW; Schema: shadow; Owner: sqoop
|
| 100 |
|
|
--
|
| 101 |
|
|
|
| 102 |
47072
|
tsampikos.
|
--CREATE VIEW shadow.datasource_results AS
|
| 103 |
|
|
-- SELECT result_datasources.datasource AS id, result_datasources.id AS result FROM result_datasources;
|
| 104 |
27955
|
claudio.at
|
|
| 105 |
|
|
--
|
| 106 |
42734
|
eri.katsar
|
-- Name: datasource_topics; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 107 |
27955
|
claudio.at
|
-- Name: defaults; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 108 |
|
|
--
|
| 109 |
|
|
|
| 110 |
|
|
CREATE TABLE shadow.defaults (
|
| 111 |
|
|
tablename text NOT NULL,
|
| 112 |
|
|
xaxis text,
|
| 113 |
|
|
yaxis text,
|
| 114 |
|
|
agg text,
|
| 115 |
|
|
zaxis text,
|
| 116 |
|
|
type text,
|
| 117 |
|
|
tableorder integer NOT NULL
|
| 118 |
|
|
);
|
| 119 |
|
|
|
| 120 |
|
|
|
| 121 |
|
|
--
|
| 122 |
|
|
-- Name: category; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
|
| 123 |
|
|
--
|
| 124 |
|
|
CREATE TABLE shadow.category (
|
| 125 |
|
|
id text NOT NULL,
|
| 126 |
30977
|
eri.katsar
|
name text,
|
| 127 |
|
|
context text
|
| 128 |
27955
|
claudio.at
|
);
|
| 129 |
|
|
|
| 130 |
|
|
--
|
| 131 |
|
|
-- Name: claim; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
|
| 132 |
|
|
--
|
| 133 |
|
|
|
| 134 |
|
|
CREATE TABLE shadow.claim (
|
| 135 |
|
|
id text NOT NULL,
|
| 136 |
30977
|
eri.katsar
|
date text ,
|
| 137 |
|
|
userid text
|
| 138 |
27955
|
claudio.at
|
);
|
| 139 |
|
|
|
| 140 |
|
|
|
| 141 |
|
|
--
|
| 142 |
|
|
-- Name: context; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
|
| 143 |
|
|
--
|
| 144 |
|
|
|
| 145 |
|
|
CREATE TABLE shadow.context (
|
| 146 |
|
|
id text NOT NULL,
|
| 147 |
30977
|
eri.katsar
|
name text
|
| 148 |
27955
|
claudio.at
|
);
|
| 149 |
42734
|
eri.katsar
|
|
| 150 |
27955
|
claudio.at
|
--
|
| 151 |
|
|
-- Name: organization; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 152 |
|
|
--
|
| 153 |
|
|
|
| 154 |
|
|
CREATE TABLE shadow.organization (
|
| 155 |
|
|
id text NOT NULL,
|
| 156 |
30977
|
eri.katsar
|
name text,
|
| 157 |
54431
|
tsampikos.
|
country text
|
| 158 |
27955
|
claudio.at
|
);
|
| 159 |
|
|
|
| 160 |
|
|
|
| 161 |
|
|
|
| 162 |
|
|
--
|
| 163 |
|
|
-- Name: organization_datasources; Type: VIEW; Schema: shadow; Owner: sqoop
|
| 164 |
|
|
--
|
| 165 |
|
|
|
| 166 |
|
|
CREATE VIEW shadow.organization_datasources AS
|
| 167 |
|
|
SELECT datasource_organizations.organization AS id, datasource_organizations.id AS datasource FROM datasource_organizations;
|
| 168 |
|
|
|
| 169 |
|
|
--
|
| 170 |
|
|
-- Name: project_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 171 |
|
|
--
|
| 172 |
|
|
|
| 173 |
|
|
CREATE TABLE shadow.project_organizations (
|
| 174 |
|
|
id text NOT NULL,
|
| 175 |
|
|
organization text NOT NULL
|
| 176 |
|
|
);
|
| 177 |
|
|
|
| 178 |
|
|
|
| 179 |
43954
|
tsampikos.
|
--
|
| 180 |
|
|
-- Name: project_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 181 |
|
|
--
|
| 182 |
27955
|
claudio.at
|
|
| 183 |
43954
|
tsampikos.
|
CREATE TABLE shadow.result_organizations (
|
| 184 |
|
|
id text NOT NULL,
|
| 185 |
|
|
organization text NOT NULL
|
| 186 |
|
|
);
|
| 187 |
|
|
|
| 188 |
|
|
|
| 189 |
27955
|
claudio.at
|
--
|
| 190 |
|
|
-- Name: organization_projects; Type: VIEW; Schema: shadow; Owner: sqoop
|
| 191 |
|
|
--
|
| 192 |
|
|
|
| 193 |
|
|
CREATE VIEW shadow.organization_projects AS
|
| 194 |
|
|
SELECT project_organizations.id AS project, project_organizations.organization AS id FROM project_organizations;
|
| 195 |
|
|
|
| 196 |
|
|
--
|
| 197 |
|
|
-- Name: project; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 198 |
|
|
--
|
| 199 |
|
|
|
| 200 |
|
|
CREATE TABLE shadow.project (
|
| 201 |
|
|
id text NOT NULL,
|
| 202 |
30977
|
eri.katsar
|
acronym text ,
|
| 203 |
34210
|
eri.katsar
|
title text ,
|
| 204 |
36921
|
eri.katsar
|
funder text,
|
| 205 |
27955
|
claudio.at
|
funding_lvl0 text,
|
| 206 |
|
|
funding_lvl1 text,
|
| 207 |
|
|
funding_lvl2 text,
|
| 208 |
54431
|
tsampikos.
|
funding_lvl3 text,
|
| 209 |
27955
|
claudio.at
|
sc39 text,
|
| 210 |
45523
|
tsampikos.
|
type text,
|
| 211 |
27955
|
claudio.at
|
start_year integer,
|
| 212 |
|
|
end_year integer,
|
| 213 |
|
|
duration integer,
|
| 214 |
|
|
haspubs text DEFAULT 'no'::text,
|
| 215 |
|
|
numpubs integer,
|
| 216 |
|
|
enddate text,
|
| 217 |
|
|
startdate text,
|
| 218 |
|
|
daysforlastpub integer,
|
| 219 |
|
|
delayedpubs integer,
|
| 220 |
42734
|
eri.katsar
|
callidentifier text,
|
| 221 |
54431
|
tsampikos.
|
code text
|
| 222 |
27955
|
claudio.at
|
);
|
| 223 |
|
|
|
| 224 |
|
|
--
|
| 225 |
|
|
-- Name: result_projects; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 226 |
|
|
--
|
| 227 |
|
|
|
| 228 |
42734
|
eri.katsar
|
CREATE TABLE shadow.project_oids (
|
| 229 |
|
|
id text NOT NULL,
|
| 230 |
45523
|
tsampikos.
|
orid text
|
| 231 |
42734
|
eri.katsar
|
);
|
| 232 |
27955
|
claudio.at
|
|
| 233 |
|
|
|
| 234 |
42734
|
eri.katsar
|
|
| 235 |
|
|
CREATE TABLE shadow.result_oids (
|
| 236 |
|
|
id text NOT NULL,
|
| 237 |
45523
|
tsampikos.
|
orid text
|
| 238 |
42734
|
eri.katsar
|
);
|
| 239 |
|
|
|
| 240 |
|
|
|
| 241 |
|
|
CREATE TABLE shadow.organization_oids (
|
| 242 |
|
|
id text NOT NULL,
|
| 243 |
45523
|
tsampikos.
|
orid text
|
| 244 |
42734
|
eri.katsar
|
);
|
| 245 |
|
|
|
| 246 |
|
|
|
| 247 |
|
|
CREATE TABLE shadow.datasource_oids (
|
| 248 |
|
|
id text NOT NULL,
|
| 249 |
45523
|
tsampikos.
|
orid text
|
| 250 |
42734
|
eri.katsar
|
);
|
| 251 |
|
|
|
| 252 |
48302
|
tsampikos.
|
-- CREATE TABLE shadow.person_oids (
|
| 253 |
|
|
-- id text NOT NULL,
|
| 254 |
|
|
-- orid text
|
| 255 |
|
|
-- );
|
| 256 |
42734
|
eri.katsar
|
|
| 257 |
|
|
|
| 258 |
|
|
CREATE TABLE shadow.project_keywords (
|
| 259 |
|
|
id text NOT NULL,
|
| 260 |
|
|
keyword text
|
| 261 |
|
|
);
|
| 262 |
|
|
|
| 263 |
|
|
|
| 264 |
|
|
CREATE TABLE shadow.project_subjects (
|
| 265 |
|
|
id text NOT NULL,
|
| 266 |
|
|
subject text
|
| 267 |
|
|
);
|
| 268 |
|
|
|
| 269 |
|
|
|
| 270 |
27955
|
claudio.at
|
--ALTER TABLE shadow.result_projects OWNER TO sqoop;
|
| 271 |
|
|
|
| 272 |
|
|
CREATE TABLE shadow.project_results (
|
| 273 |
|
|
id text NOT NULL,
|
| 274 |
|
|
result text NOT NULL,
|
| 275 |
|
|
daysfromend integer
|
| 276 |
|
|
);
|
| 277 |
|
|
|
| 278 |
|
|
|
| 279 |
|
|
--
|
| 280 |
|
|
-- Name: result; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 281 |
|
|
--
|
| 282 |
|
|
|
| 283 |
|
|
CREATE TABLE shadow.result (
|
| 284 |
54431
|
tsampikos.
|
id text NOT NULL,
|
| 285 |
42734
|
eri.katsar
|
publisher text,
|
| 286 |
|
|
journal text,
|
| 287 |
|
|
year integer,
|
| 288 |
|
|
date text ,
|
| 289 |
55644
|
antonis.le
|
-- access_mode text,
|
| 290 |
42734
|
eri.katsar
|
bestlicense text,
|
| 291 |
|
|
type text ,
|
| 292 |
|
|
embargo_end_date text,
|
| 293 |
|
|
delayed text,
|
| 294 |
54431
|
tsampikos.
|
authors integer
|
| 295 |
27955
|
claudio.at
|
);
|
| 296 |
|
|
|
| 297 |
54431
|
tsampikos.
|
--
|
| 298 |
|
|
-- Name: result_extra; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 299 |
|
|
--
|
| 300 |
27955
|
claudio.at
|
|
| 301 |
54431
|
tsampikos.
|
CREATE TABLE shadow.result_extra (
|
| 302 |
|
|
id text NOT NULL,
|
| 303 |
|
|
title text,
|
| 304 |
|
|
source text
|
| 305 |
|
|
);
|
| 306 |
27955
|
claudio.at
|
|
| 307 |
|
|
--
|
| 308 |
|
|
-- Name: result_claims; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 309 |
|
|
--
|
| 310 |
|
|
|
| 311 |
|
|
CREATE TABLE shadow.result_claims (
|
| 312 |
|
|
id text NOT NULL,
|
| 313 |
|
|
claim text NOT NULL
|
| 314 |
|
|
);
|
| 315 |
|
|
|
| 316 |
47072
|
tsampikos.
|
CREATE TABLE shadow.result_citations (
|
| 317 |
42734
|
eri.katsar
|
id text NOT NULL,
|
| 318 |
47072
|
tsampikos.
|
result text NOT NULL
|
| 319 |
|
|
-- trust text,
|
| 320 |
|
|
-- provenance text,
|
| 321 |
|
|
-- citation text
|
| 322 |
42734
|
eri.katsar
|
);
|
| 323 |
27955
|
claudio.at
|
|
| 324 |
|
|
|
| 325 |
|
|
--
|
| 326 |
|
|
-- Name: result_classifications; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 327 |
|
|
--
|
| 328 |
|
|
|
| 329 |
|
|
CREATE TABLE shadow.result_classifications (
|
| 330 |
|
|
id text NOT NULL,
|
| 331 |
|
|
type text NOT NULL
|
| 332 |
|
|
);
|
| 333 |
|
|
|
| 334 |
|
|
--
|
| 335 |
|
|
-- Name: result_concepts; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 336 |
|
|
--
|
| 337 |
|
|
|
| 338 |
|
|
CREATE TABLE shadow.result_concepts (
|
| 339 |
|
|
id text NOT NULL,
|
| 340 |
|
|
concept text NOT NULL
|
| 341 |
|
|
);
|
| 342 |
|
|
|
| 343 |
|
|
--
|
| 344 |
|
|
-- Name: result_languages; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 345 |
|
|
--
|
| 346 |
|
|
|
| 347 |
32831
|
eri.katsar
|
CREATE TABLE shadow.result_languages (
|
| 348 |
27955
|
claudio.at
|
id text NOT NULL,
|
| 349 |
|
|
language text NOT NULL
|
| 350 |
|
|
);
|
| 351 |
|
|
|
| 352 |
|
|
|
| 353 |
|
|
--
|
| 354 |
|
|
-- Name: result_results; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 355 |
|
|
--
|
| 356 |
|
|
|
| 357 |
|
|
CREATE TABLE shadow.result_results (
|
| 358 |
|
|
id text NOT NULL,
|
| 359 |
|
|
result text NOT NULL
|
| 360 |
|
|
);
|
| 361 |
|
|
|
| 362 |
|
|
|
| 363 |
|
|
|
| 364 |
|
|
--
|
| 365 |
|
|
-- Name: result_topics; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
|
| 366 |
|
|
--
|
| 367 |
|
|
|
| 368 |
|
|
CREATE TABLE shadow.result_topics (
|
| 369 |
|
|
id text NOT NULL,
|
| 370 |
42734
|
eri.katsar
|
topic text
|
| 371 |
27955
|
claudio.at
|
);
|
| 372 |
|
|
|
| 373 |
|
|
|
| 374 |
42734
|
eri.katsar
|
CREATE TABLE shadow.result_descriptions (
|
| 375 |
|
|
id text NOT NULL,
|
| 376 |
|
|
description text
|
| 377 |
|
|
);
|
| 378 |
27955
|
claudio.at
|
|
| 379 |
42734
|
eri.katsar
|
CREATE TABLE shadow.result_pids (
|
| 380 |
|
|
id text NOT NULL,
|
| 381 |
|
|
type text ,
|
| 382 |
|
|
pid text
|
| 383 |
|
|
);
|
| 384 |
|
|
|
| 385 |
55644
|
antonis.le
|
CREATE TABLE shadow.country (
|
| 386 |
|
|
code text NOT NULL,
|
| 387 |
|
|
name text,
|
| 388 |
|
|
continent_code text,
|
| 389 |
|
|
continent_name text
|
| 390 |
42734
|
eri.katsar
|
|
| 391 |
55644
|
antonis.le
|
);
|
| 392 |
42734
|
eri.katsar
|
|
| 393 |
55644
|
antonis.le
|
-----------
|
| 394 |
|
|
--functions
|
| 395 |
|
|
-----------
|
| 396 |
|
|
CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 397 |
|
|
BEGIN
|
| 398 |
42734
|
eri.katsar
|
|
| 399 |
55644
|
antonis.le
|
INSERT INTO "shadow".datasource (id,name,type,compatibility) (SELECT 'other','Other','Repository','unknown' WHERE NOT EXISTS (SELECT 1 FROM "shadow".datasource WHERE name='Unknown Repository'));
|
| 400 |
|
|
UPDATE "shadow".datasource SET name='Other' where name='Unknown Repository';
|
| 401 |
|
|
UPDATE "shadow".result_datasources SET datasource=(SELECT id FROM "shadow".datasource WHERE name='Other') WHERE datasource NOT IN (SELECT id FROM "shadow".datasource);
|
| 402 |
42734
|
eri.katsar
|
|
| 403 |
55644
|
antonis.le
|
TRUNCATE TABLE "shadow".defaults;
|
| 404 |
|
|
INSERT INTO "shadow".defaults VALUES ('result', 'year', 'number', 'count', '', 'column', 1);
|
| 405 |
|
|
INSERT INTO "shadow".defaults VALUES ('project', 'funding_lvl1', 'number', 'count', '', 'column', 2);
|
| 406 |
|
|
INSERT INTO "shadow".defaults VALUES ('organization', 'country', 'number', 'count', '', 'column', 3);
|
| 407 |
|
|
INSERT INTO "shadow".defaults VALUES ('datasource', 'oa_compatible', 'number', 'count', '', 'column', 4);
|
| 408 |
42734
|
eri.katsar
|
|
| 409 |
55644
|
antonis.le
|
UPDATE "shadow".datasource SET yearofvalidation=null WHERE yearofvalidation='-1';
|
| 410 |
|
|
UPDATE shadow.datasource set harvested ='true' WHERE datasource.id IN (SELECT DISTINCT d.id FROM datasource d, result_datasources rd where d.id=rd.datasource);
|
| 411 |
|
|
END $BODY$;
|
| 412 |
42734
|
eri.katsar
|
|
| 413 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.extra_defaults_datasource() OWNER TO sqoop;
|
| 414 |
34208
|
eri.katsar
|
|
| 415 |
42734
|
eri.katsar
|
|
| 416 |
27955
|
claudio.at
|
--
|
| 417 |
29888
|
eri.katsar
|
-- Project Results Extra Inserts
|
| 418 |
|
|
--
|
| 419 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.update_project_results() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 420 |
|
|
BEGIN
|
| 421 |
|
|
CREATE OR REPLACE VIEW shadow.project_results_publication AS
|
| 422 |
|
|
SELECT "shadow".result_projects.id AS result, "shadow".result_projects.project AS project_results, "shadow"."result"."date" as resultdate,
|
| 423 |
|
|
"shadow"."project"."enddate" as projectenddate, "shadow".result_projects.daysfromend as daysfromend
|
| 424 |
|
|
FROM "shadow".result_projects, "shadow".result, "shadow".project
|
| 425 |
|
|
where "shadow".result_projects.id="shadow".result.id and "shadow".result.type='publication' and "shadow".project.id= "shadow".result_projects.project;
|
| 426 |
|
|
END $BODY$;
|
| 427 |
29888
|
eri.katsar
|
|
| 428 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.update_project_results() OWNER TO sqoop;
|
| 429 |
42734
|
eri.katsar
|
|
| 430 |
|
|
|
| 431 |
|
|
|
| 432 |
29888
|
eri.katsar
|
--
|
| 433 |
|
|
-- Project Has Publications Extra Inserts
|
| 434 |
|
|
--
|
| 435 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.project_has_pubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 436 |
|
|
BEGIN
|
| 437 |
|
|
UPDATE shadow.project SET haspubs='yes' WHERE id IN (SELECT pr.id FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='publication');
|
| 438 |
|
|
END $BODY$;
|
| 439 |
42734
|
eri.katsar
|
|
| 440 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.project_has_pubs() OWNER TO sqoop;
|
| 441 |
42734
|
eri.katsar
|
|
| 442 |
29888
|
eri.katsar
|
|
| 443 |
|
|
--
|
| 444 |
|
|
-- Project Publications Count Updates
|
| 445 |
|
|
--
|
| 446 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.project_pubs_count() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 447 |
|
|
BEGIN
|
| 448 |
|
|
UPDATE shadow.project p SET numpubs=prr.np FROM (SELECT pr.id, count(distinct pr.result) AS np FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='publication' GROUP BY pr.id) AS prr WHERE prr.id=p.id;
|
| 449 |
|
|
END $BODY$;
|
| 450 |
29888
|
eri.katsar
|
|
| 451 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.project_pubs_count() OWNER TO sqoop;
|
| 452 |
29888
|
eri.katsar
|
|
| 453 |
|
|
|
| 454 |
42734
|
eri.katsar
|
|
| 455 |
29888
|
eri.katsar
|
--
|
| 456 |
|
|
-- Project Delayed Publications
|
| 457 |
|
|
--
|
| 458 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.project_delayedpubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 459 |
|
|
BEGIN
|
| 460 |
|
|
UPDATE shadow.project pp SET delayedpubs = prr.dp FROM (SELECT p.id, count(distinct r.id) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;
|
| 461 |
|
|
END $BODY$;
|
| 462 |
29888
|
eri.katsar
|
|
| 463 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.project_delayedpubs() OWNER TO sqoop;
|
| 464 |
29888
|
eri.katsar
|
|
| 465 |
|
|
|
| 466 |
|
|
--
|
| 467 |
|
|
-- Project daysforlastpub
|
| 468 |
|
|
--
|
| 469 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.project_daysforlastpub() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 470 |
|
|
BEGIN
|
| 471 |
|
|
UPDATE shadow.project pp SET daysforlastpub = prr.dp FROM (SELECT p.id, max(to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD')) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;
|
| 472 |
|
|
END $BODY$;
|
| 473 |
29888
|
eri.katsar
|
|
| 474 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.project_daysforlastpub() OWNER TO sqoop;
|
| 475 |
29888
|
eri.katsar
|
|
| 476 |
|
|
--
|
| 477 |
|
|
-- Project delayed
|
| 478 |
|
|
--
|
| 479 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.project_delayed () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 480 |
|
|
BEGIN
|
| 481 |
|
|
UPDATE shadow.result SET delayed = 'yes' WHERE id IN (SELECT distinct r.id FROM result r, project_results pr, project p WHERE r.id=pr.result AND pr.id=p.id AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0);
|
| 482 |
|
|
END $BODY$;
|
| 483 |
29888
|
eri.katsar
|
|
| 484 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.project_delayed() OWNER TO sqoop;
|
| 485 |
29888
|
eri.katsar
|
|
| 486 |
|
|
--
|
| 487 |
|
|
-- Cleaning Up Temps
|
| 488 |
29996
|
eri.katsar
|
--
|
| 489 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.cleanTemps () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 490 |
|
|
BEGIN
|
| 491 |
|
|
drop view if exists shadow.delayedpubs ;
|
| 492 |
|
|
drop view if exists shadow.project_pub_count ;
|
| 493 |
|
|
drop view if exists shadow.delayedpubs ;
|
| 494 |
|
|
drop view if exists shadow.project_results_publication ;
|
| 495 |
|
|
END $BODY$;
|
| 496 |
42734
|
eri.katsar
|
|
| 497 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.cleanTemps() OWNER TO sqoop;
|
| 498 |
42734
|
eri.katsar
|
|
| 499 |
29995
|
eri.katsar
|
--
|
| 500 |
54431
|
tsampikos.
|
-- Name: create_arrays(); Type FUNCTION; Schema: shadow; Owner: sqoop
|
| 501 |
|
|
--
|
| 502 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION shadow.create_arrays() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
| 503 |
|
|
BEGIN
|
| 504 |
54431
|
tsampikos.
|
|
| 505 |
55644
|
antonis.le
|
ALTER TABLE shadow.result ADD COLUMN funders text[], ADD COLUMN funding_lvl0 text[], ADD COLUMN projects text[], ADD COLUMN datasources text[];
|
| 506 |
|
|
UPDATE shadow.result r SET funders = prr.funders FROM (SELECT pr.result AS rid, array_agg(distinct funder) AS funders FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
|
| 507 |
|
|
UPDATE shadow.result r SET funding_lvl0 = prr.funding_lvl0 FROM (SELECT pr.result AS rid, array_agg(distinct funding_lvl0) AS funding_lvl0 FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
|
| 508 |
|
|
UPDATE shadow.result r SET projects = prr.ids FROM (SELECT pr.result AS rid, array_agg(distinct pr.id) AS ids FROM shadow.project_results pr GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
|
| 509 |
|
|
-- UPDATE shadow.result r SET datasources = drr.ids FROM (SELECT rd.id AS rid, array_agg(distinct rd.datasource) AS ids FROM shadow.result_datasources rd GROUP BY rd.id) AS drr WHERE r.id = drr.rid;
|
| 510 |
54431
|
tsampikos.
|
|
| 511 |
55644
|
antonis.le
|
CREATE TABLE shadow.result_temp AS SELECT r.id, publisher, journal, year, date, bestlicense, type, embargo_end_date, delayed, authors, funders, funding_lvl0, projects, array_agg(distinct rd.datasource) as datasources FROM shadow.result r, shadow.result_datasources rd WHERE r.id=rd.id GROUP BY r.id, r.publisher, r.journal, r.year, r.date, r.bestlicense, r.type, r.embargo_end_date, r.delayed, r.authors, r.funders, r.funding_lvl0, r.projects;
|
| 512 |
|
|
DROP TABLE shadow.result CASCADE;
|
| 513 |
|
|
ALTER TABLE shadow.result_temp RENAME TO result;
|
| 514 |
|
|
END $BODY$;
|
| 515 |
54431
|
tsampikos.
|
|
| 516 |
55644
|
antonis.le
|
ALTER FUNCTION shadow.create_arrays() OWNER TO sqoop;
|
| 517 |
54431
|
tsampikos.
|
|
| 518 |
|
|
--
|
| 519 |
29995
|
eri.katsar
|
-- Name: create_indexes(); Type: FUNCTION; Schema: shadow; Owner: sqoop
|
| 520 |
|
|
--
|
| 521 |
|
|
|
| 522 |
55644
|
antonis.le
|
CREATE FUNCTION shadow.create_indexes() RETURNS void LANGUAGE plpgsql AS $$
|
| 523 |
|
|
BEGIN
|
| 524 |
|
|
CREATE INDEX datasource_id ON "shadow".datasource USING btree (id);
|
| 525 |
|
|
CREATE INDEX datasource_type ON "shadow".datasource USING btree (type);
|
| 526 |
|
|
CREATE INDEX datasource_name ON "shadow".datasource USING btree (name);
|
| 527 |
|
|
CREATE INDEX datasource_piwik_id ON "shadow".datasource USING btree (piwik_id);
|
| 528 |
|
|
CREATE INDEX dtsrc_type ON shadow.datasource USING btree (type COLLATE pg_catalog."default");
|
| 529 |
|
|
CREATE INDEX dtsrc_name ON shadow.datasource USING btree (name COLLATE pg_catalog."default");
|
| 530 |
|
|
CREATE INDEX dtsrc_compatibility ON shadow.datasource USING btree (compatibility COLLATE pg_catalog."default");
|
| 531 |
29995
|
eri.katsar
|
|
| 532 |
55644
|
antonis.le
|
CREATE INDEX datasource_oids_id ON "shadow".datasource_oids USING btree (id COLLATE pg_catalog."default");
|
| 533 |
|
|
CREATE INDEX datasource_oids_orid ON "shadow".datasource_oids USING btree(orid COLLATE pg_catalog."default");
|
| 534 |
|
|
CREATE INDEX datasource_websites_id ON "shadow".datasource_websites USING btree(id COLLATE pg_catalog."default");
|
| 535 |
|
|
CREATE INDEX datasource_lang_id ON shadow.datasource_languages (id ASC NULLS LAST);
|
| 536 |
|
|
CREATE INDEX dtsrc_org_id ON shadow.datasource_organizations USING btree (id COLLATE pg_catalog."default");
|
| 537 |
|
|
CREATE INDEX dtsrc_org_org ON shadow.datasource_organizations USING btree (organization COLLATE pg_catalog."default");
|
| 538 |
42734
|
eri.katsar
|
|
| 539 |
55644
|
antonis.le
|
CREATE INDEX result_authors ON "shadow".result USING btree (authors);
|
| 540 |
|
|
CREATE INDEX result_id ON "shadow".result USING btree (id);
|
| 541 |
|
|
CREATE INDEX result_year ON "shadow".result USING btree (year);
|
| 542 |
|
|
CREATE INDEX result_date ON "shadow"."result" USING btree ("date");
|
| 543 |
|
|
CREATE INDEX result_type ON "shadow"."result" USING btree ("type");
|
| 544 |
|
|
CREATE INDEX res_bestlicense ON shadow.result USING btree (bestlicense COLLATE pg_catalog."default");
|
| 545 |
|
|
CREATE INDEX result_funders_idx ON shadow.result USING gin (funders);
|
| 546 |
|
|
CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0);
|
| 547 |
|
|
CREATE INDEX result_projects_idx ON shadow.result USING gin (projects);
|
| 548 |
|
|
CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources);
|
| 549 |
54431
|
tsampikos.
|
|
| 550 |
30019
|
eri.katsar
|
|
| 551 |
55644
|
antonis.le
|
CREATE INDEX project_acronym ON "shadow"."project" USING btree (acronym);
|
| 552 |
|
|
CREATE INDEX project_enddate ON "shadow"."project" USING btree (enddate);
|
| 553 |
|
|
CREATE INDEX project_id ON "shadow"."project" USING btree (id);
|
| 554 |
|
|
CREATE INDEX proj_funder ON shadow.project USING btree (funder COLLATE pg_catalog."default");
|
| 555 |
|
|
CREATE INDEX proj_title ON shadow.project USING btree (title COLLATE pg_catalog."default");
|
| 556 |
|
|
CREATE INDEX proj_fndlvl0 ON shadow.project USING btree (funding_lvl0 COLLATE pg_catalog."default");
|
| 557 |
|
|
CREATE INDEX proj_fndlvl1 ON shadow.project USING btree (funding_lvl1 COLLATE pg_catalog."default");
|
| 558 |
|
|
CREATE INDEX proj_fndlvl2 ON shadow.project USING btree (funding_lvl2 COLLATE pg_catalog."default");
|
| 559 |
|
|
CREATE INDEX proj_sc39 ON shadow.project USING btree (sc39 COLLATE pg_catalog."default");
|
| 560 |
|
|
CREATE INDEX proj_fndlvl3 ON shadow.project USING btree (funding_lvl3 COLLATE pg_catalog."default");
|
| 561 |
30019
|
eri.katsar
|
|
| 562 |
55644
|
antonis.le
|
CREATE INDEX project_results_result ON "shadow"."project_results" USING btree (result);
|
| 563 |
|
|
CREATE INDEX project_results_project ON "shadow"."project_results" USING btree (id);
|
| 564 |
38736
|
eri.katsar
|
|
| 565 |
55644
|
antonis.le
|
CREATE INDEX result_oids_id ON "shadow".result_oids USING btree (id COLLATE pg_catalog."default");
|
| 566 |
|
|
CREATE INDEX result_oids_oid ON "shadow".result_oids USING btree(orid COLLATE pg_catalog."default");
|
| 567 |
38736
|
eri.katsar
|
|
| 568 |
55644
|
antonis.le
|
CREATE INDEX result_pids_id ON "shadow".result_pids USING btree (id COLLATE pg_catalog."default");
|
| 569 |
|
|
CREATE INDEX result_pids_type ON "shadow".result_pids USING btree (type COLLATE pg_catalog."default");
|
| 570 |
|
|
CREATE INDEX result_pids_pid ON "shadow".result_pids USING btree(pid COLLATE pg_catalog."default");
|
| 571 |
39008
|
eri.katsar
|
|
| 572 |
55644
|
antonis.le
|
CREATE INDEX result_extra_id ON "shadow".result_extra USING btree(id COLLATE pg_catalog."default");
|
| 573 |
39008
|
eri.katsar
|
|
| 574 |
55644
|
antonis.le
|
CREATE INDEX res_class_type ON shadow.result_classifications USING btree (type COLLATE pg_catalog."default");
|
| 575 |
|
|
CREATE INDEX res_class_id ON shadow.result_classifications USING btree (id COLLATE pg_catalog."default");
|
| 576 |
39008
|
eri.katsar
|
|
| 577 |
55644
|
antonis.le
|
CREATE INDEX res_dtsrc_dtsrc ON shadow.result_datasources USING btree (datasource COLLATE pg_catalog."default");
|
| 578 |
|
|
CREATE INDEX res_dtsrc_id ON shadow.result_datasources USING btree (id COLLATE pg_catalog."default");
|
| 579 |
39008
|
eri.katsar
|
|
| 580 |
55644
|
antonis.le
|
CREATE INDEX res_lang_lang ON shadow.result_languages USING btree (language COLLATE pg_catalog."default");
|
| 581 |
39008
|
eri.katsar
|
|
| 582 |
55644
|
antonis.le
|
CREATE INDEX org_id ON shadow.organization USING btree (id COLLATE pg_catalog."default");
|
| 583 |
|
|
CREATE INDEX org_country ON shadow.organization USING btree (country COLLATE pg_catalog."default");
|
| 584 |
|
|
CREATE INDEX org_name ON shadow.organization USING btree (name COLLATE pg_catalog."default");
|
| 585 |
38736
|
eri.katsar
|
|
| 586 |
55644
|
antonis.le
|
CREATE INDEX proj_org_org ON shadow.project_organizations USING btree (organization COLLATE pg_catalog."default");
|
| 587 |
|
|
CREATE INDEX proj_org_id ON shadow.project_organizations USING btree (id COLLATE pg_catalog."default");
|
| 588 |
38736
|
eri.katsar
|
|
| 589 |
55644
|
antonis.le
|
CREATE INDEX res_res_id ON shadow.result_results USING btree (id COLLATE pg_catalog."default");
|
| 590 |
|
|
CREATE INDEX res_res_res ON shadow.result_results USING btree (result COLLATE pg_catalog."default");
|
| 591 |
38736
|
eri.katsar
|
|
| 592 |
55644
|
antonis.le
|
CREATE INDEX res_lang_id ON shadow.result_languages USING btree (id COLLATE pg_catalog."default");
|
| 593 |
38736
|
eri.katsar
|
|
| 594 |
55644
|
antonis.le
|
CREATE INDEX res_conc_id ON shadow.result_concepts USING btree (id COLLATE pg_catalog."default");
|
| 595 |
|
|
CREATE INDEX res_conc_conc ON shadow.result_concepts USING btree (concept COLLATE pg_catalog."default");
|
| 596 |
|
|
END;$$;
|
| 597 |
38736
|
eri.katsar
|
|
| 598 |
29995
|
eri.katsar
|
ALTER FUNCTION shadow.create_indexes() OWNER TO sqoop;
|
| 599 |
|
|
|
| 600 |
|
|
--
|
| 601 |
|
|
-- Name: create_views(); Type: FUNCTION; Schema: shadow; Owner: sqoop
|
| 602 |
|
|
--
|
| 603 |
|
|
|
| 604 |
55644
|
antonis.le
|
CREATE or replace FUNCTION "shadow".create_views() RETURNS void LANGUAGE plpgsql AS $$
|
| 605 |
|
|
BEGIN
|
| 606 |
|
|
CREATE OR REPLACE VIEW shadow.datasource_results AS SELECT datasource AS id, id AS result FROM shadow.result_datasources;
|
| 607 |
|
|
CREATE OR REPLACE VIEW shadow.organization_datasources AS SELECT organization AS id, id AS datasource FROM shadow.datasource_organizations;
|
| 608 |
|
|
CREATE OR REPLACE VIEW shadow.organization_projects AS SELECT id AS project, organization as id FROM shadow.project_organizations;
|
| 609 |
42734
|
eri.katsar
|
|
| 610 |
55644
|
antonis.le
|
CREATE OR REPLACE VIEW shadow.result_projects AS SELECT shadow.project_results.result AS id,
|
| 611 |
|
|
shadow.project_results.id AS project, ( select to_date("shadow"."result"."date", 'YYYY-MM-DD')- to_date("shadow"."project"."enddate", 'YYYY-MM-DD')
|
| 612 |
|
|
from shadow.result, shadow.project where shadow.result.id = shadow.project_results.result
|
| 613 |
|
|
and shadow.project_results.id= shadow.project.id and shadow.result.type='publication' )
|
| 614 |
|
|
as daysfromend FROM shadow.project_results;
|
| 615 |
41790
|
eri.katsar
|
|
| 616 |
55644
|
antonis.le
|
CREATE OR REPLACE VIEW "shadow".datasource_topics AS
|
| 617 |
|
|
SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic
|
| 618 |
|
|
FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics
|
| 619 |
|
|
WHERE "shadow".datasource.id = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND
|
| 620 |
|
|
"shadow".result_topics.id = "shadow".result.id;
|
| 621 |
29995
|
eri.katsar
|
END;$$;
|
| 622 |
47256
|
tsampikos.
|
--
|
| 623 |
|
|
-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop
|
| 624 |
|
|
--
|
| 625 |
31905
|
eri.katsar
|
|
| 626 |
55644
|
antonis.le
|
CREATE or replace FUNCTION "shadow".create_charts() RETURNS void LANGUAGE plpgsql AS $$
|
| 627 |
|
|
BEGIN
|
| 628 |
|
|
-- country nums
|
| 629 |
|
|
CREATE TABLE "shadow".numbers_country AS SELECT org.country AS country, count(distinct rd.datasource) AS datasources, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND rd.datasource=d.id AND d.id=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' GROUP BY org.country;
|
| 630 |
|
|
--
|
| 631 |
|
|
-- -- country charts
|
| 632 |
|
|
CREATE TABLE "shadow".chart_country_year AS SELECT org.country AS country, r.year AS year, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND rd.datasource=d.id AND rd.datasource=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' and r.year>='1990' AND r.year<=extract(YEAR from now()) group by org.country, r.year ORDER BY org.country, r.year;
|
| 633 |
|
|
CREATE TABLE "shadow".chart_country_datasources AS SELECT org.country AS country, d.name AS datasource, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND rd.datasource=d.id AND d.id=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' GROUP BY org.country, d.name ORDER BY org.country, count(distinct r.id) DESC;
|
| 634 |
|
|
CREATE TABLE "shadow".chart_country_type AS SELECT org.country AS country, rc.type AS type, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".result_classifications rc, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND r.id=rc.id AND rd.datasource=d.id AND rd.datasource=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' GROUP BY org.country, rc.type;
|
| 635 |
|
|
CREATE TABLE "shadow".chart_country_fp7 AS SELECT org.country AS country, r.year AS year, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org, "shadow".project_results pr, "shadow".project p WHERE r.id=rd.id AND rd.datasource=d.id AND rd.datasource=dor.id AND dor.organization=org.id AND r.id=pr.result AND pr.id=p.id AND p.funding_lvl0='FP7' AND r.type='publication' AND r.year>='1990' AND r.year<=extract(YEAR from now()) GROUP BY org.country, r.year ORDER BY org.country, r.year;
|
| 636 |
|
|
--
|
| 637 |
|
|
-- -- datasource charts
|
| 638 |
|
|
CREATE TABLE "shadow".chart_datasource_type AS SELECT rd.datasource, rc.type, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;
|
| 639 |
|
|
CREATE TABLE "shadow".chart_datasource_year AS SELECT rd.datasource, r.year, count(distinct rd.id) FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;
|
| 640 |
|
|
CREATE TABLE "shadow".chart_datasource_funders AS SELECT rd.datasource, p.funder, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;
|
| 641 |
|
|
CREATE TABLE "shadow".chart_datasource_projects_pubs AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id AND r.type='publication' GROUP BY rd.datasource, p.title;
|
| 642 |
|
|
CREATE TABLE "shadow".chart_datasource_projects_data AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id and r.type='dataset' GROUP BY rd.datasource, p.title;
|
| 643 |
|
|
--
|
| 644 |
|
|
-- project charts
|
| 645 |
|
|
CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND p.id=pr.id AND r.year>=p.start_year GROUP BY p.id, r.year;
|
| 646 |
|
|
CREATE TABLE "shadow".chart_project_license AS SELECT pr.id, r.bestlicense, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;
|
| 647 |
|
|
CREATE TABLE "shadow".chart_project_repos AS SELECT pr.id, d.name, count (distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".datasource d, "shadow".datasource_results dr WHERE r.id=dr.result AND d.id=dr.id AND r.id=pr.result AND r.type='publication' GROUP BY pr.id, d.name;
|
| 648 |
47256
|
tsampikos.
|
END;$$;
|
| 649 |
|
|
|
| 650 |
47072
|
tsampikos.
|
--
|
| 651 |
47256
|
tsampikos.
|
-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop
|
| 652 |
|
|
--
|
| 653 |
55644
|
antonis.le
|
CREATE or replace FUNCTION "shadow".create_chart_indexes() RETURNS void LANGUAGE plpgsql AS $$
|
| 654 |
|
|
BEGIN
|
| 655 |
|
|
-- country nums
|
| 656 |
|
|
-- CREATE INDEX numbers_country_country ON "shadow".numbers_country USING btree(country);
|
| 657 |
47256
|
tsampikos.
|
|
| 658 |
55644
|
antonis.le
|
-- country charts
|
| 659 |
|
|
-- CREATE INDEX chart_country_year_country ON "shadow".chart_country_year USING btree(country);
|
| 660 |
|
|
-- CREATE INDEX chart_country_datasources_country ON "shadow".chart_country_datasources USING btree(country);
|
| 661 |
|
|
-- CREATE INDEX chart_country_type_country ON "shadow".chart_country_type USING btree(country);
|
| 662 |
|
|
-- CREATE INDEX chart_country_fp7_country ON "shadow".chart_country_fp7 USING btree(country);
|
| 663 |
47256
|
tsampikos.
|
|
| 664 |
55644
|
antonis.le
|
-- datasource
|
| 665 |
|
|
-- CREATE INDEX chart_datasource_type_datasource ON "shadow".chart_datasource_type USING btree(datasource);
|
| 666 |
|
|
-- CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource);
|
| 667 |
|
|
-- CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource);
|
| 668 |
|
|
-- CREATE INDEX chart_datasource_projects_pubs_datasource ON "shadow".chart_datasource_projects_pubs USING btree(datasource);
|
| 669 |
|
|
-- CREATE INDEX chart_datasource_projects_data_datasource ON "shadow".chart_datasource_projects_data USING btree(datasource);
|
| 670 |
47256
|
tsampikos.
|
|
| 671 |
55644
|
antonis.le
|
-- project
|
| 672 |
|
|
-- CREATE INDEX chart_project_year_id ON "shadow".chart_project_year USING btree(id);
|
| 673 |
|
|
-- CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id);
|
| 674 |
|
|
-- CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id);
|
| 675 |
47256
|
tsampikos.
|
END;$$;
|
| 676 |
|
|
|
| 677 |
|
|
--
|
| 678 |
47072
|
tsampikos.
|
-- Name: clean_tables(); Type: FUNCTION; Schema: shadow; Owner: sqoop
|
| 679 |
|
|
--
|
| 680 |
55644
|
antonis.le
|
CREATE or replace FUNCTION "shadow".clean_tables() RETURNS void LANGUAGE plpgsql AS $$
|
| 681 |
|
|
BEGIN
|
| 682 |
|
|
CREATE TABLE "shadow".rd_distinct AS SELECT DISTINCT * FROM "shadow".result_datasources;
|
| 683 |
31905
|
eri.katsar
|
|
| 684 |
55644
|
antonis.le
|
DROP TABLE "shadow".result_datasources;
|
| 685 |
|
|
ALTER TABLE "shadow".rd_distinct RENAME TO result_datasources;
|
| 686 |
47072
|
tsampikos.
|
|
| 687 |
55644
|
antonis.le
|
-- TRUNCATE "shadow".result_datasources;
|
| 688 |
|
|
-- INSERT INTO "shadow".result_datasources SELECT * FROM "shadow".rd_distinct ORDER BY datasource;
|
| 689 |
|
|
-- DROP TABLE "shadow".rd_distinct;
|
| 690 |
|
|
END;$$;
|
| 691 |
47072
|
tsampikos.
|
|
| 692 |
|
|
|
| 693 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION "shadow".insert_countries() RETURNS void LANGUAGE plpgsql AS $$
|
| 694 |
|
|
BEGIN
|
| 695 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AD', 'Andorra', 'EU', 'Europe');
|
| 696 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AE', 'United Arab Emirates', 'AS', 'Asia');
|
| 697 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AF', 'Afghanistan', 'AS', 'Asia');
|
| 698 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AG', 'Antigua and Barbuda', 'NA', 'North America');
|
| 699 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AI', 'Anguilla', 'NA', 'North America');
|
| 700 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AL', 'Albania', 'EU', 'Europe');
|
| 701 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AM', 'Armenia', 'AS', 'Asia');
|
| 702 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AO', 'Angola', 'AF', 'Africa');
|
| 703 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AQ', 'Antarctica', 'AN', 'Antarctica');
|
| 704 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AR', 'Argentina', 'SA', 'South America');
|
| 705 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AS', 'American Samoa', 'OC', 'Oceania');
|
| 706 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AT', 'Austria', 'EU', 'Europe');
|
| 707 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AU', 'Australia', 'OC', 'Oceania');
|
| 708 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AW', 'Aruba', 'NA', 'North America');
|
| 709 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AX', 'Åland Islands', 'EU', 'Europe');
|
| 710 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AZ', 'Azerbaijan', 'AS', 'Asia');
|
| 711 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BA', 'Bosnia and Herzegovina', 'EU', 'Europe');
|
| 712 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BB', 'Barbados', 'NA', 'North America');
|
| 713 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BD', 'Bangladesh', 'AS', 'Asia');
|
| 714 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BE', 'Belgium', 'EU', 'Europe');
|
| 715 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BF', 'Burkina Faso', 'AF', 'Africa');
|
| 716 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BG', 'Bulgaria', 'EU', 'Europe');
|
| 717 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BH', 'Bahrain', 'AS', 'Asia');
|
| 718 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BI', 'Burundi', 'AF', 'Africa');
|
| 719 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BJ', 'Benin', 'AF', 'Africa');
|
| 720 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BL', 'Saint Barthélemy', 'NA', 'North America');
|
| 721 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BM', 'Bermuda', 'NA', 'North America');
|
| 722 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BN', 'Brunei Darussalam', 'AS', 'Asia');
|
| 723 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BO', 'Bolivia, Plurinational State of', 'SA', 'South America');
|
| 724 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BR', 'Brazil', 'SA', 'South America');
|
| 725 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BS', 'Bahamas', 'NA', 'North America');
|
| 726 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BT', 'Bhutan', 'AS', 'Asia');
|
| 727 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BV', 'Bouvet Island', 'AN', 'Antarctica');
|
| 728 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BW', 'Botswana', 'AF', 'Africa');
|
| 729 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BY', 'Belarus', 'EU', 'Europe');
|
| 730 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BZ', 'Belize', 'NA', 'North America');
|
| 731 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CA', 'Canada', 'NA', 'North America');
|
| 732 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CC', 'Cocos (Keeling) Islands', 'AS', 'Asia');
|
| 733 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CD', 'Congo, the Democratic Republic of the', 'AF', 'Africa');
|
| 734 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CF', 'Central African Republic', 'AF', 'Africa');
|
| 735 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CG', 'Congo', 'AF', 'Africa');
|
| 736 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CH', 'Switzerland', 'EU', 'Europe');
|
| 737 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CI', 'Côte d''Ivoire', 'AF', 'Africa');
|
| 738 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CK', 'Cook Islands', 'OC', 'Oceania');
|
| 739 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CL', 'Chile', 'SA', 'South America');
|
| 740 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CM', 'Cameroon', 'AF', 'Africa');
|
| 741 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CN', 'China', 'AS', 'Asia');
|
| 742 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CO', 'Colombia', 'SA', 'South America');
|
| 743 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CR', 'Costa Rica', 'NA', 'North America');
|
| 744 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CU', 'Cuba', 'NA', 'North America');
|
| 745 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CV', 'Cape Verde', 'AF', 'Africa');
|
| 746 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CX', 'Christmas Island', 'AS', 'Asia');
|
| 747 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CY', 'Cyprus', 'AS', 'Asia');
|
| 748 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CZ', 'Czech Republic', 'EU', 'Europe');
|
| 749 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DE', 'Germany', 'EU', 'Europe');
|
| 750 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DJ', 'Djibouti', 'AF', 'Africa');
|
| 751 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DK', 'Denmark', 'EU', 'Europe');
|
| 752 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DM', 'Dominica', 'NA', 'North America');
|
| 753 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DO', 'Dominican Republic', 'NA', 'North America');
|
| 754 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DZ', 'Algeria', 'AF', 'Africa');
|
| 755 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EC', 'Ecuador', 'SA', 'South America');
|
| 756 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EE', 'Estonia', 'EU', 'Europe');
|
| 757 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EG', 'Egypt', 'AF', 'Africa');
|
| 758 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EH', 'Western Sahara', 'AF', 'Africa');
|
| 759 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ER', 'Eritrea', 'AF', 'Africa');
|
| 760 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ES', 'Spain', 'EU', 'Europe');
|
| 761 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ET', 'Ethiopia', 'AF', 'Africa');
|
| 762 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FI', 'Finland', 'EU', 'Europe');
|
| 763 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FJ', 'Fiji', 'OC', 'Oceania');
|
| 764 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FK', 'Falkland Islands (Malvinas)', 'SA', 'South America');
|
| 765 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FM', 'Micronesia, Federated States of', 'OC', 'Oceania');
|
| 766 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FO', 'Faroe Islands', 'EU', 'Europe');
|
| 767 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FR', 'France', 'EU', 'Europe');
|
| 768 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GA', 'Gabon', 'AF', 'Africa');
|
| 769 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GB', 'United Kingdom', 'EU', 'Europe');
|
| 770 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GD', 'Grenada', 'NA', 'North America');
|
| 771 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GE', 'Georgia', 'AS', 'Asia');
|
| 772 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GF', 'French Guiana', 'SA', 'South America');
|
| 773 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GG', 'Guernsey', 'EU', 'Europe');
|
| 774 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GH', 'Ghana', 'AF', 'Africa');
|
| 775 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GI', 'Gibraltar', 'EU', 'Europe');
|
| 776 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GL', 'Greenland', 'NA', 'North America');
|
| 777 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GM', 'Gambia', 'AF', 'Africa');
|
| 778 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GN', 'Guinea', 'AF', 'Africa');
|
| 779 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GP', 'Guadeloupe', 'NA', 'North America');
|
| 780 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GQ', 'Equatorial Guinea', 'AF', 'Africa');
|
| 781 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GR', 'Greece', 'EU', 'Europe');
|
| 782 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GS', 'South Georgia and the South Sandwich Islands', 'AN', 'Antarctica');
|
| 783 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GT', 'Guatemala', 'NA', 'North America');
|
| 784 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GU', 'Guam', 'OC', 'Oceania');
|
| 785 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GW', 'Guinea-Bissau', 'AF', 'Africa');
|
| 786 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GY', 'Guyana', 'SA', 'South America');
|
| 787 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HK', 'Hong Kong', 'AS', 'Asia');
|
| 788 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HM', 'Heard Island and McDonald Islands', 'AN', 'Antarctica');
|
| 789 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HN', 'Honduras', 'NA', 'North America');
|
| 790 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HR', 'Croatia', 'EU', 'Europe');
|
| 791 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HT', 'Haiti', 'NA', 'North America');
|
| 792 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HU', 'Hungary', 'EU', 'Europe');
|
| 793 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ID', 'Indonesia', 'AS', 'Asia');
|
| 794 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IE', 'Ireland', 'EU', 'Europe');
|
| 795 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IL', 'Israel', 'AS', 'Asia');
|
| 796 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IM', 'Isle of Man', 'EU', 'Europe');
|
| 797 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IN', 'India', 'AS', 'Asia');
|
| 798 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IO', 'British Indian Ocean Territory', 'AS', 'Asia');
|
| 799 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IQ', 'Iraq', 'AS', 'Asia');
|
| 800 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IR', 'Iran, Islamic Republic of', 'AS', 'Asia');
|
| 801 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IS', 'Iceland', 'EU', 'Europe');
|
| 802 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IT', 'Italy', 'EU', 'Europe');
|
| 803 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JE', 'Jersey', 'EU', 'Europe');
|
| 804 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JM', 'Jamaica', 'NA', 'North America');
|
| 805 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JO', 'Jordan', 'AS', 'Asia');
|
| 806 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JP', 'Japan', 'AS', 'Asia');
|
| 807 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KE', 'Kenya', 'AF', 'Africa');
|
| 808 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KG', 'Kyrgyzstan', 'AS', 'Asia');
|
| 809 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KH', 'Cambodia', 'AS', 'Asia');
|
| 810 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KI', 'Kiribati', 'OC', 'Oceania');
|
| 811 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KM', 'Comoros', 'AF', 'Africa');
|
| 812 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KN', 'Saint Kitts and Nevis', 'NA', 'North America');
|
| 813 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KP', 'Korea, Democratic People''s Republic of', 'AS', 'Asia');
|
| 814 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KR', 'Korea, Republic of', 'AS', 'Asia');
|
| 815 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KW', 'Kuwait', 'AS', 'Asia');
|
| 816 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KY', 'Cayman Islands', 'NA', 'North America');
|
| 817 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KZ', 'Kazakhstan', 'AS', 'Asia');
|
| 818 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LA', 'Lao People''s Democratic Republic', 'AS', 'Asia');
|
| 819 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LB', 'Lebanon', 'AS', 'Asia');
|
| 820 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LC', 'Saint Lucia', 'NA', 'North America');
|
| 821 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LI', 'Liechtenstein', 'EU', 'Europe');
|
| 822 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LK', 'Sri Lanka', 'AS', 'Asia');
|
| 823 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LR', 'Liberia', 'AF', 'Africa');
|
| 824 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LS', 'Lesotho', 'AF', 'Africa');
|
| 825 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LT', 'Lithuania', 'EU', 'Europe');
|
| 826 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LU', 'Luxembourg', 'EU', 'Europe');
|
| 827 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LV', 'Latvia', 'EU', 'Europe');
|
| 828 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LY', 'Libya', 'AF', 'Africa');
|
| 829 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MA', 'Morocco', 'AF', 'Africa');
|
| 830 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MC', 'Monaco', 'EU', 'Europe');
|
| 831 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MD', 'Moldova, Republic of', 'EU', 'Europe');
|
| 832 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ME', 'Montenegro', 'EU', 'Europe');
|
| 833 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MF', 'Saint Martin (French part)', 'NA', 'North America');
|
| 834 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MG', 'Madagascar', 'AF', 'Africa');
|
| 835 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MH', 'Marshall Islands', 'OC', 'Oceania');
|
| 836 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MK', 'Macedonia, the Former Yugoslav Republic of', 'EU', 'Europe');
|
| 837 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ML', 'Mali', 'AF', 'Africa');
|
| 838 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MM', 'Myanmar', 'AS', 'Asia');
|
| 839 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MN', 'Mongolia', 'AS', 'Asia');
|
| 840 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MO', 'Macao', 'AS', 'Asia');
|
| 841 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MP', 'Northern Mariana Islands', 'OC', 'Oceania');
|
| 842 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MQ', 'Martinique', 'NA', 'North America');
|
| 843 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MR', 'Mauritania', 'AF', 'Africa');
|
| 844 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MS', 'Montserrat', 'NA', 'North America');
|
| 845 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MT', 'Malta', 'EU', 'Europe');
|
| 846 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MU', 'Mauritius', 'AF', 'Africa');
|
| 847 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MV', 'Maldives', 'AS', 'Asia');
|
| 848 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MW', 'Malawi', 'AF', 'Africa');
|
| 849 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MX', 'Mexico', 'NA', 'North America');
|
| 850 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MY', 'Malaysia', 'AS', 'Asia');
|
| 851 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MZ', 'Mozambique', 'AF', 'Africa');
|
| 852 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NA', 'Namibia', 'AF', 'Africa');
|
| 853 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NC', 'New Caledonia', 'OC', 'Oceania');
|
| 854 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NE', 'Niger', 'AF', 'Africa');
|
| 855 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NF', 'Norfolk Island', 'OC', 'Oceania');
|
| 856 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NG', 'Nigeria', 'AF', 'Africa');
|
| 857 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NI', 'Nicaragua', 'NA', 'North America');
|
| 858 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NL', 'Netherlands', 'EU', 'Europe');
|
| 859 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NO', 'Norway', 'EU', 'Europe');
|
| 860 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NP', 'Nepal', 'AS', 'Asia');
|
| 861 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NR', 'Nauru', 'OC', 'Oceania');
|
| 862 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NU', 'Niue', 'OC', 'Oceania');
|
| 863 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NZ', 'New Zealand', 'OC', 'Oceania');
|
| 864 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('OM', 'Oman', 'AS', 'Asia');
|
| 865 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PA', 'Panama', 'NA', 'North America');
|
| 866 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PE', 'Peru', 'SA', 'South America');
|
| 867 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PF', 'French Polynesia', 'OC', 'Oceania');
|
| 868 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PG', 'Papua New Guinea', 'OC', 'Oceania');
|
| 869 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PH', 'Philippines', 'AS', 'Asia');
|
| 870 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PK', 'Pakistan', 'AS', 'Asia');
|
| 871 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PL', 'Poland', 'EU', 'Europe');
|
| 872 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PM', 'Saint Pierre and Miquelon', 'NA', 'North America');
|
| 873 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PN', 'Pitcairn', 'OC', 'Oceania');
|
| 874 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PR', 'Puerto Rico', 'NA', 'North America');
|
| 875 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PS', 'Palestine, State of', 'AS', 'Asia');
|
| 876 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PT', 'Portugal', 'EU', 'Europe');
|
| 877 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PW', 'Palau', 'OC', 'Oceania');
|
| 878 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PY', 'Paraguay', 'SA', 'South America');
|
| 879 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('QA', 'Qatar', 'AS', 'Asia');
|
| 880 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RE', 'Réunion', 'AF', 'Africa');
|
| 881 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RO', 'Romania', 'EU', 'Europe');
|
| 882 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RS', 'Serbia', 'EU', 'Europe');
|
| 883 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RU', 'Russian Federation', 'EU', 'Europe');
|
| 884 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RW', 'Rwanda', 'AF', 'Africa');
|
| 885 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SA', 'Saudi Arabia', 'AS', 'Asia');
|
| 886 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SB', 'Solomon Islands', 'OC', 'Oceania');
|
| 887 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SC', 'Seychelles', 'AF', 'Africa');
|
| 888 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SD', 'Sudan', 'AF', 'Africa');
|
| 889 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SE', 'Sweden', 'EU', 'Europe');
|
| 890 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SG', 'Singapore', 'AS', 'Asia');
|
| 891 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SH', 'Saint Helena, Ascension and Tristan da Cunha', 'AF', 'Africa');
|
| 892 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SI', 'Slovenia', 'EU', 'Europe');
|
| 893 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SJ', 'Svalbard and Jan Mayen', 'EU', 'Europe');
|
| 894 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SK', 'Slovakia', 'EU', 'Europe');
|
| 895 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SL', 'Sierra Leone', 'AF', 'Africa');
|
| 896 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SM', 'San Marino', 'EU', 'Europe');
|
| 897 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SN', 'Senegal', 'AF', 'Africa');
|
| 898 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SO', 'Somalia', 'AF', 'Africa');
|
| 899 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SR', 'Suriname', 'SA', 'South America');
|
| 900 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ST', 'Sao Tome and Principe', 'AF', 'Africa');
|
| 901 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SV', 'El Salvador', 'NA', 'North America');
|
| 902 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SY', 'Syrian Arab Republic', 'AS', 'Asia');
|
| 903 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SZ', 'Swaziland', 'AF', 'Africa');
|
| 904 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TC', 'Turks and Caicos Islands', 'NA', 'North America');
|
| 905 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TD', 'Chad', 'AF', 'Africa');
|
| 906 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TF', 'French Southern Territories', 'AN', 'Antarctica');
|
| 907 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TG', 'Togo', 'AF', 'Africa');
|
| 908 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TH', 'Thailand', 'AS', 'Asia');
|
| 909 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TJ', 'Tajikistan', 'AS', 'Asia');
|
| 910 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TK', 'Tokelau', 'OC', 'Oceania');
|
| 911 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TL', 'Timor-Leste', 'AS', 'Asia');
|
| 912 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TM', 'Turkmenistan', 'AS', 'Asia');
|
| 913 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TN', 'Tunisia', 'AF', 'Africa');
|
| 914 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TO', 'Tonga', 'OC', 'Oceania');
|
| 915 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TR', 'Turkey', 'EU', 'Europe');
|
| 916 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TT', 'Trinidad and Tobago', 'NA', 'North America');
|
| 917 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TV', 'Tuvalu', 'OC', 'Oceania');
|
| 918 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TW', 'Taiwan, Province of China', 'AS', 'Asia');
|
| 919 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TZ', 'Tanzania, United Republic of', 'AF', 'Africa');
|
| 920 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UA', 'Ukraine', 'EU', 'Europe');
|
| 921 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UG', 'Uganda', 'AF', 'Africa');
|
| 922 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UM', 'United States Minor Outlying Islands', 'OC', 'Oceania');
|
| 923 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('US', 'United States', 'NA', 'North America');
|
| 924 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UY', 'Uruguay', 'SA', 'South America');
|
| 925 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UZ', 'Uzbekistan', 'AS', 'Asia');
|
| 926 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VA', 'Holy See (Vatican City State)', 'EU', 'Europe');
|
| 927 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VC', 'Saint Vincent and the Grenadines', 'NA', 'North America');
|
| 928 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VE', 'Venezuela, Bolivarian Republic of', 'SA', 'South America');
|
| 929 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VG', 'Virgin Islands, British', 'NA', 'North America');
|
| 930 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VI', 'Virgin Islands, U.S.', 'NA', 'North America');
|
| 931 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VN', 'Viet Nam', 'AS', 'Asia');
|
| 932 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VU', 'Vanuatu', 'OC', 'Oceania');
|
| 933 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WF', 'Wallis and Futuna', 'OC', 'Oceania');
|
| 934 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WS', 'Samoa', 'OC', 'Oceania');
|
| 935 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YE', 'Yemen', 'AS', 'Asia');
|
| 936 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YT', 'Mayotte', 'AF', 'Africa');
|
| 937 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZA', 'South Africa', 'AF', 'Africa');
|
| 938 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZM', 'Zambia', 'AF', 'Africa');
|
| 939 |
|
|
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZW', 'Zimbabwe', 'AF', 'Africa');
|
| 940 |
47072
|
tsampikos.
|
END;$$;
|
| 941 |
|
|
|
| 942 |
55644
|
antonis.le
|
CREATE OR REPLACE FUNCTION "shadow".backwards_compatibility() RETURNS void LANGUAGE plpgsql AS $$
|
| 943 |
|
|
BEGIN
|
| 944 |
|
|
ALTER TABLE "shadow".result RENAME TO tresult;
|
| 945 |
|
|
ALTER TABLE "shadow".datasource RENAME TO tdatasource;
|
| 946 |
|
|
ALTER TABLE "shadow".project RENAME TO tproject;
|
| 947 |
|
|
ALTER TABLE "shadow".organization RENAME TO torganization;
|
| 948 |
|
|
|
| 949 |
|
|
CREATE VIEW "shadow".result AS SELECT tresult.id, tresult.id AS result_projects, tresult.id AS result_datasources, tresult.publisher, tresult.year, tresult.bestlicense, tresult.bestlicense AS access_mode, tresult.type, tresult.embargo_end_date, tresult.delayed, tresult.authors, tresult.funders, tresult.funding_lvl0 AS arfunding_lvl0, tresult.projects, tresult.datasources, 1 AS number FROM tresult;
|
| 950 |
|
|
CREATE VIEW "shadow".datasource AS SELECT tdatasource.id, tdatasource.name, tdatasource.type, tdatasource.compatibility, tdatasource.dateofvalidation, tdatasource.yearofvalidation, tdatasource.harvested, tdatasource.piwik_id, tdatasource.id AS datasource_results, tdatasource.id AS datasource_organizations, 1 AS number FROM tdatasource;
|
| 951 |
|
|
CREATE VIEW "shadow".project AS SELECT tproject.id, tproject.acronym, tproject.title, tproject.funder, tproject.funding_lvl0, tproject.funding_lvl1, tproject.funding_lvl2, tproject.funding_lvl3, tproject.sc39, tproject.type, tproject.start_year, tproject.end_year, tproject.duration, tproject.haspubs, tproject.numpubs, tproject.enddate, tproject.startdate, tproject.daysforlastpub, tproject.delayedpubs, tproject.callidentifier, tproject.code, tproject.id AS project_results, tproject.id AS project_organizations, 1 AS number FROM tproject;
|
| 952 |
|
|
CREATE VIEW "shadow".organization AS SELECT torganization.id, torganization.name, torganization.country, torganization.id AS organization_datasources, torganization.id AS organization_projects, 1 AS number FROM torganization;
|
| 953 |
|
|
END;$$;
|
| 954 |
|
|
|
| 955 |
|
|
|
| 956 |
27955
|
claudio.at
|
-- sqoopQL database dump complete
|
| 957 |
55644
|
antonis.le
|
--
|