archive message mysql schema (#524)

Slava Bendersky opened 10 years ago

Hello Everyone,

I tried import mysql-message-archive-schema.sql into mariadb, but give error.

-- Message logging implementation. As this is quite resource consuming
-- functionality the message logging database can be located in a different
-- area or on a different database server.
-- The table schema for Messages archiving is optimized to store and, search
-- and retrieve archived messages.

-- Here is an example piece of data stored in the database.
-- The example is taken fom the XEP-0136
-- <iq type='set' id='up1'>
--  <save xmlns='http://www.xmpp.org/extensions/xep-0136.html#ns'>
--   <chat with='juliet@capulet.com/chamber'
--         start='1469-07-21T02:56:15Z'
--         thread='damduoeg08'
--         subject='She speaks!'>
--    <from secs='0'><body>Art thou not Romeo, and a Montague?</body></from>
--    <to secs='11'><body>Neither, fair saint, if either thee dislike.</body></to>
--    <from secs='7'><body>How cam'st thou hither, tell me, and wherefore?</body></from>
--    <note utc='1469-07-21T03:04:35Z'>I think she might fancy me.</note>
--   </chat>
--  </save>
-- </iq>

-- Table keeping JIDs for message logs
create table tig_ma_jid (
  -- Automatic record ID
  ma_j_id    bigint unsigned NOT NULL auto_increment,
  -- Buddy JID the chat is with
  jid     varchar(128) NOT NULL,
  primary key(ma_j_id),
  unique key jid (jid)
)

ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- Table keeping chat threads if set
create table tig_ma_thread (
  -- Automatic record ID
  ma_t_id    bigint unsigned NOT NULL auto_increment,
  -- Chat thread, if set
  thread     varchar(128),
  primary key(ma_ts_id),
  unique key thread (thread),
)

ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- Table keeping chat subjects if set
create table tig_ma_subject (
  -- Automatic record ID
  ma_s_id    bigint unsigned NOT NULL auto_increment,
	-- Chat subject if set
	subject    varchar(255),
  primary key(ma_ts_id),
  unique key subject (subject)
)

ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

-- Table keeping actual message body
create table tig_ma_message (
  -- Automatic record ID
  ma_m_id    bigint unsigned NOT NULL auto_increment,
  -- Automaticly generated timestamp and automaticly updated on change
  utc        timestamp,
	-- From address of the message, the vaulue refers to the tig_ma_jid table
	from_jid 	bigint unsigned NOT NULL,
	-- To address of the message, the vaulue refers to the tig_ma_jid table
	to_jid		bigint unsigned NOT NULL,
	-- Thread and Subject ID reference to the table tig_ma_thread_subject
	ts_id 		bigint unsigned,
	-- This field is supposed to lower database storage consumption. The idea is
	-- to not store the same message 2 times which may happen if 2 users
	-- on the same server chat with each other. With all the informations in this
	-- table it should be enough to store such message only once.
	body_hash  char(32) NOT NULL,
	body_lang  char(2) NOT NULL,
	-- The body of the message
	body       varchar(4096) NOT NULL,
  primary key(ma_m_id),
  key utc (utc),
	key from_jid (from_jid),
	key to_jid (to_jid),
	key ts_id (ts_id),
	key body_hash (body_hash),
  key body (body)
)

ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;

[root@nl database]# mysql -u root -p tigasedb< mysql-message-archive-schema.sql
Enter password: 
ERROR 1064 (42000) at line 36: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')

ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC' at line 8

Activities

Andrzej Wójcik (Tigase) commented 10 years ago

Where did you get this "mysql schema for message archive"? This do not look like Tigase Message Archiving and Tigase Message Archiving component creates database tables on it's own without need to manually import any database schema files.
Slava Bendersky commented 10 years ago

Hello Andrzej,

I got schema from https://repository.tigase.org/reps/tigase-server/trunk/database/mysql-message-archive-schema.sql

Also I open forum topic about configuration issue, where can't load main components.

https://projects.tigase.org/boards/15/topics/5363

Slava.
Andrzej Wójcik (Tigase) commented 10 years ago

I think this is not a bug in Tigase XMPP Server but it is caused by the fact that we are not using SVN as source version control for Tigase XMPP Server. Long time ago we switched from SVN to GIT and now our repositories are hosted in GIT (at Tigase XMPP Server - Repository page we present correct location of GIT repository).

Link you provided points to old version of Tigase XMPP Server source code from our old SVN repository which will not work with new Tigase XMPP Server 7.0.2.

Please use database schemas from new repository and in new versions from https://projects.tigase.org/projects/tigase-server/repository or directly from GIT repository https://repository.tigase.org/git/tigase-server.git
Login to comment

Type	Bug
Priority	Blocker
Assignee	Slava Bendersky
RedmineID	3256
Spent time	0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#524