SQL Snippets

Some useful SQL Snippets:

Oracle

Create a date without any formatting hassle (like to_date):

SELECT DATE'2014-01-01' FROM dual;

Extract values as numbers from a date object

SELECT EXTRACT (YEAR FROM DATE'2013-05-06') FROM dual;
SELECT EXTRACT (MONTH FROM DATE'2013-05-06') FROM dual;
SELECT EXTRACT (DAY FROM DATE'2013-05-06') FROM dual;

Conversion of geographical coordinates into different coordinate systems:

SELECT srid,cs_name FROM cs_srs WHERE srid IN (82027, 82032, 8307)
 
-- Von GK4 nach GK3
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82032, SDO_POINT_TYPE(4465656.81, 5338112.31, NULL), NULL, NULL ), -- Geometry in GK4
  82027                                                                                 -- Zielkoordinatensystem
) FROM dual;
 
-- und zurück
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82027, SDO_POINT_TYPE(3688714.69, 5341125.2, NULL), NULL, NULL ), -- Geometry in GK3
  82032                                                                                -- Zielkoordinatensystem
) FROM dual;
 
-- und wenn wir schon dabei sind nach WSG84 (google)
SELECT sdo_cs.transform(
  SDO_GEOMETRY(2001, 82027, SDO_POINT_TYPE(3688714.69, 5341125.2, NULL), NULL, NULL ), -- Geometry in GK3
  8307                                                                                -- Zielkoordinatensystem
) FROM dual;

Select the tables that have a foreign key on a given table:

SELECT r.table_name, r.constraint_name
FROM all_constraints s
JOIN all_constraints r ON r.constraint_type = 'R' AND r.r_constraint_name = s.constraint_name
WHERE LOWER(s.table_name) = '<table_name>';

Last update: 2014/01/17

See also:

Git Snippets and Maven Snippets.

| Comments (0) »

19-Dec-13


Archiving OS X Mavericks tags (and other data) with git

For the last 6 months i’ve been archiving all my paper work (OCR’ing and than trashing it) to a personal documents repository.

There are some document managers out there but every single one felt like overkill to me, so i just stick to a pretty simple directory structure which is enough for me.

Although i need those documents across devices, i didn’t want to use a cloud service to sync them. git does a pretty good job here.

With Mac OS X Mavericks comes a great new feature: Tagging. Certainly we’ve all used tags somewhere on the internet and i really like this kind of taxonomies. It’s way better than a fixed folder structure.

So, i can now tag all my documents without the need for an external program.

But what about sync? Those tags are stored in the extended file attributes of the Mac OS X filesystem (along with other stuff, for example if the file has been download from the web or email). git does not include those extended attributes in a repository so they will be lost.

xattr to the rescue. xattr can dump all extended attributes for all files in a directory and also can write them back.

I use the following pre-commit hook to dump all extended attributes of my archive to a file named .metadata

#!/bin/sh
 
xattr -lrx . > .metadata
git add -f .metadata

This can be a problem if only tags are modified as nothing will be committed. This can be handled by an empty commit:

git commit --allow-empty -m "New Tags

To restore them i use the following post-merge hook which is also executed after a pull (i’m pretty much doing only pulls on this repository anyway).

#!/usr/bin/env ruby
# Be careful, this can be something you don't want:
# strip all existing extended attributes
system("xattr -cr .")
 
pattern_header = /([^\0]+): (.+):/
pattern_data = /\d{8} (.+) +\|.+\|/
 
data, current_file, current_attribute = '', nil, nil
 
File.readlines('.metadata').each do |line|  
  # collect hex data
  if(m = pattern_data.match(line) and current_file)
    m = pattern_data.match line
    data += m[1].to_s.strip if m and m[1]  
  # starting hex data for a new file
  elsif(m = pattern_header.match(line))
    # we have some data for the current file
    if current_file and data != ''
      system("xattr -wx #{current_attribute} #{data.gsub(/ /, '')} \"#{current_file}\"")
    end   
    data, current_file, current_attribute = '', m[1], m[2] 
  elsif current_file   
    m = pattern_data.match line
    data += m[1].to_s.strip if m and m[1]  
  end
end

This hook is pretty simple and one can surely think of better ways for storing (and / or parsing) the data and add some error handling, but this works quite well for my purpose.

This hook also stores every extended attribute. If you’re only interested in meta tags, than only sync the “com.apple.metadata:_kMDItemUserTags” attribute.

| Comments (2) »

25-Oct-13


Creating a simple IP blacklist with mod_rewrite and RewriteMap

I’ve had a lot of URL spam (bots testing URLs for weaknesses and exploits). They haven’t been successful but i have configured my system to send me an e-mail when exceptions occurs and there have a been a lot of e-mails.

I wanted a simple solution with the following features:

  • No additional packages to install
  • No server restarts when the blacklist changes

In the given setup Apache is merely a proxy to my backend and a simple .htaccess inside the root directory with a lot of ip entries wouldn’t work. Instead this would have gone into the vhost definition which means server restarts / reloads.

I came up with the idea using the RewriteMap directive of mod_rewrite.

You can ask a RewriteMap for a value with anything ModRewrite and apache variables give you.

This is what my map (blacklist.txt) looks like:

111.73.45.82    b
111.73.45.151   b
111.73.45.164   b

I use httxt2dbm to create DBM Hash File

httxt2dbm -i blacklist.txt -o blacklist.dbm

and then in my vhost definition:

RewriteMap ipmap dbm:/etc/apache2/sites-available/blacklist.dbm 
RewriteCond ${ipmap:%{REMOTE_ADDR}} ^b$            [NC]
RewriteRule .* - [F,L]

That means:

Use the given blacklist.dbm as map named ipmap. Than ask for the value for the given remote address. If the value is “b” than deny access to all pages.

Simple, but effective.

| Comments (1) »

06-Sep-13


Pretty print XML data within Oracle Databases

Just a short post without further explanation.

Pretty printing xml in Oracle 10g and 11g differences quite a bit:

10g:

SELECT XMLTYPE('<html><body><p>Hallo, Welt.</p></body></html>').extract('/') FROM dual;

11g:

SELECT XMLSERIALIZE(Document XMLTYPE('<html><body><p>Hallo, Welt.</p></body></html>') AS CLOB INDENT SIZE = 2) FROM dual;

| Comments (2) »

05-Jun-13


How to fix jQuery UIs autocomplete width

I recently noticed that the combobox item on an jQuery UI autocomplete grows way beyond the textfield input which is kinda ugly.

This is how i fix it (that is, make it as width as the input item):

$.extend($.ui.autocomplete.prototype.options, {
	open: function(event, ui) {
		$(this).autocomplete("widget").css({
            "width": ($(this).width() + "px")
        });
    }
});

This code applies it to every autocomplete.

| Comments (17) »

02-May-13