protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_push", true)
.AddColumn(new SqlCreate.Column("id", DbType.Int32).NotNull(true).Autoincrement(true).PrimaryKey(true))
.AddColumn("username", DbType.String, 255, true)
.AddColumn("browser", DbType.String, 255)
.AddColumn("endpoint", DbType.String, 255);
return(new[] { t1 });
}
protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_user", true)
.AddColumn("jid", DbType.String, 255, true)
.AddColumn("pwd", DbType.String, 255)
.AddColumn(new SqlCreate.Column("admin", DbType.Int32).NotNull(true).Default(0))
.PrimaryKey("jid");
return(new[] { t1 });
}
protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_private", true)
.AddColumn("jid", DbType.String, 255, true)
.AddColumn("tag", DbType.String, 255, true)
.AddColumn("namespace", DbType.String, 255, true)
.AddColumn("element", DbType.String, UInt16.MaxValue)
.PrimaryKey("jid", "tag", "namespace");
return(new[] { t1 });
}
private static void CreateTable()
{
using (var db = GetDb())
{
var query = new SqlCreate.Table(Table, true)
.AddColumn(new SqlCreate.Column("id", DbType.Int32).NotNull(true).Autoincrement(true).PrimaryKey(true))
.AddColumn("value", DbType.String, 255, true);
db.ExecuteNonQuery(query);
}
}
protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_roster", true)
.AddColumn("jid", DbType.String, 255, true)
.AddColumn("item_jid", DbType.String, 255, true)
.AddColumn("name", DbType.String, 512)
.AddColumn(new SqlCreate.Column("subscription", DbType.Int32).NotNull(true).Default(0))
.AddColumn(new SqlCreate.Column("ask", DbType.Int32).NotNull(true).Default(0))
.AddColumn("groups", DbType.String, UInt16.MaxValue)
.PrimaryKey("jid", "item_jid");
return(new[] { t1 });
}
private void CreateSchema()
{
var jabber_element = new SqlCreate.Table("jabber_element", true)
.AddColumn(new SqlCreate.Column("jid", DbType.String, 3071).NotNull(true))
.AddColumn(new SqlCreate.Column("element_key", DbType.String, 1024).NotNull(true))
.AddColumn(new SqlCreate.Column("element_text", DbType.String, UInt16.MaxValue).NotNull(false))
.PrimaryKey("jid", "element_key");
using (var db = GetDb())
{
db.ExecuteNonQuery(jabber_element);
}
}
protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_archive", true)
.AddColumn(new SqlCreate.Column("id", DbType.Int32).NotNull(true).PrimaryKey(true).Autoincrement(true))
.AddColumn("jid", DbType.String, 255, true)
.AddColumn("stamp", DbType.DateTime, true)
.AddColumn("message", DbType.String, MESSAGE_COLUMN_LEN)
.AddIndex("jabber_archive_jid", "jid");
var t2 = new SqlCreate.Table("jabber_archive_switch", true)
.AddColumn(new SqlCreate.Column("id", DbType.String, 255).NotNull(true).PrimaryKey(true));
return(new[] { t1, t2 });
}
private void CreateSchema()
{
var jabber_user = new SqlCreate.Table("jabber_user", true)
.AddColumn(new SqlCreate.Column("username", DbType.String, 1071).NotNull(true).PrimaryKey(true))
.AddColumn(new SqlCreate.Column("userpass", DbType.String, 128).NotNull(true))
.AddColumn(new SqlCreate.Column("uservcard", DbType.String, UInt16.MaxValue).NotNull(false));
var jabber_roster = new SqlCreate.Table("jabber_roster", true)
.AddColumn(new SqlCreate.Column("user_jid", DbType.String, 3071).NotNull(true))
.AddColumn(new SqlCreate.Column("contact_jid", DbType.String, 3071).NotNull(true))
.AddColumn(new SqlCreate.Column("subs", DbType.Int32).NotNull(true))
.AddColumn(new SqlCreate.Column("ask", DbType.Int32).NotNull(true))
.AddColumn(new SqlCreate.Column("item", DbType.String, UInt16.MaxValue).NotNull(false))
.PrimaryKey("user_jid", "contact_jid")
.AddIndex(new SqlCreate.Index("contact_ask", "jabber_roster", "contact_jid", "ask"));
using (var db = GetDb())
{
db.ExecuteNonQuery(jabber_user);
db.ExecuteNonQuery(jabber_roster);
}
}
protected override SqlCreate[] GetCreateSchemaScript()
{
var t1 = new SqlCreate.Table("jabber_offmessage", true)
.AddColumn(new SqlCreate.Column("id", DbType.Int32).NotNull(true).Autoincrement(true).PrimaryKey(true))
.AddColumn("jid", DbType.String, 255, true)
.AddColumn("message", DbType.String, MESSAGE_COLUMN_LEN)
.AddIndex("jabber_offmessage_jid", "jid");
var t2 = new SqlCreate.Table("jabber_offpresence", true)
.AddColumn(new SqlCreate.Column("id", DbType.Int32).NotNull(true).Autoincrement(true).PrimaryKey(true))
.AddColumn("jid_to", DbType.String, 255, true)
.AddColumn("jid_from", DbType.String, 255, false)
.AddColumn(new SqlCreate.Column("type", DbType.Int32).NotNull(true).Default(0))
.AddIndex("jabber_offpresence_to", "jid_to");
var t3 = new SqlCreate.Table("jabber_offactivity", true)
.AddColumn(new SqlCreate.Column("jid", DbType.String, 255).NotNull(true).PrimaryKey(true))
.AddColumn("logout", DbType.DateTime)
.AddColumn("status", DbType.String, 255);
return(new[] { t1, t2, t3 });
}
private void Clear()
{
while (true)
{
try
{
using (var db = new DbManager("default"))
{
var t = new SqlCreate.Table("jabber_clear", true)
.AddColumn("lastdate", DbType.DateTime);
db.ExecuteNonQuery(t);
var mindate = db.ExecuteScalar <DateTime>("select lastdate from jabber_clear limit 1");
var tenants = new List <Tuple <int, string> >();
var maxdate = DateTime.UtcNow.AddDays(-365);
var sql = @"select
t.id, t.alias
from tenants_tenants t, webstudio_uservisit v
where t.id = v.tenantid and t.creationdatetime < ?
group by 1,2
having max(v.visitdate) between ? and ?";
using (var cmd = CreateCommand(db, sql, maxdate, mindate, maxdate))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tenants.Add(Tuple.Create(reader.GetInt32(0), reader.GetString(1)));
}
}
log.InfoFormat("Find {0} tenants for clear jabber messages", tenants.Count);
foreach (var tid in tenants)
{
// remove all service messages in inactive (no visits at last year) portals
var domain = CoreContext.Configuration.BaseDomain;
var replace = ConfigurationManager.AppSettings["jabber.replace-domain"];
if (!string.IsNullOrEmpty(replace))
{
var arr = replace.Split(new[] { "->" }, StringSplitOptions.RemoveEmptyEntries);
var from = arr[0];
var to = arr[1];
domain = domain.Replace(to, from); // revert replace
}
domain = (tid.Item2.EndsWith("_deleted") ? tid.Item2.Substring(0, tid.Item2.Length - 8) : tid.Item2) +
"." + domain;
if (stop.WaitOne(TimeSpan.Zero))
{
return;
}
RemoveFromArchive(db, domain, maxdate);
var users = new List <string>();
using (var cmd = CreateCommand(db, "select username from core_user where tenant = ? and username <= ?", tid.Item1, tid.Item2))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
users.Add(reader.GetString(0));
}
}
foreach (var user in users)
{
var username = user.ToLowerInvariant().Trim();
if (stop.WaitOne(TimeSpan.Zero))
{
return;
}
var jid = string.Format("{0}@{1}|{1}", username, domain);
RemoveFromArchive(db, jid, maxdate);
}
}
db.ExecuteNonQuery("delete from jabber_clear;");
db.ExecuteNonQuery("insert into jabber_clear values (?)", maxdate);
// remove offline messages
var id = 0;
using (var cmd = CreateCommand(db, "select id, message from jabber_offmessage order by 1"))
using (var reader = cmd.ExecuteReader())
{
var less = false;
while (reader.Read())
{
var message = reader.GetString(1);
var m = Regex.Match(message, "<x xmlns=\"jabber:x:delay\" stamp=\"(.+)\"");
if (m.Success)
{
var date = Time.Date(m.Groups[1].Value);
if (date != DateTime.MinValue && date <= maxdate)
{
less = true;
}
else
{
if (less)
{
id = reader.GetInt32(0);
}
break;
}
}
}
}
if (0 < id)
{
using (var cmd = CreateCommand(db, "delete from jabber_offmessage where id < ?", id))
{
var affected = cmd.ExecuteNonQuery();
log.InfoFormat("Remove {0} messages from jabber_offmessage", affected);
}
}
}
}
catch (ThreadAbortException)
{
// ignore
}
catch (Exception err)
{
log.Error(err);
}
if (stop.WaitOne(TimeSpan.FromDays(1)))
{
break;
}
}
}
