postgresql - how to install under ubuntu (debian)

uBuntu Community Wiki - PostgreSQL

How To Install and Use PostgreSQL on Ubuntu 14.04 @ Digital Ocean

install via apt-get

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

login as linux user postgres (that was auto-created during installation)

$ sudo -i -u postgres 

launch interactive postgresql console

$ psql

quit interactive console

$ \q

djfhjdhfjdhjfhdf

SQL Server - Strip NameSpace from XML String

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION Strip_XML_NS
(
    @xml varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @stripped varchar(max)

    declare @i int
    set @i = CHARINDEX('"', @xml) - LEN('xmlns="')

    declare @f int
    set @f = CHARINDEX('"', @xml, CHARINDEX('"', @xml) + 1) + 1

    declare @ns varchar(max)
    set @ns = SUBSTRING(@xml, @i, @f - @i)

    set @stripped = REPLACE(@xml, @ns, '')
   
    RETURN @stripped
END
GO

MS SQL Server - Select Columns Names with System Type for User Table

declare @tableName varchar(max)
set @tableName = 'Products'

select so.name as TableName, sc.name as ColumnName, st.name as SystemType
from sys.columns sc
inner join sys.objects so on (sc.object_id = so.object_id)
inner join sys.types st on (sc.system_type_id = st.system_type_id)
where
    (so.type_desc = 'USER_TABLE')
    and
    (so.name = @tableName)

MS SQL Server - Clear Cache - for Query Timing

[Using MS-SQL Server] When timing a query for the purposes of optimization, the query may well run quicker on re-execution subsequent to the initial execution - due to caching by the db engine.

In order to meaningfully compare execution time across optimization attempts, the cache needs to be cleared, and this can be done by executing


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go


between query timings.

To turn query timing on - SET STATISTICS TIME ON
To turn query timing off - STATISTICS TIME OFF

Export Create Table Script in mySQL

SHOW CREATE TABLE TableName;

Useful DataBase MetaData Scripts

1. given a column name, return a list of tables with similar columns names

  declare @targetColumnName varchar(500)
  set @targetColumnName = '%YourTargetColumnNameHere%'

  select so.[name] as TableName, sc.[name] as ColumnName  
  from sysobjects so
  inner join syscolumns sc on (so.[id] = sc.[id])
  where (so.[type] = 'U') and (sc.[name] like @targetColumnName)

2. given a table name, return a list of tables with similar name

  declare @targetTableName varchar(500)
  set @targetTableName = '%Entity%'

  select so.[name] as TableName
  from sysobjects so
  where (so.[type] = 'U') and (so.[name] like @targetTableName)

sqLite

Dr Dobbs
http://www.drdobbs.com/184401773;jsessionid=0COIHAUN55BUJQE1GHOSKH4ATMY32JVN

limitations of sqlite
- http://www.sqlite.org/omitted.html
- http://www.sqlite.org/limits.html
- http://www.sqlite.org/limits.html

mySQL - Python

Official mySQL Site on Integration

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0, and is built on top of the MySQL C API.

Writing MySQL Scripts with Python DB-API
SourceForge mySQL-Python Home
PEP 249 : Python Database API Specification v2.0

DOWNLOAD
MySQL for Python [SourceForge]