10 réponses à ce sujet

[AmstradHero]

Thanks to the work of Kaldir II and KingEtzel, we have an easy method for getting all the text for a particular dialogue in a easy to use text format.

However, I'm interested in a more general solution - specifically the means to extract all lines assigned to a particular individual.  This is most important when dealing with new companions in a large mod.  These companions (will likely) have interjections across a lot of different conversations.  Has anyone explored or experimented with queries to try and extract all the lines uttered by a particular individual?

e.g. How would I find all the lines uttered by Alistair across all conversations in the game?

Modifié par AmstradHero, 04 décembre 2010 - 03:51 .

[TimelordDC]

In that second query there, couldn't you filter by the speaker?

[Uneedusman]

Hi, Amstrad... take a look at this project... http://social.biowar...m/project/2449/
it should give you all the lines (well most ) from the DA companions

edit: corrected link

Modifié par Uneedusman, 04 décembre 2010 - 04:30 .

[AmstradHero]

Unee: Yes, I'm aware of that awesome project, but this is more for my mod - I want to be able to find all the lines for my companions within it. :-) It would likely also be very useful for other people developing mods that feature interjections.

TimelordDC:
Yes, I initially fiddled a bit, but thought someone else might have come up with something.
I think the following works:

SELECT A1.StringID, A2.SPEAKER, A1.Text
     FROM bw_dragonage_content.dbo.t_StringText A1, bw_dragonage_content.dbo.t_ConversationLine A2
     WHERE A1.StringID = A2.TextStringID AND A2.SPEAKER = 'npc_tag'

I think this is returning all the lines for NPCs in other conversations - it's easy enough to export their conversation using the pre-existing query.
However, I am getting a significant number duplicate lines in this script with the same StringID - my SQL is rusty, so I'm not sure whether there's a change I could make to it in order to eliminate those.

Modifié par AmstradHero, 04 décembre 2010 - 05:32 .

[TimelordDC]

I don't see anything wrong with that query and to make sure, I just ran it with A2.SPEAKER = 'gen00fl_alistair' and I couldn't see any duplicates as I scrolled through the results.

I don't know why you are getting duplicates though - are you running this for an NPC in your mod?

Edit: OK, I tested for a char from my module and I do get duplicates. I wonder if it's because of the check-in process?

Modifié par TimelordDC, 04 décembre 2010 - 05:50 .

[TimelordDC]

Assuming this SQL Server stores ModuleResRefVersionID in increasing order for check-ins, this should give one ID per line (I still got 2 IDs for certain lines but I did some database transfers between machines resulting in different user IDs checking in stuff so that might be the reason for that)

SELECT A1.StringID, convert(varchar(500),A2.SPEAKER), convert(varchar(300),A1.Text), max(A1.ModuleResRefVersionID)
FROM bw_dragonage_content.dbo.t_StringText A1, bw_dragonage_content.dbo.t_ConversationLine A2
WHERE A1.StringID = A2.TextStringID
AND convert(varchar(500),A2.SPEAKER) = 'npc_tag'
group by A1.StringID, convert(varchar(500),A2.SPEAKER), convert(varchar(300),A1.Text)

OT: After working with Oracle databases for so long, the way this thing handles queries and how it's structured/implemented is junk.
Edit: The convert to varchar is required because this thing can't group text fields.

Modifié par TimelordDC, 04 décembre 2010 - 06:23 .

[AmstradHero]

Magic work! Thanks a million for figuring that out! I haven't really dabbled in SQL in years, and so couldn't figure out why I couldn't get a "group by" to work.

Of course, a simple "order by A1.StringID" at the end of the query will help identify any duplicates that might still exist.

I'd say an update to the wiki might be in order - do you want to do the honours, or shall I?

[Uneedusman]

A problem that I ran into when I was building the repository was all the "OWNER" tags. I wasn't sure who the owner was for a particular line unless I knew who actually owned the dialog. Is that an issue for you? If not, how are you dealing with them?

Modifié par Uneedusman, 04 décembre 2010 - 06:56 .

[AmstradHero]

Ideally the character in question would only have a single dialogue outside of interjections - thus all the owner tags will be attributed to that NPC, and that particular dialogue is easily identifiable from going to the creature's blueprint and examining that. Talk triggers can be set to set particular plot flags, so it's possible to enable a plot flag, have the dialogue node set to "once per game" and then use that as a node on the creature's dialogue.

If not, then it just comes down to the modder to keep track of individual conversations that are "owned" by that NPC but not part of their conversation. I don't know how many conversations are "owned" by NPCs that aren't used as part of their conversation in the main campaign, nor the means to start such conversations.

[Uneedusman]

I was trying to get a comprehensive list of the lines of a character regardless of whether the character was tagged as himself or as "OWNER".   For example, because Murdock is tagged as OWNER for his convo, you'll only pull up a few records and miss a bunch of his lines with this query using his name:

SELECT ST.StringID,  CL.Speaker, MAX(CONVERT(nvarchar(800), ST.Text)),MAX(CL.ModuleResRefVersionID), MAX(ST.ModuleResRefVersionID)
    FROM t_ConversationLine AS CL
        INNER JOIN t_StringText AS ST ON ST.StringID = CL.TextStringID AND ST.ModuleResRefVersionID = CL.ModuleResRefVersionID
    WHERE CL.Speaker='arl100cr_murdock'
    GROUP BY ST.StringID, CL.Speaker, CONVERT(nvarchar(800), ST.Text)

You would have to filter for his name and his convo to get a full list for him.  This would have to be done for each character individually.  I was looking for an easier way.

My biggest challenge was trying to find out how to get the dialog owner info from the creature table linked up to the conversation list table. Here's what I came up with as a solution (well, at least it works for me):

SELECT ST.StringID, MAX(CASE CL.Speaker WHEN '' THEN CR.Tag WHEN 'OWNER' THEN CR.Tag ELSE CL.Speaker END), CONVERT(nvarchar(800), ST.Text),MAX(CL.ModuleResRefVersionID), MAX(ST.ModuleResRefVersionID)
    FROM t_ConversationLine AS CL
        INNER JOIN t_StringText AS ST ON ST.StringID = CL.TextStringID AND ST.ModuleResRefVersionID = CL.ModuleResRefVersionID
        LEFT JOIN t_ModuleResRef AS MR ON CL.ModuleResRefVersionID = MR.CurrentModuleResRefVersionID
        LEFT JOIN t_Creature AS CR ON MR.ResRefID = CR.ConversationResRefID
    WHERE  (CASE CL.Speaker WHEN '' THEN CR.Tag WHEN 'OWNER' THEN CR.Tag ELSE CL.Speaker END)= 'arl100cr_murdock'
    GROUP BY ST.StringID, CONVERT(nvarchar(800), ST.Text)

For my needs, I comment out the "WHERE" clause and get a full list.  Anyway, hope this helps someone down the line.

[Yara C.]

Uneedusman wrote...
 Anyway, hope this helps someone down the line.

Great. Thanks for sharing, Uneedusman. You saved me the time.
I had exactly this on my to-do-list for tomorrow. Just installed SQL Express.