QBO 3 Blog

Subpage Listing

Musings and rantings about QBO 3.

Publishing Data-Tier Components

posted Jul 28, 2016, 5:54 PM by Eric Patrick   [ updated Jul 28, 2016, 5:55 PM ]

Quandis uses a Microsoft Visual Studio SQL Project (.sqlproj) file to manage data tier components.

Publishing with qbo.DB


Attachment/UploadChunk on a server farm

posted Mar 24, 2016, 6:02 AM by Eric Patrick   [ updated Apr 6, 2016, 5:39 AM ]

The Attachment/UploadChunk method supports efficient upload of files using HTML5 features. In a load-balanced server farm without sticky sessions, this method must ensure that chunks of a file received by different servers are appended to the same physical file. To ensure this, the UploadChunk method must leverage a FileObject that implements an Append method. As of the time of this post, the only standard FileObject that supports Append is UNCFile.

UploadChunk will work as follows:
  • if the application setting ScratchpadFileObject is not empty, then a file object matching ScratchpadFileObject will be used
  • otherwise, the first UNCFile-based FileObject will be used
Once all the chunks have been appended to the scratchpad file, the application will call Attachment/Queue/MoveFile to move the file from the scratchpad to a 'final' repository (typically S3). Note that we queue this move, instead of executing it, so the user does not need to wait for a large files to be moved between repositories. A very large file move may result in an HTTP timeout.

If the file is accessed before the move happens, it is simply read from the scratchpad file object, as opposed to the 'final' repository.

Migrating State Transitions from Roles to Permissions

posted Mar 26, 2015, 8:15 AM by Eric Patrick

Early version of the ObjectState table included RoleRequired, enabling a power user to determine which role was required to effect a state transition.  This has been deprecated in favor of PermissionRequired, enabling a power use to grant a permissions to a specific transition to multiple roles.

To migrate from one to the other, run the query below. It will:
  • Create SystemFunctions based on {ImportFormTemplate}{StatusFrom}To{StatusTo}
  • Grant the appropriate Role permission to the function
  • Set the ObjectState.PermissionRequired column to the new value


DECLARE @T TABLE (ID int, RoleRequired nvarchar(50), Permission nvarchar(50))
INSERT INTO @T 

SELECT
ObjectStateID,
RoleRequired,
REPLACE(
REPLACE(
REPLACE(LEFT(ImportFormTEmplate, 50-LEN(ISNULL(LEFT(StatusFrom,15), 'None'))-LEN(ISNULL(LEFT(StatusTo,15), 'None'))) + ISNULL(LEFT(StatusFrom, 15), 'None') + 'To' + ISNULL(LEFT(StatusTo,15), 'None'), ' ', ''),
'/', ''
),
'-', ''
)
FROM ObjectState
INNER JOIN ImportFormTemplate
ON ImportFormTemplateID = ObjectState.TemplateID
WHERE RoleREquired IS NOT NULL
AND PermissionRequired IS NULL

SELECT * FROM @T

INSERT INTO SystemFunction (SystemFunction)
SELECT Permission FROM @T AS T WHERE NOT EXISTS (
SELECT 1 FROM SystemFunction WHERE SystemFunction = Permission
)

-- 
-- INSERT INTO SystemPermission (SystemRoleID, SystemFunctionID, UpdatedPersonID, UpdatedDate)
SELECT SystemRole.SystemRoleID,
SystemFunction.SystemFunctionID, 
1,
GETDATE()
FROM @T AS T
INNER JOIN SystemRole
ON SystemRole.SystemRole = T.RoleREquired
LEFT OUTER JOIN SystemFunction
ON SystemFunction.SystemFunction = T.Permission
WHERE NOT EXISTS (
SELECT 1 FROM SystemPermission
WHERE SystemRoleID = SystemRole.SystemRoleID
AND SystemFunctionID = SystemFunction.SystemFunctionID
)

UPDATE ObjectState
SET PermissionRequired = Permission
FROM @T AS T
INNER JOIN OBjectState
ON ObjectSTateID= T.ID

AWS Load Balancer and SSL Updates

posted Mar 9, 2015, 11:12 AM by Eric Patrick

Over the past year, a flurry of SSL vulnerabilities have been announced. Fortunately, the AWS team does a good job keeping up with these on our behalf.  To update an AWS load balancer to meeting up-to-date SSL requirements:

1. Navigate to the load balancer, and select the Listeners tab:


2. Click on the Cipher link, and choose the latest security policy:


3. Click Save.



QBO3: Publishing Configuration Between Sites

posted Mar 5, 2015, 4:03 PM by Kevin Cassidy

If you have multiple environments (DEV, UAT, PROD, etc.) You can publish configuration data between the different sites.

One time setup for the target system:
  • Navigate to Design / Specifications.
  • Run the test spec for Import / Setup.
    • This only needs to be done once per target system.
    • This ensures the QBO ImportFileTemplate exists for publishing.
