How to keep the order of SELECT WHERE IN() in mysql

April 21st, 2012 by xrigher No comments »

The answer is to use ORDER BY FIND_IN_SET(col_name, order_values).

Here is an example. A table `students` has 2 columns: `id`, `name`.
If you want to find the items with the IDs of 4,5,2,1, and keep them in the order of 4,5,2,1 as well.

Here is the SQL:

SELECT id, name FROM students
WHERE id IN (4,5,2,1)
ORDER BY FIND_IN_SET(id, '4,5,2,1');

Using powershell on Windows

December 6th, 2011 by xrigher No comments »

Powershell on windows is the same thing as Bash in linux.

Set the prowershell enviroment

The prowershell script file .ps1 is defaultly forbidden to execute with the ExecutionPolicy “Ristricted”. To see your current ExecutionPolicy, open a powershell windown and type:
Get-ExecutionPolicy

There are totally 4 levels of ExecutionPolicy:

  1. Restricted: no .ps1 execution is allowed
  2. AllSigned: .ps1 files can run, but need a signed digital signature. No matter they are written locally or downloaded form Internet.
  3. RemoteSigned: Local .ps1 files can run, but downloaded scripts need a digital signature.
  4. Unrestricted: All scripts can run without digital signature.
  5. Normally, we need to run local scripts, so we set it to “RemoteSigned”:
    Set-ExecutionPolicy remoteSigned -Scope CurrentUser
    With “-Scope CurrentUser”, the ExecutionPolicy will only be changed to the logged user.

    Basics

    Loops

    There are many ways to loop in powershell: do…while…, while(){}, for(){}, …
    Here is an example of while{} loop

    $i = 1
    while ($i -le 5) {
    	Write-Host $i; # write on console
    	mkdirdir x$i # create a folder
    
    	$i++
    } 

install igraph (C) on Linux(ubuntu) and use it in Eclipse

November 30th, 2011 by xrigher No comments »

Build and install igraph.

      download the source code package;
      unpack it into a directory, eg. igraph-0.5.4 (I’ll use IGRAPH_DIR instead below)
      enter the directory: user> cd IGRAPH_DIR
          user> make
          user> sudo make install This will install the igraph libary files into /usr/local/lib

Add igraph libraries to the shared libraries search path

If you haven’t include /usr/local/lib into your shared libraries search path as defined in /etc/ld.so.conf, add it. Otherwise, you will see an error like this: error while loading shared libraries: libigraph.so.0: cannot open shared object file: No such file or directory.

And DON’T forget to reload the config:user> sudo ldconfig

Use igraph in Eclipse

      Go to Project -> Properties-> C/C++ Build -> Settings
      Go to GCC C++ Compiler -> Directories, add /usr/loccal/include/igraph.
      Go to GCC C++ Linker -> Libraries, add “igraph” to Libraries (-l) and add “/usr/loca/lib” to Library search path (-L).

An example of Boost Betweenness Centrality

November 28th, 2011 by xrigher No comments »

Here is an example of using Boost to calculate Betweenness Centrality for a graph. The example is original from: http://programmingexamples.net/index.php?title=CPP/Boost/BGL/BetweennessCentralityClustering.

But now the page is down, so I cache it here:

BetweennessCentralityClustering.cpp

/*
 * In the included example, the max centrality of the graph is 14. So specifying
 * the required input argument 14 gives the "first" decomposition of the graph.
 * That is, removal of one edge (with the maximal edge centrality). Specifying a value
 * greater than 14 yields no decomposition.
 */

#include <string>
#include <iostream>
#include <fstream>
#include <sstream>

#include <boost/graph/adjacency_list.hpp>
#include <boost/graph/graphviz.hpp>

// For clustering
#include <boost/graph/bc_clustering.hpp>
#include <boost/graph/iteration_macros.hpp>

// Graph edge properties (bundled properties)
struct EdgeProperties
{
  int weight;
};

typedef boost::adjacency_list< boost::setS, boost::vecS, boost::undirectedS, boost::no_property, EdgeProperties > Graph;
typedef Graph::vertex_descriptor Vertex;
typedef Graph::edge_descriptor Edge;

void WriteGraph(const Graph& g, const std::string& filename);

