Home
TeamSite
Primary Keys with Data Deploy
rajpreet
I am using DAS to Data Deploy DCR's on save/edit events. I am using DCR's with User Defined Schema , where in I have normalized tables to have multiple parent-child tables. I need to generate Primary Keys for these tables and am plannin to do so using the IWTupleProcessor Interface implementation. However, I am not sure how the generated primary key should be refreshed back in the DCR for further updates to the DCR.
If you have come across this before , please reply with the approach you followed.
Thanks
Find more posts tagged with
Comments
Michael
The general approach I have seen is to generate the ID in DCR before it is saved -- using either an inline script, cgi callout or formAPI (or a combination).
As an example you could have a inline script which queries your database to retrieve a new sequence number. It could return this as a default value to a field you then may choose to hide using formAPI.
Generating an ID during deployment could be problematic...
Hope this helps
Cheers
Michael
Dwayne
We're doing something very similar on my current project, except that we're not writing anything back to the source file. Instead, we're querying one of the tables that DD is managing, and, if it finds a record corresponding to this file, the TupleFilter returns the corresponding ID. Otherwise, it generates a new one.
It sounds like you might not be storing the path information at all in your database, which would mean that you can't directly query the table to find the key value(s). One possible solutions (probably not supported) would be to query the IWDELTRACKER table, which DD uses internally to map paths to the key values you're using. Or, if you can modify your schema a bit, create a new root-group with just the path info and the document key. Then make your current root-group a child of this new table.
--
Current project: TS 5.5.2/6.1 W2K
Michael
Hi Dwayne
Just out of interest -- how do you handle the situation where the user moves an existing file after it has been stored in the database and then deploys it from the new location? How do you avoid having two records for the same DCR?
Cheers
Michael
Dwayne
By not allowing users to move files . ;-)
In TeamSite, a rename (move) of a file is treated just like a copy followed by a delete. So if a file is renamed, the new document will get a new ID, and the old one will be deleted from the database.
If that's a problem for you, then you probably can't use this approach.
--
Current project: TS 5.5.2/6.1 W2K
Migrateduser
we use an inline call to an oracle sequence which generates a unique id for our dcrs.
however, users wanted to smooosh our web site advertisements (which were managed 1 dcr per ad) into 1 dcr managing all the site ads. this caused us numerous problems with primary keys. so i ended up using random # generators to generate unique ids per each replicant in the form (we have about 30). it was the only way i could get it to work.
Dwayne
Couldn't you use the replicant-order column (that's not what it's called, but it's something like that) in cobination with a per-dcr ID to make a unique key? If I remember correctly, Oracle's sequences can be set to increment by values other than 1. If you increment by, say, 50, then you've got room for 50 replicants with unique IDs. Or make a composite key consisting of both columns.
--
Current project: TS 5.5.2/6.1 W2K
Migrateduser
ok my fix didnt work...freakin DAS keeps trying to reinsert columns that already exist in my table and keeps erroring out. other rows do a "DoesRowExist:" check before, but for some reason this one doesnt. its a nested replicant, is that possibly the problem? i am using a unique id plus replicant order to generate a composite key
it doesnt seem to be checking if the column already exists
INSERT INTO IWT_1604E4B4DF8C(Path,ad_loc_id,rep_order,ad_type_id,audience_category,audience_firm,ad_target_web_ad,ad_target_web_ad_right,which_homepage,which_homepage_pos,publish_date,expiration_date,content_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
Column: Path, field: path, Index: 1,Converting 'templatedata\common\advertisement\data\CN_SueEmAds_20206_31301.xml' to VARCHAR
Column: ad_loc_id, field: ad_type/12/web_ad/0/ad_loc/0/ad_loc_id, Index: 2,Converting '67010459' to DECIMAL
Column: rep_order, literal: 16, Index: 3,Converting '9' to DECIMAL
Column: ad_type_id, field: ad_type/12/web_ad/0/ad_type_id, Index: 4,Converting '93393347' to DECIMAL
Column: audience_category, field: ad_type/12/web_ad/0/ad_loc/0/audience_category, Index: 5,Converting 'profbankbroker, profbroker, profplanner, profindbroker, individual_investor, proftrust, unregistered_webuser' to VARCHAR
Column: audience_firm, field: ad_type/12/web_ad/0/ad_loc/0/audience_firm, Index: 6,Converting 'hibernia, huntington_national, manufacturer_traders, riggs, us_trust, wesbanco' to VARCHAR
Column: ad_target_web_ad, field: ad_type/12/web_ad/0/ad_loc/0/ad_target_web_ad, Index: 7,Converting '/ffw/funds/fundoverview.do?basketId=11783&fundShareId=8085' to VARCHAR
Column: ad_target_web_ad_right, field: ad_type/12/web_ad/0/ad_loc/0/ad_target_web_ad_right, Index: 8,Converting '' to VARCHAR
Column: which_homepage, field: ad_type/12/web_ad/0/ad_loc/0/which_homepage, Index: 9,Converting 'Investor Education' to VARCHAR
Column: which_homepage_pos, field: ad_type/12/web_ad/0/ad_loc/0/which_homepage_pos, Index: 10,Converting 'Top - Right Column' to VARCHAR
Column: publish_date, field: ad_type/12/web_ad/0/ad_loc/0/publish_date, Index: 11,Converting '10-07-2004' to TIMESTAMP
Column: expiration_date, field: ad_type/12/web_ad/0/ad_loc/0/expiration_date, Index: 12,Converting '11-30-2005' to TIMESTAMP
Column: content_id, field: content_id, Index: 13,Converting '20206' to DECIMAL
*******************************************************
SQLException occured in TDbSchemaGroupCfg
Exception Message: ORA-00001: unique constraint (METADATA.IWT_1604E4B4DF8C_KEY) violated
Vendor Error Code: 1
SQL state: 23000
*******************************************************
*******STACK TRACE*************
java.sql.SQLException: ORA-00001: unique constraint (METADATA.IWT_1604E4B4DF8C_KEY) violated
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:217)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1426)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:843)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1694)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1619)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2137)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:404)
at com.interwoven.dd100.dd.TDbSchemaUpdateHelper.InsertOneRow(TDbSchemaUpdateHelper.java:1106)
at com.interwoven.dd100.dd.TDbSchemaUpdateHelper.DoUpdate(TDbSchemaUpdateHelper.java:689)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.DoRealUpdate(TDbSchemaGroupCfg.java:800)
at com.interwoven.dd100.dd.TDbSchemaCfg.RealUpdateWithGroupTree(TDbSchemaCfg.java:942)
at com.interwoven.dd100.dd.TDbSchemaCfg.RealUpdate(TDbSchemaCfg.java:922)
at com.interwoven.dd100.dd.TDbSchemaSynchronizer.DoMod(TDbSchemaSynchronizer.java:601)
at com.interwoven.dd100.dd.TDbSchemaSynchronizer.WriteTupleInternal(TDbSchemaSynchronizer.java:238)
at com.interwoven.dd100.dd.TDbSchemaSynchronizer.WriteTuple(TDbSchemaSynchronizer.java:178)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumerInternal(TConsumerManager.java:398)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumers(TConsumerManager.java:387)
at com.interwoven.dd100.dd.TAgentClient.DoOneTeamSiteSource(TAgentClient.java:961)
at com.interwoven.dd100.dd.TAgentClient.ProcessOneFileList(TAgentClient.java:569)
at com.interwoven.dd100.dd.TAgentClient.ProcessFileList(TAgentClient.java:529)
at com.interwoven.dd100.dd.TAgentClient.DoTeamSiteSources(TAgentClient.java:474)
at com.interwoven.dd100.dd.TAgentClient.DoOneDeployment(TAgentClient.java:256)
at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:188)
at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:487)
at com.interwoven.dd100.dd.TWorkItem.DoIt(TWorkItem.java:149)
at com.interwoven.dd100.dd.TLonelyWorker.run(TLonelyWorker.java:32)
at java.lang.Thread.run(Thread.java:484)
ERROR
oRealUpdate() failed for group [ad_loc]. ERROR:TSynchronizer.DoMod failed at dbSchema.RealUpdate ERROR:WriteTuple failure to consumer[0]
ERROR:Unable to process TeamSite source templatedata/common/advertisement/data/CN_SueEmAds_20206_31301.xml Modified, obtained from filelist=C:\DOCUME~1\wsplocal\LOCALS~1\Temp\ddt47495.tmp
Ottawa_IWOV
Is there any way to get away from using a composite key that include the replicant id value? Can the replicant id be the primary key with another unique constraint enabled on the table? Just a question ...
