Category Archives: Language References

Intro to git

git is essentially decentralized, which seems to be the direction everything is going, so I decided to take a shot at it.

I’ve used cvs, VSS, TFS, SVN and now git and I’ve got to say it is definitely pretty cool (and free).

The main difference I have noticed between git and these other guys is git allows you to make commits to your local machine and push them later. This means faster commits, more commits and therefore more versioning and finer details on revisions, and no need to worry about potential connectivity issues.

Behind the scenes git is also much cleaner on everything from its transmission protocols to its storage mechanism, so overall it is a more evolved product.

I haven’t fully explored all of its features yet, so I’ll keep revisiting this article to keep it updated from time to time.

First of all, you will have to decide upon a remote repository. You could setup your own, but that is currently out of scope of this article. In this scenario I chose projectlocker.

I left all projectlocker settings as default and using ssh as the communication protocol. http(s) is available as well, but ssh rocks :) .

If you’re on Windows, download and install Cygwin and make sure you select OpenSSH. Cygwin will give you a nice little linux shell for us to do our work from. If you were hoping for a GUI, try this route first, you might be surprised at the simplicity of the command line.

Open cygwin to create a public key to authenticate to the remote server. Enter ssh-keygen and press enter. Keep pressing enter on all prompts and skip the password.

cd to the ~/.ssh folder where it likely created your key enter cat id_rsa.pub. This will dump the contents of your public key file. Right click on the command prompt title bar of the window and select mark then grab your dumped file and paste in your remote repo service. Make sure the username has no spaces and matches your pc name and is also listed in the file dump.

Save changes in remote service then go back to cygwin. To test you did the above step correctly do “ssh -v [your git without specifying port of \reponame.git]“. It shouldn’t connect fully, but should give you enough status to verify it’s working. If it prompts for password then it was unable to verify public key which the -v output should indicate.

Once the above is good, identify yourself with:
git config –global user.name “yourname”
git config –global user.email “youremail@yourdomain.com”

Then download the remote repository (which may or may not be empty, that’s ok).
git clone [yourauthpart@yourdomainpart:yourreponame.git]

To add in a project I already had in my local, I did the above steps first within C:\git then copied my project folder in C:\git\projectname. cd into git then use this command to add any files within:
git add [filename or wildcard * for all]
git commit -m “my first commit!”

Then to push up to repo at any point simply use:
git push origin master

And that’s it!

(Optional: If you get an error on push origin or skipped clone then first do):
git init
git remote add origin [yourauthpart@yourdomainpart:yourreponame.git]
git pull origin master

So to sum up the steps we have:
1) setup repo on remote service
2) install cygwin and openssh (skip this step on *nix)
3) ssh-keygen (enter on all prompts)
4) cat public key and enter in remote repo service
5) test key setup is correct using ssh
6) identify your user and email to git
7) clone the repo and begin adding, committing and pushing!

Rock and roll!

References
cforcoding.com (blog), http://www.cforcoding.com/2009/09/windows-git-tutorial-cygwin-ssh-and.html
git-svn crash course, http://git.or.cz/course/svn.html
bahrenbugs (blog), http://blog.bahrenburgs.com/2010/01/using-git-with-projectlocker-on-mac.html
projectlocker, http://projectlocker.com/
cygwin, http://www.cygwin.com/

How to Create secure relational databases in SQL

The first step to a secure DB, is a well structured DB and the application layers that access it.

Take the following example of a table containing factories and the robots they produce.

FACTORYNAME ROBOTNAME
FACTORY1 Ron
FACTORY1 John
FACTORY2 Bob
FACOTRY2 Bill

This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?

First, lets separate the data into two separate tables of factories and robots:

“SP_GET_FACTORIES”

FACTORYID FACTORYNAME
1 FACTORY1
2 FACTORY2

“SP_GET_ROBOTS”

ROBOTID ROBOTNAME
1 Ron
2 John
3 Bob
4 Bill

In order to group these two together. we now require a mapping table.
“FactoryRobotMapping”

FACTORYID ROBOTID
1 1
1 2
2 3
2 4

To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.

Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.

Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.

Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join

To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.

So now you have:
App A->DAL->GetRobotsInFactories
App B->DAL->GetRobotsInFactories

For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?

