Tuesday, November 25, 2014

Tomcat

Apache Tomcat Servlet/JSP container.

Apache Tomcat version 7.0 implements the Servlet 3.0 and JavaServer Pages 2.2
-------
$CATALINA_HOME: root of your Tomcat installation.
Optionally, Tomcat may be configured for multiple instances by defining $CATALINA_BASE for each instance. If multiple instances are not configured, $CATALINA_BASE is the same as $CATALINA_HOME.
---------
These are some of the key tomcat directories:
/bin - Startup, shutdown, and other scripts. The *.sh files (for Unix systems) are functional duplicates of the *.bat files (for Windows systems).
/conf - Configuration files and related DTDs. The most important file in here is server.xml. It is the main configuration file for the container.
/logs - Log files are here by default.
/webapps - This is where your webapps go.
----------
All of the information in the configuration files is read at startup, meaning that any change to the files necessitates a restart of the container.
--------
Installing Tomcat on Windows can be done using the Windows installer.
Installation as a service:Tomcat is automatically started when Windows starts. For optimal security, the service should be run as a separate user, with reduced permissions.
-----
Tomcat 7.0 was designed to run on Java SE 6.
---------
set an environment variable CATALINA_HOME that contains the pathname to the directory in which Tomcat has been installed. Optionally, if Tomcat has been configured for multiple instances, each instance will have its own CATALINA_BASE configured.
---------
WAR: Web Application Archive
A web application is defined as a hierarchy of directories and files in a standard layout. Such a hierarchy can be accessed in its "unpacked" form, where each directory and file exists in the filesystem separately, or in a "packed" form known as a Web ARchive, or WAR file.
---------
The top-level directory of your web application hierarchy is also the document root of your application. Here, you will place the HTML files and JSP pages that comprise your application's user interface. When the system administrator deploys your application into a particular server, he or she assigns a context path to your application. Thus, if the system administrator assigns your application to the context path
/catalog
, then a request URI referring to
/catalog/index.html
 will retrieve the index.html file from your document root.
-------
document root directory:
    *.html, *.jsp, etc. - The HTML and JSP pages, along with other files that must be visible to the client browser (such as JavaScript, stylesheet files, and images) for your application. In larger applications you may choose to divide these files into a subdirectory hierarchy, but for smaller apps, it is generally much simpler to maintain only a single directory for these files.
    /WEB-INF/web.xml - The Web Application Deployment Descriptor for your application. This is an XML file describing the servlets and other components that make up your application, along with any initialization parameters and container-managed security constraints that you want the server to enforce for you.
    /WEB-INF/classes/ - This directory contains any Java class files (and associated resources) required for your application, including both servlet and non-servlet classes, that are not combined into JAR files. If your classes are organized into Java packages, you must reflect this in the directory hierarchy under /WEB-INF/classes/. For example, a Java class named com.mycompany.mypackage.MyServlet would need to be stored in a file named /WEB-INF/classes/com/mycompany/mypackage/MyServlet.class.
    /WEB-INF/lib/ - This directory contains JAR files that contain Java class files (and associated resources) required for your application, such as third party class libraries or JDBC drivers.
-----------
When you install an application into Tomcat, the classes in the WEB-INF/classes/ directory, as well as all classes in JAR files found in the WEB-INF/lib/ directory, are made visible to other classes within your particular web application.
----------
/WEB-INF/web.xml file contains the Web Application Deployment Descriptor for your application. It defines everything about your application that a server needs to know (except the context path, which is assigned by the system administrator when the application is deployed).
-----------
DTD: Document Type Descriptor
---------
META-INF/context.xml file can be used to define Tomcat specific configuration options, such as an access log, data sources, session manager configuration and more.
-------------
In order to be executed, a web application must be deployed on a servlet container.
----------
A web application can be deployed in Tomcat by one of the following approaches:
・Copy unpacked directory hierarchy into a subdirectory in directory $CATALINA_BASE/webapps/. Tomcat will assign a context path to your application based on the subdirectory name you choose.
・Copy the web application archive file into directory $CATALINA_BASE/webapps/. When Tomcat is started, it will automatically expand the web application archive file into its unpacked form, and execute the application that way.
・Use the Tomcat "Manager" web application to deploy and undeploy web applications. Tomcat includes a web application, deployed by default on context path /manager, that allows you to deploy and undeploy applications on a running Tomcat server without restarting it.
・Use "Manager" Ant Tasks In Your Build Script. Tomcat includes a set of custom task definitions for the Ant build tool that allow you to automate the execution of commands to the "Manager" web application.
・Use the Tomcat Deployer. Tomcat includes a packaged tool bundling the Ant tasks, and can be used to automatically precompile JSPs which are part of the web application before deployment to the server.
--------
source code files

