Python Dictionaries and Panda

Here are some quick references to Python Dictionaries and Panda include

#Dictionaries
#Create
cities_in_europe = {'spain':'madrid', 'france':'paris', 'germany':'berlin', 'norway':'oslo' }

#Read value back
cities_in_europe['france']

#Add to Dictionaries
cities_in_europe['italy'] = 'venice'

#Change a value
cities_in_europe['italy'] = 'rome'

#"key" in dicarr
'italy' in delcities_in_europe #-> returns true

#delete a key
del(cities_in_europe['italy'])

print(cities_in_europe)

#N Dimention Dictionaries
europe = { 'spain': { 'capital':'madrid', 'population':42.33 },
'france': { 'capital':'paris', 'population':63.02 },
'germany': { 'capital':'berlin', 'population':78.62 },
'norway': { 'capital':'oslo', 'population':8.078 } }

#Get Single value
europe['france']['capital']
europe['france']['population']

# Print out the capital of France
print(europe['france']['capital'])

# Add One more Element
data = {'capital':'rome','population':59.83}

# Add data to europe under key 'italy'

europe['italy'] = data

#Add in one line
europe['england'] = {'capital':'london','population':78.88}

# Print europe

print(europe)
Pre Defined Lists
# Pre-defined lists
names = ['United States', 'Australia', 'Japan', 'India', 'Russia', 'Morocco', 'Egypt']
dr = [True, False, False, False, True, True, True]
cpc = [809, 731, 588, 18, 200, 70, 45]

import pandas as pd

my_dict = {'country':names ,
'drives_right':dr ,
'cars_per_cap':cpc }

# Build a DataFrame cars from my_dict: cars

cars = pd.DataFrame(my_dict)
print(cars)

row_labels = ['US', 'AUS', 'JAP', 'IN', 'RU', 'MOR', 'EG']

# Specify row labels of cars

cars.index = row_labels

print(cars)
Panda from CSV File

———- CVS FILE ————–
,country,capital,area,population
BR,Brazil,Brasilia,8.516,200.4
RU,Russia,Moscow,17.10,143.5
IN,India,New Delhi,3.286,1252
CH,China,Beijing,9.597,1357
SA,South Africa,Pretoria,1.221,52.98


import pandas as pd

brics = pd.read_csv("path/to/brics.csv", index_col = 0)
print(brics)

Column and Row Access
print(brics['country'])
print(type(brics['country']))
#Gives you <class 'pandas.core.series.Series'>

print(brics[['country']])
print(type(brics[['country']]))
#Gives you <class 'pandas.core.frame.DataFrame'>

#Two Columns
print(brics[['country','capital']])

#Row Access
brics[0:3] # Rows 0,1,2
brics[3:6] # Rows 3,4,5

#loc and iloc
brics.loc['RU'] #Selects row for RU
brics.iloc[1]
brics.loc[['BR','SA']] #Selects ROW BR and SA
brics.iloc[[0,4]]

#Select Area for SA
brics.loc['SA','area'])

# Capital and area for SA and RU
brics.loc[['SA','RU'],['capital','area']])
brics.loc[['RU','MOR'],['country','area']]
#Combine loc and iloc using ix
brics.ix[[4,5],['country','capital']]
brics.ix[4:6,['country','capital']]

#Slice-ing loc
brics.loc[:,'area']) #As Series
brics.loc[:,['area']]) #As DataFrame
brics.loc[:,['area','capital']]) #As DataFrame Area , Capital

Linux Mint Distribution Comparison to Ubuntu and Debian

My favourite Linux Distribution is Linux Mint and every so often you need to add a custom deb resource and the only ones out there is for Ubuntu or debian.

The latest Mint code names can be found at:

https://linuxmint.com/download_all.php

