Get files from a directory in the file system using Java

If provided, any files matching any one of the strings found in the array of string ignore[] will be ignored; this can be useful if you want pick up all files except, for example, those marked with the word “temp” or obsolete files marked with “.old” extension.

/**
 * Get the all files in a directory, ignoring files with file names containing particular strings.
 *
 * @param folderPath : Full path of directory in question, eg. "C:\docs\myFiles\"
 * @param ignore[] : An array of strings containing words with which to ignore files, eg. { "temp","ignore",".old" }
 * @return File[]
 * @author C. Peter Chen of http://dev-notes.com
 * @date 20080610
 */
private static File[] getFiles(String folderPath, String ignore[]) {
	File f = new File(folderPath);
	File[] fList = f.listFiles();
	List retFl = new ArrayList();
	if (fList != null && fList.length > 0) {
		for (File file : fList) {
			boolean pass = true;
			for (String ig : ignore) {
				if (file.getName().indexOf(ig) >= 0) {
					pass = false;
				}
			}
			if (pass) { // Only get the file if none of the ignore strings are present
				retFl.add(file);
			}
		}
	}
	return retFl.toArray(new File[0]);
}

Turn your Mythbuntu box into a file server

As it turns out, because Mythbuntu already has Samba built in, it is easy to make it a file server in your home network by using the same tool. First, identify a place where you want to open up a new folder for file sharing. In this example, we’ll do “/fileserver/”. Then, let us issue the following command to edit the Samba config file.

su pico /etc/samba/smb.conf

Note the “su” command; it will require you to put in your password before editing. Once the text editor pico loads up, add the following section at the end of the configuration file.

[files]
comment = Files
path = /fileserver/
public = yes
writable = yes
create mask = 0660
directory mask = 0770
force user = mythtv
force group = mythtv

Once this is done, restart the samba daemon from your command line.

/etc/init.d/smb restart

You should now be able to access the new “files” samba share from another computer on your network.

Sort an array of File objects by last modified date in Java

/**
 * Sorts an array of Files by the last modified date property; if the second
 * parameter is "desc", then sorting is done descending order, otherwise
 * it will be ascending. 
 * @param fList : An array of Java "File" objects, not sorted
 * @return File[] : An array of Java "File" objects, sorted by last modified date
 * @author C. Peter Chen http://dev-notes.com
 * @date 20080527
 */
public static File[] sortFilesByLastModDate(File[] fList, String order) {
	Arrays.sort(fList, new Comparator() {
		public int compare(File file1, File file2) {
			if ("desc".equals("order")) {
				return (int)(file2.lastModified() - file1.lastModified());
			}
			else {
				return (int)(file1.lastModified() - file2.lastModified());
			}
		}
	});
	return fList;
}

Set a static IP address for Mythbuntu

The process to set a static IP address to a Mythbuntu box is very similar to Ubuntu, given that Mythbuntu is built upon Ubuntu. In Mythbuntu, choose “Utilities/Setup” from the main menu, then “Setup”, and finally “Mythbuntu”. After putting in the password, you will be presented with the Mythbuntu Control Centre. Under “Advance Management”, you will be able to “Launch Terminal”.

First, check out what your current settings are.

ifconfig -a

Take a note what your current broadcast, subnet mask, and gateway settings are. Also, have you decided on an IP address for your Mythbuntu box yet?

Next, issue this command to modify the Mythbuntu box’s network settings.

sudo pico /etc/network/interfaces

In the pico text editor tool, you probably only see a set of “lo”, or loopback, settings. Let’s add a new set for “eth0” so that the whole content of the file looks something like the following. Note your addresses may be different. In my case, I set the box’s static IP address to 192.168.1.3, for example, and yours may be different.

auto lo
iface lo inet loopback

auto eth0
iface eth0 inet static
	address 192.168.1.3
	netmask 255.255.255.0
	network 192.168.1.0
	broadcast 192.168.1.255
	gateway 192.168.1.1

Finally, restart the network card with the following command.

sudo /etc/init.d/networking restart

That’s it! You should now be able to access the Mythbuntu box with the same IP every time now! No more trying to guess what the IP address might change to every time you turn it on!

Encrypt a string using SHA encryption

