Empty a Postgres database without dropping it

Posted on by Jeff Varszegi

In PostgreSQL, it may be non-obvious to delete certain database objects within a schema cleanly as part of a drop-and-create initial script for a system, especially in environments where it’s less than desirable to drop and create the entire database. The script below accomplishes this.

DO $$ DECLARE
currentSchema   VARCHAR(100);
objectname      VARCHAR(1000);
sqlstatement    VARCHAR(1000);
BEGIN

currentSchema := 'yourschemanamehere';

-- 1. Drop views
FOR objectname IN (SELECT viewname FROM pg_views WHERE schemaname = currentSchema)
LOOP
EXECUTE 'DROP VIEW IF EXISTS ' || currentSchema || '."' || objectname || '" CASCADE';
END LOOP;

-- 2. Drop sequences
FOR objectname IN (SELECT sequencename FROM pg_sequences WHERE schemaname = currentSchema)
LOOP
EXECUTE 'DROP SEQUENCE IF EXISTS ' || currentSchema || '."' || objectname || '" CASCADE';
END LOOP;

-- 3. Drop tables
FOR objectname IN (SELECT tablename FROM pg_tables WHERE schemaname = currentSchema)
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || currentSchema || '."' || objectname || '" CASCADE';
END LOOP;

-- 4. Drop functions / procedures
FOR sqlstatement IN (
SELECT
'DROP '
|| CASE pp.prokind
WHEN 'f' THEN 'FUNCTION'
WHEN 'a' THEN 'AGGREGATE'
WHEN 'p' THEN 'PROCEDURE'
WHEN 'w' THEN 'FUNCTION'
END
|| ' IF EXISTS ' || currentSchema || '."' || pp.proname || '"(' || pg_catalog.pg_get_function_identity_arguments(pp.oid) || ') CASCADE' AS sqlstatement
FROM pg_proc pp
JOIN pg_namespace pn on pp.pronamespace = pn.oid
WHERE pn.nspname = currentSchema
)
LOOP
EXECUTE sqlstatement;
END LOOP;

END $$;

 

Advertisement

Musings on extended stored procedures and the cloud

Posted on by Jeff Varszegi

(The term “extended stored procedure” as used herein is shorthand for the general technique, available in many modern RDBMSes including Microsoft SQL Server, Oracle, MySQL et al., of linking in compiled code written in non-SQL languages for invocation as stored procedures, functions, and the like.)

I recently ran into a newfangled version of DLL hell (an example support post; there are many). The basic issue was that in a SQL Server CLR stored procedure of ours, the production host machines had a different version of some .NET assemblies in the GAC, or Global Assembly Cache. It was a platform assembly causing the issue, in this case resolved by running the following statement in each affected database:

ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;

 

While prompted by security measures in Microsoft’s solution, this is not a very elegant or maintenance-free approach. Imagine a future patch bringing down production systems due to a newer version of a DLL suddenly included in GAC, for instance. The cloud makes things even more complex, since having to include even platform DLLs with lowered-safety permissions may make an application difficult or impossible to deploy in a cloud implementation such as Amazon’s RDS.

Unfortunately, there are some highly useful third-party libraries which may in a naive implementation require such extra problematic configuration, such as JSON.NET . Due to understandable security concerns with linking in unvetted code, most modern RDBMSes supporting extended stored procedures will make one jump through extra hoops to use any code which makes system-level calls, can perform I/O, and the like.

Problems like this can be avoided (while still reaping the benefits of stored procedures) with approaches like the following, each with pros and cons:

1. Avoid linking in general utility libraries with references to unsafe code.

2. Create a safe version of reusable utility code for inclusion in heightended-security containers and environments. Store this in a separate project or other compilable unit from the unsafe reusable code.

3. Investigate the use of data-platform features instead of potentially unsafe program code. For example, SQL Server 2016 includes certain features for working with JSON data that might make a library like JSON.NET largely unnecessary in a particular situation.

4. Find safer alternatives to popular libraries. To tack on to the JSON example, small, fast JSON parsers could avoid the need for something like JSON.NET, and even give performance enhancements to boot.

5. If all else fails, consider importing an unsafe library into your codebase and stripping out unsafe calls. This has the obvious drawback of increased code maintenance in case of any necessary patches, but could work for a stable, mature library. Freezing a version in this manner might actually improve the long-term stability of a dependent codebase in some cases.

Of course, this is all really another facet of planning for the cloud. In my current organization, we’re shifting away from using proprietary software like SQL Server in favor of open-source alternatives like MySQL, MariaDB, and MongDB partly for licensing reasons, but just as much for the benefits of broad cross-cloud support. Growing pains for companies shifting more systems to the cloud can be minimized with a shared approach, which explicitly plans a set of reusable, cloud-safe libraries from the start.