What if a new business change now requires you to only get robots in factories after specific manufacture dates?

You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.

This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.

What’s the alternative?

Views are joins

For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.

An example of such join for our above is:

“VW_JOIN_FACTORIES_ROBOTS”

select * from factories inner join robots on 
factories.factoryid = robots.robotid

This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.

Cool, now what?

You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.

Stored Procedures offer security

Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.

Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.

Consider the following stored procedure:

“SP_GET_ROBOTS_IN_FACTORIES”

USE [RobotCo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIED ON
GO
CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime)
AS
BEGIN
SET NOCOUNT ON;
select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate()
END

You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.

Locking it down

All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.

Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.

Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.

References
Microsoft Linq, http://msdn.microsoft.com/en-us/netframework/aa904594
Ruby on Rails, http://rubyonrails.org/

C++ Cheat Sheet and Quick Reference Guide

The Tools

To compile and test code on Windows, my recommendation would be using:
Microsoft Visual Studio 2008/2010 Express Editions (ask your instructor about full versions)

On an older Windows box (if it’s not quad core), you might want to use Visual Studio 2008 instead of 2010. 2010 is excellent but much more performance intensive, although there are some automation patches and other service packs that do help performance, start up still seems to be a little slow – kind of like me getting up in the morning after 3-day redbull-coding hyperdrive. :D

Adding “/nosplash” on your launch shortcut helps speed up launch time alot on my PC as well.
Right Click on Shortcut->Properties->Replace Target with: “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe” \nosplash

If you’re on Linux or other systems, you could use gcc which comes with your distro out right out of the box. However, since gcc is CLI, you might want to use Eclipse or Mono instead.

Don’t try to write this on the palm of your hand..

(See Matt Mahoney’s reference at bottom of page for more complete reference).

//--CODE COMMENTS--
//this is a comment
/*this 
comment 
spans multiple 
lines */

//--INCLUDES--
#include <stdio.h> //generic standard files (you don't create these)
#include "otherclass.h" //files in your project in current directory (you do create these)

//--DEFINE--
//these are examples of defining your own custom literal constants 
#define PI 3.1415
#define NEWLINE '\n'

//--PREPROCESSOR CONDITIONALS--
//similar to normal conditional logic, but fires BEFORE compilation so allows for some different things
#if defined(value) //same as #ifdef value
//do something
#else
#endif

//--BASE VALUE TYPES--
//these are the building blocks of your more complex types. every class, type or object can be broken down in simplest form to these types
int
unsigned int
signed int
short
float
double
long
bool
char
String //sometimes represented with lowercase s instead of capital. this is technically not a base value type in C++, but is generally accepted as one in many languages... since it's not a base value type it requires include <string.h> to use

//--LITERALS--
//these are commonly allowed expressions
255 //integer 
0377 //octal, preceded by 0
0xAF //hexadecimal, preceded by 0x
2000000000L //Long (32bit) integer, notice it ends with L
0x0fffffffl  //same applies to hex, notice it ends with L which is not part of hex
123.0 //known as a double
1.23e2 //also allowed
'a', '\141', '\x61' //characters
'\n', '\\', '\'', '\"' //newline, backslash, single quote, double quote
"hello world"                //a powerful force known as the "string", which is actually an array of characters
"hello" "world" //concatenated strings
true, false //boolean values, also represented by integers 1 and 0

//--SIMPLE DECLARATIONS AND SCALAR VARIABLES--
int x; //declare an integer, notice it starts with "int" and followed by variable name
int x=1; //when declaring can also specify an initial value
short y; long z; //short is 16 bit int, long is 32 bit int. this affects the max value it can be, such as 255
unsigned int largenum=52625; signed int negnum=-1;  //declaring unsigned or signed allows for larger numbers and negative values. if not specified defaults to signed
unsigned long x=0xffffffffL; //same applies to octal, hex
float f=1.00; double d; //cannot declare unsigned. float is for numbers with many decimal places, double is big numbers
bool cool=true; bool cool=1; //boolean true or false, 1 or 0 value
int x, y, z; //multiple declarations can be declared in one line
char initial='r'; //8 bit single character. may not used commonly by itself, but important for arrays

