alexandru toth

PHP Proxy for QlikView sever

2011-05-25T08:41:00.001+02:00

It is quite counter-intuitive that the server-settings for QVS have to be configured in Internet Explorer's settings page, using the same account that is used to run QVS service. Not to mention that whenever the account decides to configure itself as "roaming profile", or the domain policy for Internet explorer is changed, or .. the settings are lost = maps don't work.

Here is another way , where there is a local Apache web server running PHP and acting getting the Google maps thry the same proxy that is configured in Internet Explorer. It uses Windows authentication for the proxy server.

Probaly you can write the equivalent in other webserver (ex IIS ) and other programming language .

$usr = 'user:password';
$proxy = '10.11.12.13:8080';

//force header to JPEG
header("Content-type: image/jpeg\n\n");

//same GET parameters as with Google Maps
$url = 'http://maps.google.com/staticmap?center=' . $_GET['center'] .
'&zoom=' . $_GET['zoom'] .
'&maptype=' . $_GET['maptype'] .
'&size=' . $_GET['size'] .
'&key=' . $_GET['key'];

// create a new cURL resource
$ch = curl_init();

// set URL and other appropriate options
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);

//curl_setopt($ch, CURLOPT_HTTPPROXYTUNNEL, TRUE);
curl_setopt($ch, CURLOPT_PROXYAUTH, CURLAUTH_NTLM);
curl_setopt($ch, CURLOPT_PROXY, $proxy);
curl_setopt($ch, CURLOPT_PROXYUSERPWD, $usr);

// grab URL and pass it to the browser
curl_exec($ch);

// close cURL resource, and free up system resources
curl_close($ch);
?>

video about Reverse Snowflake Joins

2011-05-09T14:25:00.002+02:00

Hi,

Here is a nice video about RevJ : http://www.youtube.com/watch?v=UPZctPW9Nmc

-Alex

QVPR as a directed graph - part 2

2010-09-10T01:23:00.006+03:00

The QlikView 9 management console does not show complex dependencies between tasks , triggers, source documents. Also when deleting tasks there might be "orphaned" categories etc, not visible otherwise.

Let's render it with old and reliable Graphviz. So how does a big graph look like? For example like this (with names removed, due to usual non disclosure agreements)

In case you wonder why it looks small, you probably are using Internet Explorer and it's built-in Adobe SVG viewer. Those lack scrollbars when rendering SVG files, but one can right click and "zoom out" few times. Highly recommend to use any normal browser capable of rendering SVG such as Firefox, or Safari, or Chrome.

-Alex

reload metadata

2010-09-07T15:27:00.004+02:00

One is able to parse the *.qvw.log files for statements loading from database tables, Excel files or reading / writing to QVD files. again, rendering with Graphviz gives a nice directed graph

QVPR as a directed graph

2010-09-07T12:23:00.009+02:00

It takes 11 clicks to get QEMC open the “indented” task dependency. And you still don’t see it properly ..

Wouldn’t a directed graph be easier to understand?

The one bellow is made by extracting task dependencies from Trigger.xml , task names from ExternalProgramTask.XML, PauseTask.xml, DocumentTask.xml; document names from Sourcedocument.xml; categories from Category.xml . Rendering with Graphviz .

It looks as there are 3 reload tasks for same document. Adding also source document confirms it.

Tasks bellow do the same thing. It is easier to replace two external tasks with one (and make the calls from the batch file)

Adding the source documents will not create a mess.

QlikView : memory limits

2010-03-04T10:11:00.001+02:00

My client has several large machines ( 128+ Gb memory, 16+ processor cores) for developer use. There can be tens of instances of qv.exe developer tool running fine at the same time.

That is, until somebody makes a loop in the script, and loads several millions rows, and consumes all available virtual memory. In that case programs (and Windows services) will randomly fail with variations of "out of memory" and "not enough resources". In rare cases memory shortage is so bad that Windows will corrupt disk and registry .

Here is a very good remedy, inspired from Unix "ulimit": kill all processes consuming more than X amount of memory. Of course it is hard to say a correct limit. With current amounts of data, well behaved documents use up 30 Gb memory, and bad scripts consume in excess of 100 Gb memory and crash. There must be a sanity limit somewhere in between.

