-
Notifications
You must be signed in to change notification settings - Fork 4
/
sqlyoga.livecodescript
11313 lines (9041 loc) · 396 KB
/
sqlyoga.livecodescript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
script "libSQLYoga"
constant kVersion = "2.0.0"
constant kBuildNumber = "2"
constant kBuildProfile = "development"
constant kExpirationDate = ""
--> Defines (old)
constant kErrInvalidArray = 422
constant kErrInvalidBoolean = 452
constant kErrInvalidNumber = 453
constant kErrPropDoesntExist = 456
constant kErrInvalidInteger = 354
constant kErrInvalidPoint = 355
constant kErrInvalidRect = 356
constant kErrReadOnlyProp = 449
constant kErrInvalidProperty = 348
constant kErrCantFindObject = 619
constant kErrRenameErrorInDestination = 487
constant kErrObjectNameTaken = 132 ##create: error in name expression
constant kErrInvalidGroupObject = 238
constant kErrErrorInFunction = 219
local sFieldGetProps = "notes,database,database schema,table,type,limit,scale,precision,fixed width,accepts null,signed,has default value,default value,meta type"
local sFieldSetProps = "notes,name,type,limit,scale,precision,fixed width,accepts null,unique,signed,has default value,default value"
local sTableGetProps = "notes,database,database schema,relationships,fields,field names,indexes,index names,data,primary key fields,primary key field,primary key,sql queries"
local sTableSetProps = "notes,primary key,name"
local sRelationGetProps = "notes,database,database schema,type,table 1,table 1 name,table 1 key,table 2,table 2 name,table 2 key,link table,link table name,link table to table 1 key,link table to table 2 key,table 1 update action,table 1 delete action,table 2 update action,table 2 delete action,update action,delete action"
local sRelationSetProps = "notes,type,table 1,table 1 key,table 2,table 2 key,link table,link table to table 1 key,link table to table 2 key,table 1 update action,table 1 delete action,table 2 update action,table 2 delete action,update action,delete action"
local sDatabaseProps = "notes"
local sConnectionProps = "notes"
local sIndexGetProps = "notes,database,database schema,table,fields,field names,style,type"
local sIndexSetProps = "notes,fields,style,type"
local sValidationProps = ""
--> Defines (New)
constant kSupportedDatabase = "sqlite,mysql,valentina,postgresql"
constant kFieldReference = "field reference"
constant kTableReference = "table reference"
constant kConnectionReference = "connection reference"
constant kQueryTemplateReference = "query template reference"
constant kQueryReference = "query reference"
constant kRelationshipReference = "table relationship reference"
constant kIndexReference = "table index reference"
constant kSchemaMigrationReference = "schema migration reference"
constant kScopeReference = "scope reference"
constant kJoinTypes = "inner,left,left outer,right outer,full outer,self,cross" # left is a synonym for left outer
constant kDefaultJoinType = "inner"
constant kDatabaseKeyNotFound = "database key %s%s%s was not found"
constant kInvalidType = "invalid array type for"
constant kInvalidCursor = "invalid database cursor"
constant kErrorSQLExecution = "sqlyoga_executesql_err"
constant kErrorSQLConnection = "sqlyoga_connection_err"
local sDbsA
local sConnectionsA
local sSQLQuerySetProps = "database,connection,table,table as,select,select clause,distinct,find,full column names,column names length,column names,joins,related table joins,conditions,condition bindings,sort by,order by,report null,column delimiter,row delimiter,limit,offset,group by,having"
local sSQLQueryGetProps = "tables,database type,number of records,query is at end,query is at start,joins including scopes,related table joins including scopes,from clause,join clause,where clause,query"
constant kBoolTrueValues = "true,1,y,yes,t"
constant kBoolFalseValues = "false,0,n,no,f"
constant kDontQuoteTypes = "decimal,integer,sequence"
constant kTempConnProps = "username,password,log file,log field,log callback,last executed sql,version,database name,host,port"
local sDefaultDBKey = "default"
--> Version
/**
Summary: Returns the version of the SQLite library.
Description:
Returns a string in a format similar to "1.0.0 build 1".
Returns: String
*/
function sqlyoga_version
return kVersion && "build" && kBuildNumber
end sqlyoga_version
--> Messages (Engine)
on libraryStack
if the target is not me then pass libraryStack
end libraryStack
on releaseStack
if the target is not me then pass releaseStack
end releaseStack
--> Database Utilities
/**
Summary: Converts any number of key:value pairs into an array that is added to as a new element to another array.
Params:
xArrayA: The array to add an element to. A new key equal to the number of elements of xArrayA + 1 will be added.
pKey: Key of xArrayA to store key/values in.
pKeyValue: Key/Value pairs in the format key:value. Each key value passed in will be added to the new key of pArray.
Description:
Helper command for populating arrays.
Example:
put empty into tFieldsA
sqlyoga_addToArray tFieldsA, "name:id", "type:sequence"
Returns: empty
*/
command sqlyoga_addToArray @xArrayA, pKey, pKeyValue
local theIndex, theKey
if pKey is not an array and pKey is empty then
put the number of elements of xArrayA + 1 into theIndex
set the itemDelimiter to ":"
repeat with i = 3 to the paramCount
put item 1 of param(i) into theKey
put item 2 to -1 of param(i) into xArrayA[theIndex][theKey]
end repeat
else
put the number of elements of xArrayA[pKey] + 1 into theIndex
set the itemDelimiter to ":"
repeat with i = 3 to the paramCount
put item 1 of param(i) into theKey
put item 2 to -1 of param(i) into xArrayA[pKey][theIndex][theKey]
end repeat
end if
return empty
end sqlyoga_addToArray
/**
Summary: Converts the parameters to an array.
Description:
Each parameter is a key:value pair.
Example:
put sqlyoga_array("id:1", "label:My Record") into tDataA
Returns: Array
*/
function sqlyoga_array
local tKey, tArray
set the itemDelimiter to ":"
repeat with i = 1 to the paramCount
put item 1 of param(i) into tKey
put item 2 to -1 of param(i) into tArray[tKey]
end repeat
return tArray
end sqlyoga_array
/**
Summary: Adds a new element onto the end of an array.
Params:
xArrayA: The array to add an element to. A new key equal to the number of elements of xArrayA + 1 will be added.
pValue: Variable to add to array.
Description:
Helper command for populating arrays.
Example:
put "a value" into theFieldsA[1]
sqlyoga_arrayPush theFieldsA, "another value" -- added to theFieldsA[2]
Returns: empty
*/
command sqlyoga_arrayPush @xArrayA, pValue
local theIndex
put the number of elements of xArrayA + 1 into theIndex
put pValue into xArrayA[theIndex]
return empty
end sqlyoga_arrayPush
/**
Summary: Returns a return delimited list of all Database object names.
Returns: Line delimited list of database keys
*/
function sqlyoga_getDatabases
local theDatabases
put the keys of sDbsA into theDatabases
sort theDatabases
return theDatabases
end sqlyoga_getDatabases
/**
Summary: Sets the default Database object to use with the SQL Yoga API calls.
Params:
pDBKey: The name of the Database object to set as the default.
Description:
By default the SQL Yoga API calls assume you are acting on the Database object named "default".
You can use this command to specify a different Database object name to use as the default.
Returns: empty
*/
command sqlyoga_setDefaultDatabase pDBKey
if pDBKey is not "default" then
_ValidateDBKeyExistence pDBKey
end if
put pDBKey into sDefaultDBKey
return empty
end sqlyoga_setDefaultDatabase
/**
Summary: Returns the name of the Database object used by default in SQL Yoga API calls.
Returns: Name of default database key.
*/
function sqlyoga_getDefaultDatabase
return sDefaultDBKey
end sqlyoga_getDefaultDatabase
/**
Summary: Parses a search string and converts it into an array of it's component parts.
Params:
pString: A string representing the terms a user wants to search for.
pDefaultOperator: Default boolean operator to use between words that do not explicity state one. Valid values are `AND` or `OR`. Default value is `AND`.
Description:
Search interfaces often allow a user to enter a search string for querying the database.
A string entered by the user as`tacos pizza` might look like this in a `WHERE` clause:
```
field = 'tacos' AND field='pizza'
```
or
```
field='tacos' OR field='pizza'
```
A search string such as `"cheese pizza" tacos` might look like this in a `WHERE` clause:
```
field = 'cheese pizza' OR field='tacos'
```
This function splits up the string into it's component parts and stores them in a numerically
indexed array. This array can be used to generate conditions for a SQL Query object.
For example, it can be passed as a parameter to `sqlquery_addScope`.
The string `"cheese pizza" tacos` would be parsed and converted to an array that looks
like this (assuming `OR` was the default operator):
```
[1]["string"] = cheese pizza
[1]["operator"] = OR
[2]["string"] = tacos
[2]["operator"] = OR
```
Returns: Numerically indexed array. Each index has two keys: `operator` and `string`.
*/
function sqlyoga_splitUserSearchString pString, pDefaultOperator
return _ParseUserSearchString(pString, pDefaultOperator)
end sqlyoga_splitUserSearchString
/**
Summary: Escapses a string using the appropriate characters for the specified database type.
Params:
pDbType: The type of database to escape for (mysql, postgresql, sqlite, valentina, sql server)
pString: The string to escape.
Returns: Escape string
*/
function sqlyoga_escapeString pDbType, pString
_EscapeDbStringByRef pDbType, pString
return pString
end sqlyoga_escapeString
/**
Summary: Returns the query used to retrieve the id of the last record that was inserted into the database.
Returns: SQL query
*/
function sqlyoga_lastInsertIDQuery pDbType, pConnectionA
local theDo, theSQL
replace space with empty in pDbType
put "put _" & pDbType & "_lastInsertIDSQL(pConnectionA) into theSQL" into theDo
do theDO
return theSQL
end sqlyoga_lastInsertIDQuery
--> String Parsers (Private)
private function _StripDBObjectQuotes pString
local theCharsToStrip
put quote & "'" & "[" & "]" into theCharsToStrip
repeat until char 1 of pString is not in theCharsToStrip
delete char 1 of pString
end repeat
repeat until the last char of pString is not in theCharsToStrip
delete the last char of pString
end repeat
return pString
end _StripDBObjectQuotes
## break into array with value, as
private function _ParseSelectClause pString
-----
local ignoringInput
local theAsOffset
local theChar, theCharNo
local theStartEscapeChars, theEndEscapeChars
local theCharsFollowingOperators
local theCharsPrecedingOperators
local theEscapeChars
local theFieldsA
local theProcessedCharCount
local theString
-----
put false into ignoringInput
put 0 into theProcessedCharCount
put 0 into theAsOffset
put empty into theString
put empty into theFieldsA
## todo: customize for individual databases
put "'(" & quote into theStartEscapeChars
put "')" & quote into theEndEscapeChars
put " ')" & quote into theCharsPrecedingOperators
put " '(" & quote into theCharsFollowingOperators
repeat with theCharNo = 1 to the number of chars of pString
put char theCharNo of pString into theChar
## Look for escape chars
if ignoringInput then
if theChar is "\" then
## Add to string and then skip next char
add 1 to theCharNo
put theChar & char theCharNo of pString after theString
next repeat
end if
end if
if ignoringInput then
## Should we stop ignoring?
if theChar is in theEndEscapeChars then
put false into ignoringInput
put theChar after theString
next repeat
end if
else
## Should we start ignoring?
if theChar is in theStartEscapeChars then
put true into ignoringInput
put theChar after theString
next repeat
end if
end if
## Process char
if not ignoringInput then
## Find AS
if char theCharNo to (theCharNo + 3) of pString is " AS " then
put theCharNo - theProcessedCharCount into theAsOffset
end if
switch theChar
case comma
## Process chunk
_ProcessSelectClauseChunk theFieldsA, theString, theAsOffset
put empty into theString
put 0 into theAsOffset
put theCharNo into theProcessedCharCount
break
default
put theChar after theString
end switch
else
put theChar after theString
end if
end repeat
if theString is not empty then _ProcessSelectClauseChunk theFieldsA, theString, theAsOffset
return theFieldsA
end _ParseSelectClause
private command _ProcessSelectClauseChunk @xFieldA, pString, pAsOffset
local theAs, theValue, theNextIndex
## Process chunk
if pAsOffset > 0 then
put char (pAsOffset + 4) to -1 of pString into theAs
put char 1 to (pAsOffset - 1) of pString into theValue
else
put pString into theValue
end if
put the number of elements of xFieldA + 1 into theNextIndex
put word 1 to -1 of theValue into xFieldA[theNextIndex]["value"]
put word 1 to -1 of theAs into xFieldA[theNextIndex]["as"]
return empty
end _ProcessSelectClauseChunk
private function _ConvertSelectClauseToSelectClauseFieldIndex pKey, pSelectClause, pTables
-----
local i
local theTable, theTables, theFieldName, theFieldNames
local theFieldsA
local theNextIndex
local theSelectClauseA
local theTableIndex, theTableIndexes
-----
put _ParseSelectClause(pSelectClause) into theSelectClauseA
repeat with i = 1 to the number of elements of theSelectClauseA
put empty into theTableIndex
put _StripDBObjectQuotes(theSelectClauseA[i]["value"]) into theSelectClauseA[i]["value"]
set the itemDelimiter to "."
if the number of items of theSelectClauseA[i]["value"] > 1 then
put item 1 of theSelectClauseA[i]["value"] into theTable
put item 2 of theSelectClauseA[i]["value"] into theFieldName
else
put theSelectClauseA[i]["value"] into theFieldName
end if
set the itemDelimiter to comma
## Now lookup table index if need be
if theTable is empty then
put pTables into theTables
replace comma with cr in theTables
else
put theTable into theTables
end if
if theFieldName is "*" then
repeat for each line theTable in theTables
put the keys of sDbsA[ pKey ]["schema"]["tables"][ theTable ]["field order"] into theFieldNames
repeat for each line theFieldName in theFieldNames
put the number of elements of theFieldsA[theTableIndex] + 1 into theNextIndex
put theFieldName into theFieldsA[theTable][theNextIndex]["field name"]
put empty into theFieldsA[theTable][theNextIndex]["as"]
end repeat
end repeat
else
put _FindFirstInstanceOfFieldInTables(pKey, theTables, theFieldName) into theTable
if theTable is not empty then
put the number of elements of theFieldsA[theTableIndex] + 1 into theNextIndex
put theFieldName into theFieldsA[theTable][theNextIndex]["field name"]
put theSelectClauseA[i]["as"] into theFieldsA[theTableIndex][theNextIndex]["as"]
else
sqlyoga__throwError kErrPropDoesntExist, "field '" & theFieldName & "' was not found in any tables in the query template"
end if
end if
end repeat
return theFieldsA
end _ConvertSelectClauseToSelectClauseFieldIndex
private function _ParseUserSearchString pString, pDefaultOperator
-----
local foundABooleanOperator
local i
local ignoringInput
local lookingForMatch
local theChar, theCharCount
local theCharNoOfLastQuote
local theCharsFollowingOperators
local theCharsPrecedingOperators
local theEscapeChars
local theNextOperator
local theSearchA
local theWord
-----
if pDefaultOperator is not "OR" then put "AND" into pDefaultOperator
## Step 1: Balance quotes
put false into lookingForMatch
put empty into theSearchA
repeat for each char theChar in pString
add 1 to i
if theChar is quote then
put i into theCharNoOfLastQuote
put not lookingForMatch into lookingForMatch
end if
end repeat
if lookingForMatch then put space into char theCharNoOfLastQuote of pString
## Step 2: Break up into WORDS
put false into ignoringInput
put quote into theEscapeChars
put " " & quote into theCharsPrecedingOperators
put " " & quote into theCharsFollowingOperators
put the number of chars of pString into theCharCount
put pDefaultOperator into theNextOperator
repeat with i = 1 to theCharCount
put char i of pString into theChar
## Deal with quotes
if ignoringInput then
## Should we stop ignoring?
if theChar is in theEscapeChars then
put false into ignoringInput
## Done with quoted "word"
_ProcessWordInUserSearchString theWord, theSearchA, theNextOperator, pDefaultOperator
next repeat
end if
else
## Should we start ignoring?
if theChar is in theEscapeChars then
put true into ignoringInput
next repeat
end if
end if
## Look for and/or
if not ignoringInput then
put (theCharsPrecedingOperators contains char (i-1) of pString or i is 1) and \
( (char i to (i+2) of pString is "AND" and ( ((i+3) > theCharCount) or theCharsFollowingOperators contains char (i+3) of pString) ) or \
(char i to (i+1) of pString is "OR" and ( ((i+2) > theCharCount) or theCharsFollowingOperators contains char (i+2) of pString) ) ) \
into foundABooleanOperator
if foundABooleanOperator then
if char i to (i+2) of pString is "AND" then
put "AND" into theNextOperator
add 2 to i
else
put "OR" into theNextOperator
add 1 to i
end if
next repeat
end if
end if
## Process char
if not ignoringInput then
if theChar is space then
## process word
_ProcessWordInUserSearchString theWord, theSearchA, theNextOperator, pDefaultOperator
else
put theChar after theWord
end if
else
put theChar after theWord
end if
end repeat
## process word
_ProcessWordInUserSearchString theWord, theSearchA, theNextOperator, pDefaultOperator
return theSearchA
end _ParseUserSearchString
private command _ProcessWordInUserSearchString @xWord, @xSearchA, @xNextOperator, pDefaultOperator
if xWord is not empty then
local theSearchIndex
put the number of elements of xSearchA + 1 into theSearchIndex
put xWord into xSearchA[theSearchIndex]["string"]
put xNextOperator into xSearchA[theSearchIndex]["operator"]
put pDefaultOperator into xNextOperator
put empty into xWord
end if
end _ProcessWordInUserSearchString
private function _FindFirstInstanceOfFieldInTables pKey, pTables, pFieldName
-----
local theIndex
local theTableIndex
-----
repeat for each line theTable in pTables
if theTable is among the keys of sDbsA[ pKey ]["schema"]["tables"] then
if pFieldName is among the keys of sDbsA[ pKey ]["schema"]["tables"][ theTable ]["fields"] then
return theTable
end if
else
## maybe an alias?
if tableobjects_tableExists(theTable, pKey) then
local theTableA, theRealTable
put tableobjects_getTable(theTable, pKey) into theTableA
if tableobj_get(theTableA, "type") is "alias" then
put tableobj_get(theTableA, "alias for table") into theRealTable
if theRealTable is among the keys of sDbsA[ pKey ]["schema"]["tables"] then
if pFieldName is among the keys of sDbsA[ pKey ]["schema"]["tables"][ theRealTable ]["fields"] then
return theTable
end if
end if
end if
end if
end if
end repeat
return empty
end _FindFirstInstanceOfFieldInTables
private function _TranslateConditions pString
-----
local earlyMatch, lateMatch
local ignoringInput
local theChar, theCharNo
local theCharsFollowingOperators, theCharsPrecedingOperators, theOperatorA
local theClause
local theEnglishExprs
local theEscapeChars
local theExpr, theExpression
local theLongestExpr
local theProcessedCharCount
local theStartTime
local theString
local theSubstr
-----
put empty into theClause
put false into ignoringInput
put 0 into theProcessedCharCount
put 0 into theCharNo
## todo: customize for individual databases
## todo: Hard coded for sqlite right now
put "IS,IS NOT,IS GREATER THAN,IS GREATER THAN OR EQUAL TO,IS LESS THAN,IS LESS THAN OR EQUAL TO,IS IN," & \
"IS NOT IN,IS BETWEEN,CONTAINS,DOES NOT CONTAIN,BEGINS WITH,ENDS WITH,DOES NOT BEGIN WITH,DOES NOT END WITH" into theEnglishExprs
sort items of theEnglishExprs numeric descending by _SortByLength(each)
put the number of chars of item 1 of theEnglishExprs into theLongestExpr
put "'" & quote into theEscapeChars
put " ')" & quote into theCharsPrecedingOperators
put " '(" & quote into theCharsFollowingOperators
repeat with theCharNo = 1 to the number of chars of pString
put char theCharNo of pString into theChar
## Look for escape chars
if ignoringInput then
if theChar is "\" then
## Add to string and then skip next char
add 1 to theCharNo
put theChar & char theCharNo of pString after theString
next repeat
end if
end if
if ignoringInput then
## Should we stop ignoring?
if theChar is in theEscapeChars then
put false into ignoringInput
put theChar after theString
next repeat
end if
else
## Should we start ignoring?
if theChar is in theEscapeChars then
put true into ignoringInput
put theChar after theString
next repeat
end if
end if
## Process char
if not ignoringInput then
local thePrecedingChar, theCharFollowingExpr
## Look for english expressions.
## Only start looking if previous char is space
put char theCharNo to (theCharNo + (theLongestExpr - 1)) of pString into theSubstr
put char (theCharNo - 1) of pString into thePrecedingChar
if theOperatorA["start"] is empty and char (theCharNo - 1) of pString is space then
repeat for each item theExpr in theEnglishExprs ## items go from longest expression to shortest
if theSubstr begins with theExpr then
## Check that a valid termination character comes after expression: space ' ( :
put char ( theCharNo + length(theExpr) ) of pString into theCharFollowingExpr
if (theCharsFollowingOperators & ":") contains theCharFollowingExpr then
put theCharNo - theProcessedCharCount into theOperatorA["start"]
put theExpr into theExpression
## Update string and charNo/char with found expression
put char theCharNo to (theCharNo + (length(theExpression) - 2)) of pString after theString
put theCharNo + (length(theExpression) - 1) into theCharNo
put char theCharNo of pString into theChar
exit repeat
end if
end if
end repeat
end if
put theChar after theString
local terminationMatch
## Look for match in string with (...)AND/(...)OR
## Make sure AND/OR falls outside range of theExpression though
put theCharsPrecedingOperators contains theChar and \
( (char 2 to 4 of theSubstr is "AND" and theCharsFollowingOperators contains char 5 of theSubstr) or \
(char 2 to 3 of theSubstr is "OR" and theCharsFollowingOperators contains char 4 of theSubstr ) ) into terminationMatch
## Process string
if terminationMatch then
put theOperatorA["start"] + length(theExpression) - 1 into theOperatorA["end"]
_ProcessEnglishOperatorChunk theClause, theString, theOperatorA
put empty into theString
put theCharNo into theProcessedCharCount
put empty into theOperatorA
put empty into theExpression
end if
else
put theChar after theString
end if
end repeat
## Final cleanup
if theOperatorA["start"] > 0 or theOperatorA["start"] is empty then
put theOperatorA["start"] + length(theExpression) - 1 into theOperatorA["end"]
_ProcessEnglishOperatorChunk theClause, theString, theOperatorA
end if
return theClause
end _TranslateConditions
private command _ProcessEnglishOperatorChunk @xClause, pString, pOperatorA
local theValueA, theCharsToSkip
## Get portion of string appearing after expression
put pOperatorA["end"] + 1 into theValueA["start"]
put the number of chars of pString into theValueA["end"]
put " )" into theCharsToSkip
## Find last char of value
repeat with i = theValueA["end"] down to 1
if not (theCharsToSkip contains char i of pString) then
put i into theValueA["end"]
exit repeat
end if
end repeat
## Now we have first char of expression and last char of value. Process.
put _ConvertEnglishOperatorToSQLExpressionInString(pString, pOperatorA, theValueA) after xClause
return empty
end _ProcessEnglishOperatorChunk
private function _SortByLength pStr
return length(pStr)
end _SortByLength
private function _ConvertEnglishOperatorToSQLExpressionInString pString, pOperatorA, pValueA
-----
local theEnglishOperator
local theValue
-----
put char pOperatorA["start"] to pOperatorA["end"] of pString into theEnglishOperator
put char pValueA["start"] to pValueA["end"] of pString into theValue
-- answer "in:" && quote & pString & quote & cr & theEnglishOperator
switch theEnglishOperator
case "IS"
if word 1 to -1 of theValue is "NULL" then
-- NOTHING, ALREADY IN CORRECT SYNTAX
else if word 1 to -1 of theValue is "empty" then
put "= ''" into char pOperatorA["start"] to pValueA["end"] of pString
else
put "=" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
case "IS NOT"
if word 1 to -1 of theValue is "NULL" then
-- NOTHING, ALREADY IN CORRECT SYNTAX
else
put "!=" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
case "IS GREATER THAN"
put ">" into char pOperatorA["start"] to pOperatorA["end"] of pString
break
case "IS GREATER THAN OR EQUAL TO"
put ">=" into char pOperatorA["start"] to pOperatorA["end"] of pString
break
case "IS LESS THAN"
put "<" into char pOperatorA["start"] to pOperatorA["end"] of pString
break
case "IS LESS THAN OR EQUAL TO"
put "<=" into char pOperatorA["start"] to pOperatorA["end"] of pString
break
case "IS IN"
case "IS NOT IN"
if char 1 of word 1 of theValue is "'" then
delete char 1 of word 1 of theValue
end if
if char -1 of word -1 of theValue is "'" then
delete char -1 of word -1 of theValue
end if
if char 1 of word 1 of theValue is not "(" then
put "(" before word 1 of theValue
end if
if the last char of the last word of theValue is not ")" then
put ")" after the last word of theValue
end if
put theValue into char pValueA["start"] to pValueA["end"] of pString
if theEnglishOperator is "IS IN" then
put "IN" into char pOperatorA["start"] to pOperatorA["end"] of pString
else
put "NOT IN" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
case "IS BETWEEN"
put "BETWEEN" into char pOperatorA["start"] to pOperatorA["end"] of pString
break
case "CONTAINS"
case "DOES NOT CONTAIN"
## Support the use of functions: CONTAINS UPPER(':1')
## New behavior
put _AddWildcardToSearchString(theValue, true, true) into theValue
put theValue into char pValueA["start"] to pValueA["end"] of pString
if theEnglishOperator is "CONTAINS" then
put "LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
else
put "NOT LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
case "BEGINS WITH"
case "DOES NOT BEGIN WITH"
put _AddWildcardToSearchString(theValue, false, true) into theValue
put theValue into char pValueA["start"] to pValueA["end"] of pString
if theEnglishOperator is "BEGINS WITH" then
put "LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
else
put "NOT LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
case "ENDS WITH"
case "DOES NOT END WITH"
put _AddWildcardToSearchString(theValue, true, false) into theValue
put theValue into char pValueA["start"] to pValueA["end"] of pString
if theEnglishOperator is "ENDS WITH" then
put "LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
else
put "NOT LIKE" into char pOperatorA["start"] to pOperatorA["end"] of pString
end if
break
end SWITCH
-- answer "out:" && quote & pString & quote
return pString
end _ConvertEnglishOperatorToSQLExpressionInString
private function _AddWildcardToSearchString pValue, pAddPrefix, pAddSuffix
local theStartCharNo, theEndCharNo
put offset("'", pValue) into theStartCharNo
if theStartCharNo > 0 then
put offset("'", pValue, theStartCharNo) into theEndCharNo
if theEndCharNo is 0 then
put the number of chars of pValue into theEndCharNo
else
add theStartCharNo to theEndCharNo
end if
else
put 0 into theStartCharNo
put the number of chars of pValue into theEndCharNo
end if
if pAddSuffix is not false then put "%" before char theEndCharNo of pValue
if pAddPrefix is not false then put "%" after char theStartCharNo of pValue
return pValue
end _AddWildcardToSearchString
--> Array Hierarchy Parsers (Private)
/**
Summary: Converts the current row of a cursor to an array keyed by each column name.
Parameters:
pQueryA: A SQL Query object.
pFieldsInCursor: Pass in a properly ordered list of fields in the cursor if already known. Otherwise `_CursorFieldNames()` is used to fetch them.
@xArray: The array which will be filled with the results.
[pDynamicKeyA]: An optional index lookup array.
[pFieldsToReturn]: Optional list of fields to return. If empty then all fields in cursor are returned.
Description:
Assumes the cursor is not at the end.
Returns: empty
*/
private command _CursorRowToArray pQueryA, pFieldsInCursor, @xArray, pDynamicKeyA, pFieldsToReturn
local theValue, theNextKeyIndex, i
local theFldRef
set the wholematches to true
-- pFieldsInCursor should be the fields as returned by revDatabaseColumnNames. The names
-- need to match up with the revDB index since we access by index below.
put the number of elements of pDynamicKeyA + 1 into theNextKeyIndex
switch sDbsA[ pQueryA["database"] ]["connections"][ pQueryA["connection"] ]["type"]
case "valentina"
if pFieldsInCursor is empty then put _ValentinaFieldsFromCursor( pQueryA["cursor id"] ) into pFieldsInCursor
replace comma with cr in pFieldsInCursor
repeat for each line theField in pFieldsInCursor
add 1 to i
if pFieldsToReturn is not empty and theField is not among the items of pFieldsToReturn then next repeat
put VCursor_Field(pQueryA["cursor id"], i) into theFldRef
put theField into pDynamicKeyA[theNextKeyIndex]
if pQueryA["report null"] and VField_IsNull(theFldRef) then
put "NULL" into xArray[ pDynamicKeyA ]
else
switch VField_Type(theFldRef)
case "kTypePicture"
case "kTypeBLOB"
get VBLOB_ReadData(theFldRef, "theValue")
put theValue into xArray[ pDynamicKeyA ]
break
case "kTypeText"
case "kTypeFixedBinary"
case "kTypeVarBinary"
default
put VField_Value(theFldRef) into xArray[ pDynamicKeyA ]
end switch
end if
end REPEAT
break
default
if pFieldsInCursor is empty then put _CursorFieldNames(pQueryA["cursor id"]) into pFieldsInCursor
replace comma with cr in pFieldsInCursor
repeat for each line theField in pFieldsInCursor
add 1 to i
if pFieldsToReturn is not empty and theField is not among the items of pFieldsToReturn then next repeat