1. We have added a Gift Upgrades feature that allows you to gift an account upgrade to another member, just in time for the holiday season. You can see the gift option when going to the Account Upgrades screen, or on any user profile screen.
    Dismiss Notice

[BNW] Problems inserting text into Language_en_US using SQL

Discussion in 'Civ5 - Creation & Customization' started by lordplane, Jul 26, 2018.

  1. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    So I wanted to add new diplomacy texts to my custom civ and for some reason, the text themselves won't show up. I have already successfully inserted text using SQL for buildings and things and after a check of Civ5DebugDatabase.db it would seem all the Response types got inserted correctly (for some reason the program I was using didn't recognise Language_en_US as a table, so I can't query it and see if anything on the problem file was actually inserted.)

    If anyone can enlighten me on what is wrong with my SQL syntax, I would be so grateful as currently this is the only thing that is stopping me from releasing the mod.

    Here's the beginning of the problem file, I know something is wrong here, because the first meeting response texts wouldn't up when meeting the civ for the first time, the rest of the file follows the same format.
    Code:
    INSERT INTO Language_en_US
    (Tag,                                                                            Text                        ) VALUES
    --## First Greeting Message from Misaki Akeno ##--
    ('TXT_KEY_LEADER_MISAKI_AKENO_FIRSTGREETING_1',                                    'Oh hi there! Welcome aboard! I''m Misaki Akeno, the ship''s captain, but everyone calls me Mike-chan, so feel free to do so as well!'),
    ('TXT_KEY_LEADER_MISAKI_AKENO_FIRSTGREETING_2',                                    'Nice to meet you. My name is Misaki Akeno. Welcome aboard to our ship! It isn''t much but make yourself at home.'),
    ('TXT_KEY_LEADER_MISAKI_AKENO_FIRSTGREETING_3',                                    'Hello to you too, my name is Misaki Akeno. It''s a pleasure to have you onboard, hopefully we can become friends!'),
    
    ....
    I checked that the ending of the file has a semi colon and there's no other misplaced semi colon anywhere else in the file.

    Here's the beginning of another file, which in my opinion is practically the same as the beginning of the problem one,
    Code:
    INSERT INTO Language_en_US
    (Tag,                                Text                        ) VALUES
    ('TXT_KEY_HAIFURI_CITY_NAME_01',    'Tokyo Floating City'),
    ('TXT_KEY_HAIFURI_CITY_NAME_02',    'Kouzushima'),
    ('TXT_KEY_HAIFURI_CITY_NAME_03',    'Kobe'),
    ('TXT_KEY_HAIFURI_CITY_NAME_04',    'Hachijou'),
    ('TXT_KEY_HAIFURI_CITY_NAME_05',    'Manazuru'),
    ('TXT_KEY_HAIFURI_CITY_NAME_06',    'Tosashimizu'),
    ('TXT_KEY_HAIFURI_CITY_NAME_07',    'Futaba'),
    ('TXT_KEY_HAIFURI_CITY_NAME_08',    'Nishio'),
    ('TXT_KEY_HAIFURI_CITY_NAME_09',    'Oarai'),
    ('TXT_KEY_HAIFURI_CITY_NAME_10',    'Fukuyama'),
    ('TXT_KEY_HAIFURI_CITY_NAME_11',    'Kosai'),
    ('TXT_KEY_HAIFURI_CITY_NAME_12',    'Hiranai'),
    ('TXT_KEY_HAIFURI_CITY_NAME_13',    'Akita'),
    ('TXT_KEY_HAIFURI_CITY_NAME_14',    'Iki'),
    ('TXT_KEY_HAIFURI_CITY_NAME_15',    'Misawa'),
    ('TXT_KEY_HAIFURI_CITY_NAME_16',    'Ito'),
    ('TXT_KEY_HAIFURI_CITY_NAME_17',    'Okayama'),
    ('TXT_KEY_HAIFURI_CITY_NAME_18',    'Amagi'),
    ('TXT_KEY_HAIFURI_CITY_NAME_19',    'Ise'),
    ('TXT_KEY_HAIFURI_CITY_NAME_20',    'Hyuga'),
    ('TXT_KEY_HAIFURI_CITY_NAME_21',    'Nagasaki'),
    ('TXT_KEY_HAIFURI_CITY_NAME_22',    'Jogashima'),
    ('TXT_KEY_HAIFURI_CITY_NAME_23',    'Arida'),
    ('TXT_KEY_HAIFURI_CITY_NAME_24',    'Minamichita'),
    ('TXT_KEY_HAIFURI_CITY_NAME_25',    'Noshiro'),
    ('TXT_KEY_HAIFURI_CITY_NAME_26',    'Hamada'),
    ('TXT_KEY_HAIFURI_CITY_NAME_27',    'Natori'),
    ('TXT_KEY_HAIFURI_CITY_NAME_28',    'Karatsu'),
    ('TXT_KEY_HAIFURI_CITY_NAME_29',    'Nakadomari'),
    ('TXT_KEY_HAIFURI_CITY_NAME_30',    'Iwanuma'),
    ('TXT_KEY_HAIFURI_CITY_NAME_31',    'Kisarazu'),
    ('TXT_KEY_HAIFURI_CITY_NAME_32',    'Aomori'),
    ('TXT_KEY_HAIFURI_CITY_NAME_33',    'Matsuyama'),
    ('TXT_KEY_HAIFURI_CITY_NAME_34',    'Minamiise'),
    ('TXT_KEY_HAIFURI_CITY_NAME_35',    'Oga'); 
    But this one works.
     
  2. Ryika

    Ryika Lazy Wannabe Artista

    Joined:
    Aug 30, 2013
    Messages:
    9,395
    The part of the code that you've posted looks fine (as long as it ends with a ; and not a , in the last line).
    Are you sure the file is loaded? Did you actually add the Diplomacy_Responses that are required for custom dialog to work?

    Overall, enable logging and see what the Database.log tells you about your files.
     
  3. Artisanix

    Artisanix Warlord

    Joined:
    Oct 6, 2013
    Messages:
    104
    Because technically Language_en_US is not a table but a view. Table has static structure, a view is just saved sql query, executed every time you try to access it. The base table where everything is stored is LocalizedText, which has additional column Language. And Language_en_US is just a view which filters only en_US records from the base table, and omits Language column for display.

    And one important info, just in case: Localization texts are stored in Localization-Merged.db file, not in Civ5DebugDatabase.db.




    Anyway, the work flow if something is wrong with your query is like this:

    1)
    make sure your sql file updates database on mod activation

    2)
    - Open Localization-Merged.db file in some sqlite viewing/editing app (I recommend SQLiteSpy for simplicity)
    - Copy all the query to clipboard, and paste to that app, and execute directly there - if you have an error with the syntax, the app will inform you about this. Then search for typo, fix it, and check whether records from your query have been inserted in LocalizedText table - they should be listed at the end.

    3)
    If the outcome of activities from pt. 2) is successful, then it may be something related to the way how the game processes this.
    - Enable logging if you haven't done so yet,
    - To start fresh, before starting the game delete everything in Civ5\Logs folder
    - Run the game, select only your mod, and allow game to process it, and on that screen where the game lists the mods processed, switch alt-tab out of it
    - open Database.log file and search if any errors related to your mod are there
    - open Localization-Merged.db is SQLiteSpy or other app, and search if your data are there.
     
    Last edited: Jul 27, 2018
    lordplane likes this.
  4. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    Yes the Diplomacy_Responses table has all the responses loaded for my civ, they are all present when I checked the Civ5DebugDatabase.db

    Thank you for the heads up on the Localization-Merged.db , otherwise I would have never know how to check if the texts have actually been loaded or not.
    I follow your steps and the texts are not present in the Localization-Merged.db, I'm using SQLite Manager addon for firefox for the querying.

    The Database log:
    Code:
    ------------------------------
    [6840.862] Database::XMLSerializer (edited_animations/civ5artdefines_viseffects.xml): 'Row' or 'Delete' expected, got 'Name'.
    [6840.862] columns Language, Tag are not unique
    [6840.862] columns Language, Tag are not unique
    [6841.657] Validating Foreign Key Constraints...
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6841.657] Invalid Reference on ArtDefine_Landmarks.LayoutHandler - "SPECIAL" does not exist in ArtDefine_LandmarkTypes
    [6844.013] Failed Validation.
    [6844.419] 
    -- SQLite Memory Statistics --
    Memory Usage:
            [Cur]        [Max]
    Malloc:        6716768        75899608
    PageCache:    8        14
    LookAside:    0        0
    Scratch:    0        1
    
    Static Buffer Overflows:
            [TooLarge]    [NoSpace]
    PageCache:    6253968        62971272
    Scratch:    0        0
    
    Largest Allocations:
    Malloc:        262144
    PageCache:    1172
    Scratch:    6640
    
    Prepared Statements:
    Current:        14
    ------------------------------
    
    
    And when I tried to add the text to the database via SQLite Manager, the error I get is
    Code:
    UNIQUE constraint failed: LocalizedText.Language, LocalizedText.Tag
     
  5. Artisanix

    Artisanix Warlord

    Joined:
    Oct 6, 2013
    Messages:
    104
    The Tag and Language columns are set in such a way that they only accept unique combination of values.
    And above error means the data you're trying to insert already exist in database. So one of the Tag fields you're trying to insert already is present in database, and that restriction doesn't allow to insert it once again.

    The point is, for vanilla, any DLC, and every mod out there, there can't be more inserts than one with the same values in Tag/Language fields.
     
    Last edited: Jul 27, 2018
  6. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    Ah okay, thank you. I shall now go and check through a few hundred lines of text :crazyeye:, thanks for the clarification!
    If I cannot solve it I'll come back, but otherwise thanks again for the hint on the databases!
     
  7. Artisanix

    Artisanix Warlord

    Joined:
    Oct 6, 2013
    Messages:
    104
    The similar case here, though don't know whether it affects your mod.
    In that XML file two rows that tried to insert data don't have unique Tags, and the other error is somewhere else in the structure, the game expects Row or Delete tag, but Name is used, which is not correct value. It may also mean that <Row> tag is just missing.
     
    Last edited: Jul 27, 2018
  8. lordplane

    lordplane Chieftain

    Joined:
    Jul 8, 2016
    Messages:
    22
    I see, thanks for letting me know, the problem has been resolved!
    Thanks again!
     

Share This Page