Clear all objects from database

Today I have been working on web deploy solution, and needed to clear databse.
Drop database does not work for me, because the files still hang around. And I want to reuse the implementation for every build I do. This solution come from the need of regular deploy, from my build server.


I need to clear:

1. Stored procedures if any
2. Foreign keys
3. Primary key constaints
4. Tables

My implementation works with only [dbo] schema, but you can specify the block for each chema you need.

Original source for this you can find of course on Stack overflow

My modification is make sure that database name is specified for every schema, in order to satisfy DB admins, that in case of selecting wrong schmea, the implementation does not remove everything.

Now SQL:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'HomeSite')
BEGIN

    /* ====================================================*/
    /*           Drop all non-system stored procs          */
    /* ====================================================*/
        
        /* Drop all non-system stored procs */
        DECLARE @StoredProcName VARCHAR(128)
        DECLARE @StoredProcSQL VARCHAR(254)

        SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

        WHILE @StoredProcName is not null
        BEGIN
            SELECT @StoredProcSQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@StoredProcName) +']'
            EXEC (@StoredProcSQL)
            PRINT 'Dropped Procedure: ' + @StoredProcName
            SELECT @StoredProcName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @StoredProcName ORDER BY [name])
        
        END
        
    /* ====================================================*/
    /*           Drop all Foreign Key constraints          */
    /* ====================================================*/        
        
        DECLARE @ForeignKeyName VARCHAR(128)
        DECLARE @ForeignKeyConstraint VARCHAR(254)
        DECLARE @ForeignKeySQL VARCHAR(254)

        SELECT @ForeignKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

        WHILE @ForeignKeyName is not null
        BEGIN
            SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @ForeignKeyConstraint IS NOT NULL
            BEGIN
                SELECT @ForeignKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@ForeignKeyName) +'] DROP CONSTRAINT [' + RTRIM(@ForeignKeyConstraint) +']'
                EXEC (@ForeignKeySQL)
                PRINT 'Dropped FK Constraint: ' + @ForeignKeyConstraint + ' on ' + @ForeignKeyName
                SELECT @ForeignKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @ForeignKeyConstraint AND TABLE_NAME = @ForeignKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @ForeignKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
        END
    
    /* ====================================================*/
    /*           Drop all Primary Key constraints          */
    /* ====================================================*/
    
        DECLARE @PrimaryKeyName VARCHAR(128)
        DECLARE @PrimaryKeyConstraint VARCHAR(254)
        DECLARE @PrimaryKeySQL VARCHAR(254)

        SELECT @PrimaryKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

        WHILE @PrimaryKeyName IS NOT NULL
        BEGIN
            SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            WHILE @PrimaryKeyConstraint is not null
            BEGIN
                SELECT @PrimaryKeySQL = 'ALTER TABLE [dbo].[' + RTRIM(@PrimaryKeyName) +'] DROP CONSTRAINT [' + RTRIM(@PrimaryKeyConstraint)+']'
                EXEC (@PrimaryKeySQL)
                PRINT 'Dropped PK Constraint: ' + @PrimaryKeyConstraint + ' on ' + @PrimaryKeyName
                SELECT @PrimaryKeyConstraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @PrimaryKeyConstraint AND TABLE_NAME = @PrimaryKeyName ORDER BY CONSTRAINT_NAME)
            END
        SELECT @PrimaryKeyName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
        END
        
    
        
    /* ====================================================*/
    /*           Finally drop all tables                           */
    /* ====================================================*/
        DECLARE @TableName VARCHAR(128)
        DECLARE @TableSQL VARCHAR(254)

        SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

        WHILE @TableName IS NOT NULL
        BEGIN
            SELECT @TableSQL = 'DROP TABLE [dbo].[' + RTRIM(@TableName) +']'
            EXEC (@TableSQL)
            PRINT 'Dropped Table: ' + @TableName
            SELECT @TableName = (SELECT TOP 1 [name] FROM HomeSite..sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @TableName ORDER BY [name])
        END    
    
END
GO

TFS versioning

