• <form name='' enctype="multipart/form-data" action='' method=''>
• $HTTP_POST_FILES['filename']['name']-The original filename on the client machine;
• $HTTP_POST_FILES['filename']['type']-The mime type of the file:image/gif;
• $HTTP_POST_FILES['filename']['size']-The size in bytes of the uploaded file;
• $HTTP_POST_FILES['filename']['tmp_name']-The temporary filename stored on the server.

• <form action="file-upload.php" method="post" enctype="multipart/form-data">
  <input name="userfile[]" type="file">
  <input name="userfile[]" type="file">
  <input type="submit" value="Send files">
  </form>
• $HTTP_POST_FILES['filename']['name'][0];
  $HTTP_POST_FILES['filename']['name'][1];
  $HTTP_POST_FILES['filename']['type'][0];
  $HTTP_POST_FILES['filename']['type'][1];
  $HTTP_POST_FILES['filename']['size'][0];
  $HTTP_POST_FILES['filename']['size'][1];
  $HTTP_POST_FILES['filename']['tmp_name'][0];
  $HTTP_POST_FILES['filename']['tmp_name'][1];

• Method #1:
  try apt-cache search php5
  If you find the package: apt-get install php5-imap
• Method #2:
  Use PHPIZE to add extension for PHP
  1. install php5-dev, apt-get install php5-dev
  2. download the source code for php: apt-get source php5
  2. find the module source folder in ext, for example in imap
  3. phpize
  4. ./configure
  5. make
  6. make install
  7. check your php library folder (/usr/lib/php5/), and edit php.ini to enable imap.so
  

1. Use integer instead of int(11); (no '(11)')
2. Use smallint instead of tinyint(4); (no '(4)')
3. define foreign key inline: department_id smallint references departments(id);
4. Use CURRENT_DATE, CURRENT_TIME(with timezone) or CURRENT_TIMESTAMP instead of curdate(),now()
5. select * from tablename limit 5 offset 10: start from row 10, get 5 rows;

• Add column: ALTER TABLE table_name ADD column_name column_type
• Add default: ALTER TABLE table_name ALTER column_name SET DEFAULT value
• Remove default: ALTER TABLE table_name ALTER column_name DROP DEFAULT
• Rename column: ALTER TABLE table_name RENAME column_name TO new_name
• Add FK constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES foreign_table (foreign_column_name)
• Add NOT NULL constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name IS NOT NULL)
• Add UNIQUE constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name, column_name_2...)
• Drop a CHECK constraint: ALTER TABLE table_name DROP CONSTRAINT constraint_name RESTRICT

In Fedora, postgresql contrib is installed default under /usr/share/pgsql/, the tsearch2.so is installed already under /usr/lib/pgsql/ when you install posgresql 8.
install and configuration:
psql database < tsearch2.sql;
Setup the configuration for en_US locale; follow the instructions in Tsearch2 Introductions;
Get ispell source, and make a new english dictionary, move the files to the right location;
Alter the table to add a column called: idxFTI for the full text searching:
  example: alter table facstaff_out add column idxFTI tsvector;
Update the table to fill out the idxFTI column:
  update facstaff_out set idxFTI = to_tsvector('default_english',interests);
vacuum analyze;
create index: create index idxFTI_idx on facstaff_out using gist(idxFTI);
vacuumn analyse;
create trigger: create trigger tsvectorupdate before update or insert on facstaff_out for each row execute procedure tsearch2(idxFTI,interests);
grant all on pg_ts_cfg to username;
grant all on pg_ts_cfgmap to username;
grant all on pg_ts_dict to username;
grant all on pg_ts_parser to username;

Use full text searching in PHP for PostgreSQL:
Project web areas-people searching is use PostgreSQL for full text searching.
Setup Locale: select set_curcfg('default_english'); or use 'default' as the first argument in to_tsvector
get the vectors from the string, call function get_vectors()in db.inc;
use query: idxFTI @@ to_tsquery('$vectors') to get results or to_tsquery('default','$vectors');

Increase the performance:
psql#explain analyse select * from urls where name='http://www.eku.edu/web9/';
psql#cluster urls_name_idx on urls;
psql#explain analyse select * from urls where name='http://www.eku.edu/web9/';
You can see the big difference for running time.

      select
        case
          when invitation_sent='t' then 'Yes'
          else 'No'
        end as invitation_sent from visit limit 10;
      

• Primary Key: sid number(5) constraint stutent_sid_pk
• Foreign Key: locid number(5) constraint faculty_locid_fk references location(locid)
• Check: ccredit number(2) constraint course_ccredit_cc check ((ccredit>0) and (ccredit<12))
• Not Null: termid number(3) constraint course_section_termid_fk references term(termid) constraint course_termid_nn not null
• Unique: first varchar2(20),last varchar2(20), constraint emp_name_uniq(first,last)