Discover all non-abstract types implementing an interface in C#

Posted on by Jeff Varszegi

It’s sometimes useful to auto-discover types in C# and other high-level programming languages, even outside the context of coding an IoC container. In C#, robust type-discovery mechanisms make this easy. Here’s a basic formula for auto-discovery of types implementing an interface:

1. Get the list of assemblies in the application domain.
2. Get the types from each discovered assembly.
3. Test each type to see whether it implements the target interface.

This basic formula can be accomplished in a snap using Linq:

var targetType = typeof(someInterface);
var types =
    AppDomain.CurrentDomain.GetAssemblies()
    .SelectMany(a => a.GetTypes())
    .Where(t => t.GetInterfaces().Contains(targetType) && t.IsClass && !t.IsAbstract)
    .OrderBy(t => t.FullName);

Note that t.IsClass (or !t.IsInterface) in the above expression excludes the interface itself, as well as derived interfaces. The .IsAbstract check can be skipped if the goal is to discover all class types for the interface, not just those that can actually be instantiated. Lastly, the basic approach is similar but the calls not identical for .NET Core.

Combine approaches for easy, lightweight JavaScript user information detection

Posted on by Jeff Varszegi

Basic browser/user detection can be important for providing an enhanced user experience. For example, making a best-guess detection of the user’s IP address (subject to the usual caveats about VPN use) can help in pre-selecting a user’s country of origin, enabling faster form fill-outs. The user’s preferred language, especially on first access of a site, is obviously of huge importance; detecting it early with a non-intrusive script can help avoid clunkier approaches such as passing out language-differentiated URLs to globalized versions of your site, which may be passed between users with unintended consequences.

Especially for a site which already makes use of JavaScript, an unobtrusive script can make a difference. And while there are fairly robust libraries such as platform.js, they often do not address all concerns of detecting language, geoIP, and browser. Adding extra dependencies to a site, in terms of maintenance burden and JS payload, is desirable to avoid when possible.

It’s easy enough to create a tiny script which merges freely available geoIP with basic browser/language detection, as shown below. There are many free geoIP web services available these days; a first implementation can simply rely on one, perhaps adding a fallback as necessary, though eventually a more robust solution may become necessary.

The simple demo code, with browser and OS versions not yet filled in, appears below, and may be run at JSFiddle . This could, for example, be passed as a JSON bundle in a call to a web service, or submitted with a page. While the code may be reused freely, as it essentially cobbles together multiple simple well-known methods into an easy-to-use package, the emphasis is on the approach: a lightweight script that avoids heavy dependencies and still captures useful information about a user. As always, use such information responsibly, especially keeping in mind GDPR regulations which will have taken full effect by May 2018.

function UserInfo() {
  var self = this;
  this.language = ((navigator.languages ? navigator.languages[0] : (navigator.language || navigator.userLanguage)) + '').substring(0, 2);
  this.browserName = '';
  this.browserVersion = '';
  this.osName = '';
  this.osVersion = '';
  this.country = '';
  this.ipAddress = '';
  this.city = '';
  this.region = '';
  this.postalCode = '';
  
  if (navigator && navigator.userAgent) {
    var ua = (navigator.userAgent + '').toLowerCase();
    var platform = ua;
    
    var i = platform.indexOf('(');
    if (i > -1)
      platform = platform.substring(i + 1);

    i = platform.indexOf(')');
    if (i > -1)
      platform = platform.substring(0, i);
      
    if (platform.indexOf('windows') > -1)
      this.osName = 'Windows';
    else if (platform.indexOf('android') > -1)
      this.osName = 'Android';
    else if (platform.indexOf('macintosh') > -1 || platform.indexOf('macos') > -1)
      this.osName = 'macOS';
    else if (platform.indexOf('iphone') > -1 || platform.indexOf('ipad') > -1)
      this.osName = 'iOS';
  }

  if (!!window.chrome && !!window.chrome.webstore)
    this.browserName = 'Chrome';
  else if (typeof InstallTrigger !== 'undefined')
    this.browserName = 'Firefox';
  else if (/*@cc_on!@*/false || !!document.documentMode)
    this.browserName = 'IE';
  else if (/constructor/i.test(window.HTMLElement) || (function (p) { return p.toString() === "[object SafariRemoteNotification]"; })(!window['safari'] || safari.pushNotification))
    this.browserName = 'Safari';
  else if ((!!window.opr && !!opr.addons) || !!window.opera || navigator.userAgent.indexOf(' OPR/') >= 0)
    this.browserName = 'Opera';
  else if (!!window.StyleMedia)
    this.browserName = 'Edge';
  
try {
    $.getJSON('//geoip.nekudo.com/api/?callback=?', function(data) {
      self.ipAddress = data.ip + '';
      self.city = data.city + '';
      self.country = data.country.code + '';      
    });
} catch (err) {}

  this.ids = []; 
  
  this.addID = function(sn, st, idt, i) {
    var newID = {};
    newID.systemName = sn + '';
    newID.systemType = st + '';
    newID.idType = idt + '';
    newID.id = i + '';      
    this.ids.push(newID);
  };  
}

 

