Top
JohnKlann / Posts tagged "list"

List Files with their absolute path using Bash

function lsfp(){
local dirpath="$1"
local recurse="$2"

if [[ "$dirpath" == "" ]]; then
echo "no path provided!"
exit
else
local dirpath=$(echo "$dirpath" | sed 's:/$::g')
fi

local DirArr=($(ls -l "$dirpath" | grep -P "^d" | grep -oP "(?<= \d\d\:\d\d ).*"))
local FileArr=($(ls -l $dirpath | grep -oP "(?<= \d\d\:\d\d ).*$"))

for file in "${FileArr[@]}"; do
local fp="$dirpath/$file"
echo "$fp"
done

if [[ "$recurse" != "" ]]; then
for direc in "${DirArr[@]}"; do
lsfp "$dirpath/$direc" "$recurse"
done
fi
}
jklann

Postgres How To Get List of Functions Like PGAdmin III

Recently I had the need to get a bunch of object stats and counts of two postgres databases to compare them. The goal was to confirm both via query results as well as visually via PGAdmin III, just in case I missed something in excel :P. Anyways I quickly realized that some of my object counts did not match the UI counts. After a little playing with the query I realized the UI splits the Functions into aggregates, functions, and trigger functions. However by default the UI hides the views for aggregates and trigger functions and you have to manually specify these to be shown in schema tree.

File -> Options
pg_admin_III_file_options

Browser -> Display -> Objects
pg_admin_III_file_options_browser_display_objects

So If you are wanting to get only user functions like the list that is in PGAdmin III under each schema in the function tree. You will want to remember to exclude aggregate functions and trigger functions.

This Query does just that. It gets the count of functions per schema just like displayed in the UI.

select ns.nspname, count(*)
from pg_proc pr
join pg_namespace ns on 
    pr.pronamespace = ns.oid
--Exlcude system schemas
where ns.nspname <> 'pg_catalog' 
    and ns.nspname <> 'information_schema' 
    --Exclude Trigger Functions
    and pr.oid not in 
        (select tgfoid from pg_trigger)
    --Exclude aggregate functions
    and pr.proisagg = 'f'
group by ns.nspname
order by ns.nspname;

This Query Gets a count of Aggregate functions per schema.

select ns.nspname, count(*)
from pg_proc pr
join pg_namespace ns on 
      pr.pronamespace = ns.oid
--Exlcude system schemas
where ns.nspname <> 'pg_catalog' 
    and ns.nspname <> 'information_schema' 
    --Exclude Trigger Functions
    and pr.oid not in 
         (select tgfoid from pg_trigger)
    --include aggregate functions
    and pr.proisagg = 't'
group by ns.nspname
order by ns.nspname

Lastly this Query Gets a count of Trigger functions per schema.

select ns.nspname, count(*)
from pg_proc pr
join pg_namespace ns on 
    pr.pronamespace = ns.oid
--Exlcude system schemas
where ns.nspname <> 'pg_catalog' 
    and ns.nspname <> 'information_schema' 
    --Include Trigger Functions
    and pr.oid in
         (select tgfoid from pg_trigger)
group by ns.nspname
order by ns.nspname

Now you have a set of Function count queries that will match the PGAdmin III UI.

jklann

How to: Setup a SSH Server on Windows

This How to will walkthrough the steps to setting up an free ssh server on Windows operating systems. The will allow ssh access (secure shell access) to windows command prompt, and provide you with the ability to utilize other tools like Putty(tunneling), and WinSCP(file transfers). This walk through will show you how to set this up within your LAN (Local Area Network).

Requirements:

SSH server software — For the use of this tutorial, freesshd will be used.
Putty client — ssh client

Step 1: Install freesshd

freesshd installer

Step 2: Configure freesshd

Once you have finished installing the ssh server, run the application. You will want to edit the settings:

Right click on the freesshd icon in the taskbar and select settings

freesshd taskbar settings

You may see that the ssh server is not running and it may not start when you attempt to start it. Do not fret your machine probably needs a reboot. However ever this can wait until after the configuration has finished. For the next steps we want the SSH server to be stopped.

freesshd server status

Most of the defaults are effective there is only one last thing to configure.

Select the Users tab and click add user:

freesshd add user

Add your windows user account name under login. If you are not attached to a domain just leave the domain field blank.
Then select the user permissions. (personally I selected them all.)
Select ok.

Now start your SSH server on the Server status tab. Reminder if it will not start remember to reboot your machine and try to start it again.

Step 3: Create your first SSH session.

Collect you computers ip address:
Press windows key + r.
Type cmd into the run dialogue box and hit enter.

run dialogue command prompt

In the command prompt type ipconfig and press enter. Your ip address will be listed under your adapter.

finding your ip address cmd ipconfig

From another computer on your LAN (local area network) download and run the putty client.

Type in your ip address of the SSH server then select open

putty setup for simple ssh session to windows

This will then prompt you for your user name and password. *Note: it will show nothing when you type your password in. Once complete this will open a Command Prompt Shell on the remote system.

ssh windows command prompt shell

Related articles:

How to transfer files to and from a windows server using ssh with WinSCP

How to setup port forwarding for ssh

jklann