TbsSQL v3.0 beta - TRACE & QUERY

[TomH]

Hello Skrol29,

Very nice to see you added debug/trace & cache features to TbsSQL.

You know that I am limited in my understanding so thank you in advance
for your patience...

--- TRACE ---
When I do $Db->Connect($srv,$uid,$pwd,$db,$drv='',$mode=TBSSQL_TRACE);
the result page shows me the 'Connection String' & 'SQL' at the top
of page.

How do I get it to show the actual query result?

--- CACHE ---
What exactly is in the cache, the full query object/array?

I need an example of how to set the cache and to use the cache
result?

And how to disable caching the query for an individual query?

Thanks you every day for TBS,
TomH

[Skrol29]

Hi TomH,

The help file should answer most of these questions, but let's explain.

> the result page shows me the 'Connection String' & 'SQL' at the top of page.
> How do I get it to show the actual query result?

The Trace mode display all SQL queries given to the server, not the result provided by the server.
But you can easily check the result yourself with a var_export();
example:
  $x = $Db->GetRows("select * from table1");
  var_export($x);

> What exactly is in the cache, the full query object/array?

The cache contains both the SQL query string and the full result given by the server. They are saved as PHP scripts.
Example of a cache contents:
---------------------------
<?php
CacheSql='select * from table1';
$Data=array ( 0 => array ('id' => '1', 'name' => 'toto',) , 1 => array ('id' => '2', 'name' => 'tutu',) );
---------------------------
Since the result is saved as a PHP script, it can never be read by a web user. TbsSQL is different from ezSQL on that point, ezSQL has cache files that can be read by web user if they have access to the directory.
The other advantage is that TbsSQL cache files can be read much faster than ezSQL files because there is no need to serialize/unserialize data.

> I need an example of how to set the  cache and to use the cache result?

They are two examples below. But you not need to use the cache result, TbsSQL found and manage the cache itself when you enable the cache system.

> And how to disable caching the query for an individual query?

With $Db->CacheTimeout you define the cache behavior for all queries,
while with $Db->CacheSpecialTimeout you define the cache behavior for the next query only.

Example #1:
---------------------------
$Db->CacheTimeout = 2 * TBSSQL_1DAY; // cache is enabled for all queries, timeout is 2 days
$x = $Db->GetRows("select * from table1"); // this query use the cache
...
$Db->CacheSpecialTimeout = TBSSQL_NOCACHE; // the very next query won't use the cache
$x = $Db->GetRows("select * from table2"); // so this query doesn't use the cache
$x = $Db->GetRows("select * from table3"); // this query use the cache, timeout is 2 days
---------------------------

Example #2:
---------------------------
$Db->CacheTimeout = false; // cache is disabled for all queries, in fact you do not need this line because this is the default value
$x = $Db->GetRows("select * from table1"); // this query doesn't use the cache
...
$Db->CacheSpecialTimeout = 2 * TBSSQL_1DAY; // the very next query will do use the cache, timeout is 2 days
$x = $Db->GetRows("select * from table2"); // so this query does use the cache
$x = $Db->GetRows("select * from table3"); // this query doesn't use the cache
---------------------------

If you have a better name for property CacheSpecialTimeout, feel free to tell suggestions.

The TbsSQL Cache System has also an AutoClear feature which is enabled by default with the cache. AutoClear try to delete very old cache files that have not be updated since a long time. It is a kind of garbage collector for cache file. If you change some queries in your code, or if you use some debuging queries on time to time, then they cache files will be automatically deleted if they stay too long. AutoClear can be disabled if needed.

---------------------
Skrol29
www.tinybutstrong.com
---------------------

[TomH]

Skrol29, thanks for the very thorough reply, as usual ;)

I had already read the help file, but did not comprehend the subtleties - your
examples did bring it to life in an understandable fashion. Thank you.

Since I was fiddling with the new features, I practiced and made an example app
that shows the cache and trace features somewhat.

The var_export does show the result but is not easy to read. Since I am used to
ezSQL, I find that it is VERY helpful to be able to see the query results -- it
greatly facilitates coding, especially the templates, as I do development work.

I decided to see how hard it would be to use the sql result to build a
generalized "result table". I ended up creating a small function for that, kind
of clunky, but it works.

