Public Software Group e. V. · LiquidFeedback-Core

LiquidFeedback Core (Backend)

This page is dedicated to the backend of LiquidFeedback (“the core”). You may also want to visit the main page of LiquidFeedback.

The core consists of a database scheme for the PostgreSQL database, including the algorithms for delegations, feedback and the voting procedure implemented as SQL views and database procedures written in PL/pgSQL. As it is licensed under the liberal MIT/X11-License you can include it in any software project you like, as long as there is PostgreSQL support for your programming language available.

Dependencies

PostgreSQL 8.3 or higher

Development releases

liquid_feedback_core-v2.0.1.tar.gz (development release to be used with the new API)
liquid_feedback_core-v2.0.0.tar.gz
liquid_feedback_core-v1.4.0.tar.gz (intermediate release, not intended for productive use)
liquid_feedback_core-v1.4.0_rc4.tar.gz
liquid_feedback_core-v1.4.0_rc3.tar.gz
liquid_feedback_core-v1.4.0_rc2.tar.gz
liquid_feedback_core-v1.4.0_rc1.tar.gz

Download stable releases

Repository

mercurial repository

Changes

2012-01-29: Version 2.0.1
- Bugfix in update script from v1.3.1 to v1.4.0 regarding missing default for state column in issue table (updating to v2.0.1 will fix this error too)
- New table unit_setting
- Added column draft_id to table direct_supporter_snapshot
- Bugfix in function delete_private_data: Use invite_code column in member table instead of table invite_code
- Changed/clarified some comments
2011-09-11: Version 2.0.0
- Changes in final vote counting using the Schulze Method
  - Treat the "status quo" as an implicit candidate
  - Configurable implementation of supermajority requirements, including those specified in chapter 7 of Markus Schulze's paper schulze1.pdf
- Allow (wiki) formatting for member statements and suggestions
- Storage of invite codes in table member instead of table invite_code to avoid redundancy
- Removal of vote now/later feature
- Removal of auto-reject feature
- New demo.sql file containing basic settings, units and areas for an imaginary "Solar System" installation
- API related changes
  - Removed session table (it is up to frontends or the API layer to have their own session store)
  - New table "member_application" to be used by the API layer
  - Member deactivation based on last_activity instead of last_login
2011-09-11: Version 1.4.0
- (no changes since release candidate 4)
2011-06-05: Version 1.4.0_rc4 (release candidate 4)
- Bug fixed in function close_voting: Do not create autoreject votes for initiatives which have not been admitted for voting
- Bug fixed in function clean_issue, which caused database error due to constraint violation
- Delete entries from tables issue_comment and voting_comment, when calling function clean_issue
2011-06-02: Version 1.4.0_rc3 (release candidate 3)
- Bug in function close_voting fixed, which caused autoreject votes being inserted, even when members had no voting privilege in the area
2011-05-24: Version 1.4.0_rc2 (release candidate 2)
- Bug in function close_voting fixed, which caused wrong results or database errors
2011-03-13: Version 1.4.0_rc1 (release candidate 1)
- Event log and support for notification system
  - Table event, which is automatically filled by triggers
  - Columns notify_level and notify_event_id in member table
  - Tables ignored_member and ignored_initiative
  - Views event_seen_by_member and pending_notification
- Support for organizational units
  - Table unit stores organizational units as a tree
  - Table privilege selects which members have (voting) rights in a particular organizational unit
  - Global delegations are replaced by unit-delegations
  - References to unit(id) in multiple tables, views and functions
- Deprecated timeline views
- New column state in issue table, tagging each issue with one state of:
  admission, discussion, verification, voting, canceled_revoked_before_accepted, canceled_issue_not_accepted, canceled_after_revocation_during_discussion, canceled_after_revocation_during_verification, calculation, canceled_no_initiative_admitted, finished_without_winner, finished_with_winner
- Changes related to revocation of initiatives
  - New column revoked_by_member_id in table initiative records which member revoked an initiative
  - Revocation of last initiative closes issue without delay, if issue has not been accepted yet
- New tables (rendered_)issue_comment and (rendered_)voting_comment to store comments of members
- Issues do no longer enter rank-calculation phase, if no initiative has passed the second quorum
- Function delete_member deletes sessions to force immediate log-out
- Dropped table ignored_issue
- New table non_voter (partly replacing ignored_issue table) to indicate that somebody does not want to vote him/herself
- Other minor changes
  - INT8 primary key for invite_code table
  - New index contact_other_member_id_idx ON contact(other_member_id)
  - Old update scripts removed from release (only affecting versions before beta26)
  - Included update instructions in README file
