GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  SYMPA_DATABASE (5)

.ds Aq ’

NAME

sympa_database - Strutcure of Sympa core database

CONTENTS

DECRIPTION

Core database of Sympa is based on SQL. In following list of tables and indexes, data types are based on MySQL/MariaDB. Corresponding types are used by other platforms (PostgreSQL, SQLite, ...).

    Tables

subscriber_table

This table store subscription, subscription option etc.

Fields:
user_subscriber varchar(100) (Primary key)

email of subscriber

list_subscriber varchar(50) (Primary key)

list name of a subscription

robot_subscriber varchar(80) (Primary key)

robot (domain) of the list

reception_subscriber varchar(20) reception format option of subscriber (digest, summary, etc.)
suspend_subscriber int(1) boolean set to 1 if subscription is suspended
suspend_start_date_subscriber int(11) the Unix time when message reception is suspended
suspend_end_date_subscriber int(11) the Unix time when message reception should be restored
bounce_subscriber varchar(35) FIXME
bounce_score_subscriber smallint(6) FIXME
bounce_address_subscriber varchar(100) FIXME
custom_attribute_subscriber text FIXME
date_subscriber datetime not null date of subscription
update_subscriber datetime the latest date where subscription is confirmed by subscriber
comment_subscriber varchar(150) free form name
number_messages_subscriber int(5) not null the number of message the subscriber sent
visibility_subscriber varchar(20) FIXME
topics_subscriber varchar(200) topic subscription specification
subscribed_subscriber int(1) boolean set to 1 if subscriber comes from ADD or SUB
included_subscriber int(1) boolean, set to 1 is subscriber comes from an external datasource. Note that included_subscriber and subscribed_subscriber can both value 1
include_sources_subscriber varchar(50) comma seperated list of datasource that contain this subscriber
Indexes:
subscriber_user_index user_subscriber
user_table

The user_table is mainly used to manage login from web interface. A subscriber may not appear in the user_table if he never log through the web interface.

Fields:
email_user varchar(100) (Primary key)

email of user

password_user varchar(40) password are stored as fringer print
gecos_user varchar(150) display name of user
last_login_date_user int(11) Unix time of last login, printed in login result for security purpose
last_login_host_user varchar(60) host of last login, printed in login result for security purpose
wrong_login_count_user int(11) login attempt count, used to prevent brute force attack
last_active_date_user int(11) the last Unix time when this user was confirmed their activity by purge_user_table task
cookie_delay_user int(11) FIXME
lang_user varchar(10) user langage preference
attributes_user text FIXME
data_user text FIXME
exclusion_table

Exclusion table is used in order to manage unsubscription for subsceriber inclued from an external data source.

Fields:
list_exclusion varchar(57) (Primary key)

FIXME

robot_exclusion varchar(80) (Primary key)

FIXME

user_exclusion varchar(100) (Primary key)

FIXME

family_exclusion varchar(50) (Primary key)

FIXME

date_exclusion int(11) FIXME
session_table

Management of HTTP session.

Fields:
id_session varchar(30) (Primary key)

the identifier of the database record

prev_id_session varchar(30) previous identifier of the database record
start_date_session int(11) not null the date when the session was created
date_session int(11) not null Unix time of the last use of this session. It is used in order to expire old sessions
refresh_date_session int(11) Unix time of the last refresh of this session. It is used in order to refresh available sessions
remote_addr_session varchar(60) the IP address of the computer from which the session was created
robot_session varchar(80) the virtual host in which the session was created
email_session varchar(100) the email associated to this session
hit_session int(11) the number of hit performed during this session. Used to detect crawlers
data_session text parameters attached to this session that don’t have a dedicated column in the database
one_time_ticket_table

One time ticket are random value used for authentication challenge. A ticket is associated with a context which look like a session.

Fields:
ticket_one_time_ticket varchar(30) (Primary key)

FIXME

email_one_time_ticket varchar(100) FIXME
robot_one_time_ticket varchar(80) FIXME
date_one_time_ticket int(11) FIXME
data_one_time_ticket varchar(200) FIXME
remote_addr_one_time_ticket varchar(60) FIXME
status_one_time_ticket varchar(60) FIXME
notification_table

Used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionally a message disposition notification request. When DSN and MDN are received by Sympa, they are stored in this table in relation with the related list and message ID.

Fields:
pk_notification bigint(20) auto_increment (Primary key)

autoincrement key

message_id_notification varchar(100) initial message-id. This field is used to search DSN and MDN related to a particular message
recipient_notification varchar(100) email address of recipient for which a DSN or MDN was received
reception_option_notification varchar(20) the subscription option of the subscriber when the related message was sent to the list. Useful because some recipient may have option such as //digest// or //nomail//
status_notification varchar(100) value of notification
arrival_date_notification varchar(80) reception date of latest DSN or MDN
arrival_epoch_notification int(11) reception date of latest DSN or MDN
type_notification enum(’DSN’, ’MDN’) type of the notification (DSN or MDN)
list_notification varchar(50) the listname the message was issued for
robot_notification varchar(80) the robot the message is related to
date_notification int(11) not null FIXME
logs_table

