SQL queries and info

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 mgt1 host, in /opt/administration/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.

Old stats

Some (old) stats, as of 20051217.

As you can see, we have actually few CVS users; most of our traffic is from people (spiders?) who browse resources.

# Number of users with shell access (!)
mysql> select count(*) from user where authorized_keys is not null and authorized_keys != "";
+----------+
| count(*) |
+----------+
|     3048 |
+----------+

# Number of admins
mysql> select count(distinct(user_id)) from user_group where user_group.admin_flags="A";
+--------------------------+
| count(distinct(user_id)) |
+--------------------------+
|                     2900 |
+--------------------------+

# Number of admins with shell access
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";
+-------------------------------+
| count(distinct(user.user_id)) |
+-------------------------------+
|                          1344 |
+-------------------------------+