If you look at it ( http://tomhenry.us/tbs3/ ) you see that with my little added
function I get almost the same as ezSQL $db->debug() function. Of course, it
would be better if it was integrated native within the TRACE feature ;) If you
decide to add the result table output to the trace, I would love to see ow you
will do it.

Some comments now afterwords, as I only LEARN FROM DOING myself.

* It seems strange (to me) to not get the query results as $Db -- if the query
results were returned to $Db then it would be _very_ easy to migrate from ezSQL
to TbsSQL and then us poor users would be 100% TBS (not like being 100& Microsoft).

* Am I missing something? Results only returned as array of arrays, no option
for object result?

* Also find it unexpected that cannot set $mode directly in the
$Db->Connect('glob_var') statement

* I can set the mode ok, but cannot find the current value of $Db->Mode (??)

* Understand now about the name $Db->CacheSpecialTimeout being unclear... maybe
something like $Db->CacheTimeoutOverride might give more of a sense of what it
is doing

That's all I can think of just now, as I use it more and have thoughts I will
continue.

Thanks for TBS every day,
TomH

> Le 18/06/2010 20:07, TomH a �crit :
>> Hello Skrol29,
>>
>> Very nice to see you added debug/trace& cache features to TbsSQL.

>>
>> You know that I am limited in my understanding so thank you in advance
>> for your patience...
>>
>> --- TRACE ---
>> When I do $Db->Connect($srv,$uid,$pwd,$db,$drv='',$mode=TBSSQL_TRACE);
>> the result page shows me the 'Connection String'& 'SQL' at the top
>> of page.
>>
>> How do I get it to show the actual query result?
>>
>> --- CACHE ---
>> What exactly is in the cache, the full query object/array?
>>
>> I need an example of how to set the cache and to use the cache
>> result?
>>
>> And how to disable caching the query for an individual query?
>>
>> Thanks you every day for TBS,
>> TomH
>>
>>
>

> --
> You received this message because you are subscribed to "TinyButStrong
> next version".
> Post to this group: send email to tbs-...@googlegroups.com
> Subscribe: send email to tbs-next+...@googlegroups.com
> Unsubscribe: send email to tbs-next+u...@googlegroups.com
> More options: http://groups.google.com/group/tbs-next

[Skrol29]

Hi TomH,

This is all very interesting.
The idea of a debug result is nice. Your demo is very convincing.  I'll try to do it, maybe a different way.

> * It seems strange (to me) to not get the query results as $Db -- if  the query results were returned
> to $Db then it would be _very_ easy to migrate from ezSQL to TbsSQL and then us poor users
> would be 100% TBS (not like being 100& Microsoft).

With ezSQL, when you send a SELECT statement to the server, then the result is always backup into property $Db->last_result. Even if you call $data = $Db->get_results("select...") then it is backuped. I think this is not good because it means there is probably data in double in the memory. In fact PHP doesn't double the memory when you're do $a= $b, until you modify or scann $a or $b.
I've already worked with queries that output 40 Mb of data. I would be very dispointed to know that the connectivity tool keeps them in memory for any further use, and I cannot help it.

> * Am I missing something? Results only returned as array of arrays, no option for object result?

That's what I'm working on now.
Even if I didn't see until now the technical interest in having records as standard objects (class=stdClass) instead of arrays (light me if I'm wrong), I agree it gives an intellectual comfort when working with OOP. Active Records are something else.
That why I'd like to give the possibility to retrieve that data as objects but not only standard objects. I'm still searching how to enabled the coder to choose the class or even the instance of the object returned by the methods.
Tell me what you think about the following:

New methods:
  $Db->GetRowObj($type, $sql);
  $Db->GetRowsObj($type, $sql);
(note that $type must be the first argument because TbsSQL methods accept optional arguments after $sql.)
If $type is an empty string (''), then the methods return standard objects.
If $type is an non empty string, then the methods return new instances of the corresponding class.
If $type is an object, then GetRowObj() return the same object, and GetRowsObj() returns cloned objects.

> * Also find it unexpected that cannot set $mode directly in the $Db->Connect('glob_var') statement

That's a good idea. I'll add that.

> * I can set the mode ok, but cannot find the current value of $Db->Mode (??)

