Taxonomy & Term IDs wrong after Import

Nov 5, 2012 at 7:49 PM

I posted on the Orchard forum, but thought I'd post here too in case a different user base frequents this forum. See the original post here: http://orchard.codeplex.com/discussions/401970

You can visit the post to see more details, but here's the crux of the issue:

I'm migrating data from one Orchard instance to another. I've exported everything. When I import my Taxonomies (and later my ContentTypes and Queries which have taxonomy parts/filters attached), the IDs for the corresponding Taxonomies and Terms are mismatched or don't exist at all in the new instance.  

If anyone has any ideas on how to ensure that the objects are tied together properly, I'd love to hear your thoughts. I'm at a loss, and this is preventing me from deploying.

Nov 5, 2012 at 9:14 PM

Out of curiosity can you post a snippet of the recipe you are trying to import? Are you importing the Taxonomies and Terms from the source system to the target one first? It sounds like you're saying that when you do that the Identifiers aren't the same once they are imported? 

Nov 5, 2012 at 9:15 PM

And are you able to reproduce the issue by importing into a fresh Orchard install? 

Nov 5, 2012 at 9:31 PM

Sure, this is from my Query export - data section:

    <Query Id="/Identifier=e1ea144b675b4b8c9568c0e85e9486d6" Status="Published">
      <TitlePart Title="Alternate Site Testing Query" />
      <QueryPart>
        <FilterGroups>
          <FilterGroup>
            <Filter Category="Content" Description="" Position="0" State="&lt;Form&gt;&lt;Description&gt;&lt;/Description&gt;&lt;ContentTypes&gt;ResponseTopic&lt;/ContentTypes&gt;&lt;__RequestVerificationToken&gt;6SuA9XglNs/xm0kX7rKDrmx8irp1OBf3vSZ7PQlzgOz3qt7BWNFmeuUEj9Y44kfbJalQwTxtmbtwwckl5kRy8+HU5xERoQRDkFtxLNtfr5nJTAx56kAgLSLxFjAPRDD1LlwOvzkA2Lh1AY8O//jT1p/Sh+J4cv4F5Cfnf1wGrni9A2lPRadBjh9Zka0h/OeBXQuL+Lxm0oVGpMLyu9Jj++Bwrq/dN6YDPWZgdDJrwxM=&lt;/__RequestVerificationToken&gt;&lt;/Form&gt;" Type="ContentTypes" />
            <Filter Category="Taxonomy" Description="" Position="1" State="&lt;Form&gt;&lt;Description&gt;&lt;/Description&gt;&lt;TermIds&gt;383&lt;/TermIds&gt;&lt;__RequestVerificationToken&gt;40TdUxRZVif9aPWOSV7Kfx2sdfivkE5bxYp4twVtG4csNCuSbkzJMnT7xiSwnfdJtvvvKg///5dbZZaNK3eYv0uLzr9fNUD4s1744Z1xD6+NAI4iOxm3r3SdqWT7DfOHCAPGVxMAu/4mF8ZBD/kCRPpgt8SrMaHHoLlJ6WrP9n8oDFDZuv5EVb48h6Rkx1Agp7HA3CL4oaYmZcqcg59sfHZ6lTlMLopi0P4QFzAIVLM=&lt;/__RequestVerificationToken&gt;&lt;/Form&gt;" Type="HasTerms" />
          </FilterGroup>
        </FilterGroups>
        <SortCriteria />
        <Layouts>
          <Layout Category="Html" Description="" State="&lt;Form&gt;&lt;QueryId&gt;385&lt;/QueryId&gt;&lt;Category&gt;Html&lt;/Category&gt;&lt;Type&gt;List&lt;/Type&gt;&lt;Description&gt;&lt;/Description&gt;&lt;Display&gt;0&lt;/Display&gt;&lt;DisplayType&gt;Summary&lt;/DisplayType&gt;&lt;Order&gt;unordered&lt;/Order&gt;&lt;ListId&gt;&lt;/ListId&gt;&lt;ListClass&gt;alternate-site-glucose-testing-list&lt;/ListClass&gt;&lt;ItemClass&gt;alternate-site-glucose-testing-item&lt;/ItemClass&gt;&lt;__RequestVerificationToken&gt;2CF/2INp0EpkqsjpRBBWtEMUzg7wh4j/ylUrGD0EM7hwRf80pSFziASps+NpoC+027VDLs1Bzf2tEcWC87N2C++y3b0I61Qu9L3O9O64EVhilw0rr4mU6leofI1AfgrGMx85IvcD8Itp8zIuHnpOVMPR29cPgzPz/yaL+Fj2fZaqHMzC2bAfbTGKJAcEtcih8T1/y8GVKPmQVoUAOZyia3EjoPzf9LCVlzScJ81coaI=&lt;/__RequestVerificationToken&gt;&lt;/Form&gt;" Display="0" DisplayType="Summary" Type="List">
            <Properties />
            <Group />
          </Layout>
        </Layouts>
      </QueryPart>
      <IdentityPart Identifier="e1ea144b675b4b8c9568c0e85e9486d6" />
    </Query>

