File: pgsql_objects.sql

package info (click to toggle)
dspam 3.10.1+dfsg-11
  • links: PTS, VCS
  • area: main
  • in suites: wheezy
  • size: 6,656 kB
  • sloc: ansic: 26,034; sh: 12,546; perl: 5,469; makefile: 690; sql: 379
file content (90 lines) | stat: -rw-r--r-- 2,226 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
/* $Id: pgsql_objects.sql,v 1.18 2009/06/23 21:51:22 sbajic Exp $ */

CREATE TABLE dspam_token_data (
  uid INT,
  token BIGINT,
  spam_hits INT,
  innocent_hits INT,
  last_hit DATE,
  UNIQUE (uid, token)
) WITHOUT OIDS;

CREATE TABLE dspam_signature_data (
  uid INT,
  signature varchar(128),
  data BYTEA,
  length INT,
  created_on DATE,
  UNIQUE (uid, signature)
) WITHOUT OIDS;

CREATE TABLE dspam_stats (
  uid INT PRIMARY KEY,
  spam_learned INT,
  innocent_learned INT,
  spam_misclassified INT,
  innocent_misclassified INT,
  spam_corpusfed INT,
  innocent_corpusfed INT,
  spam_classified INT,
  innocent_classified int
) WITHOUT OIDS;

CREATE TABLE dspam_preferences (
  uid INT,
  preference VARCHAR(128),
  value VARCHAR(128),
  UNIQUE (uid, preference)
) WITHOUT OIDS;

create function lookup_tokens(integer,bigint[])
  returns setof dspam_token_data
  language plpgsql stable
  as '
declare
  v_rec record;
begin
  for v_rec in select * from dspam_token_data
    where uid=$1
      and token in (select $2[i]
        from generate_series(array_lower($2,1),array_upper($2,1)) s(i))
  loop
    return next v_rec;
  end loop;
  return;
end;';

create function lookup_tokens(integer,integer,bigint[])
  returns setof dspam_token_data
  language plpgsql stable
  as '
declare
  v_rec record;
begin
  for v_rec in select * from dspam_token_data
    where uid=$1
      and token in (select $3[i]
        from generate_series(array_lower($3,1),array_upper($3,1)) s(i))
  loop
    return next v_rec;
  end loop;
  for v_rec in select * from dspam_token_data
    where uid=$2
      and token in (select $3[i]
        from generate_series(array_lower($3,1),array_upper($3,1)) s(i))
  loop
    return next v_rec;
  end loop;
  return;
end;';

/* For much better performance
 * see http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php
 * and http://archives.postgresql.org/pgsql-performance/2004-11/msg00417.php
 * for details
 */
ALTER TABLE dspam_token_data ALTER token SET STATISTICS 200;
ALTER TABLE dspam_signature_data ALTER signature SET STATISTICS 200;
ALTER TABLE dspam_token_data ALTER innocent_hits SET STATISTICS 200;
ALTER TABLE dspam_token_data ALTER spam_hits SET STATISTICS 200;
ANALYZE;