It is a property, so you can retrieve is using echo $Db->Mode;

> * Understand now about the name $Db->CacheSpecialTimeout being unclear... maybe something like $Db->CacheTimeoutOverride might give more of a sense of what it is doing

What do you think of the following:

$Db->EphemeralCacheTimeout (my favorite)
$Db->EphemeralDebugResult

$Db->OccasionalCacheTimeout
$Db->OccasionalDebugResult

$Db->MomentaryCacheTimeout
$Db->MomentaryDebugResult

---------------------
Skrol29
www.tinybutstrong.com
---------------------

[TomH]

Hello again,

Thanks you for the compliments ;)

Skrol29 wrote:
> Hi TomH,
>
> This is all very interesting.
> The idea of a debug result is nice. Your demo is very convincing. I'll
> try to do it, maybe a different way.

My code is your code as far as that goes...

>
> > * Am I missing something? Results only returned as array of arrays,
> no option for object result?
>
> That's what I'm working on now.
> Even if I didn't see until now the technical interest in having records
> as standard objects (class=stdClass) instead of arrays (light me if I'm
> wrong), I agree it gives an intellectual comfort when working with OOP.
> Active Records are something else.
> That why I'd like to give the possibility to retrieve that data as
> objects but not only standard objects. I'm still searching how to
> enabled the coder to choose the class or even the instance of the object
> returned by the methods.
> Tell me what you think about the following:
>
> New methods:
> $Db->GetRowObj($type, $sql);
> $Db->GetRowsObj($type, $sql);
> (note that $type must be the first argument because TbsSQL methods
> accept optional arguments after $sql.)
> If $type is an empty string (''), then the methods return standard objects.
> If $type is an non empty string, then the methods return new instances
> of the corresponding class.
> If $type is an object, then GetRowObj() return the same object, and
> GetRowsObj() returns cloned objects.

Personally, if you are going to use the $type param to lead to creating object
result then why not just $Db->GetRows($type, $sql); it simplifies understanding
(for me)

> > * I can set the mode ok, but cannot find the current value of
> $Db->Mode (??)
>
> It is a property, so you can retrieve is using echo $Db->Mode;

I discovered this issue when I tried to place the Mode value in the header of
the result table
$output = "[TbsSQL] ".$Db->Mode." mode";
echo $output;
screen result: "[TbsSQL] 3 mode"

> > * Understand now about the name $Db->CacheSpecialTimeout being
> unclear... maybe something like $Db->CacheTimeoutOverride might give
> more of a sense of what it is doing
>
> What do you think of the following:
>
> $Db->EphemeralCacheTimeout (my favorite)
> $Db->EphemeralDebugResult
>
> $Db->OccasionalCacheTimeout
> $Db->OccasionalDebugResult
>
> $Db->MomentaryCacheTimeout
> $Db->MomentaryDebugResult

They are all backwards (logically) to me,
my mind expects $Db->CacheTimeoutSomething

but if it's not $Db->CacheTimeoutOverride
the next best is $Db->CacheTimeoutMomentary

Cheers,
TomH

[Skrol29]

>> > * Understand now about the name $Db->CacheSpecialTimeout being
>> unclear... maybe something like $Db->CacheTimeoutOverride might give
>> more of a sense of what it is doing

>> What do you think of the following:
>> $Db->EphemeralCacheTimeout (my favorite)
>> $Db->EphemeralDebugResult
>> $Db->OccasionalCacheTimeout
>> $Db->OccasionalDebugResult
>> $Db->MomentaryCacheTimeout
>> $Db->MomentaryDebugResult

>They are all backwards (logically) to me,
>my mind expects $Db->CacheTimeoutSomething
>
>but if it's not $Db->CacheTimeoutOverride
>the next best is $Db->CacheTimeoutMomentary

Override has the idea of replacement, but not of the duration.

$Db->CacheTimeoutOnce ?
$Db->CacheTimeoutOnlyOnce ?
$Db->CacheTimeoutSingle ?
$Db->CacheTimeoutOneShot ?

Regards,
Skrol29