One time setup for the source system:
  • Navigate to Design / Configuration / Modules / System Default.
  • Click on the "Publish URL" panel.
  • Click on the "Options" menu and choose "New Type".
  • Enter the url of the target system in the "Type" field.
    • ex. https://www.quandis.com/Import/Import.ashx/QBO
  • Enter a description of the environment in the "Label" field.
  • Click "Save".
From configured "Home" pages, you can publish templates, configuration overrides, matrices, etc.

Publishing from the source system:
  • Navigate to the Objects "Home" page.
  • Search for the data to publish.
  • Click on the "Options" menu and choose to publish to your configured end point.

RESTHooks

posted Feb 5, 2015, 11:07 AM by Eric Patrick

This blog post is intended to be a working draft to discuss the implementation of RESTHooks in QBO3.

QBO3 needs to implement RESTHooks, so third parties can be notified of when an async operation completes.

Sample use cases include:
  • Call ImportFile/Queue/Import, let me know when done
  • Call Decision/Save, let me know when the decision completes
  • Call ImportForm/Save, let me know when the task is completed
  • Call Attachment/Queue/Generate, let me know when the operation is done
  • Call {ClassName}/Queue/{Operation}, let me know when the operation is done
Provide RESTHook callback parameters:
  • Add them to the query string?
  • Add them as an HTTP header?
RESTHook actions:
  • Make an HTTP GET
  • Make an HTTP POST
  • Send an Email
  • Send an SMS
RESTHook payload:
  • A serialized object?  (E.g. ImportFile/Select?)
  • A serialized IQueue message?
  • An empty payload, but string-substitute parameters
What a RESTHook is NOT:
  • a mechanism to replace IService for complicated operations (e.g. no extra query lookups or data transformations when invoking the RESTHook)

Sample Use Case: Matrix/AsyncLookup

Matrix/AsyncLookup is used to bulk-process matrix calls.  The body of the post is an XML document containing Matrix inputs; we probably don't want to force a third party developer to mix their RESTHook into that.  

Thus, we could do the following on the request:

RESTHook on the query string:

GET http://localhost/Application/Matrix.ashx/AsyncLookup?Matrix=ABC 123&RESTHook=smtp:someone@example.com HTTP/1.1
Host: localhost
Connection: keep-alive
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
...

RESTHook in an HTTP Header:

GET http://localhost/Application/Matrix.ashx/AsyncLookup?Matrix=ABC 123 HTTP/1.1
Host: localhost
Connection: keep-alive
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
X-REST-Hook: smtp:someone@example.com
...

In either case, en email would go out to someone@example.com with some sort of body.

We might extend the queuing infrastructure to handle RESTHooks by considering them a callback.  Roughly:
  • HttpHandler: if we see a RESTHook header, inject a callback based on the operation
  • ?

QBO3 Database Deployments

posted Jul 25, 2014, 10:38 AM by Eric Patrick   [ updated Jul 27, 2014, 1:26 PM ]

Background

A Visual Studio Database project 'qbo.Db' has been created in Source\qbo.3\qbo.Db. This project contains all database components required for a 'fresh' QBO 3 installation, and can be used to upgrade QBO 2 and QBO 3 systems to a current data structure.

Publication

To create a 'fresh' database:
  • Open the qbo.Db solution
  • Right-click on the Standard project, and choose Publish
  • From the Publish screen, create a connection to the SQL Server that you wish to install the database to
    • you need to pre-select an existing database; don't worry, you'll be able to choose the name of your new database before you publish
    • test your connection
  • From the Publish screen, enter the name of your database (e.g. uatclient.quandis.net)
  • Click Publish
To update an existing database, follow the instructions above, but choose an existing database instead of enter a new database name.

Notes

The Visual Studio database projects are very powerful; they allow definition of file groups, extended properties, and installation of CLR modules.

When publishing a database, the project will generate a schema from source code, and if you are updating an existing database, it will also generate a schema from the target database. Publication to an existing database is done by comparing the schemes, generating a change script, and running the change script against the target db.  If you prefer, when you publish, you can just generate the change script and inspect that prior to running it.

Creating the 'Standard' DB on TAURUS2UAT over the VPN took a little more than 7 minutes (7:12).

Snapshots

Project "snapshots" create .dacpac files that can be deployed to create new or updated existing databases.  A .datpac is the database equivalent to a web publication package (.zip file with all the web components).  When deploying a .datpac, a tool called SqlPackage.exe will compare the .datpac schema to the target database schema, build an upgrade script, and deploy.

To deploy a .dacpac directly, open a CMD or Powershell window in a folder containing SqlPackage.exe:

PS C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120> .\sqlpackage.exe
  /action:publish

  /sourcefile:E:\Source\qbo.3\qbo.Db\Standard\Snapshots\Standard_20140727_15-00-49.dacpac
  /targetServerName:minbar
  /targetdatabaseName:testdb.quandis.net
  /variables:FileDataPath=e:\data\
  /variables:FileBackupPath=e:\data\
  /variables:FileBlobPath=e:\data\
  /variables:FileHistoryPath=e:\data\
  /variables:FileLogPath=e:\data\
  /variables:FileIndexPath=e:\data\

