FxGqlC: Added aggregation functions ENLIST and ENLISTDISTINCT

Added aggregation functions ENLIST and ENLISTDISTINCT.
ENLIST creates a string value containing the list of all string values in their original order.
ENLISTDISTINCT creates a similar list, but the dupplicate values are removed, and the list is ordered. 

SELECT [Winner], COUNT(*), ENLIST([Tournament])
   FROM ['SampleFiles/Tennis-ATP-2011.csv' -Heading=On]
   GROUP BY [Winner] ORDER BY 2 DESC
SELECT [Winner], COUNT(*), ENLISTDISTINCT([Tournament])
   FROM ['SampleFiles/Tennis-ATP-2011.csv' -Heading=On]
   GROUP BY [Winner] ORDER BY 2 DESC

This feature is added to FxGqlC in v2.5-alpha5.

FxGqlC: Added new function 'PREFIX'

Added (non-aggregation) text function PREFIX to return the common prefix of two strings.
An aggregation function PREFIX (with 1 argument) was already added in v2.4.

SELECT PREFIX('0032478123456', '0032478654321')  
-- returns '0032478'

This feature is added to FxGqlC in v2.5-alpha5.

Converting a date/time to time_t using FxGqlC (or SQL)

The time_t is used in C++ to represent a date/time.  It is expressed in seconds since Januari 1st, 1970.

To get the current date/time as a time_t value, you can run this query in FxGqlC (or SQL):
select datediff(second, '1970-01-01', getutcdate())

You need to use getutcdate() because time_t defines the UTC time.

Or for any arbitrary date/time (in UTC):
select datediff(second, '1970-01-01', '2012-10-18 22:33') 
-- Returns 1350599580

The other way around is also easy: run this query to convert a time_t to a date/time
select dateadd(second, 1234567890, '1970-01-01')
-- Returns 13/02/2009 23:31:30

FxGqlC v2.3

A new version of FxGqlC has been released.

The major changes are documented here:
https://sites.google.com/site/fxgqlc/home/fxgqlc-manual/changes-in-fxgqlc-2-3

You can download FxGqlC v2.3 here:
https://sites.google.com/site/fxgqlc/home/downloads

Export of Office Outlook contacts to GMail

To import your Microsoft Office Outlook contacts to GMail or Google Apps, you need to export them first to a CSV file.

• In Outlook, go to the "File" tab in the ribbon menu, and click "Options" in the left sidebar.
• In the Outlook Options dialog, click on "Advanced" in the sidebar, and click the "Export" button. 
• In the first step of the Import and Export wizard, select "Export to a file", and click "Next".
• In the second step, select "Comma Separated Values (Windows)", and click "Next".
• In the third step, select your Contacts folder that you want to export (normally "Contacts"), and click "Next".
• In the fourth step, enter or select the filename, e.g. "contacts.csv".
• Click "Finish" to start the export.

When you import this file in GMail, and you are a member of a Windows Active Directory domain, the e-mail addresses are not imported.  Instead, the e-mail address field in GMail contains the "distinguished name" of your contact as known to your ActiveDirectory.  E.g. "cn=jsmith,ou=promotions,ou=marketing,dc=noam,dc=reskit,dc=com".
The real e-mail address is however included in the CSV file, as part of the column "E-mail Display Name", which contains the full name and the regular e-mail address between parentheses, but this column isn't used by the GMail import.

You could replace all E-Mail Addresses in the file using an Excel formula, or manually in a text-editor.
Or you can simply use this FxGqlC command to replace all e-mail address columns with the e-mail address taken from the display name:

select replaceregex($line, '\"/o=.*?\",\"EX\",(\".*?\((.*?)\)\")', '"$2","EX",$1') into [contacts2.csv] from [contacts.csv]

The same method can be used to replace national telephone numbers into an international format:
select replaceregex($line, '\+?(32\d{8,9})', '+$1') into [meucci3.csv] from [meucci2.csv]

You need to adopt the regular expression to a format appropriate for your contacts.

