~codescore-dev/codescore/version-1.0

((SELECT ppl_list.name, 'Person' AS role FROM ppl_list UNION SELECT ppl_list.name, 'Admin' AS role FROM ppl_list WHERE (ppl_list.hidden = true)) UNION SELECT ppl_list.name, 'Coach' AS role FROM (ppl_list RIGHT JOIN ppl_coaches ON ((ppl_list.personid = ppl_coaches.personid)))) UNION SELECT ppl_list.name, 'Judge' AS role FROM (ppl_list RIGHT JOIN ppl_judges ON ((ppl_list.personid = ppl_judges.personid)));

164

165

166

ALTER TABLE public.ppl_roles OWNER TO codescore;

167

168

169

-- TOC entry 1777 (class 1259 OID 17888)

170

-- Dependencies: 6

171

-- Name: pk_ppl_schools; Type: SEQUENCE; Schema: public; Owner: codescore

172

173

174

CREATE SEQUENCE pk_ppl_schools

175

START WITH 1

176

INCREMENT BY 1

177

NO MAXVALUE

178

NO MINVALUE

179

CACHE 1;

180

181

182

ALTER TABLE public.pk_ppl_schools OWNER TO codescore;

183

184

185

-- TOC entry 1752 (class 1259 OID 17689)

186

-- Dependencies: 2117 6

187

-- Name: ppl_schools; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

188

189

190

CREATE TABLE ppl_schools (

191

name character varying(255) NOT NULL,

192

schoolid integer DEFAULT nextval('pk_ppl_schools'::regclass) NOT NULL,

193

webpage character varying(255),

194

regionid integer

195

);

196

197

198

ALTER TABLE public.ppl_schools OWNER TO codescore;

199

200

201

-- TOC entry 1753 (class 1259 OID 17695)

202

-- Dependencies: 6

203

-- Name: pk_prob_cases; Type: SEQUENCE; Schema: public; Owner: codescore

204

205

206

CREATE SEQUENCE pk_prob_cases

207

INCREMENT BY 1

208

NO MAXVALUE

209

NO MINVALUE

210

CACHE 2;

211

212

213

ALTER TABLE public.pk_prob_cases OWNER TO codescore;

214

215

216

-- TOC entry 1794 (class 1259 OID 18146)

217

-- Dependencies: 2139 2140 6

218

-- Name: prob_cases; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

219

220

221

CREATE TABLE prob_cases (

222

active boolean DEFAULT true NOT NULL,

223

caseid integer DEFAULT nextval('pk_prob_cases'::regclass) NOT NULL,

224

input text NOT NULL,

225

output text NOT NULL,

226

probid integer NOT NULL

227

);

228

229

230

ALTER TABLE public.prob_cases OWNER TO codescore;

231

232

233

-- TOC entry 1778 (class 1259 OID 17890)

234

-- Dependencies: 6

235

-- Name: pk_prob_comments; Type: SEQUENCE; Schema: public; Owner: codescore

236

237

238

CREATE SEQUENCE pk_prob_comments

239

START WITH 1

240

INCREMENT BY 1

241

NO MAXVALUE

242

NO MINVALUE

243

CACHE 1;

244

245

246

ALTER TABLE public.pk_prob_comments OWNER TO codescore;

247

248

249

-- TOC entry 1754 (class 1259 OID 17705)

250

-- Dependencies: 2118 2119 6

251

-- Name: prob_comments; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

252

253

254

CREATE TABLE prob_comments (

255

comid integer DEFAULT nextval('pk_prob_comments'::regclass) NOT NULL,

256

message text NOT NULL,

257

personid integer NOT NULL,

258

probid integer NOT NULL,

259

tstamp timestamp with time zone DEFAULT now() NOT NULL

260

);

261

262

263

ALTER TABLE public.prob_comments OWNER TO codescore;

264

265

266

-- TOC entry 1779 (class 1259 OID 17892)

267

-- Dependencies: 6

268

-- Name: pk_prob_descfigs; Type: SEQUENCE; Schema: public; Owner: codescore

269

270

271

CREATE SEQUENCE pk_prob_descfigs

272

START WITH 1

273

INCREMENT BY 1

274

NO MAXVALUE

275

NO MINVALUE

276

CACHE 1;

277

278

279

ALTER TABLE public.pk_prob_descfigs OWNER TO codescore;

280

281

282

-- TOC entry 1755 (class 1259 OID 17712)

283

-- Dependencies: 2120 6

284

-- Name: prob_descfigs; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

285

286

287

CREATE TABLE prob_descfigs (

288

figid integer DEFAULT nextval('pk_prob_descfigs'::regclass) NOT NULL,

289

figure bytea NOT NULL,

290

name character varying(100),

291

probid integer NOT NULL

292

);

293

294

295

ALTER TABLE public.prob_descfigs OWNER TO codescore;

296

297

298

-- TOC entry 1780 (class 1259 OID 17894)

299

-- Dependencies: 6

300

-- Name: pk_prob_list; Type: SEQUENCE; Schema: public; Owner: codescore

301

302

303

CREATE SEQUENCE pk_prob_list

304

INCREMENT BY 1

305

NO MAXVALUE

306

NO MINVALUE

307

CACHE 1;

308

309

310

ALTER TABLE public.pk_prob_list OWNER TO codescore;

311

312

313

-- TOC entry 1756 (class 1259 OID 17718)

314

-- Dependencies: 2121 2122 2123 6

315

-- Name: prob_list; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

316

317

318

CREATE TABLE prob_list (

319

active boolean DEFAULT true NOT NULL,

320

description text,

321

difficulty smallint DEFAULT 128 NOT NULL,

322

name character varying(255) NOT NULL,

323

problemid integer DEFAULT nextval('pk_prob_list'::regclass) NOT NULL,

324

sampleinput text,

325

sampleoutput text,

326

unixname character varying(128) NOT NULL,

327

parsed_document tsvector,

328

clean_desc text

329

);

330

331

332

ALTER TABLE public.prob_list OWNER TO codescore;

333

334

335

-- TOC entry 1757 (class 1259 OID 17726)

336

-- Dependencies: 6

337

-- Name: prob_set; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

338

339

340

CREATE TABLE prob_set (

341

compid integer NOT NULL,

342

probid integer NOT NULL

343

);

344

345

346

ALTER TABLE public.prob_set OWNER TO codescore;

347

348

349

-- TOC entry 1782 (class 1259 OID 17898)

350

-- Dependencies: 6

351

-- Name: pk_prob_solutions; Type: SEQUENCE; Schema: public; Owner: codescore

352

353

354

CREATE SEQUENCE pk_prob_solutions

355

START WITH 1

356

INCREMENT BY 1

357

NO MAXVALUE

358

NO MINVALUE

359

CACHE 1;

360

361

362

ALTER TABLE public.pk_prob_solutions OWNER TO codescore;

363

364

365

-- TOC entry 1758 (class 1259 OID 17729)

366

-- Dependencies: 2124 6

367

-- Name: prob_solutions; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

368

369

370

CREATE TABLE prob_solutions (

371

compilestderr text,

372

compilestdout text,

373

exestderr text,

374

exestdout text,

375

langid integer NOT NULL,

376

pid integer NOT NULL,

377

sid integer DEFAULT nextval('pk_prob_solutions'::regclass) NOT NULL,

378

source text,

379

stat integer,

380

stdoutdiff text,

381

"time" timestamp with time zone

382

);

383

384

385

ALTER TABLE public.prob_solutions OWNER TO codescore;

386

387

388

-- TOC entry 1783 (class 1259 OID 17900)

389

-- Dependencies: 6

390

-- Name: pk_prob_status; Type: SEQUENCE; Schema: public; Owner: codescore

391

392

393

CREATE SEQUENCE pk_prob_status

394

START WITH 1

395

INCREMENT BY 1

396

NO MAXVALUE

397

NO MINVALUE

398

CACHE 1;

399

400

401

ALTER TABLE public.pk_prob_status OWNER TO codescore;

402

403

404

-- TOC entry 1759 (class 1259 OID 17735)

405

-- Dependencies: 2125 6

406

-- Name: prob_status; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

407

408

409

CREATE TABLE prob_status (

410

name character varying(255),

411

statid integer DEFAULT nextval('pk_prob_status'::regclass) NOT NULL

412

);

413

414

415

ALTER TABLE public.prob_status OWNER TO codescore;

416

417

418

-- TOC entry 1760 (class 1259 OID 17738)

419

-- Dependencies: 2126 6

420

-- Name: prob_tag_list; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

421

422

423

CREATE TABLE prob_tag_list (

424

name character varying(100) NOT NULL,

425

tagid integer DEFAULT nextval('prob_tag_list'::regclass) NOT NULL

426

);

427

428

429

ALTER TABLE public.prob_tag_list OWNER TO codescore;

430

431

432

-- TOC entry 1761 (class 1259 OID 17741)

433

-- Dependencies: 6

434

-- Name: prob_tags; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

435

436

437

CREATE TABLE prob_tags (

438

probid integer NOT NULL,

439

tagid integer NOT NULL

440

);

441

442

443

ALTER TABLE public.prob_tags OWNER TO codescore;

444

445

446

-- TOC entry 415 (class 1247 OID 17746)

447

-- Dependencies: 6 1762

448

-- Name: prob_ts_result; Type: TYPE; Schema: public; Owner: postgres

449

450

451

CREATE TYPE prob_ts_result AS (

452

pname text,

453

rank real,

454

headline text,

455

probid integer

456

);

457

458

459

ALTER TYPE public.prob_ts_result OWNER TO postgres;

460

461

462

-- TOC entry 1845 (class 1259 OID 18729)

463

-- Dependencies: 6

464

-- Name: pk_prob_versions; Type: SEQUENCE; Schema: public; Owner: postgres

465

466

467

CREATE SEQUENCE pk_prob_versions

468

START WITH 1

469

INCREMENT BY 1

470

NO MAXVALUE

471

NO MINVALUE

472

CACHE 1;

473

474

475

ALTER TABLE public.pk_prob_versions OWNER TO postgres;

