sql_backup.php to run with crontab

[Madriix]

Will this PHP files be useful in the future? The idea is to execute a cron task every 1 minute : php /home/<account>/unrealircd-webpanel/sql_backup/sql_backup.php

And it creates the following SQL tables:

• unreal_irc_users, equivalent to the anope_user table (or anope_db_user)
• unreal_irc_channels, equivalent to the anope_chan table
• unreal_irc_spamfilter, equivalent to a table from the old denora irc (not exist on anope)
• unreal_irc_servers, equivalent to the anope_servers table
• unreal_irc_name_bans (not exist on anope)

It would also be necessary to create the following:

• unreal_irc_top_countries
• unreal_irc_ison (for unreal_irc_users and unreal_irc_channels)

This way, it would be possible to display the desired statistics on the websites.

The only thing that bothers me is that I would like it to be real-time by executing it every 1 second, but I'm afraid it would overload the web server php, causing it to slow down or self-DDOS.

What do you think?

[Lord255]

\o CHARSET=latin1 COLLATE=latin1_swedish_ci in backup_sql.php ?:) why swedish?

but nice idea. 1m is not that bad :)

[Madriix]

@Lord255 I just tested this:

CREATE TABLE `unreal_irc_users` (
.....
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

and :

CREATE TABLE `unreal_irc_users` (
.....
)

by default the second it executes it in Swedish

[Lord255]

i presume your default is that, thats why. but all good. i was just wondering why. :) (y)

[ValwareIRC]

I'm not sure why there needs to be an SQL backup of information which is already available from RPC.

Please can you explain this to me?

[ValwareIRC]

with RPC the information is always up-to-date. why would you need to take information (which is not realtime) about users and channels when there is already a realtime option?

[Madriix]

@ValwareIRC Here are some examples:

• On a website, sometimes we need to display the number of connected there is on the IRC server and just count the number of items in the unreal_irc_users column to display "There are xx currently connected on our IRC network"
• The spamfilter is ideal for checking things to see if there are blacklists equal to users, in order to give a little boost to avoid, for example, a person who registers or to prevent him from receiving a confirmation email when his ip is found in the spamfilter or even in "unreal_irc_name_bans" with his nickname
• Unreal_irc_channels allows you to list the list of channels on a website like here: https://discussionner.com/salons here I use anope_chan but it could be more powerful with unreal_irc_channels. You can also see the number of connected, which could be used by unreal_irc_channels.
• unreal_irc_top_countries could list a top of the most populated countries in terms of users and list that on a website, that's always an additional page

A website does not need to have real-time updates, there may be a difference of 1 or 5 minutes, that's okay.

[TehPeGaSuS]

Hello @Madriix,

While we appreciate both your interest in the web panel and PR submissions, there are certain things that should be looked into first.

While GitHub is our means of hosting the source code and making it available to anyone, most discussions involving the project are held on IRC.

We therefore invite you to join us on the #unreal-webpanel channel at irc.unrealircd.org:+6697 (TLS mandatory) where we discuss the next steps to take, the most important features to add, etc.

In this way, it will be possible to monitor more closely what we consider important, what can wait and what we consider outside the scope of the web panel.

It is important to note that this will also help to avoid unnecessary coding.

[ValwareIRC]

Maybe what we're working on next could help you to solve what you are looking to achieve.

Firstly, it's important to note that you can get most of these statistics directly from unrealircd's json-rpc, and putting them in an SQL table to fetch from instead is confusing to me.

Secondly, for such cases where it's simpler to make your external things show information related to unrealircd without having to query the RPC directly, we are currently making several json-api endpoints where you can query the realtime information.

Please join us on the support channel.

[Madriix]

@ValwareIRC Oh, I see what you mean. You would prefer, for example, that I use unrealircd-rpc-php to display the number of users currently connected to our IRC network on a homepage or all pages. However, if the page receives 2,000 visitors a day, I am concerned that each visitor would query unrealircd-rpc in real-time, as there is a request sent to unrealircd each time. If 2,000 or more visitors go to a site's page and pull real-time data from unrealircd rpc, I'm not sure if it's suitable to use unrealircd-rpc-php for SEO, as it may cause problems for pagespeed.

Unrealircd-rpc-php is suitable for APIs and processing on pages with low traffic like admins-pages, but it's probably not intended for a homepage of a site that receives thousands of visitors every day. Suppose Unrealircd is turned off and is no longer running. How will unrealircd-rpc-php behave? Will the homepage lag significantly, or will it simply not display the requested data, whereas with MySQL, it will always display the old data even if the Unrealircd server is offline?

Can unrealircd-rpc-php be used for a homepage or even all pages of a site that receives thousands of visitors each day? Will it impact pagespeed? I am more accustomed to using SQL for websites without pulling from servers like unrealircd-rpc-php or socket.io from nodejs, etc.

Also, by using SQL, it allows for less coding to display statistics on a website.

Furthermore, let's be honest, the data coming from unrealircd-rpc is more reliable compared to anopedb* because of some observations I have made over time. For example, I have noticed that one column on an Anope table had difficulty updating, possibly the vhosts column, which records the real vhost/vident but sometimes forgets to update the vhost/vident defined by /chghost or /chgident or by HostServ. The GeoIP country code on Anope is also no longer functioning and they have not updated to Geoip2, still using the old version of GeoIP with broken links. In the past, I had to create a workaround to retrieve the old Anope database for GeoIP to continue having country codes on anope_db_user, but they are not up to date, and the country codes for IPv6 do not work. With Unrealircd-rpc, the data is reliable, and the "unreal_irc_users" table is much more up to date and reliable.

Furthermore, there are missing columns on anopedb but unrealircd-rpc-sql has new columns with more up-to-date data, such as the country code and even the list of a user's channels directly in the "channels" column on unreal_irc_channels. There are likely other useful columns missing on anopedb.

However, one thing that I find unfortunate is the Anope_NickAlias and Anope_db_NickCore tables, which are directly the user accounts on Anope and their aliases. Is there no way to do this with Unrealircd-rpc? It would be better to have all the aliases of users in a single table column such as "aliases" in unreal_irc_accounts, where it would list the principal nick and aliases in the form of nick2,nick3,nick4. This would avoid having two tables, and on Anope_db_NickAlias, many aliases are missing or not up to date, which is a problem with Anope tables not being 100% perfect. I also have a Node.js robot that helps update columns in an Anope table because Anope itself doesn't always update certain columns or not consistently.

[syzop]

@Madriix Indeed i would not recommend doing RPC calls for every page load of a visitor. What you are doing could be useful, and sure, the JSON-RPC stuff to SQL could be great functionality.

But, to be honest, it doesn't really have anything to do with the admin panel. So this is not like something we would include. Similar it has nothing to do with "backups", it more like live stats.

So I'm closing this issue. Doesn't mean it's bad! Just that it does not belong in core webpanel. You could very well publish it as a 3rd party plugin later (once we have that functionality). Or you could make a separate PHP project that uses unrealircd-rpc-php and MySQL. Both are fine, really.

Hope you continue enjoy coding with the panel and having fun, and our offer for you to join irc.unrealircd.org / #unreal-webpanel still stands :D.