Import the resulting file in GMail, and that's it.

FxGqlC: Added support for DateTime datatype

SELECT convert(string, convert(datetime, '2012-07-13'), 'yyyyMMdd HH:mm:ss') 
-- Formats datetime using a format string, as defined by the .net Framework
--   "Standard Date and Time Format Strings" (http://msdn.microsoft.com/en-us/library/az4se3k1), and 
--   "Custom Date and Time Format Strings" (http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx)
SELECT datepart(day, '2012-07-13') -- returns 13
-- valid datepart values are: (with examples for '2012-07-12 23:59:50.1234567')
--    year,        yy, yyyy :   2012
--    quarter,     qq, q    :         3  (1 ... 4)
--    month,       mm, m    :         7  (1 ... 12)
--    dayofyear,   dy, y    :       194  (1 ... 366) 
--    day,         dd, d    :        12  (1 ... 31)
--    weekday,     dw, w    :         5  (1 = Sunday ... 7 = Saturday)
--    hour,        hh, h    :        23  (0 ... 23)
--    minute,      mi, n    :        59  (0 ... 59)
--    second,      ss, s    :        50  (0 ... 59)
--    millisecond, ms       :       123  (0 ... 999)
--    microsecond, mcs      :    123456  (0 ... 999999)
--    nanosecond,  ns       : 123456700  (0 ... 999999900)
SELECT dateadd(day, 10, '2012-07-03')
-- returns 2012-07-13
SELECT datediff(day, '2012-07-03', '2012-07-13') 
-- returns 10
SELECT datediff(day, '2012-07-12 23:59', '2012-07-13 00:01') 
-- returns 1, the number of day-boundaries crossed (as in T-SQL)
SELECT datediff(day, '2012-07-13 23:59', '2012-07-13 00:01') 
-- returns 0
SELECT datediff(day, '2012-07-14 23:59', '2012-07-13 00:00')  
-- returns -1
SELECT getdate(), getutcdate() 
-- returns current DateTime in local and UTC/GMT time

FxGqlC: DailyRollingFileAppender in log4j/log4net/log4cxx

Using log4j or one of the ports (like log4net or log4cxx), you can configure the appender to "roll" to a new file every day:
log4j.rootLogger=INFO, logfile

# logfile appender: writes its output to a file that is rolled each midnight.
log4j.appender.logfile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.logfile.File=c:/logs/MyLogFile.log
log4j.appender.logfile.Append=true
log4j.appender.logfile.DatePattern='.'yyyy-MM-dd
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{}{GMT} %X{pid} %X{pname} [%t-%X{tname}] %-5p - %c %m%n


This gives you one log file per day, suffixed with the date, except the last day:

• MyLogFile.log.2012-07-21
• MyLogFile.log.2012-07-22
• MyLogFile.log

When running the query "select distinct $filename from [*.*]" in FxGqlC, you get this list:

MyLogFile.log

MyLogFile.log.2012-07-21

MyLogFile.log.2012-07-22

So, the most recent file is scanned first, because this is the "ascending" order as returned by the operating system.

A workaround for this is to change the query like this: "select distinct $filename from [*.log.2*], [*.log]"

This workaround also applies to the regular RollingFileAppender, based on filesize instead of date.

A future feature will be to sort the files on modification date, by extending the FROM-clause option -fileorder.  Possibly in version 2.3.

Update: the FROM-clause option -fileorder has been extended in v2.3 to allow an order based on modification time.  For more information, have a look at:
https://sites.google.com/site/fxgqlc/home/fxgqlc-manual/changes-in-fxgqlc-2-3

Gource video of FxGqlC 2.2

I refreshed the Gource video of FxGqlC to reflect the latest version:

This time, the video was created on Ubuntu, and it is even simpler then on Windows.  These are the commands that I've run in a terminal window:

• sudo apt-get install gource
• sudo apt-get install ffmpeg
• cd ~/Projects/FxGqlC
  (the GIT-directory where the FxGqlC source code is located)
• gource -s 0.25 -title 'FxGqlC' -i 1000 -o /tmp/fxgqlc-gource.ppm
  (-s 0.25 to speed up to 4 days per second, -i 1000 to prevent fading out of idle items)
• avconv -y -r 25 -f image2pipe -vcodec ppm -i /tmp/fxgqlc-gource.ppm -vcodec wmv1 -r 25 -same_quant /tmp/fxgqlc-gource.wmv
• upload the video /tmp/fxgqlc-gource.wmv to youtube

Initially, I used ffmpeg as on Windows which worked without problems, but I got this warning:

*** THIS PROGRAM IS DEPRECATED ***

This program is only provided for compatibility and will be removed in a future release. Please use avconv instead.

Migrating to "avconv" was no problem, since the exact same parameters could be used with it, except for the parameter -sameq which is replaced by -same_quant.

FxGqlC 2.2 released

A new version of FxGqlC has been released.  There are many improvements, both in terms of performance and capabilities. 

Check it out on: https://sites.google.com/site/fxgqlc/home , and give it a try.

And many more things are in the pipeline, so come back in a few weeks for the next version.

The most important new features are:

• Change the working directory with the USE statement.  Similar to the cd/chdir commands in command prompts. USE [c:\temp]USE [../subdir]USE ['sub directory']

• Added support for variables.  Setting variables in select output (e.g. select )is not yet support
DECLARE @var string
SET @var = 'US' + ' ' + 'Open'
SELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on] WHERE [Tournament] = @var AND [Round] = 'The Final'
• System variable $filename: Returns current filename (without path).  The implementation of this system variable has been changed. Before v2.2, the full filename was returned (same behavior as current system variable $fullfilename).
  SELECT DISTINCT $filename FROM ['SampleFiles\*' -recurse]-- Returns:     AirportCodes.csv     AirportCodes.csv.zip     AirportCodesTwice.zip     CountryList.csv     IP2Country.csv.zip     Tennis-ATP-2011.csv
       AirportCodes2.csv     AirportCodes2.csv.zip

