VIM - Search and replace a string, globally, case insensitive

:%s/OLD_STRING/NEW_STRING/gi

MongoDB - Index management commands

db.COLLECTION_NAME.getIndexes();

db.COLLECTION_NAME.createIndex( { FIELD_NAME:1 } );

db.COLLECTION_NAME.dropIndex( { FIELD_NAME:1 } ); // use same creation signature


// Background index creation (no read locks, no write locks, a bit slower)
db.COLLECTION_NAME.createIndex( { FIELD_NAME:1 }, {background:true} );

// Compound index creation
db.COLLECTION_NAME.createIndex( { FIELD_NAME:1, FIELD_NAME_2:-1 } );


// Sub-document index creation
db.COLLECTION_NAME.createIndex( { 'FIELD_NAME.SUB_FIELD_NAME':1} );


PS. Number 1 represents an ascending-order index, while -1 a descending-order one.

MongoDB - Explaining queries (plan and actual execution explanation)

db.COLLECTION_NAME.explain().find({FILTER_HERE});

// This will explain the actual execution (execute and explain)
db.COLLECTION_NAME.explain(true).find({FILTER_HERE});


// Including execution statistics (more verbose than regular explain)
db.COLLECTION_NAME.explain("executionStats").find({FILTER_HERE});


PS. The explain method does not only apply to .find() but also to .aggregate(), .count(), .group(), .remove(), .update()

MySQL - How to load a data text file into a table

LOAD DATA LOCAL INFILE "file_name_here.csv" INTO TABLE table_name_here FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES STARTING BY 'xxx' LINES TERMINATED BY '\r\n' IGNORE 0 LINES


PS. Most arguments are optional (e.g. lines starting by... to be used only if each line starts with a given string).

MySQL - Run a .sql script directly in the command line

mysql -h localhost -u USER_HERE -p PASSWORD_HERE DATABASE_NAME_HERE < SCRIPT_FILE_NAME_HERE.sql

MySQL - How to optimize for large SQL files upload/import

(1) Change the value of the mysql configuration file variable "" from 1 to 0.

(2) If using InnoDB, read the configuration file and do use up to 80% of the machine's available RAM.

(3) Start your .sql script with "SET autocommit = 0;" and end it with a manual "commit;".

(4) If possible, make sure your script inserts rows in PK-order.

(5) Avoid using IDE's... mysql command-line is usually faster and more reliable. Loading using MySQL Workbench or Sequel Pro (mac), often produces random disconnections, or interruptions due to strange errors such as the database detecting a different encoding than the one specified or automatically detected.

VIM - How to save the file as, with a particular encoding

:write ++enc=utf-8 TARGET_FILE_NAME.EXTENSION

VIM - How to save each line of a file into a separate file, numbered

:g/./execute '.w '.line('.').'.FILE_EXTENSION'

VIM - How to show and hide line number

:set number
:set nonumber

MacOS - How to split a file for a given row number range

cat LARGE_FILE_NAME.TXT | awk 'NR >= 999  && NR <= 1500 { print }' > SMALLER_FILE_NAME.TXT

PS. Where 999 is the line to start with, and 1500 the last line you want.

VIM - How to go to the end of the file (last line of the file)

(1) Press ESC to get out of INSERT mode.
(2) Type G
(3) Press ENTER

PS. To go back to the beginning issue this command  G1

MacOS - How to replace all lines of a text file matching a string (in terminal)

grep -vwE "(string to replace)" sourcefile.txt > destinationfile.txt

MacOS - Consolidate all files in a directory into a single file

cat *.* > all.txt

MongoDB - Performance optimization tips

1. Issue updates to specific fields, avoid retrieving the entire document.
2. Avoid negation in queries... MongoDB does not index the absence of values.
3. Test/explain every query in your application with explain().
1. Eliminate unnecessary indexes.
2. Use covered queries when possible (queries that exclusively retrieve values that are indexed)
4. Make sure to use the latest available MongoDB drivers.
5. Store all data for a record in a single document.
6. Avoid large documents (use GridFS if you must).
7. Manually pad your documents with fields that may later on exist. This helps the Mongo engine from having to move/re-write the document in a separate disk location.
8. Avoid large indexed arrays.
9. Avoid long field names (repeated field names add-up, and take space!).
10. Avoid creating separate indexes for fields already found in compounded indexes.
11. Store indexes in a separate disk/volume.
12. Use EXT4 or XFS files systems (avoid EXT3).
13. Use RAID10 (not RAID0, nor RAID5)

Taken from "Performance Best Practicers for MongoDB", MongoDB 3.0, August 2015.

MongoDB - Project (select) only a specific field from a sub-document

db.COLLECTION_HERE.find(
{FILTER_HERE}
,{
"SUB_DOC_FIELD_NAME.FIELD_NAME" : 1
}
)

MongoDB - Drop column, remove a field from all documents (records)

db.COLLECTION.update(
{
  FIELD_NAME : { $exists : true }
  }
  , {
  $unset : { 'FIELD_NAME' : 1 }
    }
  , {
  multi : true
  }
);