476

477

478

-- TOC entry 1763 (class 1259 OID 17747)

479

-- Dependencies: 2127 2128 6

480

-- Name: prob_versions; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

481

482

483

CREATE TABLE prob_versions (

484

versionid integer DEFAULT nextval('pk_prob_versions'::regclass) NOT NULL,

485

problemid integer NOT NULL,

486

description text,

487

sampleinput text,

488

sampleoutput text,

489

unixname character varying(128) NOT NULL,

490

tstamp timestamp with time zone DEFAULT now(),

491

personid integer NOT NULL

492

);

493

494

495

ALTER TABLE public.prob_versions OWNER TO postgres;

496

497

498

-- TOC entry 1786 (class 1259 OID 17906)

499

-- Dependencies: 6

500

-- Name: pk_sys_clari; Type: SEQUENCE; Schema: public; Owner: codescore

501

502

503

CREATE SEQUENCE pk_sys_clari

504

INCREMENT BY 1

505

NO MAXVALUE

506

NO MINVALUE

507

CACHE 1;

508

509

510

ALTER TABLE public.pk_sys_clari OWNER TO codescore;

511

512

513

-- TOC entry 1764 (class 1259 OID 17754)

514

-- Dependencies: 2129 6

515

-- Name: sys_clari; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

516

517

518

CREATE TABLE sys_clari (

519

cid integer DEFAULT nextval('pk_sys_clari'::regclass) NOT NULL,

520

compid integer NOT NULL,

521

message text,

522

response text

523

);

524

525

526

ALTER TABLE public.sys_clari OWNER TO codescore;

527

528

529

-- TOC entry 1787 (class 1259 OID 17908)

530

-- Dependencies: 6

531

-- Name: pk_sys_compargs; Type: SEQUENCE; Schema: public; Owner: codescore

532

533

534

CREATE SEQUENCE pk_sys_compargs

535

START WITH 1

536

INCREMENT BY 1

537

NO MAXVALUE

538

NO MINVALUE

539

CACHE 1;

540

541

542

ALTER TABLE public.pk_sys_compargs OWNER TO codescore;

543

544

545

-- TOC entry 1765 (class 1259 OID 17760)

546

-- Dependencies: 2130 6

547

-- Name: sys_compargs; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

548

549

550

CREATE TABLE sys_compargs (

551

argid integer DEFAULT nextval('pk_sys_compargs'::regclass) NOT NULL,

552

langid integer NOT NULL,

553

"position" integer NOT NULL,

554

val character varying(255) NOT NULL

555

);

556

557

558

ALTER TABLE public.sys_compargs OWNER TO codescore;

559

560

561

-- TOC entry 1788 (class 1259 OID 17910)

562

-- Dependencies: 6

563

-- Name: pk_sys_comps; Type: SEQUENCE; Schema: public; Owner: codescore

564

565

566

CREATE SEQUENCE pk_sys_comps

567

INCREMENT BY 1

568

NO MAXVALUE

569

NO MINVALUE

570

CACHE 1;

571

572

573

ALTER TABLE public.pk_sys_comps OWNER TO codescore;

574

575

576

-- TOC entry 1766 (class 1259 OID 17763)

577

-- Dependencies: 2131 6

578

-- Name: sys_comps; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

579

580

581

CREATE TABLE sys_comps (

582

active boolean NOT NULL,

583

compid integer DEFAULT nextval('pk_sys_comps'::regclass) NOT NULL,

584

endtime timestamp with time zone NOT NULL,

585

name character varying(255) NOT NULL,

586

propsbin bytea,

587

starttime timestamp with time zone NOT NULL

588

);

589

590

591

ALTER TABLE public.sys_comps OWNER TO codescore;

592

593

594

-- TOC entry 1789 (class 1259 OID 17912)

595

-- Dependencies: 6

596

-- Name: pk_sys_exeargs; Type: SEQUENCE; Schema: public; Owner: codescore

597

598

599

CREATE SEQUENCE pk_sys_exeargs

600

START WITH 1

601

INCREMENT BY 1

602

NO MAXVALUE

603

NO MINVALUE

604

CACHE 1;

605

606

607

ALTER TABLE public.pk_sys_exeargs OWNER TO codescore;

608

609

610

-- TOC entry 1767 (class 1259 OID 17769)

611

-- Dependencies: 2132 6

612

-- Name: sys_exeargs; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

613

614

615

CREATE TABLE sys_exeargs (

616

argid integer DEFAULT nextval('pk_sys_exeargs'::regclass) NOT NULL,

617

langid integer NOT NULL,

618

"position" integer NOT NULL,

619

val character varying(255) NOT NULL

620

);

621

622

623

ALTER TABLE public.sys_exeargs OWNER TO codescore;

624

625

626

-- TOC entry 1790 (class 1259 OID 17914)

627

-- Dependencies: 6

628

-- Name: pk_sys_langs; Type: SEQUENCE; Schema: public; Owner: codescore

629

630

631

CREATE SEQUENCE pk_sys_langs

632

START WITH 1

633

INCREMENT BY 1

634

NO MAXVALUE

635

NO MINVALUE

636

CACHE 1;

637

638

639

ALTER TABLE public.pk_sys_langs OWNER TO codescore;

640

641

642

-- TOC entry 1768 (class 1259 OID 17772)

643

-- Dependencies: 2133 6

644

-- Name: sys_langs; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

645

646

647

CREATE TABLE sys_langs (

648

fileextention character varying(16) NOT NULL,

649

lid integer DEFAULT nextval('pk_sys_langs'::regclass) NOT NULL,

650

name character varying(255) NOT NULL

651

);

652

653

654

ALTER TABLE public.sys_langs OWNER TO codescore;

655

656

657

-- TOC entry 1791 (class 1259 OID 17916)

658

-- Dependencies: 6

659

-- Name: pk_team_list; Type: SEQUENCE; Schema: public; Owner: codescore

660

661

662

CREATE SEQUENCE pk_team_list

663

INCREMENT BY 1

664

NO MAXVALUE

665

NO MINVALUE

666

CACHE 1;

667

668

669

ALTER TABLE public.pk_team_list OWNER TO codescore;

670

671

672

-- TOC entry 1769 (class 1259 OID 17775)

673

-- Dependencies: 2134 6

674

-- Name: team_list; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

675

676

677

CREATE TABLE team_list (

678

name character varying(255) NOT NULL,

679

schoolid integer NOT NULL,

680

teamid integer DEFAULT nextval('pk_team_list'::regclass) NOT NULL

681

);

682

683

684

ALTER TABLE public.team_list OWNER TO codescore;

685

686

687

-- TOC entry 1793 (class 1259 OID 17920)

688

-- Dependencies: 6

689

-- Name: pk_team_subs; Type: SEQUENCE; Schema: public; Owner: codescore

690

691

692

CREATE SEQUENCE pk_team_subs

693

INCREMENT BY 1

694

NO MAXVALUE

695

NO MINVALUE

696

CACHE 1;

697

698

699

ALTER TABLE public.pk_team_subs OWNER TO codescore;

700

701

702

-- TOC entry 1770 (class 1259 OID 17778)

703

-- Dependencies: 2135 2136 2137 2138 6

704

-- Name: team_subs; Type: TABLE; Schema: public; Owner: codescore; Tablespace:

705

706

707

CREATE TABLE team_subs (

708

compilestderr text,

709

compilestdout text,

710

exestderr text,

711

exestdout text,

712

langid integer NOT NULL,

713

pid integer NOT NULL,

714

points integer DEFAULT 0 NOT NULL,

715

source text,

716

stat integer,

717

stdoutdiff text,

718

subid integer DEFAULT nextval('pk_team_subs'::regclass) NOT NULL,

719

tid integer NOT NULL,

720

"time" timestamp with time zone DEFAULT now(),

721

CONSTRAINT non_neg_points CHECK ((points >= 0))

722

);

723

724

725

ALTER TABLE public.team_subs OWNER TO codescore;

726

727

728

-- TOC entry 2239 (class 0 OID 0)

729

-- Dependencies: 1770

730

-- Name: CONSTRAINT non_neg_points ON team_subs; Type: COMMENT; Schema: public; Owner: codescore

731

732

733

COMMENT ON CONSTRAINT non_neg_points ON team_subs IS 'the points column cannot be negitive';

734

735

736

737

-- TOC entry 21 (class 1255 OID 17787)

738

-- Dependencies: 6

739

-- Name: armor(bytea); Type: FUNCTION; Schema: public; Owner: postgres

740

741

742

CREATE FUNCTION armor(bytea) RETURNS text

743

AS '$libdir/pgcrypto', 'pg_armor'

744

LANGUAGE c IMMUTABLE STRICT;

745

746

747

ALTER FUNCTION public.armor(bytea) OWNER TO postgres;

748

749

750

-- TOC entry 22 (class 1255 OID 17788)

751

-- Dependencies: 6

752

-- Name: crypt(text, text); Type: FUNCTION; Schema: public; Owner: postgres

753

754

755

CREATE FUNCTION crypt(text, text) RETURNS text

756

AS '$libdir/pgcrypto', 'pg_crypt'

757

LANGUAGE c IMMUTABLE STRICT;

758

759

760

ALTER FUNCTION public.crypt(text, text) OWNER TO postgres;

761

762

763

-- TOC entry 23 (class 1255 OID 17789)

764

-- Dependencies: 6

765

-- Name: dearmor(text); Type: FUNCTION; Schema: public; Owner: postgres

766

767

768

CREATE FUNCTION dearmor(text) RETURNS bytea

769

AS '$libdir/pgcrypto', 'pg_dearmor'

770

LANGUAGE c IMMUTABLE STRICT;

771

772

773

ALTER FUNCTION public.dearmor(text) OWNER TO postgres;

774

775

776

-- TOC entry 24 (class 1255 OID 17790)

777

-- Dependencies: 6

778

-- Name: decrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

779

780

781

CREATE FUNCTION decrypt(bytea, bytea, text) RETURNS bytea

782

AS '$libdir/pgcrypto', 'pg_decrypt'

783

