QBO 3 Blog

Subpage Listing

Musings and rantings about QBO 3.
Subscribe to posts

AWS and TinyProxy

posted Feb 17, 2018, 4:27 PM by Eric Patrick

AWS EC2 instances can be used as proxy servers. To start a proxy server:
  1. From the AWS EC2 Dashboard, select AMIs
  2. From the TinyProxy AMI, right click and choose Launch
  3. Select the General Purpose t2.micro instance
  4. Configuration instance details:
    • Network: choose a VPC (PlayGround2 recommended)
    • Subnet: choose any subnet
    • Auto-assign Public IP: choose 'Enable'
    • choose Review and Launch
  5. Review Instance Launch
    • Edit Security Groups, and choose the Launch Wizard 11 security group
    • Edit Tags, and add a tag: Description = Proxy (just to make it easy to find from the EC2 search bar)
    • Choose Launch
From the EC2 Security Groups panel:
  • Find the launch-wizard-11 group
  • From the Inbound tab, Edit the rules
  • Add a "Custom TCP Rule", port 8888, source the IP address of the machine that needs to use the proxy server
  • Save
From the EC2 Instances panel:
  • Search for 'proxy'
  • For any new instances, click on the instance, and see it's Public IP address
  • Use this public IP address as a proxy server from Windows, HttpWebRequest, or any code that enables a custom proxy.


Configuration of the TinyProxy image was based on the following references.
Issues: 

SQL Constraints CHECK, NOCHECK, WITH CHECK, WITH NOCHECK

posted Aug 15, 2017, 12:12 PM by Kevin Foley

From a 2015 email...

I was researching disabling constraints and came across the posts below, which lead me to an OH MY moment.

http://geekswithblogs.net/dturner/archive/2011/01/31/sql-constraints-check-and-nocheck.aspx

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

http://sqlwithmanoj.com/2012/03/14/nocheck-vs-with-nocheck-while-creating-disabled-foreign-key-fk-constraint/

https://msdn.microsoft.com/en-us/library/ms190273(v=sql.105).aspx

To summarize, constraint parameters "NOCHECK" and "WITH NOCHECK" are two different things, as are their brethren "CHECK" and "WITH CHECK"

"WITH CHECK" and "WITH NOCHECK" determine if the constraint will be enforced on existing rows at the time of constraint is created or modified.

"CHECK" and "NOCHECK" determine if the constraint is enabled or disabled.  Microsoft should have just named these parameters ENABLE and DISABLED

You can't create a disabled constraint with "NOCHECK", but it can be disabled after creation by using "NOCHECK"

So what is the problem?

When a constraint is modified or created using "WITH NOCHECK" the SQL optimizer ignores the constraint which can result in table scans.  From the MS library...

The query optimizer does not consider constraints that are defined WITH NOCHECK. 
Such constraints are ignored until they are re-enabled by using 
ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL.

Ouch !!

The queries below will list un-trusted constraints where "WITH NOCHECK" should probably be removed

SELECT *
from sys.check_constraints
WHERE is_not_trusted = 1

SELECT *
from sys.foreign_keys
WHERE is_not_trusted = 1

If the number of un-trusted foreign keys is extremely high then the performance impact has to be considered.

The command below will do an alter table on all tables and constraints to issue a WITH CHECK CHECK.  It will enable the constraint and force a check of all rows.

exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

This would be fine to run on an empty DB, but a little scary to run on a large DB.

It would be better to check the constraints first via executing the output of the below - again be aware that on large DBs this may take a while 

exec sp_MSforeachtable @command1="PRINT '?';  DBCC CHECKCONSTRAINTS ([?])

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

1-10 of 58

Comments