>>>Why?
>>
>>There may be functions (with arguments separated by commas) in the select.
>>Counting the number of commas would not give the right result
>>
>>
>>select left(field,2) as xx
>>
>
>After I read your message I started to wonder why?
>And JUST wanted to update my question with "unless you are using functions" :-)))))
>But I didn't drink my coffee yet :-)
On second thought, the commas are the way to go
In the absence of a lexical parser, the code below is a start - I did not test a lot
Given a valid sql statement, it gives for each expression in the select
- the Index
- the length
- the expression (without the AS name)
- the AS name if there was one
The algorithm
- copy the stmt
- mask all strings (ie "abc" becomes ~~~~~)
- mask all matching parentheses (ie LEFT(abc, 4) becomes LEFT~~~~~~~)
Once we are here there are no commas left in the selected expressions, so we can safely split on the comma
- take the part between SELECT and FROM
- split it - separator = space + comma + space
- foreach part
take off the AS clause if there is one
local obj, Colobj
assert SqlStatement_Object(@m.obj)
assert m.obj.GetSelectFields(@m.Colobj, "SELECT aField+1, bField Field2, [, ]+PADR(cField,2) AS cField from ppp")
for each obj in Colobj foxobject
?obj.Index, obj.Length, obj.name, obj.Expr
endfor
&& SqlStatement
&& (c) Gregory Adam 2010
*===============================================================================
#include "FoxPro.h"
*===============================================================================
#define true .t.
#define false .f.
#define IS_RUNTIME (empty(Version(2)))
*===============================================================================
#define LIGHTWEIGHT_CLASS [_LightWeightClass_]
#define SQLSTATEMENT_CLASS [_SqlStatementClass_]
*===============================================================================
*_______________________________________________________________________________
function SqlStatement_Object(obj)
return Object_Create(@m.obj, SQLSTATEMENT_CLASS)
endfunc
*_______________________________________________________________________________
function RegEx_Object(obj, Global, IgnoreCase, MultiLine, Pattern )
local success
success = true
do case
case !m.success
case !Object_Create(@m.obj, 'VBScript.RegExp')
assert false
success = false
otherwise
try
obj.Global = m.Global
obj.IgnoreCase = m.Ignorecase
obj.MultiLine = m.MultiLine
if( IsChar(m.Pattern) and !empty(m.Pattern) )
obj.Pattern = m.Pattern
endif
catch
assert false
success = false
endtry
endcase
return m.success
endfunc
*_______________________________________________________________________________
function Object_Create(obj, ClassName, p01, p02, p03, p04, p05, p06, p07, p08)
return Object_Create_np( @m.obj, ;
m.ClassName, ;
pcount()-2, ;
m.p01, m.p02, m.p03, m.p04, m.p05, m.p06, m.p07, m.p08 ;
)
endfunc
*_______________________________________________________________________________
function Object_Create_np(obj, ClassName, np, p01, p02, p03, p04, p05, p06, p07, p08)
local success
success = true
obj = null
try
do case
case inlist(m.np, 0)
obj = CreateObject(m.ClassName)
case inlist(m.np, 1)
obj = CreateObject(m.ClassName, m.p01)
case inlist(m.np, 2)
obj = CreateObject(m.ClassName, m.p01, m.p02)
case inlist(m.np, 3)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03)
case inlist(m.np, 4)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03, m.p04)
case inlist(m.np, 5)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03, m.p04, m.p05)
case inlist(m.np, 6)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03, m.p04, m.p05, m.p06)
case inlist(m.np, 7)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03, m.p04, m.p05, m.p06, m.p07)
case inlist(m.np, 8)
obj = CreateObject(m.ClassName, m.p01, m.p02, m.p03, m.p04, m.p05, m.p06, m.p07, m.p08)
case m.np < 0
assert false
success = false
otherwise
assert false
success = false
endcase
catch
if( !IS_RUNTIME )
=aerror(pp)
endif
assert false
success = false
endtry
do case
case !m.success
case !inlist(vartype(m.obj), T_OBJECT)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
function IsObject(obj, BaseClassName)
if( pcount() == 1)
return inlist(vartype(m.obj), T_OBJECT)
endif
return inlist(vartype(m.obj), T_OBJECT) ;
and pemstatus(m.obj, 'BaseClass', 5) ;
and ( proper(m.obj.BaseClass) == proper(m.BaseClassName) )
endfunc
*_______________________________________________________________________________
function IsLogical(s)
return inlist(vartype(m.s), T_LOGICAL)
endfunc
*_______________________________________________________________________________
function IsChar(s)
return inlist(vartype(m.s), T_CHARACTER, T_MEMO)
endfunc
*_______________________________________________________________________________
*===============================================================================
*===============================================================================
*===============================================================================
define class SQLSTATEMENT_CLASS as LIGHTWEIGHT_CLASS
RegExLeft = null
RegExRight = null
RegExSelect = null
RegExFrom = null
RegExComma = null
RegExAs = null
*_______________________________________________________________________________
&& RegEx_Object(obj, Global, IgnoreCase, MultiLine, Pattern )
protected function Init()
local success
success = true
local obj
do case
case !m.success
case !DoDefault()
assert false
success = false
case !RegEx_Object(@m.obj, false, false, true)
assert false
success = false
case !AddProperty(m.this, 'RegExLeft', m.obj)
assert false
success = false
case !RegEx_Object(@m.obj, false, false, true)
assert false
success = false
case !AddProperty(m.this, 'RegExRight', m.obj)
assert false
success = false
&& ignore case
case !RegEx_Object(@m.obj, false, true, true, '^\s*select\s+')
assert false
success = false
case !AddProperty(m.this, 'RegExSelect', m.obj)
assert false
success = false
case !RegEx_Object(@m.obj, false, true, true, '\s*\bfrom\b')
assert false
success = false
case !AddProperty(m.this, 'RegExFrom', m.obj)
assert false
success = false
case !RegEx_Object(@m.obj, true, false, true, '\s*,\s*')
assert false
success = false
case !AddProperty(m.this, 'RegExComma', m.obj)
assert false
success = false
case !RegEx_Object(@m.obj, false, true, true, '(\s+as)?(\s+)(\w+)$')
assert false
success = false
case !AddProperty(m.this, 'RegExAs', m.obj)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function Destroy()
store null to ;
this.RegExLeft, ;
this.RegExRight, ;
this.RegExSelect, ;
this.RegExFrom, ;
this.RegExComma, ;
this.RegExAs
return DoDefault()
endfunc
*_______________________________________________________________________________
function GetSelectFields(collectionObj, sqlstmt)
local success
success = true
local s
s = m.sqlstmt
do case
case !m.success
case !IsChar(m.s)
assert false
success = false
case !m.this.GetSelectFields_Mask(@m.s)
assert false
success = false
case !Collection_Object(@m.collectionObj)
assert false
success = false
case !m.this.GetSelectFields_GetPositions(m.collectionObj, m.s)
assert false
success = false
case !m.this.GetSelectFields_SetValues(m.collectionObj, m.sqlstmt)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_Mask(s)
local success
success = true
do case
case !m.success
case !m.this.GetSelectFields_Mask_Strings(@m.s)
assert false
success = false
case !m.this.GetSelectFields_Mask_Parentheses(@m.s)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_Mask_Strings(s)
local success
success = true
local colObj
do case
case !m.success
case !Collection_Object(@m.colObj)
assert false
success = false
case !m.ColObj.Add(["], ["])
assert false
success = false
case !m.ColObj.Add(['], ['])
assert false
success = false
case !m.ColObj.Add('\]', '[')
assert false
success = false
&& this wont work for strings like [[abc]
case !m.this.Mask_One( @m.s, '[' + [['"] + ']', m.colObj)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_Mask_Parentheses(s)
local success
success = true
local colObj
do case
case !m.success
case !Collection_Object(@m.colObj)
assert false
success = false
case !m.ColObj.Add('\)', '(')
assert false
success = false
case !m.this.Mask_One( @m.s, '\(', m.colObj)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
#if false
mask : eg xx + "abc" becomes xx + ~~~~~
#endif
protected function Mask_One(s, pattern, colObj)
local success
success = true
local regExLeft, regExRight
regExLeft = m.this.RegExLeft
regExRight = m.this.RegExRight
regExLeft.Pattern = m.pattern
do case
case !m.success
otherwise
local patternRight
local matchesLeft, offsetLeft, lengthLeft
local matchesRight, offsetRight, lengthRight
local totalLength
do while m.success
matchesLeft = m.regExLeft.Execute(m.s)
do case
case empty(m.matchesLeft.count)
exit
case !colObj.TryGetValue(@m.patternRight, m.matchesLeft.item[0].Value)
assert false message 'bad expression'
success = false
otherwise
offsetLeft = m.matchesLeft.item[0].FirstIndex+1
lengthLeft = m.matchesLeft.item[0].Length
RegExRight.Pattern = m.patternRight
matchesRight = m.regExRight.Execute(substr(m.s, m.offsetLeft + m.lengthLeft ))
do case
case empty(m.matchesRight.count)
assert false message 'bad expression'
success = false
otherwise
offsetRight = m.matchesRight.item[0].FirstIndex+1
lengthRight = m.matchesRight.item[0].Length
totalLength = m.lengthLeft + m.lengthRight + m.offsetRight - 1
s = stuff(m.s, m.offsetLeft, m.totalLength, repl('~', m.totalLength))
endcase
endcase
enddo
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_GetPositions(collectionObj, s)
local success
success = true
local matches
local fieldsIndex
do case
case !m.success
&& (1) get the leading select
otherwise
matches = m.this.RegExSelect.Execute(m.s)
do case
case matches.Count <> 1
assert false message 'bad expression'
success = false
otherwise
fieldsIndex = m.matches.item[0].FirstIndex + m.matches.item[0].Length + 1
s = substr(m.s, m.fieldsIndex)
endcase
endcase
&& (2) locate trailing from
do case
case !m.success
otherwise
matches = m.this.RegExFrom.Execute(m.s)
do case
case matches.Count <> 1
assert false message 'bad expression'
success = false
otherwise
s = left(m.s, m.matches.item[0].FirstIndex)
endcase
endcase
&& s contains only the fieldclauses separated by commas
do case
case !m.success
otherwise
matches = m.this.RegExComma.Execute(m.s + ',')
local match, i, prevoffset, offset, obj
prevoffset= 0
for i = 0 to matches.count - 1
match = m.matches.item[m.i]
do case
case !m.success
exit
case !m.this.FieldExpression_Object(@m.obj)
assert false
success = false
case !m.collectionObj.Add(m.obj)
assert false
success = false
otherwise
obj.Index = m.fieldsIndex + prevoffset
offset = m.match.FirstIndex
obj.Length = m.offset - m.prevoffset
prevoffset = m.offset + m.match.Length
endcase
endfor
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_SetValues(collectionObj, stmt)
local success
success = true
do case
case !m.success
otherwise
for each obj in collectionObj foxobject
do case
case !m.success
exit
case !m.this.GetSelectFields_SetValues_One(m.obj, m.stmt)
assert false
success = false
endcase
endfor
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function GetSelectFields_SetValues_One(obj, stmt)
local success
success = true
local matches
do case
case !m.success
otherwise
s = substr(m.stmt, m.obj.Index, m.obj.Length)
matches = m.this.RegExAs.Execute(m.s)
endcase
do case
case empty(m.matches.count)
obj.Expr = m.s
case m.matches.count == 1
obj.Length = m.obj.Length - m.matches.item[0].Length
obj.Expr= left(m.s, m.obj.Length)
obj.name = m.matches.item[0].SubMatches[2]
otherwise
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
protected function FieldExpression_Object(obj)
local success
success = true
do case
case !m.success
case !Object_Create(@m.obj, 'Empty')
assert false
success = false
case !AddProperty(m.obj, 'Index', null)
assert false
success = false
case !AddProperty(m.obj, 'Length', null)
assert false
success = false
case !AddProperty(m.obj, 'Expr', null)
assert false
success = false
case !AddProperty(m.obj, 'Name', null)
assert false
success = false
endcase
return m.success
endfunc
*_______________________________________________________________________________
enddefine
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
define class LIGHTWEIGHT_CLASS as Relation
hidden ChildAlias, ChildOrder, ClassLibrary, ;
Comment, OneToMany, ;
Parent, ParentAlias, RelationalExpr, Tag
Name = LIGHTWEIGHT_CLASS
DataSessionId = 0
*_______________________________________________________________________________
*_______________________________________________________________________________
protected function Init()
this.DataSessionId = set('dataSession')
return DoDefault()
endfunc
*_______________________________________________________________________________
protected function Message_Error(msg)
=MessageBox(m.msg, MB_ICONSTOP, m.this.Class)
endfunc
*_______________________________________________________________________________
protected function Destroy()
return DoDefault()
endfunc
*_______________________________________________________________________________
function Release()
release (m.this)
endfunc
*_______________________________________________________________________________
enddefine
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
*===============================================================================
#define COLLECTION_CLASS [_CollectionClass_]
*================================================================================
*================================================================================
*================================================================================
*
function Collection_Object(obj)
return Object_Create(@m.obj, COLLECTION_CLASS)
endfunc
*
#if false
- properties
- Keyed returns true if the items have a key
- methods
- Clear() && clears the collection
- Empty() && return true if the collection is empty
- Add(item [, key])
&& overrides base class
&& returns true if successful add
&& returns false if any error
- TryGetValue(sValue, ItemOrKey)
- ContainsKey(Key)
#endif
*================================================================================
*================================================================================
*================================================================================
define class COLLECTION_CLASS as Collection
DataSessionId = 0
&& access method
Keyed = false
*
protected function init()
local success
success = true
do case
case !m.success
case !DoDefault()
assert false
success = false
case !AddProperty(m.this, 'DataSessionId', set('dataSession'))
assert false
success = false
endcase
return m.success
endfunc
*
protected function Destroy()
return DoDefault()
endfunc
*
function Clear()
=m.this.Remove(-1)
endfunc
*
function Empty()
return Empty(m.this.Count)
endfunc
*
function add(sValue, sKey)
NODEFAULT
local success
success = true
do case
case pcount() = 1
try
if( !DoDefault(m.sValue) )
assert false
success = false
endif
catch
assert false
success = false
endtry
otherwise
try
if( !DoDefault(m.sValue, m.sKey) )
assert false
success = false
endif
catch
assert false
success = false
endtry
endcase
return m.success
endfunc
*
function Keyed_Access()
return !( empty(m.this.Count) or empty(m.this.getKey(1)) )
endfunc
*
function TryGetValue(sValue, ItemOrKey)
local success
success = true
try
sValue = m.this.Item(m.ItemOrKey)
catch
&& assert false
success = false
endtry
return m.success
endfunc
*
function ContainsKey(Key)
local success
success = true
do case
case !m.success
case !IsChar(m.Key)
assert false
success = false
otherwise
try
=m.this.Item[m.key]
catch
success = false
endtry
endcase
return m.success
endfunc
*
enddefine
*================================================================================
*================================================================================
*================================================================================
Gregory
