[Summary view] [Print] [Text view]

   1  -- CREATE DATABASE ocsweb;
   2  -- USE ocsweb;
   3  
   4  
   5  CREATE TABLE hardware (
   6    ID INTEGER NOT NULL auto_increment,
   7    DEVICEID VARCHAR(255) not NULL,
   8    NAME VARCHAR(255) default NULL,
   9    WORKGROUP VARCHAR(255) default NULL,
  10    USERDOMAIN VARCHAR(255) default NULL,
  11    OSNAME VARCHAR(255) default NULL,
  12    OSVERSION VARCHAR(255) default NULL,
  13    OSCOMMENTS VARCHAR(255) default NULL,
  14    PROCESSORT VARCHAR(255) default NULL,
  15    PROCESSORS INTEGER default 0,
  16    PROCESSORN SMALLINT default NULL,
  17    MEMORY INTEGER default NULL,
  18    SWAP INTEGER default NULL,
  19    IPADDR VARCHAR(255) default NULL,
  20    ETIME DATETIME default NULL,
  21    LASTDATE DATETIME default NULL,
  22    LASTCOME DATETIME default NULL,
  23    QUALITY DECIMAL(4,3) default 0,
  24    FIDELITY BIGINT default 1,
  25    USERID VARCHAR(255) default NULL,
  26    `TYPE` INTEGER default NULL,
  27    DESCRIPTION VARCHAR(255) default NULL,
  28    WINCOMPANY VARCHAR(255) default NULL,
  29    WINOWNER VARCHAR(255) default NULL,
  30    WINPRODID VARCHAR(255) default NULL,
  31    WINPRODKEY VARCHAR(255) default NULL,
  32    USERAGENT VARCHAR(50) default NULL,
  33    CHECKSUM INTEGER default 0,
  34    PRIMARY KEY  (ID),
  35    INDEX NAME (NAME),
  36    INDEX CHECKSUM (CHECKSUM),
  37    INDEX DEVICEID (DEVICEID)
  38  ) ENGINE=INNODB ;
  39  
  40  CREATE TABLE accesslog (
  41    ID INTEGER NOT NULL auto_increment,
  42    HARDWARE_ID INTEGER NOT NULL,
  43    USERID VARCHAR(255) default NULL,
  44    LOGDATE DATETIME default NULL,
  45    PROCESSES TEXT,
  46    PRIMARY KEY  (ID, HARDWARE_ID)
  47  ) ENGINE=INNODB ;
  48  
  49  CREATE TABLE accountinfo (
  50    HARDWARE_ID INTEGER NOT NULL,
  51    TAG VARCHAR(255) default 'NA',
  52    primary key(HARDWARE_ID),
  53    INDEX TAG (TAG)
  54  ) ENGINE=INNODB ;
  55  
  56  CREATE TABLE deploy (
  57    NAME VARCHAR(255) NOT NULL,
  58    CONTENT LONGBLOB NOT NULL,
  59    PRIMARY KEY  (NAME)
  60  ) ENGINE=MYISAM ;
  61  
  62  CREATE TABLE netmap (
  63    IP VARCHAR(15) NOT NULL,
  64    MAC VARCHAR(17) NOT NULL,
  65    MASK VARCHAR(15) NOT NULL,
  66    NETID VARCHAR(15) NOT NULL,
  67    DATE TIMESTAMP default CURRENT_TIMESTAMP,
  68    NAME VARCHAR(255) default NULL,
  69    PRIMARY KEY  (MAC),
  70    INDEX IP (IP),
  71    INDEX NETID (NETID)
  72  ) ENGINE=INNODB ;
  73  
  74  CREATE TABLE bios (
  75    HARDWARE_ID INTEGER NOT NULL,
  76    SMANUFACTURER VARCHAR(255) default NULL,
  77    SMODEL VARCHAR(255) default NULL,
  78    SSN VARCHAR(255) default NULL,
  79    `TYPE` VARCHAR(255) default NULL,
  80    BMANUFACTURER VARCHAR(255) default NULL,
  81    BVERSION VARCHAR(255) default NULL,
  82    BDATE VARCHAR(255) default NULL,
  83    PRIMARY KEY  (HARDWARE_ID),
  84    INDEX SSN (SSN)
  85  ) ENGINE=INNODB ;
  86  
  87  CREATE TABLE config (
  88    NAME VARCHAR(50) NOT NULL,
  89    IVALUE INTEGER default NULL,
  90    TVALUE VARCHAR(255) default NULL,
  91    COMMENTS TEXT,
  92    PRIMARY KEY (NAME)
  93  ) ENGINE=MYISAM ;
  94  
  95  CREATE TABLE controllers (
  96    ID INTEGER NOT NULL auto_increment,
  97    HARDWARE_ID INTEGER NOT NULL,
  98    MANUFACTURER VARCHAR(255) default NULL,
  99    NAME VARCHAR(255) default NULL,
 100    CAPTION VARCHAR(255) default NULL,
 101    DESCRIPTION VARCHAR(255) default NULL,
 102    VERSION VARCHAR(255) default NULL,
 103    `TYPE` VARCHAR(255) default NULL,
 104    PRIMARY KEY  (ID, HARDWARE_ID)
 105  ) ENGINE=INNODB ;
 106  
 107  CREATE TABLE devices (
 108    HARDWARE_ID INTEGER NOT NULL,
 109    NAME VARCHAR(50) NOT NULL,
 110    IVALUE INTEGER default NULL,
 111    TVALUE VARCHAR(255) default NULL,
 112    COMMENTS TEXT,
 113    INDEX HARDWARE_ID (HARDWARE_ID),
 114    INDEX TVALUE (TVALUE)
 115  ) ENGINE=INNODB ;
 116  
 117  CREATE TABLE drives (
 118    ID INTEGER NOT NULL auto_increment,
 119    HARDWARE_ID INTEGER NOT NULL,
 120    LETTER VARCHAR(255) default NULL,
 121    `TYPE` VARCHAR(255) default NULL,
 122    FILESYSTEM VARCHAR(255) default NULL,
 123    TOTAL INTEGER default NULL,
 124    FREE INTEGER default NULL,
 125    NUMFILES INTEGER default NULL,
 126    VOLUMN VARCHAR(255) default NULL,
 127    PRIMARY KEY  (ID, HARDWARE_ID)
 128  ) ENGINE=INNODB ;
 129  
 130  CREATE TABLE files (
 131    NAME VARCHAR(255) NOT NULL,
 132    VERSION VARCHAR(255) NOT NULL,
 133    OS VARCHAR(255) NOT NULL,
 134    CONTENT LONGBLOB NOT NULL,
 135    PRIMARY KEY  (NAME, OS, VERSION)
 136  ) ENGINE=MYISAM ;
 137  
 138  CREATE TABLE inputs (
 139    ID INTEGER NOT NULL auto_increment,
 140    HARDWARE_ID INTEGER NOT NULL,
 141    `TYPE` VARCHAR(255) default NULL,
 142    MANUFACTURER VARCHAR(255) default NULL,
 143    CAPTION VARCHAR(255) default NULL,
 144    DESCRIPTION VARCHAR(255) default NULL,
 145    INTERFACE VARCHAR(255) default NULL,
 146    POINTTYPE VARCHAR(255) default NULL,
 147    PRIMARY KEY  (ID, HARDWARE_ID)
 148  ) ENGINE=INNODB ;
 149  
 150  CREATE TABLE memories (
 151    ID INTEGER NOT NULL auto_increment,
 152    HARDWARE_ID INTEGER NOT NULL,
 153    CAPTION VARCHAR(255) default NULL,
 154    DESCRIPTION VARCHAR(255) default NULL,
 155    CAPACITY VARCHAR(255) default NULL,
 156    PURPOSE VARCHAR(255) default NULL,
 157    `TYPE` VARCHAR(255) default NULL,
 158    SPEED VARCHAR(255) default NULL,
 159    NUMSLOTS SMALLINT default NULL,
 160    PRIMARY KEY  (ID, HARDWARE_ID)
 161  ) ENGINE=INNODB ;
 162  
 163  CREATE TABLE modems (
 164    ID INTEGER NOT NULL auto_increment,
 165    HARDWARE_ID INTEGER NOT NULL,
 166    NAME VARCHAR(255) default NULL,
 167    MODEL VARCHAR(255) default NULL,
 168    DESCRIPTION VARCHAR(255) default NULL,
 169    `TYPE` VARCHAR(255) default NULL,
 170    PRIMARY KEY  (ID, HARDWARE_ID)
 171  ) ENGINE=INNODB ;
 172  
 173  CREATE TABLE monitors (
 174    ID INTEGER NOT NULL auto_increment,
 175    HARDWARE_ID INTEGER NOT NULL,
 176    MANUFACTURER VARCHAR(255) default NULL,
 177    CAPTION VARCHAR(255) default NULL,
 178    DESCRIPTION VARCHAR(255) default NULL,
 179    `TYPE` VARCHAR(255) default NULL,
 180    SERIAL VARCHAR(255) default NULL,
 181    PRIMARY KEY  (ID, HARDWARE_ID)
 182  ) ENGINE=INNODB ;
 183  
 184  
 185  CREATE TABLE networks (
 186    ID INTEGER NOT NULL auto_increment,
 187    HARDWARE_ID INTEGER NOT NULL,
 188    DESCRIPTION VARCHAR(255) default NULL,
 189    `TYPE` VARCHAR(255) default NULL,
 190    TYPEMIB VARCHAR(255) default NULL,
 191    SPEED VARCHAR(255) default NULL,
 192    MACADDR VARCHAR(255) default NULL,
 193    `STATUS` VARCHAR(255) default NULL,
 194    IPADDRESS VARCHAR(255) default NULL,
 195    IPMASK VARCHAR(255) default NULL,
 196    IPGATEWAY VARCHAR(255) default NULL,
 197    IPSUBNET VARCHAR(255) default NULL,
 198    IPDHCP VARCHAR(255) default NULL,
 199    PRIMARY KEY  (ID, HARDWARE_ID),
 200    INDEX MACADDR (MACADDR),
 201    INDEX IPSUBNET (IPSUBNET)
 202  ) ENGINE=INNODB ;
 203  
 204  CREATE TABLE network_devices(
 205    ID INTEGER NOT NULL auto_increment,
 206    DESCRIPTION VARCHAR(255) default NULL,
 207    `TYPE` VARCHAR(255) default NULL,
 208    MACADDR VARCHAR(255) default NULL,
 209    `USER` VARCHAR(255) default NULL,
 210    PRIMARY KEY (ID),
 211    INDEX MACADDR (MACADDR)
 212  ) ENGINE=MYISAM ;
 213  
 214  CREATE TABLE operators (
 215    ID VARCHAR(255) NOT NULL default '',
 216    FIRSTNAME VARCHAR(255) default NULL,
 217    LASTNAME VARCHAR(255) default NULL,
 218    PASSWD VARCHAR(50) default NULL,
 219    ACCESSLVL INTEGER default NULL,
 220    COMMENTS text,
 221    PRIMARY KEY  (ID)
 222  ) ENGINE=MYISAM ;
 223  
 224  CREATE TABLE ports (
 225    ID INTEGER NOT NULL auto_increment,
 226    HARDWARE_ID INTEGER NOT NULL,
 227    `TYPE` VARCHAR(255) default NULL,
 228    NAME VARCHAR(255) default NULL,
 229    CAPTION VARCHAR(255) default NULL,
 230    DESCRIPTION VARCHAR(255) default NULL,
 231    PRIMARY KEY  (ID, HARDWARE_ID)
 232  ) ENGINE=INNODB ;
 233  
 234  CREATE TABLE printers (
 235    ID INTEGER NOT NULL auto_increment,
 236    HARDWARE_ID INTEGER NOT NULL,
 237    NAME VARCHAR(255) default NULL,
 238    DRIVER VARCHAR(255) default NULL,
 239    PORT VARCHAR(255) default NULL,
 240    PRIMARY KEY  (ID, HARDWARE_ID)
 241  ) ENGINE=INNODB ;
 242  
 243  CREATE TABLE regconfig (
 244    ID INTEGER NOT NULL auto_increment,
 245    NAME VARCHAR(255) default NULL,
 246    REGTREE INTEGER default NULL,
 247    REGKEY text,
 248    REGVALUE VARCHAR(255) default NULL,
 249    PRIMARY KEY  (ID),
 250    KEY NAME (NAME)
 251  ) ENGINE=MYISAM ;
 252  
 253  CREATE TABLE registry (
 254    ID INTEGER NOT NULL auto_increment,
 255    HARDWARE_ID INTEGER NOT NULL,
 256    NAME VARCHAR(255) default NULL,
 257    REGVALUE VARCHAR(255) default NULL,
 258    PRIMARY KEY  (ID, HARDWARE_ID),
 259    KEY NAME (NAME)
 260  ) ENGINE=INNODB ;
 261  
 262  CREATE TABLE slots (
 263    ID INTEGER NOT NULL auto_increment,
 264    HARDWARE_ID INTEGER NOT NULL,
 265    NAME VARCHAR(255) default NULL,
 266    DESCRIPTION VARCHAR(255) default NULL,
 267    DESIGNATION VARCHAR(255) default NULL,
 268    PURPOSE VARCHAR(255) default NULL,
 269    `STATUS` VARCHAR(255) default NULL,
 270    PSHARE tinyint(4) default NULL,
 271    PRIMARY KEY  (ID, HARDWARE_ID)
 272  ) ENGINE=INNODB ;
 273  
 274  CREATE TABLE softwares (
 275    ID INTEGER NOT NULL auto_increment,
 276    HARDWARE_ID INTEGER NOT NULL,
 277    PUBLISHER VARCHAR(255) default NULL,
 278    NAME VARCHAR(255) default NULL,
 279    VERSION VARCHAR(255) default NULL,
 280    FOLDER text,
 281    COMMENTS text,
 282    FILENAME VARCHAR(255) default NULL,
 283    FILESIZE INTEGER default '0',
 284    SOURCE INTEGER default NULL,
 285    PRIMARY KEY  (ID, HARDWARE_ID),
 286    INDEX NAME (NAME)
 287  ) ENGINE=INNODB ;
 288  
 289  CREATE TABLE `sounds` (
 290    ID INTEGER NOT NULL auto_increment,
 291    HARDWARE_ID INTEGER NOT NULL,
 292    MANUFACTURER VARCHAR(255) default NULL,
 293    NAME VARCHAR(255) default NULL,
 294    DESCRIPTION VARCHAR(255) default NULL,
 295    PRIMARY KEY  (ID, HARDWARE_ID)
 296  ) ENGINE=INNODB ;
 297  
 298  CREATE TABLE storages (
 299    ID INTEGER NOT NULL auto_increment,
 300    HARDWARE_ID INTEGER NOT NULL,
 301    MANUFACTURER VARCHAR(255) default NULL,
 302    NAME VARCHAR(255) default NULL,
 303    MODEL VARCHAR(255) default NULL,
 304    DESCRIPTION VARCHAR(255) default NULL,
 305    `TYPE` VARCHAR(255) default NULL,
 306    DISKSIZE INTEGER default NULL,
 307    PRIMARY KEY  (ID, HARDWARE_ID)
 308  ) ENGINE=INNODB ;
 309  
 310  CREATE TABLE videos (
 311    ID INTEGER NOT NULL auto_increment,
 312    HARDWARE_ID INTEGER NOT NULL,
 313    NAME VARCHAR(255) default NULL,
 314    CHIPSET VARCHAR(255) default NULL,
 315    MEMORY VARCHAR(255) default NULL,
 316    RESOLUTION VARCHAR(255) default NULL,
 317    PRIMARY KEY  (ID, HARDWARE_ID)
 318  ) ENGINE=INNODB ;
 319  
 320  CREATE TABLE devicetype (
 321    ID INTEGER NOT NULL auto_increment,
 322    NAME VARCHAR(255) default NULL,
 323    PRIMARY KEY  (ID)
 324  ) ENGINE=MYISAM ;
 325  
 326  CREATE TABLE subnet (
 327    NETID VARCHAR(15) NOT NULL,
 328    NAME VARCHAR(255),
 329    ID INTEGER,
 330    MASK VARCHAR(255),
 331    PRIMARY KEY (NETID)
 332  ) ENGINE=MYISAM ;
 333  
 334  CREATE TABLE locks(
 335    HARDWARE_ID INTEGER NOT NULL PRIMARY KEY,
 336    ID INTEGER DEFAULT NULL,
 337    SINCE TIMESTAMP,
 338    INDEX SINCE (SINCE)
 339  ) ENGINE=HEAP ;
 340  
 341  CREATE TABLE dico_cat(
 342    NAME VARCHAR(255) NOT NULL,
 343    PERMANENT TINYINT DEFAULT 0,
 344    PRIMARY KEY(NAME)
 345  ) ENGINE=MYISAM ;
 346  
 347  CREATE TABLE dico_ignored(
 348    EXTRACTED VARCHAR(255) NOT NULL,
 349    PRIMARY KEY(EXTRACTED)
 350  ) ENGINE=MYISAM ;
 351  
 352  CREATE TABLE dico_soft( 
 353    EXTRACTED VARCHAR(255) NOT NULL,
 354    FORMATTED VARCHAR(255) NOT NULL,
 355    PRIMARY KEY(EXTRACTED)
 356  ) ENGINE=MYISAM ;
 357  
 358  CREATE TABLE deleted_equiv(
 359    DATE TIMESTAMP, 
 360    DELETED VARCHAR(255) NOT NULL,
 361    EQUIVALENT VARCHAR(255) default NULL
 362  ) ENGINE=MYISAM ;
 363  
 364  CREATE TABLE download_available(
 365      FILEID VARCHAR(255) NOT NULL PRIMARY KEY,
 366      NAME VARCHAR(255) NOT NULL,
 367      PRIORITY INTEGER NOT NULL,
 368      FRAGMENTS INTEGER NOT NULL,
 369      SIZE INTEGER NOT NULL,
 370      OSNAME VARCHAR(255) NOT NULL,
 371      COMMENT TEXT
 372  ) ENGINE = INNODB;
 373  
 374  CREATE TABLE download_enable(
 375      ID INTEGER NOT NULL auto_increment PRIMARY KEY,
 376      FILEID VARCHAR(255) NOT NULL,
 377      INFO_LOC VARCHAR(255) NOT NULL,
 378      PACK_LOC VARCHAR(255) NOT NULL,
 379      CERT_PATH VARCHAR(255),
 380      CERT_FILE VARCHAR(255)
 381  ) ENGINE = INNODB;
 382  
 383  CREATE TABLE download_history(
 384      HARDWARE_ID INTEGER NOT NULL,
 385      PKG_ID INTEGER default NULL,
 386      PKG_NAME VARCHAR(255),
 387      PRIMARY KEY(HARDWARE_ID, PKG_ID)
 388  ) ENGINE = INNODB;
 389  
 390  CREATE TABLE conntrack(
 391      IP VARCHAR(255),
 392      `TIMESTAMP` TIMESTAMP,
 393      PRIMARY KEY(IP)
 394  ) ENGINE = HEAP;
 395  
 396  
 397  ALTER TABLE monitors ADD COLUMN SERIAL VARCHAR(255);
 398  ALTER TABLE netmap ADD COLUMN MASK VARCHAR(15);
 399  ALTER TABLE netmap ADD COLUMN NETID VARCHAR(15);
 400  ALTER TABLE netmap ADD INDEX NETID (NETID);
 401  ALTER TABLE netmap ADD COLUMN DATE TIMESTAMP;
 402  ALTER TABLE netmap ADD COLUMN NAME VARCHAR(255) default NULL;
 403  ALTER TABLE networks ADD COLUMN IPSUBNET VARCHAR(15);
 404  ALTER TABLE networks ADD INDEX IPSUBNET (IPSUBNET);
 405  ALTER TABLE networks ADD INDEX MACADDR (MACADDR);
 406  ALTER TABLE hardware ADD COLUMN CHECKSUM INTEGER default NULL;
 407  ALTER TABLE hardware CHANGE COLUMN CHECKSUM CHECKSUM INTEGER default 131071;
 408  ALTER TABLE hardware add column WINPRODKEY VARCHAR(255) default NULL;
 409  ALTER TABLE hardware add column USERDOMAIN VARCHAR(255) default NULL;
 410  
 411  ALTER TABLE hardware CHANGE ID ID INTEGER;
 412  ALTER TABLE hardware DROP PRIMARY KEY;
 413  ALTER TABLE hardware ADD COLUMN ID integer not NULL FIRST;
 414  ALTER TABLE hardware ADD INDEX ID (ID);
 415  ALTER TABLE hardware CHANGE ID ID INTEGER auto_increment;
 416  ALTER TABLE hardware add PRIMARY KEY(DEVICEID, ID);
 417  
 418  ALTER TABLE bios DROP PRIMARY KEY;
 419  ALTER TABLE bios ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 420  UPDATE bios SET bios.HARDWARE_ID= (SELECT ID FROM hardware WHERE bios.DEVICEID = hardware.DEVICEID);
 421  ALTER TABLE bios DROP DEVICEID;
 422  ALTER TABLE bios ADD PRIMARY KEY(HARDWARE_ID);
 423  
 424  ALTER TABLE accountinfo DROP PRIMARY KEY;
 425  ALTER TABLE accountinfo ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 426  UPDATE accountinfo SET accountinfo.HARDWARE_ID= (SELECT ID FROM hardware WHERE accountinfo.DEVICEID = hardware.DEVICEID);
 427  ALTER TABLE accountinfo DROP DEVICEID;
 428  ALTER TABLE accountinfo ADD PRIMARY KEY(HARDWARE_ID);
 429  
 430  ALTER TABLE devices DROP PRIMARY KEY;
 431  ALTER TABLE devices ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 432  UPDATE devices SET devices.HARDWARE_ID= (SELECT ID FROM hardware WHERE devices.DEVICEID = hardware.DEVICEID);
 433  ALTER TABLE devices DROP DEVICEID;
 434  ALTER TABLE devices ADD INDEX HARDWARE_ID (HARDWARE_ID);
 435  
 436  ALTER TABLE controllers change ID ID INTEGER;
 437  ALTER TABLE controllers DROP PRIMARY KEY;
 438  ALTER TABLE controllers ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 439  UPDATE controllers SET controllers.HARDWARE_ID= (SELECT ID FROM hardware WHERE controllers.DEVICEID = hardware.DEVICEID);
 440  ALTER TABLE controllers ADD INDEX ID (ID);
 441  ALTER TABLE controllers change ID ID INTEGER auto_increment;
 442  ALTER TABLE controllers ADD PRIMARY KEY(HARDWARE_ID,ID);
 443  ALTER TABLE controllers DROP DEVICEID;
 444  
 445  ALTER TABLE slots change ID ID INTEGER;
 446  ALTER TABLE slots DROP PRIMARY KEY;
 447  ALTER TABLE slots ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 448  UPDATE slots SET slots.HARDWARE_ID= (SELECT ID FROM hardware WHERE slots.DEVICEID = hardware.DEVICEID);
 449  ALTER TABLE slots ADD INDEX ID (ID);
 450  ALTER TABLE slots change ID ID INTEGER auto_increment;
 451  ALTER TABLE slots ADD PRIMARY KEY(HARDWARE_ID,ID);
 452  ALTER TABLE slots DROP DEVICEID;
 453  
 454  ALTER TABLE registry change ID ID INTEGER;
 455  ALTER TABLE registry DROP PRIMARY KEY;
 456  ALTER TABLE registry ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 457  UPDATE registry SET registry.HARDWARE_ID= (SELECT ID FROM hardware WHERE registry.DEVICEID = hardware.DEVICEID);
 458  ALTER TABLE registry ADD INDEX ID (ID);
 459  ALTER TABLE registry change ID ID INTEGER auto_increment;
 460  ALTER TABLE registry ADD PRIMARY KEY(HARDWARE_ID,ID);
 461  ALTER TABLE registry DROP DEVICEID;
 462  
 463  INSERT INTO network_devices(DESCRIPTION,TYPE,MACADDR,`USER`) SELECT DESCRIPTION,TYPE,MACADDR,TYPEMIB FROM networks WHERE DEVICEID LIKE "NETWORK_DEVICE-%";
 464  DELETE FROM network_devices WHERE DEVICEID LIKE "NETWORK_DEVICE-%";
 465  
 466  ALTER TABLE networks change ID ID INTEGER;
 467  ALTER TABLE networks DROP PRIMARY KEY;
 468  ALTER TABLE networks ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 469  UPDATE networks SET networks.HARDWARE_ID= (SELECT ID FROM hardware WHERE networks.DEVICEID = hardware.DEVICEID);
 470  ALTER TABLE networks ADD INDEX ID (ID);
 471  ALTER TABLE networks change ID ID INTEGER auto_increment;
 472  ALTER TABLE networks ADD PRIMARY KEY(HARDWARE_ID,ID);
 473  ALTER TABLE networks DROP DEVICEID;
 474  
 475  ALTER TABLE memories change ID ID INTEGER;
 476  ALTER TABLE memories DROP PRIMARY KEY;
 477  ALTER TABLE memories ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 478  UPDATE memories SET memories.HARDWARE_ID= (SELECT ID FROM hardware WHERE memories.DEVICEID = hardware.DEVICEID);
 479  ALTER TABLE memories ADD INDEX ID (ID);
 480  ALTER TABLE memories change ID ID INTEGER auto_increment;
 481  ALTER TABLE memories ADD PRIMARY KEY(HARDWARE_ID,ID);
 482  ALTER TABLE memories DROP DEVICEID;
 483  
 484  ALTER TABLE drives change ID ID INTEGER;
 485  ALTER TABLE drives DROP PRIMARY KEY;
 486  ALTER TABLE drives ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 487  UPDATE drives SET drives.HARDWARE_ID= (SELECT ID FROM hardware WHERE drives.DEVICEID = hardware.DEVICEID);
 488  ALTER TABLE drives ADD INDEX ID (ID);
 489  ALTER TABLE drives change ID ID INTEGER auto_increment;
 490  ALTER TABLE drives ADD PRIMARY KEY(HARDWARE_ID,ID);
 491  ALTER TABLE drives DROP DEVICEID;
 492  
 493  ALTER TABLE storages change ID ID INTEGER;
 494  ALTER TABLE storages DROP PRIMARY KEY;
 495  ALTER TABLE storages ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 496  UPDATE storages SET storages.HARDWARE_ID= (SELECT ID FROM hardware WHERE storages.DEVICEID = hardware.DEVICEID);
 497  ALTER TABLE storages ADD INDEX ID (ID);
 498  ALTER TABLE storages change ID ID INTEGER auto_increment;
 499  ALTER TABLE storages ADD PRIMARY KEY(HARDWARE_ID,ID);
 500  ALTER TABLE storages DROP DEVICEID;
 501  
 502  ALTER TABLE ports change ID ID INTEGER;
 503  ALTER TABLE ports DROP PRIMARY KEY;
 504  ALTER TABLE ports ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 505  UPDATE ports SET ports.HARDWARE_ID= (SELECT ID FROM hardware WHERE ports.DEVICEID = hardware.DEVICEID);
 506  ALTER TABLE ports ADD INDEX ID (ID);
 507  ALTER TABLE ports change ID ID INTEGER auto_increment;
 508  ALTER TABLE ports ADD PRIMARY KEY(HARDWARE_ID,ID);
 509  ALTER TABLE ports DROP DEVICEID;
 510  
 511  ALTER TABLE accesslog change ID ID INTEGER;
 512  ALTER TABLE accesslog DROP PRIMARY KEY;
 513  ALTER TABLE accesslog ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 514  UPDATE accesslog SET accesslog.HARDWARE_ID= (SELECT ID FROM hardware WHERE accesslog.DEVICEID = hardware.DEVICEID);
 515  ALTER TABLE accesslog ADD INDEX ID (ID);
 516  ALTER TABLE accesslog change ID ID INTEGER auto_increment;
 517  ALTER TABLE accesslog ADD PRIMARY KEY(HARDWARE_ID,ID);
 518  ALTER TABLE accesslog DROP DEVICEID;
 519  
 520  ALTER TABLE softwares change ID ID INTEGER;
 521  ALTER TABLE softwares DROP PRIMARY KEY;
 522  ALTER TABLE softwares ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 523  UPDATE softwares SET softwares.HARDWARE_ID= (SELECT ID FROM hardware WHERE softwares.DEVICEID = hardware.DEVICEID);
 524  ALTER TABLE softwares ADD INDEX ID (ID);
 525  ALTER TABLE softwares change ID ID INTEGER auto_increment;
 526  ALTER TABLE softwares ADD PRIMARY KEY(HARDWARE_ID,ID);
 527  ALTER TABLE softwares DROP DEVICEID;
 528  
 529  ALTER TABLE monitors change ID ID INTEGER;
 530  ALTER TABLE monitors DROP PRIMARY KEY;
 531  ALTER TABLE monitors ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 532  UPDATE monitors SET monitors.HARDWARE_ID= (SELECT ID FROM hardware WHERE monitors.DEVICEID = hardware.DEVICEID);
 533  ALTER TABLE monitors ADD INDEX ID (ID);
 534  ALTER TABLE monitors change ID ID INTEGER auto_increment;
 535  ALTER TABLE monitors ADD PRIMARY KEY(HARDWARE_ID,ID);
 536  ALTER TABLE monitors DROP DEVICEID;
 537  
 538  ALTER TABLE modems change ID ID INTEGER;
 539  ALTER TABLE modems DROP PRIMARY KEY;
 540  ALTER TABLE modems ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 541  UPDATE modems SET modems.HARDWARE_ID= (SELECT ID FROM hardware WHERE modems.DEVICEID = hardware.DEVICEID);
 542  ALTER TABLE modems ADD INDEX ID (ID);
 543  ALTER TABLE modems change ID ID INTEGER auto_increment;
 544  ALTER TABLE modems ADD PRIMARY KEY(HARDWARE_ID,ID);
 545  ALTER TABLE modems DROP DEVICEID;
 546  
 547  ALTER TABLE inputs change ID ID INTEGER;
 548  ALTER TABLE inputs DROP PRIMARY KEY;
 549  ALTER TABLE inputs ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 550  UPDATE inputs SET inputs.HARDWARE_ID= (SELECT ID FROM hardware WHERE inputs.DEVICEID = hardware.DEVICEID);
 551  ALTER TABLE inputs ADD INDEX ID (ID);
 552  ALTER TABLE inputs change ID ID INTEGER auto_increment;
 553  ALTER TABLE inputs ADD PRIMARY KEY(HARDWARE_ID,ID);
 554  ALTER TABLE inputs DROP DEVICEID;
 555  
 556  ALTER TABLE printers change ID ID INTEGER;
 557  ALTER TABLE printers DROP PRIMARY KEY;
 558  ALTER TABLE printers ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 559  UPDATE printers SET printers.HARDWARE_ID= (SELECT ID FROM hardware WHERE printers.DEVICEID = hardware.DEVICEID);
 560  ALTER TABLE printers ADD INDEX ID (ID);
 561  ALTER TABLE printers change ID ID INTEGER auto_increment;
 562  ALTER TABLE printers ADD PRIMARY KEY(HARDWARE_ID,ID);
 563  ALTER TABLE printers DROP DEVICEID;
 564  
 565  ALTER TABLE videos change ID ID INTEGER;
 566  ALTER TABLE videos DROP PRIMARY KEY;
 567  ALTER TABLE videos ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 568  UPDATE videos SET videos.HARDWARE_ID= (SELECT ID FROM hardware WHERE videos.DEVICEID = hardware.DEVICEID);
 569  ALTER TABLE videos ADD INDEX ID (ID);
 570  ALTER TABLE videos change ID ID INTEGER auto_increment;
 571  ALTER TABLE videos ADD PRIMARY KEY(HARDWARE_ID,ID);
 572  ALTER TABLE videos DROP DEVICEID;
 573  
 574  ALTER TABLE sounds change ID ID INTEGER;
 575  ALTER TABLE sounds DROP PRIMARY KEY;
 576  ALTER TABLE sounds ADD COLUMN HARDWARE_ID integer not NULL FIRST;
 577  UPDATE sounds SET sounds.HARDWARE_ID= (SELECT ID FROM hardware WHERE sounds.DEVICEID = hardware.DEVICEID);
 578  ALTER TABLE sounds ADD INDEX ID (ID);
 579  ALTER TABLE sounds change ID ID INTEGER auto_increment;
 580  ALTER TABLE sounds ADD PRIMARY KEY(HARDWARE_ID,ID);
 581  ALTER TABLE sounds DROP DEVICEID;
 582  
 583  DROP TABLE IF EXISTS tag;
 584  
 585  TRUNCATE TABLE locks;
 586  
 587  ALTER TABLE softwares CHANGE NAME NAME VARCHAR(255) default NULL;
 588  ALTER TABLE locks DROP DEVICEID;
 589  ALTER TABLE locks ADD HARDWARE_ID INTEGER NOT NULL PRIMARY KEY FIRST;
 590  ALTER TABLE locks ADD INDEX SINCE (SINCE);
 591  
 592  DELETE FROM `config` WHERE name='GUI_VERSION';
 593  DELETE FROM `config` WHERE name='IP_MIN_QUALITY';
 594  
 595  INSERT INTO `config` VALUES ('FREQUENCY', 0, '', 'Specify the frequency (days) of inventories. (0: inventory at each login. -1: no inventory)');
 596  INSERT INTO `config` VALUES ('PROLOG_FREQ', 24, '', 'Specify the frequency (hours) of prolog, on agents');
 597  INSERT INTO `config` VALUES ('IPDISCOVER', 2, '', 'Max number of computers per gateway retrieving IP on the network');
 598  INSERT INTO `config` VALUES ('INVENTORY_DIFF', 1, '', 'Activate/Deactivate inventory incremental writing');
 599  INSERT INTO `config` VALUES ('IPDISCOVER_LATENCY', 100, '', 'Default latency between two arp requests');
 600  INSERT INTO `config` VALUES ('INVENTORY_TRANSACTION', 1, '', 'Enable/disable db commit at each inventory section');
 601  INSERT INTO `config` VALUES ('REGISTRY', 0, '', 'Activates or not the registry query function');
 602  INSERT INTO `config` VALUES ('IPDISCOVER_MAX_ALIVE', 7, '','Max number of days before an Ip Discover computer is replaced');
 603  INSERT INTO `config` VALUES ('DEPLOY', 1, '', 'Activates or not the automatic deployment option');
 604  INSERT INTO `config` VALUES ('UPDATE', 0, '', 'Activates or not the update feature');
 605  INSERT INTO `config` VALUES ('GUI_VERSION', 0, '4100', 'Version of the installed GUI and database');
 606  INSERT INTO `config` VALUES ('TRACE_DELETED', 0, '', 'Trace deleted/duplicated computers (Activated by GLPI)');
 607  INSERT INTO `config` VALUES ('LOGLEVEL', 0, '', 'ocs engine loglevel');
 608  INSERT INTO `config` VALUES ('AUTO_DUPLICATE_LVL', 7, '', 'Duplicates bitmap');
 609  INSERT INTO `config` VALUES ('DOWNLOAD', 0, '', 'Activate softwares auto deployment feature');
 610  INSERT INTO `config` VALUES ('DOWNLOAD_CYCLE_LATENCY', 60, '', 'Time between two cycles (seconds)');
 611  INSERT INTO `config` VALUES ('DOWNLOAD_PERIOD_LENGTH', 10, '', 'Number of cycles in a period');
 612  INSERT INTO `config` VALUES ('DOWNLOAD_FRAG_LATENCY', 10, '', 'Time between two downloads (seconds)');
 613  INSERT INTO `config` VALUES ('DOWNLOAD_PERIOD_LATENCY', 0, '', 'Time between two periods (seconds)');
 614  INSERT INTO `config` VALUES ('DOWNLOAD_TIMEOUT', 30, '', 'Validity of a package (in days)');
 615  INSERT INTO `config` VALUES ('LOCAL_SERVER', 0, 'localhost', 'Server address used for local import');
 616  INSERT INTO `config` VALUES ('LOCAL_PORT', 80, '', 'Server port used for local import');
 617  
 618  INSERT INTO `operators` VALUES ('admin','admin','admin','admin',1, 'Default administrator account');
 619  
 620  -- GRANT ALL PRIVILEGES ON ocsweb.* TO ocs IDENTIFIED BY 'ocs';
 621  -- GRANT ALL PRIVILEGES ON ocsweb.* TO ocs@localhost IDENTIFIED BY 'ocs';