Saturday, July 10, 2010

XQuery

All text is from www.w3schools.com/xquery/

XQuery is to XML what SQL is to database tables.

XQuery is case-sensitive and XQuery elements, attributes, and variables must be valid XML names.

FLWOR

FLWOR is an acronym for "For, Let, Where, Order by, Return".

The for clause selects all book elements under the bookstore element into a variable called $x.
The where clause selects only book elements with a price element with a value greater than 30.
The order by clause defines the sort-order. Will be sort by the title element.
The return clause specifies what should be returned. Here it returns the title elements.

All text is from www.w3schools.com/XPath/

XPath is a language for finding information in an XML document.

XPath includes over 100 built-in functions. There are functions for string values, numeric values, date and time comparison, node and QName manipulation, sequence manipulation, Boolean values, and more.

In XPath, there are seven kinds of nodes:

element
attribute
text
namespace
processing-instruction
comment
document nodes

The XML Example Document

Selecting Nodes

Path Expression	Result
bookstore	Selects all the child nodes of the bookstore element
/bookstore	Selects the root element bookstore Note: If the path starts with a slash ( / ) it always represents an absolute path to an element!
bookstore/book	Selects all book elements that are children of bookstore
//book	Selects all book elements no matter where they are in the document
bookstore//book	Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element
//@lang	Selects all attributes that are named lang

Predicates

Predicates are always embedded in square brackets.

Path Expression	Result
/bookstore/book[1]	Selects the first book element that is the child of the bookstore element. Note: IE5 and later has implemented that [0] should be the first node, but according to the W3C standard it should have been [1]!!
/bookstore/book[last()]	Selects the last book element that is the child of the bookstore element
/bookstore/book[last()-1]	Selects the last but one book element that is the child of the bookstore element
/bookstore/book[position()<3]	Selects the first two book elements that are children of the bookstore element
//title[@lang]	Selects all the title elements that have an attribute named lang
//title[@lang='eng']	Selects all the title elements that have an attribute named lang with a value of 'eng'
/bookstore/book[price>35.00]	Selects all the book elements of the bookstore element that have a price element with a value greater than 35.00
/bookstore/book[price>35.00]/title	Selects all the title elements of the book elements of the bookstore element that have a price element with a value greater than 35.00

Wildcard

Wildcard	Description
*	Matches any element node
@*	Matches any attribute node
node()	Matches any node of any kind

In the table below we have listed some path expressions and the result of the expressions:

Path Expression	Result
/bookstore/*	Selects all the child nodes of the bookstore element
//*	Selects all elements in the document
//title[@*]	Selects all title elements which have any attribute

Selecting Several Paths

By using the | operator in an XPath expression you can select several paths.

Path Expression	Result
//book/title \| //book/price	Selects all the title AND price elements of all book elements
//title \| //price	Selects all the title AND price elements in the document
/bookstore/book/title \| //price	Selects all the title elements of the book element of the bookstore element AND all the price elements in the document

XPath Axes

AxisName	Result
ancestor	Selects all ancestors (parent, grandparent, etc.) of the current node
ancestor-or-self	Selects all ancestors (parent, grandparent, etc.) of the current node and the current node itself
attribute	Selects all attributes of the current node
child	Selects all children of the current node
descendant	Selects all descendants (children, grandchildren, etc.) of the current node
descendant-or-self	Selects all descendants (children, grandchildren, etc.) of the current node and the current node itself
following	Selects everything in the document after the closing tag of the current node
following-sibling	Selects all siblings after the current node
namespace	Selects all namespace nodes of the current node
parent	Selects the parent of the current node
preceding	Selects everything in the document that is before the start tag of the current node
preceding-sibling	Selects all siblings before the current node
self	Selects the current node

The syntax for a location step is:

axisname::nodetest[predicate]

Examples

Example	Result
child::book	Selects all book nodes that are children of the current node
attribute::lang	Selects the lang attribute of the current node
child::*	Selects all children of the current node
attribute::*	Selects all attributes of the current node
child::text()	Selects all text child nodes of the current node
child::node()	Selects all child nodes of the current node
descendant::book	Selects all book descendants of the current node
ancestor::book	Selects all book ancestors of the current node
ancestor-or-self::book	Selects all book ancestors of the current node - and the current as well if it is a book node
child::*/child::price	Selects all price grandchildren of the curren

Examples

Select all the titles