• System variable $fullfilename: Current full filename (with complete absolute path).
  This variable is only valid in the context of a running query.SELECT DISTINCT $fullfilename FROM ['SampleFiles\*' -recurse]-- Returns:     C:\Data\SampleFiles\AirportCodes.csv     C:\Data\SampleFiles\AirportCodes.csv.zip     C:\Data\SampleFiles\AirportCodesTwice.zip     C:\Data\SampleFiles\CountryList.csv     C:\Data\SampleFiles\IP2Country.csv.zip     C:\Data\SampleFiles\Tennis-ATP-2011.csv     C:\Data\SampleFiles\SubFolder\AirportCodes2.csv     C:\Data\SampleFiles\SubFolder\AirportCodes2.csv.zip

• Added FROM-clause options '-Heading=On', '-Heading=OnWithRule' and '-Heading=Off' (default).
  SELECT [Winner] from ['Tennis-ATP-2011.csv' -heading=on]     WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Djokovic N.

  
  

• Added possibility to show column headers in output, using !SET HEADING
  !SET HEADING OFFSELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]     WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Djokovic N.
  
  !SET HEADING ONSELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Winner     Djokovic N.
  !SET HEADING ONWITHRULESELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'
  -- Returns:     Winner     ======     Djokovic N.

• The -Heading option can also be used in the INTO-clause:
  SELECT [Winner]      INTO ['US OPEN Winner.txt' -heading=onwithrule]     FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' and [Round] = 'The Final'
• Added support for VIEWs:
  CREATE VIEW Tennis AS
       SELECT [Tournament], [Winner]
            FROM ['Tennis-ATP-2011.csv' -heading=on]
            WHERE [Round] = 'The final'
  SELECT * FROM Tennis
  DROP VIEW Tennis
• Added support for parameterized VIEWs:
  CREATE VIEW Tennis(@file string, @round string) AS
       SELECT [Tournament], [Winner]
            FROM [@file -heading=on]
            WHERE [Round] = @round
  SELECT * FROM Tennis('Tennis-ATP-2011.csv', 'The final')
  DROP VIEW Tennis