All of these components exist under a top level project source directory for your application:
    docs/ - Documentation for your application, in whatever format your development team is using.
    src/ - Java source files that generate the servlets, beans, and other Java classes that are unique to your application. If your source code is organized in packages (highly recommended), the package hierarchy should be reflected as a directory structure underneath this directory.
    web/ - The static content of your web site (HTML pages, JSP pages, JavaScript files, CSS stylesheet files, and images) that will be accessible to application clients. This directory will be the document root of your web application, and any subdirectory structure found here will be reflected in the request URIs required to access those files.
    web/WEB-INF/ - The special configuration files required for your application, including the web application deployment descriptor (web.xml, defined in the Servlet Specification), tag library descriptors for custom tag libraries you have created, and other resource files you wish to include within your web application. Even though this directory appears to be a subdirectory of your document root, the Servlet Specification prohibits serving the contents of this directory (or any file it contains) directly to a client request. Therefore, this is a good place to store configuration information that is sensitive (such as database connection usernames and passwords), but is required for your application to operate successfully.
-----------

Apache HTTP Server

Apache HTTP Server

Apache is configured by the files in the conf subdirectory.
ThreadsPerChild: It tells the server how many threads it should use. This is the maximum number of connections the server can handle at once, so be sure to set this number high enough for your site if you get a lot of hits. The recommended default is ThreadsPerChild 150.
You can review the Windows Application Event Log by using the Event Viewer, e.g. Start - Settings - Control Panel - Administrative Tools - Event Viewer.
-------
install Apache as a Windows NT service from the command prompt at the Apache bin subdirectory:
httpd.exe -k install
--------
Removing an Apache service
httpd.exe -k uninstall
---------
If Apache is running as a service, stop it by opening another console window and entering:
httpd.exe -k shutdown
------
To run Apache from the command line as a console application:
httpd.exe
Press control-C to stop
---------
Running as a service should be preferred over running in a console window because this lets Apache end any current operations and clean up gracefully.
-------
to restart. It forces it to reread the configuration file. Any operations in progress are allowed to complete without interruption. Press Control-Break in the console window you used for starting Apache, or if the server is running as a service, enter:
httpd.exe -k restart
--------
specifies an absolute path to a particular configuration file:
httpd.exe -f "c:\my server files\anotherconfig.conf"