//--ARRAYS--
//even though if may not seem logical at first, for consistency purposes, remember your arrays always start at index 0 not index 1. you can workaround this by never setting value at index 0, but I wouldn't advise it since programmers rarely ever start with 1
int numberarr[5]; //declare an array. in this example this is equivalent of {0,0,0,0,0} stored in memory
int one_d_numberarr[]={0,1,2}; //initial values can also be specified for arrays. note that array size is not specified here but initial values are
int two_d_matrix[4][4]={{1,2,3,4},{5,6,7,8}};  //arrays can have multiple dimensions with different sizes, also allowing for initial values
char message[]="hello"; //array of characters, more commonly known as a string
string message = "hello"; //simpler and more common declaration if you include <string.h> or System::String in namespace System on Windows

//--POINTERS--
//pointers are not prevalent in all programming languages, and not always necessary to create your application but usually will improve performance.. as well as complexity....
int* p; //declares a variable which
char* message="hello";
int& newvalue=oldvalue; //creates a reference to original value, so both share and reuse the same space in memory

//--ENUMS--
//an enum is a simple type. see examples below
enum weekday {MON,TUE,WED,THUR,FRI};   //any variable declared with a type of weekday will strictly contain one of these variables
enum weekday day; //declare a variable where it's type is weekday instead of a base value type
enum weekday {MON=0,TUE=1,WED=2,THUR=3,FRI,=4};  //here the values are declared explicitly, which might be useful in some cases but is generally assumed from order of values
enum {MON,TUE,WED,THUR,FRI} day; //here the variable is declared without specifying a type name of weekday. this is known as an anonymous declaration
typedef String char*; //declaring "String message" would now be equivalent to "char* message"
const int commonmultiplier=2; //constants must be initialized. this effectively declares a read only variable which cannot be modified after its declaration. constants can also be used in conjunction with pointers for some interesting effects

//--LIFETIME AND SCOPE OF VARIABLES--
int x; //variables declared simply like this are automatically allocated to memory while you are within the function they are declared then cleared afterwards
static int x; //variables declared with static persist in memory, even outside of current function
extern int x; //variables declared extern exist outside of current function

//--CONDITIONAL LOGIC, STATEMENTS and LOOPS
x=y; //simplest statement ever
int x; //a statement is basically anything ending in ; or between { }

{                         //once you open a bracket, anything inside, unless static or extern only exists within those brackets
  int y; //even though this value is not accessible outside of this block, if it is within the same function the same variable name cannot be used
}

if (x) somefunc();                 //if x is true or not 0 then do func
else if (y) someotherfunc();            //optional if you want to specify an else if statement
else if (z) unexpectedfunc(); //multiple else if's can be declared
else exitstrategy();                   //also optional if you want to specify an else statement, but if you're sure that your if statement will always be executed, then ask yourself why you are using if at all

 //equivalent to the above, in a more explicit format which I recommend getting used to. it is longer but easier to read and better formatted. if you like the above better, go learn python instead!
if (x) {
somefunc();
}
else if (y) {
someotherfunc();
}
else if (z) {
unexpectedfunc();
}
else {
exitstrategy();
}

while (x) somefunc();              //execution does not fire unless condition is met and continues in an indefinite loop until condition is no longer true
while (x) {
somefunc();
}

for (int x=0; y<=x; x++) somefunc;          //this is my favorite loop logic since x is disposed of when loop is done. this is roughly equivalent to "int x =0; while(y<=x) {somefunc(); x++;}"
for (int x=0; y<=x; x++) {
somefunc;
someotherfunc;
}

do somefunc(); while (y<=x);          //similar to while, except execution fires at least once even if condition is not met, then continues indefinitely until no longer true. this might be useful for executing a process that should only execute once, but due to some unknown factor, may need to try multiple attempts to execute in case the first one does not take effect
bool executed = false;
do {
executed = somefunc();
}
while (executed!=true);

//x must always be a number in a switch, enums represent numbers so these work too
switch (day) {
  case weekday.MON: mondayfunc(); //case values must be constants
  case weekday.FRI : tuesdayfunc(); //each case is evaluated until a match is met
  default: weekdayfunc();
}

break; //exit a while,do,for loops or switch
continue; //skip any following logic in the loop and go to next interation
return x;  //breaks a loop or exits a function with return value