I have found following guid, but not wrote it. All credit goes to author on link below.

source:
http://tfsbuildextensions.codeplex.com/wikipage?title=Build%20and%20Assembly%20versioning%20%28alternate%20to%20above%20using%20SDC%20Tasks%29&referringTitle=Home

Description

Incrementing the build number, and versioning the assemblies, is a simple process with the help of the Microsoft SDC Tasks (http://www.codeplex.com/sdctasks). There are two aspects to versioning a Build in TFS, first is versioning the TFS Build number and the second is versioning the assemblies in the build. Both of these are reasonable simple to do.

Usage

The first thing to do is place the SDC tasks assembly on your team build server. I found the easiest way of doing this, and to allow different builds to use different versions, was to add the assembly to the build folder in TFS along with the tasks definition file.

The top of the Tasks file will need to be customised to match your environment by supplying, or ensuring, that the taskspath property is set correctly, for me changing the property group to the following worked.

  <PropertyGroup>
    <BuildPath Condition="'$(BuildPath)'==''">$(MSBuildProjectDirectory)\</BuildPath>
    <TasksPath Condition="Exists('$(BuildPath)\Microsoft.Sdc.Tasks.dll')">$(BuildPath)\</TasksPath>
  </PropertyGroup>

You will also need to include the tasks file in your project file by adding the following line to your .proj file.

<Import Project="$(MSBuildProjectDirectory)\Microsoft.Sdc.Common.tasks" />

Now you can customise the version numbers.

1. TFS Build Number.

TFS provides a ''BuildNumberOverrideTarget'' which should be used to modify the build number. This is the appropriate place to load and configure the build number for the entire solution by outputing a ''BuildNumber'' property.

In my solution I load the version number using the SDC's VersionNumber.Update command. To keep track of the Version number and to ensure they are always unique I keep the version.xml used by the task in Source control and check it in and out of TFS using the TF command line. This can be seen in the scripts below. (the structure of the Version.XML file can be found on the SDC codeplex site, see link below.)

TFS will then use this build number for the build rather than its built in one. 


2. Versioning the assemblies.

Once you have a build number you usually want to adjust the AssemblyInfo.cs files to match the build number. 

I do this by creating my own target ''VersionAssemblies'' which I attach as a dependency to the Team build ''AfterGet'' target. The process is simple... first collect all the AssemblyInfo.cs files into an item group and then use the SDC File.Replace task to do a regular expression search and replace all the assembly version lines to the new build number.

Source

Information on the tasks used to accomplish this script can be found on the http://www.codeplex.com/sdctasks

Full credit also to the numerous blog articles I can't remember reading that pointed me the right direction for desiging this receipe.

Script

<PropertyGroup>
    <TfCommand>&quot;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\tf.exe&quot;</TfCommand>
</PropertyGroup>
<Target Name="BuildNumberOverrideTarget" DependsOnTargets="CoreInitializeWorkspace">
    <!-- First get and check out the version files.-->
    <Exec Command="$(TfCommand) get /force /noprompt &quot;$(MSBuildProjectDirectory)\version.xml&quot;"
          ContinueOnError="true" />
    <Exec Command="$(TfCommand) checkout &quot;$(MSBuildProjectDirectory)\version.xml&quot;"
          ContinueOnError="true"/>

    <!-- Now update the version number -->
    <VersionNumber.Update VersionNumberConfigFileLocation="$(MSBuildProjectDirectory)\version.xml"
                          SkipSourceControl="true">
      <Output TaskParameter="VersionNumber" PropertyName="BuildNumber" />
    </VersionNumber.Update>

    <!-- Now check the version file back in. -->
    <Exec Command="$(TfCommand) checkin /override:&quot;Automated&quot; /comment:&quot;Update Version number $(BuildNumber)&quot; /noprompt &quot;$(MSBuildProjectDirectory)\version.xml&quot;"
          ContinueOnError="false"/>

</Target>

<!-- This target is called after Team build gets all the source files from TFS. -->
<Target Name="AfterGet" DependsOnTargets="VersionAssemblies" />

<Target Name="VersionAssemblies">
     <!-- Get the Assembly Info files.-->
     <CreateItem Include="$(SolutionRoot)\Source\**\AssemblyInfo.cs;">
          <Output TaskParameter="Include" ItemName="AssemblyInfos"/>
     </CreateItem>

     <!-- Update the version numbers -->
     <File.Replace Path="%(AssemblyInfos.FullPath)" NewValue="AssemblyVersion(&quot;$(BuildNumber)&quot;)" regularExpression="AssemblyVersion\(\&quot;(\d+.\d+.\d+.\d+)\&quot;\)" ignoreCase="true" />

</Target>

Notes

1. By making the BuildNumberOverrideTarget depend on CoreInitializeWorkspace the build is forced to initilize the workspace before the buildnumber target. This makes it possible (as long as the full path is used) for the version.xml to be checked in and out of TFS the first time the build is run. (Which was a problem with the previous version.)
2. The ''AssemblyVersion'' line in the AssemblyInfo.cs files must exist for it to be found and replaced. This shouldn't usually be a problem as it usually does exists anyway.

Fake EF DbSets example

I have need to create data for my db context for my tests when testing PageHits.

Code for implementation:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;

namespace TestHelpers
{
    /// <summary>
    /// Create fake db set for testing db sets.
    /// </summary>
    /// <typeparam name="T">Pass ojbect to cereate DbContext fake.</typeparam>
    public class FakeDbSet<T> : IDbSet<T> where T : class
    {
        private HashSet<T> _data;

        public FakeDbSet()
        {
            _data = new HashSet<T>();
        }

        public virtual T Find(params object[] keyValues)
        {
            throw new NotImplementedException();
        }


        public Task<T> FindAsync(CancellationToken cancellationToken, params object[] keyValues)
        {
            throw new NotImplementedException();
        }

        public T Add(T item)
        {
            _data.Add(item);
            return item;
        }

        public T Remove(T item)
        {
            _data.Remove(item);
            return item;
        }

        public T Attach(T item)
        {
            _data.Add(item);
            return item;
        }

        public void Detach(T item)
        {
            _data.Remove(item);
        }

        Type IQueryable.ElementType
        {
            get { return _data.AsQueryable().ElementType; }
        }

        Expression IQueryable.Expression
        {
            get { return _data.AsQueryable().Expression; }
        }

        IQueryProvider IQueryable.Provider
        {
            get { return _data.AsQueryable().Provider; }
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        IEnumerator<T> IEnumerable<T>.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        public T Create()
        {
            return Activator.CreateInstance<T>();
        }

        public ObservableCollection<T> Local
        {
            get { return new ObservableCollection<T>(_data); }
        }

        public TDerivedEntity Create<TDerivedEntity>() where TDerivedEntity : class, T
        {
            return Activator.CreateInstance<TDerivedEntity>();
        }

        DbLocalView<T> IDbSet<T>.Local
        {
            get { throw new NotImplementedException(); }
        }
    }
}

Now we have to modify our Context:

  public partial class DataContext : DbContext, DataContext
    {
        static DataContext()
        {
            Database.SetInitializer<DataContext>(null);
        }

        public DataContext()
            : base("Name=DataContext")
        {
        }

        public IDbSet<PageHit> PageHits { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new PageHitMap());
        }
    }

Add information about published node to search Umbraco

I needed to add information about published node to search Umbraco. You can do it as follows:


 Examine settings for my code:

Make sure that your indexer does allow

 <add name="AutoCompleteLookupIndexer" type="UmbracoExamine.UmbracoContentIndexer, UmbracoExamine"
           supportUnpublished="true"
           supportProtected="true"
          analyzer="Lucene.Net.Analysis.Standard.StandardAnalyzer, Lucene.Net" />


in [ExamineIndex.config]

Code:



public class ExamineEvents : ApplicationBase
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="ExamineEvents"/> class.
        /// </summary>
        public ExamineEvents()
        {
            // hookup to application
            ExamineManager.Instance.IndexProviderCollection[ExamineIndexers.AutoCompleteLookupIndexer.Name()].GatheringNodeData +=
                InternalExamineEvents_GatheringNodeData;
        }

        /// <summary>
        /// Handles the GatheringNodeData event of the InternalExamineEvents control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="IndexingNodeDataEventArgs"/> instance containing the event data.</param>
        void InternalExamineEvents_GatheringNodeData(object sender, IndexingNodeDataEventArgs e)
        {
            if (e.IndexType != IndexTypes.Content) return;

            var node = uQuery.GetNode(e.NodeId);
           
            e.Fields.Add("isPublished", node==null?Boolean.TrueString:Boolean.FalseString);
        }
    }