specifies a relative path to a particular configuration file:
httpd.exe -f files\anotherconfig.conf
-----------
If you don't specify a configuration file with -f or -n, Apache will use the file name compiled into the server, such as conf\httpd.conf. This built-in path is relative to the installation directory.
----------
After starting Apache (either in a console window or as a service) it will be listening on port 80.
-----
To connect to the server and access the default page, launch a browser and enter this URL:
http://localhost/
You may have to use this URL:
http://127.0.0.1/
If you happen to be running Apache on an alternate port, you need to explicitly put that in the URL:
http://127.0.0.1:8080/
-------
Addresses on the Web are expressed with URLs - Uniform Resource Locators - which specify a protocol (e.g. http), a servername (e.g. www.apache.org), a URL-path (e.g. /docs/current/getting-started.html), and possibly a query string (e.g. ?arg=value) used to pass additional arguments to the server.
A client (e.g., a web browser) connects to a server (e.g., your Apache HTTP Server), with the specified protocol, and makes a request for a resource using the URL-path.
---------
In order to connect to a server, the client will first have to resolve the servername to an IP address - the location on the Internet where the server resides. Thus, in order for your web server to be reachable, it is necessary that the servername be in DNS.
More than one hostname may point to the same IP address, and more than one IP address can be attached to the same physical server. Thus, you can run more than one web site on the same physical server, using a feature called virtual hosts.
-------
The default configuration file is usually called httpd.conf.
The configuration is frequently broken into multiple smaller files, for ease of management. These files are loaded via the Include directive.
The server is configured by placing configuration directives in these configuration files. A directive is a keyword followed by one or more arguments that set its value.
--------
Static content is things like HTML files, image files, CSS files, and other files that reside in the filesystem. The DocumentRoot directive specifies where in your filesystem you should place these files.
----------
Typically, a document called index.html will be served when a directory is requested without a file name being specified.
---------
The location of the error log is defined by the ErrorLog directive, which may be set globally, or per virtual host.
----------
When httpd starts, it binds to some port and address on the local machine and waits for incoming requests. By default, it listens to all addresses on the machine.
--------
The Listen directive tells the server to accept incoming requests only on the specified port(s) or address-and-port combinations.
For example, to make the server accept connections on both port 80 and port 8000, on all interfaces, use:
Listen 80
Listen 8000
-------
https is the default for port 443 and http the default for all other ports.
-----
You only need to set the protocol if you are running on non-standard ports. For example, running an https site on port 8443:
Listen 192.170.2.1:8443 https
-------
httpd configuration files contain one directive per line. The backslash "\" may be used as the last character on a line to indicate that the directive continues onto the next line. There must be no other characters or white space between the backslash and the end of the line.
Arguments to directives are separated by whitespace. If an argument contains spaces, you must enclose that argument in quotes.
Directives in the configuration files are case-insensitive, but arguments to directives are often case sensitive. Lines that begin with the hash character "#" are considered comments, and are ignored. Comments may not be included on a line after a configuration directive. Blank lines and white space occurring before a directive are ignored, so you may indent directives for clarity.
--------
Only shell environment variables defined before the server is started can be used in expansions.
-------
You can check your configuration files for syntax errors without starting the server by using
apachectl configtest
or the -t command line option.
--------
To see which modules are currently compiled into the server, you can use the -l command line option. You can also see what modules are loaded dynamically using the -M command line option.
--------
Directives placed in the main configuration files apply to the entire server. If you wish to change the configuration for only a part of the server, you can scope your directives by placing them in <Directory>, <DirectoryMatch>, <Files>, <FilesMatch>, <Location>, and <LocationMatch> sections. These sections limit the application of the directives which they enclose to particular filesystem locations or URLs. They can also be nested, allowing for very fine grained configuration.
----------
httpd has the capability to serve many different websites simultaneously. This is called Virtual Hosting. Directives can also be scoped by placing them inside <VirtualHost> sections, so that they will only apply to requests for a particular website.
--------
httpd allows for decentralized management of configuration via special files placed inside the web tree. The special files are usually called .htaccess, but any name can be specified in the AccessFileName directive. Directives placed in .htaccess files apply to the directory where you place the file, and all sub-directories. The .htaccess files follow the same syntax as the main configuration files. Since .htaccess files are read on every request, changes made in these files take immediate effect.
----------
In deciding what file to serve for a given request, httpd's default behavior is to take the URL-Path for the request (the part of the URL following the hostname and port) and add it to the end of the DocumentRoot specified in your configuration files. Therefore, the files and directories underneath the DocumentRoot make up the basic document tree which will be visible from the web.
For example, if DocumentRoot were set to
/var/www/html
 then a request for
http://www.example.com/fish/guppies.html
would result in the file
/var/www/html/fish/guppies.html
being served to the requesting client.

