Site Links
Home
Features
Documentation
Pricing & Order
Members Area
Support Options
UBBDev.com
UBBWiki.com
Who's Online Now
0 registered members (), 57 guests, and 235 spiders.
Key: Admin, Global Mod, Mod
Member Spotlight
Bill B
Bill B
Issaquah, WA
Posts: 370
Joined: October 2006
Show All Member Profiles 
Top Posters(30 Days)
isaac 22
Ruben 10
Gizmo 8
Geoff 3
Morgan 3
FREAK 2
Slaz 2
DennyP 1
Latest Photos
Testing to drag photos
Comfortable Cats
Test
BSA photos
Chinese Buddhist temple.
Previous Thread
Next Thread
Print Thread
Something overlookable #252082
01/16/13 12:21 AM
01/16/13 12:21 AM
SteveS  Offline
OP
addict
Joined: Mar 2007
Posts: 532
Massachusetts
And it has been, and I'm guessing that I'm not the only one.

I've been using the referrer log for a long time, and I have a busy forum. Even though I refer ( laugh ) to it all the time, I have not been maintaining it.

I was looking at the database, and the referrer log table had blossomed to close to two million (2,000,000!!!) rows. I have now pruned it to about 100,000 rows. Even that is taking up 26MB.

The same goes for the admin log, although it isn't nearly as large, and might be more important to keep. Mine is at 110,000 rows and 20MB.

Maybe in the future we could be able to set something up to be able to automatically delete log entries over a certain age.

Food for thought.


Steve

UBB.classic from 2000-2003
UBB.threads from 2003-present!
Express Hosting
Express Hosting "We are the official hosting company of UBB.threads. Ask us about our free migration services to migrate your UBB.threads installation."
Re: Something overlookable [Re: SteveS] #252083
01/16/13 01:28 AM
01/16/13 01:28 AM
SD  Offline
Former Developer
Joined: Apr 2007
Posts: 4,313
SoCal, USA
just do it with a custom island that runs every so often.

i do that all the time smile

Re: Something overlookable [Re: SD] #252084
01/16/13 12:43 PM
01/16/13 12:43 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Interesting idea SD.
Sounds like I could use this as a home made cron job for several items.
I never thought of that.

I assume you would also need to enable the Always Build option.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252085
01/16/13 02:23 PM
01/16/13 02:23 PM
SD  Offline
Former Developer
Joined: Apr 2007
Posts: 4,313
SoCal, USA
not really...

i just use an island that is displayed... like maybe the random image one....

then i add the special code in there..

so not only does it do the random image, but it does the 'cron' stuff too

smile

'always build' is too often anyway... with a random image every 15mins, i accomplish 2 goals smile

Re: Something overlookable [Re: SteveS] #252086
01/16/13 02:30 PM
01/16/13 02:30 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Oh, I see in your case you are hijacking a island to serve multiple uses.

Cool!


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252092
01/19/13 03:23 AM
01/19/13 03:23 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
This is such a good idea.

Would someone mind adding a "small sample" here? One that is written to be used in an Island.

For example: Delete referer logs > 60 days old.

And, again, on the recommended times. Every 15 minutes?? Wow... 100 times a day? Wouldn't it be more prudent to have it run less frequently?

Thanks!


--BIll B
Re: Something overlookable [Re: SteveS] #252120
01/22/13 12:13 AM
01/22/13 12:13 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
How about just how to delete all postings in Forum xyz that are > 60 days old.


--BIll B
Re: Something overlookable [Re: SteveS] #252129
01/26/13 05:25 AM
01/26/13 05:25 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
How about this??

SQL Query
/* PHP CODE HERE, IF NECESSARY */
/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<?PHP

DELETE * FROM "vccachat_org_-_chat".ubbt_TOPICS
WHERE forum_id = 25
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 5184000)

?>
EOF;
/* DO NOT CHANGE THE LINE ABOVE */

Now, If I only used the Island to do this one function, what would be a good way to trigger it once a day or so?

--Bill

Re: Something overlookable [Re: Bill B] #252130
01/26/13 01:42 PM
01/26/13 01:42 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
It should follow the cache time setting in the island you add it to.
So 60 minutes times 24 hours=1440 minutes
So possibly if you have say a static island then hijack that island and change the time from 0 to 1440.
See the USA island could be a candidate.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: Ruben] #252170
02/02/13 03:33 AM
02/02/13 03:33 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
Ah... very good Ruben. Yes, "See the USA" would be a great place to do that.

Now I just need the have the guts to allow an "island" to delete active records from my database!?!?!? ha ha!!
onoes


---Oh, one other question. Is it okay to have multiple "jobs" running in a single Island? Or might there be a "timing" issue if the jobs take too long? I think that I'd like to have 3 or 4 of these "jobs" set to run at different intervals, and I don't know whether I've got that many islands that I can hijack.