18.3 Sylvia Ubuntu Xenial Long term support release (LTS), supported until April 2021.
18.2 Sonya Ubuntu Xenial Long term support release (LTS), supported until April 2021.
18.1 Serena Ubuntu Xenial Long term support release (LTS), supported until April 2021.
18 Sarah Ubuntu Xenial Long term support release (LTS), supported until April 2021.
17.3 Rosa Ubuntu Trusty Long term support release (LTS), supported until April 2019.
17.2 Rafaela Ubuntu Trusty Long term support release (LTS), supported until April 2019.
17.1 Rebecca Ubuntu Trusty Long term support release (LTS), supported until April 2019.
17 Qiana Ubuntu Trusty Long term support release (LTS), supported until April 2019.
2 Betsy Debian Jessie Long term support release (LTS).

To help here is a list of distribution names from Ubuntu.

https://wiki.ubuntu.com/Releases

Ubuntu 17.10 Artful Aardvark Rel October 19, 2017 July 2018
 

Ubuntu 16.04.4 LTS

Xenial Xerus Changes <p “>March 1, 2018 April 2021
Ubuntu 16.04.3 LTS Xenial Xerus Changes August 3, 2017 April 2021
Ubuntu 16.04.2 LTS Xenial Xerus Changes February 16, 2017 April 2021
Ubuntu 16.04.1 LTS <p “>Xenial Xerus

Changes

July 21, 2016

April 2021

 

Ubuntu 16.04 LTS

Xenial Xerus

Rel

April 21, 2016

April 2021

 

Ubuntu 14.04.5 LTS

Trusty Tahr

Changes

August 4, 2016

April 2019

 

Ubuntu 14.04.4 LTS

Trusty Tahr

Changes

February 18, 2016

HWE August 2016

 

Ubuntu 14.04.3 LTS

Trusty Tahr

Changes

August 6, 2015

HWE August 2016

 

Ubuntu 14.04.2 LTS

Trusty Tahr

Changes

February 20, 2015

HWE August 2016

 

Ubuntu 14.04.1 LTS

Trusty Tahr

Changes

July 24, 2014

April 2019

 

Ubuntu 14.04 LTS

Trusty Tahr

Rel

April 17, 2014

April 2019

Debian can be found in a file called

cat /etc/debian_version

18.04  bionic     buster  / sid
17.10  artful     stretch / sid
17.04  zesty      stretch / sid
16.10  yakkety    stretch / sid
16.04  xenial     stretch / sid
15.10  wily       jessie  / sid
15.04  vivid      jessie  / sid
14.10  utopic     jessie  / sid
14.04  trusty     jessie  / sid
13.10  saucy      wheezy  / sid
13.04  raring     wheezy  / sid
12.10  quantal    wheezy  / sid
12.04  precise    wheezy  / sid
11.10  oneiric    wheezy  / sid
11.04  natty      squeeze / sid
10.10  maverick   squeeze / sid
10.04  lucid      squeeze / sid

 

 

Oracle Generate Merge Script

I have found this script and its so useful, I had to share it again.

Anytime you want to sync your data from the production database to the test database you can use this script.

JUST BE VERY CAREFUL TO NOT UPDATE YOUR PRODUCTION DATA WITH TEST DATA.

I like to run it on the test database, to be safe I create a DB link with a user that just have only Read Only access on the production data, to ensure you don’t update production data.

If you have more than one test database its always advisable to test it first between test databases.

This script wont update any data it will only generate the merge statement that you can use to update your data.

RazorFink Table Merge


--------------------------------------------------------------------------------
--Meta SQL for scaffolding table merge statements
--  Usage:
--    Execute SQL
--    export/copy lines to sql file
--    make necessary edits
--  TODO:
--    make all column references explicit
--------------------------------------------------------------------------------

