[Gambas-user] PostgreSQL multiple column problem

[Markus Schatten]

Dear Fabien and Ian,

thank you for your reply. I was kind of hoping that there is a way to
index the results. I started of to write a query rewriter, but got
soon into trouble with lots of possible ways of writing a query. I
would probably need a grammar parser to do that ;-)

Anyways, after looking up the SELECT syntax, I found another way. I
wasn't aware of the WITH clause in a select statement which allows you
(at least in PostgreSQL) to create an alias for a subquery with
specified column names. I used this to create a rewrite. The idea is
to construct a query like:

WITH query(a0, a1) AS (SELECT * FROM test t1, test t2) SELECT * FROM query

which replaces the initial column names with a0 and a1. Afterwards I
put the original column names into the TableView header (datasource +
dataview doesn't work since the table attribute doesn't seem to accept
WITH queries - is there a reason for that, or is that a bug?). Here is
the solution if anyone might need it:


Public con As New Connection
Public original As New String[]

Public Sub _new()
  With con
    .Type = "postgresql"
    .Name = "db"
    .User = "markus"
    .Password = "secret"
    .Open
  End With
  execute(rewrite_query("SELECT * FROM test t1, test t2"))
End

Public Sub execute(query As String)
  ' execute the query and show the results
  Dim res As Result
  Dim rfield As ResultField
  Dim col_counter, row_counter, i As Integer
  Dim context As New Collection
  res = con.Exec(query)
  TableView1.Rows.Count = res.Count
  TableView1.Columns.Count = res.Fields.Count
  For i = 0 To original.Max
    TableView1.Columns[i].Title = original[i]
  Next
  row_counter = 0
  For Each res
    col_counter = 0
    context["res"] = res
    TableView1.Row = row_counter
    For Each rfield In res.Fields
     TableView1.Column = col_counter
      TableView1.Current.Text = Eval("res!" & rfield.Name, context)
      col_counter += 1
    Next
    row_counter += 1
  Next
End


Public Function find_all(subj As String, pattern As String, Optional
submatchindex As Integer = 0) As String[]
  ' findall matches of a given regex in a given string, return only
submatches if submatchindex is specified
  Dim re As Regexp
  Dim matches As New String[]
  re = New Regexp(subj, pattern)

  Do While re.offset >= 0 And subj <> ""
    If submatchindex = 0 Then
      matches.push(re.Text)
    Else
      matches.push(re.SubMatches[submatchindex].Text)
    End If
    If Len(subj) > Len(re.text) Then
      subj = Mid(subj, re.offset + Len(re.text) + 1)
    Else
      subj = ""
    End If
      If subj <> "" Then re.exec(subj)
  Loop
  Return matches
End

Public Function rewrite_query(query As String) As String
  ' rewrite the query to return distinct column names
  Dim has_asterisk As String[]
  Dim defined_limit As String[]
  Dim replace_limit As String
  Dim query_copy As String
  Dim res As Result
  Dim rfield As ResultField
  Dim counter As Collection
  Dim with_string As String
  Dim i As Integer
  has_asterisk = find_all(query, "(?i)select (.*?[\\*].*?) from", 1)
  If has_asterisk.Count > 0 Then
    query_copy = query
    defined_limit = find_all(query, "(?i)(limit +[0-9]+)", 1)
    If defined_limit.Count > 0 Then
      For Each replace_limit In defined_limit
        query = Replace(query, replace_limit, "LIMIT 1")
      Next
    Else
      query &= " LIMIT 1"
    Endif
    res = con.Exec(query)
    For Each res
      counter = New Collection
      For Each rfield In res.Fields
        original.Push(rfield.name)
      Next
    Next
    with_string = "WITH query("
    For i = 0 To original.Max
      with_string &= "a" & Str(i) & ", "
    Next
    with_string = Left$(with_string, -2) & ") AS (" & query_copy & ")
SELECT * FROM query"
    Print with_string
    Return with_string
  Else
    Return query
  Endif
End

On Wed, Oct 17, 2012 at 10:33 AM, Ian Haywood <ihaywood at ...1979...> wrote:
> No, I think its your best solution
> Maybe your users can just type in the "where" part of the query and
> then you add the rest?

The app is a database development environment, so users have to enter
the whole query. I will try to find solutions for other dbms' (I'm
planning to support SQLite and MySQL as well) so if anyone has another
idea on how to approach the matter, I'll be happy to see a solution.

All the best,

M.

-- 
Markus Schatten, PhD
Assistant professor
University of Zagreb
Faculty of Organization and Informatics
Pavlinska 2, 42000 Varazdin, Croatia
http://www.foi.hr/nastavnici/schatten.markus/index.html
http://www.researchgate.net/profile/Markus_Schatten1