sebastianwebber

-- http://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/

IF EXISTS (SELECT *
             FROM dbo.sysobjects
            WHERE id = object_id (N'[dbo].[generate_series]')
              AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[generate_series]
GO

CREATE FUNCTION [dbo].[generate_series] ( @p_start INT, @p_end INT)

sebastianwebber

IF EXISTS (SELECT *
             FROM dbo.sysobjects
            WHERE id = object_id (N'[dbo].[date_interval]')
              AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[date_interval]
GO

CREATE FUNCTION [dbo].[date_interval] ( @p_start datetime, @p_end datetime)
RETURNS @DATES TABLE ( generated_date datetime )
AS

sebastianwebber

-- the original
-- avaliable on: https://wiki.postgresql.org/wiki/Show_database_bloat
CREATE VIEW vw_bloat_objects AS
SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes

sebastianwebber

#!/bin/bash
#
# chkconfig: 345 60 40
#
# JBoss AS7+/EAP 6+ Control Script for DomainMode on EL6 platform
#
# Developped by:
#     Sebastian Webber - http://swebber.me
# Last update:
#     2014-10-30

sebastianwebber

#!/bin/bash

## lista_ds.sh
## Lists datasources usage on JBoss AS 4,5 and 6 or JBoss EAP 5

### SAMPLE OUTPUT:

###  INSTANCIA            | PORTA    | DS                   | MaxConnectionsInUseCount  | AvailableConnectionCount  |
###  ---------------------|----------|----------------------|---------------------------|---------------------------|
###  jboss-server-1       | 1199     | OracleDS             | 8                         | 50                        |

sebastianwebber

#!/bin/bash

### http://blog.edwards-research.com/2010/01/quick-bash-trick-looping-through-output-lines/

SEPARADOR="|"

function formata_nro() {
    printf "%0${1}d" ${2}
}

sebastianwebber

#!/bin/bash
export AUTH_FILE=/etc/pgbouncer/pgbouncer.auth

TMPFILE=$( mktemp )
trap 'rm -f "$TMPFILE"' EXIT

psql -Upostgres -p 9876 -qAtX -c 'SELECT $$"$$ || replace( usename, $$"$$, $$""$$) || $$" "$$ || replace( passwd, $$"$$, $$""$$ ) || $$"$$ from pg_shadow where passwd is not null order by 1' > "$TMPFILE"

if [[ ! -s "$TMPFILE" ]]
then

sebastianwebber

select datname || ' = port=9876 dbname=' || datname from pg_database
order by datname;

sebastianwebber

#!/bin/bash
#
# chkconfig: 2345 90 10
#
# JBoss Standalone Control Script
#
# Developped by:
#     Sebastian Webber - sebastian@swebber.me
# Last update:
#     2013-08-01

sebastianwebber

WITH table_stats AS (
	SELECT
		schemaname,
		tablename,
		pg_relation_size(schemaname || '.'|| tablename) as table_size,
		(pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size,
		pg_total_relation_size(schemaname || '.'|| tablename) as total_size
	FROM
		pg_tables
)