XsltDb Database Access Concepts

XsltDb is a multiportal module. Therefore, it provides an access to the DotNetNuke database and allows to setup a separate portal dedicated database. So each portal can have it’s own MS SQL Server database instance with it’s own schema.

Mode 1. Portal Admin
  • Has restricted access to the DoNetNuke database via mdo:xml extension.
  • Has unrestricted access to the dedicated portal database via mdo:sql.
Mode 2. Super User
  • Has unrestricted access to the DotNetNuke database via mdo:sql (mdo:xml also works).
  • Has unrestricted access to the dedicated portal database via mdo:sql.

By default XsltDb module is created as admin module and works in mode 1. But if you are logged as a super user you can check a "Host Module" flag to switch to mode 2. If you are building a single site and you not need multisite features you just create all modules as Host modules and access database with mdo:sql extension.

In the Super User mode module can use mdo:sql to access both DNN and dedicated database. If SQL statement starts with semicolon (:) XsltDb would query DNN database. otherwise it queries dedicated database. If a portal has no dedicated database mdo:sql always queries DNN database (in admin mode error is rised).

Dedicated Database Setup

  • Create a dedicated MS SQL Server database for portal.
  • Create a dedicated windows user for portal. You must deny access to DNN folder (except portal's home directory). Access to the DNN database must bee prohibited too. However, this user must have db_owner access to it's dedicated database.
  • Setup user name, password and connection string for particular portal in XsltDb settings (Findy_XsltDb_PortalSettings table).
After that you can safely allow administrators of portals to use dedicated databases without restrictions via mdo:sql extension.
XsltDb does not provide tools for setting up dedicated account and database. But you can setup it directly through database or use the following editor (just put the code in XsltDb module).

<xsl:variable name="portals-sql">
    select p.PortalID, p.PortalName, pa.HTTPAlias
    from {databaseOwner}[{objectQualifier}Portals] p
    join {databaseOwner}[{objectQualifier}PortalAlias] pa on pa.PortalID = p.PortalID
</xsl:variable>

<xsl:variable name="select-sql">
    select *
    from {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalSettings]
    where PortalID = @PortalID
    for xml path('p')
</xsl:variable>

<xsl:variable name="update-sql">
    if exists(select * from {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalSettings] where PortalID = @PortalID)
      update {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalSettings] set
        xLogin = @xLogin,
        xDomain = @xDomain,
        xPassword = @xPassword,
        sqlConnectionString = @sqlConn
      where
        PortalID = @PortalID
    else
      insert {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalSettings](
          PortalID,  xLogin,  xDomain,  xPassword, sqlConnectionString) values(
       @PortalID, @xLogin, @xDomain, @xPassword, @sqlConn);
</xsl:variable>

<xsl:variable name="result">
  <xsl:if test="mdo:param('@save')=1">
    <xsl:copy-of select="mdo:sql($update-sql, '$script',
      '@PortalID',  mdo:param('@CurrentPortal'),
      '@xLogin',    mdo:request('xLogin'),
      '@xDomain',   mdo:request('xDomain'),
      '@xPassword', mdo:request('xPassword'),
      '@sqlConn',   mdo:request('sqlConn')
      )/root" />
  </xsl:if>
</xsl:variable>

    <b>Portals on this node:</b>
    <br/>
    <br/>
    <table style="width:100%">
      <tr>
        <th style="width:50px">ID</th>
        <th style="width:200px">Name</th>
        <th style="width:200px">Alias</th>
        <th/>
      </tr>
      <xsl:for-each select="mdo:sql($portals-sql,'p')/root/p">
        <tr>
          <td>{{PortalID}}</td>
          <td>
            <a href="{mdo:jajax('@CurrentPortal', PortalID)}">{{PortalName}}</a>
          </td>
          <td>{{HTTPAlias}}</td>
        </tr>
        <xsl:if test="mdo:param('@CurrentPortal') = PortalID">
          <tr>
            <td colspan="4">

              <xsl:if test="($result!='ok') and ($result != '')">
                <span style="color:red">{{$result}}</span>
                <br /><br /><br />
              </xsl:if>

              <xsl:if test="mdo:param('@CurrentPortal')!='' and $result!='ok'">
                <xsl:variable name="_values">
                  <xsl:choose>
                    <xsl:when test="$result=''">
                      <xsl:copy-of select="mdo:sql($select-sql, '$xml', '@PortalID', mdo:param('@CurrentPortal'))/p" />
                    </xsl:when>
                    <xsl:otherwise>
                      <p>
                        <PortalID>{{mdo:param('@CurrentPortal')}}</PortalID>
                        <xLogin>{{mdo:request('xLogin')}}</xLogin>
                        <xDomain>{{mdo:request('xDomain')}}</xDomain>
                        <xPassword>{{mdo:request('xPassword')}}</xPassword>
                        <sqlConnectionString>{{mdo:request('sqlConn')}}</sqlConnectionString>
                      </p>
                    </xsl:otherwise>
                  </xsl:choose>
                </xsl:variable>

                <xsl:variable name="values" select="msxsl:node-set($_values)/p" />

                <table style="width:100%">
                  <tr>
                    <td style="width:150px"/>
                    <td/>
                  </tr>
                  <tr>
                    <td>User name:</td>
                    <td>
                      <input type="text" name="xLogin" value="{$values/xLogin}" style="width:150px"/>
                    </td>
                  </tr>
                  <tr>
                    <td>Domain name:</td>
                    <td>
                      <input type="text" name="xDomain" value="{$values/xDomain}" style="width:150px"/>
                    </td>
                  </tr>
                  <tr>
                    <td>Password:</td>
                    <td>
                      <input type="password" name="xPassword" value="{$values/xPassword}" style="width:150px"/>
                    </td>
                  </tr>
                  <tr>
                    <td>Connection string:</td>
                    <td>
                      <input type="text" name="sqlConn" value="{$values/sqlConnectionString}" style="width:100%"/>
                    </td>
                  </tr>
                </table>
                <br />
                <a href="{mdo:jajax('@save', 1, '@CurrentPortal', mdo:param('@CurrentPortal'))}">Update</a> |
                <a href="{mdo:jajax()}">Close</a>
                <br /><br /><br />
              </xsl:if>

            </td>
          </tr>
        </xsl:if>
      </xsl:for-each>
    </table>

Last edited Feb 22, 2010 at 11:39 AM by findy, version 1

Comments

No comments yet.