• select * from tab;
• desc tablename
• SQL>desc user_tables;
• SQL>desc user_objects;
• SQL>desc all_views;
• SQL>desc all_tables;
• SQL>every table have a column called ROWNUM (select rownum from emp;)
• SQL>show linesize;
• SQL>set linesize 120;
• SQL>set pagesize 1000;/li>
• SQL>set pause on(off);
• SQL>column column_name format a30; (a stands for alphanumeric)
• SQL>column column_name format $9,999.99;
• SQL>select * from user_sequences;
• SQL>select sysdate from dual;
• SQL>select abs(-1) from dual;
• SQL>select power(2,2) from dual;
• SQL>select round(2.345,2) from dual;
• SQL>select trunc(123.456,2) from dual;

• Add or delete a constraint:
  SQL>alter table student add constraint student_sclass_cc check ((ssclass='JR') or (sclass='SO'))
• Add a new column(delete a column is not allowed)
  SQL>alter table faculty add (startdate date);
• Change a column definition if the column is empty now
  SQL>alter table student modify (spin number(4) constraint student_spin_nn not null);

• to_date('08/10/2002','MM/DD/YYYY')
• to_date('10-Aug-2002','DD-MON-YYYY')
• to_date('10:00 AM','HH:MI AM')
• MM:Number of the month
  MON:Three-letter abbreviation of month in all uppercase letter
  DD:Number of the day of the month
  D:Number of the day of the week(Sunday=1)
  DY:Three-letter abbreviation of the day in all uppercase letters SUN
  YY:Last two digits of the year
  YYYY:Full four-digit year
  HH:Hour of the day using a 12-hour clock
  HH24:Hour of the day using a 24-hour clock
  MI:Minute of the hour
  SS:Second of the minute
  A.M.,P.M.:Display A.M. or P.M. depending on the time of day
• SQL>insert into table1 values(val1,val2,to_date('08/10/2002','MM/DD/YYYY'))
• SQL>select sfname from student where sdob>to_date('01-Jan-78','DD-MON-YY')

• Commit,rollover,savepoint order_save,rollback to order_save

• SQL>Create user username identified by password
• SQL>Drop user username
• SQL>Grant select,insert,update,delete on tablename to user;
• SQL>Revoke select,insert,update,delete on tablename from user;
• SQL>Create role role_name not identified
• SQL>Grant rolename to user1,user2;
• SQL>Grant select,insert,update(col1,col2) on table_name to rolename;
• SQL>Revoke insert,update on table_name from rolename

• Store the shell directly in /etc/daily, /etc/hourly ...
• use crontab command to add, edit or remove task;
  • format: minute(0-59) hour(0-23) dayofmonth(1-31) monthofyear(1-12) dayofweek(0-6) /dir/command
  • 0 * * * * * /home/me/backup.cron: execute the backup shell every hour;
  • 0,30 * * * * * /home/me/backup.cron: execute the backup shell every half an hour;
  • 0 0 * * * * /home/me/backup.cron: execute the backup shell every day at 00:00;
  • You can only have one crontab file storing all your cron tasks;

1. install most recent quicktime, realplayer.
2. install ffmpegx from http://homepage.mac.com/major4/ and download mpeg2enc from this web site
3. go to sourceforge.net, and search for mplayerosx;
   click Download MPlayer OS X;
   select Last Mencoder & Mplayer binary;
   select ffmpegXbinaries20060307.zip
   unzip the file and save to your computer
