FreenetIS

-- phpMyAdmin SQL Dump

-- version 2.9.1.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: May 18, 2007 at 11:53 AM

-- Server version: 5.0.27

-- PHP Version: 5.2.0

--

-- Database: `freenetis`

--

-- --------------------------------------------------------

--

-- Table structure for table `access_mod`

--

CREATE TABLE `access_mod` (

`ID` int(11) NOT NULL default '0',

`ID_device` int(11) default NULL,

`ID_user_admin` int(11) default NULL,

`timestamp_start` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`timestamp_end` timestamp NOT NULL default '0000-00-00 00:00:00',

`type` varchar(254) collate utf8_czech_ci default NULL,

`reason` varchar(254) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`),

KEY `user_access_mod` (`ID_user_admin`),

KEY `device_access_mod` (`ID_device`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

--

-- Dumping data for table `access_mod`

--

-- --------------------------------------------------------

--

-- Table structure for table `contact`

--

CREATE TABLE `contact` (

`ID_user` int(11) default NULL,

`ID` int(11) NOT NULL auto_increment,

`type` varchar(40) collate utf8_czech_ci default NULL,

`value` varchar(255) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`),

KEY `user_contact` (`ID_user`)

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

--

-- Dumping data for table `contact`

--

-- --------------------------------------------------------

--

-- Table structure for table `credit_mod`

--

CREATE TABLE `credit_mod` (

`ID` int(11) NOT NULL auto_increment,

`ID_member` int(11) default NULL,

`ID_user_admin` int(11) default NULL,

`amount` double default NULL,

`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`reason` varchar(254) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`),

KEY `user_credit_mod` (`ID_user_admin`),

KEY `member_credit_mod` (`ID_member`)

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

--

-- Dumping data for table `credit_mod`

--

-- --------------------------------------------------------

--

-- Table structure for table `device`

--

