And also the code if you don't want to download it:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using NHibernate;
using Umbraco.Framework;
using Umbraco.Framework.Context;
using Umbraco.Framework.Localization;
using Umbraco.Framework.Localization.Configuration;
using Umbraco.Framework.Persistence.Model.Attribution.MetaData;
using Umbraco.Framework.Persistence.NHibernate;
using Umbraco.Framework.Persistence.NHibernate.Dependencies;
using Umbraco.Framework.Persistence.NHibernate.OrmConfig;
using Umbraco.Framework.Persistence.ProviderSupport._Revised;
using Umbraco.Framework.Persistence.RdbmsModel.Mapping;
using Umbraco.Framework.Tasks;
using Umbraco.Framework.TypeMapping;
using Umbraco.Hive;
using Umbraco.Hive.Configuration;
using Umbraco.Hive.ProviderSupport;
using Configuration = NHibernate.Cfg.Configuration;

namespace WorkingWithHive
{
    public class HiveManagerWrapper
    {
        public IHiveManager GetHiveManager()
        {
            IFrameworkContext frameworkContext = GetFrameworkContext();

            var providerMetadata = new ProviderMetadata("p__nhibernate", new Uri("content://"), true, false);

            var builder =
                new NHibernateConfigBuilder(
                    ConfigurationManager.ConnectionStrings["DatabaseConnString"].ConnectionString,
                    "unit-tester", SupportedNHDrivers.MsSql2008, "call", false, false);

            NhConfigurationCacheKey cacheKey;
            Configuration config = builder.BuildConfiguration(false, out cacheKey);

            var nhHelper = new NhFactoryHelper(config, null, false, false, frameworkContext);
            ISession session;
            nhHelper.GenerateSessionAndTransaction(false, out session);

            ProviderDependencyHelper dependencyHelper = new DependencyHelper(nhHelper, providerMetadata);
            var revisionRepositoryFactory = new RevisionRepositoryFactory(providerMetadata, frameworkContext,
                                                                          dependencyHelper);

            var revisionSchemaSessionFactory = new NullProviderRevisionRepositoryFactory<EntitySchema>(providerMetadata,
                                                                                                       frameworkContext);
            var schemaRepositoryFactory = new SchemaRepositoryFactory(providerMetadata, revisionSchemaSessionFactory,
                                                                      frameworkContext, dependencyHelper);

            ProviderSetup singleWriter = GetWriterProvider(providerMetadata, revisionRepositoryFactory,
                                                           schemaRepositoryFactory, frameworkContext, dependencyHelper);

            ReadonlyProviderSetup singleReader = GetReaderProvider(providerMetadata, revisionRepositoryFactory,
                                                                   schemaRepositoryFactory, frameworkContext,
                                                                   dependencyHelper, config);
            IHiveManager hive = new HiveManager(new[]
                                                    {
                                                        new ProviderMappingGroup(
                                                            "test",
                                                            new WildcardUriMatch("content://"),
                                                            singleReader,
                                                            singleWriter,
                                                            frameworkContext)
                                                    }, frameworkContext);
            return hive;
        }

        private ReadonlyProviderSetup GetReaderProvider(ProviderMetadata providerMetadata,
                                                        RevisionRepositoryFactory revisionRepositoryFactory,
                                                        SchemaRepositoryFactory schemaRepositoryFactory,
                                                        IFrameworkContext frameworkContext,
                                                        ProviderDependencyHelper dependencyHelper, Configuration config)
        {
            AbstractReadonlyEntityRepositoryFactory readonlyEntityRepositoryFactory =
                new EntityRepositoryFactory(providerMetadata, revisionRepositoryFactory, schemaRepositoryFactory,
                                            frameworkContext, dependencyHelper);

            var readonlyUnitFactory = new ReadonlyProviderUnitFactory(readonlyEntityRepositoryFactory);
            AbstractProviderBootstrapper bootstrapper = new ProviderBootstrapper(config, null);
            var singleReader = new ReadonlyProviderSetup(readonlyUnitFactory, providerMetadata, frameworkContext,
                                                         bootstrapper, 0);
            return singleReader;
        }

        private ProviderSetup GetWriterProvider(ProviderMetadata providerMetadata,
                                                RevisionRepositoryFactory revisionRepositoryFactory,
                                                SchemaRepositoryFactory schemaRepositoryFactory,
                                                IFrameworkContext frameworkContext,
                                                ProviderDependencyHelper dependencyHelper)
        {
            var entityRepositoryFactory = new EntityRepositoryFactory(providerMetadata, revisionRepositoryFactory,
                                                                      schemaRepositoryFactory, frameworkContext,
                                                                      dependencyHelper);
            var unitFactory = new ProviderUnitFactory(entityRepositoryFactory);
            var singleWriter = new ProviderSetup(unitFactory, providerMetadata, frameworkContext, null, 0);
            return singleWriter;
        }

        private IFrameworkContext GetFrameworkContext()
        {
            TextManager textManager = LocalizationConfig.SetupDefault();
            MappingEngineCollection typeMappers = GetTypeMappers();

            AbstractScopedCache scopedCache = new DictionaryScopedCache();
            AbstractApplicationCache applicationCache = new HttpRuntimeApplicationCache();
            AbstractFinalizer finalizer = new NestedLifetimeFinalizer();
            var taskMgr = new ApplicationTaskManager(Enumerable.Empty<Lazy<AbstractTask, TaskMetadata>>());
            IFrameworkContext frameworkContext = new DefaultFrameworkContext(textManager, typeMappers, scopedCache,
                                                                             applicationCache, finalizer, taskMgr);

            return frameworkContext;
        }

        private MappingEngineCollection GetTypeMappers()
        {
            var webmModelMapper = new RdbmsModelMapper(null, null);
            var binders = new List<Lazy<AbstractMappingEngine, TypeMapperMetadata>>();
            var metadata = new TypeMapperMetadata(true);
            var bind = new Lazy<AbstractMappingEngine, TypeMapperMetadata>(() => webmModelMapper, metadata);
            binders.Add(bind);
            binders.Add(new Lazy<AbstractMappingEngine, TypeMapperMetadata>(() => new ManualMapperv2(null, null),
                                                                            metadata));
            var typeMappers = new MappingEngineCollection(binders);
            typeMappers.Configure();
            return typeMappers;
        }
    }
}

Very nice Alex, thanks alot for posting this. I used your project as is and added a SqlCe database from a site, worked nice.

I added a Umbraco.sdf from a test-site I had + reference to the sqlce 4 dll. For some reason I'm not really sure I had to reference to the explicit version in web config, so here's what it finally turned out looking like:

  <?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="DatabaseConnString" connectionString="Data Source=Umbraco.sdf" providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <qualifyAssembly partialName="System.Data.SqlServerCe" fullName="System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </assemblyBinding>
  </runtime>
</configuration>

The Umbraco.sdf needs to be in the root and have "copy always" with this confs. Can obviously be set to a fixed path if one wants it to be kept as is.

is working on a reply...