The minified version is relatively compact, at 1560 characters:

function UserInfo(){var i=this;if(this.language=((navigator.languages?navigator.languages[0]:navigator.language||navigator.userLanguage)+"").substring(0,2),this.browserName="",this.browserVersion="",this.osName="",this.osVersion="",this.country="",this.ipAddress="",this.city="",this.region="",this.postalCode="",navigator&&navigator.userAgent){var e=(navigator.userAgent+"").toLowerCase(),o=e.indexOf("(");o>-1&&(e=e.substring(o+1)),(o=e.indexOf(")"))>-1&&(e=e.substring(0,o)),e.indexOf("windows")>-1?this.osName="Windows":e.indexOf("anddroid")>-1?this.osName="Android":e.indexOf("macintosh")>-1||e.indexOf("macos")>-1?this.osName="macOS":(e.indexOf("iphone")>-1||e.indexOf("ipad")>-1)&&(this.osName="iOS")}window.chrome&&window.chrome.webstore?this.browserName="Chrome":"undefined"!=typeof InstallTrigger?this.browserName="Firefox":document.documentMode?this.browserName="IE":/constructor/i.test(window.HTMLElement)||"[object SafariRemoteNotification]"===(!window.safari||safari.pushNotification).toString()?this.browserName="Safari":window.opr&&opr.addons||window.opera||navigator.userAgent.indexOf(" OPR/")>=0?this.browserName="Opera":window.StyleMedia?this.browserName="Edge":(isChrome||isOpera)&&window.CSS&&(this.browserName="Blink");try{$.getJSON("//freegeoip.net/json/?callback=?",function(e){i.ipAddress=e.ip+"",i.city=e.city+"",i.region=e.region_code+"",i.country=e.country_code+"",i.postalCode=e.zip_code+""})}catch(i){}this.ids=[],this.addID=function(i,e,o,s){var t={};t.systemName=i+"",t.systemType=e+"",t.idType=o+"",t.id=s+"",this.ids.push(t)}}

 

JavaScript: to prototype or not?

Posted on by Jeff Varszegi

A recurring topic of some discussion among JavaScript users is whether to use prototype or instance/constructor-inner methods when defining JavaScript objects.

Prototype methods can be incredibly useful, but are in essence much like C# extension methods (which are incredibly useful for similar reasons). The primary use of both is to add functionality to a class/object type over which one does not have control. In this way, both C# extension methods and JavaScript prototype methods can even add functionality to foundational platform types in each language or environment. (Proponents of using prototype methods for everything sometimes point out that there is a small memory-use difference between prototype and non-prototype functions, but that difference is completely negligible for client-side development.)

Methods defined inside an object constructor, directly on “this”, have a major advantage in correct object-oriented design, however. That’s because methods inside the constructor can also access private properties/variables defined inside the constructor. Prototype methods cannot do this, as this example clearly demonstrates. A minor additional benefit is that instance methods defined this way are contained inside the constructor, a more object-oriented style than the extension-method feel of prototype methods.

This difference means that constructor-inner methods are the only correct choice for constructing object-oriented JavaScript. The alternate, using prototype methods only, would be akin to designing a C# or Java class with no private fields or properties, a clear violation of the encapsulation principle.

It’s clear that at least for client-side development, prototype methods may be useful to decorate a pre-existing API–but when designing one’s own object-oriented JavaScript, there is only one appropriate choice.

Scale text with a minimum size using pure CSS

Posted on by Jeff Varszegi

Creating a responsive design is best done without unnecessary reliance on toolkits, frameworks and the like, especially where basic formatting can be controlled by CSS instead of JavaScript. The set of tools in CSS3 is of course limited compared to that in JavaScript, but one aspect of responsive design–scaling text–can often by performed satisfactorily in pure CSS.

