
SMT Database 
============


Clients(GUID        CHAR(50) PRIMARY KEY,
        HOSTNAME    VARCHAR(100) DEFAULT '',
        TARGET      VARCHAR(100),
        DESCRIPTION VARCHAR(500) DEFAULT '',
        LASTCONTACT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
       );

GUID:       The ID of the client machine (/etc/zmd/deviceid)
HOSTNAME    The hostname of the client if we get this during registration
TARGET      The target of the client if we get this during registration
DESCRIPTION Free text - currently not used
LASTCONTACT A timestamp, set everytime the client contacted the SMT server

In this table every registered GUID exists only one time. 

This table changes during every contact with the client.
It happens DELETE, UPDATE and INSERT statements during registration.
Every fetch of /repo/repoindex.xml updates the LASTCONTACT column.

Expected table size: depends on how many clients are registered against SMT 

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

Registration(GUID         CHAR(50) NOT NULL,
             PRODUCTID    integer NOT NULL,
             REGDATE      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
             NCCREGDATE   TIMESTAMP NULL default NULL,
             NCCREGERROR  integer DEFAULT 0,
             PRIMARY KEY(GUID, ProductID)
            );

GUID:       The ID of the client machine (/etc/zmd/deviceid)
PRODUCTID:  The ID of the Product which is installed on this client
            This ID is a reference to the Products table, PRODUCTDATAID column
REGDATE:    Timestamp when a client registered this product
NCCREGDATE: Timestamp when this registration was registered at NCC
NCCREGERROR:If not 0, the last registration at NCC of this GUID failed with an error.

This table is changed with every registration. 
It happens DELETE, UPDATE and INSERT statements during registration.

Expected table size: depends on how many clients are registered against SMT 
                     and how many products they have installed.


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

MachineData(GUID          CHAR(50) NOT NULL,
            KEYNAME       CHAR(50) NOT NULL,
            VALUE         BLOB,
            PRIMARY KEY(GUID, KEYNAME)
           );

GUID:      The ID of the client machine (/etc/zmd/deviceid)
           See also Registration.GUID
KEYNAME:   Parameter name which are send from the client during registration
           See Products.NEEDINFO (the <params> statements in the XML)
VALUE:     The Parameter value for this KEYNAME (can be hwinfo output)


This table is changed with every registration. 
It happens DELETE and INSERT statements during registration.

Expected table size: depends on how many clients are registered against SMT 
                     Every GUID will have around 20 rows in this table.

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

Products (PRODUCTDATAID   integer NOT NULL PRIMARY KEY,
          PRODUCT         VARCHAR(500) NOT NULL,
          VERSION         VARCHAR(100),
          REL             VARCHAR(100),
          ARCH            VARCHAR(100),
          PRODUCTLOWER    VARCHAR(500) NOT NULL,
          VERSIONLOWER    VARCHAR(100),
          RELLOWER        VARCHAR(100),
          ARCHLOWER       VARCHAR(100),
          FRIENDLY        VARCHAR(700),
          PARAMLIST       TEXT,
          NEEDINFO        TEXT,
          SERVICE         TEXT,
          PRODUCT_LIST    CHAR(1),
          PRODUCT_CLASS   CHAR(50),
          SRC             Char(1) DEFAULT 'N',
          UNIQUE(PRODUCTLOWER, VERSIONLOWER, RELLOWER, ARCHLOWER)
         );

PRODUCTDATAID: The ID of a product
PRODUCT:       The product name
VERSION:       The product version
REL:           The product release
ARCH:          The product architecture
PRODUCTLOWER:  lowercase PRODUCT
VERSIONLOWER:  lowercase VERSION
RELLOWER:      lowercase REL
ARCHLOWER:     lowercase ARCH
FRIENDLY:      Human readable Product name (if you want to display it)
PARAMLIST:     XML description of the requested parameters if you want
               to register this product.
NEEDINFO:      Similar to PARAMLIST but this is realy send during registration
               of this product.
SERVICE:       (currently not used in SMT)
PRODUCT_LIST:  Y/N Should this product send on a listProducts request of
               suse_register
PRODUCT_CLASS: Common Name for a class of products, e.g all
               SUSE-Linux-Enterprise-Server* products have PRODUCT_CALL 'SLES'
SRC:           N == data from NCC, C == custom data

This table is changed during an ncc-sync (maybe once a day). 
It happens UPDATE and INSERT statements during this operation.

Expected table size: Current rows: 227 (198 with PRODUCT_LIST Y)

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

ProductCatalogs(PRODUCTDATAID integer NOT NULL,
                CATALOGID     CHAR(50) NOT NULL,
                OPTIONAL      CHAR(1) DEFAULT 'N',
                SRC             Char(1) DEFAULT 'N',
                PRIMARY KEY(PRODUCTDATAID, CATALOGID)
               );

PRODUCTDATAID: See Products.PRODUCTDATAID
CATALOGID:     See Catalogs.CATALOGID
OPTIONAL:      Y/N If this catalog is optional with this product it is
               not send back from the registration request.
               Result: only zmd can see this catalog but it is not subscribed. 
SRC:           N == data from NCC, C == custom data

