Thursday, May 31, 2012

Oracle Drop All Tables, Views, Sequences, Triggers






1. Drop All Views




BEGIN
FOR i IN (SELECT view_name FROM user_views)
LOOP
EXECUTE IMMEDIATE('DROP VIEW ' || user || '.' || i.view_name);
END LOOP;
END;

2. Drop All Triggers

BEGIN
FOR i IN (SELECT trigger_name FROM user_triggers)
LOOP
EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name);
END LOOP;
END;

3. Drop All Sequences

BEGIN
FOR i IN (SELECT sequence_name FROM user_sequences)
LOOP
EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name);
END LOOP;
END;

4. Drop All Tables

BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i._table_name);
END LOOP;
END;

Monday, May 28, 2012

SchemaSpy

SchemaSpy is a very useful tool for database Administrators and Developers. The basic purpose of SchemaSpy is to auto-generate schema for any database. But it's evolved in a great tool that can give us so much valuable information about any database. The case in question is that of a complex database consisting of more than 30 tables with some tables having fields in excess of 50 and complex links.

I won't go into details of everything here but rather I'll outline major points. After successfully running SchemaSpy on a database you are presented with an output of number of HTML files in the designated folder on your PC. The index.html file is the starting point for this output. After opening it you can see following tabs.
 

And that's a lot of information that you get about the database.

The first tab - tables - lists all the tables, next one displays a relationship diagram, next lists Utility tables and details for them, the next one does constraints, after that it even shows anomalies that can affect the integrity of your data, then's one that lists all columns in your database and the last tab takes you to donate link.

Overall you can see that SchemaSpy is a powerful tool for DBA/Developers.

To use it, you need couple of things:
1. The JRE - since Schema Spy is written in Java.
2. GraphViz - Schema Spy uses graphviz to create the relationship diagram seen in second tab.

Tuesday, May 22, 2012

Tuesday, May 15, 2012

Online Syntax Highlighting

This is an amazing website that can highlight your code in various color themes.
It's really helps with readability of the code in documents.
http://tohtml.com/

Tuesday, May 8, 2012

Of bugs and bees (especially annoying bees)

Oftentimes a simple operation fails and screws a much complex operation;

Well mostly though it's the complex operation that often fails and makes life miserable.

"echo PASSPHRASE | gpg --yes --quiet --passphrase-fd 0 --simple-sk-checksum -c --no-use-agent SOURCE_FILE"

This was the monkey wrench that I managed to throw in the wheel of the tractor!

It all happened like this: I was tasked to design an encryption utility which will use gpg in the background and encrypt files with keys from a pool. So I wrote a small C program that would access the pool securely and encrypt the file. Later this file would be transferred across the network and at the receiving end there was a similar utility which would decrypt the file.

Now the problem was I didn't have enough time to go through gpg's api's and integrate it in the utility. So instead I used system function to execute the gpg command as shown above. For testing I would call this utility from terminal and it would work fine.

The thing was when I tested this utility from the main program, I found it working. So here are the two cases I tested against:
1. Call the utility from terminal and check for all OK.
2. Start the main program from terminal and test the functionality using this encryption utility.

Both tests came positive. So I okay-ed the utility and it got shipped with the main program. Now the thing was it wasn't used right away since the encryption routines remained unused for 2-3 months. Just last week though, when the field teams started using the encryption routines it started failing with consistency. Of course the buck got back to me. I was genuinely perplexed since it was all working fine. So I fire up the main program and test it and behold! The encryption part is working fine. I call up the field people and ask them what is the problem and they explain - blah blah blah.

So after a lot of head scratching and hair pulling I understood the scenario. I would always use terminal to start the main program since I would be looking out for debugging output. But we had provided a shortcut on the desktop as well as start menu which was the recommended way for starting the program.

At this insight, I started up the main program using the desktop shortcut; tested the encryption routine and it was failing. I tested the encryption routine separately on terminal and it's working. I modified the utility to throw up as much debug code; variable values etc and retested. Still nothing! In the main program it fails; and in terminal it works!

Well, after much more testing - I keep records for testing sessions; and it was 20th attempt - I figured out the problem with the command. Later it was simple, I modified the command as shown below. And after 5 more rounds of testing I confirmed the bug to be dead!

"gpg --yes --quiet --passphrase PASSPHRASE --simple-sk-checksum -c --no-use-agent SOURCE_FILE"

Lesson learned: Don't pick up code from shell script and put it in c program; always consider pipes and environment variables. And even after that when testing for functionality, test it like a user and not a developer. Developers test for bugs; users test for proper working of solution!

Code Conversion from C#.NET to VB.NET

Developer Fusion offers free online code conversion service and it is of better quality than almost all others. The URL for the same is as follows:
http://www.developerfusion.com/tools/convert/vb-to-csharp

You can follow following steps to convert one whole project from C#.NET to VB.NET:

1. Create empty VB.NET project.

2. Create the necessary forms – Don't add any controls; we'll copy them from respective forms in C#.NET project in following step.

4. Go to code for the forms and add Import for all assemblies required (using * statements in C#.NET project – which you can paste in the URL above to get imports * statements)

3. Copy all controls in a form and paste the in respective forms.

4. Add the extra class variables (Convert respective code to VB.NET using above URL).

5. Add constructors and methods you have added to the forms(Convert respective code to VB.NET using above URL).

6. Finally add event handlers (Convert respective code to VB.NET using above URL).

7. Compile and test the VB.NET code.

Midnight in Paris and XBMC

About the movie? I have only one word – WOW! The story is great; the execution is brilliant and the actors are great. Also I waited 2 weeks since I got this movie in my hands so that I wouldn't rush through it and instead saved it for the day when I was feeling like watching such a movie.

I have read the reviews and they were positive but this movie is the kind of film that I love – the story is leisurely told and is acted out brilliantly, you find yourself enjoying the acting, the events and the overall tone of the film. You find yourself getting involved in the film; thinking about it and you find some great thing in it. That's the recipe for a really laid back pleasurable film to watch. And Midnight in Paris is just the film. All I missed was some grapes (I don't know why!) and someone to feed them to me (Ok, just kidding!).

