Skip to content

Instantly share code, notes, and snippets.

Avatar
🏠
Working from home

Sebastian Webber sebastianwebber

🏠
Working from home
View GitHub Profile
@sebastianwebber
sebastianwebber / generate_series.sql
Last active Jul 28, 2019
SQL Server generate_series
View generate_series.sql
-- 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
sebastianwebber / date_interval.sql
Created Sep 17, 2014
SQL Server function to return interval a date interval between 2 datetime fields
View date_interval.sql
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
sebastianwebber / pg_repack helpers and bloat stuff views
Created Sep 30, 2014
Views to generate pg_repack command line and show bloat tables
View pg_repack helpers and bloat stuff views
-- 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
sebastianwebber / jboss-domain.sh
Last active Aug 29, 2015
JBoss AS7+/EAP 6+ Control Script for DomainMode on EL6 platform
View jboss-domain.sh
#!/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
sebastianwebber / lista-ds.sh
Last active Sep 16, 2018
Lists datasources usage on JBoss AS 4,5 and 6 or JBoss EAP 5
View lista-ds.sh
#!/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
sebastianwebber / baixa_hq.sh
Last active Aug 29, 2015
script para download de gibis do hqonline.com.br
View baixa_hq.sh
#!/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
sebastianwebber / gen_pgauth.sh
Created Dec 11, 2014
Generates pgauth's file
View gen_pgauth.sh
#!/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
sebastianwebber / make_pgbouncer_conn.sql
Created Dec 11, 2014
Generate connection script for pgbouncer
View make_pgbouncer_conn.sql
select datname || ' = port=9876 dbname=' || datname from pg_database
order by datname;
@sebastianwebber
sebastianwebber / jboss_as7-standalone.sh
Last active Feb 15, 2017
JBoss AS7+/Wildfly standalone start script
View jboss_as7-standalone.sh
#!/bin/bash
#
# chkconfig: 2345 90 10
#
# JBoss Standalone Control Script
#
# Developped by:
# Sebastian Webber - sebastian@swebber.me
# Last update:
# 2013-08-01
@sebastianwebber
sebastianwebber / top-10-tables_by-size.sql
Created Jan 2, 2015
Listando as 10 maiores tabelas no PostgreSQL
View top-10-tables_by-size.sql
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
)