Life in Singapore

Its new life, new day and one more new place in my diary.IMG_0915

I come to this place just 2 months back but look like living here from years. The place is so small in dimensions that some time you feel like week would have been more then enough to see entire Singapore.

I never thought of coming to this place anytime but one lazy day in Pune got call from Barclays Singapore office about my availability and interest in working with them. Rest is just happened… an Jan/2009 I’m here.

So far its good experience of meeting some unknown people from globe. And now the next phase of interacting and working more with them started.

 

FU

Requirement analysis (RA)

Requirement analysis (RA)

Systems engineering and software engineering, encompasses those tasks that go into determining the needs or conditions to meet for a new or altered product, taking account of the possibly conflicting requirements of the various stakeholders, such as beneficiaries or users.

Requirements analysis is critical to the success of a development project.

Requirements must be actionable, measurable, testable, related to identified business needs or opportunities, and defined to a level of detail sufficient for system design.

RA consist of 3 parts

  1. Requirement Gathering: Understand what is the requirement
  2. Analyzing requirement: Recheck if requirement is unclear, incomplete, ambiguous, or contradictory, and then resolving these issues
  3. Recording requirement: Document requirement in the form use cases or prototyping

RA workbook

  1. Identify stakeholders
  2. Interview stakeholders – Understand their requirement or visualize there requirement
  3. Measurable goals – Create goals of what has achieved so far. Once a small set of critical, measured goals been established, rapid prototyping and short iterative development phases may proceed to deliver actual stakeholder value long before the project is half over.
  4. Prototype – They are mock ups of application. This allows users to visualize an application that hasn’t yet been constructed. This helps to prevent confusion over the final visual look and feel of the application.
  5. Use case – Provides one or more scenarios that convey how the system should interact with the end user or another system to achieve a specific business goal. It simply shows the steps that a user follows to perform a task. All the ways that users interact with a system can be described here. Use case is also known as functional requirement.

Unix Shell Scripting

This post will help administrator and novice UNIX programmer to understand and write strong as well as useful shell programs. I will talks on shell syntax and few simple day-to-day utilities for UNIX. Will discussing mainly Bourn shell because of its widely acclaimed popularity and usage among the UNIX world.

Many standard utilities (rdist, make, cron, etc.) allow you to specify a command to run at a certain time. Usually, this command is simply passed to the Bourne shell, which means that you can execute whole scripts, should you choose to do so. Steve Bourne, wrote the Bourne shell which appeared in the Seventh Edition Bell Labs Research version of Unix.

Lastly, UNIX runs Bourne shell scripts when it boots. If you want to modify the boot-time behavior of a system, you need to learn to write and modify Bourne shell scripts. It said there are 95% shell code written in Bourne shell. Code written in Bourne shell is compatible with shell scripting like ksh, bash & zsh. That means with minimal or no change one could port shell script written in Bourne shell to ksh or bash or zsh.

*Bash is popular among Linux users.

There are couple of other shells available which I will not be talking are C shell ( Used by C program to create programming rich shell) this is incompatible to Bourne shell. The C shell, csh, and its variant tcsh is a fine interactive shell (I use tcsh), but is a lousy shell for writing scripts.

Lets understand what is an executable files, they also know as program or binary executables, This files are machine readable files and human eyes can not make sense out of it. Others only contain text, knows as script. They are interpreter scripts like awk, sed, perl and many more.

Learning Shell Scripting

#!/bin/sh

The first line of any script must begin with #!, followed by the name of the interpreter. Some versions of UNIX allow whitespace between #! and the name of the interpreter. Others do not. Hence, if you want your script to be portable, leave out the blank.

A script, like any file that can be run as a command, needs to be executable: save this script as rotatelog and run

chmod +x rotatelog
to make it executable.

You can now run it by running
./rotatelog

Unlike some other operating systems, UNIX allows any program to be used as a script interpreter. This is why people talk about “a Bourne shell script” or “an awk script.” One might even write a more script, or an ls script (though the latter wouldn’t be terribly useful). Hence, it is important to let Unix know which program will be interpreting the script.