To view prospective changes and their impact, use action:deployReport and specify an outputPath:

PS C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120> .\sqlpackage.exe 
  /action:deployReport
  /outputPath:.\qboTest.Deploy.log
  /sourcefile:E:\Source\qbo.3\qbo.Db\Standard\Snapshots\Standard_20140727_15-00-49.dacpac 
  /targetServerName:minbar 
  /targetdatabaseName:testdb.quandis.net 
  /variables:FileDataPath=e:\data\ 
  /variables:FileBackupPath=e:\data\ 
  /variables:FileBlobPath=e:\data\ 
  /variables:FileHistoryPath=e:\data\ 
  /variables:FileLogPath=e:\data\ 
  /variables:FileIndexPath=e:\data\

Items to note:
  • the Standard.sqlproj file what was used to build this .datpac includes several required project variables; each is specified with a /variables:{variable}={value} switch
  • you can specify a sql username and password; in this example, NTLM authentication was used, so no explicit credentials are required
  • carriage returns were added for readability

Behavior: OperationSignature

posted Jul 7, 2014, 2:18 PM by Kevin Cassidy   [ updated Jul 14, 2014, 4:56 PM ]

Parameters from methods, services, and statements can be rendered for the user to choose from.

<i class="icon-list" data-behavior="OperationSignature" data-operationsignature-options="{{
    'basemoduleid': 'MethodObject', 
    'baseoperationid': 'MethodOperation',
    'baseparameterid': 'MethodParameters'
    'moduleid': 'Object', 
    'operationid': 'Operation',
    'parameterid': 'Parameters'
    'appliesto': '{AppliesTo}'
}}"/>

The options passed to the behavior comprise:
  • basemoduleid: id of html element that has the method class name
  • baseoperationid: id of html element that has the method operation
    • Default is Summary
  • baseparameterid: id of html element in which the method parameters should be set
  • moduleid: id of html element that has the operation class name
  • operationid: id of html element that has the operation
  • parameterid: id of html element in which the operation parameters should be set
  • url: URL of method to supply list of parameters
    • Default is Application/ObjectConfiguration.ashx/FieldList
  • appliesto: base object referenced by operation lookup
Use case:
  • Workflow steps

Behavior: MethodSignature

posted Jul 7, 2014, 10:48 AM by Kevin Cassidy   [ updated Jul 14, 2014, 4:57 PM ]

Parameters can easily be configured by a user for method, services, and statement calls.

<i class="icon-list" data-behavior="MethodSignature" data-methodsignature-options="{
    'moduleid': 'MethodObject',
    'operationid': 'MethodOperation',
    'parameterid': 'MethodParameters'
}"/>

The options passed to the behavior comprise:
  • moduleid: id of html element that has the method class name
  • operationid: id of html element that has the method operation
  • parameterid: id of html element in which the parameters should be set
  • url: URL of method to supply list of parameters
    • Default is Application/ObjectConfiguration.ashx/ParameterList
Defining parameters:
  • Method parameters
    • Use the method comment section to define items with term and description attributes
    • AbstractObject/Pivot as example
  • Statement parameters
    • Provide a Description attribute
  • Service parameters
    • Provide Parameters/Parameter nodes, as with Statements, to define parameters for a Service
    • Provide a Description attribute
  • Fields
    • Field description is available when commented in the {Object}.Fields.cs appropriately
Use case:
  • Template custom methods
  • Workflow steps

Behavior: DataList

posted May 28, 2014, 12:32 PM by Kevin Cassidy

Objects from the database can be rendered as a datalist for input fields using the "DataList" behavior:

<input type="text" class="validate-email" list="RecipientList" data-behavior="DataList" data-datalist-options="{{
    'object': 'Process',
    'objectid': '1',
    'prefix': 'Recipients',
    'field': 'MessageRecipient',
    'validate': true,
    'data': {{
        'Category': '0'
    }}
}}"/>

The options passed to the behavior comprise:
  • object: the parent used to invoke the 'method'
  • objectid: the parent id used to invoke the 'method'
  • minchar: when 'static' is set to false, the minimum number of characters needed to invoke the 'method'
    • Default is 1
  • display: the number of results to populate the datalist with
    • Default is 25
  • static: when set to false, will render the list dynamically based on user key strokes
    • Default is true
  • method: the method to invoke against the parent
    • Default is 'RecipientByAccess'
  • include: optional parameter passed to the 'method'
  • exclude: optional parameter passed to the 'method'
  • prefix: when a value is present will create multiple hidden elements based on the 'method' results and 'data' properties.  Used when creating multiple hidden elements which should be passed back to the server such as child classes
  • field: when 'prefix' is not specified will create a single hidden element with the value selected from the datalist
  • validate: when true will attempt to validate the field using the QBO3 validation behavior
    • Default is false
  • color: sets the background color for the token
  • data: additional hidden elements to be rendered when a datalist item is selected 
Use case:
  • Adding a message: render eligible recipients for To/Cc/Bcc addresses.

1-10 of 57

Comments