The part I'm seeing issue with is the FilterGroup > Filter Category Taxonomy > TermIds. In this example, the TermIds XML block is 383. This is stored as XML in Orchard_Projections_FilterRecord. TermId 383 relates to Contrib_Taxonomies_TermPartRecord Id of 383. However, when I import my Taxonomies into a new Orchard install, the TermPartRecord Ids are reseeded and what was once 383 is now.... 21 or 25. My Query Filter with TermId 383 now maps to nothing because TermPartRecord 383 is not a valid record. 

See?

Nov 5, 2012 at 9:31 PM
Edited Nov 5, 2012 at 9:34 PM

I'm able to repro in a fresh Orchard install - that's basically what I'm trying to do - deploy to a new environment by using the Export module without copy/pasting the entire DB.

I'm probably missing something simple to make the Query Filter values get updated, but I'm just not seeing it. If you have any ideas, I'd love to hear your thoughts.

Nov 5, 2012 at 9:38 PM

For Reference, I'm using Orchard 1.5.1 and Contrib.Taxonomies 1.4 (from the Gallery).

Nov 5, 2012 at 9:53 PM

I see. This looks like a bug in projections import/export. I think it should be exporting the identifier (if one exists) for each of the terms, not the database Id. I don't use the taxonomies module (i tried it for a couple of weeks in the past) so I'm not sure if that's possible. It looks like you already understand the issue. 

Let's see how we can workaround the issue for you... What I've done in similar situations is write some custom "for xml" queries in the source database that output xml in the same format as Orchard's recipes. YOu can then control exactly how the id's will come out in the recipes. One way to do it is to import the terms into the target install, and find out the new database id's. Then create a junction table (can be a temp table) in the source db that maps the TermId's between the two installs, and then you select the data out as xml, outputting the TermId's from the target install. 

Sorry if this isn't much help. Obviously the more appropriate way to handle this is to fix the underlying issue, but I'm just offering a workaround in case you aren't able to dig into the code to solve this import/export problem. 

Nov 5, 2012 at 9:56 PM

You understand the issue perfectly. And yep - I was just now working out *how* the data maps... doesn't look like you can use an alias or anything as it is, but if you have one of those "for xml" queries, I can take the mapping knowledge I have now and do exactly what you described: import the taxonomies into the target, then use the "for xml" query to remap the values and write the XML with the correct target Ids.

Great solution! How can I get this query from you?

Nov 5, 2012 at 10:23 PM
Edited Nov 5, 2012 at 10:44 PM

Sure, here's an example of a query I used to populate my site from a custom table (the source tables here are not part of Orchard, they are from a pre-existing system, and I added some stuff to them, for example, an Identifier column (guid type) to support the unicity that Orchard recipes require: 

 