MongoDB - Array add to set (insert a value), unless it already exists, to an array type of field

db.COLLECTION_NAME.update(
    {FILTER_HERE}
    , {
        $addToSet : {
            FIELD_NAME_WITH_ARRAY : VALUE_HERE
        }
    }
);

MongoDB - Array pull all (multiple element deletion), based on value, from an array type of field

db.COLLECTION_NAME.update(
    {FILTER_HERE}
    , {
        $pullAll:{
            FIELD_NAME : [ VALUE_1, VALUE_2 ]
        }
    }
);

MongoDB - Array pull (remove) all elements with a given value, of an array type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $pull:{FIELD_NAME_WITH_ARRAY : VALUE_HERE } } );

MongoDB - Array pop the first element of an array type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $pop:{FIELD_NAME_WITH_ARRAY: -1 } } );

MongoDB - Array pop (remove, delete) last item of an array type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $pop:{FIELD_NAME_WITH_ARRAY:1} } );

MongoDB - Array push all (insert multiple values) into an array type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $pushAll:{FIELD_NAME_WITH_ARRAY:[ VALUE_1, VALUE_2, VALUE_N ]} } );

MongoDB - Array push (insert, add) a value into an array type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $push:{FIELD_NAME_WITH_ARRAY: VALUE_HERE } } );

MongoDB - Set the value of a specific item (item number 2) within an array-type of field

db.COLLECTION_NAME.update( {FILTER_HERE}, { $set:{"FIELD_NAME_WITH_ARRAY.ARRAY_INDEX_HERE": VALUE_HERE } } );

MacOS - Inspect a json file using python and 'more' in terminal

cat FILE_NAME.FILE_EXTENSION | python -m json.tool | more

PHP - Catch all query-string values and add them into variables automatically

parse_str($_SERVER['QUERY_STRING']);

MongoDB - How to drop a collection (completely remove a collection/table)

db.COLLECTION_NAME.drop();

// Indexes are lost.
// Returns false if the collection is not found.

MongoDB - How to remove all documents (row) from a collection (table)

db.COLLECTION_NAME.remove({});

// This works only on version 2 and below (no document being passed).
db.COLLECTION_NAME.remove();

MongoDB - Count records matching a given filter criteria

db.COLLECTION_NAME.find({FIELD_NAME:'VALUE_TO_MATCH'}).count();

OR

db.COLLECTION_NAME.count({FIELD_NAME:'VALUE_TO_MATCH'});

MongoDB - How to list all collections

db.getCollectionNames();

// This gets only the first found collection
db.getCollectionNames()[1];

// This ignores the system collection called "system.indexes"
db.getCollectionNames().filter(function(x){return x!=='system.indexes'});

Sublime Text - Increased readability custom user settings

{
"font_size": 12
, "caret_style": "phase"
, "highlight_line": true
  , "fade_fold_buttons": false
  , "bold_folder_labels": true
}

MongoDB - Retrieve docs where a given field ends with a certain letter (regex)

db.COLLECTION_NAME.find( { FIELD_NAME:{ $regex:"e$" } } );

MongoDB - Find documents where a field does not exist

db.COLLECTION_NAME.find( { FIELD_NAME:{$exists:false} } );

MongoDB - Find (select) documents where a field value is a string

db.COLLECTION_NAME.find( { FIELD_NAME:{$type:2} } );

MongoDB - How to specify a custom data directory

mongod --dbpath \YOUR\PATH\HERE\FOLDER_NAME

CSS - How to use Prettify

Automatic code highlighting for your web pages.

Download the zip/tar file and unzip and place within your website.
http://code.google.com/p/google-code-prettify/


Then add the CSS and the main JavaScript file to your page, along with an onLoad event trigger in the BODY tag.

TSQL - User defined function to return the greater of two given integer numbers

/*

       -- Execution

       select dbo.getGreaterInt( '201201', '201112' );

       select dbo.getGreaterInt( '201201', null );

       select dbo.getGreaterInt( null, '201201' );

       select dbo.getGreaterInt( null, null );

*/

ALTER function [dbo].[getGreaterInt](

       @int1 int

       , @int2 int

)

returns int

as

begin

       return(

              case

                     when isnull(@int1,0) >= isnull(@int2,0)

                     then @int1

                     else @int2

              end

       );

end

;

GO

PYTHON - How to install a .whl file (offline package installation)

(1) Download the wheel file (.whl... from, for example https://pypi.python.org).

(2) Run this command:

pip install PACKAGE_FILE_NAME.whl

MySQL - Filter for rows with values starting with a lowercase letter

select *
from TABLE_NAME
where CONVERT( FIELD_NAME USING latin1) COLLATE latin1_general_cs regexp '^[a-z]'
;

MySQL - How to filter in a 'case sensitive' way

select *
from TABLE_NAME
where CONVERT( FIELD_NAME USING latin1) COLLATE latin1_general_cs  like 'a%'
;

(1) You convert your field to a latin collation (if not already... from UTF8 for example)
(2) You collate it using a case sensitive collation (..._cs)