Disabling SSL certificate checking in unirest (NodeJS)

Problem:

You want to make a HTTP request with unirest like this:

const unirest = require('unirest');
unirest.get("https://mydomain.net").end(console.log)

but you encounter the following error:

{ error: 
   { Error: unable to verify the first certificate
       at TLSSocket.<anonymous> (_tls_wrap.js:1088:38)
       at emitNone (events.js:86:13)
       at TLSSocket.emit (events.js:188:7)
       at TLSSocket._finishInit (_tls_wrap.js:610:8)
       at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:440:38) code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE' } }

 Solution:

You can work around this problem by using strictSSL(false) like this:

const unirest = require('unirest');

unirest.get("https://mydomain.net")
.strictSSL(false)
.end(console.log)

Note however that this might have negative effects on the security of your application as this request will be vulnerable to man-in-the-middle attacks.

How to use concurrent.futures map with a tqdm progress bar

Problem:

You have a concurrent.futures executor, e.g.

import concurrent.futures

executor = concurrent.futures.ThreadPoolExecutor(64)

Using this executor, you want to map a function over an iterable in parallel (e.g. parallel download of HTTP pages).

In order to aid interactive execution, you want to use tqdm to provide a progress bar, showing the fraction of futures

Solution:

You can use this function:

from tqdm import tqdm
import concurrent.futures

def tqdm_parallel_map(executor, fn, *iterables, **kwargs):
    """
    Equivalent to executor.map(fn, *iterables),
    but displays a tqdm-based progress bar.
    
    Does not support timeout or chunksize as executor.submit is used internally
    
    **kwargs is passed to tqdm.
    """
    futures_list = []
    for iterable in iterables:
        futures_list += [executor.submit(fn, i) for i in iterable]
    for f in tqdm(concurrent.futures.as_completed(futures_list), total=len(futures_list), **kwargs):
        yield f.result()

Note that internally, executor.submit() is used, not executor.map() because there is no way of calling concurrent.futures.as_completed() on the iterator returned by executor.map().

Script user input

Problem

You want to remote control a program but unfortunately this program has only a „klick&gaudy“(*) interface.
(*) Okok – only has a graphical user interface (short GUI).

Solution

You may use xdotool in order to script user actions. To install this tool, use:

sudo apt-get install xdotool

Now you can get the position of the mouse pointer with:

xdotool getmouselocation

or set it via:

xdotool mousemove 400 300 (This means set the mouse to position x=400, y=300; Point of origin is the top left corner of the screen.)

In order to click use:

xdotool click 1

And in order to type a text (e.g. into a control field of the GUI)

xdotool type 'Hello World'

(P.S.: xdotool has much more options … Once this tool got installed type man xdotool in order to see them all.)

Exporting campaign contact CSVs from VTiger using SQL

In Exporting campaign account CSVs from VTiger using SQL we investigated how to use direct SQL database access to export names and emails for organizations for a given campaign.

This post shows how to do the same thing. Please refer to the previous post for a detailed explanation.

SELECT CONCAT(cont.firstname, " ", cont.lastname) AS "Name", cont.email AS "EMail" FROM vtiger_campaigncontrel AS ccr JOIN vtiger_contactdetails cont ON (cont.contactid = ccr.contactid) WHERE `campaignid` =(SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no")) AND cont.email <> ''

Besides joining on vtiger_contactdetails and vtiger_campaigncontrel instead of the account-related equivalent, the only major difference is in assembling the name. For contacts, first name and last name are stored separatedly. Therefore we use CONCAT(cont.firstname, " ", cont.lastname) to get a single name string out of that information.

As shown in the previous post, you can use phpmyadmin to export the result of the query to CSV or to Excel directly. While this might seem like a tedious process, it is much less tedious than copy-and-pasting hundreds of contacts manually.

Play a sound in a Webbrowser

Problem:

You want to play some sounds in a webbrowser.

Solution:

Modern Browsers have a fancy integrated AudioContext that allows you to play sounds. Here is an example (JavaScript Code):

// get the AudioContext
window.AudioContext = window.AudioContext || window.webkitAudioContext;

// Initialize audio context
var context = new AudioContext();

// Create an oscillator ... via this oscillator we can then play different sounds
var oscillator = context.createOscillator();
oscillator.frequency.value = 440; // this is an "A"
oscillator.type = "square";

// attach the oscillator to the sound output
oscillator.connect(context.destination);

oscillator.start(0); // start the oscillator (0=now) ...
oscillator.stop(1);  // stop playing this sound after 1 second

Exporting campaign account CSVs from VTiger using SQL

Update: Related post for exporting contacts instead of accounts

Although VTiger works great as CRM, its campaign management feature is missing one of most important features: It doesn’t support exporting

This post aims to provide technically oriented users a guide on how to use SQL queries to perform that task. We assume basic knowledge of SQL syntax, MySQL and PHPMyAdmin.

The goal for our example is to export a table of Account name and E-Mail. For the email, the primary email will be used, unless it’s empty in which case the secondary email is used.

The input for the query is the campaign name (in our example, this will be MyCampaign).

Querying the campaign number

The campaign number is a string, e.g. CAM4 or CAM9 which is stored in vtiger_campaign.

SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign"

 Finding the campaign record ID

In the campaign account relations n-to-n relations table, the campaign number is not used directly – instead, the campaign record ID is used. We need to query that from vtiger_modtracker_detail, which contain metadata key-value-data about the campaigns (among other things). One of the K-V pairs contain the campaign identifier (key campaign_no) whereas another one contains the record ID (key record_id).

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = <<campaign number>> and fieldname = "campaign_no") AND fieldname = "record_id"

Full query:

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no") AND fieldname = "record_id"

The result of this query is the numeric campaign record ID, e.g. 315.

Querying campaign-to-account relations

We can now use the campaign record ID to query the n-to-n table vtiger_campaignaccountrel. There are other, similar tables for leads (vtiger_campaignleadrel) and contacts (vtiger_campaigncontrel).

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = <<campaign record ID>>

Full query:

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = (SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no"))

The result of this query is a list of account IDs related to the campaign. The order is usually the same as displayed in VTiger.

Querying account information

Using the list of account IDs, we can JOIN on vtiger_account to query the account details. Note that you need to use the appropriate table for leads or contacts if you used an alternative relations table in the previous step.

SELECT acc.accountname AS "Name", COALESCE(acc.email1, acc.email2) AS "EMail" FROM vtiger_campaignaccountrel AS car JOIN vtiger_account acc ON (acc.accountid = car.accountid) WHERE `campaignid` =(SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no")) AND (acc.email1 <> '' OR acc.email2 <> '')

Note that besides the SELECT column list there are some aspects which are specific to the Name and Email query:

  1. COALESCE(acc.email1, acc.email2) uses email2 if email1 is empty
  2. AND (email1 <> '' OR email2 <> '') ignores accounts that have neither email1 nor email2 set

If you want a custom list of columns, you need to modify that appropriately.

Exporting CSV

When entering the query into a tool like PHPMyAdmin, you can click the Export button in order to export the result to different formats, including CSV and Excel.

While this might not be the most convenient option, it’s a very flexible way of generating and exporting the data. Any more convenient ways are likely to be less flexible.

Fixing PPA Unable to identify ‚package‘: user@mycomputer in launchpad

Problem:

You’ve uploaded a DEB package to a Launchpad PPA (e.g. using dput), but you get an error message similar to this:

Solution:

You need to use a proper email address (which must be registered in Launchpad) in debian/changelog .

In order to do this, set the $DEBEMAIL environment variable before running dch

Example:

export DEBEMAIL=myemail@example.com
dch [...]

If $DEBEMAIL is not set, [username]@[hostname] will be used

Graph layouting via Graphviz

Problem:

You want to display a Graph.

Solution:

Create a simple text file describing your graph and save it with a .dot file extension:

graph {
node1 -- node2;
node2 -- node3;
node3 -- node4;
node4 -- node1;
}

Afterwards you can use a program of the graphviz package (sudo apt-get install graphviz) in order to visualize the graph. This package contains different layouting programs like dot, neato, fdp (all from the GraphViz project) etc. Simply call one of these programs in order to visualize the graph:

neato -Tsvg yourFile.dot -o outputFile.svg

Output from this command:

Find WordPress category ID by category name using SQL

Problem:

You need the wordpress category ID for a given category name (e.g. My category), e.g. for a shortcode filter.

Solution:

You can use a SQL statement to find the correct category for your term (i.e. the category name, My category). Run this statement in your database administration interface, e.g. phpmyadmin.

SELECT termtax.term_taxonomy_id FROM wp_terms AS terms JOIN wp_term_taxonomy AS termtax ON terms.term_id = termtax.term_id WHERE terms.name = "My category"

Replace My category at the end by the category name you’re looking for.

Get the original language post ID for WPML-translated posts

Problem:

You have a post that has been translated into multiple languages using WPML.
You want to find out the ID of the original post, i.e. the equivalent post in the source langugae.

Solution:

As far as I know, there is no WPML API for this. You can, however, use $wpdb in order to query the database directly.

Here’s the SQL statement:

SELECT trans2.element_id
FROM wp_icl_translations AS trans1
INNER JOIN wp_icl_translations AS trans2
ON trans2.trid = trans1.trid
WHERE trans1.element_id = #myid#
AND trans2.source_language_code IS NULLs

where #myid# is the ID of your current (possibly translated) post.

The algorithm works like this:

  1. Find out the  translation ID (column trid) for the current post by selecting the row for the given post ID (column element_id)
  2. Find all rows with the same translation ID
  3. Select only the row where the source language is NULL – this is the original post.

You can use it like this:

global $wpdb;
$orig_lang_id = $wpdb->get_var("SELECT trans2.element_id FROM wp_icl_translations AS trans1 INNER JOIN wp_icl_translations AS trans2 ON trans2.trid = trans1.trid WHERE trans1.element_id = ".get_the_ID()." AND trans2.source_language_code IS NULL");

and then use $orig_lang_id where required. Note that $orig_lang_id is NULL if the query fails. The main reason for the query to fail is if there is no entry for the given post ID in the wp_icl_translations table.

If the current post is the source post, $orig_lang_id is the same as get_the_ID(), i.e. the current ID of the post.

In my test, the code still works (i.e. returns the correct post ID) even when creating brand-new entry without any translations being present.