//--EXCEPTION HANDLING--
//basic exception handling. extremely important to every application
mainfunc() {
try { somefunc(); }
catch (T t) { fixfunc(); mainfunc(); }        //if exception thrown is something expected and you encountered before, handle for it
catch (...) { emailerror(); }        //if something unexpected, which is the usual case
}

//--FUNCTIONS--
//ahh the good stuff
//functions must be preceded by a type, this is there return value. if no type is specified, they default to int
bool somefunc();
int someotherfunc();
someotherfunc(); //this works too and defaults to int, though being explicit is always recommended
void unexpectedfunc(); //if functions do not have a return value, then they are declared as void
bool somefunc(int x, int y); //functions may have a virtually unlimited number of parameters
//the function can be declared somewhere else, then used later. this is useful structurally and helps you stay organized such as by created a .h (header) file for every class and defining functions here, then include the file in your class, then simply use the functions
bool somefunc(int x, int y) {dosomething(x,y);} //or for rapid development it can be done in one shot
bool somefunc(int x) {dosomethingelse(x,0);} //a function with the same name and different parameters can be declared more than once. this is called overloading functions
inline f() //inline is optimized for performance

//standard operators can also be overloaded and declared for specific types
weekday operator+(weekday x, weekday y) { notifyuserofinvalidoperation();} //when adding two days, will execute custom functionality
weekday operator-(weekday x) {notifyuserofinvalidoperation();} // can do the same if you tried to do -day
weekday operator++(int) {notifyuserofinvalidoperation();} //same for ++

extern "elsewhere" {void func();}    //declare a function declared elsewhere

//--CLASSES, STRUCTURES and OBJECTS
//a class is how you define your own type. base value types like int and char are already defined for you, and some other types like enum or String are either already included in the language or part of your compiler/IDE.
//classes are usually called "reference types"
class MyClass {
private: //any variables or functions declared here are only accessible from within the class
int x;
protected: //variables or functions declared here are accessible from within the class and to classes derived from T
void func();
public: //accessible to all. this is easiest to use when learning, but keep in mind, like the public pool, this is not always best :) 
int operator+(int y);   //custom operator for adding your class to an integer

//subclass of your original class. note it is not automatically initialized when a variable of your class type is declared new
class SubClass {
}

T() {} // (optional) known as a constructor, this function is called when the object is declared new
~T() {} // (optional) known as a destructor, this function is called when the object is destroyed

explicit x(int n); //allow you to do O1.x=O2.x but now O1.x=3 (O1 and O2 are objects of your class type)

friend void G_f(); //allow global function f() to access your private members
friend class G_U(); //allows members of class U to access your private members

static int x; //each instance of an object of this class type will all have the same value for x
static void v(); //can call this function externally if accessible even if object not instantiated

virtual //if declared virtual then function or member is essentially just a placeholder, meaning other classes will inherit this class and define the same function and can override it
}

//a variable of your defined type which is created and destroyed in code is known as an OBJECT instance
//the creation process (declaring new in C++) is known as "object initialization" or just simply "instantiation"
MyClass ClassObj = new MyClass(); //note I can pass in params here if they're defined in constructor. ClassObj is now reference to an object instance of MyClass
MyClass OtherObj = new MyClass(); //this would be a second object instance of the same class
delete ClassObj; //will call destructor if it's declared
OtherObj::f(); //will call function f() defined in class

//--CLASS INHERITANCE--
class SuperClass: public MyClass {};     //SuperClass is now a derived class of the base class MyClass, and inherits all public members
class SuperClass: private MyClass {};    //same as above, but inherited members in SuperClass which were public in base class are now private
class SuperClass: public MyClass, public OtherClass {};  //new class can inherit from multiple classes
class CloneClass: public virtual MyClass {}; //essentially clones MyClass. instead of CloneClass having a base class of MyClass, both CloneClass and MyClass will have the same base class that MyClass has

//--EXPRESSIONS--
//most of these you probably inferred by now by reading through above
x++; x--; //add or subtract 1 from itself. equivalent to x=x+1 or x=x-1;

arrayname[i]; //get value in array at index

ClassName::Objectname //should look familiar from System::String earlier. this is explicit syntax when declaring an object defined in different class
NamespaceName::X //same applies for namespaces
::X //global

classname.membername //access function or variable in initialized class
pointername->membername //access function or variable pointed to by pointername