--BIll B
Re: Something overlookable [Re: Bill B] #252173
02/02/13 04:42 PM
02/02/13 04:42 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Go for the glory.
If faint of heart then make a backup first.
I have no idea on multiple processes. They don't take that much time. But this is new territory for myself as well.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252210
02/07/13 12:04 AM
02/07/13 12:04 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
Well, I got wrapped around the "Island" axle.... dang it.

The MySQL query works good. But I'm messing up when I insert it into a Custom Island. I've never really gotten the hang of exactly HOW it works. PHP at top, HTML at bottom. But.... ?

Do I need to add <? PHP or $Query ="
(Oh, I did look at all of the samples out on the Wiki, but couldn't extrapolate a solution.)

Here's a start:

Code
/* PHP CODE HERE, IF NECESSARY */

<?PHP
DELETE *
FROM "abc_org_-_chat".ubbt_TOPICS
WHERE forum_id = 22
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 5184000)
?>

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */


Or, is it more like this?
Code
/* PHP CODE HERE, IF NECESSARY */

<?PHP
$query = ("DELETE *
FROM "abct_org_-_chat".ubbt_TOPICS
WHERE forum_id = 22
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 15552000)")
?>

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */


--BIll B
Re: Something overlookable [Re: Bill B] #252211
02/07/13 01:42 AM
02/07/13 01:42 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 16,956
Portland, OR; USA
I think it'd more like:

Code
/* PHP CODE HERE, IF NECESSARY */
query

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */


I'm not entirely sure though, I haven't mucked with using islands like this...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Something overlookable [Re: SteveS] #252214
02/07/13 11:18 PM
02/07/13 11:18 PM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
Nope... I get this error message:

Quote:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /var/www/vhosts/vcca.org/httpdocs/forum/cache_builders/custom/portal_box_57.php on line 5

When I delete the query, the Island works okay.
And when I run the query using MySQL Query Browser, it works.

So it has something to do with how the Custom Island parses the coding. I wish that there was better documentation on this.

Does anybody else have a suggestion?


--BIll B
Re: Something overlookable [Re: SteveS] #252215
02/08/13 11:16 AM
02/08/13 11:16 AM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Looks like you need to define a name for the query and place quotes in the query and you need place a run command.

Take a look at example 4 at:
http://www.ubbwiki.com/wiki/view/79/custom-islands.html
SD did that one for current events in the calendar and it works.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252216
02/08/13 12:55 PM
02/08/13 12:55 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Another example as SD noted is Ricks quick hack on the random image island located on the bottom right at this site.

www.ubbcentral.com/forums/ubbthreads.php/ubb/showflat/Number/229905/


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252217
02/08/13 02:33 PM
02/08/13 02:33 PM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
ahhh... I looked at that once before -- and then went off on a different tangent... If I get this resolved, then I'll submit it as another example for the Wiki. Thanks for the tips.


--BIll B
Re: Something overlookable [Re: Bill B] #252218
02/08/13 02:44 PM
02/08/13 02:44 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Okay I just tried this for admin logs and it works fine but I just used the simple truncate command in the query.
It was my test board so it got to be a pain adding rows back to the table for testing.
PHP Code
/* PHP CODE HERE */
$sql = "TRUNCATE TABLE `ubbt_ADMIN_LOG`";
$dbh->do_query($sql);

/* BODY HERE */
$body = <<<EOF
SQL to clear logs
EOF; 

The part about sql to clear logs was just to indicate to me that that island did appear on the forum.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: Bill B] #252219
02/08/13 04:10 PM
02/08/13 04:10 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
Syntax could probably be cleaner but for the logs I have left it now as:
PHP Code
/* PHP CODE HERE, IF NECESSARY */
$sql = "TRUNCATE TABLE `ubbt_ADMIN_LOG`";
$sql2 = "TRUNCATE TABLE `ubbt_REFERER_LOG`";
$dbh->do_query($sql);
$dbh->do_query($sql2);
/* BODY HERE */ 


Then I set the cache to like every 3 days.
Works perfect as is.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252220
02/08/13 04:14 PM
02/08/13 04:14 PM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
okay, got it to work... whew... too many nickle-dime issues.

This should have been easy but I must have a hidden desire to succeed ONLY when it's difficult!!!! ha ha...

I'll post full code tonight along with info for a new WIKI Example!!!
grin laugh cool


--BIll B
Re: Something overlookable [Re: SteveS] #252221
02/08/13 07:53 PM
02/08/13 07:53 PM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
HOW TO DELETE OLD THREADS AUTOMATICALLY AFTER 6 MONTHS

Add this to any CUSTOM ISLAND. Using an island with a static graphic works good.
Set the Island "Cache Time" to 1440. That way it will only run the query once a day.

Code
/* PHP CODE HERE, IF NECESSARY */

$sql = "DELETE FROM ubbt_TOPICS
WHERE forum_id = 44
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 15552000)";