-----Message d'origine-----
De : tbs-...@googlegroups.com [mailto:tbs-...@googlegroups.com] De la part
de TomH
Envoyé : dimanche 20 juin 2010 01:06
À : tbs-...@googlegroups.com
Objet : Re: [tbs-next] TbsSQL v3.0 beta - TRACE & QUERY

Hello again,

Cheers,
TomH

--

[Skrol29]

Hi,

There is something I can do: methods Row() Rows() can both support a double
syntaxe:
$Db->Rows($sql, $val1, $val2,...) wich return arrays
and
$Db->Rows($type, $sql, $val1, $val2,...) wich return arrays or objects

If you think this is a good solution, I can try a new beta.
I'm including your debug grid in the next beta.

Regards,
Skrol29

-----Message d'origine-----
De : tbs-...@googlegroups.com [mailto:tbs-...@googlegroups.com] De la part
de TomH
Envoyé : dimanche 20 juin 2010 01:06
À : tbs-...@googlegroups.com
Objet : Re: [tbs-next] TbsSQL v3.0 beta - TRACE & QUERY

Hello again,

Cheers,
TomH

--

[TomH]

Hello Skrol29,

Good timing on the reply, this morning I just completed a revision to the debug
grid...
see http://tomhenry.us/tbs3/emailcloak/emailcloak_v3_console_Z.php

This shows a better grouping of the information (I think) -- keeping the
trace/error statements together with the query result table.

In my hack of your class I did two things...

(1) added a switch at the top of the 'constructor'
----------------------------------------
function __construct( $srv='',$uid='',$pwd='',$db='',$drv='',$Mode=TBSSQL_NORMAL) {

// === TomH === Trying to set a flag here to control echo -or- return
$this->debug_echo_is_on = true;

----------------------------------------

(2) modified the _Message function so that it output either by 'echo' or by
return of array of all messages

----------------------------------------
// == TomH == =========================
function _Message($Txt,$Color='#FF0000') {
if ($this->Mode!=TBSSQL_SILENT) {
// == TomH == Use flag here to switch between echo and return TraceMessage

if($this->debug_echo_is_on){
echo '<div style="color: '.$Color.';">[TbsSql]
'.nl2br(htmlentities($Txt)).'</div>'."\r\n"; // == TomH == original echo
}
// == TomH == Create new property for 'return' of array for all messages
// == TomH == Workaround: hide db connection message
if(!strstr($Txt,'Conn')){
$this->TraceMessage[] = '<div style="color: '.$Color.';">[TbsSql]
'.nl2br(htmlentities($Txt)).'</div>'."\r\n"; // return added as array ==TomH ==
}

flush();
}
return false;
}
// == TomH == =========================
----------------------------------------

I could not make enough sense of your sophisticated source code, except to find
the '_Message' function.

What is really needed is to create all error/debug/data output separately for
each attempted query

You will be able to imagine the problems with this too simplistic approach...
and it shows up in places like the workaround in _Message to exclude the
'Connection' message from trace (because it corrupted the synchronization
between the 'TraceMessages[]' array and the '$Data' )

Here is my PHP code for the application -- it illustrates what's wrong with my
hack ;) --- it is full of various conditions and switches because I am unable to
do any kind of serious modifications to the class and result in lots of clumsy
code in the application.

--------------------------------------
Instead of putting code here, I am attaching all the files to make it easier to
read -- as emails can make reading code very difficult.
--------------------------------------
Any questions just ask.

Cheers, and thanks for the inspiration,
TomH

Skrol29 wrote:
> Hi,
>
> There is something I can do: methods Row() Rows() can both support a double
> syntaxe:
> $Db->Rows($sql, $val1, $val2,...) wich return arrays
> and
> $Db->Rows($type, $sql, $val1, $val2,...) wich return arrays or objects
>
> If you think this is a good solution, I can try a new beta.
> I'm including your debug grid in the next beta.
>
> Regards,
> Skrol29
>
>

> -----Message d'origine-----http://tomhenry.us/tbs3/emailcloak/emailcloak_v3_console_Z.php

> De : tbs-...@googlegroups.com [mailto:tbs-...@googlegroups.com] De la part
> de TomH

> Envoy� : dimanche 20 juin 2010 01:06
> � : tbs-...@googlegroups.com
> Objet : Re: [tbs-next] TbsSQL v3.0 beta - TRACE& QUERY