Re: [lms] Paszportyzacja światłowodów w LMS - etap 2

9 Lut 2016


      On Tue, 09 Feb 2016 19:42:27 +0100 Tomasz Chiliński
<tomasz.chilinski@chilan.com> wrote

> 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
Czy kable maja miec powiazanie z netelements?
W netconnections chyba myslales o netwires-id a nie src/dst-connector ;)
I mam jeszcze prosbe konieczne jest uzywanie przedrostkow src/dst troche mylace
bo okreslaja kierunek relacji ( wiem czepiam sie)
> _______________________________________________
> lms mailing list
> lms@lists.lms.org.pl
> http://lists.lms.org.pl/mailman/listinfo/lms

Re: [lms] Paszportyzacja światłowodów w LMS - etap 2

ernest