See w3sa/templates/doc/changelog.mak for more news.

For SQL changes see script 'upgrade.py'.

8.3
-- dhcpd_kea support
-- IPv6 support for switches and nagios

8.1
-- New JSON protocol for web-daemon communication.

8.0
-- Python3 compatibility.

7.1
-- aliases split into aliases and forwardings

ALTER TABLE aliases RENAME TO aliases2;
# run: ./start-w3sa.sh initdb
# or:
CREATE TABLE `aliases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` varchar(127) DEFAULT NULL,
  `target` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `source` (`source`,`target`),
  KEY `target` (`target`),
  CONSTRAINT `aliases_ibfk_1`
    FOREIGN KEY (`target`) REFERENCES `accounts` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `forwardings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(127) NOT NULL,
  `domain` int(11) DEFAULT NULL,
  `target` varchar(127) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`,`domain`,`target`),
  KEY `domain` (`domain`),
  CONSTRAINT `forwardings_ibfk_1`
    FOREIGN KEY (`domain`) REFERENCES `domain` (`id`)
      ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# and import data
INSERT INTO aliases
  SELECT NULL, a1.login, a2.id, now() FROM aliases2 al2
    JOIN accounts a1 ON a1.id=al2.account
    JOIN accounts a2 ON a2.login=al2.target AND a2.domain=a1.domain
  WHERE al2.target NOT LIKE '%@%'
     OR substring(target, instr(target, "@")+1)
        NOT IN (SELECT name FROM domain);
INSERT INTO aliases
  SELECT NULL, a1.login, a2.id, now() FROM aliases2 al2
    JOIN accounts a1 ON a1.id=al2.account
    JOIN accounts a2 ON a2.login=substring_index(al2.target, '@', 1)
      AND a2.domain=(
        SELECT id FROM domain
         WHERE name=substring(target, instr(target, "@")+1)
      )
  WHERE al2.target LIKE '%@%'
    AND substring(target, instr(target, "@")+1) IN (SELECT name FROM domain);
INSERT INTO forwardings
  SELECT NULL, a1.login, a1.domain, al2.target, now() FROM aliases2 al2
    JOIN accounts a1 ON a1.id=al2.account
  WHERE al2.target LIKE '%@%'
    AND substring(target, instr(target, "@")+1)
        NOT IN (SELECT name FROM domain);
DELETE FROM accounts WHERE flags="V";

7.0 (november 2013)
  - ToscaWidgets 2.0 required
  - TurboGears 2.3 required
  - SQLAlchemy 0.8 supported
  - faster firewall and shaper services

6.2 (april 2012)
  - New top menu.
  - Export to CSV file.

6.1
  - Cable menu added.
  - Device tree can hide subtrees.
  - New zone for services: Nagios.

6.0
  - TurboGears 2.1 required
  - remove /var/spool/dovecot/LOGIN directory after user removal
  - added check_mk nagios support (6.3)

DROP TRIGGER new_service;
DROP TRIGGER update_service;
DROP TRIGGER delete_service;
ALTER TABLE service RENAME service2;
system ./start-w3sa.sh initdb # create new table and triggers
INSERT INTO service SELECT * FROM service2;
INSERT INTO service SELECT NULL, ip, proto, port, 'N' FROM service2
  WHERE ip IN (SELECT DISTINCT ip FROM service2 WHERE port=90000);
DROP TABLE service2;
DELETE FROM service WHERE port>=90000 AND access!='N';

ALTER TABLE ip MODIFY device int NOT NULL;

DELIMITER ;;
DROP TRIGGER IF EXISTS quota_action;;
DROP TRIGGER IF EXISTS new_accounts;;
DROP TRIGGER IF EXISTS update_accounts;;
CREATE TRIGGER new_accounts AFTER INSERT ON accounts FOR EACH ROW
BEGIN
  INSERT INTO actions (account, action) VALUES (NEW.id, 'QUOTA');
  UPDATE w3sa_counters SET n=n+1, time=now() WHERE name='changes';
END;;
CREATE TRIGGER update_accounts AFTER UPDATE ON accounts FOR EACH ROW
BEGIN
    IF NEW.quota<>OLD.quota THEN
        INSERT INTO actions (account, action) VALUES (NEW.id, 'QUOTA');
    END IF;
    UPDATE w3sa_counters SET n=n+1, time=now() WHERE name='changes';
