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 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(-) ) 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: /repo// 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. -------------------------------------------------------------------------------