/bookstore/book/title

Select the title of the first book

/bookstore/book[1]/title

Select price nodes with price>35

/bookstore/book[price>35]/price

Select title nodes with price>35

/bookstore/book[price>35]/title

Wednesday, May 26, 2010

File permissions notation in Linux

Every file or folder in Linux has access permissions. There are three types of permissions (what allowed to do with a file):

read access
write access
execute access

Permissions are defined for three types of users:

owner of the file
group that the owner belongs to
other users

Numeric (octal) representation

Octal digit	Text equivalent	Binary value	Meaning
0	---	000	All types of access are denied
1	--x	001	Execute access is allowed only
2	-w-	010	Write access is allowed only
3	-wx	011	Write and execute access are allowed
4	r--	100	Read access is allowed only
5	r-x	101	Read and execute access are allowed
6	rw-	110	Read and write access are allowed
7	rwx	111	Everything is allowed

Difference in access permissions for files and folders

Access type	File	Folder
Read	can read	can listing
Write	can write to file	can create, rename, delete files in direcory
Execute	can be executed	can pass through or into

Special bit

setuid: Execute file with owner permission (example: program su); -rws------; bit:4000
setgid: If file execute this with group permission.; If folder save every file into folder with folder group permission. All file in folder have been the same group.; -rwxrws---; bit:2000
sticky: If file already is not supported; If folder has sticky bit can delete files into folder only owner of file or superuser.; -rwxrwxrwt; bit:1000

File types

Regular file: -
Directory: d
Link: l
Special files: c
Socket: s
Named pipe: p
Block device: b

Examples

chmod 644 myFile.txt
chmod 4700 /bin/ls (setuid)
chmod 2770 /bin/ls (setgid)
chmod 1777 myFolder (sticky)
chown tomas myFile.txt
chgrp user myFile.txt
chown tomas.user myFile.txt

Wednesday, April 14, 2010

VPN client server configuration

In our company we need to allow certain members access to our computer network from the outside. Therefore, we decided to get a VPN.

We choose OpenVPN, because it is Open Source and it is available for Linux, Windows and another OS.

You can let running VPN server on Linux and let blunt Windows client to connect to server.

For server-client configuration you need to generate keys and certificates, but configuration is very simple.

Server configuration

/etc/openvpn/server.conf


port 1194
proto tcp
dev tun
keepalive 10 60
comp-lzo

server  10.10.1.0 255.255.255.0
ifconfig-pool-persist /etc/openvpn/ipp.txt
max-clients 3

push "route 172.16.128.0 255.255.255.0"
push "dhcp-option DNS 172.16.128.1"

ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/akela.crt
key /etc/openvpn/keys/akela.key
dh /etc/openvpn/keys/dh1024.pem
tls-auth /etc/openvpn/keys/static.key

log-append /var/log/openvpn.log
status /var/log/openvpn-status.log
mute 10
verb 5

All important you find out in man pages or on project documentation.

push "dhcp-option DNS 172.16.128.1" get only Windows client. For Linux client you need script. See HOWTO

Generate certificates

You can use OpenSSL, but developers of OpenVPN prepare scripts for simply generate keys and certificates for server and clients.

You find out it: /user/share/doc/openvpn/examples/easy-rsa/2.0/

Scripts config file is vars. There you can set same variables.

1. We load the config file to system, and create self-signed Certificate Authority


. ./vars
./clean-all
./build-ca

In folder keys (depend on your vars files) you find out file:

ca.pem - public certificate
ca.key - private key of Certificate Authority

2. we create key and certificate for server.


./build-key-server akela

It creates file:

akela.pem - certificate of your server signed of your CA
akela.ctr - request of sign (you don´t need that file)
akela.key - private key of your server

3. we create Deffie-Hellmann parameters for dynamic key encryption


./build-df

It creates file:

dh1024.pem

4. we create static TLS-AUTH key


openvpn --genkey --secret static.key

It creates file:

static.key

Client configuration

We need to create certificate for your client.

In your server use:


./build-key kibo

kibo.pem - certificate of your client(notebook) signed of your CA
kibo.ctr - request of sign (you don´t need that file)
kibo.key - private key of your client(notebook)

In your client(notebook) save this file:

kibo.pem
kibo.key
ca.pem
static.key

Clinet configuration on /etc/openvpn/client.conf in Linux client.