CREATE TABLE `device` (

`ID` int(11) NOT NULL auto_increment,

`ID_user` int(11) default NULL,

`name` varchar(254) character set latin1 default NULL,

`type` varchar(254) character set latin1 default NULL,

`PPPoE_logging_in` tinyint(4) default NULL,

`login` varchar(254) character set latin1 default NULL,

`password` varchar(254) character set latin1 default NULL,

`GPS` varchar(100) character set latin1 default NULL,

`location_address` varchar(254) character set latin1 default NULL,

`location_details` varchar(254) character set latin1 default NULL,

`comment` varchar(254) character set latin1 default NULL,

PRIMARY KEY (`ID`),

KEY `ID_user` (`ID_user`)

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

--

-- Dumping data for table `device`

--

-- --------------------------------------------------------

--

-- Table structure for table `iface`

--

CREATE TABLE `iface` (

`ID` int(11) NOT NULL auto_increment,

`ID_device` int(11) default NULL,

`ID_segment` int(11) default NULL,

`MAC` varchar(15) collate utf8_czech_ci default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`),

KEY `device_iface` (`ID_device`),

KEY `segment_iface` (`ID_segment`)

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

--

-- Dumping data for table `iface`

--

-- --------------------------------------------------------

--

-- Table structure for table `ip_address`

--

CREATE TABLE `ip_address` (

`ID_iface` int(11) default NULL,

`ID_VLAN_iface` int(11) default NULL,

`ID_subnet` int(11) default NULL,

`IP_address` int(11) default NULL,

`ID` int(11) NOT NULL auto_increment,

PRIMARY KEY (`ID`),

KEY `iface_address` (`ID_iface`),

KEY `VLAN_iface_address` (`ID_VLAN_iface`),

KEY `subnet_address` (`ID_subnet`)

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

--

-- Dumping data for table `ip_address`

--

-- --------------------------------------------------------

--

-- Table structure for table `member`

--

CREATE TABLE `member` (

`ID` int(11) NOT NULL auto_increment,

`street_number` varchar(50) character set latin1 default NULL,

`street` varchar(250) character set latin1 default NULL,

`town` varchar(250) character set latin1 default NULL,

`ZIP_code` varchar(10) character set latin1 default NULL,

`type` tinyint(4) default NULL,

`login` varchar(40) character set latin1 default NULL,

`password` varchar(40) character set latin1 default NULL,

`qos_ceil` varchar(20) character set latin1 default NULL,

`qos_rate` varchar(20) character set latin1 default NULL,

`entrance_fee` double default NULL,

`debt_payment_rate` double default NULL,

`entrance_fee_left` double default NULL,

`must_pay_regular_fee` tinyint(4) default NULL,

`current_credit` float default NULL,

`entrance_date` date default NULL,

`comment` varchar(250) character set latin1 default NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=2 ;

--

-- Dumping data for table `member`

--

-- --------------------------------------------------------

--

-- Table structure for table `payment`

--

CREATE TABLE `payment` (

`ID` int(11) NOT NULL auto_increment,

`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`text` varchar(254) collate utf8_czech_ci default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`type` varchar(254) collate utf8_czech_ci default NULL,

`variable_symbol` bigint(20) default NULL,

`constant_symbol` int(11) default NULL,

`specific_symbol` int(11) default NULL,

`amount` double default NULL,

`fee` double default NULL,

`ID_payer` int(11) default NULL,

`unassigned_amount` double default NULL,

PRIMARY KEY (`ID`),

KEY `user_payment` (`ID_payer`)

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

--

-- Dumping data for table `payment`

--

-- --------------------------------------------------------

--

-- Table structure for table `payment_assignment`

--

CREATE TABLE `payment_assignment` (

`ID_member` int(11) default NULL,

`ID_payment` int(11) default NULL,

`amount` double default NULL,

`type` varchar(20) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

`ID` int(11) NOT NULL auto_increment,

PRIMARY KEY (`ID`),

KEY `member_payment` (`ID_member`),

KEY `payment_assignment` (`ID_payment`)

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

--

-- Dumping data for table `payment_assignment`

--

-- --------------------------------------------------------

--

-- Table structure for table `port`

--

CREATE TABLE `port` (

`ID` int(11) NOT NULL auto_increment,

`ID_device` int(11) default NULL,

`ID_segment` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`),

KEY `device_port` (`ID_device`),

KEY `segment_port` (`ID_segment`)

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

--

-- Dumping data for table `port`

--

-- --------------------------------------------------------

--

-- Table structure for table `port_belongs_to_vlan`

--

CREATE TABLE `port_belongs_to_vlan` (

`ID_port` int(11) default NULL,

`ID_VLAN` int(11) default NULL,

`ID` int(11) NOT NULL auto_increment,

PRIMARY KEY (`ID`),

KEY `VLAN_on_port` (`ID_VLAN`),

KEY `port_belongs` (`ID_port`)

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

--

-- Dumping data for table `port_belongs_to_vlan`

--

-- --------------------------------------------------------

--

-- Table structure for table `segment`

--

CREATE TABLE `segment` (

`ID` int(11) NOT NULL auto_increment,

`name` varchar(254) collate utf8_czech_ci default NULL,

`technology` varchar(254) collate utf8_czech_ci default NULL,

`bitrate` int(11) default NULL,

`duplex` tinyint(4) default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`)

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

--

-- Dumping data for table `segment`

--

-- --------------------------------------------------------

--

-- Table structure for table `subnet`

--

CREATE TABLE `subnet` (

`ID` int(11) NOT NULL auto_increment,

`ID_OSPF_area` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`network_address` int(11) default NULL,

`netmask` int(11) default NULL,

PRIMARY KEY (`ID`)

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

--

-- Dumping data for table `subnet`

--

-- --------------------------------------------------------

--

-- Table structure for table `user`

--

CREATE TABLE `user` (

`ID_member` int(11) default NULL,

`ID` int(11) NOT NULL default '0',

`name` varchar(30) character set latin1 default NULL,

`middle_name` varchar(30) character set latin1 default NULL,

`surname` varchar(60) character set latin1 default NULL,

`pre_title` varchar(40) character set latin1 default NULL,

`post_title` varchar(30) character set latin1 default NULL,

`birthday` date default NULL,

`phone` varchar(40) character set latin1 default NULL,

`email` varchar(60) character set latin1 default NULL,

`login` varchar(50) character set latin1 default NULL,

`password` varchar(50) character set latin1 default NULL,

`web_messages_types` int(11) default NULL,

`email_messages_types` int(11) default NULL,

`comment` varchar(250) character set latin1 default NULL,

PRIMARY KEY (`ID`),

KEY `member_user` (`ID_member`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

--

-- Dumping data for table `user`

--

-- --------------------------------------------------------

--

-- Table structure for table `vlan`

--

CREATE TABLE `vlan` (

`ID` int(11) NOT NULL auto_increment,

`name` varchar(254) collate utf8_czech_ci default NULL,

`tag_802_1q` int(11) default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`ID`)

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

--

-- Dumping data for table `vlan`

--

-- --------------------------------------------------------

--

-- Table structure for table `vlan_iface`

--

CREATE TABLE `vlan_iface` (

`ID` int(11) NOT NULL auto_increment,

`ID_VLAN` int(11) default NULL,

`ID_iface` int(11) default NULL,

PRIMARY KEY (`ID`),

KEY `VLAN_on_iface` (`ID_VLAN`),

KEY `iface_belongs_to_VLAN` (`ID_iface`)

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

--

-- Dumping data for table `vlan_iface`

--

--

-- Constraints for dumped tables

--

--

-- Constraints for table `access_mod`

--

ALTER TABLE `access_mod`

ADD CONSTRAINT `device_access_mod` FOREIGN KEY (`ID_device`) REFERENCES `device` (`ID`),

ADD CONSTRAINT `user_access_mod` FOREIGN KEY (`ID_user_admin`) REFERENCES `user` (`ID`);

--

-- Constraints for table `contact`

--

ALTER TABLE `contact`

ADD CONSTRAINT `user_contact` FOREIGN KEY (`ID_user`) REFERENCES `user` (`ID`);

--

-- Constraints for table `credit_mod`

--

ALTER TABLE `credit_mod`

ADD CONSTRAINT `member_credit_mod` FOREIGN KEY (`ID_member`) REFERENCES `member` (`ID`),

ADD CONSTRAINT `user_credit_mod` FOREIGN KEY (`ID_user_admin`) REFERENCES `user` (`ID`);

--

-- Constraints for table `device`

--

ALTER TABLE `device`

ADD CONSTRAINT `user_device` FOREIGN KEY (`ID_user`) REFERENCES `user` (`ID`);

--

-- Constraints for table `iface`

--

ALTER TABLE `iface`

ADD CONSTRAINT `segment_iface` FOREIGN KEY (`ID_segment`) REFERENCES `segment` (`ID`),

ADD CONSTRAINT `device_iface` FOREIGN KEY (`ID_device`) REFERENCES `device` (`ID`);

--

-- Constraints for table `ip_address`

--

ALTER TABLE `ip_address`

ADD CONSTRAINT `subnet_address` FOREIGN KEY (`ID_subnet`) REFERENCES `subnet` (`ID`),

ADD CONSTRAINT `iface_address` FOREIGN KEY (`ID_iface`) REFERENCES `iface` (`ID`),

ADD CONSTRAINT `VLAN_iface_address` FOREIGN KEY (`ID_VLAN_iface`) REFERENCES `vlan_iface` (`ID`);

--

-- Constraints for table `payment`

--

ALTER TABLE `payment`

ADD CONSTRAINT `user_payment` FOREIGN KEY (`ID_payer`) REFERENCES `user` (`ID`);

--

-- Constraints for table `payment_assignment`

--

ALTER TABLE `payment_assignment`

ADD CONSTRAINT `payment_assignment` FOREIGN KEY (`ID_payment`) REFERENCES `member` (`ID`),

ADD CONSTRAINT `member_payment` FOREIGN KEY (`ID_member`) REFERENCES `member` (`ID`);

--

-- Constraints for table `port`

--

ALTER TABLE `port`

ADD CONSTRAINT `segment_port` FOREIGN KEY (`ID_segment`) REFERENCES `segment` (`ID`),

ADD CONSTRAINT `device_port` FOREIGN KEY (`ID_device`) REFERENCES `port` (`ID`);

--

-- Constraints for table `port_belongs_to_vlan`

--

ALTER TABLE `port_belongs_to_vlan`

ADD CONSTRAINT `port_belongs` FOREIGN KEY (`ID_port`) REFERENCES `port` (`ID`),

ADD CONSTRAINT `VLAN_on_port` FOREIGN KEY (`ID_VLAN`) REFERENCES `vlan` (`ID`);

--

-- Constraints for table `user`

--

ALTER TABLE `user`

ADD CONSTRAINT `member_user` FOREIGN KEY (`ID_member`) REFERENCES `member` (`ID`);

--

-- Constraints for table `vlan_iface`

--

ALTER TABLE `vlan_iface`

ADD CONSTRAINT `iface_belongs_to_VLAN` FOREIGN KEY (`ID_iface`) REFERENCES `iface` (`ID`),

ADD CONSTRAINT `VLAN_on_iface` FOREIGN KEY (`ID_VLAN`) REFERENCES `vlan` (`ID`);