int main(int argc, char** argv)
{
  // Verify arguments
  if( argc != 2 )
  {
    std::cerr << "USAGE: " << argv[0] << " <Max centrality>" << std::endl;
    return -1;
  }

  // Convert the input argument to a double
  std::stringstream ss;
  ss << argv[1];
  double max_centrality;
  ss >> max_centrality;

  // Create a star graph
  Graph g;

  // Central vertex
  Vertex centerVertex = boost::add_vertex(g);

  // Surrounding vertices
  Vertex v;
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);
  v = boost::add_vertex(g);
  boost::add_edge(centerVertex, v, g);

  // Attach an additional vertex to one of the star arm vertices
  Vertex x = boost::add_vertex(g);
  boost::add_edge(v, x, g);

  // std::map used for convenient initialization
  typedef std::map<Edge, int> StdEdgeIndexMap;
  StdEdgeIndexMap my_e_index;
  // associative property map needed for iterator property map-wrapper
  typedef boost::associative_property_map< StdEdgeIndexMap > EdgeIndexMap;
  EdgeIndexMap e_index(my_e_index);

  // We use setS as edge-container -> no automatic indices
  // -> Create and set it explicitly
  int i = 0;
  BGL_FORALL_EDGES(edge, g, Graph)
  {
    my_e_index.insert(std::pair< Edge, int >( edge, i));
    ++i;
  }

  // Define EdgeCentralityMap
  std::vector< double > e_centrality_vec(boost::num_edges(g), 0.0);
  // Create the external property map
  boost::iterator_property_map< std::vector< double >::iterator, EdgeIndexMap >
          e_centrality_map(e_centrality_vec.begin(), e_index);

  // Define VertexCentralityMap
  typedef boost::property_map< Graph, boost::vertex_index_t>::type VertexIndexMap;
  VertexIndexMap v_index = get(boost::vertex_index, g);
  std::vector< double > v_centrality_vec(boost::num_vertices(g), 0.0);
  // Create the external property map
  boost::iterator_property_map< std::vector< double >::iterator, VertexIndexMap >
          v_centrality_map(v_centrality_vec.begin(), v_index);

  std::cout << "Before" << std::endl;
  print_graph(g);

  BGL_FORALL_EDGES(edge, g, Graph)
  {
    std::cout << edge << ": " << e_centrality_map[edge] << std::endl;
  }

  // Write to graphviz -> illustrate the graph via 'neato -Tps before.dot > before.ps'
  WriteGraph(g, "before.dot");

  // Calculate the vertex and edge centralites
  // Can be used to get an initial impression about the edge centrality values for the graph
  //brandes_betweenness_centrality( g, v_centrality_map, e_centrality_map );

  // Define the done-object:
  // 'false' means here that no normalization is performed, so edge centrality-values can become big
  // If set to 'true', values will range between 0 and 1 but will be more difficult to use for this
  // illustrative example.
  boost::bc_clustering_threshold< double > terminate(max_centrality, g, false);

  //
  // Do the clustering
  // Does also calculate the brandes_betweenness_centrality and stores it in e_centrality_map
  //
  betweenness_centrality_clustering( g, terminate, e_centrality_map );

  // Print the results
  std::cout << "\nAfter" << std::endl;
  print_graph(g);

  BGL_FORALL_EDGES(edge, g, Graph)
  {
    std::cout << edge << ": " <<e_centrality_map[edge] << std::endl;
  }

  // Write to graphviz -> illustrate the graph via 'neato -Tps after.dot > after.ps'
  WriteGraph(g, "after.dot");

  return 0;
}

void WriteGraph(const Graph& g, const std::string& filename)
{
  std::ofstream graphStream;
  graphStream.open(filename.c_str());
  boost::write_graphviz(graphStream, g );
  graphStream.close();
}

linux batch rename files

October 28th, 2011 by xrigher No comments »

Use this command to rename a set of files:

for file in * ; do mv $file `echo $file | sed 's/OLD_STRRING/NEW_STRING/'` ; done

And here is a batch file:
——————
batch_rename.sh
——————

#!/bin/bash