typeid(x); //get type of x
(int)x; ///implicit conversion of x to an integer.
dynamic_cast<T>(x) //performs conversion and checks it when the application is run
static_cast<T>(x) //performs conversion and does not check it
reinterpret_cast<T>(x) //bit conversion
const_cast<T>(x) //performs conversion and remaining variable is type without constant

sizeof(x) //size in bytes of object
sizeof(T) //size in bytes of type

new T //returns address of newly created T
new T(x, y) //returns address of newly created T, contains parameters
new T[x] //address of newly allocated T define in array
delete p_addr //destroy object and free resources which was earlier declared new at address
delete[] p_addr //destroy array of objects stored at address

//--EVALUATIONS and  EXPRESSIONS--
x * y //multiply
x / y //divide, rounded down
x % y //modulus, divides x and y and returns the remainder
x + y //add, different functionality with pointers
x - y //substract, different functionality with pointers
x << y // x shifted y bits to left
x >> y // x shifted y bits to right
x < y //x is less than, returns bool
x <= y //x is less than, returns bool
x > y //x is greater than, returns bool
x >= y //x is greater than or equal to y, returns bool
x == y //equals, returns bool
x != y //not equal, returns bool
x & y //bitwise and
x ^ y //bitwise exclusive or
x | y //bitwise or
x && y //x and y (doesn't get to y if x is false)
((1==1) && (2==2)) //example of above syntax
x || y //x or y (doesn't get to y if x is false)
((1==1)||(2==2)) //example of above syntax
x = y //assign value in y to x
x += y //x = x + y
-= *= /= <<= >>= &= |= ^= //same as above

x ? y : z;                 // return y if x is true (nonzero), else return z
(1==1) ? dosomething() : dosomethingelse(); //example of above

throw x; //throw an exception. if not caught within try catch will probably exit application

x , y //evalutates x and y and returns y
int returnvalue = dosomething, dosomethingthatreturns();

~x //bitwise complement of x
!x //returns opposite of x, if x is true, returns false. if x is 1, returns 0.
-x //unary minus
+x //unary plus
&x //address of x (used in conjunction with pointers)
*p // value pointed to by address p (*&x equals x)

Hello World

A namespace is basically exactly what it’s called. The name which you used to identify your application from code, which wraps all your classes and occupies a defined “space” on your computer/in memory.

//So basically when you see:
using namespace System;
//or on non Windows machines
using namespace std;
//this is saving you from having to write out System::SomeClass or std::SomeFunction

The information in the above cheat sheet is useful, but you’ll need to reference other code to actually make some magic. This is where namespaces come in. See example below for basic “hello world” app which uses standard namespaces to write output to your console.

Unix:

#include <iostream.h>
//OR
#include <stdio.h>

using namespace std;
int main() {
cout << "Hello World"; //write to console window
char* s;
cin >> s; //pause for input key so you can actually see what was written
return 0;
}

Windows:

using namespace System;
int main(array<System::String ^> ^args)
{
Console::WriteLine("Hello World"); //technically not an output stream, but essentially does the same thing as cout
Console::ReadLine(); //approximately the same as cin
return 0;
}

A few things were not covered, such as pointers, using typedef in classes, overloading and overriding functions and common string operations as well as other notable “common” operations defined in standard namespaces. Hopefully this will help serve as a quick reference to you in the future if you decide to learn more or take a break for awhile and need to quickly brush up.

References
Sourcepole (Matt Mahoney), http://www.sourcepole.com/sources/programming/cpp/cppqref.html
Cplusplus.com, http://www.cplusplus.com/doc/tutorial/constants/
MSDN (Visual Studio Express), http://www.microsoft.com/express/Downloads/
Eclipse (C/C++ page), http://www.eclipse.org/downloads/moreinfo/c.php
Mono, http://www.mono-project.com/Main_Page
(MSDN) Windows Automation API 3.0, http://support.microsoft.com/kb/981741
Visual Studio “nosplash” startup speed, http://geekswithblogs.net/MikeParks/archive/2010/05/12/visual-studio-2010—faster-startup.aspx

Ruby Quick Code Reference

CRUD:

#Create
o = ObjectName.new
o.propname1 = "value"
o.propname2 = "othervalue"
o.save

#Read
ObjectName.find(1)

