[Spamassassin Wiki] Trivial Update of "UsingSQL" by QuinnComendant

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Spamassassin Wiki" for change notification.

The following page has been changed by QuinnComendant:
http://wiki.apache.org/spamassassin/UsingSQL

The comment on the change is:
Changed table names to userpref to be consistent.

------------------------------------------------------------------------------
  Personally, I use the following table structure for my user prefs...  This allows my web interface to add additional information to the items via the '''descript''' field, and also has added, added_by, and modified fields.
  
  {{{
- CREATE TABLE `userprefs` (
+ CREATE TABLE `userpref` (
    `id` int(8) unsigned NOT NULL auto_increment,
    `username` varchar(128) NOT NULL default '',
    `preference` varchar(64) NOT NULL default '',
@@ -45, +45 @@

  ) TYPE=MyISAM COMMENT='Spamassassin Preferences';
  }}}
  
- The userprefs table as recommended by '''sql/userpref_mysql.sql''' in your SA tarball is..
+ The userpref table as recommended by '''sql/userpref_mysql.sql''' in your SA tarball is..
  
  {{{
  CREATE TABLE userpref (
@@ -58, +58 @@

  ) TYPE=MyISAM;
  }}}
  
- Not alot of difference... use whatever you want.   If you are planning on having 1000's of userprefs via SQL, i would scale it back and not have all the extra fields.
+ Not alot of difference... use whatever you want.   If you are planning on having 1000's of user prefs via SQL, i would scale it back and not have all the extra fields.
  
- To get this table active in your mysql database, save the syntax above to a file named '''userprefs.sql''' and run
+ To get this table active in your mysql database, save the syntax above to a file named '''userpref.sql''' and run
  
  {{{
- # cat userprefs.sql | mysql <DB>
+ # cat userpref.sql | mysql <DB>
  }}}
  
  where '''<DB>''' is the database you want to store this table.  If you have not already created it, you will want to run something like..
@@ -77, +77 @@

  and then
  
  {{{
- # cat userprefs.sql | mysql spamassassin
+ # cat userpref.sql | mysql spamassassin
  }}}
  
- Once you have the table active, you need to assign access to it.  All SpamAssassin needs is '''SELECT''' access to the userprefs table.   Running
+ Once you have the table active, you need to assign access to it.  All SpamAssassin needs is '''SELECT''' access to the userpref table.   Running
  
  {{{
  # mysql spamassassin
- > GRANT SELECT ON spamassassin.userprefs TO username IDENTIFIED BY 'password';
+ > GRANT SELECT ON spamassassin.userpref TO username IDENTIFIED BY 'password';
  > exit;
  }}}
  
@@ -95, +95 @@

  
  SpamAssassin 3.0 supports the config option '''user_scores_sql_custom_query''' - Using this feature, one can accomplish any number of custom configuration for supporting tiered user preferences.
  
- However, for proper sorting of userprefs when using Per-Domain settings, one must be careful on how SQL sorts the results.  To make this work properly, you may be required to change how you reference your '''GLOBAL''' and '''PER-DOMAIN''' config preferences.   To acheive proper sorting of SQL prefs, I use the following custom_query:
+ However, for proper sorting of userpref when using Per-Domain settings, one must be careful on how SQL sorts the results.  To make this work properly, you may be required to change how you reference your '''GLOBAL''' and '''PER-DOMAIN''' config preferences.   To acheive proper sorting of SQL prefs, I use the following custom_query:
  
  {{{
  user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR 
@@ -111, +111 @@

  Here is what the query produces...
  
  {{{
- mysql> select username,preference,value from sa_rules WHERE
+ mysql> select username,preference,value from userpref WHERE
  (username='$GLOBAL' OR username='%nmgi.com' OR
  username='dallase@nmgi.com') ORDER by username ASC;
  +------------------+-------------------------+------------------------+
@@ -175, +175 @@

  
  {{{
  # mysql spamassassin;
- > INSERT INTO userprefs (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
+ > INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
- > INSERT INTO userprefs (username,preference,value) VALUES ('%nmgi.com','required_hits','4.5');
+ > INSERT INTO userpref (username,preference,value) VALUES ('%nmgi.com','required_hits','4.5');
- > INSERT INTO userprefs (username,preference,value) VALUES ('dallase@nmgi.com','required_hits','4.0');
+ > INSERT INTO userpref (username,preference,value) VALUES ('dallase@nmgi.com','required_hits','4.0');
  > exit;
  }}}
  
@@ -282, +282 @@

  
  content by DallasEngelken
  ----
- CategoryResources
+ CategoryResources CategoryInstall