SELECT * INTO tmp_cli FROM phpads_clients;
SELECT * INTO tmp_ban FROM phpads_banners;
SELECT * INTO tmp_adc FROM phpads_adclicks;
SELECT * INTO tmp_adv FROM phpads_adviews;
SELECT * INTO tmp_acl FROM phpads_acls;
SELECT * INTO tmp_ads FROM phpads_adstats;
DROP TABLE phpads_zones;
DROP TABLE phpads_adclicks;
DROP TABLE phpads_adviews;
DROP TABLE phpads_acls;
DROP TABLE phpads_adstats;
DROP TABLE phpads_banners;
DROP TABLE phpads_clients;
CREATE TABLE phpads_clients (
clientid int4 NOT NULL DEFAULT nextval('phpads_clients_clientid_seq')::int4,
clientname varchar(255) NOT NULL,
contact varchar(255),
email varchar(64),
views int8,
clicks int8,
clientusername varchar(64),
clientpassword varchar(64),
expire date,
activate date,
permissions int2,
language varchar(64),
active boolean,
weight int2 default 1,
parent int4,
report boolean,
reportinterval int4 DEFAULT 7,
reportlastdate date,
reportdeactivate boolean,
PRIMARY KEY (clientid),
FOREIGN KEY (parent) REFERENCES phpads_clients (clientid)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_clients_parent ON phpads_clients (parent);
CREATE TABLE phpads_banners (
bannerid int4 NOT NULL DEFAULT nextval('phpads_banners_bannerid_seq')::int4,
clientid int4 DEFAULT 0 NOT NULL,
active boolean NOT NULL,
weight int2 DEFAULT 1 NOT NULL,
seq int2 DEFAULT 0 NOT NULL,
banner text,
width int2 DEFAULT 0 NOT NULL,
height int2 DEFAULT 0 NOT NULL,
format varchar(4) DEFAULT 'gif' NOT NULL,
url varchar(255) NOT NULL,
alt varchar(255) NOT NULL,
status varchar(255) NOT NULL,
keyword varchar(255) NOT NULL,
bannertext varchar(255) NOT NULL,
target varchar(8) DEFAULT '' NOT NULL,
description varchar(255) DEFAULT '' NOT NULL,
autohtml boolean DEFAULT 't' NOT NULL,
PRIMARY KEY (bannerid),
FOREIGN KEY (clientid) REFERENCES phpads_clients (clientid)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_banners_clientid ON phpads_banners(clientid);
CREATE TABLE phpads_adclicks (
bannerid int4 DEFAULT '0' NOT NULL,
t_stamp timestamp,
host varchar(255) NOT NULL,
FOREIGN KEY (bannerid) REFERENCES phpads_banners(bannerid)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_adclicks_bannerid ON phpads_adclicks(bannerid);
CREATE TABLE phpads_adviews (
bannerid int4 DEFAULT '0' NOT NULL,
t_stamp timestamp,
host varchar(255) NOT NULL,
FOREIGN KEY (bannerid) REFERENCES phpads_banners(bannerid)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_adviews_bannerid ON phpads_adviews(bannerid);
CREATE TABLE phpads_acls (
bannerid int4 DEFAULT 0 NOT NULL,
acl_type varchar(12) NOT NULL,
acl_data varchar(255) NOT NULL,
acl_ad boolean NOT NULL,
acl_order int8 DEFAULT 0 NOT NULL,
acl_con varchar(5),
PRIMARY KEY (bannerid, acl_order),
FOREIGN KEY (bannerid) REFERENCES phpads_banners(bannerid)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_acls_bannerid ON phpads_acls (bannerid);
CREATE TABLE phpads_adstats (
views int4 DEFAULT 0 NOT NULL,
clicks int4 DEFAULT 0 NOT NULL,
day date DEFAULT NOW() NOT NULL,
bannerid int4 DEFAULT 0 NOT NULL,
PRIMARY KEY (day, bannerid),
FOREIGN KEY (bannerid) REFERENCES phpads_banners(bannerid) ON
UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX phpads_adstats_bannerid ON phpads_acls (bannerid);
CREATE TABLE phpads_zones (
zoneid int4 NOT NULL DEFAULT nextval('phpads_zones_zoneid_seq')::int4,
zonename varchar(255) DEFAULT '' NOT NULL,
description varchar(255) DEFAULT '' NOT NULL,
zonetype int2 DEFAULT 0 NOT NULL,
what text DEFAULT '' NOT NULL,
width int2 DEFAULT 0 NOT NULL,
height int2 DEFAULT 0 NOT NULL,
retrieval varchar(16) DEFAULT 'random' NOT NULL,
cachecontents oid,
cachetimestamp timestamp,
cachesize int4,
PRIMARY KEY (zoneid)
);
INSERT INTO phpads_clients SELECT clientid, clientname,
contact, email, views::int4, clicks::int4, clientusername,
clientpassword, expire, activate, permissions, language, active,
weight, CASE WHEN parent = THEN NULL ELSE parent END AS parent,
report, reportinterval, reportlastdate, reportdeactivate FROM
tmp_cli;
INSERT INTO phpads_banners SELECT * FROM tmp_ban;
INSERT INTO phpads_adclicks SELECT * FROM tmp_adc;
INSERT INTO phpads_adviews SELECT * FROM tmp_adv;
INSERT INTO phpads_acls SELECT * FROM tmp_acl;
INSERT INTO phpads_adstats SELECT * FROM tmp_ads;
DROP TABLE tmp_cli;
DROP TABLE tmp_ban;
DROP TABLE tmp_adc;
DROP TABLE tmp_adv;
DROP TABLE tmp_acl;
DROP TABLE tmp_ads;
|