LANGUAGE c IMMUTABLE STRICT;

784

785

786

ALTER FUNCTION public.decrypt(bytea, bytea, text) OWNER TO postgres;

787

788

789

-- TOC entry 25 (class 1255 OID 17791)

790

-- Dependencies: 6

791

-- Name: decrypt_iv(bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

792

793

794

CREATE FUNCTION decrypt_iv(bytea, bytea, bytea, text) RETURNS bytea

795

AS '$libdir/pgcrypto', 'pg_decrypt_iv'

796

LANGUAGE c IMMUTABLE STRICT;

797

798

799

ALTER FUNCTION public.decrypt_iv(bytea, bytea, bytea, text) OWNER TO postgres;

800

801

802

-- TOC entry 26 (class 1255 OID 17792)

803

-- Dependencies: 6

804

-- Name: digest(text, text); Type: FUNCTION; Schema: public; Owner: postgres

805

806

807

CREATE FUNCTION digest(text, text) RETURNS bytea

808

AS '$libdir/pgcrypto', 'pg_digest'

809

LANGUAGE c IMMUTABLE STRICT;

810

811

812

ALTER FUNCTION public.digest(text, text) OWNER TO postgres;

813

814

815

-- TOC entry 27 (class 1255 OID 17793)

816

-- Dependencies: 6

817

-- Name: digest(bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

818

819

820

CREATE FUNCTION digest(bytea, text) RETURNS bytea

821

AS '$libdir/pgcrypto', 'pg_digest'

822

LANGUAGE c IMMUTABLE STRICT;

823

824

825

ALTER FUNCTION public.digest(bytea, text) OWNER TO postgres;

826

827

828

-- TOC entry 28 (class 1255 OID 17794)

829

-- Dependencies: 6

830

-- Name: encrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

831

832

833

CREATE FUNCTION encrypt(bytea, bytea, text) RETURNS bytea

834

AS '$libdir/pgcrypto', 'pg_encrypt'

835

LANGUAGE c IMMUTABLE STRICT;

836

837

838

ALTER FUNCTION public.encrypt(bytea, bytea, text) OWNER TO postgres;

839

840

841

-- TOC entry 29 (class 1255 OID 17795)

842

-- Dependencies: 6

843

-- Name: encrypt_iv(bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

844

845

846

CREATE FUNCTION encrypt_iv(bytea, bytea, bytea, text) RETURNS bytea

847

AS '$libdir/pgcrypto', 'pg_encrypt_iv'

848

LANGUAGE c IMMUTABLE STRICT;

849

850

851

ALTER FUNCTION public.encrypt_iv(bytea, bytea, bytea, text) OWNER TO postgres;

852

853

854

-- TOC entry 30 (class 1255 OID 17796)

855

-- Dependencies: 6

856

-- Name: gen_random_bytes(integer); Type: FUNCTION; Schema: public; Owner: postgres

857

858

859

CREATE FUNCTION gen_random_bytes(integer) RETURNS bytea

860

AS '$libdir/pgcrypto', 'pg_random_bytes'

861

LANGUAGE c STRICT;

862

863

864

ALTER FUNCTION public.gen_random_bytes(integer) OWNER TO postgres;

865

866

867

-- TOC entry 31 (class 1255 OID 17797)

868

-- Dependencies: 6

869

-- Name: gen_salt(text); Type: FUNCTION; Schema: public; Owner: postgres

870

871

872

CREATE FUNCTION gen_salt(text) RETURNS text

873

AS '$libdir/pgcrypto', 'pg_gen_salt'

874

LANGUAGE c STRICT;

875

876

877

ALTER FUNCTION public.gen_salt(text) OWNER TO postgres;

878

879

880

-- TOC entry 32 (class 1255 OID 17798)

881

-- Dependencies: 6

882

-- Name: gen_salt(text, integer); Type: FUNCTION; Schema: public; Owner: postgres

883

884

885

CREATE FUNCTION gen_salt(text, integer) RETURNS text

886

AS '$libdir/pgcrypto', 'pg_gen_salt_rounds'

887

LANGUAGE c STRICT;

888

889

890

ALTER FUNCTION public.gen_salt(text, integer) OWNER TO postgres;

891

892

893

-- TOC entry 33 (class 1255 OID 17799)

894

-- Dependencies: 6

895

-- Name: hmac(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres

896

897

898

CREATE FUNCTION hmac(text, text, text) RETURNS bytea

899

AS '$libdir/pgcrypto', 'pg_hmac'

900

LANGUAGE c IMMUTABLE STRICT;

901

902

903

ALTER FUNCTION public.hmac(text, text, text) OWNER TO postgres;

904

905

906

-- TOC entry 34 (class 1255 OID 17800)

907

-- Dependencies: 6

908

-- Name: hmac(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

909

910

911

CREATE FUNCTION hmac(bytea, bytea, text) RETURNS bytea

912

AS '$libdir/pgcrypto', 'pg_hmac'

913

LANGUAGE c IMMUTABLE STRICT;

914

915

916

ALTER FUNCTION public.hmac(bytea, bytea, text) OWNER TO postgres;

917

918

919

-- TOC entry 35 (class 1255 OID 17801)

920

-- Dependencies: 6

921

-- Name: pgp_key_id(bytea); Type: FUNCTION; Schema: public; Owner: postgres

922

923

924

CREATE FUNCTION pgp_key_id(bytea) RETURNS text

925

AS '$libdir/pgcrypto', 'pgp_key_id_w'

926

LANGUAGE c IMMUTABLE STRICT;

927

928

929

ALTER FUNCTION public.pgp_key_id(bytea) OWNER TO postgres;

930

931

932

-- TOC entry 36 (class 1255 OID 17802)

933

-- Dependencies: 6

934

-- Name: pgp_pub_decrypt(bytea, bytea); Type: FUNCTION; Schema: public; Owner: postgres

935

936

937

CREATE FUNCTION pgp_pub_decrypt(bytea, bytea) RETURNS text

938

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_text'

939

LANGUAGE c IMMUTABLE STRICT;

940

941

942

ALTER FUNCTION public.pgp_pub_decrypt(bytea, bytea) OWNER TO postgres;

943

944

945

-- TOC entry 37 (class 1255 OID 17803)

946

-- Dependencies: 6

947

-- Name: pgp_pub_decrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

948

949

950

CREATE FUNCTION pgp_pub_decrypt(bytea, bytea, text) RETURNS text

951

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_text'

952

LANGUAGE c IMMUTABLE STRICT;

953

954

955

ALTER FUNCTION public.pgp_pub_decrypt(bytea, bytea, text) OWNER TO postgres;

956

957

958

-- TOC entry 38 (class 1255 OID 17804)

959

-- Dependencies: 6

960

-- Name: pgp_pub_decrypt(bytea, bytea, text, text); Type: FUNCTION; Schema: public; Owner: postgres

961

962

963

CREATE FUNCTION pgp_pub_decrypt(bytea, bytea, text, text) RETURNS text

964

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_text'

965

LANGUAGE c IMMUTABLE STRICT;

966

967

968

ALTER FUNCTION public.pgp_pub_decrypt(bytea, bytea, text, text) OWNER TO postgres;

969

970

971

-- TOC entry 39 (class 1255 OID 17805)

972

-- Dependencies: 6

973

-- Name: pgp_pub_decrypt_bytea(bytea, bytea); Type: FUNCTION; Schema: public; Owner: postgres

974

975

976

CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea) RETURNS bytea

977

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_bytea'

978

LANGUAGE c IMMUTABLE STRICT;

979

980

981

ALTER FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea) OWNER TO postgres;

982

983

984

-- TOC entry 40 (class 1255 OID 17806)

985

-- Dependencies: 6

986

-- Name: pgp_pub_decrypt_bytea(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

987

988

989

CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea, text) RETURNS bytea

990

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_bytea'

991

LANGUAGE c IMMUTABLE STRICT;

992

993

994

ALTER FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea, text) OWNER TO postgres;

995

996

997

-- TOC entry 41 (class 1255 OID 17807)

998

-- Dependencies: 6

999

-- Name: pgp_pub_decrypt_bytea(bytea, bytea, text, text); Type: FUNCTION; Schema: public; Owner: postgres

1000

1001

1002

CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea, text, text) RETURNS bytea

1003

AS '$libdir/pgcrypto', 'pgp_pub_decrypt_bytea'

1004

LANGUAGE c IMMUTABLE STRICT;

1005

1006

1007

ALTER FUNCTION public.pgp_pub_decrypt_bytea(bytea, bytea, text, text) OWNER TO postgres;

1008

1009

1010

-- TOC entry 42 (class 1255 OID 17808)

1011

-- Dependencies: 6

1012

-- Name: pgp_pub_encrypt(text, bytea); Type: FUNCTION; Schema: public; Owner: postgres

1013

1014

1015

CREATE FUNCTION pgp_pub_encrypt(text, bytea) RETURNS bytea

1016

AS '$libdir/pgcrypto', 'pgp_pub_encrypt_text'

1017

LANGUAGE c STRICT;

1018

1019

1020

ALTER FUNCTION public.pgp_pub_encrypt(text, bytea) OWNER TO postgres;

1021

1022

1023

-- TOC entry 43 (class 1255 OID 17809)

1024

-- Dependencies: 6

1025

-- Name: pgp_pub_encrypt(text, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

1026

1027

1028

CREATE FUNCTION pgp_pub_encrypt(text, bytea, text) RETURNS bytea

1029

AS '$libdir/pgcrypto', 'pgp_pub_encrypt_text'

1030

LANGUAGE c STRICT;

1031

1032

1033

ALTER FUNCTION public.pgp_pub_encrypt(text, bytea, text) OWNER TO postgres;

1034

1035

1036

-- TOC entry 44 (class 1255 OID 17810)

1037

-- Dependencies: 6

1038

-- Name: pgp_pub_encrypt_bytea(bytea, bytea); Type: FUNCTION; Schema: public; Owner: postgres

1039

1040

1041

CREATE FUNCTION pgp_pub_encrypt_bytea(bytea, bytea) RETURNS bytea

1042

AS '$libdir/pgcrypto', 'pgp_pub_encrypt_bytea'

1043

LANGUAGE c STRICT;

1044

1045

1046

ALTER FUNCTION public.pgp_pub_encrypt_bytea(bytea, bytea) OWNER TO postgres;

1047

1048

1049

-- TOC entry 45 (class 1255 OID 17811)

1050

-- Dependencies: 6

1051

-- Name: pgp_pub_encrypt_bytea(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

1052

1053

1054

CREATE FUNCTION pgp_pub_encrypt_bytea(bytea, bytea, text) RETURNS bytea

1055

AS '$libdir/pgcrypto', 'pgp_pub_encrypt_bytea'

1056

LANGUAGE c STRICT;

1057

1058

1059

ALTER FUNCTION public.pgp_pub_encrypt_bytea(bytea, bytea, text) OWNER TO postgres;

1060

1061

1062

-- TOC entry 46 (class 1255 OID 17812)

1063

-- Dependencies: 6

1064

-- Name: pgp_sym_decrypt(bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

1065

1066

1067

CREATE FUNCTION pgp_sym_decrypt(bytea, text) RETURNS text

1068

AS '$libdir/pgcrypto', 'pgp_sym_decrypt_text'

1069

LANGUAGE c IMMUTABLE STRICT;

1070

1071

1072

ALTER FUNCTION public.pgp_sym_decrypt(bytea, text) OWNER TO postgres;

1073

1074

1075

-- TOC entry 47 (class 1255 OID 17813)

1076

-- Dependencies: 6

1077

-- Name: pgp_sym_decrypt(bytea, text, text); Type: FUNCTION; Schema: public; Owner: postgres

1078

1079

1080

CREATE FUNCTION pgp_sym_decrypt(bytea, text, text) RETURNS text

1081

AS '$libdir/pgcrypto', 'pgp_sym_decrypt_text'

1082

LANGUAGE c IMMUTABLE STRICT;

1083

1084

1085

ALTER FUNCTION public.pgp_sym_decrypt(bytea, text, text) OWNER TO postgres;

1086

1087

1088

-- TOC entry 48 (class 1255 OID 17814)

1089

-- Dependencies: 6

1090

-- Name: pgp_sym_decrypt_bytea(bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

1091

1092

1093

CREATE FUNCTION pgp_sym_decrypt_bytea(bytea, text) RETURNS bytea

1094

AS '$libdir/pgcrypto', 'pgp_sym_decrypt_bytea'

1095

LANGUAGE c IMMUTABLE STRICT;

1096

1097

1098

ALTER FUNCTION public.pgp_sym_decrypt_bytea(bytea, text) OWNER TO postgres;

1099

1100

1101

-- TOC entry 49 (class 1255 OID 17815)

1102

-- Dependencies: 6

1103

-- Name: pgp_sym_decrypt_bytea(bytea, text, text); Type: FUNCTION; Schema: public; Owner: postgres

1104

1105

1106

CREATE FUNCTION pgp_sym_decrypt_bytea(bytea, text, text) RETURNS bytea

1107

AS '$libdir/pgcrypto', 'pgp_sym_decrypt_bytea'

1108

LANGUAGE c IMMUTABLE STRICT;

1109

1110

1111

ALTER FUNCTION public.pgp_sym_decrypt_bytea(bytea, text, text) OWNER TO postgres;

1112

1113

1114

-- TOC entry 50 (class 1255 OID 17816)

1115

-- Dependencies: 6

1116

-- Name: pgp_sym_encrypt(text, text); Type: FUNCTION; Schema: public; Owner: postgres

1117

1118

1119

CREATE FUNCTION pgp_sym_encrypt(text, text) RETURNS bytea

1120

AS '$libdir/pgcrypto', 'pgp_sym_encrypt_text'

1121

LANGUAGE c STRICT;

1122

1123

1124

ALTER FUNCTION public.pgp_sym_encrypt(text, text) OWNER TO postgres;

1125

1126

1127

-- TOC entry 51 (class 1255 OID 17817)

1128

-- Dependencies: 6

1129

-- Name: pgp_sym_encrypt(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres

1130

1131

1132

CREATE FUNCTION pgp_sym_encrypt(text, text, text) RETURNS bytea

1133

AS '$libdir/pgcrypto', 'pgp_sym_encrypt_text'

1134

LANGUAGE c STRICT;

1135

1136

1137

ALTER FUNCTION public.pgp_sym_encrypt(text, text, text) OWNER TO postgres;

1138

1139

1140

-- TOC entry 52 (class 1255 OID 17818)

1141

-- Dependencies: 6

1142

-- Name: pgp_sym_encrypt_bytea(bytea, text); Type: FUNCTION; Schema: public; Owner: postgres

1143

1144

1145

CREATE FUNCTION pgp_sym_encrypt_bytea(bytea, text) RETURNS bytea

1146

AS '$libdir/pgcrypto', 'pgp_sym_encrypt_bytea'

1147

LANGUAGE c STRICT;

1148

1149

1150

ALTER FUNCTION public.pgp_sym_encrypt_bytea(bytea, text) OWNER TO postgres;

1151

1152

1153

-- TOC entry 53 (class 1255 OID 17819)

1154

-- Dependencies: 6

1155

-- Name: pgp_sym_encrypt_bytea(bytea, text, text); Type: FUNCTION; Schema: public; Owner: postgres

1156

1157

1158

CREATE FUNCTION pgp_sym_encrypt_bytea(bytea, text, text) RETURNS bytea

1159

AS '$libdir/pgcrypto', 'pgp_sym_encrypt_bytea'

1160

LANGUAGE c STRICT;

1161

1162

1163

ALTER FUNCTION public.pgp_sym_encrypt_bytea(bytea, text, text) OWNER TO postgres;

1164

1165

1166

-- TOC entry 54 (class 1255 OID 17820)

1167

-- Dependencies: 6 551

1168

-- Name: prob_desc_index(); Type: FUNCTION; Schema: public; Owner: postgres

1169

1170

1171

CREATE FUNCTION prob_desc_index() RETURNS trigger

1172

AS $$

1173

DECLARE

1174

tag_list text;

1175

begin

1176

select str_list(tl.name) into tag_list from prob_tags as tgs join prob_tag_list as tl on tgs.tagid=tl.tagid where probid=NEW.problemid;

1177

new.clean_desc := strip_html(NEW.description);

1178

new.parsed_document :=

1179

setweight(to_tsvector(NEW.name),'A') ||

1180

setweight(to_tsvector(NEW.unixname),'B') ||

1181

setweight(to_tsvector(tag_list),'B') ||

1182

setweight(to_tsvector(NEW.clean_desc),'C') ||

1183

setweight(to_tsvector(NEW.sampleinput),'D') ||

1184

setweight(to_tsvector(NEW.sampleoutput),'D');

1185

return new;

1186

end

1187

1188

LANGUAGE plpgsql;

1189

1190

1191

ALTER FUNCTION public.prob_desc_index() OWNER TO postgres;

1192

1193

1194

-- TOC entry 55 (class 1255 OID 17821)

1195

-- Dependencies: 6 551

1196

-- Name: prob_desc_update(); Type: FUNCTION; Schema: public; Owner: codescore

1197

1198

1199

CREATE FUNCTION prob_desc_update() RETURNS trigger

1200

AS $$

1201

begin

1202

update prob_list set description = NEW.description, sampleinput=NEW.sampleinput, sampleoutput=NEW.sampleoutput WHERE problemid = NEW.problemid;

1203

end

1204

1205

LANGUAGE plpgsql;

1206

1207

1208

ALTER FUNCTION public.prob_desc_update() OWNER TO codescore;

1209

1210

1211

-- TOC entry 56 (class 1255 OID 17822)

1212

-- Dependencies: 6 551

1213

-- Name: prob_reindex_all(); Type: FUNCTION; Schema: public; Owner: postgres

1214

1215

1216

CREATE FUNCTION prob_reindex_all() RETURNS integer

1217

AS $$

1218

DECLARE

1219

pr RECORD;

1220

prcnt integer;

1221

begin

1222

prcnt:=0;

1223

for pr IN SELECT problemid from prob_list order by problemid LOOP

1224

PERFORM reindex_prob(pr.problemid);

1225

prcnt:=prcnt+1;

1226

END LOOP;

1227

return prcnt;

1228

end

1229

1230

LANGUAGE plpgsql COST 900;

1231

1232

1233

ALTER FUNCTION public.prob_reindex_all() OWNER TO postgres;

1234

1235

1236

-- TOC entry 57 (class 1255 OID 17823)

1237

-- Dependencies: 551 6

1238

-- Name: prob_reindex_trigger(); Type: FUNCTION; Schema: public; Owner: postgres

1239

1240

1241

CREATE FUNCTION prob_reindex_trigger() RETURNS trigger

1242

AS $$

1243

DECLARE

1244

tag_list text;

1245

begin

1246

new:=reindex_prob(new);

1247

return new;

1248

end

1249

1250

LANGUAGE plpgsql;

1251

1252

1253

ALTER FUNCTION public.prob_reindex_trigger() OWNER TO postgres;

1254

1255

1256

-- TOC entry 58 (class 1255 OID 17824)

1257

-- Dependencies: 6 415

1258

-- Name: prob_search(tsquery); Type: FUNCTION; Schema: public; Owner: postgres

1259

1260

1261

CREATE FUNCTION prob_search(tsq tsquery) RETURNS SETOF prob_ts_result

1262

AS $_$

1263

SELECT (

1264

(pl.name,

1265

ts_rank(parsed_document,$1) ,

1266

ts_headline('english',pl.description,$1),

1267

pl.problemid)::prob_ts_result) as result

1268

FROM prob_list as pl where parsed_document @@ $1 ORDER BY ts_rank(parsed_document,$1);

1269

$_$

1270

LANGUAGE sql COST 300 ROWS 100;

1271

1272

1273

ALTER FUNCTION public.prob_search(tsq tsquery) OWNER TO postgres;

1274

1275

1276

-- TOC entry 59 (class 1255 OID 17825)

1277

-- Dependencies: 6 551

1278

-- Name: prob_tag_reindex(); Type: FUNCTION; Schema: public; Owner: postgres

1279

1280

1281

CREATE FUNCTION prob_tag_reindex() RETURNS trigger

1282

AS $$

1283

DECLARE

1284

prob prob_list;

1285

begin

1286

prob:=reindex_prob(NEW.probid);

1287

return NEW;

1288

end

1289

1290

LANGUAGE plpgsql;

1291

1292

1293

ALTER FUNCTION public.prob_tag_reindex() OWNER TO postgres;

1294

1295

1296

-- TOC entry 60 (class 1255 OID 17826)

1297

-- Dependencies: 387 6 551 387

1298

-- Name: reindex_prob(prob_list); Type: FUNCTION; Schema: public; Owner: postgres

1299

1300

1301

CREATE FUNCTION reindex_prob(prob_list) RETURNS prob_list

1302

AS $_$

1303

DECLARE

1304

tag_list text;

1305

begin

1306

select str_list(tl.name) into tag_list from prob_tags as tgs join prob_tag_list as tl on tgs.tagid=tl.tagid where probid=$1.problemid;

1307

$1.clean_desc := strip_html($1.description);

1308

$1.parsed_document :=

1309

setweight(to_tsvector($1.name),'A') ||

1310

setweight(to_tsvector($1.unixname),'B') ||

1311

setweight(to_tsvector(tag_list),'B') ||

1312

setweight(to_tsvector($1.clean_desc),'C') ||

1313

setweight(to_tsvector($1.sampleinput),'D') ||

1314

setweight(to_tsvector($1.sampleoutput),'D');

1315

return $1;

1316

end

1317

$_$

1318

LANGUAGE plpgsql;

1319

1320

1321

ALTER FUNCTION public.reindex_prob(prob_list) OWNER TO postgres;

1322

1323

1324

-- TOC entry 61 (class 1255 OID 17827)

1325

-- Dependencies: 551 6 387

1326

-- Name: reindex_prob(integer); Type: FUNCTION; Schema: public; Owner: postgres

1327

1328

1329

CREATE FUNCTION reindex_prob(integer) RETURNS prob_list

1330

AS $_$

1331

DECLARE

1332

pl prob_list;

1333

begin

1334

select * into pl from prob_list where problemid=$1;

1335

pl:=reindex_prob(pl);

1336

update prob_list set parsed_document=pl.parsed_document, clean_desc=pl.clean_desc where problemid=pl.problemid;

1337

return pl;

1338

end

1339

$_$

1340

LANGUAGE plpgsql;

1341

1342

1343

ALTER FUNCTION public.reindex_prob(integer) OWNER TO postgres;

1344

1345

1346

-- TOC entry 62 (class 1255 OID 17828)

1347

-- Dependencies: 6

1348

-- Name: str_list_add(text, text); Type: FUNCTION; Schema: public; Owner: postgres

1349

1350

1351

CREATE FUNCTION str_list_add(text, text) RETURNS text

1352

AS $_$

1353

SELECT ($1||' '||$2)::text;

1354

$_$

1355

LANGUAGE sql IMMUTABLE;

1356

1357

1358

ALTER FUNCTION public.str_list_add(text, text) OWNER TO postgres;

1359

1360

1361

-- TOC entry 63 (class 1255 OID 17829)

1362

-- Dependencies: 6

1363

-- Name: strip_html(text); Type: FUNCTION; Schema: public; Owner: postgres

1364

1365

1366

CREATE FUNCTION strip_html(text) RETURNS text

1367

AS $_$

1368

select regexp_replace(lower($1),'<[^>]+>|</[^>]*>','','ig');

1369

$_$

1370

LANGUAGE sql IMMUTABLE;

1371

1372

1373

ALTER FUNCTION public.strip_html(text) OWNER TO postgres;

1374

1375

1376

-- TOC entry 552 (class 1255 OID 17830)

1377

-- Dependencies: 62 6

1378

-- Name: str_list(text); Type: AGGREGATE; Schema: public; Owner: postgres

1379

1380

1381

CREATE AGGREGATE str_list(text) (

1382

SFUNC = str_list_add,

1383

STYPE = text,

1384

INITCOND = ''

1385

);

1386

1387

1388

ALTER AGGREGATE public.str_list(text) OWNER TO postgres;

1389

1390

1391

-- TOC entry 1485 (class 2753 OID 17831)

1392

-- Dependencies: 6

1393

-- Name: gin_tsvector_ops; Type: OPERATOR FAMILY; Schema: public; Owner: acornett

1394

1395

1396

CREATE OPERATOR FAMILY gin_tsvector_ops USING gin;

1397

1398

1399

ALTER OPERATOR FAMILY public.gin_tsvector_ops USING gin OWNER TO acornett;

1400

1401

1402

-- TOC entry 1368 (class 2616 OID 17832)

1403

-- Dependencies: 1485 6

1404

-- Name: gin_tsvector_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres

1405

1406

1407

CREATE OPERATOR CLASS gin_tsvector_ops

1408

FOR TYPE tsvector USING gin AS

1409

STORAGE text ,

1410

OPERATOR 1 @@(tsvector,tsquery) ,

1411

OPERATOR 2 @@@(tsvector,tsquery) RECHECK ,

1412

FUNCTION 1 bttextcmp(text,text) ,

1413

FUNCTION 2 gin_extract_tsvector(tsvector,internal) ,

1414

FUNCTION 3 gin_extract_tsquery(tsquery,internal,smallint) ,

1415

FUNCTION 4 gin_tsquery_consistent(internal,smallint,tsquery);

1416

1417

1418

ALTER OPERATOR CLASS public.gin_tsvector_ops USING gin OWNER TO postgres;

1419

1420

1421

-- TOC entry 1486 (class 2753 OID 17839)

1422

-- Dependencies: 6

1423

-- Name: gist_tp_tsquery_ops; Type: OPERATOR FAMILY; Schema: public; Owner: acornett

1424

1425

1426

CREATE OPERATOR FAMILY gist_tp_tsquery_ops USING gist;

1427

1428

1429

ALTER OPERATOR FAMILY public.gist_tp_tsquery_ops USING gist OWNER TO acornett;

1430

1431

1432

-- TOC entry 1369 (class 2616 OID 17840)

1433

-- Dependencies: 6 1486

1434

-- Name: gist_tp_tsquery_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres

1435

1436

1437

CREATE OPERATOR CLASS gist_tp_tsquery_ops

1438

FOR TYPE tsquery USING gist AS

1439

STORAGE bigint ,

1440

OPERATOR 7 @>(tsquery,tsquery) RECHECK ,

1441

OPERATOR 8 <@(tsquery,tsquery) RECHECK ,

1442

FUNCTION 1 gtsquery_consistent(bigint,internal,integer) ,

1443

FUNCTION 2 gtsquery_union(internal,internal) ,

1444

FUNCTION 3 gtsquery_compress(internal) ,

1445

FUNCTION 4 gtsquery_decompress(internal) ,

1446

FUNCTION 5 gtsquery_penalty(internal,internal,internal) ,

1447

FUNCTION 6 gtsquery_picksplit(internal,internal) ,

1448

FUNCTION 7 gtsquery_same(bigint,bigint,internal);

1449

1450

1451

ALTER OPERATOR CLASS public.gist_tp_tsquery_ops USING gist OWNER TO postgres;

1452

1453

1454

-- TOC entry 1487 (class 2753 OID 17850)

1455

-- Dependencies: 6

1456

-- Name: gist_tsvector_ops; Type: OPERATOR FAMILY; Schema: public; Owner: acornett

1457

1458

1459

CREATE OPERATOR FAMILY gist_tsvector_ops USING gist;

1460

1461

1462

ALTER OPERATOR FAMILY public.gist_tsvector_ops USING gist OWNER TO acornett;

1463

1464

1465

-- TOC entry 1370 (class 2616 OID 17851)

1466

-- Dependencies: 1487 6

1467

-- Name: gist_tsvector_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres

1468

1469

1470

CREATE OPERATOR CLASS gist_tsvector_ops

1471

FOR TYPE tsvector USING gist AS

1472

STORAGE gtsvector ,

1473

OPERATOR 1 @@(tsvector,tsquery) RECHECK ,

1474

FUNCTION 1 gtsvector_consistent(gtsvector,internal,integer) ,

1475

FUNCTION 2 gtsvector_union(internal,internal) ,

1476

FUNCTION 3 gtsvector_compress(internal) ,

1477

FUNCTION 4 gtsvector_decompress(internal) ,

1478

FUNCTION 5 gtsvector_penalty(internal,internal,internal) ,

1479

FUNCTION 6 gtsvector_picksplit(internal,internal) ,

1480

FUNCTION 7 gtsvector_same(gtsvector,gtsvector,internal);

1481

1482

1483

ALTER OPERATOR CLASS public.gist_tsvector_ops USING gist OWNER TO postgres;

1484

1485

1486

-- TOC entry 1488 (class 2753 OID 17860)

1487

-- Dependencies: 6

1488

-- Name: tsquery_ops; Type: OPERATOR FAMILY; Schema: public; Owner: acornett

1489

1490

1491

CREATE OPERATOR FAMILY tsquery_ops USING btree;

1492

1493

1494

ALTER OPERATOR FAMILY public.tsquery_ops USING btree OWNER TO acornett;

1495

1496

1497

-- TOC entry 1371 (class 2616 OID 17861)

1498

-- Dependencies: 1488 6

1499

-- Name: tsquery_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres

1500

1501

1502

CREATE OPERATOR CLASS tsquery_ops

1503

FOR TYPE tsquery USING btree AS

1504

OPERATOR 1 <(tsquery,tsquery) ,

1505

OPERATOR 2 <=(tsquery,tsquery) ,

1506

OPERATOR 3 =(tsquery,tsquery) ,

1507

OPERATOR 4 >=(tsquery,tsquery) ,

1508

OPERATOR 5 >(tsquery,tsquery) ,

1509

FUNCTION 1 tsquery_cmp(tsquery,tsquery);

1510

1511

1512

ALTER OPERATOR CLASS public.tsquery_ops USING btree OWNER TO postgres;

1513

1514

1515

-- TOC entry 1489 (class 2753 OID 17868)

1516

-- Dependencies: 6

1517

-- Name: tsvector_ops; Type: OPERATOR FAMILY; Schema: public; Owner: acornett

1518

1519

1520

CREATE OPERATOR FAMILY tsvector_ops USING btree;

1521

1522

1523

ALTER OPERATOR FAMILY public.tsvector_ops USING btree OWNER TO acornett;

1524

1525

1526

-- TOC entry 1372 (class 2616 OID 17869)

1527

-- Dependencies: 1489 6

1528

-- Name: tsvector_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres

1529

1530

1531

CREATE OPERATOR CLASS tsvector_ops

1532

FOR TYPE tsvector USING btree AS

1533

OPERATOR 1 <(tsvector,tsvector) ,

1534

OPERATOR 2 <=(tsvector,tsvector) ,

1535

OPERATOR 3 =(tsvector,tsvector) ,

1536

OPERATOR 4 >=(tsvector,tsvector) ,

1537

OPERATOR 5 >(tsvector,tsvector) ,

1538

FUNCTION 1 tsvector_cmp(tsvector,tsvector);

1539

1540

1541

ALTER OPERATOR CLASS public.tsvector_ops USING btree OWNER TO postgres;

1542

1543

1544

-- TOC entry 1771 (class 1259 OID 17876)

1545

-- Dependencies: 6

1546

-- Name: pk_ppl_coaches; Type: SEQUENCE; Schema: public; Owner: codescore

1547

1548

1549

CREATE SEQUENCE pk_ppl_coaches

1550

START WITH 1

1551

INCREMENT BY 1

1552

NO MAXVALUE

1553

NO MINVALUE

1554

CACHE 1;

1555

1556

1557

ALTER TABLE public.pk_ppl_coaches OWNER TO codescore;

1558

1559

1560

-- TOC entry 1772 (class 1259 OID 17878)

1561

-- Dependencies: 6

1562

-- Name: pk_ppl_comps; Type: SEQUENCE; Schema: public; Owner: codescore

1563

1564

1565

CREATE SEQUENCE pk_ppl_comps

1566

INCREMENT BY 1

1567

NO MAXVALUE

1568

NO MINVALUE

1569

CACHE 1;

1570

1571

1572

ALTER TABLE public.pk_ppl_comps OWNER TO codescore;

1573

1574

1575

-- TOC entry 1774 (class 1259 OID 17882)

1576

-- Dependencies: 6

1577

-- Name: pk_ppl_judges; Type: SEQUENCE; Schema: public; Owner: codescore

1578

1579

1580

CREATE SEQUENCE pk_ppl_judges

1581

START WITH 1

1582

INCREMENT BY 1

1583

NO MAXVALUE

1584

NO MINVALUE

1585

CACHE 1;

1586

1587

1588

ALTER TABLE public.pk_ppl_judges OWNER TO codescore;

1589

1590

1591

-- TOC entry 1781 (class 1259 OID 17896)

1592

-- Dependencies: 6

1593

-- Name: pk_prob_set; Type: SEQUENCE; Schema: public; Owner: codescore

1594

1595

1596

CREATE SEQUENCE pk_prob_set

1597

START WITH 1

1598

INCREMENT BY 1

1599

NO MAXVALUE

1600

NO MINVALUE

1601

CACHE 1;

1602

1603

1604

ALTER TABLE public.pk_prob_set OWNER TO codescore;

1605

1606

1607

-- TOC entry 1784 (class 1259 OID 17902)

1608

-- Dependencies: 6

1609

-- Name: pk_prob_tag_list; Type: SEQUENCE; Schema: public; Owner: codescore

1610

1611

1612

CREATE SEQUENCE pk_prob_tag_list

1613

START WITH 1

1614

INCREMENT BY 1

1615

NO MAXVALUE

1616

NO MINVALUE

1617

CACHE 1;

1618

1619

1620

ALTER TABLE public.pk_prob_tag_list OWNER TO codescore;

1621

1622

1623

-- TOC entry 1785 (class 1259 OID 17904)

1624

-- Dependencies: 6

1625

-- Name: pk_prob_tags; Type: SEQUENCE; Schema: public; Owner: codescore

1626

1627

1628

CREATE SEQUENCE pk_prob_tags

1629

START WITH 1

1630

INCREMENT BY 1

1631

NO MAXVALUE

1632

NO MINVALUE

1633

CACHE 1;

1634

1635

1636

ALTER TABLE public.pk_prob_tags OWNER TO codescore;

1637

1638

1639

-- TOC entry 1792 (class 1259 OID 17918)

1640

-- Dependencies: 6

1641

-- Name: pk_team_pass; Type: SEQUENCE; Schema: public; Owner: codescore

1642

1643

1644

CREATE SEQUENCE pk_team_pass

1645

START WITH 1

1646

INCREMENT BY 1

1647

NO MAXVALUE

1648

NO MINVALUE

1649

CACHE 1;

1650

1651

1652

ALTER TABLE public.pk_team_pass OWNER TO codescore;

1653

1654

1655

-- TOC entry 2142 (class 2606 OID 17926)

1656

-- Dependencies: 1745 1745 1745

1657

-- Name: ppl_coaches_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1658

1659

1660

ALTER TABLE ONLY ppl_coaches

1661

ADD CONSTRAINT ppl_coaches_pkey PRIMARY KEY (personid, schoolid);

1662

1663

1664

1665

-- TOC entry 2144 (class 2606 OID 17928)

1666

-- Dependencies: 1746 1746

1667

-- Name: ppl_comps_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1668

1669

1670

ALTER TABLE ONLY ppl_comps

1671

ADD CONSTRAINT ppl_comps_pkey PRIMARY KEY (bad);

1672

1673

1674

1675

-- TOC entry 2146 (class 2606 OID 18715)

1676

-- Dependencies: 1747 1747

1677

-- Name: ppl_email_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1678

1679

1680

ALTER TABLE ONLY ppl_email

1681

ADD CONSTRAINT ppl_email_pkey PRIMARY KEY (emailid);

1682

1683

1684

1685

-- TOC entry 2148 (class 2606 OID 17932)

1686

-- Dependencies: 1747 1747

1687

-- Name: ppl_email_unique; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1688

1689

1690

ALTER TABLE ONLY ppl_email

1691

ADD CONSTRAINT ppl_email_unique UNIQUE (address);

1692

1693

1694

1695

-- TOC entry 2150 (class 2606 OID 17934)

1696

-- Dependencies: 1748 1748 1748

1697

-- Name: ppl_judges_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1698

1699

1700

ALTER TABLE ONLY ppl_judges

1701

ADD CONSTRAINT ppl_judges_pkey PRIMARY KEY (compid, personid);

1702

1703

1704

1705

-- TOC entry 2152 (class 2606 OID 17936)

1706

-- Dependencies: 1749 1749

1707

-- Name: ppl_list_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1708

1709

1710

ALTER TABLE ONLY ppl_list

1711

ADD CONSTRAINT ppl_list_pkey PRIMARY KEY (personid);

1712

1713

1714

1715

-- TOC entry 2154 (class 2606 OID 17938)

1716

-- Dependencies: 1749 1749

1717

-- Name: ppl_name_unique; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1718

1719

1720

ALTER TABLE ONLY ppl_list

1721

ADD CONSTRAINT ppl_name_unique UNIQUE (name);

1722

1723

1724

1725

-- TOC entry 2156 (class 2606 OID 17940)

1726

-- Dependencies: 1750 1750

1727

-- Name: ppl_regions_name; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:

1728

1729

1730

ALTER TABLE ONLY ppl_region

1731

ADD CONSTRAINT ppl_regions_name UNIQUE ("Name");

1732

1733

1734

1735

-- TOC entry 2158 (class 2606 OID 17942)

1736

-- Dependencies: 1750 1750

1737

-- Name: ppl_regions_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:

1738

1739

1740

ALTER TABLE ONLY ppl_region

1741

ADD CONSTRAINT ppl_regions_pk PRIMARY KEY (regionid);

1742

1743

1744

1745

-- TOC entry 2161 (class 2606 OID 17944)

1746

-- Dependencies: 1752 1752

1747

-- Name: ppl_schools_name_unique; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1748

1749

1750

ALTER TABLE ONLY ppl_schools

1751

ADD CONSTRAINT ppl_schools_name_unique UNIQUE (name);

1752

1753

1754

1755

-- TOC entry 2163 (class 2606 OID 17946)

1756

-- Dependencies: 1752 1752

1757

-- Name: ppl_schools_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1758

1759

1760

ALTER TABLE ONLY ppl_schools

1761

ADD CONSTRAINT ppl_schools_pkey PRIMARY KEY (schoolid);

1762

1763

1764

1765

-- TOC entry 2202 (class 2606 OID 18154)

1766

-- Dependencies: 1794 1794

1767

-- Name: prob_cases_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1768

1769

1770

ALTER TABLE ONLY prob_cases

1771

ADD CONSTRAINT prob_cases_pkey PRIMARY KEY (caseid);

1772

1773

1774

1775

-- TOC entry 2165 (class 2606 OID 17950)

1776

-- Dependencies: 1754 1754

1777

-- Name: prob_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1778

1779

1780

ALTER TABLE ONLY prob_comments

1781

ADD CONSTRAINT prob_comments_pkey PRIMARY KEY (comid);

1782

1783

1784

1785

-- TOC entry 2167 (class 2606 OID 17952)

1786

-- Dependencies: 1755 1755

1787

-- Name: prob_descfigs_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1788

1789

1790

ALTER TABLE ONLY prob_descfigs

1791

ADD CONSTRAINT prob_descfigs_pkey PRIMARY KEY (figid);

1792

1793

1794

1795

-- TOC entry 2169 (class 2606 OID 17954)

1796

-- Dependencies: 1756 1756

1797

-- Name: prob_list_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1798

1799

1800

ALTER TABLE ONLY prob_list

1801

ADD CONSTRAINT prob_list_pkey PRIMARY KEY (problemid);

1802

1803

1804

1805

-- TOC entry 2174 (class 2606 OID 17956)

1806

-- Dependencies: 1757 1757 1757

1807

-- Name: prob_set_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1808

1809

1810

ALTER TABLE ONLY prob_set

1811

ADD CONSTRAINT prob_set_pkey PRIMARY KEY (compid, probid);

1812

1813

1814

1815

-- TOC entry 2176 (class 2606 OID 18723)

1816

-- Dependencies: 1758 1758

1817

-- Name: prob_solutions_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1818

1819

1820

ALTER TABLE ONLY prob_solutions

1821

ADD CONSTRAINT prob_solutions_pkey PRIMARY KEY (sid);

1822

1823

1824

1825

-- TOC entry 2178 (class 2606 OID 17960)

1826

-- Dependencies: 1759 1759

1827

-- Name: prob_status_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1828

1829

1830

ALTER TABLE ONLY prob_status

1831

ADD CONSTRAINT prob_status_pkey PRIMARY KEY (statid);

1832

1833

1834

1835

-- TOC entry 2180 (class 2606 OID 17962)

1836

-- Dependencies: 1760 1760

1837

-- Name: prob_tag_list_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1838

1839

1840

ALTER TABLE ONLY prob_tag_list

1841

ADD CONSTRAINT prob_tag_list_pkey PRIMARY KEY (tagid);

1842

1843

1844

1845

-- TOC entry 2184 (class 2606 OID 17964)

1846

-- Dependencies: 1761 1761 1761

1847

-- Name: prob_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1848

1849

1850

ALTER TABLE ONLY prob_tags

1851

ADD CONSTRAINT prob_tags_pkey PRIMARY KEY (probid, tagid);

1852

1853

1854

1855

-- TOC entry 2172 (class 2606 OID 17966)

1856

-- Dependencies: 1756 1756

1857

-- Name: prob_unixname_unique; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1858

1859

1860

ALTER TABLE ONLY prob_list

1861

ADD CONSTRAINT prob_unixname_unique UNIQUE (unixname);

1862

1863

1864

1865

-- TOC entry 2186 (class 2606 OID 17968)

1866

-- Dependencies: 1763 1763

1867

-- Name: prob_version_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:

1868

1869

1870

ALTER TABLE ONLY prob_versions

1871

ADD CONSTRAINT prob_version_pk PRIMARY KEY (versionid);

1872

1873

1874

1875

-- TOC entry 2188 (class 2606 OID 17970)

1876

-- Dependencies: 1764 1764

1877

-- Name: sys_clari_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1878

1879

1880

ALTER TABLE ONLY sys_clari

1881

ADD CONSTRAINT sys_clari_pkey PRIMARY KEY (cid);

1882

1883

1884

1885

-- TOC entry 2190 (class 2606 OID 17972)

1886

-- Dependencies: 1765 1765

1887

-- Name: sys_compargs_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1888

1889

1890

ALTER TABLE ONLY sys_compargs

1891

ADD CONSTRAINT sys_compargs_pkey PRIMARY KEY (argid);

1892

1893

1894

1895

-- TOC entry 2192 (class 2606 OID 17974)

1896

-- Dependencies: 1766 1766

1897

-- Name: sys_comps_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1898

1899

1900

ALTER TABLE ONLY sys_comps

1901

ADD CONSTRAINT sys_comps_pkey PRIMARY KEY (compid);

1902

1903

1904

1905

-- TOC entry 2194 (class 2606 OID 17976)

1906

-- Dependencies: 1767 1767

1907

-- Name: sys_exeargs_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1908

1909

1910

ALTER TABLE ONLY sys_exeargs

1911

ADD CONSTRAINT sys_exeargs_pkey PRIMARY KEY (argid);

1912

1913

1914

1915

-- TOC entry 2196 (class 2606 OID 17978)

1916

-- Dependencies: 1768 1768

1917

-- Name: sys_langs_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1918

1919

1920

ALTER TABLE ONLY sys_langs

1921

ADD CONSTRAINT sys_langs_pkey PRIMARY KEY (lid);

1922

1923

1924

1925

-- TOC entry 2182 (class 2606 OID 18727)

1926

-- Dependencies: 1760 1760

1927

-- Name: tag_name_unique; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1928

1929

1930

ALTER TABLE ONLY prob_tag_list

1931

ADD CONSTRAINT tag_name_unique UNIQUE (name);

1932

1933

1934

1935

-- TOC entry 2198 (class 2606 OID 17980)

1936

-- Dependencies: 1769 1769

1937

-- Name: team_list_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1938

1939

1940

ALTER TABLE ONLY team_list

1941

ADD CONSTRAINT team_list_pkey PRIMARY KEY (teamid);

1942

1943

1944

1945

-- TOC entry 2200 (class 2606 OID 18243)

1946

-- Dependencies: 1770 1770

1947

-- Name: team_subs_pkey; Type: CONSTRAINT; Schema: public; Owner: codescore; Tablespace:

1948

1949

1950

ALTER TABLE ONLY team_subs

1951

ADD CONSTRAINT team_subs_pkey PRIMARY KEY (subid);

1952

1953

1954

1955

-- TOC entry 2159 (class 1259 OID 17983)

1956

-- Dependencies: 1752

1957

-- Name: fki_ppl_school_region; Type: INDEX; Schema: public; Owner: codescore; Tablespace:

1958

1959

1960

CREATE INDEX fki_ppl_school_region ON ppl_schools USING btree (regionid);

1961

1962

1963

1964

-- TOC entry 2170 (class 1259 OID 17984)

1965

-- Dependencies: 1756

1966

-- Name: prob_ts; Type: INDEX; Schema: public; Owner: codescore; Tablespace:

1967

1968

1969

CREATE INDEX prob_ts ON prob_list USING gin (parsed_document);

1970

1971

1972

1973

-- TOC entry 2233 (class 2620 OID 17985)

1974

-- Dependencies: 59 1761

1975

-- Name: prob_tag_index; Type: TRIGGER; Schema: public; Owner: codescore

1976

1977

1978

CREATE TRIGGER prob_tag_index

1979

AFTER INSERT OR DELETE OR UPDATE ON prob_tags

1980

FOR EACH ROW

1981

EXECUTE PROCEDURE prob_tag_reindex();

1982

1983

1984

1985

-- TOC entry 2234 (class 2620 OID 17986)

1986

-- Dependencies: 1763 55

1987

-- Name: update_prob_desc; Type: TRIGGER; Schema: public; Owner: postgres

1988

1989

1990

CREATE TRIGGER update_prob_desc

1991

AFTER INSERT OR UPDATE ON prob_versions

1992

FOR EACH ROW

1993

EXECUTE PROCEDURE prob_desc_update();

1994

1995

1996

1997

-- TOC entry 2232 (class 2620 OID 17987)

1998

-- Dependencies: 1756 57

1999

-- Name: update_prob_index; Type: TRIGGER; Schema: public; Owner: codescore

2000

2001

2002

CREATE TRIGGER update_prob_index

2003

BEFORE INSERT OR UPDATE ON prob_list

2004

FOR EACH ROW

2005

EXECUTE PROCEDURE prob_reindex_trigger();

2006

2007

2008

2009

-- TOC entry 2203 (class 2606 OID 17988)

2010

-- Dependencies: 1745 2151 1749

2011

-- Name: ppl_coaches_personid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2012

2013

2014

ALTER TABLE ONLY ppl_coaches

2015

ADD CONSTRAINT ppl_coaches_personid_fkey FOREIGN KEY (personid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE CASCADE;

2016

2017

2018

2019

-- TOC entry 2204 (class 2606 OID 17993)

2020

-- Dependencies: 1752 2162 1745

2021

-- Name: ppl_coaches_schoolid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2022

2023

2024

ALTER TABLE ONLY ppl_coaches

2025

ADD CONSTRAINT ppl_coaches_schoolid_fkey FOREIGN KEY (schoolid) REFERENCES ppl_schools(schoolid) ON UPDATE CASCADE ON DELETE CASCADE;

2026

2027

2028

2029

-- TOC entry 2205 (class 2606 OID 17998)

2030

-- Dependencies: 1766 1746 2191

2031

-- Name: ppl_comps_cid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2032

2033

2034

ALTER TABLE ONLY ppl_comps

2035

ADD CONSTRAINT ppl_comps_cid_fkey FOREIGN KEY (cid) REFERENCES sys_comps(compid) ON UPDATE CASCADE ON DELETE CASCADE;

2036

2037

2038

2039

-- TOC entry 2206 (class 2606 OID 18003)

2040

-- Dependencies: 1749 2151 1746

2041

-- Name: ppl_comps_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2042

2043

2044

ALTER TABLE ONLY ppl_comps

2045

ADD CONSTRAINT ppl_comps_pid_fkey FOREIGN KEY (pid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE CASCADE;

2046

2047

2048

2049

-- TOC entry 2207 (class 2606 OID 18008)

2050

-- Dependencies: 1746 1769 2197

2051

-- Name: ppl_comps_tid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2052

2053

2054

ALTER TABLE ONLY ppl_comps

2055

ADD CONSTRAINT ppl_comps_tid_fkey FOREIGN KEY (tid) REFERENCES team_list(teamid) ON UPDATE CASCADE ON DELETE CASCADE;

2056

2057

2058

2059

-- TOC entry 2208 (class 2606 OID 18013)

2060

-- Dependencies: 2151 1747 1749

2061

-- Name: ppl_email_personid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2062

2063

2064

ALTER TABLE ONLY ppl_email

2065

ADD CONSTRAINT ppl_email_personid_fkey FOREIGN KEY (personid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE CASCADE;

2066

2067

2068

2069

-- TOC entry 2209 (class 2606 OID 18018)

2070

-- Dependencies: 1766 1748 2191

2071

-- Name: ppl_judges_compid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2072

2073

2074

ALTER TABLE ONLY ppl_judges

2075

ADD CONSTRAINT ppl_judges_compid_fkey FOREIGN KEY (compid) REFERENCES sys_comps(compid) ON UPDATE CASCADE ON DELETE CASCADE;

2076

2077

2078

2079

-- TOC entry 2210 (class 2606 OID 18023)

2080

-- Dependencies: 1749 2151 1748

2081

-- Name: ppl_judges_personid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2082

2083

2084

ALTER TABLE ONLY ppl_judges

2085

ADD CONSTRAINT ppl_judges_personid_fkey FOREIGN KEY (personid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE CASCADE;

2086

2087

2088

2089

-- TOC entry 2211 (class 2606 OID 18028)

2090

-- Dependencies: 2157 1752 1750

2091

-- Name: ppl_school_region; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2092

2093

2094

ALTER TABLE ONLY ppl_schools

2095

ADD CONSTRAINT ppl_school_region FOREIGN KEY (regionid) REFERENCES ppl_region(regionid) ON UPDATE CASCADE ON DELETE CASCADE;

2096

2097

2098

2099

-- TOC entry 2231 (class 2606 OID 18155)

2100

-- Dependencies: 1794 1756 2168

2101

-- Name: prob_cases_probid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2102

2103

2104

ALTER TABLE ONLY prob_cases

2105

ADD CONSTRAINT prob_cases_probid_fkey FOREIGN KEY (probid) REFERENCES prob_list(problemid) ON UPDATE CASCADE;

2106

2107

2108

2109

-- TOC entry 2212 (class 2606 OID 18038)

2110

-- Dependencies: 2151 1754 1749

2111

-- Name: prob_comments_personid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2112

2113

2114

ALTER TABLE ONLY prob_comments

2115

ADD CONSTRAINT prob_comments_personid_fkey FOREIGN KEY (personid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE CASCADE;

2116

2117

2118

2119

-- TOC entry 2213 (class 2606 OID 18043)

2120

-- Dependencies: 1756 1754 2168

2121

-- Name: prob_comments_probid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2122

2123

2124

ALTER TABLE ONLY prob_comments

2125

ADD CONSTRAINT prob_comments_probid_fkey FOREIGN KEY (probid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2126

2127

2128

2129

-- TOC entry 2214 (class 2606 OID 18048)

2130

-- Dependencies: 1755 1756 2168

2131

-- Name: prob_descfigs_figid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2132

2133

2134

ALTER TABLE ONLY prob_descfigs

2135

ADD CONSTRAINT prob_descfigs_figid_fkey FOREIGN KEY (figid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2136

2137

2138

2139

-- TOC entry 2215 (class 2606 OID 18053)

2140

-- Dependencies: 1757 2191 1766

2141

-- Name: prob_set_compid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2142

2143

2144

ALTER TABLE ONLY prob_set

2145

ADD CONSTRAINT prob_set_compid_fkey FOREIGN KEY (compid) REFERENCES sys_comps(compid) ON UPDATE CASCADE ON DELETE CASCADE;

2146

2147

2148

2149

-- TOC entry 2216 (class 2606 OID 18058)

2150

-- Dependencies: 1756 2168 1757

2151

-- Name: prob_set_probid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2152

2153

2154

ALTER TABLE ONLY prob_set

2155

ADD CONSTRAINT prob_set_probid_fkey FOREIGN KEY (probid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2156

2157

2158

2159

-- TOC entry 2217 (class 2606 OID 18063)

2160

-- Dependencies: 1768 1758 2195

2161

-- Name: prob_solutions_langid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2162

2163

2164

ALTER TABLE ONLY prob_solutions

2165

ADD CONSTRAINT prob_solutions_langid_fkey FOREIGN KEY (langid) REFERENCES sys_langs(lid) ON UPDATE CASCADE ON DELETE CASCADE;

2166

2167

2168

2169

-- TOC entry 2218 (class 2606 OID 18068)

2170

-- Dependencies: 1756 2168 1758

2171

-- Name: prob_solutions_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2172

2173

2174

ALTER TABLE ONLY prob_solutions

2175

ADD CONSTRAINT prob_solutions_pid_fkey FOREIGN KEY (pid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2176

2177

2178

2179

-- TOC entry 2219 (class 2606 OID 18073)

2180

-- Dependencies: 1758 1759 2177

2181

-- Name: prob_solutions_stat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2182

2183

2184

ALTER TABLE ONLY prob_solutions

2185

ADD CONSTRAINT prob_solutions_stat_fkey FOREIGN KEY (stat) REFERENCES prob_status(statid) ON UPDATE CASCADE ON DELETE CASCADE;

2186

2187

2188

2189

-- TOC entry 2220 (class 2606 OID 18078)

2190

-- Dependencies: 1761 2168 1756

2191

-- Name: prob_tags_probid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2192

2193

2194

ALTER TABLE ONLY prob_tags

2195

ADD CONSTRAINT prob_tags_probid_fkey FOREIGN KEY (probid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2196

2197

2198

2199

-- TOC entry 2221 (class 2606 OID 18083)

2200

-- Dependencies: 1761 1760 2179

2201

-- Name: prob_tags_tagid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2202

2203

2204

ALTER TABLE ONLY prob_tags

2205

ADD CONSTRAINT prob_tags_tagid_fkey FOREIGN KEY (tagid) REFERENCES prob_tag_list(tagid) ON UPDATE CASCADE ON DELETE CASCADE;

2206

2207

2208

2209

-- TOC entry 2222 (class 2606 OID 18088)

2210

-- Dependencies: 2151 1763 1749

2211

-- Name: prob_version_person; Type: FK CONSTRAINT; Schema: public; Owner: postgres

2212

2213

2214

ALTER TABLE ONLY prob_versions

2215

ADD CONSTRAINT prob_version_person FOREIGN KEY (personid) REFERENCES ppl_list(personid) ON UPDATE CASCADE ON DELETE RESTRICT;

2216

2217

2218

2219

-- TOC entry 2223 (class 2606 OID 18093)

2220

-- Dependencies: 1764 1766 2191

2221

-- Name: sys_clari_compid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2222

2223

2224

ALTER TABLE ONLY sys_clari

2225

ADD CONSTRAINT sys_clari_compid_fkey FOREIGN KEY (compid) REFERENCES sys_comps(compid) ON UPDATE CASCADE ON DELETE CASCADE;

2226

2227

2228

2229

-- TOC entry 2224 (class 2606 OID 18098)

2230

-- Dependencies: 2195 1768 1765

2231

-- Name: sys_compargs_langid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2232

2233

2234

ALTER TABLE ONLY sys_compargs

2235

ADD CONSTRAINT sys_compargs_langid_fkey FOREIGN KEY (langid) REFERENCES sys_langs(lid) ON UPDATE CASCADE ON DELETE CASCADE;

2236

2237

2238

2239

-- TOC entry 2225 (class 2606 OID 18103)

2240

-- Dependencies: 2195 1768 1767

2241

-- Name: sys_exeargs_langid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2242

2243

2244

ALTER TABLE ONLY sys_exeargs

2245

ADD CONSTRAINT sys_exeargs_langid_fkey FOREIGN KEY (langid) REFERENCES sys_langs(lid) ON UPDATE CASCADE ON DELETE CASCADE;

2246

2247

2248

2249

-- TOC entry 2226 (class 2606 OID 18108)

2250

-- Dependencies: 2162 1752 1769

2251

-- Name: team_list_school_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2252

2253

2254

ALTER TABLE ONLY team_list

2255

ADD CONSTRAINT team_list_school_fkey FOREIGN KEY (schoolid) REFERENCES ppl_schools(schoolid) ON UPDATE CASCADE ON DELETE CASCADE;

2256

2257

2258

2259

-- TOC entry 2227 (class 2606 OID 18113)

2260

-- Dependencies: 1770 1768 2195

2261

-- Name: team_subs_langid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2262

2263

2264

ALTER TABLE ONLY team_subs

2265

ADD CONSTRAINT team_subs_langid_fkey FOREIGN KEY (langid) REFERENCES sys_langs(lid) ON UPDATE CASCADE ON DELETE CASCADE;

2266

2267

2268

2269

-- TOC entry 2228 (class 2606 OID 18118)

2270

-- Dependencies: 1770 1756 2168

2271

-- Name: team_subs_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2272

2273

2274

ALTER TABLE ONLY team_subs

2275

ADD CONSTRAINT team_subs_pid_fkey FOREIGN KEY (pid) REFERENCES prob_list(problemid) ON UPDATE CASCADE ON DELETE CASCADE;

2276

2277

2278

2279

-- TOC entry 2229 (class 2606 OID 18123)

2280

-- Dependencies: 2177 1759 1770

2281

-- Name: team_subs_stat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2282

2283

2284

ALTER TABLE ONLY team_subs

2285

ADD CONSTRAINT team_subs_stat_fkey FOREIGN KEY (stat) REFERENCES prob_status(statid) ON UPDATE CASCADE ON DELETE CASCADE;

2286

2287

2288

2289

-- TOC entry 2230 (class 2606 OID 18128)

2290

-- Dependencies: 2197 1770 1769

2291

-- Name: team_subs_tid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: codescore

2292

2293

2294

ALTER TABLE ONLY team_subs

2295

ADD CONSTRAINT team_subs_tid_fkey FOREIGN KEY (tid) REFERENCES team_list(teamid) ON UPDATE CASCADE ON DELETE CASCADE;

2296

2297

2298

2299

-- TOC entry 2238 (class 0 OID 0)

2300

-- Dependencies: 6

2301

-- Name: public; Type: ACL; Schema: -; Owner: postgres

2302

2303

2304

REVOKE ALL ON SCHEMA public FROM PUBLIC;

2305

REVOKE ALL ON SCHEMA public FROM postgres;

2306

GRANT ALL ON SCHEMA public TO postgres;

2307

GRANT ALL ON SCHEMA public TO PUBLIC;

2308

2309

2310

-- Completed on 2008-05-15 17:56:21 EDT

2311

2312

2313

-- PostgreSQL database dump complete

2314

2315

Older »