WITH
    target AS
    (SELECT
        upper(trim(:owner))      AS owner,
        upper(trim(:table_name)) AS table_name
      FROM
        dual
    ),
    all_cols AS
    (SELECT
        atc.owner       AS owner,
        atc.table_name  AS table_name,
        atc.column_name AS column_name,
        atc.column_id   AS column_id,
        atc.data_type   AS data_type,
        atc.data_length AS data_length
      FROM
        all_tab_cols atc,
        target
      WHERE
        atc.owner           = target.owner      AND
        atc.table_name      = target.table_name AND
        atc.hidden_column  != 'YES'             AND
        atc.virtual_column != 'YES'
      ORDER BY
        column_id
    ),
    pk_cols AS
    (SELECT
        ac.owner           AS owner,
        ac.table_name      AS table_name,
        acc.column_name    AS column_name,
        acc.position       AS position,
        all_cols.column_id AS column_id
      FROM
        all_constraints ac,
        all_cons_columns acc,
        all_cols
      WHERE
        ac.owner            = all_cols.owner AND
        ac.table_name       = all_cols.table_name AND
        ac.constraint_type  = 'P' AND
        acc.owner           = ac.owner AND
        acc.table_name      = ac.table_name AND
        acc.constraint_name = ac.constraint_name AND
        acc.column_name     = all_cols.column_name
      ORDER BY
        acc.position
    ),
    data_cols AS
    (SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        all_cols
    MINUS
    SELECT
        owner,
        table_name,
        column_name,
        column_id
      FROM
        pk_cols
      ORDER BY
        column_id
    ),
    sql_parts AS
    (SELECT
        'SELECT'                 AS sql_select,
        'FROM'                   AS sql_from,
        'WHERE'                  AS sql_where,
        'ORDER BY'               AS sql_order_by,
        ';'                      AS sql_semi,
        'MERGE INTO'             AS sql_merge_into,
        'USING'                  AS sql_using,
        'ON'                     AS sql_on,
        'WHEN MATCHED THEN'      AS sql_when_matched,
        'UPDATE SET'             AS sql_update_set,
        'WHEN NOT MATCHED THEN'  AS sql_when_not_matched,
        'INSERT'                 AS sql_insert,
        'VALUES'                 AS sql_values,
        '('                      AS sql_paren_open,
        ')'                      AS sql_paren_close,
        chr(10)                  AS sql_lf,
        '  '                     AS sql_t,
        MAX(LENGTH(column_name)) AS max_col_length
      FROM
        dual,
        all_cols
    )
  --------------------------------------------------------------------------------
  SELECT
      STATEMENT
    FROM
      (SELECT
          1              AS statement_order,
          rownum         AS row_order,
          sql_merge_into AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          2      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||lower(owner)
          ||'.'
          ||lower(table_name)
          ||' o' AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          3         AS statement_order,
          rownum    AS row_order,
          sql_using AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          4      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          5      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_select AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          6      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || rpad(lower(all_cols.column_name), sql_parts.max_col_length, ' ')
          ||rpad(' AS '
          ||all_cols.column_name
          ||(
            CASE
              WHEN lead(all_cols.column_id) over (order by all_cols.column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END), sql_parts.max_col_length+6, ' ')
          ||'-- '
          ||all_cols.data_type
          ||sql_parts.sql_paren_open
          ||all_cols.data_length
          ||sql_parts.sql_paren_close AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          7      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_from
        FROM
          sql_parts
      UNION
      SELECT
          8      AS statement_order,
          rownum AS row_order,
          sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          ||sql_parts.sql_t
          || lower(target.owner
          ||'.'
          ||target.table_name) AS STATEMENT
        FROM
          target,
          sql_parts
      UNION
      SELECT
          9      AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || sql_order_by AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          10       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          ||sql_t
          || lower(column_name)
          || (
            CASE
              WHEN lead(position) over (order by position) IS NOT NULL
              THEN ','
              ELSE NULL
            END )
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          11     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close
          ||' n' AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          12     AS statement_order,
          rownum AS row_order,
          sql_on AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          13     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          14       AS statement_order,
          position AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by position) IS NOT NULL
              THEN ' AND'
              ELSE NULL
            END) AS STATEMENT
        FROM
          pk_cols,
          sql_parts
      UNION
      SELECT
          15     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          16               AS statement_order,
          rownum           AS row_order,
          sql_when_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          17     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_update_set AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          18     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          || rpad('o.'
          ||lower(column_name), max_col_length+2, ' ')
          ||' = '
          ||'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          data_cols,
          sql_parts
      UNION
      SELECT
          19                   AS statement_order,
          rownum               AS row_order,
          sql_when_not_matched AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          20     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_insert AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          21     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          22     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'o.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          23     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          24     AS statement_order,
          rownum AS row_order,
          sql_t
          || sql_values
        FROM
          sql_parts
      UNION
      SELECT
          25     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_open AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          26     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_t
          || 'n.'
          ||lower(column_name)
          ||(
            CASE
              WHEN lead(column_id) over (order by column_id) IS NOT NULL
              THEN ','
              ELSE NULL
            END) AS STATEMENT
        FROM
          all_cols,
          sql_parts
      UNION
      SELECT
          27     AS statement_order,
          rownum AS row_order,
          sql_t
          ||sql_t
          ||sql_paren_close AS STATEMENT
        FROM
          sql_parts
      UNION
      SELECT
          28       AS statement_order,
          rownum   AS row_order,
          sql_semi AS STATEMENT
        FROM
          sql_parts
      )
    ORDER BY
      statement_order,
      row_order ;