END;;
DELIMITER ;

# Location update
ALTER TABLE location ADD height double DEFAULT 0 AFTER gps_longitude;
ALTER TABLE location DROP KEY gps_latitude;
ALTER TABLE location ADD UNIQUE KEY
  gps_latitude (gps_latitude, gps_longitude, height);

ALTER TABLE domain ADD created datetime NOT NULL;
ALTER TABLE vlan ADD created datetime NOT NULL;
ALTER TABLE user ADD created datetime NOT NULL;
ALTER TABLE accounts ADD created datetime NOT NULL;
ALTER TABLE device ADD created datetime NOT NULL;

5.3
  - sqlalchemy-0.5 required
  - accounts module rewrite
  - invalidate nscd cache after commit
  - restart icon displayed after each change
  - rewritten Services tab
  - added serial an inventory number

ALTER TABLE service CHANGE ip ip int NOT NULL;
ALTER TABLE cert_users CHANGE passwd passwd varchar(127) DEFAULT NULL;
INSERT INTO w3sa_counters (name) VALUES ('changes');
ALTER TABLE device ADD serial varchar(32) DEFAULT '';
ALTER TABLE device ADD inventory varchar(32) DEFAULT '';
# update serial numbers and remove them from description
UPDATE device SET
         serial=substr(description, locate('SN: ', description)+4),
         description=trim(',' FROM trim(' ' FROM substr(description, 1,
           locate('SN: ', description)-1)))
       WHERE description LIKE '%SN:%';
INSERT INTO w3sa_counters VALUES (DEFAULT, 'port_updates', '2010-01-01', 0);

5.2
  - IPv6 support
    you need to run this command to update your SQL tables:
	tg-admin sql create
  - multi gateways per vlan (secondary IPs)
  - nrpe services added
  - CNAME/MX definitions
  - security: fix user privileges

DELETE FROM gateway;
INSERT INTO gateway
  SELECT NULL, id, gateway, netmask, 10 FROM vlan
    WHERE round(num)=num;
INSERT INTO gateway
  SELECT NULL, (SELECT id FROM vlan c WHERE c.num=round(p.num)) AS vlan,
         gateway, netmask, 20
  FROM vlan p WHERE round(num)<>num;
ALTER TABLE vlan DROP gateway;
ALTER TABLE vlan DROP netmask;
# old records?
UPDATE ip SET vlan = (
    SELECT id FROM vlan c WHERE c.num=round(
      (SELECT num FROM vlan WHERE id=ip.vlan)))
  WHERE vlan IN (SELECT id FROM vlan x WHERE round(num)<>num);
DELETE FROM vlan WHERE round(num)<>num;
# add addr length
ALTER TABLE ip ADD len int;
UPDATE ip SET len=length(addr);
CREATE TRIGGER ip_addr_length_insert BEFORE INSERT ON ip FOR EACH ROW
    SET NEW.len = length(NEW.addr);
CREATE TRIGGER ip_addr_length_update BEFORE UPDATE ON ip FOR EACH ROW
    SET NEW.len = length(NEW.addr);
# if there is no id for actions:
ALTER TABLE actions ADD id int PRIMARY KEY auto_increment;
# if there is no id for w3sa_counters
#ALTER TABLE w3sa_counters DROP PRIMARY KEY;
#ALTER TABLE w3sa_counters ADD id int PRIMARY KEY auto_increment FIRST;

# recreate database
mysqldump -u root w3sa -t --skip-extended-insert --skip-triggers > /tmp/1
mysql -u root -e "DROP DATABASE w3sa;CREATE DATABASE w3sa CHARACTER SET utf8;"
./tg-admin sql create
mysql -v -u root w3sa </tmp/1 | tail -5

5.1
  - new modular architecture, rewritten modules:
      dns_*
      apache
      shaper
      firewall
  - python-2.6 warning fixes

5.0
  - turbogears web interface
  - dbtunnel module (TG interface required)
  - dbtest module
  - ability to create web symlinks to www.domain
  - move directories owned by others to deleted
  - ability to define which directory to move to deleted
  - ISO2ASCII replaced by utf2ascii
  - automatic restart after user password change
  - added chrootfs.py, an easy chroot filesystem (obsolete)
  - hidden guessing of parent and switchport
  - device_tree is aprox. 30% faster
  - copy also permissions for mkskel
  - added access (L, D, R) for each TCP/UDP port
  - quotas for "E" flagged accounts
  - named masks for reverse DNS

