Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Post a Comment

Embrace The Non-DBO Schema

-
I don't know how it is everywhere, but in my experience, custom schemas (i.e. something other than dbo) have been seldom used, and I think it's a bit of a shame.

First, what's wrong with dbo? Inherently, nothing. But as time passes and databases grow, dbo can become a cluttered place with objects of overlapping interest. Disentangling those objects from one another can be a bit of a pain.

What Do Schemas Address?

Schemas can help with several things, but in my mind, they boil down to three big ones:

  1. Organization
    • Keeping objects that belong in the same "place" together
  2. Isolation
    • Making clear delineations between the responsibilities of the objects in the schema
  3. Management
    • Differentiating permissions
    • Being able to do operations on a specific subset of objects without having to rely on external documentation or naming conventions

How Else Can You Achieve Those Benefits?

There are really three ways you can achieve the points from the previous section, and they sort of fall along a spectrum.

Small Addition: Naming Convention
At one end, you have a small number of objects to add and/or further categorizing the objects beyond their name would be overkill, or force you to add a super specific schema or database. Or, you might have very good reasons for putting the things in dbo and leaving it at that; remember, there's nothing inherently bad about using dbo.

In this case, it probably makes sense to just rely on naming convention to distinguish your objects from others. Maybe that's the project and the description, so something like dbo.SpyLaserUsers and dbo.SpyLaserVictims.

Large Addition: Database
At the other end of the spectrum, you have a LOT of objects to add, and they don't make sense to put into an existing database. This is for things like new, distinct, products, or something of the scope large enough to warrant standing up a completely different database.

Depending on your situation, this might be easier said than done. And if an existing database makes sense to put your changes in (even if it's a lot of changes), it might be a better fit to put the objects in there. But if you feel like you're having to shoehorn in your changes to pick an existing database, this might be the right route.

Goldilocks Zone: Schemas

Smack dab in the middle are adding custom schemas. Your changes are not so numerous or new to necessitate a brand new database, but not so trivial that you'd want to start appending lots of strings to all your objects, and you'd like to have a clear idea of where your scope of work begins and where that of other objects ends.

Use Cases

There are two major categories of situations I've experienced where creating a new schema just makes total sense; the "dumping ground database" and the "monster database".

Dumping Ground
I worked on a database defined to contain "third party data". Back in the day when that was a handful of products, there was no problem throwing everything in there and moving on with life. However as time progressed, hundreds of products and tools got thrown in here so that Objects had huge long name prefixes to distinguish ClientADataWarehouse_Users from ClientA_Users. 

Over time, the database just became a dumping ground for any project that didn't have a home. And while I'll admit, the ad hoc nature of some of the projects made this not an unreasonable decision (as opposed to creating a new database), it made finding anything, and the related objects for anything an exercise in detective work and clever use of wildcards.

This situation benefits from custom schemas by creating new objects there under a schema which alleviates all the concerns around identifying related objects, distinguishing similarly named objects from eachother, and providing a way to erect permission barriers so that Clients A and Z can't "accidentally" read each others data.

Monster Database
The second case is a single, well defined single-scope database, but that is just big enough to make it difficult to manage through naming alone. It might be the database an ISP uses for all their online viewing content. Or a database related to customers and their data. Just like with a project in something like C#, at a certain point, you can't just put everything in the base directory, so to can you not just put everything in the "base schema"; you need organization. 

Here, you might have a logging schema, and a utilities schema, and a schema for archive data, or transactional data, or any of several other distinct subsets of database objects.

Conclusion

The reasons for creating non-dbo schemas are sort of "soft" in a "milage may vary" sort of way. You can also get carried away creating schemas to the point where they now just add a new kind of clutter as opposed to getting rid of existing clutter. But unless you find yourself in that position, I'd really consider giving the custom schema a spin. They're super easy to work with (the only changes you have to get used to are around fully qualifying objects, and some nuances around functions like sp_help).

To paraphrase an often misattributed quote, code is meant to be understood by humans and only incidentally for computers to execute. Schemas go a long way towards the goal of making your databases easier to work with. Even if you only tiptoe into those waters for now, I hope you will, and let me know how it goes.

Comments

Post a Comment

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Read more

Fixing Git "Unexpected Disconnect while reading sideband packet"

-
 I ran into an issue the other day (which my co-worker just ran into as well) when doing a git push, where it fails, saying "RPC failed; HTTP 500 curl 22 The Requested URL returned error: 500 send-pack: unexpected disconnect while reading sideband packet". I found almost no useful articles on the matter; most just talked about adding tracing flags or saying "your internet sucks", neither of which addressed the issue. What fixed it for me was setting the http.postBuffer size to something very large, like git config --global http.postBuffer 157286400 This allowed larger files/repositories or something like that to be posted successfully.
Read more

Left-Padding Zeroes

-
 I recently ran across a case where a time value was stored as an Int, with the idea that the first two numbers are the hours, second two the minutes, and third two the seconds. This is all well and good if the hours are double digits (e.g. 144415), as you can just split the string every two characters. If the hours are single digit (e.g. 93251) doing that won't give you the right result. You'll wind up splitting out "93", "25", 1" when what you really want is "09", "32", "51". You also run into this stuff sometimes when you're trying to left-pad a string for a year, month, or date to a pretty format. None of these cases are particularly compelling, and there are probably other solutions for these sorts of issues, but what I DO want to share with you, is a couple ways you can left-pad a string with characters.  FWIW, I've tried doing left-padding with the FORMAT function, and maybe I just suck with it, but I haven&#
Read more