with f as (
	select 
		rl.*
		, re.entity_name as rankings_entity_name
		, 'rankings/' + dbo.Slugify(rl.title) as slug
	from 
		tbRankingList rl
		join tbRankingsEntity re
			on rl.rankings_entity_id = re.rankings_entity_id 
), s as (
	select 
		rl.ranking_list_id
		, rls.rank_order
		, rls.institutionID
		, rls.unitID
	from 
		tbRankingList rl
		left join tbRankingListSchools rls 
			on rl.ranking_list_id = rls.ranking_list_id
), c as (
	select distinct 
		rlc.ranking_list_id, rlc.concentration_id, l.mergeto_program_concentration_id as program_concentration_id
	from 
		tbRankingListConcentrations rlc 
		join tbRankingList rl 
			on rl.ranking_list_id = rlc.ranking_list_id
		join tbProgramConcentrations_xref_legacy_mapping l
			on rlc.concentration_id = l.program_concentration_id
)
select 
	'/Identifier='+ identifier+'/alias=' +replace(slug,'/','\/')	as '@Id'
	-- RankingListPart
	, title					as 'RankingListPart/@Title'
	, sub_title				as 'RankingListPart/@SubTitle'
	, rankings_entity_name	as 'RankingListPart/@RankingsEntity_Name'
	, Url					as 'RankingListPart/@Url'
	-- IdentityPart
	, identifier as 'IdentityPart/@Identifier'
	-- CommonPart
	, '/User.UserName=admin' as 'CommonPart/@Owner'
	, '2012-02-29T20:48:40Z' as 'CommonPart/@CreatedUtc'
	, '2012-02-29T20:48:40Z' as 'CommonPart/@PublishedUtc'
	, '2012-02-29T20:48:40Z' as 'CommonPart/@ModifiedUtc'
	-- AutoroutePart
	, slug					as 'AutoroutePart/@Alias'	
	, 'false'				as 'AutoroutePart/@UseCustomPattern'
	-- TitlePart
	, title					as 'TitlePart/@Title'
	-- // Schools
	, (
		select  
			rank_order				as '@SequenceNr'
			, institutionID			as '@InstitutionId'
			, unitID				as '@UnitId'
		from 
			s 
		where 
			s.ranking_list_id = f.ranking_list_id
		for xml path('School'), type
	) as 'Schools'
from 
	f 
group by 
	f.ranking_list_id, identifier, title, sub_title, rankings_entity_name, url, slug
for xml path ('RankingList')
go

 

This example includes some nesting so you can use that for your example. Here is some sample output from the above query: 

 

<RankingList Id="/Identifier=512cf1bcfdab49a1a0357cfc8017a476/alias=rankings\/top-10-fashion-schools">
  <RankingListPart Title="Top 10 Schools For A Career In Fashion" SubTitle="Career in Fashion" RankingsEntity_Name="Fashion.com (fake)" Url="fasion.com/fashion-school-rankings"/>
  <IdentityPart Identifier="512cf1bcfdab49a1a0357cfc8017a476"/>
  <CommonPart Owner="/User.UserName=admin" CreatedUtc="2012-02-29T20:48:40Z" PublishedUtc="2012-02-29T20:48:40Z" ModifiedUtc="2012-02-29T20:48:40Z"/>
  <AutoroutePart Alias="rankings/top-10-fashion-schools" UseCustomPattern="false"/>
  <TitlePart Title="Top 10 Fashion Schools"/>
  <Schools>
    <School SequenceNr="1" InstitutionId="1234567"/>
    <School SequenceNr="4" InstitutionId="2345678"/>
  </Schools>
</RankingList>

 

I've been working on some blog posts to show examples like this of how to transport massive amounts of content from existing databases into a new Orchard site. 

Nov 5, 2012 at 10:25 PM

Looks like this will work perfectly. Might take me a few hours, but I'll let you know how it turns out. Thanks!

Nov 5, 2012 at 10:28 PM

Are you sure it wouldn't be easier to redefine the projections via the Orchard dashboard in the new system? Just make sure you aren't missing some other solution that might be tedious but faster overall. Good luck, let us know how it goes!

Nov 5, 2012 at 10:35 PM

Actually, I had thought of that as a possible solution, but the problem arises when the Id the query is looking for doesn't exist (383). In that case, instead of being taken to the Admin Query Edit screen, I receive the "Oops. Something went wrong ... sorry" message with this error:

An unhandled exception has occurred and the request was terminated. Please refresh the page. If the error persists, go back
Object reference not set to an instance of an object.
System.NullReferenceException: Object reference not set to an instance of an object. at 
Contrib.Taxonomies.Projections.TermsFilter.<DisplayFilter>b__19(String x) at
System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext() at
System.String.Join(String separator, IEnumerable`1 values) at
Contrib.Taxonomies.Projections.TermsFilter.DisplayFilter(Object context)...

So, transfiguring the data before importing will probably be the best option because it can be semi-automated. The person who deploys the site won't necessarily know how to "fix" the projections/queries in the site. Good thought, though. =)

Nov 6, 2012 at 3:18 AM

Orchard bug logged: http://orchard.codeplex.com/workitem/19224