Now the software, and yeah this article was originally about XBMC 11. It was lauded all over the internet as a worthy upgrade to XBMC 10 in terms of both functionality and performance. To my pleasure I found it to be true.

Of course I'm not regular user of XBMC, but I had the opportunity to use XBMC 10 a few months ago and I had to drop it because of pathetic performance. But with this release I find myself turning on XBMC all the while when I am touching my machine for only entertainment.

The interface is smooth to operate – the mouse pointer used to move jerkily and used to lag a fair bit for version 10; but in this version it's almost as good as windows mouse pointer. Also the lags in various places while navigating the menus are gone and navigation in 11 is smooth. Also smooth is the theme used. So this release scores high on usability.

Now I use vlc for everyday videos and stuff but with xbmc's inbuilt video browser (files menu under videos; you add sources here) and the ability to browse and queue up others while watching one is making me use XBMC more and more.

I have powerDVD for Movies but since XBMC provides almost all the options powerDVD has and the fact is I'm still light years away from Blue Ray Disks makes powerDVD's advantages over XBMC (inbuilt blue ray playback; sound enhancements etc) moot for me. As for powerDVD's sound effects; I prefer plain sound rather than the emulated (or digitally created) surround sound.

Well kudos to XBMC Team for giving us something so beautiful and so functional!

Friday, May 4, 2012

MS Office woes

Change the separator in all .csv text files

##########################################################################
In Microsoft Windows, click the Start button, and then click Control Panel.
Open the Regional and Language Options dialog box.
Do one of the following:
In Windows Vista, click the Formats tab, and then click Customize this format.
In Windows XP, click the Regional Options tab, and then click Customize.
Type a new separator in the List separator box.
Click OK twice.
 NOTE   After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.


##########################################################################

','(comma ) is used as Separator for CSV file; regardless of 'List Separator' Setting in Control Panel. 

##########################################################################
SYMPTOMS
When you save your file as a Comma Separated Value (CSV) file from Excel, the s...
When you save your file as a Comma Separated Value (CSV) file from Excel, the separator (delimiter) that is used is not the character that you chose to be the List separator for your system. When this occurs, you do not receive a warning or error message.

CAUSE
You chose the same character for both your Decimal symbol and your List separat...
You chose the same character for both your Decimal symbol and your List separator in the Regional Settings of your system Control Panel . In this condition, Excel must substitute another character for the list separator to avoid a possible loss of information when the CSV file is saved.

WORKAROUND
Change either your List separator or your Decimal symbol in the Regional Settin...
Change either your List separator or your Decimal symbol in the Regional Settings , so that they are not the same character. To do this, follow these steps:
Click Start , point to Settings , and then click Control Panel .
Double-click Regional Settings or Regional Options . Click the Number (or Numbers ) tab.
In the Decimal symbols list, choose a character that is not the same as your list separator. Alternately, in the List separator list, choose a character that is not the same as your decimal symbol.
Click OK .
If you are prompted to restart the computer, do so.

##########################################################################

Wednesday, May 2, 2012

Perl Script for brute force cracking gpg encrypted file





Two Points never to forget while using encryption:
1. Keep the passwords handy/ safe.
2. Don't use encryption if you are not familiar with the procedures /options available when it comes to recovering the password/ decrypting the file.

LEARNT THE HARD WAY! :D

####################################################################
#!/usr/bin/perl -w

# This script was largely based on this Python script: http://www.rbgrn.net/content/25-how-to-write-brute-force-password-cracker

# Author: Tom

# Blog: kaabel.net/blog/

# IRC: irc.malvager.com #perlbar



use strict;

use GnuPG;



my $gpg = new GnuPG(); # Create GnuPG handle

my $found;

my @range = ('0' .. '9'); # Edit your charset here (Numbers, in this case) ('A'..'Z') for caps

my $maxlength = 5; # Max length of the password

my $minlength = 1;

my $time = time();



sub recurse($$) {

my ($width, $position, $basestring) = @_; # Get the arguments supplied in the function's arguments.

foreach my $char (@range) {

if ($position < $width - 1) { # If the position needs to be shifted

&recurse ($width, $position+1, ("$basestring" . "$char")); # Guess why it's called recurse :p

next;

}

# You must enter the file name of your encrypted file after ciphertext => and optionally a name for the output as well

eval { $gpg->decrypt(ciphertext => 'example.txt.gpg', output => 'example_d.txt', passphrase => $basestring . $char, symmetric => 'true') }; # Eval is needed here, otherwise the program will end after an error.

$found = $basestring . $char if !$@; # If there were no errors, make $found equal the password

if (($time + 60) < time()) { # If 60 seconds have passed (1 minute),

print "Trying: " . $basestring . $char . "\n"; # print the current 'try'

$time = time(); # Reset the time

}



if ($found) { # If the password was found,

print "Found: $found\n";

exit;

}

}

}



foreach my $basewidth ($minlength .. $maxlength) { # Loop through the possible lengths of the password

print "Checking paswords with length $basewidth\n";

&recurse($basewidth, 0, ""); # Call the cracking sub

}