if [ $# -ne 3 ]; then
        echo "Usage: $0 'FilePattern' SearchString ReplaceString";
        echo "Eg: $0 'r-*.sh' 5 6";
else
        for file in $1 ; do mv $file `echo $file | sed s/$2/$3/` ; done
fi

If the 2nd or 3rd parameter contains space, use ‘ ‘ to surround the parameter.

CPP (C++) time

October 21st, 2011 by xrigher No comments »

There are may ways to estimate how long a program (or a part of the program) elapsed.

Use clock()

clock() is a standard function defined in . Here is the usage:

#include <ctime>

int main() {
  clock_t start, end;
  start = clock();
  // do sth...
  end = clock();

  double elapsedSeconds = (end - start) / CLOCKS_PER_SEC;
}
 

Use gettimeofday()

clock() is a linux function defined in . Here is the usage: #include <sys/time.h> int main() { timeval start, end; gettimeofday(&start, 0); // the second parameter is the timezone. // do sth... gettimeofday(&end, 0); double elapsedSeconds = (end.tv_sec - start.tv_sec + 1e-6*(end.tv_usec - start.tv_usec)); }

solve the problem of no output in console on windows eclipse with MinGW

October 14th, 2011 by xrigher No comments »

If you are experiencing the problem of no output in console on windows eclipse with MinGW, here is the solution.

And actually the solution the is very simple, you only have to add a PATH variable to your MinGW bin directory in the run configuration.

Here are the steps:

From menu: Run -> Run Configuration…, open the run configuration window, and add the PATH variable to your MinGW bin directory (as shown in the picture below).

Differences between new/delete and malloc/free in C and CPP

October 4th, 2011 by xrigher No comments »

There is a very good post about this: http://www.codeproject.com/KB/tips/newandmalloc.aspx.

Here I’ll just list some of the differences concisely:

  • new & delete will call the object’s default constructor & destructor respectively; while malloc & free will not. This is also why dynamically array will call it’s element’s default constuctor. See http://xrigher.info/cpp/array-initialization-in-cpp/.
  • malloc returns a void*, so it’s result always needs cast. Such as: FooCls* t = (FooCls*) malloc(sizeof FooCls);
  • when using new to create an array, delete[] is needed. And never delete[] a single object, it's even worse.
  • realloc only exists with malloc/free pair. It's handy when resize an object to the same or less size.
  • new & delete are based on malloc & free. Malloc additionally allocate a header storing the size allocated, so free() knows the size to free. new[] not only keeps the malloc header, but also has an extra header storing the size of the array/vector, so delete[] knows how many objects are in the array/vector, and thus knows how many times to call the destructor.
    details of new[] & delete[] can be found at: Mismatching scalar and vector new and delete

mysqlpp connection timeout problem

October 3rd, 2011 by xrigher No comments »

If you have a application having long time connection with mysql using mysqlpp. You may at first think of using Connection::connected() to determine whether the connection is still valid. But actually this doesn’t work if the connection gets timeout.

After checking the reference, I found out Connection::ping() would be a good way to determine timeout.

Here is a piece of test code:

#include <iostream>
#include <string>
#include <mysql++.h>

using namespace std;

int main() {
	string db_schema("DB_SCHEMA");
	string db_host("DB_HOST");
	string db_user("DB_USER");
	string db_pass("DB_PASS");
	string qry_str("SELECT MAX(user_id) FROM users");

	mysqlpp::Connection cn;
	cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());
	mysqlpp::Query qry = cn.query(qry_str);
	mysqlpp::StoreQueryResult res = qry.store();
	cout << "frist query result: " << res[0][0] << endl;

	string tmp;
	cout << "Wait for a period of time to let the connection timeout, and then input whatever a string to continue: " << endl;
	cin >> tmp;

	cout << "ping result: " << cn.ping() << endl;
	cout << "connected: " << cn.connected() << endl;

	if(!cn.ping())
		cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());
	mysqlpp::Query qry2 = cn.query(qry_str);
	res = qry2.store();
	cout << "second query result: " << res[0][0] << endl;

	cn.disconnect();

	return 0;
}

But of course, I’m still new to mysqlpp. There may be a better way to do this. Please comment below if you have a better way.

NOTE: Here is how to change mysql connection idle timeout.

UPDATE: Just found another way to handle the timeout problem: use Connection::set_option(new mysqlpp::ReconnectOption(true));. This is much more elegant.
Here is a piece of sample code:

mysqlpp::Connection cn;
cn.set_option(new mysqlpp::ReconnectOption(true));
cn.connect(db_schema.c_str(), db_host.c_str(), db_user.c_str(), db_pass.c_str());

set mysql connection idle timeout

October 3rd, 2011 by xrigher 1 comment »

in config file my.ini, under [mysqld] add these 2 lines:


# set timout to be 1h (3600 seconds)
wait_timeout=3600
interactive_timeout=3600