Have you try your sql request in the gambas3 IDE database manager?
Le 18 oct. 2012 02:54, "Markus Schatten" <markus.schat...@foi.hr> 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 <ihayw...@iinet.net.au>
> 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@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>
------------------------------------------------------------------------------
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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