Pivot Tables in PostgreSQL

To use the pivot table function you need the tablefunc extention for postgres.

CREATE extension tablefunc;

I liked this post : vertabelo post it helped me but I want to add to that one, so please read it first. Then come back.
So consider this SQL

select IDENTIFIER, CATAGORIES, SUM(value) as VALUES 
from TABLE 
group by IDENTIFIER, CATAGORIES

So lets consider our categories being 3 letter Months ie (Jan, Feb,….) and we want to show payments for each month.

select SOME_PRIMARY_KEY, 
       to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
       SUM(PAYMENTS) as TOT_PAID 
from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')

Now to run this have each month as a column we would

 SELECT *   FROM crosstab( '
     select SOME_PRIMARY_KEY, 
            to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
            SUM(PAYMENTS) as TOT_PAID 
      from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
') 
AS final_result(MyPK character varying(20) , Jan numeric,Feb numeric,Mar numeric,Apr numeric,May numeric,Jun numeric,Jul numeric,Aug numeric,Sep numeric, Oct numeric, Nov numeric,Dec numeric) 
)

This does not give us the desired result since it will fill up from the fist column the values and not assign it to the correct column. To get the desired result we will have to add a second parameter to crosstab

 SELECT *   FROM crosstab( '
     select SOME_PRIMARY_KEY, 
            to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
            SUM(PAYMENTS) as TOT_PAID 
      from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
',
  $$VALUES ('Jan'::text),('Feb'),('Mar'),('Apr'),('May'),('Jun'),('Jul'),('Aug'),('Sep'),('Oct'),('Nov''),('Dec')$$
) 
AS final_result(MyPK character varying(20) , Jan numeric,Feb numeric,Mar numeric,Apr numeric,May numeric,Jun numeric,Jul numeric,Aug numeric,Sep numeric, Oct numeric, Nov numeric,Dec numeric) 
)

Java 8 Lambda – Simple explanation of the Consumer Interface

I have not found a simple explanation of how the consumer interface works and how to use it in a lambda expression. Hopefully I will help you guys to see how easy it is to use and you will also simplify your code.
The legacy way to create a List and loop through it :

public class ConventionalForLoop {
    
    public static void main(String[] args) {
        List<Integer> il = new ArrayList<>();
        il.add(10); il.add(15); il.add(20); il.add(25);

        for (Iterator<Integer> i = il.iterator(); i.hasNext();) {
            Integer item = i.next();
            System.out.println("For Loop Value :" + item);
        }
    }
}
Now with Java 8 we can use a Consumer Interface and for explanation I have declared it.

