Page 1 of 5 12345 LastLast
Results 1 to 15 of 66

Thread: Common Use SQL Game Database Source

  1. #1

    Common Use SQL Game Database Source

    [Thread discontinued]

    I wondered if the SQL database i'm creating to reflect game elements could be different in distinct tools.

    As the answer was no, it would be sufficient to do that work only one time.

    Here's what i have so far (not finished yet). Please help improving it if you see anything.

    It's easier to discuss a diagram than source code. The database schema should be close to complete. (Use SaveAs for the unzoomed image.)



    Just found the export command. The diagram isn't sometimes clear, so refer to the sql.

    Code:
    --
    -- Table structure for table `AOTypes`
    --
    
    DROP TABLE IF EXISTS `AOTypes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `AOTypes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='AstroObject Types, Known: Star, Planet, Moon, Station, Resou';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `AstroObjects`
    --
    
    DROP TABLE IF EXISTS `AstroObjects`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `AstroObjects` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) CHARACTER SET latin1 NOT NULL,
      `primaryStarDistance` mediumint(9) NOT NULL DEFAULT '0',
      `ref_parent` int(11) NOT NULL DEFAULT '0',
      `ref_AOType` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `parent_idx` (`ref_parent`),
      KEY `aoType_idx` (`ref_AOType`),
      CONSTRAINT `aoType` FOREIGN KEY (`ref_AOType`) REFERENCES `AOTypes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `parent` FOREIGN KEY (`ref_parent`) REFERENCES `AstroObjects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ref_parent=0 will indicate a primary star with its parent be';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `BlastDoorTypes`
    --
    
    DROP TABLE IF EXISTS `BlastDoorTypes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `BlastDoorTypes` (
      `id` int(11) NOT NULL,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used in Ships, Known: Small, Medium, Large';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Commodities`
    --
    
    DROP TABLE IF EXISTS `Commodities`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Commodities` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      `galaxyAverage` int(11) DEFAULT NULL,
      `ref_category` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `category_idx` (`ref_category`),
      CONSTRAINT `category` FOREIGN KEY (`ref_category`) REFERENCES `CommodityCategories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `CommodityCategories`
    --
    
    DROP TABLE IF EXISTS `CommodityCategories`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `CommodityCategories` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sortId` smallint(2) DEFAULT NULL,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `FacilityTypes`
    --
    
    DROP TABLE IF EXISTS `FacilityTypes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `FacilityTypes` (
      `id` int(11) NOT NULL,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Known: Shipyard, Outfitting, Black Market\nid is not auto inc';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `LuminosityClasses`
    --
    
    DROP TABLE IF EXISTS `LuminosityClasses`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `LuminosityClasses` (
      `id` int(11) NOT NULL,
      `luminosityClass` varchar(3) NOT NULL,
      `description` varchar(50) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='0, Ia, Ib, II, III, IV, V, VI, VII';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ModuleCategories`
    --
    
    DROP TABLE IF EXISTS `ModuleCategories`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ModuleCategories` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Small Hardpoint, ..., Utility Mount, Bulkheads, ...';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ModuleVersions`
    --
    
    DROP TABLE IF EXISTS `ModuleVersions`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ModuleVersions` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Modules`
    --
    
    DROP TABLE IF EXISTS `Modules`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Modules` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Subclasses: WeaponModules';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Prices`
    --
    
    DROP TABLE IF EXISTS `Prices`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Prices` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ref_station` int(11) NOT NULL,
      `ref_commodity` int(11) DEFAULT NULL,
      `buy` mediumint(6) DEFAULT NULL,
      `sell` mediumint(6) DEFAULT NULL,
      `demand` int(11) DEFAULT NULL,
      `supply` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `station_idx` (`ref_station`),
      KEY `commodity_idx` (`ref_commodity`),
      CONSTRAINT `station` FOREIGN KEY (`ref_station`) REFERENCES `Stations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `commodity` FOREIGN KEY (`ref_commodity`) REFERENCES `Commodities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ShipManufacturers`
    --
    
    DROP TABLE IF EXISTS `ShipManufacturers`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ShipManufacturers` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Known: Faulcon DeLacy, Zorgon Petersen, Lakon, Gutamaya, Sau';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ShipRoles`
    --
    
    DROP TABLE IF EXISTS `ShipRoles`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ShipRoles` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Ships`
    --
    
    DROP TABLE IF EXISTS `Ships`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Ships` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      `manufacturer` varchar(45) DEFAULT NULL,
      `price` int(11) DEFAULT NULL,
      `ref_role` int(11) DEFAULT NULL,
      `ref_blastDoorType` int(11) DEFAULT NULL,
      `topSpeed` smallint(5) DEFAULT NULL,
      `boostSpeed` smallint(5) DEFAULT NULL,
      `maneuverability` tinyint(2) DEFAULT NULL,
      `shields` smallint(4) DEFAULT NULL,
      `armour` smallint(4) DEFAULT NULL,
      `hullMass` smallint(4) DEFAULT NULL,
      `cargoCapacity` smallint(4) DEFAULT NULL,
      `cargoMaxCapacity` smallint(4) DEFAULT NULL,
      `jumpRange` float DEFAULT NULL,
      `fuelCapacity` smallint(4) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `role_idx` (`ref_role`),
      KEY `blastDoorType_idx` (`ref_blastDoorType`),
      CONSTRAINT `role` FOREIGN KEY (`ref_role`) REFERENCES `ShipRoles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `blastDoorType` FOREIGN KEY (`ref_blastDoorType`) REFERENCES `BlastDoorTypes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ShipsInternalCompartments`
    --
    
    DROP TABLE IF EXISTS `ShipsInternalCompartments`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ShipsInternalCompartments` (
      `id` int(11) NOT NULL,
      `ref_ship` int(11) NOT NULL,
      `compClass` tinyint(2) NOT NULL,
      `compSlots` tinyint(2) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `ship_idx` (`ref_ship`),
      CONSTRAINT `ship` FOREIGN KEY (`ref_ship`) REFERENCES `Ships` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `ShipsOutfitting`
    --
    
    DROP TABLE IF EXISTS `ShipsOutfitting`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `ShipsOutfitting` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ref_ship` int(11) NOT NULL,
      `ref_moduleCategory` int(11) NOT NULL,
      `amount` tinyint(2) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `ship_idx` (`ref_ship`),
      KEY `moduleCategory_idx` (`ref_moduleCategory`),
      CONSTRAINT `shipRef` FOREIGN KEY (`ref_ship`) REFERENCES `Ships` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `moduleCategory` FOREIGN KEY (`ref_moduleCategory`) REFERENCES `ModuleCategories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `SpectralClasses`
    --
    
    DROP TABLE IF EXISTS `SpectralClasses`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `SpectralClasses` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `spectralClass` char(1) CHARACTER SET latin1 NOT NULL,
      `conventionalColor` varchar(20) CHARACTER SET latin1 NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Oh, Be A Fine Girl, Kiss Me';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `SpectralPeculiarityCodes`
    --
    
    DROP TABLE IF EXISTS `SpectralPeculiarityCodes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `SpectralPeculiarityCodes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `code` varchar(10) NOT NULL,
      `description` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Stars`
    --
    
    DROP TABLE IF EXISTS `Stars`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Stars` (
      `id` int(11) NOT NULL,
      `ref_spectralClass` int(11) DEFAULT NULL,
      `ref_spectralPeculiarityCode` int(11) DEFAULT NULL,
      `ref_luminosityClass` int(11) DEFAULT NULL,
      `ref_system` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `spectralClass` (`ref_spectralClass`),
      KEY `spectralPeculiarityCode` (`ref_spectralPeculiarityCode`),
      KEY `luminosityClass_idx` (`ref_luminosityClass`),
      KEY `system_idx` (`ref_system`),
      CONSTRAINT `system` FOREIGN KEY (`ref_system`) REFERENCES `Systems` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `astroObject` FOREIGN KEY (`id`) REFERENCES `AstroObjects` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT `luminosityClass` FOREIGN KEY (`ref_luminosityClass`) REFERENCES `LuminosityClasses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `spectralClass` FOREIGN KEY (`ref_spectralClass`) REFERENCES `SpectralClasses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `spectralPeculiarityCode` FOREIGN KEY (`ref_spectralPeculiarityCode`) REFERENCES `SpectralPeculiarityCodes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ref_system=not null indicates a primary star';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `StationTypes`
    --
    
    DROP TABLE IF EXISTS `StationTypes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `StationTypes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Stations`
    --
    
    DROP TABLE IF EXISTS `Stations`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Stations` (
      `id` int(11) NOT NULL,
      `ref_type` int(11) DEFAULT NULL,
      `facilityTypesMask` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `ref_type_idx` (`ref_type`),
      CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `AstroObjects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `ref_type` FOREIGN KEY (`ref_type`) REFERENCES `StationTypes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Station is an AstroObject\nfacilityTypesMask is a bit mask po';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `SystemAllegiances`
    --
    
    DROP TABLE IF EXISTS `SystemAllegiances`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `SystemAllegiances` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `SystemEconomies`
    --
    
    DROP TABLE IF EXISTS `SystemEconomies`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `SystemEconomies` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `SystemGovernments`
    --
    
    DROP TABLE IF EXISTS `SystemGovernments`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `SystemGovernments` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `Systems`
    --
    
    DROP TABLE IF EXISTS `Systems`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Systems` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `x` int(10) unsigned NOT NULL DEFAULT '0',
      `y` int(10) unsigned NOT NULL DEFAULT '0',
      `z` int(10) unsigned NOT NULL DEFAULT '0',
      `name` varchar(50) NOT NULL,
      `population` bigint(20) NOT NULL DEFAULT '0',
      `blackMarket` tinyint(1) NOT NULL DEFAULT '0',
      `ref_allegiance` int(11) NOT NULL,
      `ref_economy` int(11) NOT NULL,
      `ref_government` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `allegiance_idx` (`ref_allegiance`),
      KEY `economy_idx` (`ref_economy`),
      KEY `government_idx` (`ref_government`),
      CONSTRAINT `allegiance` FOREIGN KEY (`ref_allegiance`) REFERENCES `SystemAllegiances` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `economy` FOREIGN KEY (`ref_economy`) REFERENCES `SystemEconomies` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `government` FOREIGN KEY (`ref_government`) REFERENCES `SystemGovernments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `WeaponModules`
    --
    
    DROP TABLE IF EXISTS `WeaponModules`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `WeaponModules` (
      `id` int(11) NOT NULL,
      `ref_mount` int(11) DEFAULT NULL,
      `ref_version` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `mountType_idx` (`ref_mount`),
      KEY `version_idx` (`ref_version`),
      CONSTRAINT `module` FOREIGN KEY (`id`) REFERENCES `Modules` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `mountType` FOREIGN KEY (`ref_mount`) REFERENCES `WeaponMountTypes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT `version` FOREIGN KEY (`ref_version`) REFERENCES `WeaponVersions` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `WeaponMountTypes`
    --
    
    DROP TABLE IF EXISTS `WeaponMountTypes`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `WeaponMountTypes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `type` char(2) NOT NULL,
      `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='type="G", name="Gimbaled"';
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Table structure for table `WeaponVersions`
    --
    
    DROP TABLE IF EXISTS `WeaponVersions`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `WeaponVersions` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Low Heat, Focused, Overcharged, ...';
    /*!40101 SET character_set_client = @saved_cs_client */;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2014-12-17 19:30:37

  2. #2
    I very much like the idea of what you are trying to achieve. Why reinvent the wheel over and over again. Lets do it right and then let others use that wheel. Imho the "Elite: Dangerous Fan Creations" forum is NOT the most suitable if you want to get attention/exposure/help. I personally would have created it in the "Gamma Discussion Forum". If you want and choose so you could ask a moderator to move the thread. I'm not a DBA guy so can't really help. Only thing I can do is the following.

    Added thread "[GENERAL] Common Use SQL Game Database Source", section "Miscellaneous" to the OP of the thread "Complete list of all third party tools, spreadsheets, manuals and other data sources"

    I REALLY do hope you get attention and help. Figners crossed.

  3. #3
    Thanks!

    Well, i saw TradeDangerous here too..

    Won't the gamma forum be obsolete after release? But ofc if there's a better place, feel free Mister Moderator.

  4. #4
    Originally Posted by sngerous View Post (Source)
    Thanks!

    Well, i saw TradeDangerous here too..

    Won't the gamma forum be obsolete after release? But ofc if there's a better place, feel free Mister Moderator.
    Kfsone is hiding his tool on purpose here because its a command line tool for advanced users. Others are working on a GUI for it. That was his reason for hiding it here ;-) You probably don't want to hide ;-) Its best to PM a moderator or use the report button, I believe that is for stuff like having threads moved etc.. Otherwise you must be lucky enough that the mods see this thread and your request.

    When Gamma hit the beta forum did become obsolete, they created the Gamma forums then and moved most/all threads to it - or something like that. I guess they can do the same when Release hits us.

  5. #5
    I approve of the initiative regarding standard db scheme. Thread subscribed

  6. #6
    I thought about this myself, good job man!
    It would be nice to have a pulic accessible database to be used by all CMDRs to report prices, stations, locations, equipment etc.
    This brings me to several unsolved questions:


    1. How to prevent trolls entering stupid things? Possible: Check every info several times, like EDSC does with system coordinates. Is it working to have 5 commanders enter commoditie's prices before they get validated? No, I don't think so. Some places are rarely visited, and prices change.
    2. Have the CMDRs who enter the data - who have write acces to the DB - authenticate by password and maybe cookies? This may be practicable, because they who bother updating the data may bother to register and log on. Will not prevent mistakes, but will prevent (most of the) trolls.
    3. What size will the DB have in a year or two? Will your PC at home still be able to handle this? I fear not.
    4. Leads to the location of the DB: Maybe have more than one copy, to keep running if a server goes down for some reason. Anyone got experience with SQL clusters? Not me.
    5. Any way to spread parts of the database over different servers? Could help with the size issue. Right now I have no idea how to do this.


    I got some experience with some different database systems, preferrable MySQL, maybe Postgres. And I can do some PHP programming. I don't have good knowledge of Java & Javascript, HTML and its features, and with database clusters. I do have hands on a server running MySQL, some gigs free capacity, lots of free RAM and CPU resources, good internet connection. I may install Postgres or any other software running under Linux 2.6.32-5-vserver-amd64, if needed.

  7. #7
    Originally Posted by Inhumierer View Post (Source)
    I got some experience with some different database systems, preferrable MySQL, maybe Postgres. And I can do some PHP programming. I don't have good knowledge of Java & Javascript, HTML and its features, and with database clusters. I do have hands on a server running MySQL, some gigs free capacity, lots of free RAM and CPU resources, good internet connection. I may install Postgres or any other software running under Linux 2.6.32-5-vserver-amd64, if needed.
    Well, this is nice! I would very much like to contribute to that.

    If you follow Wolverine's sig you will find some databases around which primarily focus on routing/ trading.


    The problem i have atm with traveling the galaxy is that even if i haven't played much i'm totally lost in space. I don't know where i've come from, even the system's name and i have 20t stolen goods and no idea where i've seen a black market the last time.

    So i want to write some kind of notebook where you usually start from zero. All that i needed - and found in edstarcoordinator.com - was a source for the systems and their relations/distances in order to not needing to type everything into my tablet and to have a minimal navigation.
    But of course, even if i planned a local/offline(yeeha) database, you can push the idea further.


    1. I have seen this solved in edstarcoordinator with a "Confidence Rating", a counter that increases when data has been repeatedly entered equally.

    2. People (including me) usually don't like to register. There could be a big timeout between submits.
    Another idea could be to earn a 'license' or 'lose trust' if you don't match present data repeatedly.

    3. What will grow is the system data:
    10.000 people will each discover
    10 systems a day which is
    3650 a year with
    10k system data makes
    365 TB (actually it's GB, sorry)
    Even if it's so high, you will only download a portion of the system data/ only systems in jump range.

    4. I think that's far beyond where we are now^^

    5. If there are 1000 hardworking people exploring 20 systems each day with system data being like
    - 200 B per astronomical object
    - 1000 B per station (50 commodities * (buy, sell, demand, supply) * 4 (64 bit int) (actually demand supply could be represented with positive/negative)
    - with an average of 5 astronomical objects and 1 station per system, it's
    - 2000 Byte per system

    - 20,000 * 365 * 2KB =
    14.6 TB (GB again) and i think this is still a much too high number.
    Even if the average system's size is higher (could be growing when content is added), there won't be so much explorations a day and far fewer people entering data to the database.


    There's another point about a community database: A direct API from FD. Questions:

    a. What data will be accessible through the API?
    b. Will the API be accessible apart from the game client?
    c. Will the API provide data that is not available in the game?

    Answer c will probably resolve to 'no' because players would be forced to use a specific tool. So the final question is:

    Will a possible official API make a community DB redundant?

    This would be the point about a community online database, but ofc not the data structure itself which has to be modeled by every tool again: You will query the database/api and store it in a structure in memory or cache it locally. That means, having a ready-to-use data structure is at first not a bad idea.


    IF you want to write a web interface/web api ANYWAY then it would pay to look if there are existing systems one could use. E.g. edstarcoordinator stores systems and distances between them and outputs relative coordinates to Sol.
    So if someone wanted to submit a system, that portion of data [system name, neighbour1, neighbor2, ...] could be pushed there and coordinates taken from there. Other existing databases could be used, too.

  8. #8
    It points out that SQLite has a pretty simplyfied infrastructure. As Android is my primary destination and SQLite is the common database there, i will focus on that first.
    SQLite is easily accessible from Python, there are JDBC drivers for Java and according to the PHP manual, an SQLite extension is activated by default.

  9. #9
    Few thoughts:
    Originally Posted by sngerous View Post (Source)
    # Commodities Categories
    # Additional id to match ingame sorting which is not alphabetical.
    CREATE TABLE CommodityCategories(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sortId INT NOT NULL,
    name VARCHAR(50)
    )
    sortId & name, why not make them unique?

    Originally Posted by sngerous View Post (Source)
    # Coordinates are currently not supported directly by the game,
    # only through calculation of distances and a center by definition
    # e.g. Sol [0, 0, 0].
    # Type as bitmask?
    CREATE TABLE Systems(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    x FLOAT NOT NULL DEFAULT '0',
    y FLOAT NOT NULL DEFAULT '0',
    z FLOAT NOT NULL DEFAULT '0',
    name VARCHAR(50) NOT NULL,
    type INT,
    ref_note INT DEFAULT NULL
    )
    Coordinates of the ~ 20k systems in the table shown some weeks ago had a precision of 1/32 Ly. Doing any calculation with coordinates will dramatically speed up if they are stored in big integers, each representing 1/32 Ly. Largest number at a glance is app. 51k, and no negatives. So maybe 64k * 32 = 2M will be enough to store every coordinate. unsigned int max value is 4294967295 or ~ 4 Gig (at least in MySQL), needs 4 bytes in a table. Is for sure large enough to store any coordinate, would be a cube of 128 MLy. Astronoms, what's the size of the milky way?
    An unsigned mediumint will be max 16777215 or 16M or 500 kLy, not sure if it fits. And I don't expect a huge advantage as it is only 25% smaller, and I'm not sure if it's mySQL specific.

  10. #10
    @Inhumierer: Well, i said i wanted SQLite. What do you think?

    SQLite ints are 64 bit. I think it's variable depending on the values' size.

    Milky Way is 120 k diameter.

    'nother edit: even if it's SQLite, a port onto a larger dbms is not that difficult or is it?

  11. #11
    Originally Posted by sngerous View Post (Source)
    10.000 people will each discover
    10 systems a day which is
    3650 a year with
    10k system data makes
    365 TB

    ...

    - 20,000 * 365 * 2KB = 14.6 TB
    Your math is wrong, its GB, not TB.

  12. #12
    Tnx!
    Originally Posted by sngerous View Post (Source)
    If you follow Wolverine's sig you will find some databases around which primarily focus on routing/ trading.
    Yes, I looked at some (most?) of them. All this needs to be centralized.
    Originally Posted by sngerous View Post (Source)
    1. I have seen this solved in edstarcoordinator with a "Confidence Rating", a counter that increases when data has been repeatedly entered equally.
    Yes, I like this idea, regarding the coordinates as they don't change. But this will not work f.e. for market data.
    Originally Posted by sngerous View Post (Source)
    2. People (including me) usually don't like to register. There could be a big timeout between submits.
    OK, I understand and respect this. Registering may be optional. What kind of timeout are you talking about?
    Originally Posted by sngerous View Post (Source)
    Another idea could be to earn a 'license' or 'lose trust' if you don't match present data repeatedly.
    Sure. But how to keep this "reputation" if not registered? Maybe have a unique cookie to identify the browser? Usually ppl don't change their PCs or browsers very frequently, so you can keep your "reputation" while using the same browser. If you switch browsers, you need to start from scratch. Or you optionally register to keep track.
    Originally Posted by sngerous View Post (Source)
    3. What will grow is the system data:
    [...]
    365 TB
    Klonk. Oh, wait. That's a bit too much, at least for the server I'm actually thinking of. We could just give it a try, see how it's growing and (hopefully) have some good ideas before it bangs
    You know this it diagram from a specific problem to a solution, including many steps of planning, doing etc., and in the middle there is a small point "something magic happens here"
    Some tables may be stored on a different server, if there's not much interaction, like "Notes". Don't know if this would reduce the space needed.
    Originally Posted by sngerous View Post (Source)
    4. I think that's far beyond where we are now^^
    I don't think so. If we have 2 or 3 more commanders willing to help out with a powerful server with a good internet connection, this would be worth a shot.

    Originally Posted by sngerous View Post (Source)
    - 1000 B per station (50 commodities * (buy, sell, demand, supply) * 4 (64 bit int) (actually demand supply could be represented with positive/negative)
    Didn't see this before. Why don't use unsigned smallint? What's the max price someone saw? How much do you get for rare commodities? More than you get for Palladium? This is about 15k, highest I saw until today.
    Originally Posted by sngerous View Post (Source)

    - with an average of 5 astronomical objects and 1 station per system, it's
    - 2000 Byte per system

    - 20,000 * 365 * 2KB =
    14.6 TB and i think this is still a much too high number.
    Sorry, you got some zeros too much It should be 14.6 Gigs. For one year? I agree, it will probably be smaller. But anyway, this is a size which is ok for 2 or 3 years.

    Originally Posted by sngerous View Post (Source)
    There's another point about a community database: A direct API from FD. Questions:

    a. What data will be accessible through the API?
    b. Will the API be accessible apart from the game client?
    c. Will the API provide data that is not available in the game?

    Will a possible official API make a community DB redundant?
    a) only FD knows
    b) sure, by definition
    c) unlikely

    Redundant? Depends on the data you can access from the API. I believe the API will reveal actual commodities prices, actual system name, maybe system map, maybe part of the gal map. Maybe 1000 things more of which I even can't dream ATM.
    Originally Posted by sngerous View Post (Source)
    IF you want to write a web interface/web api ANYWAY then it would pay to look if there are existing systems one could use. E.g. edstarcoordinator stores systems and distances between them and outputs relative coordinates to Sol.
    So if someone wanted to submit a system, that portion of data [system name, neighbour1, neighbor2, ...] could be pushed there and coordinates taken from there. Other existing databases could be used, too.
    NAAA! I don't WANT to write a web interface! I want to help the community, and I'd like to help with things I am good at ;-) Like creating and providing a DB (server). If no one would volunteer to write a web frontend, I'd do that. But I am not good at Java, including JSON. Never used that. I'd like to get a read only mysql access to the EDSC DB. And maybe someone to show me how to submit new coordinates to EDSC, f.ex.

  13. #13
    Originally Posted by Elgar View Post (Source)
    Your math is wrong, its GB, not TB.
    Yes shortcut in the head was too short^^

  14. #14
    Originally Posted by sngerous View Post (Source)
    @Inhumierer: Well, i said i wanted SQLite. What do you think?

    SQLite ints are 64 bit. I think it's variable depending on the values' size.

    Milky Way is 120 k diameter.

    'nother edit: even if it's SQLite, a port onto a larger dbms is not that difficult or is it?
    I don't know SQLite. Well, I've heard it many times, but never used it. Will it handle a database size of several TB? What about the performance? It's free, and several huge projects use it, so it can't be bad.
    120kLy: OK, then in an unsigned int vector we can store 1 million milky ways
    And no, I don't think it will be difficult to port it to any common SQL system, as far as we don't use very special features.

  15. #15
    Originally Posted by sngerous View Post (Source)
    CREATE TABLE Planets(
    id INT NOT NULL,
    ref_star INT NOT NULL,

    FOREIGN KEY (id) REFERENCES AstroObjects(id),
    FOREIGN KEY (ref_parentObject) REFERENCES AstroObjects(id)
    )
    [/CODE]
    Ehm, I just fed this to a MySQL server, and if you put semicolons after each statement, it works without error. Exept this table.

    I believe this should be: FOREIGN KEY (ref_star) REFERENCES AstroObjects(id)

    And this is irritating:
    FOREIGN KEY (ref_subObject) REFERENCES SubObjects(id)
    It's a foreign key, that references a column in it's own table. Ehm...
    Anyway I don't understand, and MySQL doesn't, too.

Page 1 of 5 12345 LastLast