LiveCode Forums.

Posted: **Thu Nov 07, 2013 5:29 pm**

I have a task to maintain an Access Database and as part of that task I wish to identify and document which data tables are used in each query. The Access documenter is not helpful so I decided to copy and process the SQL of each query extracting each reference to a table made by the query. Fortunately the table names all start with the characters "tbl". I have attached the stack and copied my code below, it all works but I am interested if there is a better way of parsing the data.

Code: Select all

On ExtractTableNames
   // Routine that parses an SQL Query and produces a list of tables used by the query
   // Simon Knight Nov 2013
   put empty into fld "output"
   put empty into tOutput
   put fld"SQL" into tSQL
   //Replace the SQL joining characters with Tab
   replace "(" with tab in tSQL
   replace ")" with tab in tSQL
   replace "." with tab in tSQL
   replace "=" with tab in tSQL
   replace ";" with tab in tSQL
   replace "," with tab in tSQL
   replace " " with tab in tSQL
   
   set itemdelimiter to tab
   --Parse each item checking to see if it starts with "tbl"
      repeat for each item theItem in tSQL
         if theItem begins with "tbl"  AND theItem is not among the words of tOutput then
            put theItem & CR after tOutput
         end if
      end repeat
   
   put tOutput into fld"output"
end ExtractTableNames

Posted: **Thu Nov 07, 2013 6:53 pm**

Hi.

This seems pretty clean to me.

The only thing I can think of to make it faster, since as you say it already works fine, is to load each of the items 'theItem" into an array preloaded with the "tOutput" items. This would add only those items that do not already exist in that array, doing what your "...and the item is not among the words of tOutput" does. But then again, you would no longer have a distinct list of those new items. They would be merged with the original contents. Not sure if this is important or not, or if it is worth tagging the new items in some way to be able to extract them later.

If speed is adequate, I would have done it the way you already have.

Craig Newman

Posted: **Thu Nov 07, 2013 7:53 pm**

You could probably do all the replacements with a single line of code by using replaceText. My regex is weak, but it would be something like this:

Code: Select all

get replaceText(tSQL,"\(|\)|\.|=|;|,",tab)

You might not need the backslashes before the parentheses marks, I forget. Thierry would know, he breathes regex. Then I might do something like this:

Code: Select all

split tSQL by tab as set
put the keys of tSQL into tKeys
filter tKeys with "tbl*"

That should give you a list.

Posted: **Thu Nov 07, 2013 8:04 pm**

jacque wrote: You could probably do all the replacements with a single line of code by using replaceText. My regex is weak, but it would be something like this:

Thierry would know, he breathes regex.
.

LOL
I'm sorry , I fall into a regex pot when I was young

Ok, here is one quick one not tested:

Code: Select all


put ExtractTableNames( fld "SQL") into fld "output"


function ExtractTableNames tSQL
   local tList
   repeat while matchChunk( tSQL, "(?msi)(\btbl\w+)", pStart, pEnd )
      add 1 to tList[char pStart to pEnd of tSQL]
      delete char 1 to pEnd of tSQL
   end repeat
   return the keys of tList
end ExtractTableNames

Thierry

Posted: **Thu Nov 07, 2013 9:07 pm**

Wow. I'll have to look up all that, Thierry. That's amazing.

Probably you should remove the "then" at the end of the line though.

Posted: **Thu Nov 07, 2013 9:27 pm**

jacque wrote:Wow. I'll have to look up all that, Thierry. That's amazing.
Probably you should remove the "then" at the end of the line though.

Oups. typo!
I did remove it, thanks.

Posted: **Fri Nov 08, 2013 7:47 am**

jacque wrote:Wow. I'll have to look up all that, Thierry. That's amazing.

Jacque,

One of the best place to learn *peacefully* how regular expression works:

http://www.regular-expressions.info/tutorial.html

Regards,

Thierry

Posted: **Fri Nov 08, 2013 4:20 pm**

Thanks for all your comments. I have tried the code written by Thierry and it works a treat so I am off to the tutorial to find out how.

Posted: **Fri Nov 08, 2013 6:37 pm**

Simon Knight wrote:I have tried the code written by Thierry and it works a treat so I am off to the tutorial to find out how.

Hi Simon, Jacque and \w+,

This might be interesting to you:

http://forums.runrev.com/viewtopic.php?f=11&t=7462

somehow a very similar approach for a different goal..

Regards,

Thierry

LiveCode Forums.

Extracting sub strings from a string

Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string

Re: Extracting sub strings from a string