#-- MSSQL Generate Test Case Example
CREATE PROCEDURE dbo.ts_sys_gentestcase @inputparam char(200) as
set nocount on
declare @ctr int, @ctr2 int, @codestr varchar(20), @curtime varchar(20)
set @curtime= convert(varchar(20),GETDATE( ))
set @ctr= 0
while @ctr < 500
begin
	set @ctr= @ctr + 1
	insert into dbo.ebook_property (docid,title,entrytime) values (@ctr, convert(varchar(4),@ctr), @curtime)
	set @ctr2= 0
	while @ctr2 < 110
	begin
		set @ctr2= @ctr2 + 1
		set @codestr= convert(varchar(4),@ctr)+ 'x'+ convert(varchar(4),@ctr2)
		insert into dbo.ebook_license (code,docid,entrytime) values (@codestr,@ctr,@curtime)
	end
end
insert into TESTDB.dbo.table1 select * from PRODDB.dbo.table1;
return 'test case completed'


#-- MYSQL stored proc exmaple:
create procedure p1 (in id1 int,out title1 varchar(50)) select title into title1 from documents where docid=id1;
call p1(2,@a); select @a;

DBCC CHECKIDENT(tablename, RESEED, 1);			//mssql reset identity column
ALTER TABLE tablename AUTO_INCREMENT=value		//mysql reset identity
select name from sysobjects where xtype='U'		//mssql show tables
select table_name,column_name,data_type,character_maximum_length from information_schema.columns //mssql desc table
select * from information_schema.views  |  information_schema.routines
#-- mssql & mysql desc  view | stored proc

grant all privileges on dbname1.* to 'user1'@'localhost' identified by 'pass1';	#--//mysql new user
select * from mysql.user;


sp_changeobjectowner '[olduser].[tblname]','dbo'	//mssql change dbtable owner to dbo
//NOTE: mssql select varchar of length >255 will get cut off, must use "select CONVERT(TEXT,$field) as $field" instead
create procedure p1 (in id1 int,out title1 varchar(50)) select title into title1 from documents where docid=id1;
call p1(2,@a); select @a;

MSSQL -> MYSQL translation
	select top n -> limit n
	DBCC CHECKIDENT(tablename, RESEED, n) -> ALTER TABLE tablename AUTO_INCREMENT= n
	remove all "dbo."s
	remove all "convert(TEXT,field)"
	desc tablename columns changed

	##---mysql v5.0+ foreign key
select column_name,table_name,referenced_table_name,referenced_column_name from information_schema.key_column_usage
	##--- mssql foreign key
select u.column_name,u.constraint_name,u.table_name,c.update_rule,c.delete_rule from information_schema.referential_constraints c,  information_schema.constraint_column_usage u where c.constraint_name=u.constraint_name and c.constraint_name like 'FK%' ;


##--- Posgresql DB notes -------------------------
PostgreSQL Database notes
Binary Download: http://www.enterprisedb.com/products-services-training/pgbindownload
http://get.enterprisedb.com/postgresql/postgresql-9.1.9-1-windows-binaries.zip
Java JDBC driver name: org.postgresql.Driver
PGSQL JDBC driver jar: http://jdbc.postgresql.org/download.html
##----------------------------------------------
#-- DOS command lines to setup & run PGSQL DB server:
pgsqlbininitdb.exe -D "../data"
pgsqlbinpostgres.exe -D "../data"
binpgAdmin3.exe : graphical GUI

Data dump: in GUI, right click on table name -> 'Backup' -> Ensure 'Insert statements' options checked
pg_dump.exe --host localhost --port 1234 --username "user1" --format plain --create --inserts --verbose --file "C:\"bak1.sql" "dbname"
##----------------------------------------------
Datatypes: serial= ID type(auto-inc), numeric(7,4)= sig.dig & decimal, integer, character varying, real, timestamp, date, time
DOUBLE Quote: Object names (Table & columns)
Single Quotes: String literals/constans.
|| : for string concat
Hirearchy: Database -> Catalog/ Schema -> Tables
Schema information_schema contains general table info (eg. columns)
Catalog pg_catalog contains all system tables starting with 'pg_', eg. pg_authid...etc.

