|
|
You're in the
sql server notification services
group:To muck around in tables, or not
sql server notification services:
found is in the tools available to integrate it with existing applications. Specifically, the XML and dynamic table approach is great for creating applications, subscriptions, and events... but there's no real way to manage those in an application-centric way once the whole show is on the road. To use a specific example, I'm working on a notification app for a classic ASP based forum that's derived from the popular Snitz forum package. So it's natural to have a "new post" event with the fields "Forum_ID" and "Topic_ID", among others. Users who've subscribed to notifications for new posts in a particular forum get matched with a rule that checks Forum_ID against their subscription parameter. So far, so good. That works beautifully. Now, what happens if we delete a forum? Certainly all subscriptions that reference the forum should go, too. So far, I haven't found a way to do that cleanly through the NS interface other than literally iterating through every subscription in the entire application looking for GetFieldValue("Forum_ID"). In my application, that's brutal. First, there will be tens to hundreds of thousands of subscriptions, so it's like a table scan, but worse since it's done in the app. Worse, that process needs to be done synchronously in the app with the delete, so the app's going to sit there and be unresponsive for a while. Now, I can cheat and access the tables directly and do the job more or less instantly (since I defined an index on the Forum_ID column of the subscription class) by just doing a "delete from InstanceApp.dbo.NSSubClassSubscriptions where forum_id=XX" -- it's a lot less code for me to write, and the performance will be dramatically better. Of course, it's breaking the rules. Anyways, I'd love to hear if I'm missing something here (the classic two-line response to an 80 line message). If I'm not missing something, I'd just like for the issue to be noted, and more utility SP's provided in future versions that allow for more flexible management and application integration. Cheers
Brooks -
You're not missing anything. Right now the only supported way to disable or delete subscriptions is through the API. For your app, that's painful to even think about. The 2 options you mentioned are the only ones I can think of too. 1) use the API to enumerate through the Subscriptions and delete each one individually, or 2) write a pretty simple T-SQL statement to do it for you. Of course, it would be a best practice to delete the related subscriptions when a Forum is delete, however leaving them around would not actually cause in noticable pain. Since the Forum is gone, there will be no events entering the NS app and thus no notifications. For a database normalization perspective that is bad, but it's still an option to consider until better tools are available. I assume that deleting a forum is more of an exception than a rule (not something you'll do on a daily or even weekly basis) then I'd tend to treat it as such and either issue the T-SQL statements to delete the subscriptions myself or just allow the subscriptions to hang around and clean them up on a semi-annual basis. Just my $0.02 worth... HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Brooks Talley wrote:
> As happy as I am with notificaiton services, one of the big lacks I've > found is in the tools available to integrate it with existing > applications. Specifically, the XML and dynamic table approach is > great for creating applications, subscriptions, and events... but > there's no real way to manage those in an application-centric way once > the whole show is on the road. > > To use a specific example, I'm working on a notification app for a > classic ASP based forum that's derived from the popular Snitz forum > package. So it's natural to have a "new post" event with the fields > "Forum_ID" and "Topic_ID", among others. Users who've subscribed to > notifications for new posts in a particular forum get matched with a > rule that checks Forum_ID against their subscription parameter. > > So far, so good. That works beautifully. Now, what happens if we > delete a forum? Certainly all subscriptions that reference the forum > should go, too. So far, I haven't found a way to do that cleanly > through the NS interface other than literally iterating through every > subscription in the entire application looking for > GetFieldValue("Forum_ID"). > > In my application, that's brutal. First, there will be tens to > hundreds of thousands of subscriptions, so it's like a table scan, but > worse since it's done in the app. Worse, that process needs to be > done synchronously in the app with the delete, so the app's going to > sit there and be unresponsive for a while. > > Now, I can cheat and access the tables directly and do the job more or > less instantly (since I defined an index on the Forum_ID column of the > subscription class) by just doing a "delete from > InstanceApp.dbo.NSSubClassSubscriptions where forum_id=XX" -- it's a > lot less code for me to write, and the performance will be > dramatically better. Of course, it's breaking the rules. > > Anyways, I'd love to hear if I'm missing something here (the classic > two-line response to an 80 line message). If I'm not missing > something, I'd just like for the issue to be noted, and more utility > SP's provided in future versions that allow for more flexible > management and application integration. > > Cheers
Thanks very much for the response, Joe. Your book is sitting right
beside me and has been invaluable in getting this app going. I probably should have used a better example than forums, which aren't deleted that often. A better example would be individual topics, which are deleted fairly frequently. In any event, just leaving the subscriptions is kind of ugly both from a relational standpoint and because it complicates the subscription admin interface. For now, I've settled on a poor-man's version of what I believe should have been included as a NS management stored procedure in InstanceNSMain: you pass in the instance and application name, and the content of a "where" clause. It executs the delete as dynamic SQL. Cheers -b [quoted text, click to view] > I assume that deleting a forum is more of an exception > than a rule (not something you'll do on a daily or even > weekly basis) then I'd tend to treat it as such and > either issue the T-SQL statements to delete the > subscriptions myself or just allow the subscriptions > to hang around and clean them up on a semi-annual basis. *** Sent via Developersdex http://www.developersdex.com ***
Brooks,
Thanks for your feedback on this issue. I think there is a solution to the specific problem you're talking about here (more on that below), but on the bigger problem of integrating an NS app database into a larger whole, I hear you and will definitely take your feedback into account during feature planning. To solve the immediate problem of deleting subscriptions, take a look at the public subscriptions view that NS makes available. In every app database, there will be a view called NS<SubscriptionClassName>View that always shows all the subscriptions of a given subscription class. This view is documented in the Books Online (see the Table and View reference) and is intended for public use i.e. it's not an internal NS view. I know this was intended to be a read-only view, but I don't think there's any reason you couldn't delete from it also. It selects from a single base table, so SQL should not restrict that, and from an NS point of view, I think it's alright too. I just tried deleting from this view on a simple test app and didn't see any problems. That said, be aware that this isn't a scenario we particularly thought about when building and testing NS, so there may be some hidden gotchas I'm not yet aware of. It'll definitely be worth your time to try this out - and on our end, I'll be pushing testing of it too. Hope this helps. Thanks -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "Brooks Talley" <brooks@frnk.com> wrote in message news:eu57gQTtEHA.2532@TK2MSFTNGP10.phx.gbl... > Thanks very much for the response, Joe. Your book is sitting right > beside me and has been invaluable in getting this app going. > > I probably should have used a better example than forums, which aren't > deleted that often. A better example would be individual topics, which > are deleted fairly frequently. In any event, just leaving the > subscriptions is kind of ugly both from a relational standpoint and > because it complicates the subscription admin interface. > > For now, I've settled on a poor-man's version of what I believe should > have been included as a NS management stored procedure in > InstanceNSMain: you pass in the instance and application name, and the > content of a "where" clause. It executs the delete as dynamic SQL. > > Cheers > -b > > > I assume that deleting a forum is more of an exception > > than a rule (not something you'll do on a daily or even > > weekly basis) then I'd tend to treat it as such and > > either issue the T-SQL statements to delete the > > subscriptions myself or just allow the subscriptions > > to hang around and clean them up on a semi-annual basis. > > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Makes sense. If you find any gotchas along the way, would you share them
with the community? BTW, thanks for the kind feedback on the book! Glad you found it useful!! HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Brooks Talley wrote:
> Thanks very much for the response, Joe. Your book is sitting right > beside me and has been invaluable in getting this app going. > > I probably should have used a better example than forums, which aren't > deleted that often. A better example would be individual topics, which > are deleted fairly frequently. In any event, just leaving the > subscriptions is kind of ugly both from a relational standpoint and > because it complicates the subscription admin interface. > > For now, I've settled on a poor-man's version of what I believe should > have been included as a NS management stored procedure in > InstanceNSMain: you pass in the instance and application name, and the > content of a "where" clause. It executs the delete as dynamic SQL. > > Cheers > -b > > >>I assume that deleting a forum is more of an exception >>than a rule (not something you'll do on a daily or even >>weekly basis) then I'd tend to treat it as such and >>either issue the T-SQL statements to delete the >>subscriptions myself or just allow the subscriptions >>to hang around and clean them up on a semi-annual basis. > > > > > *** Sent via Developersdex http://www.developersdex.com ***
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |