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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
-- $Id: database.sql 163 2006-11-10 21:16:44Z linuxtuxie $
-- PersonalBackup Database
DROP VIEW overview;
DROP VIEW backuplog_today;
DROP TABLE folders;
DROP TABLE shares;
DROP TABLE machines;
DROP TABLE users;
DROP TABLE backuplog;
DROP SEQUENCE s_users;
DROP SEQUENCE s_machines;
DROP SEQUENCE s_shares;
DROP SEQUENCE s_backup;
DROP SEQUENCE s_folders;
CREATE SEQUENCE s_users START 1 MINVALUE 1;
CREATE SEQUENCE s_machines START 1 MINVALUE 1;
CREATE SEQUENCE s_shares START 1 MINVALUE 1;
CREATE SEQUENCE s_backup START 1 MINVALUE 1;
CREATE SEQUENCE s_folders START 1 MINVALUE 1;
CREATE TABLE users (id BIGINT PRIMARY KEY DEFAULT NEXTVAL('s_users'),
username VARCHAR(256) NOT NULL,
password VARCHAR(256),
realname VARCHAR(256),
emailadres VARCHAR(256),
language CHAR(2) DEFAULT 'en' NOT NULL,
admin CHAR(1) DEFAULT 'N' NOT NULL,
UNIQUE (username));
CREATE TABLE machines (id BIGINT PRIMARY KEY DEFAULT NEXTVAL('s_machines'),
user_id BIGINT REFERENCES users(id) NOT NULL,
machine_name VARCHAR(15) NOT NULL,
type CHAR(1) NOT NULL,
status BIGINT NOT NULL DEFAULT -1,
email CHAR(1) DEFAULT 'N',
info CHAR(1) DEFAULT 'N',
notification CHAR(1) DEFAULT '0',
errcount BIGINT DEFAULT 0,
UNIQUE (user_id, machine_name));
CREATE TABLE shares (id BIGINT PRIMARY KEY DEFAULT NEXTVAL('s_shares'),
machine_id BIGINT REFERENCES machines(id) NOT NULL,
share_name VARCHAR(256) NOT NULL,
username VARCHAR(256) NOT NULL,
password VARCHAR(256) NOT NULL,
schedule INT NOT NULL DEFAULT 0,
vpn CHAR(1) NOT NULL DEFAULT 'N',
backed_up BIGINT DEFAULT -1,
errcount BIGINT DEFAULT 0,
maxvpnsize BIGINT DEFAULT 0,
maxbackupsize BIGINT DEFAULT 0,
progress INT DEFAULT 0,
enabled CHAR(1) DEFAULT 'Y');
CREATE TABLE folders (id BIGINT PRIMARY KEY DEFAULT nextval('s_folders'),
share_id BIGINT REFERENCES shares(id),
dirname VARCHAR(200) NOT NULL);
CREATE TABLE backuplog (backup_id BIGINT PRIMARY KEY DEFAULT nextval('s_backup'),
machine_id BIGINT NOT NULL,
share_id BIGINT NOT NULL,
backup_date DATE NOT NULL DEFAULT CURRENT_DATE,
transfer_size BIGINT NOT NULL,
backup_size BIGINT NOT NULL,
backup_location VARCHAR(2048) NOT NULL,
skip_size BIGINT NOT NULL DEFAULT 0,
skip_count BIGINT NOT NULL DEFAULT 0,
fail_count BIGINT NOT NULL DEFAULT 0,
backup_count BIGINT NOT NULL DEFAULT 0,
limit_size BIGINT NOT NULL DEFAULT 0,
backup_time FLOAT8 NOT NULL DEFAULT 0,
conn_time FLOAT8 NOT NULL DEFAULT 0,
vpn CHAR(1) NOT NULL DEFAULT 'N',
archived CHAR(1) NOT NULL DEFAULT 'N',
status CHAR(1) NOT NULL);
CREATE VIEW backuplog_today AS SELECT * FROM backuplog WHERE backup_date = CURRENT_DATE;
CREATE VIEW overview AS SELECT s.id AS share_id,
u.id AS user_id,
m.id AS machine_id,
m.machine_name,
m.type,
u.username,
s.backed_up,
s.share_name,
s.progress,
s.enabled,
s.maxbackupsize,
s.maxvpnsize,
s.schedule,
b.vpn,
b.transfer_size,
b.backup_size,
b.backup_count,
b.skip_size,
b.skip_count,
b.fail_count,
(s.schedule & (2^((TO_NUMBER(TO_CHAR(NOW(), 'D'),9))-1))::integer) AS scheduled
FROM shares s JOIN machines m ON (s.machine_id = m.id)
LEFT OUTER JOIN backuplog_today b ON (b.share_id = s.id)
JOIN users u ON (m.user_id = u.id);
CREATE INDEX shares_machine_id_idx ON shares(machine_id);
CREATE INDEX backuplog_share_id_idx ON backuplog(share_id);
CREATE INDEX machines_user_id_idx ON machines(user_id);
CREATE INDEX folders_share_id_idx ON folders(share_id);
GRANT ALL ON s_backup TO postgres;
GRANT ALL ON s_machines TO postgres;
GRANT ALL ON s_shares TO postgres;
GRANT ALL ON s_users TO postgres;
GRANT ALL ON s_folders TO postgres;
GRANT ALL ON machines TO postgres;
GRANT ALL ON backuplog TO postgres;
GRANT ALL ON shares TO postgres;
GRANT ALL ON folders TO postgres;
GRANT ALL ON users TO postgres;
GRANT ALL ON overview TO postgres;
GRANT ALL ON backuplog_today TO postgres;
INSERT INTO users (username, password, realname, emailadres, admin) VALUES ('admin', 'jxq6VKFcS.hT2', 'PersonalBackup Administrator', 'personalbackup@acme.com', 'Y');
/*
type 9 = Win95/98/ME
type X = WinNT/W2K/XP
type S = Samba
schedule = S M T W T F S
1 2 4 8 16 32 64
vpn = backup over VPN Y/N
backed_up
-1 = not yet today
-2 = busy
-3 = done
-4 = skipped for now
-5 = failed
*/
|