# run: ./tg-admin sql create
# update script:

DROP TRIGGER quota_action;
delimiter |
CREATE TRIGGER quota_action BEFORE UPDATE ON accounts
  FOR EACH ROW BEGIN
    IF NEW.quota<>OLD.quota THEN
      INSERT INTO actions (account, action) VALUES (NEW.id, 'QUOTA');
    END IF;
  END;
|
delimiter ;

ALTER TABLE service ADD access varchar(1) DEFAULT 'L';

INSERT INTO tg_user
  SELECT accounts.id, login, concat(login,'@localhost'),
         concat(first_name, ' ', last_name), passwd, now()
  FROM accounts
  LEFT JOIN user ON user.id=accounts.user
  WHERE flags RLIKE 'A';
INSERT INTO tg_group
  SELECT DISTINCT grp, name, name, now() FROM groups;
INSERT INTO tg_user_group
  SELECT member, grp FROM groups WHERE member IS NOT NULL;

INSERT INTO tg_group VALUES (1, 'admin', 'Admin', now());
#UPDATE tg_user SET password='$1$w8wrF9Xq$Xck7Ejne6t99A5q2E0gpS.';
INSERT INTO tg_user_group VALUES (1,1);

# foreign keys
ALTER TABLE accounts DROP FOREIGN KEY accounts_grp;
ALTER TABLE accounts ADD FOREIGN KEY (grp) REFERENCES tg_group(group_id);

ALTER TABLE device DROP FOREIGN KEY device_grp;
ALTER TABLE device ADD FOREIGN KEY (grp) REFERENCES tg_group(group_id);
ALTER TABLE device ADD switchport varchar(10) DEFAULT '';

ALTER TABLE domain DROP FOREIGN KEY domain_grp;
ALTER TABLE domain ADD FOREIGN KEY (grp) REFERENCES tg_group(group_id);

ALTER TABLE vlan DROP FOREIGN KEY vlan_grp;
ALTER TABLE vlan ADD FOREIGN KEY (grp) REFERENCES tg_group(group_id);
ALTER TABLE vlan CHANGE num num float(11) NOT NULL DEFAULT '-1';

ALTER TABLE ip ADD priority int NOT NULL DEFAULT 10 AFTER addr;

#DROP VIEW IF EXISTS w3sa_domain;
#DROP VIEW IF EXISTS w3sa_users;
#DROP TABLE IF EXISTS groups;

# unused views
#DROP VIEW IF EXISTS w3sa_aliases;
#DROP VIEW IF EXISTS w3sa_devices;

# Virtual accounts
UPDATE accounts SET quota=0 WHERE flags LIKE 'V';

4.3
  - default IP format is %03d.%03d.%03d.%03d
  - shapeuje sa aj v noci, zapocitava sa ale iba 1/10 prenesenych dat
  - pre parent "-3" neblokuje usera, shaper ostava v platnosti podla
    aktualne prenesenych dat

4.2
  - netmask moved to vlan

update ip set mask=29 where mask='255.255.255.248';
update ip set mask=27 where mask='255.255.255.224';
update ip set mask=24 where mask='255.255.255.0';
update ip set mask=28 where mask='255.255.255.240';
alter table vlan add netmask int not null default 24;
update vlan set netmask=(select mask from ip where ip.vlan=vlan.id limit 1);
alter table ip drop mask;

4.1
  - pre zmenu hesla uz nie je nutne zadavat action='PASSWD', meni sa
    automaticky, ak doslo k zmene hesla
  - action='INIT' sa uz neda pouzit, je nutne pouzit prislusny flag
    (B - deaktivovanie konta)

alter table vlan add column gateway varchar(16) not null default '0.0.0.0';
alter table device drop gateway;

alter table accounts add comment varchar(127) not null default '';
update accounts set comment=target;
alter table accounts drop target;
drop view w3sa_users;
CREATE VIEW `w3sa_users` AS select `a`.`id` AS `id`,`a`.`login` AS `login`,`d`.`name` AS `domain`,`g`.`name` AS `grp`,`a`.`passwd` AS `passwd`,`u`.`first_name` AS `first_name`,`u`.`last_name` AS `last_name`,`a`.`quota` AS `quota`,`a`.`flags` AS `flags`,`a`.`comment` AS `target`,`w3sa`.`actions`.`action` AS `action` from ((((`accounts` `a` left join `domain` `d` on((`a`.`domain` = `d`.`id`))) left join `user` `u` on((`a`.`user` = `u`.`id`))) left join `groups` `g` on((`a`.`grp` = `g`.`grp`))) left join `actions` on((`w3sa`.`actions`.`account` = `a`.`id`)));
#CREATE VIEW w3sa_devices AS SELECT ...OBSOLETE NOW...!!!