One part of the puzzle is to scale text smoothly according to the size of the viewport. For this, the oft-overlooked vw unit can work wonders. However, without a min-font-size property available in CSS3, simply scaling to zero wouldn’t work in a wide range of situations, and a pure percentage-based scaling without a foundation might also not scale appropriately. In general, for usability purposes it’s best to use the smallest size of text that satisfies both the readability requirement and relative size requirement at the smallest viewport size, and scale up from there.

Luckily, by using the CSS calc() function, we can supply a floor value for font sizes and scale up from there. This provides a functional equivalent for the missing min-font-size, since the vw-based component of the calculated size can safely go to zero, approaching the limit of the constant base size. Using this approach, one might create some classes to apply standard font sizes as in the following, apply them to heading tags, etc.

Try it for yourself! Note that the linked demo includes a moderate amount of viewport height in the calculation as well, to theoretically better fill a proposed space, and also includes the viewport height in scaling the line height for each font size (again above a safe minimum).

.fontSizeXXSmallScaled { font-size:calc(.8em + 1vw); }
.fontSizeXSmallScaled { font-size:calc(.8em + 1.2vw); }
.fontSizeSmallScaled { font-size:calc(.9em + 1.4vw); }
.fontSizeMediumScaled { font-size:calc(1em + 1.5vw); }
.fontSizeLargeScaled { font-size:calc(1.25em + 1.6vw); }
.fontSizeXLargeScaled { font-size:calc(1.5em + 1.7vw); }
.fontSizeXXLargeScaled { font-size:calc(1.75em + 1.8vw); }

Dynamically resize elements sans JavaScript with the help of clutter-free CSS

Posted on by Jeff Varszegi

I’m a big fan of code, including CSS, that’s as easy to use as possible. I’m not a fan of using certain large frameworks such as Bootstrap just to get responsive design onto a site, when something much smaller and simpler will often do. With a few CSS tricks, it’s possible to create CSS containers that will behave responsibly at your chosen viewport break-points, but that can be stripped down to a single class added to a container in many cases.

To that end, observe the following example, which applies table-style formatting at a large/desktop screen size wraps items into a grid-style layout at a medium size, with configurable options for numbers of columns at different numbers of child items; and finally collapses to an item width of 100%. You may also try it for yourself. 

* { box-sizing: border-box }

.columnContainer:after {
   content: " ";
   visibility: hidden;
   display: block;
   height: 0;
   clear: both;
}

@media all and ( min-width: 768px ) {

  .columnContainer { 
    display: table;
    width: 100%;
  }
  .columnContainer > div { 
    display: table-cell;
  }

}

