Getting topic_id of target into hyperlink using bookmark ref

[DWM]

Hi I am processing a commentary and have created close to 11,000 bookmarks -(each paragraph is numbered) - that sit within its topics. There are many more times as many internal xrefs to the paragraph numbers that I would like to have linked. What I am trying to do is 1. identify the target topic_id # and 2. have this placed into the hyperlink address. To do that I have created a table that maps each of the bookmarks I have created to the relevant topic_id (multiple bookmarks to a single topic in a lot of cases). I have imported this into the module, and am wondering how, or even if its possible, to use SQL's commands to return the topic_id by looking up the bookmark ref in the table I created and have it written into the hyperlink addresses in the data of the content table. Thanks.

[csterg]

Hm, I am afraid this is not possible with plain SQL. You would need to script that: if i understand properly, you would need to load the rtf code of each topic in memory and replace the bookmark placeholder topic id with the actual topic id from the map. Is that correct?

[DWM]

Yes, thanks Costas, that sounds like what needs to happen. Any suggestions as to a scripting tool that I might be able to get that could help with this? The last week has been a steep learning curve, really appreciate the work you are doing. Even more so now.

[csterg]

Hm... unfortunately there is no tool, and to be honest, i can't imagine how a generic tool could do that. I mean as easy as this can be to do if you write the script code yourself (assuming you know some basic scripting/programming) it can be complex to make it generic enough to put in a tool.

Your only option would be to use the REPLACE(x,y,z) sqlite function (see http://www.sqlite.org/lang_corefunc.html) IF and only IF your content is in RTF and the topic placeholder in the rtf is unique indeed and you are certain that the REPLACE function will not replace something else...
Costas

[DWM]

Thanks Costas, so just to confirm that I understand this...

Where x=ABC(123) with y being (123) and where z=DEF - the replace command would return ABCDEF

This would still require a script to get z (topic_id) using y (the bookmark) to get the match from the Bookmark Lookup table I created. Does this make sense?

David

[csterg]

Thanks Costas, so just to confirm that I understand this...

Where x=ABC(123) with y being (123) and where z=DEF - the replace command would return ABCDEF

This is correct.

This would still require a script to get z (topic_id) using y (the bookmark) to get the match from the Bookmark Lookup table I created. Does this make sense?

I am afraid this is correct, yes. So you are back at the start, yet something like that could work:

Code: Select all

  select replace(x, y, 
                   (select bkm_mark from BKM_LOOKUP where topic_id = A.topic_id)
         ) from content A

Notice how the internal select used the topic_id of the current row from the external select
Costas

[DWM]

So off the cuff I'm now wondering if this could work using some kind of joining of the tables on topic_id??

[csterg]

So off the cuff I'm now wondering if this could work using some kind of joining of the tables on topic_id??

Maybe yes, this is what I tried to put together. But I can't tell without having the actual data and schema
Costas

[DWM]

Ok, I'll have a play with it to see what's possible - I'll let you know how it works out - In the mean time I've passed a sample over to a friend who is more familiar with scripting so he may come back with something I can use. Thanks again for giving your time to this.

David