4.0
  - major project tree reorganization (cleanup and renames)
  - pyformer adaptations
  - major database changes
      - database name changed from system to w3sa
      - for more details see SQL update script below
  - aliases not listed in accounts as "V" or "E" flag will be silently ignored

  - SQL update script:

use w3sa;
delete from service;
delete from ip;
delete from device order by parent desc;
delete from vlan;
delete from w3sa.aliases;
delete from w3sa.accounts;
delete from w3sa.groups;
delete from w3sa.targets;
delete from w3sa.user where id not in (select owner from device)
  and id not in (select user from accounts);
delete from w3sa.user;
delete from w3sa.domain;
delete from system.users where domain='' or flags='VV';
# insert new content
insert into user (id,last_name,first_name) values (1,'Nobody','Nobody');
insert into user (id,last_name,first_name)
  select distinct id,last_name,first_name from system.users;
#insert into groups (grp,name,member)
#  select distinct concat('d-',domain),grp,concat(login,'@',domain)
#         from system.users
#         where flags like '%A%';
insert into groups (grp,name,member)
  select distinct 0,grp,NULL from system.users;
update groups set grp=id;
insert into domain (grp,name)
  select distinct 1,domain from system.users;
insert into accounts (grp,login,user,domain,passwd,flags,quota)
  select (select distinct grp from groups where name=s.grp),
         login,
         id,
         (select id from domain where name=s.domain),
         passwd,flags,q_quota
  from system.users s;
# create virtual users/aliases
insert into accounts (grp,login,user,domain,flags)
  select distinct (select grp from groups where name=a.domain),
         a.alias,
         (1),
         (select id from domain where name=a.domain),
         'V'
  from system.aliases a
  where (alias,domain) NOT IN (select login,domain from w3sa_users);
# delete aliases without key in account table
delete from system.aliases where concat(alias,'@',domain) not in
  (select concat(login,'@',domain) from system.users where flags rlike 'V');
# insert new aliases
insert into aliases (account,target)
  select u.id,a.target from system.aliases a
    join w3sa_users u on u.login=a.alias and u.domain=a.domain;
insert into aliases (account,target)
  select u.id,a.target from system.users a
    join w3sa_users u on u.login=a.login and u.domain=a.domain
    where a.target is not NULL and a.target!='';

3.0
  - command parameteres are separated by newlines (\n) and an
      empty line as last command is required
  - error is now displayed uppercase (ERROR)
  - quotas are in blocks (kilobytes)
  - quotas for non admins are not ignored and set as user quotas
      (admin has only group quota and no other quota)
  - repquota report user quotas by default, use parameter grp for group qoutas
  - cursor implementation (LOCK is not possible always and with cursor
      there will be less errors in PHP scripts)
  - virtual service renamed to postfix
  - mailalias table renamed to aliases with some changes

TODO:
  employers aliases in ie.py

DB UPDATE:
  alter table users add grp varchar(127) after login;
  alter table users add target varchar(127) after folder;
  update users set
    target=folder,grp=domain,q_quota=q_quota/1024,q_limit=q_limit/1024;
  alter table users drop folder;
  update users set grp=domain;
  alter table users add first_name varchar(20) NOT NULL default ''
    after q_limit;
  alter table users add last_name varchar(40) NOT NULL default ''
    after first_name;
  alter table users add action varchar(10) default NULL;
  alter table users add id varchar(10) default NULL;
  #alter table users add unique fid (id,flags);

  create table aliases (
    alias varchar(127),domain varchar(127),target varchar(127)
  ) character set latin2;

  alter table users alter q_limit set default -1;
  alter table users alter q_quota set default -1;
  alter table users alter passwd set default '!!';

  alter table mailalias drop primary key;
  alter table mailalias drop target;
  alter table mailalias add target text;

DB Changes from 2.X version:
  alter table pcs drop column oid;
  alter table pcs add column oid int auto_increment, add primary key (oid);