@media all and ( max-width: 767px ) and ( min-width: 481px ) {

  .columnContainer { 
     width: 100%;
  }

  .columnContainer > div {
    float: left;
    width: 100%;
  }

  .columnContainer > div:first-child:nth-last-child(2), .columnContainer > div:first-child:nth-last-child(2) ~ div {  width: 50%;  } 

  .columnContainer > div:first-child:nth-last-child(3), .columnContainer > div:first-child:nth-last-child(3) ~ div {  width: 33.3333%;  } 

  .columnContainer > div:first-child:nth-last-child(4), .columnContainer > div:first-child:nth-last-child(4) ~ div {  width: 50%;  } 
  .columnContainer > div:first-child:nth-last-child(4) ~ div:nth-child(2n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(5), .columnContainer > div:first-child:nth-last-child(5) ~ div {  width: 20%; } 

  .columnContainer > div:first-child:nth-last-child(6), .columnContainer > div:first-child:nth-last-child(6) ~ div {  width: 33.3333%;  } 
  .columnContainer > div:first-child:nth-last-child(6) ~ div:nth-child(3n+1) { clear: both; } 
  
  .columnContainer > div:first-child:nth-last-child(7), .columnContainer > div:first-child:nth-last-child(7) ~ div {  width: 14.2857%; } 

  .columnContainer > div:first-child:nth-last-child(8), .columnContainer > div:first-child:nth-last-child(8) ~ div {  width: 25%;  } 
  .columnContainer > div:first-child:nth-last-child(8) ~ div:nth-child(4n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(9), .columnContainer > div:first-child:nth-last-child(9) ~ div {  width: 33%;  } 
  .columnContainer > div:first-child:nth-last-child(9) ~ div:nth-child(3n+1) { clear: both !important; } 
  
  .columnContainer > div:first-child:nth-last-child(10), .columnContainer > div:first-child:nth-last-child(10) ~ div {  width: 20%;  } 
  .columnContainer > div:first-child:nth-last-child(10) ~ div:nth-child(5n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(11), .columnContainer > div:first-child:nth-last-child(11) ~ div {  width: 9.0909%; } 

  .columnContainer > div:first-child:nth-last-child(12), .columnContainer > div:first-child:nth-last-child(12) ~ div {  width: 25%;  } 
  .columnContainer > div:first-child:nth-last-child(12) ~ div:nth-child(4n+1) { clear: both !important; } 

}

@media all and ( max-width: 480px ) {
  .columnContainer > div { width: 100% }
}

This implementation depends on some CSS3 features working in tandem. At the medium viewport size, by combining the :first-child and :nth-last-child(n) (with following siblings) selectors in tandem, the widths and wrapping for child items can be set specifically based on the number of child items. To create a new row, the nth child is then selected for clearing. An implementation without using floats is also possible; the main technique here is the selection of different layout techniques based on the number of children.

Once this has been implemented, adding a single class, in this case “columnContainer”, on a parent element will hook all this up so it just works. It would also be possible to combine this technique, of course, with named custom classes that applied different widths to columns and the like.

Applying shadow overlays with pure CSS

Posted on by Jeff Varszegi

Shadow or darkening overlays can be useful for providing contrast between a container’s background and content, as well as providing an easy way to differentiate presentation for button mouseovers and similar situations. Older methods of providing such useful inner shadows relied on using an extra div or other container, to which one would apply a color and opacity. However, there’s a better way.

The desired effect can be achieved using two related tricks: the now well-supported opacity feature of rgba colors, as well as the “inset” property of the CSS3 box shadow which applies the box shadow inside an element. The trick is to set the shadow width to greater than half the size of the element (any greater size will do), to ensure that the shadow covers the entire element. Using this approach, one might specify the following to achieve a 50% shading:

box-shadow: rgba(0, 0, 0, .5) 0 0 0 1000000px inset;

Try it for yourself.

Dynamically adding JavaScript and CSS using a resource-tracking object type

Posted on by Jeff Varszegi

This is a quick demonstration of a few techniques in basic JavaScript: creating a custom JavaScript object type with properties and methods; dynamically adding both JavaScript and CSS references in a cross-browser-safe way; adding JavaScript async; and adding inline JavaScript and CSS. The resource loader tracks added resources, to ensure that they are loaded only once. Try it for yourself (open your browser’s developer tools to see logging output).

// A JavaScript type which manages references to scripts and stylesheets
function ResourceManager() {
  var addedReferences = {};
  var addedResources = {};
  
  this.isLoggingEnabled = false;
  
  this.referenceExists = function(url) {
  	if (url in addedReferences) {
    	this.log('Resource ' + url + ' already added');
      return true;
    }
    else return false;
  }
  
  this.resourceExists = function(text) {
  	if (text in addedResources) {
    	this.log('Text resource already added');
      return true;
    }
    else return false;
  }
  
  this.addScriptReference = function(url, addAsync, addDeferred) {
    if (typeof(url) == 'undefined' || this.referenceExists(url)) return;
    this.log('Adding script reference: ' + url + (addAsync ? ' [async]' : '') + (addAsync ? ' [deferred]' : ''));
        
    var script = document.createElement('script');
    script.src = url;
    if (addAsync) script.async = true;
    if (addDeferred) script.defer = true;
    document.head.appendChild(script); 
    
    addedReferences[url] = url;
  };
  
  this.addScript = function(text) {
    if (typeof(text) == 'undefined' || this.resourceExists(text)) return;
    this.log('Adding script');
      
    var script = document.createElement('script');
    script.appendChild(document.createTextNode(text));
    document.head.appendChild(script); 
    
    addedResources[text] = text;
  };
  
  this.addStyleSheetReference = function(url) {
    if (typeof(url) == 'undefined' || this.referenceExists(url)) return;
    this.log('Adding script reference: ' + url + (addAsync ? ' [async]' : '') + (addAsync ? ' [deferred]' : ''));
    
    var ss = document.createElement('link');
    ss.rel = 'stylesheet';
    ss.type = 'text/css';
    ss.href = url;
    head.appendChild(ss);
    
    addedReferences[url] = url;
  };  
    
  this.addStyleSheet = function(text) {
    if (typeof(text) == 'undefined' || this.resourceExists(text)) return;
    this.log('Adding style sheet');
    
    var ss = document.createElement('style');
    ss.type = 'text/css';
    
    if (ss.styleSheet){
      ss.styleSheet.cssText = text;
    } else {
      ss.appendChild(document.createTextNode(text));
    }
      
    document.head.appendChild(ss);  
      
    addedResources[text] = text;
  };    
    
  this.log = function(message) { if (this.isLoggingEnabled) console.log(message); };
}