[Savannah-cvs] [SqlQueries] (edit) mention scripts for tracker/forum explorations and spam

[karlberry]

??changed:
-The following are not related to stats, but making a new page seemed worse.  
Here are some queries to get a grip on the mysql databases for savannah:
-
-mysql savane     # on the internal subhost
-show databases;           # show all databases in the installation
-show tables from savane;  # show tables in the main database
-show columns from savane.groups;  # show columns in one of the main tables
-
-Random info: it seems the top-level "news" posts are saved in the 
savane.news_bytes table, with the group_id field recording the project and the 
id field the unique id for each post.  The savane.forum table is apparently 
only for replies to these news posts; its group_forum_id column matches up with 
the id column in news_bytes.
-
-
-Some stats as of 20051217.
(The following are not related to stats, but making a new page seemed worse.)  
Here are some queries to get a grip on the mysql databases for savannah:

  mysql savane     # on the internal subhost
  show databases;           # show all databases in the installation
  show tables from savane;  # show tables in the main database
  show columns from savane.groups;  # show columns in one of the main tables

It seems the top-level "news" posts are saved in the savane.news_bytes table, 
with the group_id field recording the project and the id field the unique id 
for each post.  The savane.forum table is apparently only for replies to these 
news posts; its group_forum_id column matches up with the forum_id column in 
news_bytes.  Set display type of news comments to flat or replies-to-comments 
will not be displayed.

On the internal host, in /root/bin are some scripts:
  forum-remove-by-id, items-list-by-id, items-remove-by-id
The first silently removes all forum posts for ids listed on the cmdline.
The second lists all posts in all trackers, plus forum posts, for the given id,
the third removes all posts in all trackers, plus forum posts.

This was all to deal with the overwhelming accumulated spam in news posts 
comments, sept 2011.


Some (old) stats, as of 20051217.

??changed:
-mysql> select count(distinct(user_id)) from user_group where 
user_group.admin_flags='A';
mysql> select count(distinct(user_id)) from user_group where 
user_group.admin_flags="A";

??changed:
-mysql> select count(distinct(user.user_id)) from user,user_group where 
authorized_keys is not null and authorized_keys != "" and 
user.user_id=user_group.user_id and user_group.admin_flags='A';
mysql> select count(distinct(user.user_id)) from user,user_group where 
authorized_keys is not null and authorized_keys != "" and 
user.user_id=user_group.user_id and user_group.admin_flags="A";

--
forwarded from http://savannah.gnu.org/maintenance/address@hidden/maintenance