Archive for the ‘c#.net’ tag
LAB: Image Detection, Part 2
Warning – this post will be heavy on images, so if you are on a low bandwidth connection, well sucks to be you then.
With a few minor improvements to the code, here we have the Source image:
Here is the target image’s blobs:
As you can see, it has found both blobs, and here is the raw output for the blob data:
They are very similar, only a few pixels out! Now to compare the images somehow…
Code Snippets – Text on images
Two code snippets for you:
private void drawTextWithBackground (string Text, Font font, Graphics grpaphics, Brush backgroundBrush, Brush foregroundBrush, int x, int y)
{
SizeF size = grpaphics.MeasureString(Text, font);
grpaphics.FillRectangle(backgroundBrush, x,y,size.Width, size.Height);
grpaphics.DrawString(Text,font,foregroundBrush,x+1,y+1);
}
private void drawTextAtBottom (String Text, Font font, Graphics graphics, Image sourceImage, Brush backgroundBrush, Brush foregroundBrush)
{
SizeF size = graphics.MeasureString(Text, font);
int y = (int) (sourceImage.Height – size.Height);
drawTextWithBackground(Text,font,graphics,backgroundBrush,foregroundBrush,1,y);
}
Does exactly what it says on the tin!
LAB: Image Detection, Part 1
Every now and then, I get a crazy idea, to try something rather hard in .net – maybe hard isnt the right word for it, but something that hasn’t really been attempted before, or if it has, nothing public about it. So the idea I have is for some form of image detection, to say that Image X is x % similar to Image Y. I prefer to use the terms Source and Target, but it doesn’t really matter.
The source and the target images are here. That’s a photo I took a few weeks ago, if you do decide to steal it, please put a message on there that points back to me 
Anyway, the target image is 14×15 pixels smaller – that’s a whopping 210 pixels different! It shouldn’t be too hard to match up the two images, or should it?
Histograms
My first attempt was with Histograms. I grabbed some open source (unsafe!) code that generates an array of int[] and lists the histogram values, so I shoved both images through that, and got it to output the Source’s histogram value, the Target’s histogram value, the difference between them both, and the percentage of how similar it is, like so: (The first number is the key# of the int in the array, just because)
Right at the bottom, I had an average of all the percentages, to see how “similar” the image is. I was expecting the percentage to be fairly high, since there is only a few hundred pixels difference, and I didn’t change any of the levels or colours when I cut the images out of each other, but it told me there was a 87.7% similarity! That was very shockingly low. If you see something wrong with my maths from the code below, please let me know:
public HistogramCompareResults(int key, int source, int target)
{
this.key = key;
this.source = source;
this.target = target;
difference = source – target;
if (difference < 0)
{
difference = difference*-1;
}
if (source != 0 && target !=0)
{
if (source > target)
{
percentage = (double)target / (double)source;
}
else
{
percentage = (double)source / (double)target;
}
percentage = percentage*100;
}
}
Image Processing
My next port of call was basically image processing, like what I did with my ANPR project that I created – basically filtering stuff out and building a “thumbprint” of the image, that hopefully will withstand being resized and stuff like that. Using image filters, flattening images and looking for large “blobs” of images, sofar I have come up with this:
That is the current “thumbprint” for the red channel, on the source image.
An introduction to Pex
Pex generates Unit Tests from Parameterized Unit Tests through Automated White box Testing based on Input Generation
Ill be the first to admit, I have never been a big fan of unit testing. To me, it seems like a big waste of time. I’m not sure why, but I just don’t like it – I suppose it has something to-do with spending time writing tests instead of actually doing proper, paid work. Yes, yes, I know that tests “prove” that your code works, as long as you have coded your tests right. But what if you haven’t?
Anyway, I thought id give Pex a test since it generates unit tests for me. I love things that generate things for me, because I like to do things as quickly as possible, and the computer is millions of times faster than me at generating stuff.
For starters – head over to the Pex website, and download it – I downloaded the academic version, since im running VS.NET 2008 pro. Installing it after downloading it also might help matters.
Once you have installed it, you should be able to just open up a project and when right clicking inside a .cs file, you should see a few menu items:
Once you click on that little baby, Pex should start whizzing into action. Be warned, Pex will only test Public classes and methods – it dosent like testing anything else that isnt public, and I believe it will give you a warning.
In VS.NET’s status bar, you will get a bunch of messages, along the lines of “Pex: listening to monitored process (cold start)” and then “Pex: Finished”. It should also pop up a window, something vaguely like the following:
I know this looks confusing at first, but it really isn’t. Its quite simple really. You have various options, but what’s interesting (I think) is the grid view in the middle. The first icon says if the test passed or failed – green = pass, red = ? (Take a guess, gwan, take a guess!) The number denotes the number of the test (its incremental), and the name is the value it has tried If the test fails and throws an exception, that will be listed under the Summary and Error message bits.
If you click on the test, you can see the following to the side :
The details is the actual code for the unit test that it has executed. I know, it says “this.” instead of the class name, and that’s because Pex creates a copy of the code to run tests on, in a partial class. Under the stack trace subtab, it well, gives you the stack trace (Please tell me you knew what that would do. Please.)
This is where Pex becomes rather … brilliant – it allows you to save all of these generated tests into its own project, simply by selecting the tests you want to save, and hitting the fancy “Save Test…” button – it will go off, and generate its own project (It will ask you for the name of the project, where it should live etc, I will provide a screenshot later) – and it will save the tests to that project! Tests that you can run later, and you don’t have to use vs.net’s test suite – you can decide to plug in different testing libraries such as MBUnit (my current fav) by downloading extensions that are available on CodePlex, or I believe you can create your own.
More on Pex later!
OnItemCommand for Nettiers 2.3
Well the good news is I have submitted my patch to include the OnItemCommand for the TableRepeater in Nettiers 2.3 – You can either download the file from this link, or directly from here (it lives in WebLibrary\UI)
Nesting Repeaters without OnItemDataBound
And the page result is:
abc 12345
def 67890
Delete YAF from my database
BEGIN TRAN DROP PROCEDURE yaf_registry_list DROP PROCEDURE yaf_checkemail_save DROP PROCEDURE yaf_registry_save DROP PROCEDURE yaf_user_delete DROP PROCEDURE yaf_active_updatemaxstats DROP PROCEDURE yaf_user_approve DROP PROCEDURE yaf_eventlog_delete DROP PROCEDURE yaf_poll_stats DROP PROCEDURE yaf_eventlog_create DROP PROCEDURE yaf_poll_save DROP PROCEDURE yaf_bannedip_save DROP PROCEDURE yaf_choice_vote DROP PROCEDURE yaf_bannedip_list DROP PROCEDURE yaf_pollvote_check DROP PROCEDURE yaf_bannedip_delete DROP PROCEDURE yaf_post_list DROP PROCEDURE yaf_category_list DROP PROCEDURE yaf_user_accessmasks DROP PROCEDURE yaf_category_save DROP PROCEDURE yaf_forum_simplelist DROP PROCEDURE yaf_category_simplelist DROP PROCEDURE yaf_topic_delete DROP PROCEDURE yaf_topic_active DROP PROCEDURE yaf_forum_delete DROP PROCEDURE yaf_topic_latest DROP PROCEDURE yaf_forum_listpath DROP PROCEDURE yaf_post_last10user DROP PROCEDURE yaf_forum_listSubForums DROP PROCEDURE yaf_message_approve DROP PROCEDURE yaf_forum_moderatelist DROP PROCEDURE yaf_message_delete DROP PROCEDURE yaf_forum_list DROP PROCEDURE yaf_pageload DROP PROCEDURE yaf_forum_listall_fromcat DROP PROCEDURE yaf_forum_listall DROP PROCEDURE yaf_forum_save DROP PROCEDURE yaf_forum_listallmymoderated DROP PROCEDURE yaf_forum_updatelastpost DROP PROCEDURE yaf_category_listread DROP PROCEDURE yaf_forum_updatestats DROP PROCEDURE yaf_user_list DROP PROCEDURE yaf_forumaccess_group DROP PROCEDURE yaf_nntpforum_update DROP PROCEDURE yaf_group_save DROP PROCEDURE yaf_topic_updatelastpost DROP PROCEDURE yaf_message_update DROP PROCEDURE yaf_topic_move DROP PROCEDURE yaf_nntptopic_savemessage DROP PROCEDURE yaf_system_initialize DROP PROCEDURE yaf_nntpforum_list DROP PROCEDURE yaf_system_updateversion DROP PROCEDURE yaf_message_save DROP PROCEDURE yaf_active_list DROP PROCEDURE yaf_topic_prune DROP PROCEDURE yaf_attachment_list DROP PROCEDURE yaf_topic_save DROP PROCEDURE yaf_board_create DROP PROCEDURE yaf_board_delete DROP PROCEDURE yaf_watchforum_list DROP PROCEDURE yaf_board_poststats DROP PROCEDURE yaf_category_delete DROP PROCEDURE yaf_accessmask_delete DROP PROCEDURE yaf_forumaccess_list DROP PROCEDURE yaf_forumaccess_save DROP PROCEDURE yaf_group_delete DROP PROCEDURE yaf_forum_moderators DROP PROCEDURE yaf_user_find DROP PROCEDURE yaf_user_guest DROP PROCEDURE yaf_pmessage_save DROP PROCEDURE yaf_group_list DROP PROCEDURE yaf_group_member DROP PROCEDURE yaf_active_stats DROP PROCEDURE yaf_usergroup_list DROP PROCEDURE yaf_mail_createwatch DROP PROCEDURE yaf_mail_delete DROP PROCEDURE yaf_mail_list DROP PROCEDURE yaf_message_findunread DROP PROCEDURE yaf_message_getReplies DROP PROCEDURE yaf_message_list DROP PROCEDURE yaf_message_unapproved DROP PROCEDURE yaf_board_stats DROP PROCEDURE yaf_message_simplelist DROP PROCEDURE yaf_watchtopic_list DROP PROCEDURE yaf_post_list_reverse10 DROP PROCEDURE yaf_topic_listmessages DROP PROCEDURE yaf_user_activity_rank DROP PROCEDURE yaf_pmessage_list DROP PROCEDURE yaf_forum_listread DROP PROCEDURE yaf_pmessage_prune DROP PROCEDURE yaf_topic_list DROP PROCEDURE yaf_userpmessage_list DROP PROCEDURE yaf_user_deleteold DROP PROCEDURE yaf_pmessage_delete DROP PROCEDURE yaf_smiley_delete DROP PROCEDURE yaf_smiley_list DROP PROCEDURE yaf_smiley_listunique DROP PROCEDURE yaf_smiley_save DROP PROCEDURE yaf_topic_lock DROP PROCEDURE yaf_topic_findnext DROP PROCEDURE yaf_topic_findprev DROP PROCEDURE yaf_topic_info DROP PROCEDURE yaf_topic_simplelist DROP PROCEDURE yaf_forum_listtopics DROP PROCEDURE yaf_user_removepointsbytopicid DROP PROCEDURE yaf_user_resetpoints DROP PROCEDURE yaf_user_removepoints DROP PROCEDURE yaf_user_login DROP PROCEDURE yaf_user_recoverpassword DROP PROCEDURE yaf_user_nntp DROP PROCEDURE yaf_user_savepassword DROP PROCEDURE yaf_user_saveavatar DROP PROCEDURE yaf_user_suspend DROP PROCEDURE yaf_user_setpoints DROP PROCEDURE yaf_active_listtopic DROP PROCEDURE yaf_user_savesignature DROP PROCEDURE yaf_active_listforum DROP PROCEDURE yaf_userforum_list DROP PROCEDURE yaf_user_upgrade DROP PROCEDURE yaf_eventlog_list DROP PROCEDURE yaf_user_simplelist DROP PROCEDURE yaf_user_emails DROP PROCEDURE yaf_user_getsignature DROP PROCEDURE yaf_user_addpoints DROP PROCEDURE yaf_user_adminsave DROP PROCEDURE yaf_user_avatarimage DROP PROCEDURE yaf_user_changepassword DROP PROCEDURE yaf_user_deleteavatar DROP PROCEDURE yaf_user_getpoints DROP PROCEDURE yaf_watchforum_delete DROP PROCEDURE yaf_watchforum_add DROP PROCEDURE yaf_watchforum_check DROP PROCEDURE yaf_watchtopic_delete DROP PROCEDURE yaf_watchtopic_check DROP PROCEDURE yaf_watchtopic_add DROP PROCEDURE yaf_attachment_save DROP PROCEDURE yaf_attachment_delete DROP PROCEDURE yaf_attachment_download DROP PROCEDURE yaf_usergroup_save DROP PROCEDURE yaf_rank_delete DROP PROCEDURE yaf_rank_list DROP PROCEDURE yaf_rank_save DROP PROCEDURE yaf_accessmask_save DROP PROCEDURE yaf_accessmask_list DROP PROCEDURE yaf_userforum_save DROP PROCEDURE yaf_userforum_delete DROP PROCEDURE yaf_board_save DROP PROCEDURE yaf_board_list DROP PROCEDURE yaf_nntpserver_delete DROP PROCEDURE yaf_nntpserver_list DROP PROCEDURE yaf_nntpserver_save DROP PROCEDURE yaf_nntpforum_save DROP PROCEDURE yaf_nntpforum_delete DROP PROCEDURE yaf_nntptopic_list DROP PROCEDURE yaf_pmessage_info DROP PROCEDURE yaf_userpmessage_delete DROP PROCEDURE yaf_pmessage_markread DROP PROCEDURE yaf_replace_words_delete DROP PROCEDURE yaf_replace_words_edit DROP PROCEDURE yaf_replace_words_list DROP PROCEDURE yaf_user_save DROP PROCEDURE yaf_replace_words_save DROP PROCEDURE yaf_checkemail_update DROP VIEW yaf_vaccess DROP FUNCTION yaf_forum_topics DROP FUNCTION yaf_forum_posts DROP FUNCTION yaf_bitset ALTER TABLE yaf_Choice DROP CONSTRAINT [FK_yaf_Choice_yaf_Poll] ALTER TABLE yaf_PollVote DROP CONSTRAINT [FK_yaf_PollVote_yaf_Poll] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Poll] ALTER TABLE yaf_AccessMask DROP CONSTRAINT [FK_yaf_AccessMask_yaf_Board] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Board] ALTER TABLE yaf_BannedIP DROP CONSTRAINT [FK_yaf_BannedIP_yaf_Board] ALTER TABLE yaf_Category DROP CONSTRAINT [FK_yaf_Category_yaf_Board] ALTER TABLE yaf_Group DROP CONSTRAINT [FK_yaf_Group_yaf_Board] ALTER TABLE yaf_NntpServer DROP CONSTRAINT [FK_yaf_NntpServer_yaf_Board] ALTER TABLE yaf_Rank DROP CONSTRAINT [FK_yaf_Rank_yaf_Board] ALTER TABLE yaf_Registry DROP CONSTRAINT [FK_yaf_Registry_yaf_Board] ALTER TABLE yaf_Smiley DROP CONSTRAINT [FK_yaf_Smiley_yaf_Board] ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Board] ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Rank] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_User] ALTER TABLE yaf_CheckEmail DROP CONSTRAINT [FK_yaf_CheckEmail_yaf_User] ALTER TABLE yaf_EventLog DROP CONSTRAINT [FK_yaf_EventLog_yaf_User] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_User] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_User] ALTER TABLE yaf_PMessage DROP CONSTRAINT [FK_yaf_PMessage_yaf_User1] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User2] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_User] ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_User] ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_User] ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_User] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Category] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Forum] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Forum] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Forum] ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_Forum] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Forum] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_Forum] ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_Forum] ALTER TABLE yaf_Attachment DROP CONSTRAINT [FK_yaf_Attachment_yaf_Message] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Message] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Message] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Message] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Topic] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Topic] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Topic] ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_Topic] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Topic] ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_Topic] ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_NntpServer] ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_NntpForum] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_AccessMask] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_AccessMask] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Group] ALTER TABLE yaf_UserGroup DROP CONSTRAINT [FK_yaf_UserGroup_yaf_Group] ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_PMessage] drop table dbo.yaf_AccessMask drop table dbo.yaf_Active drop table dbo.yaf_Attachment drop table dbo.yaf_BannedIP drop table dbo.yaf_Board drop table dbo.yaf_Category drop table dbo.yaf_CheckEmail drop table dbo.yaf_Choice drop table dbo.yaf_EventLog drop table dbo.yaf_Forum drop table dbo.yaf_ForumAccess drop table dbo.yaf_Group drop table dbo.yaf_Mail drop table dbo.yaf_Message drop table dbo.yaf_NntpForum drop table dbo.yaf_NntpServer drop table dbo.yaf_NntpTopic drop table dbo.yaf_PMessage drop table dbo.yaf_Poll drop table dbo.yaf_PollVote drop table dbo.yaf_Rank drop table dbo.yaf_Registry drop table dbo.yaf_Replace_Words drop table dbo.yaf_Smiley drop table dbo.yaf_Topic drop table dbo.yaf_User drop table dbo.yaf_UserForum drop table dbo.yaf_UserGroup drop table dbo.yaf_UserPMessage drop table dbo.yaf_WatchForum drop table dbo.yaf_WatchTopic IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return 11 END COMMIT TRAN
Dropping views, functions and proc’s
Part 3 of my “Getting rid of YAF” series involves dropping views, functions and stored procedures.
SELECT 'DROP PROCEDURE '+name FROM dbo.sysobjects WHERE (type = 'P') union all SELECT 'DROP VIEW ' + name from dbo.sysobjects WHERE (type = 'V') union all SELECT 'DROP FUNCTION ' + name from dbo.sysobjects WHERE (type = 'FN')
Which should hopefully give you the following:
DROP PROCEDURE yaf_registry_list DROP PROCEDURE yaf_checkemail_save DROP PROCEDURE yaf_registry_save DROP PROCEDURE yaf_user_delete DROP PROCEDURE yaf_active_updatemaxstats DROP PROCEDURE yaf_user_approve DROP PROCEDURE yaf_eventlog_delete DROP PROCEDURE yaf_poll_stats DROP PROCEDURE yaf_eventlog_create DROP PROCEDURE yaf_poll_save DROP PROCEDURE yaf_bannedip_save DROP PROCEDURE yaf_choice_vote DROP PROCEDURE yaf_bannedip_list DROP PROCEDURE yaf_pollvote_check DROP PROCEDURE yaf_bannedip_delete DROP PROCEDURE yaf_post_list DROP PROCEDURE yaf_category_list DROP PROCEDURE yaf_user_accessmasks DROP PROCEDURE yaf_category_save DROP PROCEDURE yaf_forum_simplelist DROP PROCEDURE yaf_category_simplelist DROP PROCEDURE yaf_topic_delete DROP PROCEDURE yaf_topic_active DROP PROCEDURE yaf_forum_delete DROP PROCEDURE yaf_topic_latest DROP PROCEDURE yaf_forum_listpath DROP PROCEDURE yaf_post_last10user DROP PROCEDURE yaf_forum_listSubForums DROP PROCEDURE yaf_message_approve DROP PROCEDURE yaf_forum_moderatelist DROP PROCEDURE yaf_message_delete DROP PROCEDURE yaf_forum_list DROP PROCEDURE yaf_pageload DROP PROCEDURE yaf_forum_listall_fromcat DROP PROCEDURE yaf_forum_listall DROP PROCEDURE yaf_forum_save DROP PROCEDURE yaf_forum_listallmymoderated DROP PROCEDURE yaf_forum_updatelastpost DROP PROCEDURE yaf_category_listread DROP PROCEDURE yaf_forum_updatestats DROP PROCEDURE yaf_user_list DROP PROCEDURE yaf_forumaccess_group DROP PROCEDURE yaf_nntpforum_update DROP PROCEDURE yaf_group_save DROP PROCEDURE yaf_topic_updatelastpost DROP PROCEDURE yaf_message_update DROP PROCEDURE yaf_topic_move DROP PROCEDURE yaf_nntptopic_savemessage DROP PROCEDURE yaf_system_initialize DROP PROCEDURE yaf_nntpforum_list DROP PROCEDURE yaf_system_updateversion DROP PROCEDURE yaf_message_save DROP PROCEDURE yaf_active_list DROP PROCEDURE yaf_topic_prune DROP PROCEDURE yaf_attachment_list DROP PROCEDURE yaf_topic_save DROP PROCEDURE yaf_board_create DROP PROCEDURE yaf_board_delete DROP PROCEDURE yaf_watchforum_list DROP PROCEDURE yaf_board_poststats DROP PROCEDURE yaf_category_delete DROP PROCEDURE yaf_accessmask_delete DROP PROCEDURE yaf_forumaccess_list DROP PROCEDURE yaf_forumaccess_save DROP PROCEDURE yaf_group_delete DROP PROCEDURE yaf_forum_moderators DROP PROCEDURE yaf_user_find DROP PROCEDURE yaf_user_guest DROP PROCEDURE yaf_pmessage_save DROP PROCEDURE yaf_group_list DROP PROCEDURE yaf_group_member DROP PROCEDURE yaf_active_stats DROP PROCEDURE yaf_usergroup_list DROP PROCEDURE yaf_mail_createwatch DROP PROCEDURE yaf_mail_delete DROP PROCEDURE yaf_mail_list DROP PROCEDURE yaf_message_findunread DROP PROCEDURE yaf_message_getReplies DROP PROCEDURE yaf_message_list DROP PROCEDURE yaf_message_unapproved DROP PROCEDURE yaf_board_stats DROP PROCEDURE yaf_message_simplelist DROP PROCEDURE yaf_watchtopic_list DROP PROCEDURE yaf_post_list_reverse10 DROP PROCEDURE yaf_topic_listmessages DROP PROCEDURE yaf_user_activity_rank DROP PROCEDURE yaf_pmessage_list DROP PROCEDURE yaf_forum_listread DROP PROCEDURE yaf_pmessage_prune DROP PROCEDURE yaf_topic_list DROP PROCEDURE yaf_userpmessage_list DROP PROCEDURE yaf_user_deleteold DROP PROCEDURE yaf_pmessage_delete DROP PROCEDURE yaf_smiley_delete DROP PROCEDURE yaf_smiley_list DROP PROCEDURE yaf_smiley_listunique DROP PROCEDURE yaf_smiley_save DROP PROCEDURE yaf_topic_lock DROP PROCEDURE yaf_topic_findnext DROP PROCEDURE yaf_topic_findprev DROP PROCEDURE yaf_topic_info DROP PROCEDURE yaf_topic_simplelist DROP PROCEDURE yaf_forum_listtopics DROP PROCEDURE yaf_user_removepointsbytopicid DROP PROCEDURE yaf_user_resetpoints DROP PROCEDURE yaf_user_removepoints DROP PROCEDURE yaf_user_login DROP PROCEDURE yaf_user_recoverpassword DROP PROCEDURE yaf_user_nntp DROP PROCEDURE yaf_user_savepassword DROP PROCEDURE yaf_user_saveavatar DROP PROCEDURE yaf_user_suspend DROP PROCEDURE yaf_user_setpoints DROP PROCEDURE yaf_active_listtopic DROP PROCEDURE yaf_user_savesignature DROP PROCEDURE yaf_active_listforum DROP PROCEDURE yaf_userforum_list DROP PROCEDURE yaf_user_upgrade DROP PROCEDURE yaf_eventlog_list DROP PROCEDURE yaf_user_simplelist DROP PROCEDURE yaf_user_emails DROP PROCEDURE yaf_user_getsignature DROP PROCEDURE yaf_user_addpoints DROP PROCEDURE yaf_user_adminsave DROP PROCEDURE yaf_user_avatarimage DROP PROCEDURE yaf_user_changepassword DROP PROCEDURE yaf_user_deleteavatar DROP PROCEDURE yaf_user_getpoints DROP PROCEDURE yaf_watchforum_delete DROP PROCEDURE yaf_watchforum_add DROP PROCEDURE yaf_watchforum_check DROP PROCEDURE yaf_watchtopic_delete DROP PROCEDURE yaf_watchtopic_check DROP PROCEDURE yaf_watchtopic_add DROP PROCEDURE yaf_attachment_save DROP PROCEDURE yaf_attachment_delete DROP PROCEDURE yaf_attachment_download DROP PROCEDURE yaf_usergroup_save DROP PROCEDURE yaf_rank_delete DROP PROCEDURE yaf_rank_list DROP PROCEDURE yaf_rank_save DROP PROCEDURE yaf_accessmask_save DROP PROCEDURE yaf_accessmask_list DROP PROCEDURE yaf_userforum_save DROP PROCEDURE yaf_userforum_delete DROP PROCEDURE yaf_board_save DROP PROCEDURE yaf_board_list DROP PROCEDURE yaf_nntpserver_delete DROP PROCEDURE yaf_nntpserver_list DROP PROCEDURE yaf_nntpserver_save DROP PROCEDURE yaf_nntpforum_save DROP PROCEDURE yaf_nntpforum_delete DROP PROCEDURE yaf_nntptopic_list DROP PROCEDURE yaf_pmessage_info DROP PROCEDURE yaf_userpmessage_delete DROP PROCEDURE yaf_pmessage_markread DROP PROCEDURE yaf_replace_words_delete DROP PROCEDURE yaf_replace_words_edit DROP PROCEDURE yaf_replace_words_list DROP PROCEDURE yaf_user_save DROP PROCEDURE yaf_replace_words_save DROP PROCEDURE yaf_checkemail_update DROP VIEW yaf_vaccess DROP FUNCTION yaf_forum_topics DROP FUNCTION yaf_forum_posts DROP FUNCTION yaf_bitset
Using the scrips below and this script should be all you need to have a YAF free database.
Creating FK’s
If you get the following error, when trying to export data from YAF:
You might want to try running this script, to generate the create FK statements. Inconjuction with the drop FK statements, it will alow you to generate YAF scripts painlessly.
SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' ADD CONSTRAINT ' + f.name + ' FOREIGN KEY (' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ') REFERENCES ' + OBJECT_NAME (f.referenced_object_id) + '(' + COL_NAME(fc.referenced_object_id, fc.referenced_column_id) + ')' FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
It should give you an output like:
ALTER TABLE yaf_Choice ADD CONSTRAINT FK_yaf_Choice_yaf_Poll FOREIGN KEY (PollID) REFERENCES yaf_Poll(PollID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_Poll FOREIGN KEY (PollID) REFERENCES yaf_Poll(PollID) ALTER TABLE yaf_PollVote ADD CONSTRAINT FK_yaf_PollVote_yaf_Poll FOREIGN KEY (PollID) REFERENCES yaf_Poll(PollID) ALTER TABLE yaf_AccessMask ADD CONSTRAINT FK_yaf_AccessMask_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Active ADD CONSTRAINT FK_yaf_Active_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_BannedIP ADD CONSTRAINT FK_yaf_BannedIP_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Category ADD CONSTRAINT FK_yaf_Category_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Group ADD CONSTRAINT FK_yaf_Group_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_NntpServer ADD CONSTRAINT FK_yaf_NntpServer_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Rank ADD CONSTRAINT FK_yaf_Rank_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Registry ADD CONSTRAINT FK_yaf_Registry_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_Smiley ADD CONSTRAINT FK_yaf_Smiley_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_User ADD CONSTRAINT FK_yaf_User_yaf_Board FOREIGN KEY (BoardID) REFERENCES yaf_Board(BoardID) ALTER TABLE yaf_User ADD CONSTRAINT FK_yaf_User_yaf_Rank FOREIGN KEY (RankID) REFERENCES yaf_Rank(RankID) ALTER TABLE yaf_Active ADD CONSTRAINT FK_yaf_Active_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_CheckEmail ADD CONSTRAINT FK_yaf_CheckEmail_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_EventLog ADD CONSTRAINT FK_yaf_EventLog_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_Forum ADD CONSTRAINT FK_yaf_Forum_yaf_User FOREIGN KEY (LastUserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_Message ADD CONSTRAINT FK_yaf_Message_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_PMessage ADD CONSTRAINT FK_yaf_PMessage_yaf_User1 FOREIGN KEY (FromUserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_User2 FOREIGN KEY (LastUserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_UserForum ADD CONSTRAINT FK_yaf_UserForum_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_UserGroup ADD CONSTRAINT FK_yaf_UserGroup_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_UserPMessage ADD CONSTRAINT FK_yaf_UserPMessage_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_WatchForum ADD CONSTRAINT FK_yaf_WatchForum_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_WatchTopic ADD CONSTRAINT FK_yaf_WatchTopic_yaf_User FOREIGN KEY (UserID) REFERENCES yaf_User(UserID) ALTER TABLE yaf_Forum ADD CONSTRAINT FK_yaf_Forum_yaf_Category FOREIGN KEY (CategoryID) REFERENCES yaf_Category(CategoryID) ALTER TABLE yaf_Active ADD CONSTRAINT FK_yaf_Active_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_Forum ADD CONSTRAINT FK_yaf_Forum_yaf_Forum FOREIGN KEY (ParentID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_ForumAccess ADD CONSTRAINT FK_yaf_ForumAccess_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_NntpForum ADD CONSTRAINT FK_yaf_NntpForum_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_UserForum ADD CONSTRAINT FK_yaf_UserForum_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_WatchForum ADD CONSTRAINT FK_yaf_WatchForum_yaf_Forum FOREIGN KEY (ForumID) REFERENCES yaf_Forum(ForumID) ALTER TABLE yaf_Attachment ADD CONSTRAINT FK_yaf_Attachment_yaf_Message FOREIGN KEY (MessageID) REFERENCES yaf_Message(MessageID) ALTER TABLE yaf_Forum ADD CONSTRAINT FK_yaf_Forum_yaf_Message FOREIGN KEY (LastMessageID) REFERENCES yaf_Message(MessageID) ALTER TABLE yaf_Message ADD CONSTRAINT FK_yaf_Message_yaf_Message FOREIGN KEY (ReplyTo) REFERENCES yaf_Message(MessageID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_Message FOREIGN KEY (LastMessageID) REFERENCES yaf_Message(MessageID) ALTER TABLE yaf_Active ADD CONSTRAINT FK_yaf_Active_yaf_Topic FOREIGN KEY (TopicID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_Forum ADD CONSTRAINT FK_yaf_Forum_yaf_Topic FOREIGN KEY (LastTopicID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_Message ADD CONSTRAINT FK_yaf_Message_yaf_Topic FOREIGN KEY (TopicID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_NntpTopic ADD CONSTRAINT FK_yaf_NntpTopic_yaf_Topic FOREIGN KEY (TopicID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_Topic ADD CONSTRAINT FK_yaf_Topic_yaf_Topic FOREIGN KEY (TopicMovedID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_WatchTopic ADD CONSTRAINT FK_yaf_WatchTopic_yaf_Topic FOREIGN KEY (TopicID) REFERENCES yaf_Topic(TopicID) ALTER TABLE yaf_NntpForum ADD CONSTRAINT FK_yaf_NntpForum_yaf_NntpServer FOREIGN KEY (NntpServerID) REFERENCES yaf_NntpServer(NntpServerID) ALTER TABLE yaf_NntpTopic ADD CONSTRAINT FK_yaf_NntpTopic_yaf_NntpForum FOREIGN KEY (NntpForumID) REFERENCES yaf_NntpForum(NntpForumID) ALTER TABLE yaf_ForumAccess ADD CONSTRAINT FK_yaf_ForumAccess_yaf_AccessMask FOREIGN KEY (AccessMaskID) REFERENCES yaf_AccessMask(AccessMaskID) ALTER TABLE yaf_UserForum ADD CONSTRAINT FK_yaf_UserForum_yaf_AccessMask FOREIGN KEY (AccessMaskID) REFERENCES yaf_AccessMask(AccessMaskID) ALTER TABLE yaf_ForumAccess ADD CONSTRAINT FK_yaf_ForumAccess_yaf_Group FOREIGN KEY (GroupID) REFERENCES yaf_Group(GroupID) ALTER TABLE yaf_UserGroup ADD CONSTRAINT FK_yaf_UserGroup_yaf_Group FOREIGN KEY (GroupID) REFERENCES yaf_Group(GroupID) ALTER TABLE yaf_UserPMessage ADD CONSTRAINT FK_yaf_UserPMessage_yaf_PMessage FOREIGN KEY (PMessageID) REFERENCES yaf_PMessage(PMessageID)
Deleting FK’s from MSSQL
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
This script is a godsend. Basically, we had a botched install of Yet Another Forum, and we had to delete it (YAY!), but because of the FK constraints, we couldnt do a simple “DROP TABLE *” on the database, and nor could we just drop the database.
So I came up with this script that generated the SQL Script for me, based on the above:
SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' DROP CONSTRAINT [' + f.name + ']' AS Moo, f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
Can be refined alot better, but works. The resulting script is as follows – note I added the “drop table” by hand:
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Forum] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Forum] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Forum] ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_Forum] ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_Forum] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_Forum] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Forum] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Message] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Message] ALTER TABLE yaf_Attachment DROP CONSTRAINT [FK_yaf_Attachment_yaf_Message] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Message] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Poll] ALTER TABLE yaf_Choice DROP CONSTRAINT [FK_yaf_Choice_yaf_Poll] ALTER TABLE yaf_PollVote DROP CONSTRAINT [FK_yaf_PollVote_yaf_Poll] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Topic] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Topic] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Topic] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Topic] ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_Topic] ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_Topic] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_User] ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_User] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User] ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User2] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_User] ALTER TABLE yaf_CheckEmail DROP CONSTRAINT [FK_yaf_CheckEmail_yaf_User] ALTER TABLE yaf_PMessage DROP CONSTRAINT [FK_yaf_PMessage_yaf_User1] ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_User] ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_User] ALTER TABLE yaf_UserGroup DROP CONSTRAINT [FK_yaf_UserGroup_yaf_User] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_User] ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_User] ALTER TABLE yaf_EventLog DROP CONSTRAINT [FK_yaf_EventLog_yaf_User] ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Rank] ALTER TABLE yaf_Category DROP CONSTRAINT [FK_yaf_Category_yaf_Board] ALTER TABLE yaf_Rank DROP CONSTRAINT [FK_yaf_Rank_yaf_Board] ALTER TABLE yaf_AccessMask DROP CONSTRAINT [FK_yaf_AccessMask_yaf_Board] ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Board] ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Board] ALTER TABLE yaf_BannedIP DROP CONSTRAINT [FK_yaf_BannedIP_yaf_Board] ALTER TABLE yaf_Group DROP CONSTRAINT [FK_yaf_Group_yaf_Board] ALTER TABLE yaf_NntpServer DROP CONSTRAINT [FK_yaf_NntpServer_yaf_Board] ALTER TABLE yaf_Smiley DROP CONSTRAINT [FK_yaf_Smiley_yaf_Board] ALTER TABLE yaf_Registry DROP CONSTRAINT [FK_yaf_Registry_yaf_Board] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Group] ALTER TABLE yaf_UserGroup DROP CONSTRAINT [FK_yaf_UserGroup_yaf_Group] ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_PMessage] ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_AccessMask] ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_AccessMask] ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_NntpServer] ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_NntpForum] ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Category] drop table dbo.yaf_AccessMask drop table dbo.yaf_Active drop table dbo.yaf_Attachment drop table dbo.yaf_BannedIP drop table dbo.yaf_Board drop table dbo.yaf_Category drop table dbo.yaf_CheckEmail drop table dbo.yaf_Choice drop table dbo.yaf_EventLog drop table dbo.yaf_Forum drop table dbo.yaf_ForumAccess drop table dbo.yaf_Group drop table dbo.yaf_Mail drop table dbo.yaf_Message drop table dbo.yaf_NntpForum drop table dbo.yaf_NntpServer drop table dbo.yaf_NntpTopic drop table dbo.yaf_PMessage drop table dbo.yaf_Poll drop table dbo.yaf_PollVote drop table dbo.yaf_Rank drop table dbo.yaf_Registry drop table dbo.yaf_Replace_Words drop table dbo.yaf_Smiley drop table dbo.yaf_Topic drop table dbo.yaf_User drop table dbo.yaf_UserForum drop table dbo.yaf_UserGroup drop table dbo.yaf_UserPMessage drop table dbo.yaf_WatchForum drop table dbo.yaf_WatchTopic drop view dbo.yaf_vaccess