Clinet configuration on ..\ProgramFiles\OpenVPN\Config\client.ovpn in Windows client.


client
pull
dev tun
proto tcp
remote ourServer.tld

ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/kibo.crt
key /etc/openvpn/keys/kibo.key
tls-auth /etc/openvpn/keys/static.key

comp-lzo
verb 4
mute 10
ns-cert-type server

Start server and then try to connect from client.

openvpn /etc/openvpn/server.conf - on Linux server (or as service)
openvpn /etc/openvpn/client.conf - on Linux client(notebook)

OpenVPN and IPTables


$IPTABLES -A INPUT -p tcp --dport 1194 -j ACCEPT       # VPN new
$IPTABLES -A INPUT -i tun+             -j ACCEPT       # VPN tun
$IPTABLES -A FORWARD -i tun+ -o $LAN_IFACE -j ACCEPT   # VPN forward >
$IPTABLES -A FORWARD -i $LAN_IFACE -o tun+ -j ACCEPT   # VPN forward <

Good Luck.

Tuesday, April 6, 2010

www.harvart.cz with Grails

For us last web project www.harvart.cz we used Grails framework.
Earlier we used Spring MVC with Spring Roo for all us projects.
We are really excited to work with Grails framework.
Let mi introduce same of features in Grails:

Rapid development

We don´t often restart Tomcat. After changes in controller classes, gsp pages or config classes is project automatically reloaded. When you add or change same bean in Spring context, it automatically reload too. It is very comfortable behaviour.

URL mapping

For URL mapping Grails use simple mapping file:

"/clanky"(controller:'article', action:'all')
"/clanky/$url"(controller:'article', action:'show')
"/klub"(controller:'klub', action:'about')
"/fotogalerie"(controller:'gallery', action:'all')
"/fotogalerie/$url"(controller:'gallery', action:'show')

You only determine which controller and action is conjunction with url. When you use Grails link tag then the url is automatically compile in all your HTML links. It is grait!

GORM

Every domain class has automatically all GRUD methods. You don´t need to write boring DAO classes.

Plugins

There are a lot of useful plugins. You only install same plugin and you can use it immediately. In www.harvart.cz we used search plugin. We only marked domain class, which can be search. It is unbelievably simply!

Conclusion

Grails is great! As developer you can concentrate to write your own code only. Groovy give you all you need as web developer. You can use all for Spring, Hibernate and SiteMesh.

Saturday, April 3, 2010

MYSQL - JOIN conjunction

Where you have two and more tables, you can made conjunction between table.

You can use this kind of conjunction:

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL JOIN
CROSS JOIN

Source table


mysql> select* from EMPLOYEE;
+----+--------+---------------+
| ID | NAME   | ID_DEPARTMENT |
+----+--------+---------------+
|  1 | Tom    |             1 | 
|  2 | Petr   |             1 | 
|  3 | Jan    |             2 | 
|  4 | Michal |             2 | 
|  5 | Pepa   |             3 | 
|  6 | Zdenek |             0 | 
+----+--------+---------------+


mysql> select* from DEPARTMENT;
+----+----------+
| ID | TITLE    |
+----+----------+
|  1 | economy  | 
|  2 | it       | 
|  3 | research | 
|  4 | sales    | 
+----+----------+

INNER JOIN

Return only corresponding records. You use inner join or join or empty clause.

This selects are the same:


select * from EMPLOYEE inner join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID


select * from EMPLOYEE join DEPARTMENT on EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID


select * from EMPLOYEE, DEPARTMENT where EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID


+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  4 | Michal |             2 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
+----+--------+---------------+----+----------+

LEFT OUTER JOIN

Return all records from left table. You can use left outer join or
left join clause only.


mysql> SELECT *                         
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+----+--------+---------------+------+----------+
| ID | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+----+--------+---------------+------+----------+
|  1 | Tom    |             1 |    1 | economy  | 
|  2 | Petr   |             1 |    1 | economy  | 
|  3 | Jan    |             2 |    2 | it       | 
|  4 | Michal |             2 |    2 | it       | 
|  5 | Pepa   |             3 |    3 | research | 
|  6 | Zdenek |             0 | NULL | NULL     | 
+----+--------+---------------+------+----------+

RIGHT OUTER JOIN

Return all records from right table. You can use right outer join or
right join clause only.