This table say which products gets which catalogs.

This table is changed during an ncc-sync (maybe once a day). 
It happens UPDATE and INSERT statements during this operation.

Expected table size: ~ 2 * Products

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

Catalogs(CATALOGID   CHAR(50) PRIMARY KEY, 
         NAME        VARCHAR(200) NOT NULL, 
         DESCRIPTION VARCHAR(500), 
         TARGET      VARCHAR(100),           -- null in case of single RPMMD source
         LOCALPATH   VARCHAR(300) NOT NULL,
         EXTHOST     VARCHAR(300) NOT NULL,
         EXTURL      VARCHAR(300) NOT NULL,  -- where to mirror from
         CATALOGTYPE CHAR(10) NOT NULL,
         DOMIRROR    CHAR(1) DEFAULT 'N',
         MIRRORABLE  CHAR(1) DEFAULT 'N',
         SRC             Char(1) DEFAULT 'N',
         UNIQUE(NAME, TARGET)
        );

CATALOGID:    The ID of this catalog (currently a sha1sum(<NAME>-<TARGET>) )
              This may change.
NAME:         The name of this catalog
DESCRIPTION:  A catalog description
TARGET:       The catalog target (NULL in case of a single rpmmd catalog)
              The target is something zmd special. It tells zmd about 
              interoperability for products(suse vs. rhat) and architectures.
LOCALPATH:    Path on SMT where this catalog can be found
              (this is not a full path! The full path can be build this way:
               <BasePath>/repo/<LOCALPATH>/ 
               where BasePath can be smt.con.LOCAL.mirrorTo or 
               apache DocumentRoot)
EXTURL:       scheme and host part of EXTURL
EXTURL:       URL where we can mirror this catalog from
CATALOGTYPE:  zypp/nu The catalog type
DOMIRROR:     Y/N Do the customer want to mirror this catalog?
MIRRORABLE:   Y/N Do we have access to EXTURL to mirror this catalog?
SRC:          N == data from NCC, C == custom data

This table is changed during an ncc-sync (maybe once a day). 
It happens UPDATE and INSERT statements during this operation.

Additionaly the DOMIRROR flag can be changed by the administrator.

Expected table size: ~ 2 * Products

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

Targets (OS      VARCHAR(200) NOT NULL PRIMARY KEY,
         TARGET  VARCHAR(100) NOT NULL,
         SRC             Char(1) DEFAULT 'N',
        );

OS:     The string you get if you call rug ping and return OSTarget.
TARGET: internal representation (See also Catalog.TARGET)
SRC:    N == data from NCC, C == custom data

This table is changed during an ncc-sync (maybe once a day). 
It happens UPDATE and INSERT statements during this operation.

Expected table size: Current rows: 8

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

Subscriptions(SUBID          CHAR(50) PRIMARY KEY,
              REGCODE        VARCHAR(100),
              SUBNAME        VARCHAR(100) NOT NULL,
              SUBTYPE        CHAR(20)  DEFAULT "UNKNOWN",
              SUBSTATUS      CHAR(20)  DEFAULT "UNKNOWN",
              SUBSTARTDATE   TIMESTAMP NOT NULL,
              SUBENDDATE     TIMESTAMP NOT NULL,
              SUBDURATION    BIGINT    DEFAULT 0,
              SERVERCLASS    CHAR(50),
              PRODUCT_CLASS  VARCHAR(100),
              NODECOUNT      integer NOT NULL,
              CONSUMED       integer DEFAULT 0
            );

SUBID          A unique id for this subscription
REGCODE        The registration code for a subscription
SUBNAME        The name of the subscription
SUBTYPE        The type of the subscription 
               FULL, PROVISIONAL, EVALUATION (maybe more if needed)
SUBSTATUS      The status of this subscription 
               ACTIVE, EXPIRED (maybe more if needed)
SUBSTARTDATE   time when the customer buy this subscription
SUBENDDATE     time when this subscription expires
SUBDURATION    valid period of this subscription
SERVERCLASS    The class of this subscription (OS or ADDON)
PRODUCT_CLASS  Define which products are valid for this subscription. 
               See also Products(PRODUCT_CLASS)
NODECOUNT      The number how many installations can be assigned to this 
               subscription. The value “-1” is defined as “unlimited”
CONSUMED       How many clients are assigned to this subscription 
               (value from NCC). This is a total value over the company.
               It includes also clients from other SMT servers then
               this one. 
This table is changed during an ncc-sync (maybe once a day). 
This table will be setup new during this operation, which means 
first all entries are deleted and then filled new with the current data.

Expected table size: Depends on how many subscriptions the customer has.

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

ClientSubscriptions(GUID    CHAR(50) NOT NULL,
                    SUBID   CHAR(50) NOT NULL,
                    PRIMARY KEY(GUID, SUBID)
                   );

This table defines which client is assigned to which subscription.

GUID     A Client GUID 
SUBID    The id of a subscription

This table is changed during an ncc-sync (maybe once a day). 
This table will be setup new during this operation, which means 
first all entries are deleted and then filled new with the current data.

Expected table size: Depends on how many machines are registered at NCC.


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