-------- Original-Nachricht -------- > Datum: Tue, 28 Jul 2009 06:37:18 -0500 > Von: "Todd T. Fries" <[email protected]> > An: Steve <[email protected]> > CC: [email protected] > Betreff: Re: [Dspam-user] dspam_merge and mysql errors
> Steve, > Hallo Todd, > Maybe dspam_merge might abstract things a bit and use the below for > mysql? I wonder what the postgresql equivalent would be since I > use dspam and postgresql. > Do you have any feedback regarding the function I have posted for PostgreSQL? Does it work? Fast? Slow? Anything not working the way it should? etc.... > Thanks, > // Steve > Penned by Steve on 20090705 4:33.57, we have: > | Hallo Mark, > | > | here the SQL clause doing what dspam_merge does. Assuming you would like > to merge the tokens from USER1, USER2 and USER3 into the data of USER2000. > | > | Assuming: > | - USER2000 has uid 2000 > | - USER1 has uid 1 > | - USER2 has uid 2 > | - USER3 has uid 3 > | > | Then the SQL clause for merging the tokens would be: > | INSERT INTO dspam_token_data(uid,token,spam_hits,innocent_hits,last_hit) > SELECT 2000,token,SUM(spam_hits),SUM(innocent_hits),MAX(last_hit) FROM > dspam_token_data WHERE uid IN(1,2,3) GROUP BY token ON DUPLICATE KEY UPDATE > spam_hits=VALUES(spam_hits)+spam_hits,innocent_hits=VALUES(innocent_hits)+innocent_hits,last_hit=IF(VALUES(last_hit)>last_hit,VALUES(last_hit),last_hit); > | > | > | And the SQL clause for cumulating the statistics would be: > | INSERT INTO > dspam_stats(uid,spam_learned,innocent_learned,spam_misclassified,innocent_misclassified,spam_corpusfed,innocent_corpusfed,spam_classified,innocent_classified) > SELECT > 2000,SUM(spam_learned),SUM(innocent_learned),SUM(spam_misclassified),SUM(innocent_misclassified),SUM(spam_corpusfed),SUM(innocent_corpusfed),SUM(spam_classified),SUM(innocent_classified) > FROM dspam_stats WHERE uid IN(1,2,3) GROUP BY uid ON DUPLICATE KEY UPDATE > spam_learned=VALUES(spam_learned)+spam_learned,innocent_learned=VALUES(innocent_learned)+innocent_learned,spam_misclassified=VALUES(spam_misclassified)+spam_misclassified,innocent_misclassified=VALUES(innocent_misclassified)+innocent_misclassified,spam_corpusfed=VALUES(spam_corpusfed)+spam_corpusfed,innocent_corpusfed=VALUES(innocent_corpusfed)+innocent_corpusfed,spam_classified=VALUES(spam_classified)+spam_classified,innocent_classified=VALUES(innocent_classified)+innocent_classified; > | > | > | I know, I know. It's a crazy long SQL clause. Anyway... it should be > faster then doing it with dspam_merge. > | > | Let me know how it went for you. > | > | > | cheers, > | > | Steve > | > | > | -------- Original-Nachricht -------- > | > Datum: Fri, 03 Jul 2009 18:27:25 +0200 > | > Von: "Steve" <[email protected]> > | > An: > | > CC: [email protected] > | > Betreff: Re: [Dspam-user] dspam_merge and mysql errors > | > | > Hallo Mark, > | > > | > > | > > It does seem to be a somewhat unhappy tool :) If you ask it for > help, > | > > it barfs in a nasty way. I can send that output along too if you'd > like > | > > that as well. > | > > > | > I am going to fix that. I must! > | > > | > > | > > Absolutely acceptable. > | > > > | > I am a bit in a rush right now so I am going to send you a SQL clause > that > | > does something like dspam_merge but not exactly what dspam_merge does. > | > dspam_merge is able to merge the tokens from USER1 into the tokens of > USER2. > | > The SQL clause from here will merge tokens from USER1, USER2, USERx > into a > | > new user USERy. > | > > | > Assuming: > | > - USERy has uid 2000 > | > - USER1 has uid 1 > | > - USER2 has uid 2 > | > - USERx has uid 3 > | > > | > Then you should first drop all the tokens for USERy: > | > DELETE FROM dspam_token_data WHERE uid=2000; > | > > | > And then you can use one SQL clause to merge the tokens from USER1, > USER2 > | > and USERx into USERy: > | > > | > INSERT INTO dspam_token_data > (uid,token,spam_hits,innocent_hits,last_hit) > | > SELECT 2000,token,SUM(spam_hits),SUM(innocent_hits),MAX(last_hit) FROM > | > dspam_token_data WHERE uid IN (1,2,3) GROUP BY token; > | > > | > To be 100% like dspam_merge I would need to do "ON DUPLICATE" but > right > | > now I can't test that since I am on the run and I don't want to send > you > | > something non working. But I will quickly do the stuff over the > weekend and > | > send it here. I have to look if I can glue everything into SQL or if I > need to > | > make a quick script? Anyway... I will send the script here to the > list. > | > Just give me the weekend to look at it. > | > > | > > | > > pace > | > > > | > Steve > | > -- > | > GRATIS f?r alle GMX-Mitglieder: Die maxdome Movie-FLAT! > | > Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 > | > > | > > ------------------------------------------------------------------------------ > | > _______________________________________________ > | > Dspam-user mailing list > | > [email protected] > | > https://lists.sourceforge.net/lists/listinfo/dspam-user > | > | -- > | GRATIS f?r alle GMX-Mitglieder: Die maxdome Movie-FLAT! > | Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 > | > | > ------------------------------------------------------------------------------ > | _______________________________________________ > | Dspam-user mailing list > | [email protected] > | https://lists.sourceforge.net/lists/listinfo/dspam-user > > -- > Todd Fries .. [email protected] > > _____________________________________________ > | \ 1.636.410.0632 (voice) > | Free Daemon Consulting, LLC \ 1.405.227.9094 (voice) > | http://FreeDaemonConsulting.com \ 1.866.792.3418 (FAX) > | "..in support of free software solutions." \ sip:[email protected] > | \ sip:[email protected] > \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ > > 37E7 D3EB 74D0 8D66 A68D B866 0326 204E 3F42 004A > http://todd.fries.net/pgp.txt -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Dspam-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspam-user
