[Gambas-user] PostgreSQL multiple column problem

[Fabien Bodard]

Have you try your sql request in the gambas3 IDE database manager?
Le 18 oct. 2012 02:54, "Markus Schatten" <markus.schatten at ...2005...> a écrit :

> 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
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_sfd2d_oct
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>