Each important event is stored in this table. List owners and listmaster can search entries in this table using web interface.

Fields:
user_email_logs varchar(100) e-mail address of the message sender or email of identified web interface user (or soap user)
date_logs int(11) not null date when the action was executed
usec_logs int(6) subsecond in microsecond when the action was executed
robot_logs varchar(80) name of the robot in which context the action was executed
list_logs varchar(50) name of the mailing-list in which context the action was executed
action_logs varchar(50) not null name of the Sympa subroutine which initiated the log
parameters_logs varchar(100) comma-separated list of parameters. The amount and type of parameters can differ from an action to another
target_email_logs varchar(100) e-mail address (if any) targeted by the message
msg_id_logs varchar(255) identifier of the message which triggered the action
status_logs varchar(10) not null exit status of the action. If it was an error, it is likely that the error_type_logs field will contain a description of this error
error_type_logs varchar(150) name of the error string - if any - issued by the subroutine
client_logs varchar(100) IP address of the client machine from which the message was sent
daemon_logs varchar(10) not null name of the Sympa daemon which ran the action
stat_table

Statistics item are stored in this table, Sum average and so on are stored in stat_counter_table.

Fields:
date_stat int(11) not null FIXME
email_stat varchar(100) FIXME
operation_stat varchar(50) not null FIXME
list_stat varchar(50) FIXME
daemon_stat varchar(20) FIXME
user_ip_stat varchar(100) FIXME
robot_stat varchar(80) not null FIXME
parameter_stat varchar(50) FIXME
read_stat tinyint(1) not null FIXME
Indexes:
stats_user_index email_stat
stat_counter_table

Used in conjunction with stat_table for users statistics.

Fields:
end_date_counter int(11) FIXME
beginning_date_counter int(11) not null FIXME
data_counter varchar(50) not null FIXME
robot_counter varchar(80) not null FIXME
list_counter varchar(50) FIXME
count_counter int FIXME
admin_table

This table is an internal cash where list admin roles are stored. It is just a cash and it does not need to be saved. You may remove its content if needed. It will just make next Sympa startup slower.

Fields:
user_admin varchar(100) (Primary key)

list admin email

list_admin varchar(50) (Primary key)

list name

robot_admin varchar(80) (Primary key)

list domain

role_admin enum(’listmaster’,’owner’,’editor’) (Primary key)

a role of this user for this list (editor, owner or listmaster which a kind of list owner too)

profile_admin enum(’privileged’,’normal’) privilege level for this owner, value //normal// or //privileged//. The related privilege are listed in editlist.conf.
date_admin datetime not null date this user become a list admin
update_admin datetime last update timestamp
reception_admin varchar(20) email reception option for list management messages
visibility_admin varchar(20) admin user email can be hidden in the list web page description
comment_admin varchar(150) FIXME
subscribed_admin int(1) set to 1 if user is list admin by definition in list config file
included_admin int(1) set to 1 if user is admin by an external data source
include_sources_admin varchar(50) name of external datasource
info_admin varchar(150) private information usually dedicated to listmasters who needs some additional information about list owners
Indexes:
admin_user_index user_admin
netidmap_table

FIXME

Fields:
netid_netidmap varchar(100) (Primary key)

FIXME

serviceid_netidmap varchar(100) (Primary key)

FIXME

robot_netidmap varchar(80) (Primary key)

FIXME

email_netidmap varchar(100) FIXME
conf_table

FIXME

Fields:
robot_conf varchar(80) (Primary key)

FIXME

label_conf varchar(80) (Primary key)

FIXME

value_conf varchar(300) the value of parameter //label_conf// of robot //robot_conf//.
list_table

The list_table holds cached list config and some items to help searching lists.

Fields:
name_list varchar(50) (Primary key)

name of the list

robot_list varchar(80) (Primary key)

name of the robot (domain) the list belongs to

family_list varchar(50) name of the family the list belongs to
status_list enum(’open’,’closed’,’pending’,’error_config’,’family_closed’) status of the list
creation_email_list varchar(100) email of user who created the list
creation_epoch_list int(11) UNIX time when the list was created
update_email_list varchar(100) email of user who updated the list
update_epoch_list int(11) UNIX time when the list was updated
searchkey_list varchar(255) case-folded list subject to help searching
web_archive_list tinyint(1) if the list has archives
topics_list varchar(255) topics of the list, separated and enclosed by commas
total_list int(7) estimated number of subscribers

SEE ALSO

Sympa and its database. <https://www.sympa.org/manual/database>.
Search for    or go to Top of page |  Section 5 |  Main Index


6.2.14 SYMPA_DATABASE (5) 2016-04-14

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.