Davor Josipovic Just another WordPress blog – rather tryout

03/04/2013

Search for methods and properties in WMI with PowerShell

Filed under: PowerShell,Programming — Tags: , — Davor @ 10:14

Here is some simple recursive code to search for methods/properties in the WMI.

Much can be improved, but for now, it is a start.

##########################################################################################
## CHANGE LOG
#########################
##
## v0.1
## - first version
##########################################################################################
 
filter seek_properties {
	$_ | Get-Member | select-object name | property $_;
}
 
filter property ($obj) {			
			if ((++$global:i % 100000) -eq 0) { echo $i;}
			if ($obj.($_.name) -match 'YOUR SEARCH STRING') {
				echo "Object $($obj.__path) contains in property $($_.name): $($obj.$($_.name))";
				echo $obj;
				echo '-----------------------------------------------';
			}
}
 
function start_search ($namespace) {
	Get-WmiObject -Namespace $namespace -list * | foreach {
		if ($_.__CLASS -eq "__NAMESPACE") {
			Get-WmiObject -Namespace $namespace $_.__CLASS | foreach { start_search "$($_.__NAMESPACE)\$($_.Name)"; } 		
		} else {
			echo "$namespace`: $($_.__CLASS)";
			Get-WmiObject -Namespace $namespace $_.__CLASS | seek_properties; 
		}
	}
 
}
 
$global:i = 0;
 
start_search "ROOT";

This script can take quite some time to finish…

28/07/2012

Transcode mkv – batch file for windows

Filed under: batch,Programming — Tags: , , , , , — Davor @ 10:47

What it does: transcode your mkv to x264 with a specified file size. You drag & drop your mkv-file on the batch file, chose some options like target size, and it starts the transcoding process. It re-encodes the video and leaves all other streams intact (unless you exclude them). It uses handbreakcli and mkvtoolnix.

The batch file can be found on GitHub. I based it mainly on this fine article (for linux).

It’s still a work in progress, but currently, it does the job.

What you need is:

Make sure you set the correct paths in the transcode_settings.ini file. The rest should work.

Here is how it works. After you drag a file on transcode.bat, a command line window opens:

There is only one destination path set, so I press “0”. I can also fill in a new destination path. This new destination path will then be saved in transcode_settings.ini and will be available next time under option [1]. Now I have the option to remove some audio and subtitle tracks.

Since I don’t want to remove any audio tracks I press “Enter”. Then I press “1” to remove the English subtitle stream.

Now I have to set some general transcoding options. I want a very good quality to bitrate ratio, so I select the very slow option by pressing “V”. I further select “A” for auto cropping (which is in this case equivalent to no cropping), and I also type “N” to signify that the movie is not black & white. Since I want a fixed size encode, I type “Y”, “N”, “N” to get an impression of what the optimal size would be.

The batch will now transcode a few samples based on the selected settings, and suggest a few optimal transcoding techniques:

For DVD resolution, if I transcode the audio stream to ac3 @448kbit/s, then the 700MiB size is 10% within the optimal range of predefined RF 20. Now I can select the handbrake transcode setting. I type “S” for a fixed size 2-pass transcode, “D” for AC3 448kbit/s audio transcode, “D” for DVD resolution, and twice “N” since I don’t want any noise reduction or decomb filter. Since I selected “S” for Size in the first option, I am now aked what my target size is. I type “1” for 700MiB and the transcoding process starts.

Since this is an ordinary batch file, all the options are easily adjustable to one’s own needs.

15/07/2012

Deus Ex: Human Revolution – some thoughts from a fan

Filed under: Gaming — Tags: , — Davor @ 20:36

I played the original twice. When I was 17, and later when I was about 22. I am not really into shooters – yes, I skipped the Invisible War – but this game is so much more. One gets immersed into the future as seen by its creator Warren Spector. To describe this “not the end of the world but you can see it from there”-feeling would take many pages. So I’ll keep it short. Beside the great gameplay, sound and story, Deus Ex confronts you with moral dilemmas and makes you think about human augmentation and just polity. Beside the entertainment value it also has some “educational” value.