4. open ffmpegx, install binaries for mencoder and mplayer, select the binary files from step 3
5. open Library/Application Support/ffmpegx/, create a new folder named "reallib"
6. open RealPlayer-Show Package Contents-Contents-Framworks-HXClientKit.framework-HelixPlugins-Codecs
7. copy all *.bundle files to reallib folder created in step 5
8. convert real media file (.rm) to avi, using MPEG 4 (.avi) (mencoder)
9. convert avi file to mov quicktime format
10. convert mov file to flv file

        
        ' Build a MailMessage
        Dim mailMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage
        mailMessage.From = "web@windows.eku.edu"
        mailMessage.To = "qing.cui@eku.edu"
        mailMessage.Subject = "try .net"
        mailMessage.BodyFormat = System.Web.Mail.MailFormat.Text
        

        ' TODO: Set the mailMessage.Body property
        mailMessage.Body = "New Registration:" & txtName.Text
        Dim oAttch As MailAttachment = New MailAttachment("C:\myattachment.zip") 
        mailMessage.Attachments.Add(oAttch) 
        System.Web.Mail.SmtpMail.SmtpServer = "ekumail.eku.edu"
        System.Web.Mail.SmtpMail.Send(mailMessage)
        

        Format Description Example 
        d Short Date  
        D Long date  
        f long date & short time  
        F long date and long time  
        g short date and short time  
        G short date and long time  
        M or m month and day  
        Y or y year and month  
        t short time  
        T long time  
        s displays in ISO 8601 format using local time  
        u displays in ISO 8601 format using universal time  
        U date and time in unversal time  
        R or r displays in RFC 1123 format  
        

        Format      Description 
        d day of month (1 or 2 digits as required) 
        dd day of month (always 2 digits, with a leading 0 if needed) 
        ddd day of week (3 letter abbreviation) 
        dddd day of week (full name) 
        M month number (1 or 2 digits as required) 
        MM month number (always 2 digits, with a leading 0 if needed) 
        MMM month name (3 letter abbreviation) 
        MMMM month name (full name) 
        y year ( last 1 or 2 digits, no leading 0) 
        yy year (last 2 digits) 
        yyyy year (4 digits) 
        H hour in 24-hour format (1 or 2 digits as required) 
        HH hour in 24-hour format (always 2 digits, with a leading 0 if needed) 
        h hour in 12-hour format (1 or 2 digits as required) 
        hh hour in 12 hour format 
        m minutes (1 or 2 digits as required) 
        mm minutes (always 2 digits, with a leading 0 if needed) 
        s seconds (1 or 2 digits as required) 
        ss seconds 
        t first character in the am/pm designator 
        tt am/pm designator 
        z time zone offset, hour only (1 or 2 digits as required) 
        zz time zone offset, hour only (always 2 digits, with a leading 0 if needed) 
        zzz time zone offset, hour and minute 
        / default date separator 
        : default time separator 
        \ escape characters 
        

File Uploading

Setting up HTML form for file uploading.

The requirements for HTML form to be able to upload files are very simple: you have to use multipart/form-data encryption and you have to use method of post.

This is all that needs to be done to HTML form for a client to be able to submit a file to your ASP.NET application.

Receiving uploaded file.

        
        protected HtmlInputFile filMyFile;
        

HtmlInputFile class have a few different properties, but only one that really concerns us here is PostedFile. This property will tell us all we need to know about the file that had been uploaded to our server. PostedFile property is null if no file was submitted by a client. So we can simply check whether file was sent by doing something like this:

        if( filMyFile.PostedFile != null )
        {
	        // File was sent
        }
        else
        {
	        // No file
        }
        

PostedFile property will contain a valid System.Web.HttpPostedFile object if file indeed was uploaded. HttpPostedFile provides us with 4 properties:
ContentLength ¨C size of uploaded file in bytes
ContentType ¨C MIME type of uploaded file, i.e. ¡°image/gif¡±
FileName ¨C full path to uploaded file on client¡¯s system, i.e. c:\Some folder\MyPicture.gif
InputStream ¨C stream object that gives us access to uploaded data.

Let¡¯s find out the size of the file: // Get a reference to PostedFile object

        HttpPostedFile myFile = filMyFile.PostedFile;

        // Get size of uploaded file
        int nFileLen = myFile.ContentLength;
        

Now that we know the size we can move on to retrieving the data. First we need to allocate a byte array to store the data: // Allocate a buffer for reading of the file

        byte[] myData = new byte[nFileLen];
        

Next we can read uploaded file in our buffer using InputStream object: // Read uploaded file from the Stream

        myFile.InputStream.Read(myData, 0, nFileLen);
        

At this point we successfully retrieved uploaded file into a byte array called myData. What to do with it next depends largely on the requirements of your application I will show both saving the file to hard drive and to a database in the next sections.

Saving uploaded file to disk

        // Writes file to current folder
        private void WriteToFile(string strPath, ref byte[] Buffer)
        {
	        // Create a file
	        FileStream newFile = new FileStream(strPath, FileMode.Create);

 	        // Write data to the file
	        newFile.Write(Buffer, 0, Buffer.Length);

	        // Close file
	        newFile.Close();
        }
        

I pass a full path of where I want file to be stored and reference to a buffer with file data to this function. It uses System.IO.FileStream object to write buffer to a disk.

 
string strFilename = Path.GetFileName(myFile.FileName);

Security is another matter. In my demo project I store files in the same folder where project is executed. In order for that to work ASPNET account (account that is used to execute ASP.NET processes) has to have Write permissions on that folder. By default it does not, so you need to right-click the folder, go to security tab and add ASPNET account to the list, then grant write permissions to that account (by checking Write checkbox) and click OK.

Saving uploaded file to database

Following function is used in my demo project to store uploaded file in the database:

