1<?
2curFile.cache = false
3curScript.timeout = 1
4
5global QBDEBUG = false
6
7global CTOT = 0
8global CLOG = ""
9global LAST_EXEC = 0
10global LAST_FREE = 0
11global QUELOG = ""
12/*
13 Changes
14
15 10-04-2013: modifica nel sistema di deallocazione dei cursori (non uso piu' suspend/resume)
16 22-03-2013: inizializzazione solo se serve
17
18
19
20 Documentazione
21
22 Le Select non devono essere modificate
23
24
25 Prima di leggere i valori deve essere chiamata la funzione:
26
27 usersdb.Exec()
28
29 Lettura:
30
31 in testa deve essere chiamato
32
33 rec = usersdb.FetchRec()
34
35 ogni lettura di un campo deve essere trasformata in:
36
37 userdb.codice[xx] ==> rec['codice']
38
39 Se c'e' un loop:
40
41 Loop:
42
43 se ho un loop:
44
45 es:
46 for(i=first;i<=last;i++)
47 {
48 }
49
50 in alternativa a 'Exec' si utilizza 'LoopExec' con i parametri del loop
51
52 usersdb.LoopExec(first,last)
53 for(i=first;i<=last;i++)
54 {
55 rec = usersdb.FetchRec()
56
57 aaa = rec['codice']
58 ..
59 }
60
61
62 Scrittura:
63
64 Ogni assegnamento deve essere sostituito con la funzione Set
65
66 userdb.codice[xx] = "abcd"
67
68 diventa
69
70 userdb.Set("codice","abcd")
71
72 Come scrivere sul record giusto:
73
74 - nel caso di nuovi record si chiama NewRecord in modo normale, il successivo GetRecNum si puo' rimuovere (o lasciare, ininfluente)
75
76 - nel caso di modifiche il GetRecNum seleziona il record da modificare e restituisce il record
77
78 Modifiche in loop:
79
80
81 usersdb.LoopExec(first,last)
82 for(i=first;i<=last;i++)
83 {
84 rec = usersdb.FetchRec()
85
86 userdb.Seek(rec['codice'])
87 userdb.Set("nome","pippo")
88 userdb.Save()
89 }
90
91
92 Nel caso dei nuovi record...
93
94
95*/
96class gxHttpExt
97{
98 string userAgent = "Biferno\r\n"
99
100 string url = ""
101 string request = ""
102 string host = ""
103// string scheme = ""
104 int port = 80
105
106 // headers di risposta
107 header headers
108
109 void gxHttp(string url)
110 {
111 this.url = url
112 // explode the url
113 if (url.Begins("http://"))
114 url = url.SubString(8)
115
116 this.request = ansi.strch(url, "/")
117 this.host = url.SubString(1, url.length - this.request.length)
118 if NOT(this.request)
119 this.request = "/"
120 if (t = ansi.strch(this.host, ':'))
121 { this.port = t.SubString(2)
122 this.host.length -= this.port.length + 1
123 }
124 else
125 port = 80
126
127 }
128 string Get()
129 {
130 /* if (url.Begins("https://"))
131 {
132 // supporto https attraverso curl
133 script = "curl -k \"" + url + "\""
134 unix.bash(script, 3000, &stdOut, &stdErr)
135 return stdOut // stdOut ? stdOut : (stdErr)
136 }*/
137 headerString = "GET " + this.request + " HTTP/1.0\r\nHost: " + this.host + "\r\nUser-Agent: " + this.userAgent + "\r\n"
138
139 reqPage = httpPage(header(headerString), "")
140 resPage = reqPage.Exec(this.host, port)
141
142 this.headers = resPage.head
143 return resPage.body
144 }
145 //===========================================================================================
146 string Post(array params)
147 {
148
149 headerString = "POST " + this.request + " HTTP/1.1\r\nHost: " + this.host + "\r\nUser-Agent: " + this.userAgent
150 // params encoding
151 for(p=1;p<=params.dim;p++)
152 {
153 params[p] = params.name[p] + "=" + params[p].UrlEncode()
154 }
155 paramsString = params.ToString("&")
156
157 headerString += "Content-Type: application/x-www-form-urlencoded\r\n"
158 headerString += "Content-Length: " + paramsString.length + "\r\n"
159 headerString += "\r\n"
160
161
162 reqPage = httpPage(header(headerString), paramsString)
163 resPage = reqPage.Exec(this.host, port)
164
165 this.headers = resPage.head
166
167 return resPage.body
168 }
169 //===========================================================================================
170
171 string GetHeader(string header)
172 {
173 return this.headers.GetField(header)
174 }
175
176 //===========================================================================================
177 void Destructor(void)
178 {
179 /* if(wRec.dim)
180 Error("Array di scrittura non vuoto!!!! Aggiungere '" + tname + "db.Save()' \r --> " + wRec.ToString(", ",true) )
181
182 if(cursorID)
183 Free()
184 */
185 }
186
187}
188
189class qb
190{
191 // Indica se e' stata inizializzata
192 boolean init = false
193 // Constants
194 static const int all = 0
195 static const int allNot = 1
196 static const int and = 2
197 static const int andNot = 3
198 static const int or = 4
199 static const int orNot = 5
200
201 static const int equal = 1
202 static const int begins = 2
203 static const int contains = 3
204 static const int greater = 4
205 static const int greaterEqu = 5
206 static const int less = 6
207 static const int lessEqu = 7
208 static const int containsWordExact = 8
209 static const int containsWordBegin = 9
210 static const int ends = 10
211
212 static const int asc = -1
213 static const int desc = 1
214
215
216 // Query vars
217 string primaryKey
218 string selectString
219 string tname
220
221 // Query vars (lettura)
222 int cursorID = 0
223
224 string joinName = "" // select rel
225 string distinct = ""
226 string whereCond = ""
227 string orderBy = ""
228
229 // Query vars (scrittura)
230 int WcursorID = 0
231 boolean writeNew = false
232 array wRec // array di valori da scrivere
233 array wDateFields // campi data
234 array wBoolFields // campi bool
235 string wWhere = "" // condizione where per scrittura
236
237 // Info
238 array lastRec
239 boolean executed = false
240 int rec_count = -1
241
242 static array cursor_list
243
244 static array global_primary_key
245 static array global_select
246 static array global_wdate
247 static array global_wbool
248/* static private boolean debugging = false
249
250 static private string db_url = global remote_db_url
251
252 public string tname
253 public string last_query = "(empty)"
254 public string last_result = "(empty)"
255
256 private string sort_field = ""
257 private string sort_type = ""
258 // fields
259 public array field_names;
260
261*/
262 public array _web_result = array()
263
264 boolean _web_query(string query)
265 {
266 this._web_result = array() // empties
267
268 //docUrl = "https://www.novarium.net/images/que.php?query=" + query.UrlEncode()
269 docUrl = "-d \"query=" + query.UrlEncode() + "\" https://www.novarium.net/images/que.php"
270 script = "curl -k " + docUrl
271 unix.bash(script, 3000, &stdOut, &stdErr)
272/*
273 http = gxHttpExt("https://www.novarium.net/images/que.php")
274 params = array("query":query)
275 stdOut = http.Post()
276*/
277 global QUELOG += "QUERY '" + query + "' -> \r" + stdOut + "\r"
278 echo global QUELOG
279 exit
280 lines = stdOut.ToArray("\r")
281 for(i=1;i<=lines.dim;i++)
282 Eval("this._web_result.Add( " + lines[i] + " )")
283
284 return true
285 }
286 int _web_get_cur_recs()
287 {
288 return this._web_result.dim
289 }
290 array _web_fetch_rec()
291 {
292 if(!this._web_result.dim)
293 return array()
294 //print("siz: " + this._web_result.dim)
295 rec = this._web_result[1]
296 this._web_result.Delete(1)
297 return rec
298 }
299
300 void _init_table()
301 {
302 if(global_primary_key.name.Find(tname))
303 {
304 // gia' inizializzata, ricopio i valori
305 selectString = global_select[tname]
306 primaryKey = global_primary_key[tname]
307 wDateFields = global_wdate[tname]
308 wBoolFields = global_wbool[tname]
309
310 init = true
311 global QUELOG += "INIT TABLE '" + tname + "' (fast)\r"
312 return
313 }
314 // ANALIZZO I CAMPI per definire eventuali trasformazioni
315 this._web_query("SHOW COLUMNS FROM `" + tname + "`")
316 tot = this._web_get_cur_recs()
317
318 for(i=1;i<=tot;i++)
319 {
320 fRec = _web_fetch_rec()
321 if(selectString)
322 selectString += ","
323 if(fRec['Type'].Begins("date"))
324 {
325 // Funzione di lettura
326 selectString += "DATE_FORMAT(`" + tname + "`." + fRec['Field'] + ",\"%d-%m-%Y\") AS " + fRec['Field']
327 wDateFields.Add(fRec['Field'])
328 }
329 else if(fRec['Type'].Begins("tinyint")) // campo boolean
330 {
331 selectString += "`" + tname + "`."
332 selectString += fRec['Field']
333 wBoolFields.Add(fRec['Field'])
334 }
335 else
336 {
337 selectString += "`" + tname + "`."
338 selectString += fRec['Field']
339 }
340 if(fRec['Key'] == "PRI")
341 primaryKey = fRec['Field']
342 }
343// global dbconn.Free(dbh)
344 // ANALIZZO GLI INDICI PER VEDERE SE CE NE SONO FULLTEXT
345 // SHOW INDEXES FROM cli WHERE Index_type = "FULLTEXT"
346
347 global_select.Add(tname:selectString)
348 global_primary_key.Add(tname:primaryKey)
349 global_wdate.Add(tname:wDateFields)
350 global_wbool.Add(tname:wBoolFields)
351
352 // non fa niente
353 //print(dummy + "<br />")
354 init = true
355 global QUELOG += "INIT TABLE '" + tname + "'\r"
356 }
357
358 void qb(string dummy)
359 {
360 // Stabilisce il nome della tabella
361 if(dummy.Contains("/"))
362 {
363 dummy = ansi.strrch(dummy,"/")
364 tname = dummy.SubString(2,dummy.length-4)
365 }
366 else
367 tname = dummy.SubString(0,dummy.length-3)
368 }
369 void Destructor(void)
370 {
371 if(wRec.dim)
372 Error("Array di scrittura non vuoto!!!! Aggiungere '" + tname + "db.Save()' \r --> " + wRec.ToString(", ",true) )
373
374 if(cursorID)
375 Free()
376 }
377
378 void Error(string errorMessage)
379 {
380 aaa = "ERRORE (leggi sotto)"
381 debug
382 }
383
384 string ft_begins(string keyword)
385 {
386 retString = keyword
387 if(keyword.Contains(" "))
388 {
389 keyArr = keyword.ToArray(" ")
390 for(xx=1;xx<=keyArr.dim;xx++)
391 {
392 if(keyArr[xx].length>1)
393 keyArr[xx] = "+" + keyArr[xx]
394 keyArr[xx] += "*"
395 }
396 retString = keyArr.ToString(" ")
397 // retString = keyword.Substitute(" ","* ") + "*" else
398 }
399 else if(keyword.Contains(".") && !keyword.Contains("'"))
400 retString = "+'" + retString + "'"
401 else
402 retString = keyword + "*"
403 return retString
404 return "+" + keyword.Substitute(" ","* ")
405 }
406//====== pubblici
407 void SelectAll()
408 {
409 //if(!init)
410 _init_table()
411
412 // reset del contatore
413 rec_count = -1 //una nuova Select resetta i dati
414 executed = false
415
416 joinName = ""
417 whereCond = ""
418 orderBy = ""
419 }
420
421 void _where_mode(int mode)
422 {
423 switch(mode/2)
424 {
425 case 0: // all/allNot
426 whereCond = ""
427 joinName = distinct = "" // reset
428 break
429 case 1: // and/andNot
430 if(whereCond)
431 whereCond += " AND "
432 break
433 case 2: // or/orNot
434 if(whereCond)
435 whereCond += " OR "
436 break
437 default:
438 debug
439 }
440 if(mode%2) // pari
441 whereCond += "NOT "
442 }
443 string _where_mode_inter(string curWhere,int mode)
444 {
445 retString = ""
446 // Mode
447 switch(mode/2)
448 {
449 case 0: // all/allNot
450 debug
451 break
452 case 1: // and/andNot
453 if(curWhere)
454 retString = " AND "
455 break
456 case 2: // or/orNot
457 if(curWhere)
458 retString = " OR "
459 break
460 default:
461 debug
462 }
463
464 if(mode%2) // pari
465 retString += "NOT "
466
467 return retString
468 }
469
470 string _where_cond(string field, int operat, string val)
471 {
472 if(wDateFields.Find(field)) // Trasforma la data
473 {
474 if(val.IsDate())
475 val = time(val).Strftime("%Y-%m-%d")
476 else
477 val = "0000-00-00"
478 }
479 else if(wBoolFields.Find(field)) // Trasforma la data
480 {
481 if(val == "no")
482 val = "0"
483 else if(val == "si")
484 val = "1"
485 }
486 tabbedField = "`" + tname + "`." + field
487 quotedVal = val.Substitute("\"","\\\"")
488 // Operation
489 switch(operat)
490 {
491 case equal:
492 /*if(tname == "cli" && field == "master")
493 retString = " MATCH(" + field + ") AGAINST(\"" + quotedVal + "\" IN BOOLEAN MODE)"
494 else*/
495 retString = tabbedField + " = \"" + quotedVal + "\""
496 break
497 case begins:
498 retString = tabbedField + " LIKE \"" + quotedVal + "%\""
499 break
500 case ends:
501 retString = tabbedField + " LIKE \"%" + quotedVal + "\""
502 break
503 case contains:
504 retString = tabbedField + " LIKE \"%" + quotedVal + "%\""
505 break
506 case containsWordBegin:
507 if(quotedVal.Contains("-"))
508 retString = tabbedField + " LIKE \"%" + quotedVal + "%\""
509 else if(quotedVal.Contains("@")) // le email vengono digerite bene da MATCH (1/2/2017
510 retString = " MATCH(" + tabbedField + ") AGAINST('\"" + quotedVal + "*\"' IN BOOLEAN MODE)"
511 else if(1)
512 retString = " MATCH(" + tabbedField + ") AGAINST(\"" + ft_begins(quotedVal) + "\" IN BOOLEAN MODE)"
513 else if(quotedVal.Contains(" "))
514 retString = " MATCH(" + tabbedField + ") AGAINST(\"" + quotedVal.Substitute(" ","* ") + "*\" IN BOOLEAN MODE)"
515 else
516 retString = " MATCH(" + tabbedField + ") AGAINST(\"" + quotedVal + "*\" IN BOOLEAN MODE)"
517 break
518 case containsWordExact:
519 // if(quotedVal.Contains("@")) // le email vengono digerite bene da MATCH (1/2/2017
520 retString = " MATCH(" + tabbedField + ") AGAINST('\"" + quotedVal + "\"' IN BOOLEAN MODE)"
521 // else
522 // retString = " MATCH(" + tabbedField + ") AGAINST(\"" + quotedVal + "\" IN BOOLEAN MODE)"
523 break
524 case greater:
525 if(wDateFields.Find(field)) // Trasforma la data
526 retString = "(" + tabbedField + " > \"" + quotedVal + "\" OR " + tabbedField + " = \"0000-00-00\")"
527 else
528 retString = tabbedField + " > \"" + quotedVal + "\""
529 break
530 case greaterEqu:
531 if(wDateFields.Find(field)) // Trasforma la data
532 retString = "(" + tabbedField + " >= \"" + quotedVal + "\" OR " + tabbedField + " = \"0000-00-00\")"
533 else
534 retString = tabbedField + " >= \"" + quotedVal + "\""
535 break
536 case less:
537 if(wDateFields.Find(field)) // Trasforma la data
538 retString = "(" + tabbedField + " < \"" + quotedVal + "\" OR " + tabbedField + " = \"0000-00-00\")"
539 else
540 retString = tabbedField + " < \"" + quotedVal + "\""
541 break
542 case lessEqu:
543 if(wDateFields.Find(field)) // Trasforma la data
544 retString = "(" + tabbedField + " <= \"" + quotedVal + "\" OR " + tabbedField + " = \"0000-00-00\")"
545 else
546 retString = tabbedField + " <= \"" + quotedVal + "\""
547 break
548
549 default:
550 debug
551 }
552
553 return retString
554 }
555 void Select(...)
556 {
557 if(!init)
558 _init_table()
559
560 rec_count = -1 //una nuova Select resetta i dati
561 executed = false
562 orderBy = ""
563
564 whe = ""
565
566 // Primo giro
567 mode = _1
568 field = _2
569 operat = _3
570 searchStr = string(_4)
571
572 if(searchStr)
573 {
574 // Mode
575 _where_mode(mode)
576 whe += _where_cond(field, operat, searchStr)
577 }
578 arg = 5
579 while(1)
580 {
581 if(!curScript.IsDef("_" + arg)) // vale per il secondo giro
582 break
583
584 Eval("mode = _" + (arg++))
585 Eval("field = _" + (arg++))
586 Eval("operat = _" + (arg++))
587 Eval("searchStr = string(_" + (arg++) + ")")
588
589 if(!searchStr) // ricerca vuota: per 'qb' e' come se non venisse fatta
590 continue
591
592 whe += _where_mode_inter(whe,mode)
593 whe += _where_cond(field, operat, searchStr)
594 }
595
596 if(curScript.IsDef("_5") && whe) // vale per il secondo giro
597 whereCond += "(" + whe + ")"
598 /* else if(curScript.IsDef("_9") && whe) // vale per il terzo giro
599 whereCond += "(" + whe + ")"*/
600 else
601 whereCond += whe
602 }
603 void SelectMand(...)
604 {
605 if(!init)
606 _init_table()
607
608 rec_count = -1 //una nuova Select resetta i dati
609 executed = false
610 orderBy = ""
611
612 whe = ""
613
614 // Primo giro
615 mode = _1
616 field = _2
617 operat = _3
618 searchStr = string(_4)
619
620 // Mode
621 _where_mode(mode)
622 whe += _where_cond(field, operat, searchStr)
623
624 arg = 5
625 while(1)
626 {
627 if(!curScript.IsDef("_" + arg)) // vale per il secondo giro
628 break
629
630 Eval("mode = _" + (arg++))
631 Eval("field = _" + (arg++))
632 Eval("operat = _" + (arg++))
633 Eval("searchStr = string(_" + (arg++) + ")")
634
635
636 whe += _where_mode_inter(whe,mode)
637 whe += _where_cond(field, operat, searchStr)
638 }
639
640 if(curScript.IsDef("_5") && whe) // vale per il secondo giro
641 whereCond += "(" + whe + ")"
642 else
643 whereCond += whe
644 }
645 void SelectDistinct(string field1, int sortType1=asc...)
646 {
647 if(!init)
648 _init_table()
649
650 rec_count = -1 //una nuova Select resetta i dati
651 executed = false
652 orderBy = ""
653
654 distinct = field1
655 }
656 void SelectRel(int mode, string field, int operat, qb relDb, string dbField)
657 {
658 if(!init)
659 _init_table()
660
661 // es: maindb.SelectRel(and,"master",equal,clidb,"master")
662 rec_count = -1 //una nuova Select resetta i dati
663 executed = false
664 orderBy = ""
665
666 // Mode
667 _where_mode(mode)
668
669 // Operation
670 switch(operat)
671 {
672 case equal:
673 subQue = "SELECT " + dbField + " FROM `" + relDb.tname + "`"
674 if(relDb.whereCond)
675 subQue += " WHERE " + relDb.whereCond
676 whereCond += "`" + tname + "`." + field + " IN (" + subQue + ")"
677 break
678 case containsWordExact: // UNSTABLE: // PROBLEMA IN messages/discussion
679 //UNSTABLE
680 subQue = "SELECT " + dbField + " FROM `" + relDb.tname + "`"
681 if(relDb.whereCond)
682 subQue += " WHERE " + relDb.whereCond
683 whereCond += "`" + tname + "`." + field + " IN (" + subQue + ")"
684 break
685 default:
686 debug
687 }
688 }
689 void SelectRelJoin(int mode, string field, int operat, qb relDb, string dbField)
690 {
691 /*
692 Esegue la SelectRel in un altro modo: non usa l'operatore "IN"
693
694 */
695 if(!init)
696 _init_table()
697
698 // es: maindb.SelectRel(and,"master",equal,clidb,"master")
699 rec_count = -1 //una nuova Select resetta i dati
700 executed = false
701 orderBy = ""
702
703 // Mode
704 _where_mode(mode)
705
706
707 // Operation
708 switch(operat)
709 {
710 case equal:
711 if(tname != relDb.tname)
712 {
713 joinName += ",`" + relDb.tname + "`"
714 whereCond += "`" + tname + "`." + field + " = `" + relDb.tname + "`." + dbField
715 if(relDb.whereCond)
716 whereCond += " AND "
717 }
718 // Mi porto dietro eventuali join dell'altra tabella
719 joinName += relDb.joinName
720 if(relDb.whereCond)
721 whereCond += relDb.whereCond
722 break
723 case containsWordExact: // UNSTABLE: // PROBLEMA IN messages/discussion
724 //UNSTABLE
725 subQue = "SELECT " + dbField + " FROM `" + relDb.tname + "`"
726 if(relDb.whereCond)
727 subQue += " WHERE " + relDb.whereCond
728 whereCond += field + " IN (" + subQue + ")"
729 break
730 default:
731 debug
732 }
733/*
734 print(whereCond)
735 exit
736aaa = whereCond
737debug
738*/
739 }
740 void SelectRel2(int mode, string field, int operat, qb relDb, string dbField,int mode2, string field2, int operat2, qb relDb2, string dbField2)
741 {
742 if(!init)
743 _init_table()
744
745 // es: maindb.SelectRel(and,"master",equal,clidb,"master")
746 rec_count = -1 //una nuova Select resetta i dati
747 executed = false
748 orderBy = ""
749
750 // Mode
751 _where_mode(mode)
752
753 whereCond += " ("
754 // Operation
755 switch(operat)
756 {
757 case equal:
758 subQue = "SELECT " + dbField + " FROM `" + relDb.tname + "`"
759 if(relDb.whereCond)
760 subQue += " WHERE " + relDb.whereCond
761 whereCond += "`" + tname + "`." + field + " IN (" + subQue + ")"
762 break
763 case containsWordExact: // UNSTABLE: // PROBLEMA IN messages/discussion
764 //UNSTABLE
765 subQue = "SELECT " + dbField + " FROM `" + relDb.tname + "`"
766 if(relDb.whereCond)
767 subQue += " WHERE " + relDb.whereCond
768 whereCond += "`" + tname + "`." + field + " IN (" + subQue + ")"
769 break
770 default:
771 debug
772 }
773
774 whereCond += " OR "
775 // Operation
776 switch(operat2)
777 {
778 case equal:
779 subQue = "SELECT " + dbField2 + " FROM `" + relDb2.tname + "`"
780 if(relDb2.whereCond)
781 subQue += " WHERE " + relDb2.whereCond
782 whereCond += "`" + tname + "`." + field2 + " IN (" + subQue + ")"
783 break
784 case containsWordExact: // UNSTABLE: // PROBLEMA IN messages/discussion
785 //UNSTABLE
786 subQue = "SELECT " + dbField2 + " FROM `" + relDb2.tname + "`"
787 if(relDb2.whereCond)
788 subQue += " WHERE " + relDb2.whereCond
789 whereCond += "`" + tname + "`." + field2 + " IN (" + subQue + ")"
790 break
791 default:
792 debug
793 }
794 whereCond += ")"
795
796 }
797
798// void Sort(string field_name, int direction = global asc , string field_name2 = "", int direction2 = 0)
799 void Sort(string field1, int direction=asc,...)
800 {
801 if(!init)
802 _init_table()
803
804 if(wDateFields.Find(field1)) // BUG su DATE_FORMAT
805 orderBy += "`" + tname + "`." + field1
806 else
807 orderBy += field1
808 if(direction == desc)
809 orderBy += " DESC"
810
811 if(curScript.IsDef("_3"))
812 {
813 orderBy += "," + _3
814 if(curScript.IsDef("_4") && _4 == desc)
815 orderBy += " DESC"
816 }
817 if(curScript.IsDef("_5"))
818 {
819 orderBy += "," + _5
820 if(curScript.IsDef("_6") && _6 == desc)
821 orderBy += " DESC"
822 }
823 if(curScript.IsDef("_7"))
824 {
825 orderBy += "," + _7
826 if(curScript.IsDef("_8") && _8 == desc)
827 orderBy += " DESC"
828 }
829 if(curScript.IsDef("_9"))
830 debug
831 }
832
833/*
834 int GetTotFields()
835 {
836 if(!field_names)
837 retrieve_field_info()
838
839 return field_names.dim
840 }
841
842 string GetFieldName(int idx)
843 {
844 if(!field_names)
845 retrieve_field_info()
846
847 return field_names[idx]
848 }
849*/
850 int GetCurRecs()
851 {
852 if(!init)
853 _init_table()
854
855 if(rec_count>-1)
856 return rec_count
857
858 if(distinct)
859 query = "SELECT COUNT(DISTINCT " + distinct + ") FROM `" + tname + "`" + joinName
860 else
861 query = "SELECT COUNT(*) FROM `" + tname + "`" + joinName
862
863 if(whereCond)
864 query += " WHERE " + whereCond
865 // query = query.Substitute(" = \"no\""," = 0").Substitute(" = \"si\""," = 1")
866 //if(cursorID)Free()
867 if(global QBDEBUG)
868 TSTART = time.Millisecs()
869 _web_query(query)
870 rec = _web_fetch_rec()
871
872 rec_count = 0
873 if(rec[1].IsNumeric())
874 rec_count = long(rec[1])
875
876 if(global QBDEBUG)
877 {
878 global CTOT++
879 global LAST_EXEC = cursorID
880 global CLOG += "[" + cursorID + "]"
881 TINTERVAL = (time.Millisecs() - TSTART).ToString()
882 if(long(TINTERVAL) > 50)
883 TINTERVAL = "<span>" + TINTERVAL + " millis</span>"
884 sqlTot = global dbconn.GetCurRecs()
885 // global QUELOG += time().Strftime("%Y-%m-%d") + " "
886 global QUELOG += time().Strftime("%H:%M:%S") + "." + (time.Millisecs()%1000)
887 global QUELOG += "[" + global CTOT + "]" + query + " ===> " + rec_count + " (T: " + (TINTERVAL) + ") " + ((sqlTot>100)?("<span>!!!" + sqlTot + " records!!!</span>"):"") + "\r"
888 if(TINTERVAL.Begins("<"))
889 {
890 hhh = global dbconn.Exec("EXPLAIN " + query)
891
892 expl = ""
893 while(rrr = global dbconn.FetchRec(hhh))
894 expl += rrr.ToString("\r",true)
895 global QUELOG += "<label style='color: green'>" + expl + "</label>"
896 global dbconn.Free(hhh)
897 }
898 }
899
900 return rec_count
901 }
902
903 string GetRecID()
904 {
905 if(!init)
906 _init_table()
907
908 // Se occere legge un record dai risultati
909 if(!lastRec.dim)
910 FetchRec()
911
912 if(!primaryKey)
913 Error("DEFINIRE UNA PRIMARY KEY PER `" + tname + "`")
914
915 return lastRec[primaryKey]
916 }
917 array GetRecNum(string recID)
918 {
919 if(!init)
920 _init_table()
921
922 if(!primaryKey)
923 Error("DEFINIRE UNA PRIMARY KEY PER `" + tname + "`")
924
925 if(writeNew)
926 return 0
927
928
929 whereCond = primaryKey + " = '" + recID + "'"
930 this.Exec()
931 return this.FetchRec()
932 }
933/*
934 string GetCell(string fname, int idx)
935 {
936 return data_array[fname][idx];
937 }
938
939 void SetProperty(string propertyName)
940 {
941 if(propertyName == "codice")
942 stop
943 //if(p)
944 }
945*/
946 // new methods
947 void Exec()
948 {
949 if(!init)
950 _init_table()
951
952 if(distinct)
953 query = "SELECT DISTINCT " + distinct + " FROM `" + tname + "`" + joinName
954 else
955 query = "SELECT " + selectString + " FROM `" + tname + "`" + joinName
956 if(whereCond)
957 query += " WHERE " + whereCond
958 // query = query.Substitute(" = \"no\""," = 0").Substitute(" = \"si\""," = 1")
959 if(orderBy)
960 query += " ORDER BY " + orderBy
961
962 if(cursorID)
963 Free()
964
965 if(global QBDEBUG)
966 TSTART = time.Millisecs()
967 _web_query(query)
968 executed = true
969
970 if(global QBDEBUG)
971 {
972 global CTOT++
973 global LAST_EXEC = cursorID
974 global CLOG += "[" + cursorID + "]"
975 TINTERVAL = (time.Millisecs() - TSTART).ToString()
976 if(long(TINTERVAL) > 50)
977 TINTERVAL = "<span>" + TINTERVAL + " millis</span>"
978 sqlTot = global dbconn.GetCurRecs()
979 global QUELOG += time().Strftime("%H:%M:%S") + "." + (time.Millisecs()%1000)
980 global QUELOG += "[" + global CTOT + "]" + query + " (T: " + (TINTERVAL) + ") " + ((sqlTot>100)?("<span>!!!" + sqlTot + " records!!!</span>"):"") + "\r"
981 if(TINTERVAL.Begins("<"))
982 {
983 hhh = global dbconn.Exec("EXPLAIN " + query)
984
985 expl = ""
986 while(rrr = global dbconn.FetchRec(hhh))
987 expl += rrr.ToString("\r",true)
988 global QUELOG += "<label style='color: green'>" + expl + "</label>"
989 global dbconn.Free(hhh)
990 }
991 }
992 }
993 void LoopExec(int first,int last)
994 {
995 if(!init)
996 _init_table()
997
998 if(distinct)
999 query = "SELECT DISTINCT " + distinct + " FROM `" + tname + "`" + joinName
1000 else
1001 query = "SELECT " + selectString + " FROM `" + tname + "`" + joinName
1002 if(whereCond)
1003 query += " WHERE " + whereCond
1004 // query = query.Substitute(" = \"no\""," = 0").Substitute(" = \"si\""," = 1")
1005 if(orderBy)
1006 query += " ORDER BY " + orderBy
1007 query += " LIMIT " + (last-first+1) + " OFFSET " + (first-1)
1008
1009 if(cursorID)
1010 Free()
1011
1012 if(global QBDEBUG)
1013 TSTART = time.Millisecs()
1014 _web_query(query)
1015 executed = true
1016
1017 if(global QBDEBUG)
1018 {
1019 global CTOT++
1020 global LAST_EXEC = cursorID
1021 global CLOG += "[" + cursorID + "]"
1022 TINTERVAL = (time.Millisecs() - TSTART).ToString()
1023 if(long(TINTERVAL) > 50)
1024 TINTERVAL = "<span>" + TINTERVAL + " millis</span>"
1025 sqlTot = global dbconn.GetCurRecs()
1026 global QUELOG += time().Strftime("%H:%M:%S") + "." + (time.Millisecs()%1000)
1027 global QUELOG += "[" + global CTOT + "]" + query + " (T: " + (TINTERVAL) + ") " + ((sqlTot>100)?("<span>!!!" + sqlTot + " records!!!</span>"):"") + "\r"
1028 if(TINTERVAL.Begins("<"))
1029 {
1030 hhh = global dbconn.Exec("EXPLAIN " + query)
1031
1032 expl = ""
1033 while(rrr = global dbconn.FetchRec(hhh))
1034 expl += rrr.ToString("\r",true)
1035 global QUELOG += "<label style='color: green'>" + expl + "</label>"
1036 global dbconn.Free(hhh)
1037 }
1038 }
1039 }
1040 void ExecSQL(string query)
1041 {
1042 if(!init)
1043 _init_table()
1044
1045 if(cursorID)
1046 Free()
1047
1048 if(global QBDEBUG)
1049 TSTART = time.Millisecs()
1050 _web_query(query)
1051 executed = true
1052
1053 if(global QBDEBUG)
1054 {
1055 global CTOT++
1056 global LAST_EXEC = cursorID
1057 global CLOG += "[" + cursorID + "]"
1058 TINTERVAL = (time.Millisecs() - TSTART).ToString()
1059 if(long(TINTERVAL) > 50)
1060 TINTERVAL = "<span>" + TINTERVAL + " millis</span>"
1061 sqlTot = global dbconn.GetCurRecs()
1062 global QUELOG += time().Strftime("%H:%M:%S") + "." + (time.Millisecs()%1000)
1063 global QUELOG += "[" + global CTOT + "]" + query + " (T: " + (TINTERVAL) + ") " + ((sqlTot>100)?("<span>!!!" + sqlTot + " records!!!</span>"):"") + "\r"
1064 if(TINTERVAL.Begins("<"))
1065 {
1066 hhh = global dbconn.Exec("EXPLAIN " + query)
1067
1068 expl = ""
1069 while(rrr = global dbconn.FetchRec(hhh))
1070 expl += rrr.ToString("\r",true)
1071 global QUELOG += "<label style='color: green'>" + expl + "</label>"
1072 global dbconn.Free(hhh)
1073 }
1074 }
1075 }
1076
1077 void Free()
1078 {
1079/* if(!cursorID)
1080 return
1081
1082 // gia' deallocato
1083 if(cursor_list.Find(cursorID))
1084 return
1085
1086 cursor_list.Add(cursorID)
1087*/
1088 // error.Resume() // Aggiunto perche' in qualche caso da 'BadCursorID'
1089// global dbconn.Free(cursorID)
1090 // error.Suspend()
1091 if(global QBDEBUG)
1092 {
1093 global CLOG += "<" + cursorID + ">"
1094 }
1095 cursorID = 0
1096 }
1097 array FetchRec()
1098 {
1099 if(!executed) // Query non ancora seguita
1100 this.Exec()
1101
1102 lastRec = _web_fetch_rec()
1103 if(lastRec && !distinct)
1104 {
1105 // Azzero tutti i campi data nulli
1106 for(dd=1;dd<=wDateFields.dim;dd++)
1107 {
1108 if(lastRec[wDateFields[dd]] == "00-00-0000")
1109 lastRec[wDateFields[dd]] = ""
1110 }
1111
1112 if(!primaryKey)
1113 Error("DEFINIRE UNA PRIMARY KEY PER `" + tname + "`")
1114
1115 // Mi posiziono per un'eventuale scrittura
1116 wWhere = primaryKey + " = '" + lastRec[primaryKey] + "'"
1117 }
1118
1119 return lastRec
1120 }
1121
1122 // Scrittura
1123 string NewRecord()
1124 {
1125 if(!init)
1126 _init_table()
1127
1128 writeNew = true
1129
1130 _web_query("SELECT COUNT(*) AS tot FROM `" + tname + "`")
1131 rec = _web_fetch_rec()
1132
1133 totRec = rec['tot'].Pad(6,"0",true)
1134// global dbconn.Free(dbh)
1135
1136 // alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
1137 // alpha = "ABCDEFGHIJKLMNOPQRSTVVWXYZ" // tolo la U che viene usata nei codici fornitore
1138 alpha = "0123456789"
1139 // ansi.srandom(time.Millisecs() % 10000)
1140 rnd1 = (ansi.random() % alpha.length) + 1
1141 rnd2 = (ansi.random() % alpha.length) + 1
1142
1143
1144 // newRecID = alpha.char[rnd1] + alpha.char[rnd2] + totRec
1145 newRecID = totRec.SubString(1,2) + alpha.char[rnd1] + alpha.char[rnd2] + totRec.SubString(3)
1146 return newRecID
1147 }
1148 void DeleteRecord(int limit = 1)
1149 {
1150 if(!limit)
1151 return
1152
1153 if(writeNew)
1154 Error("Scrittura sospesa!!!!")
1155 if(!wWhere)
1156 Error("Where condition non definita!!!!")
1157
1158 // query = "DELETE FROM `" + tname + "`" + joinName + " WHERE " + wWhere
1159 query = "DELETE FROM `" + tname + "` WHERE " + wWhere + " LIMIT " + limit
1160 _web_query(query)
1161// retValue = global dbconn.GetAffectedRecs()
1162// global dbconn.Free(WcursorID)
1163 if(!retValue)
1164 debug
1165 }
1166 void Set(string field,string val)
1167 {
1168 if(wDateFields.Find(field)) // Trasforma la data
1169 {
1170 if(val.IsDate())
1171 val = time(val).Strftime("%Y-%m-%d")
1172 else if(val == "00-00-0000" || !val)
1173 val = "0000-00-00"
1174 else if(val != "0000-00-00")
1175 debug
1176 }
1177 if(wRec.name.Find(field))
1178 Error("Campo gia' impostato ma non scritto: manca un '" + tname + "db.Save()'?")
1179
1180 wRec.Add(field: "\"" + val.Substitute("\"","\\\"") + "\"")
1181 }
1182 void Save()
1183 {
1184 if(writeNew)
1185 {
1186 query = "INSERT INTO `" + tname + "` (" + wRec.name.ToString(",") + ") VALUES (" + wRec.ToString(",") + ")"
1187 writeNew = false
1188 }
1189 else
1190 {
1191 if(!wWhere)
1192 debug // dovrebbe essere stata definita da GetRecNum o da FetchRec
1193
1194 if(!wWhere.Begins(primaryKey + " "))
1195 debug
1196
1197 query = "UPDATE `" + tname + "` SET " + wRec.ToString(",",true) + " WHERE " + wWhere
1198 }
1199 if(global QBDEBUG)
1200 TSTART = time.Millisecs()
1201
1202 // Reset dell'array di scrittura
1203 wRec = array()
1204
1205 // error.Resume()
1206/* WcursorID = global dbconn.Exec(query)
1207 retValue = global dbconn.GetAffectedRecs()
1208 global dbconn.Free(WcursorID)
1209*/ // error.Suspend()
1210
1211 if(global QBDEBUG)
1212 {
1213 global CTOT++
1214 global LAST_EXEC = WcursorID
1215 // global CLOG += "[" + cursorID + "]"
1216 TEND = time.Millisecs()
1217 global QUELOG += time().Strftime("%H:%M:%S") + "." + (time.Millisecs()%1000) + " "
1218 global QUELOG += "[" + global CTOT + "]" + query + " (T: " + (TEND-TSTART) + ") " + "\r"
1219 }
1220
1221 }
1222 qb Copy()
1223 {
1224
1225 theCopy = qb(this.tname + ".db")
1226 theCopy._init_table()
1227
1228 theCopy.selectString = this.selectString
1229 theCopy.joinName = this.joinName
1230 theCopy.distinct = this.distinct
1231 theCopy.whereCond = this.whereCond
1232 theCopy.orderBy = this.orderBy
1233
1234 return theCopy
1235 }
1236 // debug
1237 string Dump(boolean doExec = true)
1238 {
1239 if(doExec)
1240 Exec()
1241
1242// totRecords = global dbconn.GetCurRecs()
1243 ret = "<table border='1'>"
1244 hhh = false
1245 while(fRec = FetchRec())
1246 {
1247 if(!hhh)
1248 {
1249 ret += "<tr><th colspan='" + fRec.name.dim + "'>" + totRecords + " results</th></tr>"
1250 ret += "<tr><th>" + fRec.name.ToString("</th><th>") + "</th></tr>"
1251 hhh = true
1252 }
1253 ret += "<tr><td style='font-weight: bold'>" + fRec.ToString("</td><td>") + "</td></tr>"
1254 }
1255 ret += "</table>"
1256
1257 return ret
1258 }
1259 string QueryString()
1260 {
1261 if(distinct)
1262 query = "SELECT DISTINCT " + distinct + " FROM `" + tname + "`" + joinName
1263 else
1264 query = "SELECT " + selectString + " FROM `" + tname + "`" + joinName
1265 if(whereCond)
1266 query += " WHERE " + whereCond
1267
1268 if(orderBy)
1269 query += " ORDER BY " + orderBy
1270
1271 return query
1272 }
1273 string tostring()
1274 {
1275 ret = "TABLE: '" + tname + "'"
1276 /* if(distinct)
1277 ret += " [DISTINCT " + distinct + "]"
1278 if(whereCond)
1279 ret += "\r[" + whereCond + "] {" + rec_count + "}" + (executed ? "EXECUTED" : "NOT EXECUTED")
1280 */
1281 if(distinct)
1282 query = "SELECT COUNT(DISTINCT " + distinct + ") FROM `" + tname + "`" + joinName
1283 else
1284 query = "SELECT COUNT(*) FROM `" + tname + "`" + joinName
1285 if(whereCond)
1286 query += " WHERE " + whereCond
1287 ret += " [QUERY: '" + query + "']"
1288
1289 if(orderBy)
1290 ret += " [order: '" + orderBy + "']"
1291
1292 return ret
1293 }
1294}
1295?>