The following class provides the venue to translate the sample string “mySecr3tP4ssw0rd” into the encrypted string “Nj3lzFVrt9dx2gENZeh2H5xY6PY=”, which would be tougher to crack by brute force than a plain password in case a hackers gets hold of your data store.

import java.security.MessageDigest;
import sun.misc.BASE64Encoder;

/**
 * String encryption related utilities.
 * @author C. Peter Chen of http://dev-notes.com
 * @date 20080512
 */

public class StringEncryptUtil {
	/**
	 * This main() class is used for demo only.
	 * @param args
	 */
	public static void main(String[] args) {
		System.out.println("SHA encrypted mySecr3tP4ssw0rd: " + encryptSha("mySecr3tP4ssw0rd"));
	}
	
	/**
	 * Performs a SHA encryption process on the incoming string parameter.
	 * @param inputStr
	 * @return SHA-encrypted string if successful, or null if there are problems.
	 */
	public static synchronized String encryptSha(String inputStr) {
		try {
			MessageDigest md = MessageDigest.getInstance("SHA");
			md.update(inputStr.getBytes("UTF-8"));
			byte digest[] = md.digest();
			return (new BASE64Encoder()).encode(digest);
		}
		catch (Exception e) {
			return null;
		}
	}
}

As you will notice, there is no decrypt method, that is because there probably is no need for one. For instance, we have “Nj3lzFVrt9dx2gENZeh2H5xY6PY=” in the data store for the user Scott; when Scott logs in, we should SHA-encrypt the password he had just typed in, and compare the encrypted string with the encrypted string found in the data store.

Custom is_number() and is_date() functions in Oracle

To do so, create this following two functions. The first function tests for numbers, and the second tests for dates. You should be sure to change the schema name to the correct value for your environment.

create or replace function schema1.is_number(in_var in varchar2)
return varchar2
is
  v_number number;
begin
  select to_number(in_var) into v_number from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

create or replace function schema1.is_date(in_var in varchar2, in_format in varchar2)
return varchar2
is
  v_date date;
begin
  select to_date(in_var, in_format) into v_date from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

Below are two samples on how to use our newly created is_number() function.

select schema1.is_number('pete') from dual;
select schema1.is_number(123) from dual;

The first query will result “N” because “pete” is not a number. The second query will return “Y”.

Below are four samples on how to use our newly created is_date() function.

select schema1.is_date('pete','mm/dd/yyyy') from dual;
select schema1.is_date('2-MAY-2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/99/2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/02/2008','mm/dd/yyyy') from dual;

The first query will result “N” because “pete” is not a date. The second also returns “N” because the date entered does not conform to the format specified. The third also will return “N” because the date, although formatted correctly, is not a valid date. Finally, we will have “Y” for the last test.

Oracle SQL case expression

The example below retrieves a sample list of employees, their job roles, and how many hours they have billed to a particular customer.

select e.emp_name, e.job_role, sum(b.hours) from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)
--------- ------------- ------------
Sandy     attorney      10
Bob       programmer    4
Jill      programmer    8
John      accountant    32
Barbara   secretary     20
Danny     proj manager  8

The company has a simple billing rule. Attorney hours are billed at $200 per hour, programmers at $100 per hour, accountants at $100 per hour, and all others at $25 per hour. We can use the case statement to help us derive how much to bill the customer.

select e.emp_name, e.job_role, sum(b.hours),  
case when job_role='attorney' then sum(b.hours)*200
  when job_role='programmer' then sum(b.hours)*100
  when job_role='accountant' then sum(b.hours)*100
  else charge=sum(b.hours)*25
end as charge
from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)  charge
--------- ------------- ------------- -------
Sandy     attorney      10            200
Bob       programmer    4             400
Jill      programmer    8             800
John      accountant    32            3200
Barbara   secretary     20            500
Danny     proj manager  8             200

Of course, this is just a hands-on illustration of the case expression in Oracle SQL. In real life, it is arguable that this type of business logic should probably be written elsewhere, perhaps with the base rates stored in a table, so that it can be easily modified if need be.

Pivot table with Oracle SQL

Often, we store data in a normalized way in our database, perhaps something that resembles key-value pairs. To illustrate, let us see the following table, which describes properties of computers.

select comp_id, comp_property, comp_property_value from schema1.comp_properties;