So now we have a new prequel Deus Ex: HR. Usually I play a game 2-4 years after its release, when it’s close to bugfree and possibly improved by the mods. But after I saw the Deus Ex: HR trailer and the intro I just couldn’t resist… So here is what I liked and did not like about the game. It might contain spoilers.

The visuals are stunning and memorable. Some are simply jawdropping. The creativity involved in making the world look mystical, but at the same time futuristic and “Deus Ex” is really top notch. These artists definitely understand the original.

Just like the original, this Deus Ex has definitely a memorable soundtrack. Very futuristic and at the same time “Deus Ex”. The voice acting is also incredibly well done, for all the NPC, but especially the main character and main NPC’s.

Overall gameplay is good to very good/perfect. Although this game seems to be made for console, it plays very well on PC too. It simply feels right after you get used to the gameplay. It is also obvious that much time has been spent on making the gameplay flawless, and incorporating various ways for one to get to the objective.

The dialogues are much improved compared to the original. There is much more interaction and choice left to the player. The social interaction aug is also very well implemented. The thing that bothered me were the sometimes shallow facial expressions.

Story is OK for a Deus Ex game. Whereas the first Deus Ex dealt with the just governmental system, the subject of HR is more about the dangers of augmentation and human transcendence. From that perspective it seems that the end of HR was pushed to resemble the original Deus Ex ending where one could choose – and play God. I did miss some depth and consistency. The story seems artificially prolonged because the plot is too simple, and at times becomes boring. So although the plot of HR is good and has great potential, the presentation of it seems far less to me. I think I missed the message during my 60h play. The original did better here, although it didn’t had as much content – I think.

Quake-like boss fights? Well, I don’t think they have a place in a Deus Ex game. Also it is a pity that these bosses are completely void of personality – in contrast with the other NPC.

The skill system is removed, and now completely replaced with the augmentation system. I really liked the skill system: it gave one a certain level of specialization that is unrelated to augs. Like better aiming with weapons you specialize in, faster movement with heavy weapons, staying longer under water, some hacking skills, medical skills, etc. So, to me this was definitely a big loss. An unique playing experience could be highly increased with a skill system.

The augs now get unlocked with XP-points. Most are well designed and well implemented in the game. The fact that you already have them all removes the “wauw” effect of finding one though. Auto-recharging one battery is a good improvement from the previous series – now one can experiment without permanently using up the bio-energy. But recharging only one seems inconsistent: having two charged cells and taking one enemy down will “waste” this second cell, while doing the same with one cell will not – the empty cell will reload eventually.

The praxis packs (and other items) scattered through the game on sometimes strange locations… it could have been better. Same with the empty lockers…

I also missed a way to replay (or re-read) the conversations I had with people. Sometimes there is much information at once, and you don’t want to miss a thing… I though the original had a log for this.

The hacking is improved – resembles System Shock (?) – but is imbalanced in my opinion. With a few aug-points one can hack everything and get money and XP for free. One can misuse this eventually. On hardest settings I had eventually more than 40 stops! and viruses in my backpack, and gathered very much gold. Even the toughest terminals went down easily. Such hacking makes searching for passcodes completely irrelevant.

Hacked turrets and robots have no cameras. They could have them.

The stealth difficulty could be higher. The patrol routes could be extended, randomized, etc. Now the guards usually don’t go just far enough to see you. Also, the AI during alarm or hostile faze could be better. The guards don’t go too far in their search, and if they do, they sometimes go alone, which makes them easy to take down unnoticed. Guards also don’t notice when some of them are missing for a long time.

The third person view unlocked with the right mouse button is interesting but lacks explanation – certainly for a game that is trying to look as realistic as possible. How come Adam is able to have such a “field of view”?