// Writes file to the database
private int WriteToDB(string strName, string strType, ref byte[] Buffer)
{
	int nFileID = 0;

	// Create connection
	OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

	// Create Adapter
	OleDbDataAdapter dbAdapt = new OleDbDataAdapter("SELECT * FROM tblFile", dbConn);
			
	// We need this to get an ID back from the database
	dbAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			
	// Create and initialize CommandBuilder
	OleDbCommandBuilder dbCB = new OleDbCommandBuilder(dbAdapt);

	// Open Connection
	dbConn.Open();
					
	// New DataSet
	DataSet dbSet = new DataSet();
					
	// Populate DataSet with data
	dbAdapt.Fill(dbSet, "tblFile");

	// Get reference to our table
	DataTable dbTable = dbSet.Tables["tblFile"];

	// Create new row
	DataRow dbRow = dbTable.NewRow();

	// Store data in the row
	dbRow["FileName"] = strName;
	dbRow["FileSize"] = Buffer.Length;
	dbRow["ContentType"] = strType;
	dbRow["FileData"] = Buffer;

	// Add row back to table
	dbTable.Rows.Add(dbRow);

	// Update data source
	dbAdapt.Update(dbSet, "tblFile");

	// Get newFileID
	if( !dbRow.IsNull("FileID") )
		nFileID = (int)dbRow["FileID"];
			
	// Close connection
	dbConn.Close();

	// Return FileID
	return nFileID;
}


Access database table tblFile that has 5 fields defined as follow: 
FileID ¨C autonumber
Filename ¨C text 255
FileSize - long integer
ContentType ¨C text 100
FileData ¨C OLE Object 

The code in the above function is very straight forward. I create OleDbConnection, OleDbDataAdapter and DataSet objects and then append a row to the only table in DataSet. The important thing to note here is that OleDbCommandBuilder object needs to be created and initialized with a reference to OleDbDataAdapter object to build the insert query for us automatically. Also if you would like to retrieve newly assigned FileID of the file you have just stored in the database you need to make sure you set MissingSchemaAction property of Adapter to MissingSchemaAction.AddWithKey. That assures that Primary Key/autonumber FileID field will be populated with new ID when you call Update method of your DataAdapter.

Local ASPNET account has to have write permissions to a database file in order for your code to succeed. If you file is placed anywhere under wwwroot you will need to manually give database file all the necessary permissions before running my demo project.

Retrieving of a file from database.

I used the same aspx page to read and return file data out of the database in my demo project. I check for FileID parameter being passed to a page. If it was passed I know that I need to return a file rather than process user¡¯s input:

private void Page_Load(object sender, System.EventArgs e)
{
	// Check if FileID was passed to this page as a parameter
	if( Request.QueryString["FileID"] != null )
	{
		// Get the file out of database and return it to requesting client
		ShowTheFile(Convert.ToInt32(Request.QueryString["FileID"]));
	}
			
}

private void ShowTheFile(int FileID)
{
	// Define SQL select statement
	string SQL = "SELECT FileSize, FileData, ContentType FROM tblFile WHERE FileID = " 
				+ FileID.ToString();

	// Create Connection object
	OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

	// Create Command Object
	OleDbCommand dbComm = new OleDbCommand(SQL, dbConn);

	// Open Connection
	dbConn.Open();

	// Execute command and receive DataReader
	OleDbDataReader dbRead = dbComm.ExecuteReader();

	// Read row
	dbRead.Read();

	// Clear Response buffer
	Response.Clear();

	// Set ContentType to the ContentType of our file
	Response.ContentType = (string)dbRead["ContentType"];

	// Write data out of database into Output Stream
	Response.OutputStream.Write((byte[])dbRead["FileData"], 0, (int)dbRead["FileSize"]);

	// Close database connection
	dbConn.Close();

	// End the page
	Response.End();
}

Using Assembly in C# - similar to dll

• create a key file, run command: sn -k myKeyName.snk

• in the source .cs file:

  using System.Reflection;
  using System.Runtime.CompilerServices;
  [assembly: AssemblyTitle("ekuweb.ActiveDirectory")]
  [assembly: AssemblyVersion("1.0.5000.0")]
  [assembly: AssemblyKeyFile("myKeyName.snk")]
  
• compile as a dll file: csc /t:library /out:ekuweb.ActiveDirectory.dll checklogin.cs
• add to GAC: gacutil /if ekuweb.ActiveDirectory.dll
• find the manifests about this assembly using command gacutil /l

• in web.config add the assembly:

  <assemblies>
  <add assembly="System.DirectoryServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  <add assembly="ekuweb.ActiveDirectory, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=617fd54ccbccc3b4" />
  </assemblies>

• to use this assembly, define: using ekuweb.ActiveDirectory;

Include a C# file from aspx web page:

<%@ Page Language="C#" Src="webform.cs" Inherits="ekuweb.webform" %>

Src="cs source file name"
Inherits="namespace.classname"

using protected for all web controls in the web page:
protected txtEmail;
protected txtPassword;
protected System.Web.UI.HtmlControls.HtmlForm content;