COMP_ID COMP_PROPERTY COMP_PROPERTY_VALUE
------- ------------- -------------------
1       hostname      comp1
1       ipaddress     192.168.0.1
1       os            Windows XP SP2
1       hdd1          60gb
1       hdd2          500gb
2       hostname      comp2
2       ipaddress     192.168.0.2
2       os            MacOS 10.5
2       hdd1          80gb
3       hostname      comp3
3       os            Ubuntu Linux 7.10

In this particular case, we can see that the three computers have different properties. While in this example of three computers we can see the information easily, we can also imagine that it will become cumbersome if we are managing 100 computers instead of only three. Thus, a pivot table type of presentation would be much more useful.

To rewrite the SQL statement to present the same data in a pivot table manner, we can do the following.

create or replace schema1.v_comp_properties
select comp_id, hostname, ipaddress, os, hdd1, hdd2
from (
	select comp_id,
	max(case when comp_property='hostname' then comp_property_value else null end) as hostname,
	max(case when comp_property='ipaddress' then comp_property_value else null end) as ipaddress,
	max(case when comp_property='os' then comp_property_value else null end) as os,
	max(case when comp_property='hdd1' then comp_property_value else null end) as hdd1,
	max(case when comp_property='hdd2' then comp_property_value else null end) as hdd2
	from schema1.comp_properties
	group by comp_id
);

The result should look like the following.

select comp_id, hostname, ipaddress, os, hdd1, hdd2 from schema1.v_comp_properties;

COMP_ID HOSTNAME IPADDRESS   OS                HDD1 HDD2
------- -------- ----------- ----------------- ---- -----
1       comp1    192.168.0.1 Windows XP SP2    60gb 500gb
2       comp2    192.168.0.2 MacOS 10.5        80gb
3       comp3    192.168.0.3 Ubuntu Linux 7.10

With this pivot table, we will be able to see all properties of each computer on each row easily, while still maintain a normalized database design.

MySQL backup and recovery

I am anal with backups. Personally, I probably over-do it with the amount of redundancy, but I think the over-kill is justified. I spend hours and hours each week building up World War II Database, for example, and I do not want to chance a hard drive failure to destroy hours of work. Backup is important. Period.

With that said, it is actually very easy to backup a MySQL database, which is what powers World War II Database in the backend. The following command line illustrates how to create a MySQL database dump file which will contain your latest data.

mysqldump --user=username --password=password --host=localhost db1 --opt > ~/backups/db1dump.sql
mysqldump --user=username --password=password --host=localhost --databases  [db1 db4 db9] --opt > ~/backups/dbsdump.sql
mysqldump --user=username --password=password --host=localhost --all-databases --opt > ~/backups/dballdump.sql

The first example above dumps only one database, “db1”, to the file “db1dump.sql” in the specified directory. The second example illustrates how we can backup data from multiple databases. If we just wish to dump out all data across all database, the third example shows how we can do so.

Once we have chosen the method we want and have the command line we wish to use, we should schedule it to run regularly. For windows, the built-in “Scheduled Task” utility works well for this. For Unix/Linux, “cron” could be used to generate the command line. Do not forget it is probably wise to transfer a copy of your database dump file to a different machine immediately to prevent a hard drive failure wiping out both your database and backups at the same time.

In the dreadful event that our MySQL database goes bad and we are forced to establish a new database machine, we just have to retrieve our most recent database dump file and run the following command on the new install.

mysql --user=username --password=password --host=localhost db1 < db1dump.sql

Writing SQL output to CSV with VBScript

Enter your specific information in the “Configuration” section near the top of the script. For the “dbType” variable, the only accepted values are “oracle”, “sqlserver”, or “mysql”. Once this is done, just run the script and you should have your quote-delimited comma-separated CSV file!

The email-related variables are optional. To enable the emailing functionality (send the generated CSV file to the address as an attachment), enter the recipient email address in the box. If you do not wish to email, just leave that variable as empty string (“”), and the other email related variable such as smtp and smtpPort will be ignored.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'	1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject"            ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!"      ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com"          ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25                     ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = "Test Email from vbs"
	objMessage.From = email
	objMessage.To = email
	objMessage.TextBody = "Please see attached file."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")