$dbh->do_query($sql);

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/some-image.jpg" width="108" height="25" align=center alt="Good times!">Some Text Message

EOF;
/* DO NOT CHANGE THE LINE ABOVE */

You can change the "age" of the files that it deletes by modifying seconds (15552000) for the Unix calculation. (Example: Three months would be half of this number.)

---WARNING: DO NOT ADD THIS CODE UNTIL YOU READ THE FOLLOWING POSTING FIRST--- IT MAY NOT BE ADVISABLE TO DO IT TO LARGE, OR ACTIVE FORUMS---
Warning this code could break your site and may not be recoverable without data backups prior to running.
The user that created this is well aware of the risks involved

Last edited by Ruben; 02/12/13 06:13 PM. Reason: Added comment by Ruben

--BIll B
Re: Something overlookable [Re: SteveS] #252222
02/08/13 08:02 PM
02/08/13 08:02 PM
SD  Offline
Former Developer
Joined: Apr 2007
Posts: 4,313
SoCal, USA
HOW TO DELETE OLD THREADS AUTOMATICALLY AFTER 6 MONTHS and degrade your database...

just deleting from ubbt_TOPICS does NOT delete all the other supporting table entries that make up a topic!

you have ubbt_POSTS table to consider, which now has orphaned posts with no topic..

watch lists, where people might have been watching that topic.

possible polls.

possible files ( attachments ) that have been added to individual posts and more!

calendar events, announcements, stickies....

so do NOT consider the above CI to be what i'd recommend. matter of fact, i highly discourage it.

just open up scripts/doexpirethreads.inc.php to see all that needs to be taken into account smile

this is probably a ubbdev kinda topic anyway.. 2c

Re: Something overlookable [Re: SD] #252224
02/09/13 12:10 AM
02/09/13 12:10 AM
Bill B  Offline

enthusiast
Joined: Oct 2006
Posts: 370
Issaquah, WA
Actually I meant to inquire about the related posts that might be orphaned when this is done. But I got in a rush this afternoon while I was drafting a comment - since we had a whole house-load of grand-kids come over. So I just pressed "SEND".

I agree that that is not a good situation. And I've thought previously about trying to hold this discussion in UBBDEV, but 1) nobody is there any more, and 2) well... nobody is there any more. :-)

It's really a shame that this "basic function" isn't provided with the core code. No way that it should be a "hack". And when I say that, I mean as an AUTOMATED function.


--BIll B
Re: Something overlookable [Re: SteveS] #252225
02/09/13 12:40 AM
02/09/13 12:40 AM
SD  Offline
Former Developer
Joined: Apr 2007
Posts: 4,313
SoCal, USA
there are any number of 'automated' niceties that could be added, however my post was to dissuade anyone from using that particular piece of code, since it harms ALL forums who use it ( large, small, medium, active, inactive )

now if Brooks wants to add a 'mods and hacks' forum here and we actively use such a forum, i'm more than willing to participate, since ubbdev is essentially dormant.

Re: Something overlookable [Re: SteveS] #252226
02/09/13 12:45 AM
02/09/13 12:45 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 16,956
Portland, OR; USA
Well, UBBDev is dormant as a lot of our userbase went elsewhere due to threads basically being shelved for quite a while and users went elsewhere :/...

I'm more than willing to take on suggestions though; any mod requests (or feature requests) I've seen here that look worthwhile I've slapped together and published there (and will only publish there).


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Something overlookable [Re: SteveS] #252228
02/09/13 02:07 PM
02/09/13 02:07 PM
Ruben  Offline
Joined: Dec 2003
Posts: 5,920
Lutz,FL
I agree SD.
This topic started about clearing the admin/referer logs. Which is a simple single table to clean.
Then topic drift went onto pruning posts. Which is dangerous since the data is spread out among several tables.
Since we already have a tool for pruning.
Control Panel ยป Prune Topics
It would be best to mimic or hack that process


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Something overlookable [Re: SteveS] #252238
02/09/13 07:32 PM
02/09/13 07:32 PM
SD  Offline
Former Developer
Joined: Apr 2007
Posts: 4,313
SoCal, USA
yup, i WILL say that Bill B has the right attitude though smile

just don't want to let that one Content Island example get used anywhere wink


Shout Box
Today's Birthdays
No Birthdays
Recent Topics
UBB.threads 7.6.2 - Released
by isaac. 09/17/18 08:08 AM
7.6.2 Changelog Discussion
by isaac. 09/17/18 06:35 AM
Upgrade Documentation
by DennyP. 09/15/18 03:55 PM
PHP 7.2
by Geoff. 09/12/18 10:22 PM
Email Cleanup-Is it possible?
by Ruben. 09/06/18 11:25 AM
Forum Statistics
Forums36
Topics35,139
Posts191,443
Members12,106
Most Online978
Jun 24th, 2007
Random Image
Powered by UBB.threads™ PHP Forum Software 7.6.2