At the same time there are much eBooks scattered through the world that refer to real-life happenings. It’s kind of strange… it doesn’t feel right.

The DLC – Missing Link – doesn’t run from the main game. This is a pity. The pre-order special text in the weapon descriptions I also found very bothering.

Replay value? Not so if you ask me. But hard to tell why though. Maybe because the game tries to be so realistic your every decision feels like it is definite. The game feels more like an onetime experience… which sticks!

So does it live up to the name? I think it does. To be honest: there are flaws. But the vocals and visuals are stunning. The experience is unique. The story is OK. The gameplay is quite ambitious, and speaking of value: it is definitely worth the money. I am happy that de developers also tried successfully new things and didn’t simply brought us the original in a new package – like with HL2 (!!). I think they should have gone even further – although this is always a big risk. Because of this, I find it not as “redefining” as the original was, but at the same time I cannot look past its aspirations. So, although there are definitely things that could have been better, these shortcomings don’t measure up against the love, care and attention for detail that have been put into this game.

10/07/2011

Select screen for XBMC without 3th party tools

Filed under: batch — Tags: , — Davor @ 20:43

This is usually done by changing the default display with some tool (DisplaySwitch.exe, UltraMon,…) Here we simply use XBMC’s advanced settings and a simple batch file which will allow to select the startup options (in this case the screen), copy the corresponding advancedsettings.xml to the \userdata\ folder and start XBMC on the correct screen with custom properties.
For this we use XBMC in portable mode (with command line option –p), but it could also work without. Make this folder structure in your XBMC directory:

portable_data\config\adv_settings

In adv_settings folder we put 2 files from which we will chose during startup:
Advancedsettings_monitor.xml:

<advancedsettings>
	<videoscreen>
        		<screenmode>WINDOW</screenmode>
	</videoscreen>
    	<window>
        		<height>720</height>
	        	<width>1280</width>
    	</window>
	<input>
        		<enablemouse>false</enablemouse>
	</input>
</advancedsettings>

Advancedsettings_tv.xml:

<advancedsettings>
	<videoscreen>
		<screenmode>10128000720050.00000</screenmode>
	</videoscreen>
</advancedsettings>

These values are taken from guisettings.xml in the \userdata\ folder. So it’s best you set up XBMC and take the settings you need from guisettings.xml and put them into advancedsettings.xml. Apparently, in my case 10128000720050.00000 corresponds to full screen on the TV.
Finally this simple batch file will let you choose between the two:

@set batch_path=%~dp0

:: Choose Monitor-------------------------------------
@choice /C 12 /M "Start on TV[1], Monitor[2]?" /N /T 3 /D 1
@goto ANSWER%ERRORLEVEL%
::----------------------------------------------------
 
:ANSWER0
:ANSWER255
@GOTO ERROR
 
:ANSWER1
@copy "%batch_path%adv_settings\advancedsettings_tv.xml" "%batch_path%..\userdata\advancedsettings.xml" /Y
@IF ERRORLEVEL 1 GOTO ERROR
@GOTO exit
 
:ANSWER2
@copy "%batch_path%adv_settings\advancedsettings_monitor.xml" "%batch_path%..\userdata\advancedsettings.xml" /Y
@IF ERRORLEVEL 1 GOTO ERROR
@GOTO exit
 
:ERROR
@echo "Wrong batch execution... check batch"
@pause
@GOTO QUIT
 
:EXIT
@start /D "%batch_path%..\..\" XBMC.exe -p
 
:QUIT

Now all you need to do is run the batch file, type 1 or 2 and XBMC will start on the correct screen. It is also obvious that these advanced settings allow much more customization than is shown here.

An other interesting way would be to use the API called SetwindowsPos… and even more interesting would be to expose the XMBC API to the command line prompt as suggested here.

26/11/2010

Google Analytics on-the-fly insertion with Apache

