Tag: schema

Demystifying the focus_obj2act_entry relation

December 5, 2016 I’ve had multiple people inquire lately about the focus_obj2act_entry relation.It’s definitely a bit of a magic relation, and can be confusing, so I figured it was time to blog about it. Business rule properties allow you to define a value that is determined by traversing through the schema starting from a particular object.For example, the Site Name property for a Case has a path of case_reporter2site:name. So when this rule property gets resolved, the property evaluator knows how to turn that schema path into actual SQL, such as:select name from table_case, table_site where case_reporter2site = table_site.objid and table_case.objid = {The Objid of the Case in Context}Pretty straightforward. focus_obj2act_entry propertiesWhen looking at existing business rule properties, you may notice that some of them start with the relation focus_obj2act_entry.For example, the Logger property uses focus_obj2act_entry:act_entry2user:login_name Notice the "focus_obj2act_entry" part of the path. If you look…

SchemaEditor improvements when dropping columns on Oracle

July 8, 2013 I’ve been recently working with a couple of customers who were dropping database columns from their schema on Oracle, and the drops were taking a long time to complete. In one instance, the customer estimated it would take 50 hours to drop a bunch of columns from their contact table. In another, a customer observed that dropping two columns from table_site_part took over two hours. Dropping 3 columns from table_contact took over 1 hour. These long execution times can disrupt normal operations – especially those environments with limited maintenance windows. Dovetail’s schema editing tool (Schema Editor) and Amdocs schema editing tools (ddcomp, SchemaManager) all do the same basic operations when it comes to dropping columns. Basically they all do this: ALTER TABLE table_name DROP COLUMN column_name; There’s a better way. Set Unused On large tables the process of physically…

SchemaEditor and OneToOne relations : clearing up a bit of ambiguity

October 22, 2012 Using Dovetail SchemaEditor to add a new relation to your schema is super easy. For example, here’s my schema script for adding a new one-to-one relation between the modem table and the user table: What’s nice is that I only have to define the relation once in the schema file, as opposed to other tools (ddcomp, DD Editor, SchemaManager) where I would have to add the relation to the file in two places (as a relation under both the modem table and as a relation under the user table). But, there is a bit of ambiguity here – which side is the primary side (OTOP) and which side is the foreign side (OTOF)? Answer The foreign side will be on the table/name, and the primary side will be inverseTable/inverseRelationName. So in the example above, the modem2user is the OTOF, and…

Automated Deployment for Clarify Example

January 11, 2012 Last year I posted about automated deployments for Clarify. From that post: Doing automatic deployments of Clarify changes is pretty straightforward. If I was starting off, I would use a contemporary build script tool such as nant or rake, and simply have it call out to your schema editing tool (Dovetail SchemaEditor, or ddcomp) to perform schema changes, use an import tool (such as Dovetail ArchiveManager, or dataex) for importing forms, and then compile your Clearbasic code using CBEX, or just import it as a DAT file using Dovetail ArchiveManager. File copying, registering components, configuring web servers, and setting permissions are all common tasks that are available in these build tools as well. Should you also need to do additional stuff in your database, some code that uses the Dovetail SDK can be useful. You could tie this…

Transform a Pseudo Relation into an Exclusive Relation

October 18, 2011 Recently I had the need to use a pseudo relation as part of a path evaluation (i.e. as part of a business rule path). But, the path evaluator can’t do this, because it’s not a “real” relation in the Clarify/Dovetail schema. Pseudo Relations I covered pseudo relations in my recent Schema 201 webinar, but lets give a quick review. A pseudo relation is a sort of schema hack that implements a relation using two fields. One field holds the type_id of the object that we want to relate to, and one field holds the objid of the object. These pseudo relations were a bit of a data modeling hack, before exclusive relations. Pseudo relations are nice, because we don’t have to create a new physical column for a relation, and we can relate to any other object in…

Schema 201 Webinar Follow-up

August 24, 2011 Big Thanks! to all those that attended today’s Schema 201 webinar. We had a great turnout! Slides The slides from today’s presentation are available on Slideshare. Recording The webinar recording is available for playback. Links Schema 101: http://dovetal.es/schema101webinar SQL View example: http://dovetal.es/sqlView SQL View that includes case data, plus a column that indicates “how many open subcases does this case have” More Info on Dovetail SchemaEditor: http://dovetal.es/dtSchema More Info (and free download) for Dovetail BOLT: http://dovetal.es/dtBolt Future Webinars Have an idea for a future webinar? Let’s hear it! You can leave a comment here, or drop me an email at gary@dovetailsoftware.com

Reminder: Clarify / Dovetail Schema 201 webinar this week

August 22, 2011 Just a little reminder that I’ll be presenting a session on advanced schema topics this Wednesday. We already have a good number of registrants, so it seems like a topic that folks are interested in. Cool! Topics SQL Views Many-To-Many Tables Role Objects Pseudo Relations Exclusive Relations Change Fields Closer look at the ADP tables Those are the topics I have planned to cover. If there’s anything else you’d be interested in, please let me know, and I’ll see what I can do. When Wednesday, August 24, 2011 11:00 AM - 12:00 PM (Central Time) Register Register at: https://dovetailsoftwareevents.webex.com/dovetailsoftwareevents/onstage/g.php?t=a&d=669502491

Webinar: Clarify / Dovetail Schema 201

August 1, 2011 In this complimentary webinar, we'll build on the previous Schema 101 session, and dive into more advanced schema topics. We had a great turnout in the first session, and a lot of requests for part 2. Topics SQL Views Many-To-Many Tables Role Objects Pseudo Relations Exclusive Relations Change Fields Closer look at the ADP tables Those are the topics I have planned to cover. If there’s anything else you’d be interested in, please let me know, and I’ll see what I can do. When Wednesday, August 24, 2011 11:00 AM - 12:00 PM (Central Time) Register Register at: https://dovetailsoftwareevents.webex.com/dovetailsoftwareevents/onstage/g.php?t=a&d=669502491

Schema 101 Webinar Follow-up

May 17, 2011 Big Thanks! to all those that attended today’s webinar on Schema 101. Slides The slides from today’s presentation are available on Slideshare. Recording The webinar recording is available for playback. Links More Info on Dovetail SchemaEditor: http://dovetal.es/dtSchema More Info (and free download) for Dovetail BOLT: http://dovetal.es/dtBolt Schema 201 Interested in a more advanced session? I’m considering doing a Schema 201 session, including items such as SQL Views, Database Flags, Change Fields, Pseudo Relations, MTM tables, ADP in more detail, etc. If this is something you’re interested in – please let me know! I’ll probably only do this if there’s enough interest up front. You can leave a comment here, or drop me an email at gary@dovetailsoftware.com

Dovetail SchemaEditor vs. ddcomp – or – why is ddcomp so slow?

May 2, 2011 While doing some prep for my upcoming webinar on Clarify/Dovetail Schema 101, I started doing some closer looking into what SchemaEditor and ddcomp actually do under the hood. We’ve had some good reports from customers on how much faster schema changes get applied when using Dovetail SchemaEditor. I knew it as well, but I didn’t really know why. So I put on my Encyclopedia Brown hat and started my investigation. The scenario My test case was pretty simple: add a new column to the case table. A pretty common scenario. Dovetail SchemaEditor I ran SchemaEditor, adding a new x_test_integer column. I examined the logs emitted by SchemaEditor, as well as the SQL Profiler trace. Here are the changes SchemaEditor made to the database: 1. Add the new column to the metadata (adp_sch_info table) INSERT INTO adp_sch_info (…) VALUES…