powershell -command "& {get-process qv | Where-Object {$_.PeakVirtualMemorySize64 -gt 50000000000 } | Stop-Process } "

Powershell is not particularly fast, but other command line tools (ex from Sysinternals) do not display correctly memory usage for 64 bit processes.

Script above is scheduled every 10 minutes, and has improved a lot the availability of the servers. Of course, here and there some developer starts yelling, but so far there was always something wrong with those QVW files. My favourite so far: Export 50 million rows (with calculated columns) to Excel ..

-Alex

link to revj from Canada

2008-11-11T09:03:00.002+02:00

Dr. Dwight Deugo from School of Computer Science, Carleton, Canada kindly mentions Reverse Snowflake Joins in his COMP5900 course on Open Source Software (Fall 2008). Link is here

Link from Pyparsing site

2008-09-26T11:14:00.000+02:00

There is a link from Pyparsing to revj

Digg

2008-09-12T21:33:00.001+02:00

Posted a link on Digg, hope that somebody will care to vote it up

online demo

2008-09-10T17:13:00.001+02:00

Finally managed to put an online demo of Reverse Snowflake Joins .

Link from Graphviz

2008-09-06T21:54:00.001+02:00

Reverse Snowflake Joins was linked from Graphviz site

Link from MonetDB

2008-08-27T12:57:00.003+03:00

MonetDB site posted a link to Reverse Snowflake Joins, their link is http://monetdb.cwi.nl/

Wikipedia

2008-08-05T14:57:00.001+03:00

And a link on Wikipedia :-)

Reverse Snowflake Joins

2008-08-04T22:21:00.001+03:00

Now the project is on SourceForge, at http://sourceforge.net/projects/revj/ . And a presentation at http://revj.sourceforge.net/

Reverse Snowflake Joins

2008-08-01T14:52:00.005+03:00

Just subbmited to Sourceforge.net a new project, called "Reverse Snowflake Joins" (revj). The description:

A tool that parses SQL Select statements and generates a diagram. In addition to joins, the diagram shows parts of the underlying SQL directly in the diagram. For example x=30 , GROUP BY (year), SUM(profit), HAVING MIN(age) > 18

And a sample diagram with some random column names, WHERE conditions and GROUP BYs

-Alex

Practical usage of "Tangible Functional Programming"

2008-07-30T20:15:00.004+03:00

Just watched the Google Talk about " Tangible Functional Programming" by Conal Elliott.

Got the idea of using it in practice by wrapping up existing applications and offering them inside the framework. For example a "transformation" around say, Firefox, that takes as input a string and represents as output the webpage (maybe of primitive type image) . An example application would be: input text box "www.google.com/search?q=" , another input text box with the actual query, say "Python", and the transformation "browse with Firefox"

Or a transformation for mysql command line utility, that would take inputs: database + username + password + SQL statement and return the query result as text.

Many applications , including GUIs, can be accessed on the simple command line + parameters paradigm, and as such could be wrapped up as TV transformations.

Cubulus and bitmap indexes opinion

2007-05-27T20:19:00.000+03:00

S. Lott has written a short note regarding Cubulus. Quote: "It may be that this technique helps a lot with an RDBMS that doesn't support the star schema via bit-mapped indexes. It may be that this technique only helps a little with a more modern RDBMS."

Somewhat true, haven't measured GROUP BY versus range-queries with bitmap indexes (say, in Oracle), and neither in MonetDB. Time will tell if simpler and "embarrasingly parallel" SQL is better..

Daily Python URL

2007-05-27T10:02:00.000+03:00

Cubulus mentioned in Pythonware's Daily Python URLon 2007-05-25

Cubulus mentioned by pyparsing

2007-05-27T09:59:00.000+03:00

Pyparsing is an easy to use parser for Python. The notice link: here

compiling MonetDB python libraries on the Mac

2007-05-16T00:00:00.000+03:00

MonetDB is a "think different" kind of database. Link: http://monetdb.cwi.nl