Filed under: php,Programming — Tags: , — Davor @ 22:46

I recently needed to insert the Google Analytics script on the fly on each html page that an Apache server was sending. Kirill Minkovich has made a good overview of the standard ways for doing so. Particularly the second and third solutions are interesting because they use the ext_filter_module which is meant for such things.

External filters are slow. Since Apache 2.2.7 there is a internal SUBSTITUTE filter.

AddOutputFilterByType SUBSTITUTE text/html
 
Substitute "s#<head(.*)>#<head$1>\
    <!-- Global site tag (gtag.js) - Google Analytics -->\
    <script async src=\"https://www.googletagmanager.com/gtag/js?id=UA-19913980-1\"></script>\
    <script>\
       window.dataLayer = window.dataLayer || [];\
       function gtag(){dataLayer.push(arguments);}\
       gtag('js', new Date());\
       gtag('config', 'UA-XXXXXXX-X');\
    </script>#iq"

That’s it! Make sure that DEFLATE filter is not run before, otherwise the SUBSTITUTE filter will get a gzipped stream and thus will not be able to insert the code.

Note: Aaron Gloege also has a very interesting php-only solution for this problem, but I, IMHO, thought it a bit farfetched. Nonetheless, it is probably (much) faster than the one used below.

Here is a simpler version for the filter.google-analytics.php file:

<?php
$ga_script =
"<script type=\"text/javascript\">
 
  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-XXXXXXXX-X']);
  _gaq.push(['_trackPageview']);
 
  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();
 
</script>";
 
$handle = fopen ("php://stdin","r");
// Will read the whole stream. 
// Use $sHtmlFile = fgets($handle); for testing purposes from command line.
$sHtmlFile = stream_get_contents($handle);
 
// We allow only one replacement before one of the 3 tags.
// From testing it appears that php PCRE engine replaces the
// first match from the stream. So the ga_script will probably
// be inserted before </head> as Google recommends.
$replacements;
$sHtmlFile = preg_replace(
	'/(<\/head>|<\/body>|<\/html>)/si',
	"$ga_script$1",
	$sHtmlFile,
	1,
	$replacements);
 
if ($replacements == 0)
{
	$sHtmlFile = $sHtmlFile . $ga_script;
}
 
echo $sHtmlFile;
?>

And of course, add the following to your apache.conf file:

LoadModule ext_filter_module modules/mod_ext_filter.so
ExtFilterDefine insert-google-analytics cmd="/bin/php /cgi-bin/filter.google-analytics.php" mode=output
AddOutputFilter insert-google-analytics htm html

24/11/2010

System-wide events and Qt

Filed under: C++,Programming,Qt — Tags: , , — Davor @ 23:02

Few days back I wanted to create a macro recorder for the desktop. I though I could do this from within the Qt framework which has a neat high-level platform-independent event system that I thought I could incorporate with widgets to make a nice user interface. Well, that’s what I though at least… Truth is, Qt doesn’t offer any means for catching system-wide events. Eventually, I managed to put together a little command line application that can record and replay/loop the mouse and keyboard input messages based on the Windows API. What you will read next is my explanation why catching system-wide events is impossible from within the Qt framework, what the Windows API offers for this purpose, and a proposal on how system-wide events could be incorporated in the Qt event system.

Background

Windows and Qt applications are event driven, which means that they wait for the system to pass events to them upon which they can act. Simply put, those events are passed as “messages” to application windows through a callback function named the “window procedure”.

For example, when a user moves a mouse, the mouse device driver places the recorded movement in the system’s message queue. The system then determines the destination window and “posts” this message to the correct “thread message queue” (i.e. the message queue of the thread which created the destination window). Our Qt application has only access to these messages from its thread message queue. These messages are called “spontaneous events” in the Qt documentation. (Note: there exist also “sent” messages which bypass the message queue, but which we do not consider here.)