2011-02-05: Version 1.3.1 released
- Mechanism to temporarily disable members, who do not login for a long time
  - Added table system_setting with member_ttl field
  - Added columns locked and last_login_public to member table
  - New function check_last_login(), which is called by the lf_update program
- Code cleanup
  - Removed unneccessary JOIN in create_interest_snapshot(...)
2010-11-08: Version 1.3.0 released
- Possibility to explicitly disable delegation in a particular area or issue by delegating to NULL (setting trustee_id to NULL)
- Added table ignored_issue to store information about which issues are ignored by a certain member
- Removed CREATE LANGUAGE statement at the beginning of core.sql file for PostgreSQL 9.0 and higher
  (for PostgreSQL versions prior 9.0 execute CREATE LANGUAGE manually when installing the database)
- Added helpful test policies in demo.sql file
- Minor bugfix: Password in demo.sql file is now encrypted
2010-10-26: Version 1.2.9 released
- Bugfix: Ignore disabled members when adding votes due to autoreject
2010-09-07: Version 1.2.8 released
- Bugfix: Fixed unique constraints forbidding delegations from the same truster to multiple trustees in the same area or issue, and in case of global delegations
2010-08-30: Version 1.2.7 released
- Removed unintentionally inserted extra columns in view global_delegation from previous update
- Changed update script from v1.2.5 to v1.2.6 to work with PostgreSQL 8.3
2010-08-30: Version 1.2.6 released
- Important bugfix, affecting correct counting of supporters/votes (delegations of inactive members were still counted)
2010-08-16: Version 1.2.5 released
- Improved locking to avoid unneccessary delays and possible deadlocks
2010-08-15: Version 1.2.4 released
- lf_update continues on error (but still reports errors)
2010-07-24: Version 1.2.3 released
- Added rendered_draft table for caching
2010-07-20: Version 1.2.2 released
- Save battle data from calls of function clean_issue(issue.id) by materializing battle view
2010-07-19: Version 1.2.1 released
- New function clean_issue(issue.id), which deletes all discussion data and votes of an issue
2010-07-14: Version 1.2.0 released
- Removed "login" name history
- Deletion functions delete_member(...) and delete_private_data() delete more data
  - Fields last_login and notify_email_lock_expiry of table member
  - Final votes on open issues are deleted by delete_member(...) function as well
2010-07-08: Version 1.1.0 released
- Removed column interest_exists from table direct_population_snapshot
- Replaced function delete_member_data(...) by function delete_member(...)
- Added column notify_email_lock_expiry
- Bugfix: Corrected comment for function manual_freeze(...)
2010-06-17: Version 1.0.3 released
- Bugfix: Corrected table for index delegating_voter_member_id_idx
2010-04-21: Version 1.0.2 released
- Function delete_member_data(...) also deletes area memberships now
- Bug introduced in v1.0.1 fixed: Function delete_private_data() used by lf_export does not delete delegations anymore
2010-04-20: Version 1.0.1 released
- Function delete_member_data(...) also deletes outgoing delegations now
2010-04-20: Tarball for Version 1.0.0 recreated
- The .tar.gz-file was replaced, due to a wrong directory prefix in the old archive.
2010-04-15: Version 1.0.0 released
- New function delete_member_data(...)
- Added missing index for table member_history
- Some references fixed to restrict updates
2010-03-26: Version beta26 released
- Save timestamp of last login
- Save timestamps of member activation/deactivation
- Deletion of suggestions with attached opinions is now possible (changed reference to ON DELETE CASCADE)
2010-03-08: Version beta25 released
- Changes in lf_export: Use pg_dump and psql instead of createdb to copy database
2010-03-08: Version beta24 released
- Added missing DELETEs in function delete_private_data()
- Fixed wrong COMMENT statement for table member_relation_setting
2010-03-04: Version beta23 released
- Critical bugfix in function "create_snapshot"(...): Fixed incorrect calculation of opinion count for suggestions
2010-02-22: Version beta22 released
- Bugfix: Added missing states in "valid_state" constraint of table "issue"
2010-02-21: Version beta21 released
- Bug in "forbid_changes_on_closed_issue_trigger"() fixed, which caused errors when closing issues under certain circumstances
2010-02-20: Version beta20 released
- Better handling of ties in final voting (changes rankings and final winners in certain cases)
  - Calculate strengths of pairwise defeats primarily by absolute number of votes for winner and secondarily by absolute number of votes for loser (as recommended in this paper, section 6)
  - Treatment of edge cases in function "vote_ratio"(...)