in [your file]

Lucene concrete example

Lucene concrete example

Purpose:

I am building autocomplete search for umbraco. I need to get all pages that are searchable and not hidden from navigation.

Searchable and hiddenFromNavigation are my custom properties.

My seach examine index
  <IndexSet SetName="AutoCompleteLookupIndexSet" IndexPath="~/App_Data/TEMP/ExamineIndexes/AutoCompleteLookupIndexSet/">
    <IndexAttributeFields>
      <add Name="id" />
      <add Name="nodeName" />
      <add Name="updateDate" />
      <add Name="writerName" />
      <add Name="path" />
      <add Name="nodeTypeAlias" />
      <add Name="parentID" />
    </IndexAttributeFields>
    <IndexUserFields>
      <add Name="description"/>
      <add Name="hideFromNav"/>
      <add Name="searchable"/>    </IndexUserFields>
    <IncludeNodeTypes>
    </IncludeNodeTypes>
    <ExcludeNodeTypes>
    </ExcludeNodeTypes>
  </IndexSet>

My examine settings:

      <add name="AutoCompleteLookupIndexer" type="UmbracoExamine.UmbracoContentIndexer, UmbracoExamine"
           supportUnpublished="false"
           supportProtected="true"
          analyzer="Lucene.Net.Analysis.Standard.StandardAnalyzer, Lucene.Net" />

 <!-- AutoComplete lookup search-->
      <add name="AutoCompleteLookupSearcher" type="UmbracoExamine.UmbracoExamineSearcher, UmbracoExamine"
           analyzer="Lucene.Net.Analysis.WhitespaceAnalyzer, Lucene.Net" enableLeadingWildcards="true"/>


