FreenetIS

"ALTER TABLE `ifaces` CHANGE `device_id` `device_id` INT( 11 ) NOT NULL",

"ALTER TABLE `ifaces` ADD `number` INT( 11 ) NULL DEFAULT NULL AFTER `name`",

"ALTER TABLE `ifaces` ADD `wireless_mode` INT( 11 ) NULL DEFAULT NULL AFTER `number` ,

ADD `wireless_antenna` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_mode` ,

ADD `port_mode` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_antenna`",

"UPDATE ifaces i, wireless_ifaces wi

SET wireless_mode = wmode, wireless_antenna = antenna

WHERE wi.iface_id = i.id",

"DROP TABLE wireless_ifaces",

"RENAME TABLE `segments` TO `links`",

"ALTER TABLE `ifaces` DROP FOREIGN KEY `ifaces_ibfk_2`;",

"ALTER TABLE `ifaces` CHANGE `segment_id` `link_id` INT( 11 ) NULL DEFAULT NULL",

"ALTER TABLE `ifaces`

ADD FOREIGN KEY `ifaces_ibfk_2` (`link_id`)

REFERENCES `links` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT",

"ALTER TABLE `links` CHANGE `medium_id` `medium` INT( 11 ) NOT NULL",

"ALTER TABLE `links`

ADD `wireless_ssid` INT( 11 ) NULL DEFAULT NULL AFTER `duplex` ,

ADD `wireless_norm` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_ssid` ,

ADD `wireless_frequency` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_norm` ,

ADD `wireless_channel` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_frequency` ,

ADD `wireless_channel_width` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_channel` ,

ADD `wireless_polarization` INT( 11 ) NULL DEFAULT NULL AFTER `wireless_channel_width`",

"UPDATE links l, wireless_segments ws

SET

wireless_ssid = ssid,

wireless_norm = norm,

wireless_frequency = frequence,

wireless_channel = channel,

wireless_channel_width = channel_width,

wireless_polarization = polarization

WHERE ws.segment_id = l.id",

"DROP TABLE wireless_segments",

"CREATE TABLE IF NOT EXISTS `ifaces_vlans` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`iface_id` int(11) NOT NULL,

`vlan_id` int(11) NOT NULL,

`tagged` tinyint(1) DEFAULT NULL,

`port_vlan` tinyint(1) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `iface_id` (`iface_id`),

KEY `vlan_id` (`vlan_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE IF NOT EXISTS `ifaces_relationships` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_iface_id` int(11) NOT NULL,

`iface_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `parent_iface_id` (`iface_id`),

KEY `iface_id` (`iface_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1;",

"ALTER TABLE `ifaces` ADD `original_id` INT( 11 ) NULL DEFAULT NULL",

"INSERT INTO ifaces (device_id, type, name, original_id)

SELECT i.device_id, 5, vi.name, vi.id

FROM vlan_ifaces vi

JOIN ifaces i ON vi.iface_id = i.id",

"INSERT INTO ifaces_relationships

SELECT NULL, vi.iface_id, i.id

FROM vlan_ifaces vi

JOIN ifaces i ON i.original_id = vi.id",

"INSERT INTO ifaces_vlans (iface_id, vlan_id)

SELECT i.id, vi.vlan_id

FROM vlan_ifaces vi

JOIN ifaces i ON i.original_id = vi.id",

"UPDATE ip_addresses ip, ifaces i

SET ip.iface_id = i.id

WHERE ip.vlan_iface_id = i.original_id",

"ALTER TABLE `ip_addresses` DROP FOREIGN KEY `ip_addresses_ibfk_3` ;",

"ALTER TABLE `ip_addresses` DROP `vlan_iface_id`",

"DROP TABLE vlan_ifaces",

"UPDATE ifaces SET original_id = NULL",

"INSERT INTO ifaces (type, device_id, link_id, name, number, port_mode, original_id)

SELECT 3, device_id, segment_id, name, port_nr, mode, id

FROM ports p",

"INSERT INTO ifaces_vlans (id, iface_id, vlan_id, tagged, port_vlan)

SELECT NULL, i.id, pv.vlan_id, pv.tagged, IF(pv.vlan_id = p.port_vlan_id,1,0)

FROM ports p

JOIN ports_vlans pv ON pv.port_id = p.id

JOIN ifaces i ON i.original_id = p.id",

"DROP TABLE ports_vlans",

"DROP TABLE ports",

"ALTER TABLE `ifaces` DROP `original_id`",

"ALTER TABLE `ifaces_relationships`

ADD FOREIGN KEY ( `parent_iface_id` )

REFERENCES `ifaces` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;",

"ALTER TABLE `ifaces_relationships`

ADD FOREIGN KEY ( `iface_id` )

REFERENCES `ifaces` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ;",

"ALTER TABLE `ifaces_vlans`

ADD FOREIGN KEY ( `iface_id` )

REFERENCES `ifaces` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ;",

"ALTER TABLE `ifaces_vlans`

ADD FOREIGN KEY ( `vlan_id` )

REFERENCES `vlans` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ;"

);

<?php defined('SYSPATH') or die('No direct script access.');

/*

* This file is part of open source system FreeNetIS

* and it is released under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*

*/

$upgrade_sql[1397] = array

(

"ALTER TABLE `ports` ADD `mode` INT( 11 ) NOT NULL ,

ADD `port_vlan_id` INT( 11 ) NOT NULL ,

ADD INDEX ( `port_vlan_id` )",

"ALTER TABLE `vlans` ADD UNIQUE (

`tag_802_1q`

)",

"INSERT INTO vlans(id, name, tag_802_1q, comment)

VALUES (NULL, 'Default VLAN', 1, 'Default VLAN')

ON DUPLICATE KEY

UPDATE name='Default VLAN', comment='Default VLAN';",

"ALTER TABLE `ports_vlans` ADD `tagged` BOOLEAN NOT NULL ",

"ALTER TABLE `segments` CHANGE `bitrate` `bitrate` BIGINT NULL DEFAULT NULL",

"UPDATE ports p,

(

SELECT port_id, vlan_id, IF(COUNT(*)=1, 1, 2) AS mode

FROM ports_vlans pv

GROUP BY pv.port_id

) pv

SET p.mode = pv.mode, p.port_vlan_id = pv.vlan_id

WHERE pv.port_id = p.id"

);