mysql> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+----+----------+
| ID   | NAME   | ID_DEPARTMENT | ID | TITLE    |
+------+--------+---------------+----+----------+
|    1 | Tom    |             1 |  1 | economy  | 
|    2 | Petr   |             1 |  1 | economy  | 
|    3 | Jan    |             2 |  2 | it       | 
|    4 | Michal |             2 |  2 | it       | 
|    5 | Pepa   |             3 |  3 | research | 
| NULL | NULL   |          NULL |  4 | sales    | 
+------+--------+---------------+----+----------+

CROSS JOIN

Create cartesian product, You can use cross join or empty.

This selects are the same:


SELECT * FROM EMPLOYEE CROSS JOIN DEPARTMENT


SELECT * FROM EMPLOYEE, DEPARTMENT


mysql> SELECT *
    -> FROM EMPLOYEE
    -> CROSS JOIN DEPARTMENT;
+----+--------+---------------+----+----------+
| ID | NAME   | ID_DEPARTMENT | ID | TITLE    |
+----+--------+---------------+----+----------+
|  1 | Tom    |             1 |  1 | economy  | 
|  1 | Tom    |             1 |  2 | it       | 
|  1 | Tom    |             1 |  3 | research | 
|  1 | Tom    |             1 |  4 | sales    | 
|  2 | Petr   |             1 |  1 | economy  | 
|  2 | Petr   |             1 |  2 | it       | 
|  2 | Petr   |             1 |  3 | research | 
|  2 | Petr   |             1 |  4 | sales    | 
|  3 | Jan    |             2 |  1 | economy  | 
|  3 | Jan    |             2 |  2 | it       | 
|  3 | Jan    |             2 |  3 | research | 
|  3 | Jan    |             2 |  4 | sales    | 
|  4 | Michal |             2 |  1 | economy  | 
|  4 | Michal |             2 |  2 | it       | 
|  4 | Michal |             2 |  3 | research | 
|  4 | Michal |             2 |  4 | sales    | 
|  5 | Pepa   |             3 |  1 | economy  | 
|  5 | Pepa   |             3 |  2 | it       | 
|  5 | Pepa   |             3 |  3 | research | 
|  5 | Pepa   |             3 |  4 | sales    | 
|  6 | Zdenek |             0 |  1 | economy  | 
|  6 | Zdenek |             0 |  2 | it       | 
|  6 | Zdenek |             0 |  3 | research | 
|  6 | Zdenek |             0 |  4 | sales    | 
+----+--------+---------------+----+----------+

FULL JOIN

You need to use two JOINs and UNION. More example in article How to simulate FULL OUTER JOIN in MySQL


mysql> SELECT *
    -> FROM EMPLOYEE
    -> LEFT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID
    -> UNION
    -> SELECT *
    -> FROM EMPLOYEE
    -> RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.ID_DEPARTMENT = DEPARTMENT.ID;
+------+--------+---------------+------+----------+
| ID   | NAME   | ID_DEPARTMENT | ID   | TITLE    |
+------+--------+---------------+------+----------+
|    1 | Tom    |             1 |    1 | economy  | 
|    2 | Petr   |             1 |    1 | economy  | 
|    3 | Jan    |             2 |    2 | it       | 
|    4 | Michal |             2 |    2 | it       | 
|    5 | Pepa   |             3 |    3 | research | 
|    6 | Zdenek |             0 | NULL | NULL     | 
| NULL | NULL   |          NULL |    4 | sales    | 
+------+--------+---------------+------+----------+

COALESCE

In most of DB engine you can find COALESCE function. It is helpful where you need to work with NULL values.

source table


mysql> select * from INFO;
+----+-------------+--------------+
| ID | GALLERY     | PHOTOGRAPHER |
+----+-------------+--------------+
|  1 | Cars        | Tom          | 
|  2 | Countryside | Petr         | 
|  3 | City        | Jan          | 
|  4 | Sport       | NULL         | 
+----+-------------+--------------+

When you need to return not NULL report, you can use COALESCE

COALESCE function


mysql> select GALLERY, coalesce(PHOTOGRAPHER,"unknown") as PHOTOGRAPHER from coalesce;
+-------------+--------------+
| GALLERY     | PHOTOGRAPHER |
+-------------+--------------+
| Cars        | Tom          | 
| Countryside | Petr         | 
| City        | Jan          | 
| Sport       | unknown      | 
+-------------+--------------+