My Lucene query

(hideFromNav:"0" AND searchable:"1" AND nodeName:searchTextAsWildCard*)

Lucene query

Lucene Query Syntax

Lucene has a custom query syntax for querying its indexes. Here are some query examples demonstrating the query syntax.

Keyword matching

Search for word "foo" in the title field.

title:foo

Search for phrase "foo bar" in the title field.

title:"foo bar"

Search for phrase "foo bar" in the title field AND the phrase "quick fox" in the body field.

title:"foo bar" AND body:"quick fox"

Search for either the phrase "foo bar" in the title field AND the phrase "quick fox" in the body field, or the word "fox" in the title field.

(title:"foo bar" AND body:"quick fox") OR title:fox

Search for word "foo" and not "bar" in the title field.

title:foo -title:bar

Wildcard matching

Search for any word that starts with "foo" in the title field.

title:foo*

Search for any word that starts with "foo" and ends with bar in the title field.

title:foo*bar

Note that Lucene doesn't support using a * symbol as the first character of a search.

Proximity matching

Lucene supports finding words are a within a specific distance away.
Search for "foo bar" within 4 words from each other.

"foo bar"~4

Note that for proximity searches, exact matches are proximity zero, and word transpositions (bar foo) are proximity 1.
A query such as "foo bar"~10000000 is an interesting alternative to foo AND bar.
Whilst both queries are effectively equivalent with respect to the documents that are returned, the proximity query assigns a higher score to documents for which the terms foo and bar are closer together.
The trade-off, is that the proximity query is slower to perform and requires more CPU.
Solr DisMax and eDisMax query parsers can add phrase proximity matches to a user query.

Range searches