• Added support for count(*) as alternative to count(<expression>):
  SELECT count(*) FROM ['Tennis-ATP-2011.csv' -heading=on]
• Added support for count(distinct <expression>) to count unique values:
  SELECT count(distinct [Tournament]) FROM ['Tennis-ATP-2011.csv' -heading=on] 
• Block comments are now also supported.
  SELECT distinct [Tournament] /* block comment */ FROM ['Tennis-ATP-2011.csv' -heading=on] -- line comment
• Added support for option -columndelimiter in FROM-clause and INTO-clause.  Until now, the tab character "\t" was always used as delimiter, which is still the default. The string specified is unescaped using the RegularExpression syntax (e.g. \t becomes a tab character).SELECT [Date], [Winner]
       INTO ['output.txt' -columndelimiter=';']
       FROM ['Tennis-ATP-2011.csv' -heading=on]
       WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'
  -- Output.txt contains:
       12/09/2011;Djokovic N.
• HAVING-clause: Add a filter that is applied AFTER the GROUP BY aggregation.
  SELECT [Winner], count(*) FROM ['Tennis-ATP-2011.csv' -heading=on] GROUP BY [Winner] HAVING count(*) > 60
  
  
• Added support for "alias" in FROM-clause, which makes it possible to link subquery columns to outer query columns.
  SELECT [Date], [Tournament], [Winner],      (     SELECT count(*)           FROM ['Tennis-ATP-2011.csv' -heading=on] [inner]           WHERE [outer].[Winner] = [inner].[Winner]     )      FROM ['Tennis-ATP-2011.csv' -heading=on] [outer]      WHERE [Round] = 'The Final'

• A startup script file is automatically executed when FxGqlC.exe is started in command mode (-c, -command), in file mode (-gqlfile) or in prompt mode (-p, -prompt).  This can be useful to create regularly used views or variables, or to execute any comand such as USE or SET.  The startup script file path can be configured using the startup option -autoexec <filename>.  When the startup option -autoexec is not present, the default startup script file "autoexec.gql" is searched, first in the current directory and then in the directory where FxGqlC.exe is located. 

Graphically visualize software development with Gource

Gource is a very cool open source visualization tool to get a graphical view on the check-ins of source code in a version control system like GIT, SubVersion, CVS, ...

http://code.google.com/p/gource/

This is a video of the development of FxGqlC 2.x, generated with Gource:



It is very easy to generate a video like this based on your source control system. In the explanation below I used Windows, but all the tools were originally developed for Unix.

• Local snapshot of the source code
  (You can skip this step if you already have a local copy of a source controlled project in GIT, SubVersion,...)
  I started from a local snapshot of the last version of the source code. The FxGqlC source code is hosted on GitHub, a public GIT version control repository. 

• You first need to install the client of the version control system. In this case: the GIT client the GIT client for Windows.
  http://git-scm.com/downloads
• Install to the default location "c:\Program Files" (or "c:\program files (x86)" on 64-bit Windows) 
• Open the GIT-console (bash) from the start menu, and execute these commands to get a local copy of the source code:
  $ mkdir ~/Documents/FxGqlC-Source
  $ cd ~/Documents/FxGqlC-Source
  $ git clone https://github.com/WimObiwan/FxGqlC.git
• Set up Gource and run Gource
• Download gource from http://code.google.com/p/gource/ (I used the Windows build of version 0.38, posted on 2012-04-23)
• Unzip to c:\temp\gource
• Open a command prompt to start Gource:
  cd %userprofile%\Documents\FxGqlC-Source\FxGqlC
  SET PATH=%PATH%;c:\program files\git\bin
  On 64-bit Windows, use instead:
  SET PATH=%PATH%;c:\program files (x86)\git\bin
  c:\temp\gource\gource.exe