Here are some notes for the builtin python2.3 for Mac OS X 10.4.9 . It builds correctly, and it works after manually moving some libs .
-headers: /usr/include/python2.3/
-libs: /usr/lib (/usr/lib/libpython.dylib , /usr/lib/libpython2.dylib , /usr/lib/libpython2.3.dylib )
-site-packages: /usr/lib/python2.3/site-packages/
-dynamic libs: /usr/lib/python2.3/lib-dynload/

It is easy to get readline from www.darwinports.org (sudo port install readline):
-Headers: /opt/local/include/readline
-Libs: /opt/local/lib/libreadline.5.0.dylib /opt/local/lib/libreadline.a /opt/local/lib/libreadline.5.dylib /opt/local/lib/libreadline.dylib

Swig also from darwinports , installed as: /opt/local/bin/swig . (Get it with: sudo port install swig)

./configure --with-python=python2.3 --with-python-library=/usr/lib --with-python-incdir=/usr/include/python2.3/ --with-swig=/opt/local/bin/swig
make
sudo make install

This still DOES NOT copy MonetSQLdb under python2.3 site-packages, and places dynamic libs under /usr/local/lib/python2.3/site-packages/lib/ instead of usr/lib/python2.3/lib-dynload/

Fixed with:
cp /usr/local/lib/python2.3/site-packages/lib/_* ~/Desktop/clients/src/python/Cimpl/MonetSQLdb
cp /usr/local/lib/python2.3/site-packages/lib/_* ~/Desktop/clients/src/python/Cimpl

waiting for Sun's Try & Buy

2007-04-28T12:17:00.000+03:00

Cubulus can scale (tested on 1 and 2 cores computers = we can call this "theory" with regards to real servers) horizontally and vertically. I just can't wait for the Sun Ultra T1000 server from Try & Buy programme.

24 threads (or 32, depending on config), now that's scaling !

links to "new kid on the block"

2007-04-27T13:53:00.000+03:00

Another link from John Sequeira here:

Cubulus OLAP mentioned on SoftPedia

2007-04-27T10:10:00.002+03:00

New links on Softpedia:

Cubulus OLAP mentioned on Teleinteractive and Technorati

2007-04-24T06:41:00.000+02:00

One more post here: http://press.teleinteractive.net/oss/2007/04/23/cubulus_presentation , and tagged on Technorati

Cubulus OLAP mentioned by Gobán Saor

2007-04-23T06:38:00.000+02:00

Submitting Cubulus on Freshmeat was very succesfull: people started noticing it, and writing about it. For example
Goban Saor

Cubulus OLAP mentioned in Chris Webb's blog

2007-04-22T08:02:00.000+02:00

Cubulus OLAP mentioned in Chris Webb's blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1132.entry

Hurray!!

Python and multiple processes

2007-04-20T09:19:00.000+02:00

Hi,

Somebody was wondering about locking in Python (and CherryPy), link: http://groups.google.com/group/cherrypy-users/browse_thread/thread/bbc2800aa0f329c4?hl=en

Why not running running multiple python processes for CherryPy ? That would ease the GIL . You can "load balance" requests with Apache's mod_proxy .

If you need sessions, try web reverse proxy + state-full balancer called Pound : http://www.apsis.ch/pound/

From the docs: "a load balancer: it will distribute the requests from the client browsers among several back-end servers, while keeping session information." ... "Five possible ways of detecting a session have been implemented in Pound (hopefully the most useful ones): by client address, by Basic authentication, by URL parameter, by cookie and by header value"

Cheers, Alex

digest authentication with CherryPy

2007-04-04T12:06:00.000+02:00

For the authorizations, Trac project elegantly delegates authentication to Apache , see here

Unaware of Trac solution, I was fighting problem on my own:

CherryPy has built-in support for authorization. "Digest" and "ldap" are the ones that make most sense in practice.

I've been wondering if CherryPy is the right place for doing authorization. Because instead of _coding_ authorization it is easier to _declare_ it in the front web servers. Instead of re-inventing the wheel, let the right tool handle the job.

IMHO it is the Right Thing (TM) to do, since it also makes sense to use a reverse proxy (apache/lighttpd/squid) in front of CherryPy to deal with buffer overflows, malformed URLs and other nasty things.