#Update
o = ObjectName.find(1)
ObjectName.propname1 = "new value"
ObjectName.save

#Delete
o = ObjectName.find(1)
o.destroy

Create (alternate syntax):

#syntax 1 above

#syntax 2
o = ObjectName.new(:propname1 => "value", :propname2 => "other value")
o.save

#syntax 3
ObjectName.create(:propname1 => "value", :propname2 => "other value")

Read (alternate syntax):

ObjectName.find(1) #single
ObjectName.find(1,2,3) #array
ObjectName.first #first item
ObjectName.last #last item
ObjectName.all #all items
ObjectName.count #total number of all items
ObjectName.order(:propname) #same as tsql order by
ObjectName.limit(10) #10 items
ObjectName.where(:propname => "value") #where propname==value
ObjectName.where(:propname => "value").order(:propname).limit(10) #ruby calls this "method chaining"

Update (alternate syntax):

#type 1
o = ObjectName.find(1)
o.propname = "value"
o.save

#type 2
o = ObjectName.find(1)
o.attributes = {:propname => "value", :propname2 => "other value"}
o.save

#type3
o = ObjectName.find(1)
o.update_attributes(:propname => "value", :propname2 => "other value")

Delete (alternate syntax):

#type 1
o = ObjectName.find(1)
o.destroy

#type 2
ObjectName.find(1).destroy

#wouldn't recommend
ObjectName.destroy_all

References
Railsforzombies.org

MySQL Quick Reference

Import CSV:

LOAD DATA LOCAL INFILE '/filename.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1, filed2, field3);

References
http://www.pantz.org/software/mysql/mysqlcommands.html
http://support.modwest.com/content/6/253/en/how-do-i-import-delimited-data-into-mysql.html

Quick Cisco IOS Reference

As a general word of caution, if you have no experience with Cisco or other CLI based router admin (such as linux and red hat derivatives), I would not recommend diving in unless you have a lot of spare time on your hands in the event that you accidentally delete all routing tables or cause other serious problems!

Even in a professional business environment, due to some differences between IOS and hardware versions and the need to reboot for some settings to take effect, it’s always a good idea to perform changes after hours when critical business operations will not be interrupted by internet or service downtime.

Official Cisco IOS Manual, http://www.cisco.com/en/US/docs/ios/12_3/featlist/sec_vcg.html

Manipulate NAT and other general help references and top hits on Google for common Cisco beginner questions:
http://www.nthelp.com/handy_cisco.htm
http://www.loeppenthien.dk/Network_IOS.asp
http://www.simulationexams.com/tutorials/ccna/cisco-ios.htm
http://www.networkclue.com/routing/Cisco/IOS/index.aspx
http://www.oreillynet.com/pub/a/network/2002/01/02/ciscotips.html
http://portforward.com/english/routers/port_forwarding/Cisco/Cisco800Series/default.htm

PHP Quick Reference

Escape sequences for print output:

\" - double quote
\' - single quote
\n - new line
\t - tab
\r - carriage return
\$ - dollar sign
\\ - backslash

Max integer and float size (overflow):

//on a 32-bit system
$large_number = 2147483647;
var_dump($large_number);                     // int(2147483647)

$large_number = 2147483648;
var_dump($large_number);                     // float(2147483648)

$million = 1000000;
$large_number =  50000 * $million;
var_dump($large_number);                     // float(50000000000)

//on a 64-bit system
$large_number = 9223372036854775807;
var_dump($large_number);                     // int(9223372036854775807)

$large_number = 9223372036854775808;
var_dump($large_number);                     // float(9.2233720368548E+18)

$million = 1000000;
$large_number =  50000000000000 * $million;
var_dump($large_number);                     // float(5.0E+19)

generate random number:

echo rand() . "\n";
echo rand() . "\n";

echo rand(5, 15);

Get Current Page Name:

function curPageName() {
 return substr($_SERVER["SCRIPT_NAME"],strrpos($_SERVER["SCRIPT_NAME"],"/")+1);
}

Came across the following on stackoverflow while looking for the PHP equivalent of string.format.