• Gource gets the necessary information from the GIT-server, and the video is played.
• To generate the video in WMV format:
• First generate the video in PPM format with this command:
  .\Gource\gource.exe -o fxgqlc-gource.ppm
• Download ffmpeg: http://www.videohelp.com/tools/ffmpeg
• Unzip to c:\temp
• Cd c:\temp
• ffmpeg.exe -y -r 25 -f image2pipe -vcodec ppm -i fxgqlc-gource.ppm -vcodec wmv1 -r 25 -sameq fxgqlc-gource.wmv
• The video fxgqlc-gource.wmv can be played with a media player or uploaded to YouTube.
• On YouTube, you find several Gource videos of big open-source projects:
• Linux: http://youtu.be/ntTpM8hfl_E
• MySQL: http://youtu.be/s98-5rZIVyk
• Python: http://youtu.be/aPk1BqK8zzI
• Perl: http://youtu.be/jl9HVEJl_-w
• PHP: http://youtu.be/jhbzEwxbCfI

Update: have a look at "http://mycomputeradventures.blogspot.be/2012/07/gource-video-of-fxgqlc-22.html" on how to create a gource video on Ubuntu.

FxGqlC: Obtain the minutes that have the most log lines

When you have a (big) log file, and you need to know which minutes generated the most lines, you can use a GQL query like this:
FxGqlC> select top 100 left($line, 16), count(1) into [logfile_linespermin.txt] from [logfile.txt] group by left($line, 16) order by 2 desc
In this example, the first 16 characters of every line contain the date and time up till the minutes.

When the date/time is somewhere in the middle of the line, a regular expression can be used:
FxGqlC> select top 100 matchregex($line, '(\d{4}-\d{2}-\d{2} \d{2}\:\d{2})\:\d{2}'), count(1) into [logfile_linespermin.txt] from [logfile.txt] group by matchregex($line, '(\d{4}-\d{2}-\d{2} \d{2}\:\d{2})\:\d{2}') order by 2 desc

And if you need to get the total number of lines per minute, totaled over all the hours, you can run:
FxGqlC> select matchregex($line, '\d{4}-\d{2}-\d{2} \d{2}\:(\d{2})\:\d{2}'), count(1) into [logfile_linespermin.txt] from [logfile.txt] group by matchregex($line, '\d{4}-\d{2}-\d{2} \d{2}\:(\d{2})\:\d{2}') order by 2 desc

More information on: https://sites.google.com/site/fxgqlc

FxGqlC: Skip the first lines of every file

Sometimes a text files contains "header lines" that you want to skip when scanning the files using FxGqlC.
A new FROM-clause option "-skip=n" has been added to skip the first n lines of the input files.  When the input files are multiple files (e.g. when using a zip file containing multiple files, or when using a wildcard like "*.txt"), the first n lines are skipped in every file. This feature is added in version 2.1.

Example:
select distinct * from ['Tennis-ATP-2011.csv' -skip=1]

More information on: https://sites.google.com/site/fxgqlc

FxGqlC: Define the columns of an input text file using a regular expression

An input text file can be parsed into columns using a regular expression, using the new FROM-clause option " -Columns='<regular expression>' ".  The input line is matched against the regular expression.  If there is no match, the line is ignored. If the line matches, the first match is used, and the columns are filled with the named "capture groups" of the regulare expression.  The syntax to define a capture group in .net regular expressions is: "(?<MyColumnName>MyFilter)".  For example: "(?<Count>\d+)". The column name can then be used in any expression like the WHERE-clause.  This feature is added in version 2.1.

Example:
select distinct [Tournament] from ['Tennis-ATP-2011.csv' -columns='^(?<ATP>.*?)\t(?<Location>.*?)\t(?<Tournament>.*?)\t.*?$']

More information on using regular expressions, can be found on this site: http://www.regular-expressions.info/ .
For the usage of "Capture Groups", see the section "Named Capture with .NET’s System.Text.RegularExpressions" on this link: http://www.regular-expressions.info/named.html

More information on: https://sites.google.com/site/fxgqlc