This allows CherryPy to run in session-less mode, which allows for easier fail-over or load balancing. Another benefit Windows users: one could use "Integrated Windows Authorization" with IIS.

I tried to deploy this with Apache mod_proxy and mod_digest. Unfortunately, mod_proxy "eats" all extra headers (see http://allafrica.com/tools/apache/mod_proxy ). On Webfaction forum, Remi Delon suggested to try mod_rewrite. Well, Apache mod_rewrite is "voodoo" (quote from http://httpd.apache.org/docs/1.3/mod/mod_rewrite.html).. If someone manages to get it work, please let me know.

Instead, here is quick-and-dirty solution with .. lighttpd. Relevant lines from lighttpd.conf are :
server.modules = (
"mod_access",
"mod_auth",
"mod_proxy",
"mod_accesslog" )
server.port = 8090
proxy.server = ( "" => ( ( "host" => "127.0.0.1", "port" => 8080 ) ) )
auth.backend = "plain"
auth.backend.plain.userfile = "/opt/local/etc/lighttpd/lighttpd.user"
auth.require = ( "/" => (
"method" => "digest",
"realm" => "cubulus",
"require" => "valid-user"
) )

CherryPy receives header 'Authorization' with content 'Digest username="a", realm="cubulus", nonce=.., uri=.. qop="auth" ..... '

Easy thing is that if authorization fails, CherryPy receives.. nothing, so it's enough to look for Digest username="XXX"

Cheers, Alex
links: CherryPy wiki , Webfaction forum

digest authentication in CherryPy

2007-04-02T10:42:00.001+02:00

Apache already has mod_digest (and mod_ldap), and IMHO it is better to delegate security to Apache (rather than re-implement digest in CherryPy). Also, it might be safer to _configure_ security declaratively than code it.

Has anyone deployed CherrPy behind mod_digest (or mod_ldap), and retrieved the "Authorization-Info" ?

Or, is it a wrong question? I am trying a parallel with MS IIS , where is possible to set Directory Security to "Windows Integrated". The CGI or ASP pages retrieve the "authenticated user" server variable, or headers.

When deploying CherryPy (3.0.1) behind Apache (Apache/1.3.33-Darwin with mod_digest and mod_proxy), CherryPy only receives these headers, and there is no username...

Request Headers:
X-FORWARDED-SERVER: local
REFERER: http://local/index
ACCEPT-LANGUAGE: en
HOST: localhost:8080
ACCEPT: */*
USER-AGENT: Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/419 (KHTML, like Gecko) Safari/419.3
CONNECTION: close
Remote-Addr: 127.0.0.1
X-FORWARDED-HOST: local
X-FORWARDED-FOR: 192.168.255.3
ACCEPT-ENCODING: gzip, deflate

mySQL slow on MacOS, most of CPU time is %nice

2007-03-31T10:13:00.000+02:00

Most of the time mySQL appears "blue" in Activity Monitor, meaning that CPU time is mostly spent as "nice".

This happens with mysql-standard-4.1.13-apple-darwin8.1.0-powerpc (the one that comes with OS X 10.4.9), and same with mysql-5.1.16-beta-osx10.4-powerpc

First I thought that I'm running "wrong queries" (generated by Cubulus, work in progress), but today it happened during standard operations ex: "alter table fact drop column dim_4;" (MyISAM table with 2 500 000 rows)

Computer is an iMac G5 1.8 Ghz with 1.5 Gb memory (table fits in memory, there is no waiting due to disk). At the time of screen capture there was no other task doing anything relevant, except for Activity Monitor. There was no other query for mySQL.

Configuration /etc/my.cnf file is:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 0M
#lowered in order to minimize OS X context switching overhead
thread_concurrency = 2

#OS X recommended setting
skip-thread-priority

skip-networking

log-bin

server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Cubulus OLAP post on Partha's blog

2007-03-29T12:09:00.000+02:00

The post about Cubulus OLAP appeared in Partha's BLOG about partitioning in MySQL 5

Hurray!

live demo at Webfaction

2007-03-29T07:37:00.000+02:00

There is a live demo of Cubulus. Webfaction offers free hosting for Open Source projects.

if you must use Win**, use ipfw firewall

2007-03-26T20:27:00.001+02:00

Windows firewalls usually all suck. They have a complicated interface where you can see the alerts, flash when there i traffic..

Anyway, use wipfw, which is a port of the FreeBSD ipfw firewall (also used in Darwin and Mac OS).

Cubulus Olap

2007-03-18T20:50:00.000+02:00

I have an Open Source project at http://sourceforge.net/projects/cubulus . Who knows, maybe someone will read this blog, and click there

OS X firewall

2007-03-18T20:27:00.000+02:00

There is a nice article about improving OS X security http://www.tuaw.com/2007/03/12/make-os-x-more-secure-now/
. I am interested in closing some ports I don't need (no DNS server in my machine, no Bonjour, no printing..) in such way that fw rules will stay after reboot.

I have a script to delete un-needed ports. Since I can't figure a better way to modify the default rules of ipfw2, I will follow Sebastiaan de With's advice to use Lingon.

Here is the script, the Lingon job is easy: create new "User Daemon", put the path (no command line params needed) , set "Run at load", and reboot to see if it works. It Does !

-----------------------------------------------
/sbin/ipfw show > /tmp/origrules

awk '/dst-port 137 in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules > /tmp/newrules
awk '/dst-port 427 in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules >> /tmp/newrules
awk '/dst-port 631 in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules >> /tmp/newrules
awk '/dst-port 5353 in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules >> /tmp/newrules
awk '/67 to me in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules >> /tmp/newrules
awk '/5353 to me in/ {print "/sbin/ipfw del", substr($1,0,5)}' /tmp/origrules >> /tmp/newrules

chmod +x /tmp/newrules
sh /tmp/newrules

rm /tmp/origrules
rm /tmp/newrules

memcached ssslllooowww on Mac OS X

2007-02-17T16:59:00.000+02:00

The purpose of memcached is .. to be fast .. . Unfortunately the default port (Darwinports) is really slow (slow as in 3 seconds of doing nothing on a 1.8GHz PowerPC G5), and somebody figured out a fix.

Now here are few notes on how to recompile it (quick and dirty):

find the source /opt/local/var/db/dports/distfiles/memcached/memcached-1.1.11.tar.gz . Unpack it (ex: on Desktop)

as suggested in fix., edit the memcached.c file, adding #undef TCP_NOPSH on line 105
find the location of libevent (which came as part of port install)
in the folder with patched memcached run

./configure --with-libevent=/opt/local/var/db/dports/software/libevent/1.1a_0/opt/local/
make
take a look at the original permissions
copy new file into it's original location by: sudo mv memcached /opt/local/bin/memcached
restore original permissions : sudo chown root:admin /opt/local/bin/memcached

Now start memcached frm command line (memcached -vv for verbose logging), and test as it says in the README (from python):

import memcache
mc = memcache.Client(['127.0.0.1:11211'], debug=0)

mc.set("some_key", "Some value")
value = mc.get("some_key")

Now it will not wait 3 seconds

threadsafe CheryPy

2007-02-17T16:36:00.000+02:00

Using CherryPy is so easy, like everything in Python. But when running some pages with heavy SQL processing time, there are a lot of funny errors (malloc.. double free). Ooops, looks as something is not theadsafe. mySQLdb is not necessarily threadsafe, but cherrypy.engine.on_start_thread_list should handle this as suggested . Instead of digging deep into debugging, here is an alternative fix, using DBUtils.

Yipee, my code got posted into CherryPy:

import cherrypy
import MySQLdb

from DBUtils.PersistentDB import PersistentDB

class HomePage:
   @cherrypy.expose

   def index(self):
       sql = "select max(whatever) from bigtable;" #long running query

       c = persDb.connection().cursor()
       c.execute(sql)

       res = c.fetchone()
       c.close()
       return ' '.join( ['<html>', repr(res), '</html>'] )


cherrypy.tree.mount(HomePage())

if __name__ == '__main__': 
   persDb=PersistentDB(MySQLdb, 10000, host='localhost', db='db', passwd='****')

   cherrypy.config.update({'server.thread_pool': 10})
   cherrypy.server.quickstart()

   cherrypy.engine.start()