public class ShowConsumer {
    public static void main(String[] args) {
        class ConsumeInt implements Consumer<Integer>
            {
                @Override
                public void accept(Integer i) {
                    System.out.println("Value From Consumer :" + i.toString());
                }
            }
        List<Integer> arrl = Arrays.asList(10,15,20,25);
        ConsumeInt ci = new ConsumeInt();
        arrl.forEach(ci);
    }
With the lambda expression it is very simple :

public class ShowConsumer {
    public static void main(String[] args) {
        List<Integer> arrl = Arrays.asList(10,15,20,25);
        arrl.forEach(i -> System.out.println("Value From Consumer :" + i.toString()));
    }
}

Java 8 Lambda Expressions quick explanation

To get started with Lambda expressions in Java 8 I have found that there was no simple explanation, so here is it its basic form for everyone to understand.
First of all look at this implementation of the Animal Interface. you will notice there are lots of unnecessary boilerplate code.
They are all Highlighted in the code:

  
package showlambda;

interface Animal {
    void eat();
}

class Dog implements Animal {
    @Override
    public void eat() {
        System.out.println("Feed me Anything");
    }
}

class Cat implements Animal {
    @Override
    public void eat() {
        System.out.println("Feed Me Meat");
    }
}

public class ShowLambda {
    public static void main(String[] args) {
        Cat c = new Cat();
        c.eat();
        Dog d = new Dog();
        d.eat();
    }
    
}
We dont have to declare the classes we can create one Anonymously like so:

package showlambda;

interface Animal {
    void eat();
}

public class ShowLambda {
    public static void main(String[] args) {
        Animal anonymousdog;
        Animal anonymouscat;
        anonymousdog = new Animal() {
            @Override
            public void eat() {
                System.out.println("Feed me Anything anonymously :-)") ;
            }
            
        };
        anonymouscat = new Animal() {
            @Override
            public void eat() {
                System.out.println("Feed me Meat anonymously :-)") ;
            }
        };
        anonymousdog.eat();
        anonymouscat.eat();
    }
}

But we are still stuck with all the boilerplate code.
The only thing that we really require is the implementation code and this is where the lambda comes in,
Now we don’t have to mention the eat method since there is only one:

package showlambda;

@FunctionalInterface
interface Animal {
    void eat();
}

public class ShowLambda {
    public static void main(String[] args) {
        Animal anonymousdog;
        Animal anonymouscat;
        anonymousdog = () -> 
            {
                System.out.println("Feed me Anything Yeah Lambda :-)") ;
            };
        anonymouscat = () -> 
            {
                System.out.println("Feed me Meat Yeah Lambda :-)") ;
            };
        anonymousdog.eat();
        anonymouscat.eat();
        
    }
}

Since there is just one line of code to implement eat we can even simplify it more:

package showlambda;

@FunctionalInterface
interface Animal {
    void eat();
}

public class ShowLambda {
    public static void main(String[] args) {
        Animal anonymousdog;
        Animal anonymouscat;
        anonymousdog = () -> System.out.println("Feed me Anything Yeah Lambda :-)") ;
        anonymouscat = () -> System.out.println("Feed me Meat Yeah Lambda :-)");
        anonymousdog.eat();
        anonymouscat.eat();
        
    }
}
Now to show how to implement a parameter:

package showlambda;

interface Animal{
    void eat(String food);
}

public class ShowLambda {
    public static void main(String[] args) {
        Animal anonymousdog;
        Animal anonymouscat;
        anonymousdog = (food) -> System.out.println("Feed me Anything Yeah Lambda :-) Parameter :" + food) ;
        anonymouscat = (f) -> System.out.println("Feed me Meat Yeah Lambda :-) Parameter :" + f);
        anonymousdog.eat("Dog Food");
        anonymouscat.eat("Cat Food");
        
    }
}

Few lines of code to show all the basics of python datetime handling

Here is a few lines of code to show :
Line 3. How to Create a datetime object on a certain time
Line 4. How to subtract or add time to another time and how to compare datetime objects
Line 5. How to convert datetime to string
Line 6. How to get the current time

import datetime

lasttime = datetime.datetime(1900,1,1,0,0,0)

while True :
    if lasttime  < datetime.datetime.now() - datetime.timedelta(seconds=30) :
        print("30 Secornds has passed Date Now is :" + datetime.datetime.now().strftime("%A %d %B %Y %I:%M:%S%p") )
        lasttime = datetime.datetime.now()

Delete files that are older than 10 (or x) days

Here is a simple python script to delete files from the current directory that are older than 10 days,
you probably need to customize it a little to specify the files to delete.


#!/usr/bin/python

# run by crontab
# removes any files in current folder older than days_old days

import os, sys, time

def get_file_directory(file):
    return os.path.dirname(os.path.abspath(file))
    
now = time.time()
days_old = 1 #change this to how many days old files to delete
cutoff = now - (days_old * 86400)
files = os.listdir(get_file_directory(__file__))
file_path = get_file_directory(__file__)
for xfile in files:
        print("Check :" + xfile)
        if xfile.startswith('0000'):    #My Files stated with 0000 you can add other criteria here maybe xfile.endswith('.OLD')
            fullFile = os.path.join(str(file_path), xfile)
            #print("Path : " + fullFile)  Remove the comment to list all the files to be deleted
            if os.path.isfile(fullFile):
                t = os.stat(fullFile)
                c = t.st_ctime
                # delete file if older than [days_old] days
                if c < cutoff:
                    print("Will Remove " +fullFile + "to go ahead and remove the files please remove the # in the next line") #for now we will just print it
                    #os.remove(fullFile)  #comment this line to delete the files
            

Thanks to https://stackoverflow.com/users/1465704/xman-classical for the outline

PHP 7.0 Apache Oracle 12 Instantclient OCI8 install apt-get

If you need to do this from scratch I have followed these how-to-install-and-configure-php-70-or-php-71-on-ubuntu-16-04 insructions to install PHP7.0 and apache.

Once installed then
Follow these instructions to compile oci8 extentions Oracle tech note OCI8

Download the instant client from oracle’s web site here : Oracle instant client download
I downloaded the .ZIP files since I dont have RPM installed.
I then extracted it to ~/Downloads/instantclient_12_2
NOTE: Download the instantclient-basic-linux.x64-12.2.0.1.0.zip and instantclient-sdk-linux.x64-12.2.0.1.0.zip

cd ~Downloads
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip

Now we need to create the paths

sudo mkdir /usr/lib/oracle
sudo mkdir /usr/lib/oracle/12.2
sudo mkdir /usr/lib/oracle/12.2/client64
sudo mkdir /usr/lib/oracle/12.2/client64/lib
cd ~/Downloads/instantclient_12_2
sudo cp * /usr/lib/oracle/12.2/client64/lib/
cd /usr/lib/oracle/12.2/client64/lib/
ln -s libclntsh.so.12.1 libclntsh.so
sudo mkdir /usr/include/oracle
sudo mkdir /usr/include/oracle/12.2
sudo mkdir /usr/include/oracle/12.2/client64
cd ~/Downloads/instantclient_12_2/sdk/include
sudo cp * /usr/include/oracle/12.2/client64
export ORACLE_HOME=/usr/lib/oracle/12.2

Edit ld.so.conf and include oracle libs. Add a file called oracle.conf under /etc/ld.so.conf.d

cd /etc/ld.so.conf.d
sudo vi oracle.conf
PRESS ESC i
/usr/lib/oracle/12.2/client64/lib
PRESS ESC :wq

Now to download and compile OCI8

sudo pecl install oci8 --with-oci8=instantclient,/usr/lib/oracle/12.2/client64/lib

On My machine it compiled it under ‘/usr/lib/php/20151012/oci8.so’ but look at the last few lines it should echo it.

First lets install a page to test that the configuration works.

sudo vi /var/www/html/t.php ( or xed /var/www/html/t.php if you dont know vi)
Press ESC i and paste

Press ESC :qw

now open your favorite browser and go to http://localhost/t.php and you should see a information page of php

to configure php do:

cd /etc/php/7.0/mods-available
vi oci8.ini

Edit oci8.ini with content

extension=oci8.so

Now restart

sudo service apache2 restart

My Installation now gave an error

apachectl configtest
if you get an error about libaio.so.1: cannot open shared object file: No such file or directory

sudo apt-get install libaio1 libaio-dev

Now we need to give the path to the libraries.
Edit /etc/apache2/envvars and add the flowing to the bottom of the file

ORACLE_HOME=/usr/lib/oracle/12.2/client64
LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
LD_LIBRARY_PATH64=/usr/lib/oracle/12.2/client64/lib/
#TNS_ADMIN=$ORACLE_HOME/network/admin
#NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
#ORACLE_SID=your_db

To Test

php --ri oci8

PHP Active Directory Authentication using Self Signed SSL Cert (LDAPS://)

Here is the steps I followed to get the domain controller’s SSL CA cert and configure PHP with LDAP to do LDAPS authentication on Active Directory (Domain Controller AD).

  • First let get the CA cert. If you dont have it, it can be done via openssl.
  • To get the CA cert

    openssl s_client -connect YOUR_WINDOWS_DOMAIN_NAME.YOUR_DOMAIN_EXTENTION:636

    you will see all the values but what we are interested in is the –BEGIN CERTIFICATE — and –END CERTIFICATE—
    so save all the output to a file :

    openssl s_client -connect YOUR_WINDOWS_DOMAIN_NAME.YOUR_DOMAIN_EXTENTION:636 > ca_cert.pem

    then edit it: vi ca_cert.pem (or xed ca_cert.pem) and delete everything only leaving the –BEGIN CERTIFICATE — …. –END CERTIFICATE— including the –BEGIN CERTIFICATE — and –END CERTIFICATE— tages.

    Rename the file to a more descriptive name for your CA
    mv ca_cert.pem MyCA.pem

    To make sure you have the right Domain you can always do a nslookup to verify the domain controllers.
    nslookup
    set type=ANY
    your_microsoft_domain.your_web_domain.com

  • Now to install the new CA cert on the server
  • To see where the cert dir is located do a : openssl version -d
    (Mine was /etc/openssl/certs)
    so I copy
    cp ca_cert.pem /etc/openssl/certs
    and to create the HASH (You can do below or try running c_rehash in /etc/openssl/certs )

    openssl x509 -noout -hash -in ca-certificate-file

    grab the output hash (ie 23456789d)
    then do a link
    ln -s MaCA.crt 23456789d.0

  • New verify the installed certificate

  • openssl s_client -connect ANY_OF_THE_LDAP_SERVERS_GET_LIST_FROM_NSLOOKUP:636 -CAfile
    /etc/ssl/certs/MyCA.pem -verify 0

  • Now to configure the LDAP to use secure LDAPS
  • Here is the tricky part. I have found that my PHP does not pick up the changes I have made on /etc/openldap/ldap.conf .
    but start there first edit : xed /etc/openldap/ldap.conf (or vi /etc/openldap/ldap.conf if you prefer)
    add these lines:
    TLS_CACERT /etc/ssl/certs/23456789d.0

    You can always add this to ensure that you accept all certificates
    TLS_REQCERT allow

    now test and to see any errors you can do a
    sudo tail -f /var/log/apache2/error_log to see the errors

    If it does not work (Like on my UNIX box HP-UX) then on your phpinfo(); page under “Environment” look for variable HOME and lets say its
    /svr/www
    cp /etc/openldap/ldap.conf /srv/www/.ldaprc

  • Using PHP to autenticate
  • Now when its configured now you can use Active Directory Include from Source Forge