Range Queries allow one to match documents whose field(s) values are between the lower and upper bound specified by the Range Query. Range Queries can be inclusive or exclusive of the upper and lower bounds. Sorting is done lexicographically.

mod_date:[20020101 TO 20030101]

Solr's built-in field types are very convenient for performing range queries on numbers without requiring padding.

Boosts

Query-time boosts allow one to specify which terms/clauses are "more important". The higher the boost factor, the more relevant the term will be, and therefore the higher the corresponding document scores.
A typical boosting technique is assigning higher boosts to title matches than to body content matches:

(title:foo OR title:bar)^1.5 (body:foo OR body:bar)

You should carefully examine explain output to determine the appropriate boost weights.
The official docs for the query parser syntax are here: http://lucene.apache.org/java/3_5_0/queryparsersyntax.html
The query syntax has not changed significantly since Lucene 1.3 (it is now 3.5.0).

Parsing Queries

Queries can be parsed by constructing a QueryParser object and invoking the parse() method.

String querystr = args.length > 0 ? args[0] : "lucene";
Query q = new QueryParser(Version.LUCENE_CURRENT, "title", analyzer).parse(querystr);

Programmatic construction of queries

Lucene queries can also be constructed programmatically. This can be really handy at times. Besides, there are some queries which are not possible to construct by parsing.
Available query objects as of 3.4.0 are:

• BooleanQuery
• ConstantScoreQuery
• CustomScoreQuery
• DisjunctionMaxQuery
• FilteredQuery
• MatchAllDocsQuery
• MultiPhraseQuery
• MultiTermQuery
• PhraseQuery
• RangeQuery
• SpanQuery
• TermQuery
• ValueSourceQuery

Use the BooleanQuery object to join and nest queries.
These classes are part of the org.apache.lucene.search package.
Here's a simple example:

String str = "foo bar"; String id = "123456";
BooleanQuery bq = new BooleanQuery();
Query query = qp.parse(str);
bq.add(query, BooleanClause.Occur.MUST);
bq.add(new TermQuery(new Term("id", id), BooleanClause.Occur.MUST_NOT);

 

 

 

All credits goes to:

http://www.lucenetutorial.com/lucene-query-syntax.html

Umbraco and MVC tips

How to access Umbraco Current Node 

public class CommentSurfaceController : SurfaceController
{
    private readonly IUmbracoApplicationContext context;
    public CommentSurfaceController(IUmbracoApplicationContext context)
    {
        this.context = context;
    }
}

Implementation surface controller

I have search for which I want to implement autocomplete. 

My json result will be based on AutoCompleteSearchResult:

 public class AutoCompleteSearchResult
    {
        public string value { get; set; }
        public string data { get; set; }
    }

My controller

  public class SearchController: SurfaceController
    {
        public JsonResult Index(RenderModel model)
        {

            var result = new List<AutoCompleteSearchResult>();

            result.Add(new AutoCompleteSearchResult(){data = "1", value = "test1"});
            result.Add(new AutoCompleteSearchResult(){data = "2", value = "test2"});
            result.Add(new AutoCompleteSearchResult(){data = "3", value = "test3"});


            return new JsonResult(){Data = result, JsonRequestBehavior = JsonRequestBehavior.AllowGet};
        }
    }

 

If you are using surface controllers your link has to have prefix:

umbraco/surface/ 

Access the action on url

http://yourserver/umbraco/surface/Search/index/ 

Binding tip

If you are binding RenderMvcController to controller make sure that 

DataType and Controller name are the same

SearchPage , SearchPageController

Load data using uQuery

umbraco.NodeFactory.Node node = uQuery.GetNodesByName("Page Name")
    .Where(n => n.NodeTypeAlias == "NodeTypeAlias").FirstOrDefault();

if (node != null)
{
    //...
}

GetNodesByType(string or int):

umbraco.NodeFactory.Node node = uQuery.GetNodesByType("NodeTypeAlias")
    .Where(n => n.Name == "Page Name").FirstOrDefault();

if (node != null)
{
    //...
}