2010-02-09: Version beta19 released
- Triggers ensure that votes can't be modified by a broken frontend after issues have been frozen
- New snapshot event 'half_freeze' and renamed event 'start_of_voting' to 'full_freeze'
- Policy values admission_, discussion_, verification_ and voting_time get copied into issue, and each issue uses its own values
- Added tables allowing frontends to store member-relation, area, issue and suggestion settings
- Close issues when verification time elapsed after revoking last initiative
- Moved core-update files to update directory; Added BEGIN and COMMIT to core-update.beta16-beta17.sql
- Changed year in LICENSE to "2009-2010"
2010-01-23: Version beta18 released
- Bug fixed, which caused issue_voting_started events in timeline to be shown only after an issue had been closed
2010-01-22: Version beta17 released
- New view timeline
- More indicies (for timeline)
- New table setting_map to store more complicated frontend user settings
2010-01-13: Version beta16 released
- Critical bug related to revoked initiatives fixed, which caused lf_update or check_everything() to exit with an error instead of performing the neccessary tasks
2010-01-10: Version beta15 released
- Bugfix: Revoked initiatives will never be admitted
- Possibility to recommend another initiative to support when revoking an initiative
- Allow "initiator"."accepted" to be NULL (NULL means "no answer yet", while FALSE means "rejected")
2010-01-04: Version beta14 released
- Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published
- New table member_history logging changes of names and logins
2010-01-02: Version beta13 released
- Function delete_private_data() deletes now all invite codes again, due to privacy reasons when using unencrypted channels for sending the invite codes
2010-01-02: Version beta12 released
- Changed function delete_private_data() to delete more data, including tables setting and member_image, for better data protection
- Bugfix related to linux-sh in shell script lf_export
- New fields password_reset_secret and password_reset_secret_expiry in member table
- UNIQUE constraint for notify_email_secret and password_reset_secret fields in member table
- Database constraint forbids adding yourself as a contact
2009-12-25: Version beta11 released
- Support is now automatically deleted, when interest is deleted
- All tables dealing with delegations now contain a column scope (including table delegation)
- Changes in fields related to notify_email of member
- Policies can now specify larger majorities to be reached (e.g. 2/3)
- New column agreed in table initiative to mark, if initiative has reached majority specified by policy
- Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table
- Function delete_private_data() no longer deletes invite codes, which have been already used
- Removed view battle_participant
- Added index on opinion (member_id, initiative_id)
2009-12-10: Version beta10 released
- Voting will be skipped, if no initiative is admitted for voting
- Invite code feature allows people having an invite code to create one account
- Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time
- Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers
- New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)
- Better policy support
  - New table allowed_policy to select which policies can be used in each area
  - Policies are now ordered by an index field
- Bugfixes
  - In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
  - In delete_private_data() function: Secret fields of member table are now deleted too
  - Major bug fixed in lf_export, which caused data loss on execution
2009-11-30: Version beta9 released
- Added missing indicies on TSVECTOR fields
- New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken
- Added weight field also for (intermediate) delegating members in snapshot and voter tables
- Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)
- Export concept for creating a database dump, without some non-public information (e.g. private contact data)
  - Shell script lf_export
  - Database function delete_private_data()
- Field in member table to be used by a frontend to store information about hints being hidden by the user
- Minor cleanup / New comments
2009-11-23: Version beta8 released
- More attibutes in member table
- Renamed column ident_number of member table to identification
- Images of members are now stored in extra table member_image
- Minor bugfix in init.sql: Added missing verification_time column
- Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes
- New function highlight(...), which helps to highlight matching words in search results
2009-11-18: Version beta7 released
- Fixed bug in function close_voting(...) leading to wrong voter_count values in issue table
- Added avatar column to member table for storing a small picture of the member
2009-11-17: Version beta6 released
- Added function for printing delegation chains
- Replaced column member_count of table area by 3 new columns:
  - direct_member_count
  - member_weight (regarding delegations)
  - autoreject_weight
- Added calculated field to member_count table, storing the time of computation of the total member count and area member counts
- Added view liquid_feedback_version
- Code cleanup
2009-11-15: Version beta5 released
- Precalculation of certain values to increase performance:
  - Count of active members per area
  - Count of active members altogether
  - Count of people involved in the voting process for an issue
- Minor bugfix in function close_voting(...) related to initiatives having no positive or negative votes
2009-11-15: Version beta4 released
- Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.
- Bugfix: end_of_admission snapshots are now created for accepted issues.
2009-11-07: Version beta3 released
- Minor bugfix: Autocreated entries in supporter table refer to the current draft of the initiative instead of throwing an error
2009-11-03: Version beta2 released
- Serious bugfix in SQL function create_snapshot(...), which caused wrong counting of opinions on suggestions
- lf_update now deletes expired sessions
- Redundancy in SQL function check_everything() removed by using existent views
2009-10-27: Version beta1 released