If a directory is requested (i.e. a path ending with /), the file served from that directory is defined by the DirectoryIndex directive. For example, if DocumentRoot were set as above, and you were to set:
DirectoryIndex index.html index.php
Then a request for http://www.example.com/fish/ will cause httpd to attempt to serve the file /var/www/html/fish/index.html. In the event that that file does not exist, it will next attempt to serve the file /var/www/html/fish/index.php.
--------
There are frequently circumstances where it is necessary to allow web access to parts of the filesystem that are not strictly underneath the DocumentRoot.
the Alias directive will map any part of the filesystem into the web space. For example, with
Alias /docs /var/web
the URL http://www.example.com/docs/dir/file.html will be served from /var/web/dir/file.html. The ScriptAlias directive works the same way, with the additional effect that all content located at the target path is treated as CGI scripts.
---------
Sometimes, it is desirable instead to inform the client that the requested content is located at a different URL, and instruct the client to make a new request with the new URL. This is called redirection and is implemented by the Redirect directive. For example, if the contents of the directory /foo/ under the DocumentRoot are moved to the new directory /bar/, you can instruct clients to request the content at the new location as follows:
Redirect permanent /foo/   http://www.example.com/bar/
This will redirect any URL-Path starting in /foo/ to the same URL path on the www.example.com server with /bar/ substituted for /foo/. You can redirect clients to any server, not only the origin server.
----------

Monday, November 24, 2014

MySQL

MySQL
RDBMS: relational database management system
SQL: Structured Query Language
LAMP: Linux, Apache, MySQL, Perl/PHP/Python
MySQL Workbench: free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structures. 
---------
ODBC: Open Database Connectivity
user account
privilege
----------
foreign key references
--------
trigger
---------
Ensuring high availability requires a certain amount of redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master, and using replication to keep secondaries available to take over in case the primary fails. This means that the "server" that the application connects to is in reality a collection of servers, not a single server. In a similar manner, if the application is using a sharded database, it is in reality working with a collection of servers, not a single server. In this case, a collection of servers is usually referred to as a farm.
---------
shell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
shell> mysql db_name < script.sql > output.tab

--column-names Write column names in results
--tee=file_name Append a copy of output to named file
--help, -?
--auto-vertical-output: Cause result sets to be displayed vertically if they are too wide for the current window, and using normal
tabular format otherwise. (This applies to statements terminated by ; or \G.)
--default-character-set=charset_name: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multibyte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
quit
exit: Exit mysql. Same as quit.
help: Display this help.
tee:  Set outfile [to_outfile]. Append everything into given outfile.
use:  Use another database. Takes database name as argument.
---------
The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can
contain the following special sequences.
\d The default database
\h The server host
\p The current TCP/IP port or socket file
\U Your full user_name@host_name account name
\u Your user name
\v The server version
-------
SELECT 'Today is' , CURDATE();
--------
mysqladmin

 create db_name
 debug: Tell the server to write debug information to the error log.
 drop db_name
 extended-status: Display the server status variables and their values.
 flush-privileges: Reload the grant tables
 password new-password
 ping:  Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
 shutdown: Stop the server.
 variables:  Display the server system variables and their values.
 version:  Display version information from the server.
------------
shell> mysql --help
shell> mysql -h host -u user -p
Enter password: ********
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER(),CURRENT_DATE;
SELECT USER();
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;
SHOW DATABASES;
USE test; use the database called "test"
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE DATABASE menagerie;
USE menagerie
shell> mysql -h host -u user -p menagerie
SELECT DATABASE()  : to see which database is currently selected
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; (to create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).)
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
SELECT * FROM pet;
DELETE FROM pet;
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
SELECT name, birth FROM pet ORDER BY birth;
SELECT name, birth FROM pet ORDER BY birth DESC;
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
SELECT DATABASE();
SELECT MAX(article) AS article FROM shop;
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-----------
A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:
'a string'
"another string"
Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
'a string'
'a' ' ' 'string'
--------------