Now as one can see, the system is very selective on which mouse and keyboard events our Qt application will get. For example, our window will not get any keyboard messages if it is not in focus, nor will it get any mouse events if the mouse is not inside the window borders. So how can we access those events from the Qt framework? To my knowledge Qt doesn’t offer any ready made functions for this, so we will have to find some other way: directly accessing the Windows API.

Hooks

The Windows API offers a mechanism called a “hook” which we can hook somewhere in the message-handling mechanism and which will intercept and reroute the messages to some “hook procedure” we define. We define a hook with the API SetWindowsHookEx procedure. This procedure allows us to specify the “hook type” which defines the kind of messages to be intercepted (like WH_MOUSE_LL, WH_KEYBOARD_LL, WH_SHELL), the “hook scope” which can be thread or global (i.e. system), and the hook procedure with the following predefined signature:

 LRESULT CALLBACK HookProc(int nCode, WPARAM wParam, LPARAM lParam);

So to get you started here is a very basic application which uses the above described mechanism an which can be used as a starting point for building applications which are able to monitor system events.

#include <QtGui/QApplication>
#include <QDebug>
 
#include <windows.h>
 
LRESULT CALLBACK HookProc(int nCode, WPARAM wParam, LPARAM lParam)
{
    // process event...
    qDebug() << nCode << wParam << lParam;
 
    return CallNextHookEx(NULL, nCode, wParam, lParam);
}
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
 
    if (SetWindowsHookEx(WH_KEYBOARD_LL, HookProc, qWinAppInst(), NULL) == 0)
        qDebug() << "Hook failed for application instance" << qWinAppInst() << "with error:" << GetLastError();
 
    return a.exec();
}

For more information, the following links might be interesting:

I’ve put together a little command line utility that is able to record and replay/loop the recording based on the above explanation. Sourcecode is available here. To record: “winrec -record /f “recording.txt””. To play: “winrec -play /f “recording.txt” /repeat 0″. To stop any of these, just press Crtl+Esc.

I think integration with an Qt would probably be straightforward when used with QAbstractEventDispatcher, although I didn’t have time to test it.

11/08/2010

“Undefined reference to vtable” for QObject-derived classes in the implementation (.cpp) file.

Filed under: Programming,Qt — Tags: , — Davor @ 18:14

“undefined reference to vtable” seems to be a common problem with Qt’s meta-object system in combination with GCC.

The main cause due to GCC is described here: http://gcc.gnu.org/faq.html#vtables

But with QObject it gets a bit more complex. When you use the macro Q_OBJECT, you define some virtual methods which might trigger the above GCC error under certain circumstances. So make sure your use of the Q_OBJECT macro fulfills the following requirements:

  1. Make sure the Q_OBJECT macro is present in the definition of all QObject-derived classes
  2. Make sure you define your QObject-derived classes in your header files ONLY
  3. Make sure all of your header files are listed in your .pro file in the HEADERS-list
  4. Run qmake every time you add Q_OBJECT to one of your classes or modify your .pro file

