SQL – Anup Saund

gitSQL – Source Control for SQL Server

On August 19, 2015

TL;DR: How to export SQL Server objects and data to flat file for version control. A blog post behind the gitSQL tool, why it came about and the journey so far.

Problem – I need to source control my database

Earlier this year I was thinking about how to automate the extraction of SQL objects and data from SQL Server into flat file. I wanted to do this so I could save the files to GIT and then track changes.

I also wanted a bit more than that too – I wanted to be able to build a database from source control.

I don’t ask for much right?

Research

I searched and searched and found the following products on the market.

Redgate Source Control (http://www.red-gate.com/products/sql-development/sql-source-control/)
ApexSQL Source Control (http://www.apexsql.com/sql_tools_source_control.aspx)
DB Ghost (http://www.dbghost.com/)
Randolph (http://www.nobhillsoft.com/randolph.aspx)
Liquibase (http://www.liquibase.org/)

Liquibase is free – but I couldn’t manage to configure it to my requirements. If i did manage then this blog post wouldn’t exist.

I tried each of the other products and they all felt good – but only if you are willing to spend between $200 – $1000.

I didn’t want to spend any money, or at least, I could not justify spending any money right now.

I then set on it to distill the very essence of what these apps were offering and what is going on underneath the hood.

What did I do next?

You probably guessed it –

I created my own App, and launched it in April this year as

http://www.gitsql.net

My main reason for creating the app was because I wanted something simple for myself and I thought it would be great to share it;

gitSQL has 3 screens and a command line interface coming in the next release.

The CLI will help with continuous automation, and general streamlining of source control.

gitSQL is Free for up to 20 tables, and 10 other objects, with an UNLIMITED version @ $40.

At first, I didn’t want to charge anything for it but I soon realised that I would need to support the app with potential bug fixes and future features.

Therefore – the UNLIMITED licence is simply to cover development costs and hosting costs.

Any happy customers?

One of my early customers had a fantastic experience with the product, so much so that he kindly blogged about his experience.

Read all about it:

https://rcbsql.wordpress.com/2015/04/24/version-control-for-this-dummy/

I have also had a lot of direct messages on Facebook and Twitter asking about current features and CLI options for the future – This is really rewarding for me because it means that gitSQL is working for others like I thought it might do. #success

I also had a request for adding Sequences as an object type – through gitHub, and after a bit of testing i worked out it could be added and it’s made it into the latest release. #github issue 4

What’s next?

I would really like to include a PostgreSQL, MySQL and maybe an Oracle version(s) of gitSQL to make a more comprehensive suite of source control apps.

I am currently working on the PostgreSQL version which should be released Q2 2016. The MySQL version may also be released at the same time if I can engineer things correctly. The end result may be one app for them all – or several individual apps bundled into one single download. #not_decided_yet

The future products will follow the same model of being fully featured for FREE, with a small licence cost for UNLIMITED usage (again just to cover my development/hosting/support costs).

I hope gitSQL works for you – and if it doesn’t please let me know over at gitHub, Facebook or Twitter.

Stripslashes in SQL for MYSQL and ORACLE

by noopy

On April 28, 2010

In PHP, SQL

Problem

Today, I wanted to strip slashes from a comment field using SQL (INSTEAD of using php stripslashes).

Why you ask?

I was working on an application which had not considered stripslashes for formatting output.

So I had a look and realised. Do I change 40,000 lines of code to add Stripslashes, or 1 line at the SQL select statement?

OK, slight exaggeration, but you get what I mean.

Solution

MySQL

[sourcecode language=’sql’]
Select
Replace(field_name, ‘\’, ”) as stripped_field
from
table;
[/sourcecode]

Oracle

[sourcecode language=’sql’]
Select
Replace(field_name, ”, ”) as stripped_field
from
table;
[/sourcecode]

PHP EXTRA

If using in PHP add an extra backslash like this;

[sourcecode language=’sql’]
// strip slashes as part of the SQL select statement
$sql = “Select Replace(field_name, ‘\’, ”) as stripped_field from table”;
[/sourcecode]

Please get in touch if you know of an easier way to do this, for example, Oracle Functions, or MySQL functions.

Many thanks and happy coding.

I remember the days before ADODB

by noopy

On March 4, 2010

In SQL

I remember a time when SQL was written for a database.

Oracle SQL was different to Access SQL

mySQL was different to SQL Server SQL

… then somebody said no, stop, enough is enough.

Sometime in 2009 ADODB was born for PHP. The 1 wrapper for ALL database types. Ah, just what we wanted.

Wait a minute? This is ADO, didn’t microsoft do this in 1996? The short answer is yes. Database abstracting is old in theory, tried out by microsoft, worked to a degree but never fully made it in the world (for whatever reason).

This brings us up to ADODB – the promise of a database wrapper that will make it through, and iron out the issues that microsofts offering had.

Today I got my hands dirty with ADODB and I liked it. Looks and feels just like any other wrapper such as ADO.

Actually, I think it’s the same same. So so… così così.

One thing for sure. It makes hot swapping between Oracle/MySQL (which is what i’m doing right now) a breeze.

That’s why i’m writing about it.

I like it… how about you?

??British Expat ??Vancouver ❤ Gardening ?Founder of gitSQL

Category: SQL