List of helpful script and sql

check-for-duplicate-record-based-key.sql

select * from
(select callTime,callType,employee,phoneNumber,count(*) as n from table2 group by callTime,callType,employee,phoneNumber) x
where x.n > 1;


delete a
    from leads a
        LEFT JOIN
        (
            SELECT MIN(ID) ID,phone
            FROM   leads
            GROUP   BY phone
        ) b ON  a.ID = b.ID AND 
                a.phone = b.phone
WHERE   b.ID IS NULL;

delete-duplicate.sql

DELETE a
FROM table1 as a, table1 as b
WHERE
          (a.phone   = b.phone OR a.phone IS NULL AND b.phone IS NULL)
      AND (a.comments = b.comments OR a.comments IS NULL AND b.comments IS NULL) 
AND (a.rm = b.rm OR a.rm IS NULL AND b.rm IS NULL)
AND (a.voice_record = b.voice_record OR a.voice_record IS NULL AND b.voice_record IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
AND (a.disposition = b.disposition OR a.disposition IS NULL AND b.disposition IS NULL)
      AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
      AND a.submission_id < b.submission_id;


delete a
    from table1 a
        LEFT JOIN
        (
            SELECT MIN(submission_id) submission_id,phone
            FROM    table1
            GROUP   BY form_id,phone,rm,comments,disposition,call_duration,voice_record
        ) b ON  a.submission_id = b.submission_id AND 
       (a.phone   = b.phone OR a.phone IS NULL AND b.phone IS NULL)
      AND (a.comments = b.comments OR a.comments IS NULL AND b.comments IS NULL) 
AND (a.rm = b.rm OR a.rm IS NULL AND b.rm IS NULL)
AND (a.voice_record = b.voice_record OR a.voice_record IS NULL AND b.voice_record IS NULL)
AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
AND (a.disposition = b.disposition OR a.disposition IS NULL AND b.disposition IS NULL)
      AND (a.call_duration = b.call_duration OR a.call_duration IS NULL AND b.call_duration IS NULL)
WHERE   b.submission_id IS NULL;

download_s3.py

import boto3
import os

aws_session = boto3.Session(profile_name='prod')
s3_client = aws_session.client('s3')

def download_dir(prefix, local, bucket, client=s3_client):
    """
    params:
    - prefix: pattern to match in s3
    - local: local path to folder in which to place files
    - bucket: s3 bucket with target contents
    - client: initialized s3 client object
    """
    keys = []
    dirs = []
    next_token = ''
    base_kwargs = {
        'Bucket':bucket,
        'Prefix':prefix,
    }
    while next_token is not None:
        kwargs = base_kwargs.copy()
        if next_token != '':
            kwargs.update({'ContinuationToken': next_token})
        results = client.list_objects_v2(**kwargs)
        contents = results.get('Contents')
        for i in contents:
            k = i.get('Key')
            if k[-1] != '/':
                keys.append(k)
            else:
                dirs.append(k)
        next_token = results.get('NextContinuationToken')
    for d in dirs:
        dest_pathname = os.path.join(local, d)
        if not os.path.exists(os.path.dirname(dest_pathname)):
            os.makedirs(os.path.dirname(dest_pathname))
    for k in keys:
        dest_pathname = os.path.join(local, k)
        if not os.path.exists(os.path.dirname(dest_pathname)):
            os.makedirs(os.path.dirname(dest_pathname))
        client.download_file(bucket, k, dest_pathname)


download_dir('sample_format/','/home/avinash/Templates','bucketname')

dump_symphony_query.php

<?php
          print_r(array(
     'sql'        => $dq->getSQL(),
     'parameters' => $dq->getParameters(),
 ));
          var_dump($dq->getParams());
         exit();  
        $sql=$db->getSQL();

?>

find-all-tables-containing-column-with.sql

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

search_replace.sql

UPDATE table
SET
    columnname = REPLACE(columnname,
        'value2',
        'value1')
WHERE
    columnname like '%value1%';