(Source: http://www.theirishpenguin.com/2007/07/01/qobject-qmake-and-sadness-undefined-reference-to-vtable/)

Not fulfilling point 2:
You get the “undefined reference to vtable” error if you put your QObject-derived class in the implementation file because moc will not “add” the implementation of the virtual functions to the cpp file. Apparently you can enforce this by adding “#include cpp_file_name.moc” to your “cpp_file_name.cpp” file. Moc will detect this and will generate a “cpp_file_name.moc” file.

29/06/2010

PostgreSQL table audit in plpgsql

Filed under: Postgres,Programming — Tags: , — Davor @ 15:27

I recently had to implement a logging system for our database. Googling resulted in a few interesting ways to do this. The best I have found so far is from Lorenzo Alberton. I recommend you glance through that, and also an extension/improvement of it by James Gardner.

Now, Lorenzo’s implementation has a few problems. Most notably: not all of us want to install Tcl. So, I have “rewritten” it in plpgsql. This is possible since in the meantime the EXECUTE command was extended with the USING clause.

Some extra features are added also:

  • Support for multi-field PK’s.
  • Logging of PK’s.
  • Changes are detected by default type operators instead of textbased operators.

Note: I am using a slightly different naming convention, so you should adjust the names before using it with extra functions from James.

Note2: This is still a (working) tryout. Comments on improvement are welcome.

Update: fnc_audit(…) is used by trg_audit()

CREATE OR REPLACE FUNCTION private.tfc_audit()
  RETURNS TRIGGER AS
$BODY$ 
 
/*!
 Dynamic query exectution through plpgsql EXECUTE command makes this function possible.
 Some complications which can araise using this command are described here:
  - http://archives.postgresql.org/pgsql-general/2008-05/msg00314.php
*/ 
 
DECLARE 
 
_PK_NAMES CONSTANT VARCHAR[] := ARRAY(SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
     WHERE c.oid = i.indrelid
     AND a.attrelid = i.indexrelid
     AND a.attisdropped = FALSE /* such column names are like "........pg.dropped.1........" and are invalid */
     AND a.attnum > 0 /* i.e is not a system column like OID: they have (arbitrary) negative numbers */ 
     AND i.indisprimary = TRUE /* is pk */
     AND c.oid = TG_RELID /* regclass takes current schema into consideration! */); 
 
_FIELD_NAMES CONSTANT VARCHAR[] := ARRAY(SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a
     WHERE c.oid = a.attrelid
     AND a.attisdropped = FALSE /* such column names are like "........pg.dropped.1........" and are invalid */
     AND a.attnum > 0 /* i.e is not a system column like OID: they have (arbitrary) negative numbers */ 
     AND c.oid = TG_RELID ); /* regclass takes current schema into consideration! */ 
 
_PK_VALUES VARCHAR[]; 
 
BEGIN 
 
-- this function works only when marked as AFTER trigger!
PERFORM assert(TG_WHEN = 'AFTER', 'Wrong execution of fnc_audit(): should only be executed AFTER an event!');
-- this function works only when marked as FOR EACH ROW trigger!
PERFORM assert(TG_LEVEL = 'ROW', 'Wrong execution of fnc_audit(): should only be executed FOR EACH ROW!'); 
 
-- table sanity check
PERFORM assert(TG_TABLE_NAME IS NOT NULL, 'TG_TABLE_NAME IS NULL');
PERFORM assert(_PK_NAMES IS NOT NULL AND array_length(_PK_NAMES, 1) IS NOT NULL, 'Table ' || TG_TABLE_NAME || ' can not be logged: it has no PK!');  -- FOR-loop will otherwise throw "upper bound of FOR loop cannot be null" 
 
-- skip changes on audit_table: otherwise endless loop
IF TG_TABLE_NAME = 'tbl_audits' THEN
 RETURN NULL; -- result is ignored since this is an AFTER trigger
END IF; 
 
-- find PK value
FOR i IN 1 .. array_length(_PK_NAMES, 1)
LOOP
 DECLARE
  tmp text;
  tmp_record record;
 BEGIN
  CASE TG_OP
  WHEN 'UPDATE', 'INSERT' THEN tmp_record = NEW;
  WHEN 'DELETE' THEN tmp_record = OLD;
  END CASE; 
 
  EXECUTE 'SELECT $1.' || quote_ident(_PK_NAMES[i]) INTO STRICT tmp USING tmp_record;
  _PK_VALUES[i] := tmp;
 END;
END LOOP; 
 
-- PK values sanity check
PERFORM assert(_PK_VALUES IS NOT NULL AND array_length(_PK_VALUES, 1) IS NOT NULL, '_PK_VALUES IS NULL'); -- FOR-loop will otherwise throw "upper bound of FOR loop cannot be null"
PERFORM assert(array_length(_PK_VALUES, 1) = array_length(_PK_NAMES, 1), '_PK_VALUES count not equal to _PK_NAMES count in table ' || TG_TABLE_NAME); 
 
-- find fields to be logged
FOR i IN 1 .. array_length(_FIELD_NAMES, 1)
LOOP
 -- field_name sanity check
 PERFORM assert(_FIELD_NAMES[i] IS NOT NULL, '_FIELD_NAMES[' || i || '] IS NULL'); 
 
 DECLARE
  _field_name CONSTANT VARCHAR := quote_ident(_FIELD_NAMES[i]);
  _distinct BOOLEAN;
  _old text;
  _new text;
 BEGIN 
 
 CASE TG_OP --fall through is unfortunately not supported
  WHEN 'UPDATE' THEN
   -- compare the two fields according to the type-default functions.
   -- note that <> returns false on NULL input and IS DISTINCT FROM does not, but only if both inputs are NULL!
   EXECUTE 'SELECT $1.' || _field_name || ' IS DISTINCT FROM $2.' || _field_name || ';' INTO STRICT _distinct USING OLD, NEW;
   CONTINUE WHEN NOT _distinct; 
 
   EXECUTE 'SELECT $1.' || _field_name || ';' INTO STRICT _new USING NEW;
   EXECUTE 'SELECT $1.' || _field_name || ';' INTO STRICT _old USING OLD;
  WHEN 'INSERT' THEN
   EXECUTE 'SELECT $1.' || _field_name || ';' INTO STRICT _new USING NEW;
  WHEN 'DELETE' THEN
   EXECUTE 'SELECT $1.' || _field_name || ';' INTO STRICT _old USING OLD;
  ELSE
   RAISE EXCEPTION 'Unhandled TG_OP in fnc_audit(): %', TG_OP;
 END CASE; 
 
 CONTINUE WHEN _new IS NULL AND _old IS NULL; 
 
 PERFORM private.fnc_audit(statement_timestamp()::TIMESTAMP WITHOUT TIME zone, SESSION_USER::VARCHAR, TG_TABLE_NAME::VARCHAR, _field_name, _PK_NAMES, _PK_VALUES, TG_OP::private.table_audit_mod_type, _old, _new);
 
 END;
END LOOP; 
 
RETURN NULL; -- result is ignored since this is an AFTER trigger
END; 
 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

A specific function for inserting into tbl_audits is used:

CREATE OR REPLACE FUNCTION private.fnc_audit(_audi_timestamp TIMESTAMP WITHOUT TIME zone, _audi_user name, _audi_table name, _audi_field name, _audi_pk_name CHARACTER VARYING[], _audi_pk_value CHARACTER VARYING[], _audi_mod_type private.table_audit_mod_type, _audi_value_old text, _audi_value_new text)
  RETURNS void AS
$BODY$
DECLARE
BEGIN 
 
INSERT INTO private.tbl_audits(audi_timestamp, audi_user, audi_table, audi_field, audi_pk_name, audi_pk_value, audi_mod_type, audi_value_old, audi_value_new)
 VALUES (_audi_timestamp, _audi_user, _audi_table, _audi_field, _audi_pk_name, _audi_pk_value, _audi_mod_type, _audi_value_old, _audi_value_new); 
 
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

TG_OP is stored as an ENUM (= more efficient) so we need a new type:

CREATE TYPE private.table_audit_mod_type AS ENUM ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE');

Here is a very important helper function:

CREATE OR REPLACE FUNCTION assert(BOOLEAN, CHARACTER VARYING)
  RETURNS void AS
$BODY$
BEGIN
 IF NOT $1 OR $1 IS NULL THEN
   IF $2 IS NOT NULL THEN 
     RAISE EXCEPTION 'Assert failure: %', $2;
   END IF;
   RAISE NOTICE 'Assert. Message is null';
 END IF; 
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

And finaly the audit table:

CREATE TABLE private.tbl_audits
(
  audi_timestamp TIMESTAMP WITHOUT TIME zone NOT NULL,
  audi_user CHARACTER VARYING(30) NOT NULL,
  audi_table CHARACTER VARYING(45) NOT NULL,
  audi_field CHARACTER VARYING(30) NOT NULL,
  audi_pk_name CHARACTER VARYING(30)[] NOT NULL,
  audi_pk_value CHARACTER VARYING(60)[] NOT NULL,
  audi_mod_type private.table_audit_mod_type NOT NULL,
  audi_value_old text,
  audi_value_new text,
  CONSTRAINT tbl_audits_check_pk CHECK (array_length(audi_pk_name, 1) = array_length(audi_pk_value, 1))
);

A more storage-efficient way to store logged data would be to make an audi_changeset column which consists of an array of changed fieldnames, old values and new values for each PK during a statement execution. It would be equivalent to the following view:

CREATE OR REPLACE VIEW vew_audit_changesets AS
 SELECT tbl_audits.audi_timestamp, tbl_audits.audi_user, tbl_audits.audi_table, tbl_audits.audi_pk_name, tbl_audits.audi_pk_value, tbl_audits.audi_mod_type, ARRAY[array_agg(tbl_audits.audi_field), array_agg(tbl_audits.audi_value_old)::CHARACTER VARYING[], array_agg(tbl_audits.audi_value_new)::CHARACTER VARYING[]] AS audi_changeset
   FROM tbl_audits
  GROUP BY tbl_audits.audi_timestamp, tbl_audits.audi_user, tbl_audits.audi_table, tbl_audits.audi_pk_name, tbl_audits.audi_pk_value, tbl_audits.audi_mod_type;

fnc_audit() is easily adjustable to store logs to such a table. But I wonder whether this is worth the fuss? Most of the queries run on vew_audit_changesets would first require the “unnesting of array audi_changeset to sets” (i.e. a structure which resembles tbl_audits). Why? Because SQL is set-oriented, not array-oriented. In other words, it seems easy to convert from tbl_audits structure to vew_audit_changesets structure, but how does one go from vew_audit_changesets structure to tbl_audits structure, and is this conversion efficient? Is it worth to save logs in a vew_audit_changesets-like table?

01/06/2010

Restore last viewed page when opening pdf files in Acrobat

Filed under: Uncategorized — Tags: — Davor @ 19:07

A very nice feature of WinDjView is that on opening a document it by default goes to the page you viewed the last time before you closed it. Now, for those who read pdf files, one may wonder whether this feature is supported by Adobe Acrobat. And guess what: It is. Go to Edit->Preferences->Documents and select “Restore last view settings when reopening documents”.

As far as I am concerned, this feature should be on by default.

11/05/2010

IsDirty() class member for QDataWidgetMapper

Filed under: C++,Programming,Qt — Tags: , — Davor @ 15:57

Here is something I wanted to share. It’s a pity something similar isn’t implemented by the Trolls. But then again, it doesn’t require much coding either.

    bool isDirty() const {
        Q_ASSERT(orientation() == Qt::Horizontal);
        Q_ASSERT(rootIndex() == QModelIndex());
        for(int i = 0; i < model()->columnCount(); i++) {
            QWidget *mapWidget = mappedWidgetAt(i);
            if (mapWidget){
                QByteArray p = mappedPropertyName(mapWidget);
                QModelIndex idx = model()->index(currentIndex(), i);
                if (idx.data(Qt::EditRole) != mapWidget->property(p))
                    return true;
            }
        }
        return false;
    }

One use is to check whether an update is required before going to the next record…

PS Other way one could achieve the same thing by adjusting the source code because the “mappings”-container is in the private implementation of QDataWidgetMapper.

« Newer PostsOlder Posts »

Powered by WordPress