When Unix tries to execute the script, it sees the first two characters (#!) and knows that it is a script. It then reads the rest of the line to find out which program is to execute the script. For a Bourne shell script, this will be /bin/sh. Hence, the first line of our script must be

#!/bin/sh

After the command interpreter, you can have one, and sometimes more, options. Some flavors of Unix only allow one, though, so don’t assume that you can have more.

Variables

sh allows you to have variables, just like any programming languages. Variables do not need to be declared.

To set a sh variable, use VAR=value

And to use the value of the variable later, use $VAR or ${VAR}

The latter syntax is useful if the variable name immediately followed by other text:

#!/bin/sh

COLOR=yellow

echo This looks $COLORish

echo This seems ${COLOR}ish

prints

This looks

This seems yellowish

There is only one type of variable in sh: strings.

This is somewhat limited, but is sufficient for most purposes.

Local vs. environment variables

A sh variable can be either a local variable or an environment variable. They both work the same way; the only difference lies in what happens when the script runs another program (which, as we saw earlier, it does all the time).

Environment variables are passed to subprocesses. Local variables are not.

By default, variables are local. To turn a local variable into an environment variable,

use

export VAR

Here’s a simple wrapper for a program:

#!/bin/sh

NETSCAPE_HOME=/usr/imports/libdata

CLASSPATH=$NETSCAPE_HOME/classes

export CLASSPATH $NETSCAPE_HOME/bin/netscape.bin

Here, NETSCAPE_HOME is a local variable; CLASSPATH is an environment variable. CLASSPATH will be passed to netscape.bin (netscape.bin uses the value of this variable to find Java class files); NETSCAPE_HOME is a convenience variable that is only used by the wrapper script; netscape.bin doesn’t need to know about it, so it is kept local.

The only way to unexport a variable is to unset it:

unset VAR

This removes the variable from the shell’s symbol table, effectively making as if it had never existed; as a side effect, the variable is also unexported.

Also, if you have a function by the same name as the variable, unset will also delete that function. Since you may want to use this variable later, it is better not to define it in the first place.

Also, note that if a variable was passed in as part of the environment, it is already an environment variable when your script starts running. If there is a variable that you really don’t want to pass to any subprocesses, you should unset it near the top of your script. This is rare, but it might conceivably happen.

If you refer to a variable that hasn’t been defined, sh substitutes the empty string.

#!/bin/sh

echo aaa $FOO bbb

echo xxx${FOO}yyy

prints

aaa bbb xxxyyy

Special variables

sh treats certain variables specially: some are set for you when your script runs, and some affect the way commands are interpreted.

Command-line arguments

The most useful of these variables are the ones referring to the command-line arguments. $1 refers to the first command-line argument (after the name of the script), $2 refers to the second one, and so forth, up to $9.

If you have more than nine command-line arguments, you can use the shift command: this discards the first command-line argument, and bumps the remaining ones up by one position: $2 becomes $1, $8 becomes $7, and so forth.

The variable $0 (zero) contains the name of the script (argv[0] in C programs).

Often, it is useful to just list all of the command-line arguments.

For this, sh provides the variables $* (star) and $@ (at). Each of these expands to a string containing all of the command-line arguments, as if you had used $1 $2 $3…

The difference between $* and $@ lies in the way they behave when they occur inside double quotes: $* behaves in the normal way, whereas $@ creates a separate double-quoted string for each command-line argument.

That is, “$*” behaves as if you had written “$1 $2 $3″, whereas “$@” behaves as if you had written “$1″ “$2″ “$3″.

Finally, $# contains the number of command-line arguments that were given.

Other special variables

$? gives the exit status of the last command that was executed. This should be zero if the command exited normally.

$- lists all of the options with which sh was invoked. See sh(1) for details.

$$ holds the PID of the current process.

$! holds the PID of the last command that was executed in the background.

$IFS (Input Field Separator) determines how sh splits strings into words.

Quasi-variable constructs

The ${VAR} construct is actually a special case of a more general class of constructs:

${VAR:-expression}

Use default value: if VAR is set and non-null, expands to $VAR. Otherwise, expands to expression.

${VAR:=expression}

Set default value: if VAR is set and non-null, expands to $VAR. Otherwise, sets VAR to expression and expands to expression.

${VAR:?[expression]}

If VAR is set and non-null, expands to $VAR. Otherwise, prints expression to standard error and exits with a non-zero exit status.

${VAR:+expression}

If VAR is set and non-null, expands to the empty string. Otherwise, expands to expression.

${#VAR}

Expands to the length of $VAR.

The above patterns test whether VAR is set and non-null. Without the colon, they only test whether VAR is set.

Pattern-matching

sh supports a limited form of pattern-matching. The operators are

* Matches zero or more characters.

? Matches exactly one character.

[range] Matches any character in range.

Range can be either a list of characters that match, or two endpoints separated by a dash: [ak3] matches either a, k, or 3; [a-z] matches any character in the range a through z; [a-mz] matches either a character in the range a through m, or z. If you wish to include a dash as part of the range, it must be the first character, e.g., [-p] will match either a dash or p.

When an expression containing these characters occurs in the middle of a command, sh substitutes the list of all files whose name matches the pattern. This is known as “globbing.” Otherwise, these are used mainly in the case construct.

As a special case, when a glob begins with * or ?, it does not match files that begin with a dot. To match these, you need to specify the dot explicitly (e.g., .*, /tmp/.*).

Note to MS-DOS users: under MS-DOS, the pattern *.* matches every file. In sh, it matches every file that contains a dot.

Quoting

If you say something like

echo * MAKE $$$ FAST *

it won’t do what you want: first of all, sh will expand the *s and replace them with a list of all the files in the current directory. Then, since any number of tabs or blanks can separate words, it will compress the three spaces into one. Finally, it will replace the first instance of $$ with the PID of the shell. This is where quoting comes in.

sh supports several types of quotes. Which one you use depends on what you want to do.

Backslash

Just as in C strings, a backslash (“\”) removes any special meaning from the character that follows. If the character after the backslash isn’t special to begin with, the backslash has no effect. The backslash is itself special, so to escape it, just double it: \\.

Single quotes

Single quotes, such as ‘foo’ work pretty much the way you’d expect: anything inside them (except a single quote) is quoted.

You can say

echo ‘* MAKE $$$ FAST *’

and it’ll come out the way you want it to.

Note that a backslash inside single quotes also loses its special meaning, so you don’t need to double it. There is no way to have a single quote inside single quotes.

Double quotes, such as

“foo”

preserve spaces and most special characters. However, variables and backquoted expressions are expanded and replaced with their value.

If you have an expression within backquotes (also known as backticks), e.g.,

`cmd`

the expression is evaluated as a command, and replaced with whatever the expression prints to its standard output. Thus,

echo You are `whoami`

prints

You are irfan

Built-in commands

sh understands several built-in commands, i.e., commands that do not correspond to any program. These commands include:

{ commands ; }, ( commands )

Execute commands in a subshell. That is, run them as if they were a single command. This is useful when I/O redirection is involved, since you can pipe data to or from a mini-script inside a pipeline.

The { commands; } variant is somewhat more efficient, since it doesn’t spawn a true subshell. This also means that if you set variables inside of it, the changes will be visible in the rest of the script.

: (colon)

Does nothing. This is generally seen as

: ${VAR:=default}

. filename

The dot command reads in the specified filename, as if it had occurred at that place in the script.

bg [job], fg [job]

bg runs the specified job (or the current job, if none is specified) in the background. fg resumes the specified job (or the current job, if none is specified) in the foreground.

Jobs are specified as %number. The jobs command lists jobs.

cd [dir]

Sets the current directory to dir. If dir is not specified, sets the current directory to the home directory.

pwd

Prints the current directory.

echo args

Prints args to standard output.

eval args

Evaluates args as a sh expression. This allows you to construct a string on the fly (e.g., using a variable that holds the name of a variable that you want to set) and execute it.

exec command

Runs the specified command, and replaces the current shell with it. That is, nothing after the exec statement will be executed, unless the exec itself fails.

exit [n]

Exit the current shell with exit code n. This defaults to zero.

kill [-sig] %job

Send signal sig to the specified job. sig can be either numeric or symbolic. kill -l lists all available signals. By default, sig is SIGTERM (15).

read name…

Reads one line from standard input and assigns it to the variable name. If several variables name1, name2, name3 etc. are specified, then the first word of the line read is assigned to name1, the second to name2, and so forth. Any remaining words are assigned to the last variable.

set [+/-flag] [arg]

With no arguments, prints the values of all variables.set -x turns on the x option to sh; set +x turns it off.set args… sets the command-line arguments to args.

test expression

Evaluates a boolean expression and exits with an exit code of zero if it is true, or non-zero if it is false. See test for more details.

trap [command sig]…

If signal sig is sent to the shell, execute command. This is useful for exiting cleanly (e.g., removing temporary files etc.) when the script is interrupted.

ulimit

Print or set system limits on resource usage.

umask [nnn]

Sets the umask to nnn (an octal number). With no argument, prints the current umask. This is most useful when you want to create files, but want to restrict who can read or write them.

wait [n]

Wait for the background process whose PID is n to terminate. With no arguments, waits for all of the background processes to terminate.

Bear in mind that the list of builtins varies from one implementation to another, so don’t take this list as authoritative.

Flow control

sh supports several flow-control constructs, which add power and flexibility to your scripts.

if

The if statement is a simple conditional. You’ve seen it in every programming language. Its syntax is

if condition ; then
commands
[elif condition ; then
commands]…
[else
commands]
fi

That is, an if-block, optionally followed by one or more elif-blocks (elif is short for “else if”), optionally followed by an else-block, and terminated by fi.

The if statement pretty much does what you’d expect: if condition is true, it executes the if-block. Otherwise, it executes the else-block, if there is one. The elif construct is just syntactic sugar, to let you avoid nesting multiple if statements.

#!/bin/sh

myname=`whoami`

if [ $myname = root ]; then

echo “Welcome to FooSoft 3.0″

else

echo “You must be root to run this script”

exit 1

fi

The more observant among you (or those who are math majors) are thinking, “Hey! You forgot to include the square brackets in the syntax definition!”

Actually, I didn’t: [ is actually a command, /bin/[, and is another name for the test command. See below for details.

This is why you shouldn't call a test program test: if you have ``.'' at the end of your path, as you should, executing test will run /bin/test.

The condition can actually be any command. If it returns a zero exit status, the condition is true; otherwise, it is false. Thus, you can write things like

#!/bin/sh

user=arnie

if grep $user /etc/passwd; then

echo "$user has an account"

else

echo "$user doesn't have an account"

fi

while

The while statement should also be familiar to you from any number of other programming languages. Its syntax in sh is

while condition; do
commands
done

As you might expect, the while loop executes commands as long as condition is true. Again, condition can be any command, and is true if the command exits with a zero exit status.

A while loop may contain two special commands: break and continue.

break exits the while loop immediately, jumping to the next statement after the done.

continue skips the rest of the body of the loop, and jumps back to the top, to where condition is evaluated.

for

The for loop iterates over all of the elements in a list. Its syntax is

for var in list; do
commands
done

list is zero or more words. The for construct will assign the variable var to each word in turn, then execute commands. For example:

#!/bin/sh

for i in foo bar baz "do be do"; do

echo "$i"

done

will print

foo bar baz do be do

A for loop may also contain break and continue statements. They work the same way as in the while loop.

Case

The case construct works like C's switch statement, except that it matches patterns instead of numerical values. Its syntax is

case expression in
pattern)
commands
;;
...
esac

expression is a string; this is generally either a variable or a backquoted command.

pattern is a glob pattern (see globbing).

The patterns are evaluated in the order in which they are seen, and only the first pattern that matches will be executed. Often, you'll want to include a ``none of the above'' clause; to do this, use * as your last pattern.

IO

A command's input and/or output may be redirected to another command or to a file. By default, every process has three file descriptors: standard input (0), standard output (1) and standard error (2). By default, each of these is connected to the user's terminal.

However, one can do many interesting things by redirecting one or more file descriptor:

< filename

Connect standard input to the file filename. This allows you to have a command read from the file, rather than having to type its input in by hand.

> filename

Connect standard output to the file filename. This allows you to save the output of a command to a file. If the file does not exist, it is created. If it does exist, it is emptied before anything happens.

(Exercise: why doesn't cat * > zzzzzzz work the way you'd expect?)

>> filename

Connects standard output to the file filename. Unlike >, however, the output of the command is appended to filename.

<<word

This construct isn't used nearly as often as it could be. It causes the command's standard input to come from... standard input, but only until word appears on a line by itself. Note that there is no space between << and word.

This can be used as a mini-file within a script, e.g.,

cat > foo.c <<EOT

#include <stdio.h>

main() {

printf("Hello, world!\n");

} EOT

It is also useful for printing multiline messages, e.g.:

line=13 cat <<EOT An error occurred on line $line. See page 98 of the manual for details. EOT

As this example shows, by default, << acts like double quotes (i.e., variables are expanded). If, however, word is quoted, then << acts like single quotes.

<&digit

Use file descriptor digit as standard input.

>&digit

Use file descriptor digit as standard output.

<&-

Close standard input.

>&-

Close standard output.

command1 | command2

Creates a pipeline: the standard output of command1 is connected to the standard input of command2. This is functionally identical to

command1 > /tmp/foo
command2 < /tmp/foo

except that no temporary file is created, and both commands can run at the same time

There is a proverb that says, ``A temporary file is just a pipe with an attitude and a will to live.'' Any number of commands can be pipelined together.

command1 && command2

Execute command1. Then, if it exited with a zero (true) exit status, execute command2.

command1 || command2

Execute command1. Then, if it exited with a non-zero (false) exit status, execute command2.

If any of the redirection constructs is preceded by a digit, then it applies to the file descriptor with that number, rather than the default (0 or 1, as the case may be). For instance,

command 2>&1 > filename

associates file descriptor 2 (standard error) with the same file as file descriptor 1 (standard output), then redirects both of them to filename.

This is also useful for printing error messages:

echo "Danger! Danger Will Robinson!" 1>&2

Note that I/O redirections are parsed in the order they are encountered, from left to right. This allows you to do fairly tricky things, including throwing out standard output, and piping standard output to a command.

Functions

When a group of commands occurs several times in a script, it is useful to define a function. Defining a function is a lot like creating a mini-script within a script.

A function is defined using

name () {
commands
}

and is invoked like any other command:

name args...

You can redirect a function's I/O, embed it in backquotes, etc., just like any other command.

One way in which functions differ from external scripts is that the shell does not spawn a subshell to execute them. This means that if you set a variable inside a function, the new value will be visible outside of the function.

A function can use return n to terminate with an exit status of n. Obviously, it can also exit n, but that would terminate the entire script.

Function arguments

A function can take command-line arguments, just like any script. Intuitively enough, these are available through $1, $2... $9 just like the main script.

Useful utilities

There are a number of commands that aren't part of sh, but are often used inside sh scripts. These include:

basename

basename pathname prints the last component of pathname:

basename /foo/bar/baz

prints

baz

dirname

The complement of basename: dirname pathname prints all but the last component of pathname, that is the directory part: pathname:

dirname /foo/bar/baz

prints

/foo/bar

/bin/[ is another name for /bin/test. It evaluates its arguments as a boolean expression, and exits with an exit code of 0 if it is true, or 1 if it is false.

If test is invoked as [, then it requires a closing bracket ] as its last argument. Otherwise, there must be no closing bracket.

test understands the following expressions, among others:

-e filename

True if filename exists.

-d filename

True if filename exists and is a directory.

-f filename

True if filename exists and is a plain file.

-h filename

True if filename exists and is a symbolic link.

-r filename

True if filename exists and is readable.

-w filename

True if filename exists and is writable.

-n string

True if the length of string is non-zero.

-z string

True if the length of string is zero.

string

True if string is not the empty string.

s1 = s2

True if the strings s1 and s2 are identical.

s1 != s2

True if the strings s1 and s2 are not identical.

n1 -eq n2

True if the numbers n1 and n2 are equal.

n1 -ne n2

True if the numbers n1 and n2 are not equal.

n1 -gt n2

True if the number n1 is greater than n2.

n1 -ge n2

True if the number n1 is greater than or equal to n2.

n1 -lt n2

True if the number n1 is less than n2.

n1 -le n2

True if the number n1 is less than or equal to n2.

! expression

Negates expression, that is, returns true iff expression is false.

expr1 -a expr2

True if both expressions, expr1 and expr2 are true.

expr1 -o expr2

True if either expression, expr1 or expr2 is true.

( expression )

True if expression is true. This allows one to nest expressions.

Note that lazy evaluation does not apply, since all of the arguments to test are evaluated by sh before being passed to test. If you stand to benefit from lazy evaluation, use nested ifs.

echo

echo is a built-in in most implementations of sh, but it also exists as a standalone command.

echo simply prints its arguments to standard output. It can also be told not to append a newline at the end: under BSD-like flavors of Unix, use

echo -n “string”

Under SystemV-ish flavors of Unix, use

echo “string\c”

awk

Awk (and its derivatives, nawk and gawk) is a full-fledged scripting language. Inside sh scripts, it is generally used for its ability to split input lines into fields and print one or more fields. For instance, the following reads /etc/passwd and prints out the name and uid of each user:

awk -F : ‘{print $1, $3 }’ /etc/passwd

The -F : option says that the input records are separated by colons. By default, awk uses whitespace as the field separator.

sed

Sed (stream editor) is also a full-fledged scripting language, albeit a less powerful and more convoluted one than awk. In sh scripts, sed is mainly used to do string substitution: the following script reads standard input, replaces all instances of “foo” with “bar”, and writes the result to standard output:

sed -e ’s/foo/bar/g’

The trailing g says to replace all instances of “foo” with “bar” on a line. Without it, only the first instance would be replaced.

tee

tee [-a] filename reads standard input, copies it to standard output, and saves a copy in the file filename.

By default, tee empties filename before it begins. With the -a option, it appends to filename.

Debugging

Unfortunately, there are no symbolic debuggers such as gdb for sh scripts. When you’re debugging a script, you’ll have to rely the tried and true method of inserting trace statements, and using some useful options to sh:

The -n option causes sh to read the script but not execute any commands. This is useful for checking syntax.

The -x option causes sh to print each command to standard error before executing it. Since this can generate a lot of output, you may want to turn tracing on just before the section that you want to trace, and turn it off immediately afterward:

set -x # XXX – What’s wrong with this code? grep $user /etc/passwd 1>&2 > /dev/null set +x

Start using PostgreSQL now….

Spend 10 minutes and have PostgreSQL on your own lilo flavor

Purpose of this article to give jump starts to install and performed basic PostgreSQL (pgsql) administrator / user job.

Download binaries from this location ftp://ftp.postgresql.org/pub/source/v8.3.1/postgresql-8.3.1.tar.gz

tar -xzvf postgresql-8.3.1.tar.gz

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su – postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

You are all set with test database, go ahead and start exploring sql commands on that
$ psql test

If you leave off the database name then it will default to your user account name.

In psql, you will be greeted with the following message:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# create sequence pk_seq;
CREATE SEQUENCE

test=# create table my_data (
test(# data_pk integer DEFAULT nextval(‘pk_seq’::text) NOT NULL,
test(# my_value integer
test(# );
CREATE TABLE

test=# insert into my_data (my_value) values (1);
INSERT 17240 1
test=# select * from my_data;
data_pk | my_value
———+———-
1 | 1
(1 row)

test=# insert into my_data (my_value) values (2);
INSERT 17241 1
test=# select * from my_data;
data_pk | my_value
———+———-
1 | 1
2 | 2
(2 rows)

test=# \d
List of relations
Schema | Name | Type | Owner
——–+———+———-+———-
public | my_data | table | postgres
public | pk_seq | sequence | postgres
(2 rows)

test=# \d my_data
Table “public.my_data”
Column | Type | Modifiers
———-+———+——————————————
data_pk | integer | not null default nextval(‘pk_seq’::text)
my_value | integer |

test=# \q
[mst3k@hera ~]$

Spend 10 more minutes and learn how to connect PostgeSQL from Perl

[ ~]$ cat my_data.pl
#!/usr/bin/perl

use strict;

use DBI;

main:
{
my $dbargs = {AutoCommit => 0, PrintError => 1};

my $dbh =
DBI->connect(“dbi:Pg:dbname=test;host=localhost;port=5432;”,
“postgres”,
“”,
$dbargs);

my $sql = “select * from my_data”;
my $sth = $dbh->prepare($sql);
if ($dbh->err()) { die “prepare:\n$DBI::errstr\n”; }

$sth->execute();
if ($dbh->err()) { die “execute:\n$DBI::errstr\n”; }

while(my $hr = $sth->fetchrow_hashref())
{
print “pk: $hr->{data_pk} value: $hr->{my_value}\n”;
}
$dbh->disconnect();

Spend 10 more minutes to learn few more SQL tricks

To use tab as a field separator you can use bash shell features when
launching psql:

psql -A -t -U $DBUSER -d $DB -c “$QUERY” -F $’\t’

When you are already running psql, use this command:

\f’\t’

Apparently, using the single ticks (quotes) causes the \t to be
evaluated by the shell’s command processor.

In order to get tab separated columns, you will also need to disable
“aligned output” with \a.

Here is the entire sequence of commands:

\f’\t’
\a
\o myfile.txt
select * from big_table;
\o

I suspect that if you want true CSV output (comma separated with
quoted fields, etc.) that you will need to write a small Perl script
using a Perl CSV module, and using DBI to talk to Postgres. I’ve
already got a Perl API which makes SQL even easier than DBI, so I
could write the whole application in 10 lines of code.

The following does a date conversion from an integer containing the
Unix timestamp, as well as truncating some columns for brevity and
renaming other columns with long names. \H enables HTML output which
in this case means a file with an HTML table (no <HTML> or <BODY>
tags). This works fine in Firefox, but I haven’t tried it in othwer
browsers. \o is send output to a file. Stop outputting with a blank \o.

\H
\o public_html/summary.html
select cl_pk,who_updated as who,plate,prow,pcol,substring(date ‘1970-01-01′ + screen_date * interval ‘1 second’ from ‘.*\ ‘) as scrn_date,comments,substring(gene_id from ‘^.{5}’)||’…’ as gene,substring(sequence from ‘^.{5}’)||’…’ as seq,image_file,localization as loc,morphology as morph,migration as mig,secretion as sec,is_valid as ok from clone order by cl_pk;

Here is the same query formatted:

SELECT cl_pk,
who_updated AS who,
plate,
prow,
pcol,
SUBSTRING(DATE ‘1970-01-01′ + screen_date * INTERVAL ‘1 SECOND’ FROM ‘.*\ ‘) AS scrn_date,
comments,
SUBSTRING(gene_id FROM ‘^.{5}’)||’…’ AS gene,
SUBSTRING(sequence FROM ‘^.{5}’)||’…’ AS seq,
image_file,
localization AS loc,
morphology AS morph,
migration AS mig,
secretion AS sec,
is_valid AS ok
FROM clone ORDER BY cl_pk;

This query was run against a single table that looks like:

create table “clone”
(
“cl_pk” integer DEFAULT nextval(‘pk_seq’::text) NOT NULL,
“ec_fk” integer, — foreign key to ec_pk in exp_condition
“plate” varchar(128), — plate name aka plate id
“prow” varchar(128), — plate row A-F
“pcol” integer, — place column 1-12
“lab_book” varchar(256),
“screen_date” integer, — unix timestamp
“userid” integer, — us_pk from usersec table or it’s equivalent
“clone_status” integer, — probably from the type table
“gene_id” varchar(256),
“sequence” text,
“localization” integer, — from the type table
“morphology” integer, — from the type table
“migration” integer, — from the type table
“secretion” boolean,
“comments” text,
“image_file” varchar(256), — image file to display in web page
“who_updated” varchar(256), — last userid to update
“ip_address” varchar(128), — ip address from which record was updated
“original_image_file” varchar(256), — original uploaded image file
is_valid integer DEFAULT 1 — one=valid, zero=invalid
) without OIDs;

Spend 10 more minutes and understand why PostgreSQL is better than Oracle

The following list is not complete, and I’m not an Oracle expert. I’m
facile with Postgres, but would not call myself a Postgres expert
either. Therefore the following list may contain errors. My conclusion
is unchanged: PostgreSQL is the best database.

- Postgres isn’t encumbered by licenses. You can install it as often
as you like on as many computers as you like.

- Postgres is part of Fedora and therefore faster and easier to
install.

- Postgres is much smaller than Oracle. Postgres is around 6MB. Oracle
10g is over 200MB.

- The Postgres Perl DBD driver is part of Fedora. You’ll have to go to
CPAN, download and install the Oracle DBD driver.

- Read the DBD documentation for the Oracle DBD driver. The docs are
full of features that may or may not work. There are loads of
warnings about various features. For whatever reason, the Postgres
DBD driver seems simpler, more robust and more mature.

- Oracle doesn’t seem to provide a “native” Oracle supported DBD
driver. It isn’t clear that Oracle supports the authors of the Perl
DBD driver (maybe they do, maybe they don’t… I can’t tell).

- Postgres allows functions as default values for columns in
tables. Oracle doesn’t.

- The Oracle command line, sqlplus, is a flaming pile of crap. It
doesn’t have any cursor control support, the previous command buffer
is barely accessible, and it is missing tab complete, is appears to
have no way to cancel a command (i.e., no control-C). It looks like
something from the 1970s when a user interface meant a teletype or
card reader. Yes, there is a fancy web interface, but many types of
system administration simply are not feasible via a GUI interface,
especially a web interface.

- The PostgreSQL command line interface, psql, has fully functional
editing, a command buffer, tab complete for commands, tables and
columns, cancel (via control-C). It is wonderful.

- At the command line interface, Postgres has help for every
command. Oracle doesn’t. In fact, Oracle’s sqlplus has no built-in
help for any SQL commands.

- The standard record display from sqlplus is very crude, and
difficult to read.

- Postgres has a “text” datatype which does not require a size
specification. There is no performance penalty for using this data
type. Oracle has LONG and a couple of types of BLOBs, each of which
has several show-stopper limitations.

- Oracle can have only one LONG per table. Postgres can have as many
TEXT fields per table as you like. Oracle apparently can have only
one BLOB per table (perhaps including LONG). Postgres does not have
this limitation.

- When Perl DBI does a fetchrow_hashref() from Oracle, the field names
come back all uppercase. Postgres returns field names lowercase. I
enter all my field names lower case since they are easier to type.

- Oracle’s sqlplus defaults to autocommit off. That’s fine but when
you update a record from sqlplus, any other processes that are
trying to update that record will be locked until you
commit. Perhaps this is why PostgreSQL’s psql command line interface
defaults to autocommit (actually, there isn’t even a setting for it)
and if you want a non-autocommit transaction in psql, you use begin;
command; commit; to explicitly create a transaction.

- Postgres psql has many commands that display and/or manage the
database meta data. There is useful “help” for all these
commands. If you wish, there is also a mode that displays the actual
SQL code used to display/manage the meta data.

kuch shayari aapki nazar…

Naa mohabbat, na dosti ke liye
waqt rukta nahi kissi ke liye

Dil ko apne saza na de yoo.n hee
Iss zamaane’ be-rukhi ke liye

Kal jawaani ka hashr kya hoga
sonch le aaj, do gadhi ke liye

Har koi pyaar doondhta hai yahan
Apni tanha see zindagi ke liye

Waqt ke saath saath chalta rahe
yeh hee behtar hai aadmi ke liye

———————————————

Teray Rang Rang
Teray Rang Rang
Yeh Rang Dhang
Moula Rang Rang

Tunay Ata Kiya
Mainay Bhula Diya
Tunay Phir Say Diya
Na Shukar Kia
Tunay Aur Diya
Deta Hi Gaya
Mu Mod Diyay Daryaon Kay
Menay Hawa Main Udna Seekh Liya
Meri Agli Nazar Sitaaron Par
Phir Bhi Main Teray Sahaaron Par

Teray Rang Rang
Teray Rang Rang
Yeh Rang Rang
Teray Rang Rang

Main Jahan Bhi Jaaon Dunya Main
Teray Jalway Meray Sang Sang

Teray Rang Rang
Teray Rang Rang
Teray Rang Rang
Teray Rang Rang

Main Zaat Paat Mein Oonch Neech Mein Aur Firqon Mein Bata Huwa
Jo Sachai Ko Dhundla Day Dil Esi Gard Say Atta Huwa
Shikwa Nahin Jism Ki Matti Say Par Rooh Bhi Ab Betaab Nahin
Teray Hukam Pay Chalna Aik Taraf Tera Naam Bhi Lena Yaad Nahin
Kam Say Kam Mujhko Lota Day
Woh Rooh Aur Jism Ki Jang Jang

Teray Rang Rang
Teray Rang Rang
Teray Rang Rang
Teray Rang Rang

Main Jahan Bhi Jaaon Dunya Main
Teray Jalway Meray Sang Sang

Way Bulleya Assan Marna Nahin
Dor Peya Koi Hor
Dor Peya Koi Hor
Dor Peya Koi Hor

Na Main Maumin Wich Maseetan
Na Main Wich Kufar Diyan Rayt Aan

Na Main Paakan wich Paleet Aan
Na Main Moosa Na Firown

Ki Jaana’n Main Kon
———————————————
safar to maine kiya tha warna,
saare sazoan saaman uske the

mein to bus raazdaar tha uska,
saare raaz uske the

woh dariya mein pyasa baitha tha,
jabke samandar tamam uske the

woh dhup mein baitha hai chaaw dene ko,
jabke darakht saare sayadaar uske the,

aur yu to bazahir logoan mein maine rizk baata tha,
lekin darparda saare haath uske the,

maine kamaal bulandi pe jakar socha irfan,
yeh to saare kamaal uske the,
———————————————–

E Khuda Aaj Ye Faisla Karde,
Use Mera ya Mujhe Uska Karde.

Bahut Dukh Sahe He Maine,
Koi Khusi Ab Toh Muqadar Karde.

Bahot Muskil Lagta Hai Usse Duur Rehna,
Judai Ke Safar Ko Kum Karde.

Jitna Duur Chale Gaye Woh Mujhse,
Use Utna Kareeb Karde.

Nahi Likha Agar Nasib Me Uska Naam,
To Khatam Kar Ye Zindagi aur Mujhe FANAA Karde
———————————————–
Kitni jaldi yeh mulakat guzar jati hai
pyas bujhti nahi ke barsat guzar jati hai

Apni yaadon se keh do ke yun na aaya kare
neend aati nahi aur raat guzar jati hai
———————————————–
aashiyane ki baat karate ho
dil jalane ki baat karate ho

saari duniya ke ranj-o-gam de kar
muskuraane ki baat karate ho

ham ko apani Khabar nahi yaaro
tum zamaane ki baat karate ho

zikr meraa suna to chid ke kahaa
kis diwane ki baat karate ho

hadasaa tha guzar gaya hoga
kis ke jaane ki baat karate ho
———————————————–
apanii marzi se kaha apane safar ke ham hain
ruKh havaon ka jidhar ka hai udhar ke ham hain

pahale har chiz thi apani magar ab lagataa hai
apane hi ghar mein kisi dusare ghar ke ham hain

waqt ke saath hai mitti kaa safar sadiyon tak
kisako malum kaha ke hain kidhar ke ham hain

chalte rahate hain ki chalna hai musafir ka nasiib
sochte rahte hain ki kis rahaguzar ke ham hain
———————————————–
asar us ko zaraa nahi hota
ranj raahat-fizaa nahi hotaa

tum hamaare kisi tarah na hue
warana duniya mein kya nahi hota

narazgi se dam ruke to ruke
main kisi se Khafa nahi hota

tum mere paas hote ho goyaa
jab koi dusra nahi hota

Sybase iSQL ASE 11.5 drive

COMMAND TOUR

Connect sybase from Linux console
isql -Hpiidb199:9794 -SNYQ_MWDATA -Umware_rw -DGE
2

use DN_Name
go

select DB_Name
go

sp_helpdb DB_Name
go
shows this info
name,db_size,owner,dbid,created,status,device_fragments,size,usage,created,free kbytes
sp_help
go

It shows all the availble objects of following type
view, user table, triggers, system table, stored procedure

sp_help Table_Name
go

shows description of table i.e.
Column_name,Type,Length,Prec,Scale Nulls,Default_name,Rule_name,Access_Rule_name,Identity

set rowcount 2
select * from ge_company
go

Below query will display exactly 2 records from table
select * from dbo.ge_comapany where company_id = 766
go

*Note table should be given with owner, here dbo is owner of ge_company

sp_helpindex ge_company
go

This command will show you list of indexes available into ge_company table
select name from sysobjects where name like “ge_%”

By default these tables will be created automatically when new database created
1> sp_help
2> go

Name Owner Object_type
—————————— —————————— ———————-
syb_auto_db_extend_control dbo user table
sysalternates dbo system table
sysattributes dbo system table
syscolumns dbo system table
syscomments dbo system table
sysconstraints dbo system table
sysdepends dbo system table
sysgams dbo system table
sysindexes dbo system table
sysjars dbo system table
syskeys dbo system table
syslogs dbo system table
sysobjects dbo system table
syspartitions dbo system table
sysprocedures dbo system table
sysprotects dbo system table
sysqueryplans dbo system table
sysreferences dbo system table
sysroles dbo system table
syssegments dbo system table
sysstatistics dbo system table
systabstats dbo system table
systhresholds dbo system table
systypes dbo system table
sysusermessages dbo system table
sysusers dbo system table
sysxtypes dbo system table
Some more tips of Sybase blocking handel

2> sp_block
3> go

no process should be in Absolute blocking spid

spid hostname
————————- —— ———-
Absolute blocking spid is 537 piias1359
Absolute blocking spid is 49 pi944c3n9
Absolute blocking spid is 616 paias1186
One login can play with these commands

Show login user information
> sp_displaylogin
> go

Suid: 3018
Loginame: irfansh

Fullname:
Default Database: env
Default Language:
Auto Login Script:
Configured Authorization:
Locked: NO
Date of Last Password Change: Jun 14 2007 2:01PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)

To change logged in users password
>sp_password old_passwd, new_passwd
>go

Selecting data from Table: querys and functions
> select sum(salary) from emp_tbl
> go

Determining which table columns to join
1> sp_helpjoins ge2_group, ge2_group_apps
2> go

first_pair
—————————— ——————————
group_id group_id

(1 row affected)
(return status = 0)

To find a table’s referenced tables.
1> sp_helpconstraint
2> go

Aggregate Function Result

sum([all|distinct] expression) Total of the (distinct) values in the expression

avg([all|distinct] expression) Average of the (distinct) values in the expression

count([all|distinct] expression) Number of (distinct) non-null values in the expression

count(*) Number of selected rows

max(expression) Highest value in the expression

min(expression) Lowest value in the expression

count vs. count(*)

While count finds the number of non-null values in the expression, count(*)

finds the total number of rows in a table. This statement finds the total number of books:

Adaptive Server ignores any null values in the column on which the

aggregate function is operating for the purposes of the function (except

count(*), which includes them). If you have set ansinull to on, Adaptive

Server returns an error message whenever a null value is ignored. For

more information, see the set command in the Reference Manual.

Use the having clause to display or reject rows defined by the group by

clause. The having clause sets conditions for the group by clause in the

same way where sets conditions for the select clause, except where cannot

include aggregates, while having often does. This example is legal:

select title_id from titles where title_id like “PS%” having avg(price) > $2.0

But this example is not:

select title_id from titles where avg(price) > $20

How the having, group by, and where clauses interact

When you include the having, group by, and where clauses in a query, the

sequence in which each clause affects the rows determines the final

results:

• The where clause excludes rows that do not meet its search conditions.

• The group by clause collects the remaining rows into one group for each unique value in the group by expression.

• Aggregate functions specified in the select list calculate summary values for each group.

• The having clause excludes rows from the final results that do not meet its search conditions.

Sorting query results: the order by clause

The order by clause allows you to sort query results by one or more

columns, up to 31.

How joins work

When you join two or more tables, the columns being compared must have

similar values—that is, values using the same or similar datatypes.

There are several types of joins, such as equijoins, natural joins, and outer

joins. The most common join, the equijoin, is based on equality. The

following join finds the names of authors and publishers located in the

same city:

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

au_fname au_lname pub_name

——– ——– ——————–

Cheryl Carson Algodata Infosystems

Abraham Bennet Algodata Infosystems

(2 rows affected)

This earlier query is an example of an equijoin:

select *

from authors, publishers

where authors.city = publishers.city

In the results of that statement, the city column appears twice. By

definition, the results of an equijoin contain two identical columns.

Because there is usually no point in repeating the same information, one

of these columns can be eliminated by restating the query. The result is

called a natural join.

example of a natural join is:

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

Self-joins and correlation names

Joins that compare values within the same column of one table are called

self-joins. To distinguish the two roles in which the table appears, use

aliases, or correlation names.

select au1.au_fname, au1.au_lname,

au2.au_fname, au2.au_lname

from authors au1, authors au2

where au1.city = “Oakland” and au2.city = “Oakland”

and au1.state = “CA” and au2.state = “CA”

and au1.postalcode = au2.postalcode

Outer joins

Joins that include all rows, regardless of whether there is a matching row,

are called outer joins. Adaptive Server supports both left and right outer

joins. For example, the following query joins the titles and the titleauthor

tables on their title_id column:

select *

from titles, titleauthor

where titles.title_id *= titleauthor.title_id

Inner joins, in which the joined table includes only the rows of the

inner and outer tables that meet the conditions of the on clause.

Outer joins, in which the joined table includes all the rows from the

outer table whether or not they meet the conditions of the on clause.

SubQueries

this query finds the names of all the publishers who publish

business books:

select pub_name

from publishers

where exists

(select *

from titles

where pub_id = publishers.pub_id

and type = “business”)

pub_name

to find the names of publishers who do not publish business

books, the query is:

select pub_name

from publishers

where not exists

(select *

from titles

where pub_id = publishers.pub_id

and type = “business”)

Using and Creating Datatypes

Datatypes by

category Synonyms Range Bytes of storage

Exact numeric: integers

tinyint 0 to 255 (Negative numbers are not

permitted.)

1

smallint 215 -1 (32,767) to -215 (-32,768) 2

int integer 231 -1 (2,147,483,647) to

-231 (-2,147,483,648

4

Exact numeric: decimals

numeric (p, s) 1038 -1 to -1038 2 to 17

decimal (p, s) dec 1038 -1 to -1038 2 to 17

Approximate numeric

float (precision) machine dependent 4 for default precision < 16,

8 for default precision >= 16

double precision machine dependent 8

real machine dependent 4

Money

smallmoney 214,748.3647 to -214,748.3648 4

money 922,337,203,685,477.5807 to

-922,337,203,685,477.5808

8

Date/time

smalldatetime January 1, 1900 to June 6, 2079 4

datetime January 1, 1753 to December 31,

9999

8

Character

char(n) character pagesize n

varchar(n) character varying,

char varying

pagesize actual entry length

unichar Unicode character pagesize n * @@unicharsize

(@@unicharsize equals 2)

univarchar Unicode character

varying, char varying

pagesize actual number of characters *

@@unicharsize

nchar(n) national character,

national char

pagesize n * @@ncharsize

nvarchar(n) nchar varying,

national char varying,

national character

varying

pagesize @@ncharsize * number of

characters

text 231 -1 (2,147,483,647) bytes or fewer 0 when uninitialized;

multiple of 2K after

initialization

Binary

binary(n) pagesize n

varbinary(n) pagesize actual entry length

image 231 -1 (2,147,483,647) bytes or fewer 0 when uninitialized;

multiple of 2K after

initialization

Bit

bit 0 or 1 1 (one byte holds up to 8 bit

columns))

1> select name, hierarchy from systypes order by hierarchy

2> go

name hierarchy

—————————— ———

floatn 1

float 2

datetimn 3

datetime 4

real 5

numericn 6

numeric 7

decimaln 8

decimal 9

moneyn 10

money 11

smallmoney 12

smalldatetime 13

intn 14

int 15

smallint 16

tinyint 17

bit 18

univarchar 19

unichar 20

sysname 22

varchar 22

nvarchar 22

update_id 22

char 23

nchar 23

timestamp 24

varbinary 24

binary 25

text 26

image 27

date 28

time 29

daten 30

timen 31

extended type 99

(37 rows affected)

Creating user-defined datatypes

Here is the syntax for sp_addtype:

sp_addtype datatypename,

phystype [ (length) | (precision [, scale] ) ]

[, "identity" |nulltype]

Here is how tid was defined:

sp_addtype tid, “char(6)”, “not null”

Dropping a user-defined datatype

To drop a user-defined datatype, execute sp_droptype:

sp_droptype typename

Note You cannot drop a datatype that is already in use in any table.

Getting information about datatypes

sp_help money

What are databases and tables?

Adaptive Server database objects include:

• Tables

• Rules

• Defaults

• Stored procedures

• Triggers

• Views

• Referential integrity constraints

• Check integrity constraints

Single database can have 2 billion tables as per ASE 11.5

Indian Quotation Amount International Quotation

1 Lakh 100,000.00 100 Thousands

10 Lakhs 1,000,000.00 1 Million

1 Crore 10,000,000.00 10 Million

10 Crores 100,000,000.00 100 Million

100 Crores 1,000,000,000.00 1 Billion

You can create temporary tables either by preceding the table name in a

create table statement with a pound sign (#) or by specifying the name

prefix “tempdb..”

Temporary tables are created in the tempdb database.

There are two kinds of temporary tables:

Tables that can be shared among Adaptive Server sessions

create table tempdb..authors

(au_id char(11))

drop table tempdb..authors

Tables that are accessible only by the current Adaptive Server session

or procedure

create table #authors

(au_id char (11))

exists until the current session or procedure ends, or owner drops it.

rules on temporary tables

• You cannot create views on these tables.

• You cannot associate triggers with these tables.

• You cannot tell which session or procedure has created these tables.

*NOTE: These restrictions do not apply to shareable, temporary tables created in

tempdb.

IDENTITY

You define an IDENTITY column by specifying the keyword identity,

Example

create table sales_daily

(sale_id numeric(5,0) identity,

stor_id char(4) not null)

Column ’sale_id’ will be maintained by sybase server. Similar to MySQL auto_increment feture. A row can be uniquely identified by this key.

*NOTE:- Avoid using IDENTITY in production since some time server enters a value which might not be in order.

By default, Adaptive Server begins numbering rows with the value

1, and continues numbering rows consecutively as they are added. Some

activities, such as manual insertions, deletions, or transaction rollbacks,

and server shutdowns or failures, can create gaps in IDENTITY column

values. Adaptive Server provides several methods of controlling identity

gaps described in “Managing identity gaps in tables”

The IDENTITY column contains a unique ID number, generated by

Adaptive Server, for each row in a table.

Uniq VS Primary key

Both

constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value.

definition of unique constraints in the SQL standards specifies that the column definition shall not allow null values.

sp_primarykey, sp_foreignkey, and sp_commonkey define the logical relationship of keys (in the syskeys table) for table columns, which you enforce by creating indexes and triggers

There can be only one clustered index on a table,

The maximum number of references allowed for a table is 192.

Using sp_helpconstraint to find a table’s constraint information

A schema is a collection of objects owned by a particular user, and the permissions associated with those objects.

example:

create schema authorization dbo

create table list1

(col_a char(10) primary key,

col_b char(10) null

references list2(col_A))

create table list2

(col_A char(10) primary key,

col_B char(10) null

references list1(col_a))

You can declare a check constraint to limit the values users insert into a column in a table.

example:

create table my_new_publishers

(pub_id char(4)

check (pub_id in (“1389″, “0736″, “0877″,

“1622″, “1756″)

or pub_id like “99[0-9][0-9]“),

pub_name varchar(40),

city varchar(20),

state char(2))

Create table from existing table using “select into”

example:

1> select pname, sname into friends_name from froends_etc

2> go

(0 rows affected)

Execution Time (ms.): 1000 Clock Time (ms.): 1000

View Limitation

There are a few restrictions on the select statements in a view definition:

• You cannot include order by or compute clauses.

• You cannot include the into keyword.

• You cannot reference a temporary table.

For example, to rename titleview to bookview, enter:

sp_rename titleview, bookview

Follow these conventions when renaming views:

To display the text of the create view statement, execute sp_helptext

example:

sp_helptext hiprice

———-

1

(1 row affected)

text

——————————————–

create view hiprice

as select *

from titles

where price > $15 and advance > $5000

(1 row affected, return status = 0)

sp_depends lists all the objects that the view or table references in the

current database, and all the objects that reference that view or table.

example:

sp_depends titles

Things inside the current database that reference

the object.

object type

————- —————————

dbo.history_proc stored procedure

dbo.title_proc stored procedure

dbo.titleid_proc stored procedure

dbo.deltitle trigger

dbo.totalsales_trig trigger

dbo.accounts view

dbo.bookview view

dbo.categories view

dbo.hiprice view

dbo.multitable_view view

dbo.titleview view

(return status = 0)

sp_tables lists all views in a database when used in the following format:

sp_tables @table_type = “’VIEW’”

 

Call Me

Call sirfan98cs from your phone!

Ruby Rocks :)

Today I am going to share my very first experience with RubyOnRails, and to describe my feelings its fabulous.

Lets begin

What you need to know already?

  • You should be familiar with any MVC pattern
  • Should have knowledge of Ruby
  • Should know DB & Web server fundamentals

I guess thats more then enough to begin rails journey.

To start with you need following software installed on your box, :) I ma LILO fan so all below configuration and are Linux based. But based on this understanding one can quickly start writing on any other OS too.

Software Needed

  • ruby 1.8.4
  • rails 1.2.3

Once installation and configuration done

rails test_app

cd test_app

vi config/database.ym

add your db credential

script/generate migration add_formelements

> create db/migrate
> create db/migrate/001_add_formelements.rb

vim db/migrate/001_add_formelements.rb

Add code
class AddUsers < ActiveRecord::Migration
def self.up
create_table “formelements” do |t|
t.column “user”, :string
t.column “email”, :string
t.column “password”, :string
t.column “passordreminder”, :text
end
end

def self.down
drop_table “
formelements
end
end

rake db:migrate

== AddUsers: migrating ========================================================
– create_table(“formelements”)
-> 0.0234s
== AddUsers: migrated (0.0236s) ===============================================

script/generate scaffold Formelement

exists app/controllers/
exists app/helpers/
exists app/views/formelements
create app/views/layouts/
create test/functional/
dependency model
create app/models/
create test/unit/
create test/fixtures/
create app/models/formelement.rb
create test/unit/formelement_test.rb
create test/fixtures/formelement.yml
identical app/views/formelements/_form.rhtml
create app/views/formelements/list.rhtml
create app/views/formelements/show.rhtml
create app/views/formelements/new.rhtml
create app/views/formelements/edit.rhtml
create app/controllers/formelements_controller.rb
create test/functional/formelements_controller_test.rb
create app/helpers/formelements_helper.rb
create app/views/layouts/formelements.rhtml
create public/stylesheets/scaffold.css

script/server

http://yourserver3000/skills

That’s all enjoy!

Hope you have grate time doing above exercise.

Check this out http://tryruby.hobix.com/

JAVA Design Pattern

JAVA Design Pattern

The design patten is a vast subject, I will be giving you an head start here

Gang of Four – widely used design patterns

  1. Behavioral patterns
    1. Chain of responsibility
    2. Command
    3. Interpreter
    4. Iterator
    5. Mediator
    6. Memento
    7. Observer
    8. State
    9. Strategy
    10. Template method
    11. Visitor
  2. Creational patterns
    1. Abstract factory
    2. Builder
    3. Factory method
    4. Prototype
    5. Singleton
  3. Structural patterns
    1. Adapter
    2. Bridge
    3. Composite
    4. Decorator
    5. Facade
    6. Flyweight
    7. Proxy

Definition:- Common Problem and Common Solution for them, If you have data with problem and to tackle that you need to find problem pattern and propose solution pattern for that.

Note: No body owns the design pattern

Standard pattern are 23 those are listed above.

J2EE design pattern standardize by sun

EJB Patterns

  1. Intercepting Filter (apache mod rewrite)
  2. Composite View
  3. Front Controller
  4. Service to worker
  5. Business delegate
  6. Service Locater
  7. Transfer object assembler
  8. Facade

site: theserverside.com
site:www.tml.tkk.fi/~pnr/GoF-models/html

EJB cont

Entity Bean

An entity bean is representation of single row in a databases. Entity always be an heavy component. But the connectivity to database will be a transparent to developer. When you create a new entity bean automatically in database new row will be added.

ORM – Object Relational Mapping, row in a table will be one object in memory.

Stateless Session Bean can have pooled creation.

Statefull Session Bean can have cached creation.

But Entity bean can have either cache or pooled creation.

Whenever you need to work with processed or in other words process matters over data you should use session bean. But whenever you want to work with DATA or in other words DATA matters over process you should use entity beans.

Entity beans are heavy object because its a in memory object for database table. Where each instance of entity bean object refer to one record set in table or row in table.

Entity beans are of two types
Bean Managed Persistence
Container Managed Persistence

CMP Bean
Local Client
Remote Client

Favourite topic WSAD entity bean example :) without writing a single line of code get the entity bean in place here we go

  1. Create new project EntityBean
  2. Create new Enterprise bean
  3. Select Type Entity bean
  4. Select CMP bean 2.0
  5. Bean detail select Remote & Local both the interfaces
  6. Select the project
  7. Run the Entity bean on server
  8. Configure new Test Server or run on existing server
  9. Create table and deploy entity bean
  10. Finish
  11. It will show success message, i.e. it will test the create and alter statement on newly created table.
  12. Select server client local
  13. Finish
  14. It will open Test Client
  15. Click on EntityExampleLocal
  16. Click on CMPLocal.findByPrimaryKey
  17. Enter value into and click invoke.

This how you will be testing tyour entity beans. Just now what we have seen is the Top to Bottom appoch, we have another approach which is Bottom to Top where in we will be having table already existing in and then will be creating the entity bean accordingly.  

Facts……….

-EJB can be accessed using servlet
-Entity Bean should not be remote
-Session Bean should not be local
-In entity bean you should not use primitive data-type.