9 Lut
2016
9 Lut
'16
19:42
W dniu 09.02.2016 19:40, Jaroslaw Dziubek napisał(a): > [Tuesday, 09 February 2016], Tomasz Chiliński napisał(a): > >> W dniu 09.02.2016 19:16, Jaroslaw Dziubek napisał(a): >> > [Tuesday, 09 February 2016], Tomasz Chiliński napisał(a): >> > >> >> To co szukujesz schemat mysql, a ja przygotuję pgsql i ewentualnie >> >> skoryguję mysql? >> > >> > Netnodes - dodałem pole ownerid - aczkolwiek mozna wykorzystac >> > ownership: >> > - ownership=0, coowner='' - węzeł firmowy >> > - ownership=0, coowner<>'' - węzeł współdzielony >> > - ownership>0 - węzeł kliencki >> > >> > >> > CREATE TABLE netnodes ( >> > id int(11) NOT NULL auto_increment, >> > name varchar(255) NOT NULL, >> > type tinyint DEFAULT 0, >> > invprojectid int(11), >> > status tinyint DEFAULT 0, >> > location varchar(255) DEFAULT '', >> > location_city int(11) DEFAULT NULL, >> > location_street int(11) DEFAULT NULL, >> > location_house varchar(32) DEFAULT NULL, >> > location_flat varchar(32) DEFAULT NULL, >> > longitude decimal(10,6) DEFAULT NULL, >> > latitude decimal(10,6) DEFAULT NULL, >> > ownership tinyint(1) DEFAULT 0, >> > coowner varchar(255) DEFAULT '', >> > ownerid int(11) NOT NULL DEFAULT '0' >> > uip tinyint(1) DEFAULT 0, >> > miar tinyint(1) DEFAULT 0, >> > divisionid int(11) DEFAULT NULL, >> > PRIMARY KEY (id), >> > FOREIGN KEY (invprojectid) REFERENCES invprojects (id) ON DELETE SET >> > NULL ON UPDATE CASCADE, >> > FOREIGN KEY (location_city) REFERENCES location_cities (id) ON >> > DELETE SET NULL ON UPDATE CASCADE, >> > FOREIGN KEY (location_street) REFERENCES location_streets (id) ON >> > DELETE SET NULL ON UPDATE CASCADE >> > FOREIGN KEY (divisionid) REFERENCES divisions (id) ON DELETE SET >> > NULL ON UPDATE CASCADE >> > ) ENGINE=INNODB; >> > >> > >> > Tutaj się zastanawiam nad definicjami dla urządzeń >> > (nastype, secret, community i channelid nie wyrzucic do osobnej tabeli >> > - netdevparams) >> Tak chyba trzeba byłoby skoro to nie ma sensu dla okablowania. > OK. Poprawki ponizej. > >> > CREATE TABLE netelements ( >> > id int(11) NOT NULL auto_increment, >> > name varchar(32) NOT NULL DEFAULT '', >> > type tinyint(1) NOT NULL DEFAULT '0', >> > description text NOT NULL DEFAULT '', >> > producer varchar(64) NOT NULL DEFAULT '', >> > model varchar(32) NOT NULL DEFAULT '', >> > serialnumber varchar(32) NOT NULL DEFAULT '', >> > purchasetime int(11) NOT NULL DEFAULT '0', >> > guaranteeperiod tinyint unsigned DEFAULT '0', >> > netnodeid int(11) DEFAULT NULL, >> > invprojectid int(11) DEFAULT NULL, >> > status tinyint DEFAULT '0', >> > netdevicemodelid int(11) DEFAULT NULL, >> > PRIMARY KEY (id), >> > FOREIGN KEY (netnodeid) REFERENCES netnodes (id) ON DELETE SET NULL ON UPDATE CASCADE, >> > FOREIGN KEY (invprojectid) REFERENCES invprojects (id) ON DELETE SET NULL ON UPDATE CASCADE, >> > FOREIGN KEY (netdevicemodelid) REFERENCES netdevicemodels (id) ON UPDATE CASCADE ON DELETE SET NULL >> > ) ENGINE=InnoDB; >> > > CREATE TABLE netdevparams ( > id int(11) NOT NULL auto_increment, > netelemid int(11) NOT NULL DEFAULT '0', > shortname varchar(32) NOT NULL DEFAULT '', > nastype int(11) NOT NULL DEFAULT '0', > clients int(11) NOT NULL DEFAULT '0', > secret varchar(60) NOT NULL DEFAULT '', > community varchar(50) NOT NULL DEFAULT '', > channelid int(11) DEFAULT NULL, > PRIMARY KEY (id), > INDEX channelid (channelid), > FOREIGN KEY (channelid) REFERENCES ewx_channels (id) ON DELETE SET > NULL ON UPDATE CASCADE, > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE NULL > ON UPDATE CASCADE, > ) ENGINE=InnoDB; > > >> > Zastanawiam sie czy nie zrobić unique dla samego netelemid, aczkolwiek >> > moga byc kable hybrydowe :) >> > >> > CREATE TABLE netcables ( >> > id int(11) NOT NULL auto_increment, >> > netelemid int(11) NOT NULL DEFAULT '0', >> > type tinyint(2) NOT NULL DEFAULT '0', >> > capacity smallint(4) NOT NULL DEFAULT '0', >> > distance int(4) UNSIGNED NOT NULL DEFAULT '0', >> > srcelemid int(11) DEFAULT NULL, >> > dstelemid int(11) DEFAULT NULL, >> > PRIMARY KEY (id), >> > INDEX netelemid(netelemid), >> > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > FOREIGN KEY (srcelemid) REFERENCES netelements (id) ON DELETE SET >> > NULL ON UPDATE CASCADE, >> > FOREIGN KEY (dstelemid) REFERENCES netelements (id) ON DELETE SET >> > NULL ON UPDATE CASCADE >> > UNIQUE KEY type (netelemid,type) >> > ) ENGINE=InnoDB; >> > >> > CREATE TABLE netports ( >> > id int(11) NOT NULL auto_increment, >> > netelemid int(11) NOT NULL DEFAULT '0', >> > label varchar(32) NOT NULL DEFAULT '', >> > type tinyint(2) UNSIGNED NOT NULL DEFAULT '0', >> > connector tinyint(2) UNSIGNED NOT NULL DEFAULT '0', >> > technology tinyint(2) UNSIGNED NOT NULL DEFAULT '0', >> > PRIMARY KEY (id), >> > INDEX netelemid(netelemid), >> > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > UNIQUE KEY label (label, netelemid) >> > ) ENGINE=InnoDB; >> > >> > CREATE TABLE netradiosectors ( >> > id int(11) NOT NULL auto_increment, >> > netelemid int(11) NOT NULL DEFAULT '0', >> > name varchar(64) NOT NULL, >> > azimuth decimal(9,2) DEFAULT 0 NOT NULL, >> > width decimal(9,2) DEFAULT 0 NOT NULL, >> > altitude smallint DEFAULT 0 NOT NULL, >> > rsrange int(11) DEFAULT 0 NOT NULL, >> > license varchar(64) DEFAULT NULL, >> > technology int(11) DEFAULT 0 NOT NULL, >> > frequency numeric(9,5) DEFAULT NULL, >> > frequency2 numeric(9,5) DEFAULT NULL, >> > bandwidth numeric(9,5) DEFAULT NULL, >> > PRIMARY KEY (id), >> > INDEX netelemid (netelemid), >> > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > UNIQUE KEY name (name, netelemid) >> > ) ENGINE=INNODB; >> > >> > >> > CREATE TABLE netparams ( >> > id int(11) NOT NULL auto_increment, >> > netelemid int(11) NOT NULL DEFAULT '0', >> > label varchar(64) NOT NULL, >> > type tinyint(2) NOT NULL DEFAULT '0', >> > capacity int(11) NOT NULL DEFAULT '1', >> > PRIMARY KEY (id), >> > INDEX netelemid (netdelemid), >> > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > ) ENGINE=INNODB; >> >> >> Co to jest netparams i co oznacza capacity? >> Pytam, bo jak widzę label i capacity to dziwnie to wygląda... > To są parametry elementów pasywnych: > - label: nazwa lub numer - np. "tacka spawów", "1" > - type: rodzaj adaptera (simplex SC, duplex LC) albo "tacka spawów" > - pojemność: > - dla tacki - pojemność tacki - 12/24/itp > - dla portu rozłączalnego - 2 (czyli 2 konektory mozna wpiąc w jeden > port) > > Teoretycznie mozna zrezygnowac z label i wyswietlac pozniej: > - tacka #1, tacka #2 > - port "simplex SC #1", port "duplex LC #10" albo w skrócie: sSC#1, > dLC#10 > >> > CREATE TABLE netsplitters ( >> > id int(11) NOT NULL auto_increment, >> > netelemid int(11) NOT NULL DEFAULT '0', >> > side tinyint(2) NOT NULL DEFAULT '0', >> > capacity int(11) NOT NULL DEFAULT '1', >> > PRIMARY KEY (id), >> > INDEX netelemid (netdelemid), >> > FOREIGN KEY (netelemid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > UNIQUE KEY side (netelemid,side) >> > ) ENGINE=INNODB; >> > >> > CREATE TABLE netwires ( >> > id int(11) NOT NULL auto_increment, >> > netcableid int(11) NOT NULL DEFAULT '0', >> > bundle tinyint(2) NOT NULL DEFAULT '1', >> > wire tinyint(2) NOT NULL DEFAULT '1', >> > PRIMARY KEY (id), >> > INDEX netcableid (netcableid), >> > FOREIGN KEY (netcableid) REFERENCES netelements (id) ON DELETE >> > CASCADE ON UPDATE CASCADE, >> > UNIQUE KEY wire (netcableid,bundle,wire) >> > ) ENGINE=INNODB; >> > >> > CREATE TABLE netconnections ( >> > id int(11) NOT NULL auto_increment, >> > srcwireid int(11) DEFAULT NULL, >> > dstwireid int(11) DEFAULT NULL, >> > srcconnector tinyint(2) DEFAULT NULL, >> > dstconnector tinyint(2) DEFAULT NULL, >> > distance float(4,1) DEFAULT NULL, >> > description varchar(50) NOT NULL DEFAULT '', >> > PRIMARY KEY (id), >> > FOREIGN KEY (srcwireid) REFERENCES netwires (id) ON DELETE CASCADE >> > ON UPDATE CASCADE, >> > FOREIGN KEY (dstwireid) REFERENCES netwires (id) ON DELETE CASCADE >> > ON UPDATE CASCADE, >> > UNIQUE KEY wires (srcwireid,dstwireid), >> > ) ENGINE=INNODB; Może lepiej byłoby, żebyś ten schemat robił online przez github? Wiesz, że github ma taką funkcję jak edytowanie pliku? -- Pozdrawiam Tomasz Chiliński, Chilan