DBs: select * from pg_database;
Schemas: select * from information_schema.schemata; [information_schema,pg_catalog,public]
Tables: select * from pg_tables limit 100;
Keys: select * from pg_constraint; //include primary keys
Cols: select * from information_schema.columns where table_name='tblname';
Users: select * from pg_authid;
Sys Setting: show all //(eg. server_version)


select table_schema, table_name, column_name from information_schema.columns where table_name not like '%__opnet' and column_name like 'parent%' limit 150;
select devicename, regexp_matches(cfgli,'entPhysicalModelName[^a-zA-Z]+000.([^\\]+)') as switch_model, regexp_matches(cfgli,'entPhysicalSerialNum[^a-zA-Z]+000.([^\\]+)') as switch_serial, regexp_matches(cfgli,'switchMacAddress[^a-zA-Z]+000.([^\\]+)') as switch_macaddr, regexp_matches(cfgli,'switchRole[^a-zA-Z]+000.([^\\]+)') as switch_role, regexp_matches(cfgli,'entPhysicalSoftwareRev[^a-zA-Z]+000.([^\\]+)') as switch_softver from (select oe1.devicename, regexp_split_to_table(encode(cfg1.propblob,'escape'),'switchNumber') as cfgli from dssuser.oe oe1, dssuser.svc_cfg svc1,dssuser.cfg cfg1 where svc1.oe_parent=oe1.id and cfg1.id=svc1.configuration and svc1.config_type like '%STACK%' and oe1.devicename like 'wka%') as derivcfg limit 40;

select devicename||':'||cpuindex as "devname1" from table_name;
update dashboard as d set uuid=d2.uuid,created=d2.created from dashboard as d2 WHERE d.name like 'X_%' and d2.name=replace(d.name,'X_','');
select t.* from (values('MGM'),('UA')) as t(studio);
select distinct replace(regexp_replace(base_name,'.[a-z]{2}.sunlife$',''),'.clarica.com','') from dssuser.oe;
select distinct lower(replace(regexp_replace(regexp_replace(h,'.[a-z]{2}.sunlife$',''),'.sunlife.[a-z]+$',''),'.clarica.com','')) as h2 from ((select base_name from dssuser.oe) union (select devicename from dssuser.oe)) as t(h) order by h2;

alter table project_track rename column "PM Name" to "Project Manager";
update pg_database set datistemplate='false' where datname='template1';
drop database template1;
create database t1 template template0;
##---------Login Roles = User IDs
Group roles are Optional; Inheirit can be from another user or group.

CREATE ROLE webdev LOGIN ENCRYPTED PASSWORD 'XXXXX' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
grant usage ON SCHEMA dssuser to webdev;
grant dssuser to webdev; //inherit another user (not group) role & permissions
grant usage ON SCHEMA dssuser to webdev;

alter role webdev with superuser;
alter database postgres owner to webdev;
alter default privileges for role webdev grant all on tables to webdev;
reassign owned by "oldusr" to webdev;
drop owned BY oldusr; 
##----------------------------------------------
1): #Enable all remote connection for specific DB & user, to "pg_hba.conf"
# TYPE DATABASE USER ADDRESS METHOD
host dbname1 all 0.0.0.0/0 password
host dbname1 all 0.0.0.0/0 md5

2): Modify "postgresql.conf" to listen all remote connection:
listen_addresses = '*'
##----------------------------------------------
CREATE OR REPLACE FUNCTION uniq_words(text,text) Returns text AS $$ SELECT array_to_string(ARRAY(SELECT DISTINCT trim(x) FROM unnest(string_to_array($1,$2))
x),$2) $$ LANGUAGE SQL;