Sprintf (similar to php printf, in c# string.format):

$filter = "content:%1$s title:%1$s^4.0 path.title:%1$s^4.0 description:%1$s ...";
$filter = sprintf($filter, "Cheese");

//OR

function format() {
    $args = func_get_args();
    if (count($args) == 0) {
        return;
    }
    if (count($args) == 1) {
        return $args[0];
    }
    $str = array_shift($args);
    $str = preg_replace_callback('/\\{(0|[1-9]\\d*)\\}/', create_function('$match', '$args = '.var_export($args, true).'; return isset($args[$match[1]]) ? $args[$match[1]] : $match[0];'), $str);
    return $str;
}

References
StackOverflow, “C# String.Format() Equivalent in PHP?”,
WebCheatSheet, http://www.webcheatsheet.com/PHP/get_current_page_url.php
PHP.Net, http://php.net

MSSQL Quick Reference

Snippets below have been condensed from their original sources for brevity. See references for original articles.

Great reference on joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Visual_SQL_JOINS_V2

Show all tables in specific database: (MSSQL equivalent of MySql “show tables”)

select name from <database name>..sysobjects where xtype = 'U'

Insert into:

INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists;

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists 
    WHERE MusicianID > 3;

INSERT INTO Residents (Name, Occupation)
    SELECT Name, Occupation FROM Immigration 
    WHERE Residency = 'granted';

INSERT INTO Insurance (Name) 
    SELECT Employee.Username FROM Employee 
    INNER JOIN Project ON Employee.EmployeeID = Project.EmployeeID 
    WHERE Project.ProjectName = 'Hardwork';

Insert if not exists:

IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom ANDApplication = Calculator) BEGIN
 INSERT INTO dbo.Applications 
 (Date, Username, Application, Version) 
 VALUES
 ('3/10/2009', 'Tom', 'Calculator', '2.0') 
END

Trigger Syntax Structure:

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS 
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ] 
[ ...n ] 
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
{ comparison_operator } column_bitmask [ ...n ] 
} ] 
sql_statement [ ...n ] 
} 
} 

Basic If, Then , Else and case:

--longer
create proc sp_generic (@cust int, @type int)
if @type = 1
 select * from customer where customerid = @cust
else
 Select * from deleted_customers where customerid = @cust

--shorter
create proc sp_generic (@deptid int )
select * from employees where departmentid = case when @dept >0 then @dept else departmentid end
order by departmentid

Insert Trigger:

CREATE TRIGGER trig_addAuthor 

ON authors 

FOR INSERT 

AS

-- Get the first and last name of new author 

DECLARE @newName VARCHAR(100) 

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

-- Print the name of the new author 

PRINT 'New author "' + @newName + '" added.'

Update Trigger:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER Person.reminder;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
-- Test the trigger.
UPDATE Person.Address
SET PostalCode = 99999
WHERE PostalCode = '12345';
GO

Get and/or compare today’s date:

convert(varchar,CURRENT_TIMESTAMP,101)
convert(varchar(12),getdate(),101)

Insert or Update with case statements:

USE [mydatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_manufacturedrobots_count]
	@mfgid int,
	@robotid int,
	@paintbotcount int=null,
	@drillbotcount int=null,
	@hammerbotcount int=null
AS
BEGIN

	SET NOCOUNT ON;

declare @id int
select @id = id from manufacturedrobots where mfgid=@mfgid and robotid=@robotid
if @id is not null
update manufacturedrobots set 
paintbotcount=case
when @paintbotcount is null then paintbotcount 
else @paintbotcount end, 
favorite=case
when @drillbotcount is null then drillbotcount 
else @drillbotcount end, 
checkedin=case
when @hammerbotcount is null then hammerbotcount 
else @hammerbotcount end
where id=@id
else
insert into manufacturedrobots (paintbotcount,drillbotcount,hammerbotcount) values  (@paintbotcount,@drillbotcount,@hammerbotcount)

END

References:
MSDN, http://msdn.microsoft.com/en-us/library/ms187326.aspx
DevGuru, http://www.devguru.com/technologies/t-sql/7124.asp
DevArticleshttp://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/
MSDN, “How to debug stored procedures in Visual Studio .NET”, http://support.microsoft.com/kb/316549
CodeGuru, http://www.codeguru.com/forum/showthread.php?t=473102
DaniWeb, http://www.daniweb.com/forums/thread43719.html

Follow

Get every new post delivered to your Inbox.