https://wiki.pscs.co.uk/ 2026-05-06T11:40:27+00:00 https://wiki.pscs.co.uk/ https://wiki.pscs.co.uk/_media/wiki:dokuwiki.svg text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:database_schema_used_in_vpop3_5.x?rev=1542192349&do=diff Database schema used in VPOP3 5.x Note that all information in this section is subject to change. We do not guarantee that the database schema will remain constant, even between minor upgrade versions, so use this information at your own risk. Do not alter the database schema used by VPOP3 (including indexes, triggers & functions) or your whole VPOP3 data store may collapse. text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:database_backups?rev=1542192349&do=diff Database -> Backups Tab The Backups tab lets you specify how VPOP3 will make its daily backup of its database. We strongly recommend that you backup the database regularly in case of disk problems or file corruption. You can use the VPOP3 backup feature, or manually backup the PostgreSQL database using the pg_dump command, or use another program to backup the PostgreSQL database if you wish. Note that a simple disk copy will text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:spamrules_db_comparator?rev=1542192349&do=diff Spamfilter database comparators Several Spamfilter DB statements allow comparators which are listed below If the value specified in the statement is numeric: * = or == compare numeric values for equality * <> or != compare numeric values for inequality text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:users_autoresponderconditions?rev=1542192349&do=diff Users.AutoresponderConditions table The Users.AutoresponderConditions table contains a record of all autoresponder trigger conditions created in VPOP3. These tell VPOP3 when a particular autoresponder should fire for a particular user. Each autoresponder can have zero or more conditions. An autoresponder with zero conditions will never fire but can be reactivated later by adding a new condition. text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:database_restore?rev=1542192349&do=diff Database -> Restore Tab This tab is only in VPOP3 Enterprise (v5 and later), and is for performing a partial (mailbox/mail folder) restore of the database. To do a full restore of the database, or for VPOP3 Basic/Standard, see the “Restore a Backup of VPOP3” topic Using the text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:spamrules_statement_dbdeletetime?rev=1542192349&do=diff Spam Filter Rules: DBDeleteTime Statement DBDeleteTime Version 6.16+ Purpose Deletes records from the spam filter database based on their last update time The DBDeleteTime statement requires a component name, comparator and time value, in the format text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:calendar_calendars?rev=1542192349&do=diff Calendar.Calendars table The Calendar.Calendars table contains a list of all 'calendars' created in VPOP3. Each user account has at least one calendar Columns * calid - this is a integer sequential value which identifies the calendar uniquely in the database * text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:calendar_events?rev=1542192349&do=diff Calendar.Events table The Calendar.Events table contains a record of all the events in calendars managed by VPOP3. An 'event' is an iCalendar event. Note that if an event is a recurring series, there will only be one 'event' in the database, not one event per instance of the recurrence. text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:copy_smtp_rules?rev=1542192349&do=diff Copy SMTP Rules - Database Query This is an advanced Database action. There is no simple query that will perform the required operation, so we have to create a custom PostgreSQL function to copy the data. At a command prompt in the VPOP3 directory run text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:database_message_store?rev=1542192349&do=diff Database -> Message Store Tab This tab says how VPOP3 handles its message store Message Store The Delay while moving messages setting tells VPOP3 how quickly to import messages into the new database structure if it was upgraded from version 2.x or earlier. Usually this is OK to be left as it is, but if there is some special requirement to import the messages more quickly, you can decrease this number, but that will increase the load on the server. text/html 2026-03-16T11:12:42+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:group_a_user_s_folders_into_letter_groups?rev=1773659562&do=diff Group a user's folders into letter groups This is useful in VPOP3 Enterprise if you have a large folder with many subfolders and want to group the subfolders into other subfolders based on their first letter For example, if you have * Customers text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:logdb_attachments?rev=1542192349&do=diff LogDB.Attachments table The LogDB.Attachments table contains a record of the attachments for messages logged by VPOP3. A message may have zero or more attachments Columns * attachid - this is a bigint sequential value which identifies the attachment uniquely in the database text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:logdb_messages?rev=1542192349&do=diff LogDB.Messages table The LogDB.Messages table contains a record of all messages logged by VPOP3. A message is logged when a message is sent/received/etc. Columns * msgid - this is a bigint sequential value which identifies the message uniquely in the database text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:logdb_recipients?rev=1542192349&do=diff LogDB.Recipients table The LogDB.Recipients table contains a record of the recipients for messages logged by VPOP3. A message may have one or more recipients Columns * rcptid - this is a bigint sequential value which identifies the recipient uniquely in the database text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:logdb_sessions?rev=1542192349&do=diff LogDB.Sessions table The LogDB.Sessions table contains a record of all 'sessions' logged by VPOP3. A session is something like a POP3 server session as an email client downloads messages, or an SMTP client session as VPOP3 sends a message out to the Internet, and so on. text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:logdb_spamrules?rev=1542192349&do=diff LogDB.SpamRules table The LogDB.SpamRules table contains a record of the spamfilter rules which matched for messages logged by VPOP3. A message may have zero or more spamrules entries Columns * spamruleid - this is a bigint sequential value which identifies the spam rule entry uniquely in the database text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:running_database_updates_within_a_transaction?rev=1542192349&do=diff Running database updates within a transaction This is a trick which database experts use, because they have learned (often the hard way) that a simple mistype can trash a database... Before running any database query which can alter the database (UPDATE, DELETE, INSERT, DROP, CREATE, ALTER) begin a database transaction using the text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:spamfilter_whitelist?rev=1542192349&do=diff Spamfilter.Whitelist table The Spamfilter.Whitelist table contains the spamfilter whitelist entries which do not contain wildcards or regular expressions. They are separated out to speed up checking. Columns * address - this is the email address in the whitelist text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:spamrules_statement_dbqueryone?rev=1542192349&do=diff Spam Filter Rules: DBQueryOne Statement DBQueryOne Version 6.16+ Purpose Queries the spam filter database for a single record/aggregate The DBQueryOne statement performs a query on the spam filter database and returns a single value which is either the value of a record or an aggregate of several records. text/html 2018-11-14T10:45:49+00:00 Anonymous (anonymous@undisclosed.example.com) https://wiki.pscs.co.uk/reference:vpop3.postgresquery?rev=1542192349&do=diff VPOP3.PostgresQuery The Lua function 'VPOP3.PostgresQuery' performs a query on the PostgreSQL database and returns the results in a table It takes a single parameter which is the SQL query to perform (Note that any relations used in the query must have the schema specified).