Orbeon Forms
  • Getting started
  • Installation
    • Logging
    • Configuration banner
    • Docker
    • Azure
    • Tomcat
    • WildFly
    • WebSphere
    • WebLogic
    • GlassFish
    • Caches
    • Replication
    • Upgrading
  • Configuration
    • Properties
      • General
        • HTTP client
      • Form Runner
        • Detail page
          • Attachments
          • Email properties
          • PDF
          • Table of contents
        • Persistence
        • Summary page
      • Form Builder
      • XForms
    • Advanced
      • Workflows
      • Session management
      • State handling
      • Client-side error handling
      • Clustering and High Availability
      • Configuring a Form Runner eXist database
      • Creating a production WAR
      • Environments
      • JavaScript and CSS assets
      • Limiter filter
      • Run modes
      • Security
        • Content-Security-Policy header
      • SAP Hybris Module
      • XForms logging
    • Troubleshooting
      • Troubleshooting with the orbeon.log
      • Memory and threads
      • Relational database logging
      • Misc
  • Form Builder
    • Form settings
      • Time window
    • Form editor
      • Form area
      • Toolbox
      • Buttons bar
      • Control settings
      • Dependent fields and sections
      • Validation
      • Choices editor
      • Publishing
      • Cut, copy and paste
      • Section and grid settings
      • Section settings
      • Grid settings
      • Quick control search
      • Repeat settings
      • Repeated grids
      • Undo and redo
      • Keyboard shortcuts
    • Formulas
      • Examples of formulas
      • Formulas inspector
      • Formulas console
    • Summary page
    • Form localization
    • Advanced
      • Edit source
      • Services and actions
        • HTTP services
        • Database services
        • Simple Actions
        • Action Syntax
        • Action Syntax examples
        • Synchronizing repeated content
      • Testing a form in web mode
      • Testing PDF production
      • Testing offline functionality
      • Email Settings dialog
      • Field-level encryption
      • Messages
      • Section templates
      • Template syntax
      • XML Schemas support
      • Extensibility
        • Extension API
        • Integration
        • Toolbox component metadata
  • Form Runner
    • Overview
      • Terminology
    • Pages
      • Landing page
      • Published Forms page
      • Forms Admin page
      • Summary page
    • Components
      • Alert dialog
      • Attachment
      • Autocomplete
      • Captcha
      • Character counter
      • Checkbox input
      • Currency
      • Date
      • Dropdown date
      • Static and dynamic dropdown
      • Error summary
      • Grid
      • Handwritten signature
      • Hidden field
      • Image
      • Image annotation
      • Image attachment
      • Number
      • Open selection
      • Repeater
      • Formatted Text / Rich Text Editor
      • Section
      • Single-selection tree
      • Source code editor
      • Time
      • US phone
      • US state
      • Video
      • Video attachment
      • Wizard
      • XForms inspector
      • Yes/No answer
    • Features
      • Automatic calculations dependencies
      • Datasets
      • Excel and XML import
      • Excel and XML export
      • Summary page Excel Export
      • Form definitions and form data Zip Export
      • Purging historical data
      • Lease
      • Localization
      • Supported languages
      • Mobile support
      • Multitenancy
      • Form Runner navigation bar
      • PDF production
        • Automatic PDF
        • Automatic PDF header and footer configuration
        • PDF templates
      • Responsive design
      • Revision history
      • S3 storage
      • Simple data migration
      • TIFF production
      • Versioning
      • Wizard view
      • Workflow stage
    • Persistence
      • Using a relational database
      • Relational database schema
      • Purging old data using SQL
      • Auditing
      • Autosave
      • Database support
      • Flat view
    • Linking and embedding
      • Linking
      • Java Embedding API
      • JavaScript Embedding API
      • Liferay full portlet
      • Liferay proxy portlet
      • Securing Form Runner access
      • Form Runner offline embedding API
      • Angular component
      • React component
    • Access control and permissions
      • Users
      • Login & Logout
      • Deployed forms
      • Form fields
      • Editing forms
      • Owner and group member
      • Organizations
      • Scenarios
      • Token-based permissions
    • Styling
      • CSS
      • Grids CSS
      • Automatic PDF styling and CSS
    • APIs
      • Authentication of server-side service APIs
      • Persistence API
        • CRUD API
        • Search API
        • List form data attachments API
        • Form Metadata API
        • Lease API
        • Reindexing API
        • Caching
        • Versioning
        • Revision History API
        • Zip Export API
        • Custom persistence providers
      • Other APIs
        • Connection context API
        • Duplicate form data API
        • File scan API
        • Form Runner JavaScript API
        • Generate XML Schema API
        • PDF API
        • Publish form definition API
        • Run form in the background API
      • Data formats
        • Form data
        • Date and time
        • Form definition
    • Architecture and integration
      • Architecture
      • Access form data
      • Integration
    • Advanced
      • Buttons and processes
        • Simple process syntax
        • Core actions
        • Form Runner actions
          • Save action
          • Send action
          • Email action
        • XForms actions
        • Predefined buttons, processes and dialogs
        • Summary page buttons and processes
      • Custom dialogs/model logic
      • Services
      • Singleton form
      • Monitoring HTTP requests
  • XForms
    • Core
      • Attribute Value Templates (AVTs)
      • Binds
      • Validation
      • Variables
      • Keyboard focus
      • XForms JavaScript API
      • Error handling
        • Detailed behavior
      • Model-Bind variables
      • XForms 2.0 support
    • Events
      • Standard support
      • UI refresh events
      • Keyboard events
      • Extension events
      • Extension context information
      • Other event extensions
    • Actions
      • Repeat, insert and delete
      • Scripting actions
      • Extensions
    • Controls
      • Label, hint, help
      • Input
      • Output
      • Text area
      • Button
      • Upload
      • Dialog
    • Submission
      • Standard support
      • JSON support
      • Asynchronous submissions
      • Caching extension
      • Other submission extensions
    • XPath
      • Type annotations
      • Expression analysis
      • Tips
      • Compatibility
      • Standard functions
      • Maps and arrays Functions
      • Extension functions
        • Core functions
        • Utility functions
        • Model functions
        • Controls functions
        • XML functions
        • JSON functions
        • HTTP functions
        • Form Runner functions
        • Other functions
        • Deprecated functions
    • XBL components
      • FAQ
      • Guide
        • XBL Tutorial
        • Bindings
        • XForms models
        • Including content
        • Event handling
        • Conventions
        • Map XBL example
        • Learning from existing components
      • Advanced topics
        • XBL Modes
        • JavaScript companion classes
        • XBL library
        • Extensions
        • Attachment controls
    • XForms tutorial
      • Introduction
      • Installation
      • The Hello application
      • The Bookcast application
        • The basic app
        • Database access
        • Polishing the app
        • Adding a feed
    • Using XForms from Java apps
  • XML Platform
    • Page Flow Controller
      • Basics
      • XML submission
      • Navigating between pages
      • Paths and matchers
      • Other configuration elements
      • Typical combinations of page model and page view
      • Examples
      • Authorizing pages and services
    • Processors
      • URL generator
      • Request generator
      • PDF to image converter
    • Resources
      • Resource managers
      • Setting up an external resources directory
    • Other
      • Binary and text documents
  • FAQ
    • Licensing
    • PE and Dev Support
    • Form Builder and Form Runner
    • Resources and support
    • Other technical questions
  • Contributors
    • Automated tests
    • Building Orbeon Forms
    • Localizing Orbeon Forms
    • Validation functions
    • Contributor License Agreement
  • Release notes
    • Orbeon Forms 2022.1.9
    • Orbeon Forms 2024.1.1
    • Orbeon Forms 2023.1.7
    • Orbeon Forms 2024.1
    • Orbeon Forms 2023.1.6
    • Orbeon Forms 2023.1.5
    • Orbeon Forms 2021.1.11
    • Orbeon Forms 2022.1.8
    • Orbeon Forms 2023.1.4
    • Orbeon Forms 2023.1.3
    • Orbeon Forms 2023.1.2
    • Orbeon Forms 2022.1.7
    • Orbeon Forms 2023.1.1
    • Orbeon Forms 2023.1
    • Orbeon Forms 2022.1.6
    • Orbeon Forms 2021.1.10
    • Orbeon Forms 2022.1.5
    • Orbeon Forms 2021.1.9
    • Orbeon Forms 2022.1.4
    • Orbeon Forms 2022.1.3
    • Orbeon Forms 2021.1.8
    • Orbeon Forms 2022.1.2
    • Orbeon Forms 2022.1.1
    • Orbeon Forms 2022.1
    • Orbeon Forms 2021.1.7
    • Orbeon Forms 2021.1.6
    • Orbeon Forms 2021.1.5
    • Orbeon Forms 2021.1.4
    • Orbeon Forms 2021.1.3
    • Orbeon Forms 2021.1.2
    • Orbeon Forms 2021.1.1
    • Orbeon Forms 2021.1
    • Orbeon Forms 2020.1.6
    • Orbeon Forms 2019.2.4
    • Orbeon Forms 2019.1.2
    • Orbeon Forms 2018.2.5
    • Orbeon Forms 2018.1.4
    • Orbeon Forms 2020.1.5
    • Orbeon Forms 2020.1.4
    • Orbeon Forms 2020.1.3
    • Orbeon Forms 2020.1.2
    • Orbeon Forms 2019.2.3
    • Orbeon Forms 2020.1.1
    • Orbeon Forms 2020.1
    • Orbeon Forms 2019.2.2
    • Orbeon Forms 2019.2.1
    • Orbeon Forms 2019.1.1
    • Orbeon Forms 2019.2
    • Orbeon Forms 2019.1
    • Orbeon Forms 2018.2.4
  • Release history
  • Use cases
  • Product roadmap
  • Index of features
Powered by GitBook
On this page
  • Availability
  • Rationale
  • Form Builder data
  • Removing form data
  • Removing form attachments
  • Removing other content
  • See also
  1. Form Runner
  2. Persistence

Purging old data using SQL

PreviousRelational database schemaNextAuditing

Last updated 10 months ago

Availability

This is available to any version of Orbeon Forms.

There is now a which allows you to purge data from the Forms Admin page. This is the recommended way to purge data.

Rationale

The built-in implementation of the persistence API for relational databases never deletes saved data from your database. When users "delete" data from the UI, it marks the data as deleted in the database, and then ignores such data. This is done to increase safety, enable auditing, and allow an admin to "undelete" data that has been deleted by mistake.

While there are benefits to keeping data marked as deleted, there are cases where you might want to get rid of it, for instance to comply with regulatory requirements, or for larger systems to save space and improve performance.

This is done in 2 steps, starting by removing form data, and then the corresponding attachments. We recommend you follow the steps below only if you are familiar with SQL. Also, especially if you are going to run this on a production database, we highly recommend you first create a backup of that database.

Form Builder data

The following queries purge data that users manually deleted. However, should you want to adapt those queries to also purge "historical data", keep in mind that forms you edit in Form Builder are also stored in the orbeon_form_data and orbeon_form_data_attach tables. This means that in your own version of those queries, you might want to avoid rows with with app = 'orbeon' AND form = 'builder', so not to delete any Form Builder data and so not to loose the ability to open forms from the Form Builder summary page.

Removing form data

The following statement is for Oracle (if you're using another database, replace ADD_MONTHS(SYSDATE, -1) by an equivalent function on that database). It will return the data marked for deletion more than 1 month ago. If you want to delete that data, replace SELECT * by DELETE in the statement, and run it again.

SELECT *
FROM   orbeon_form_data
WHERE  document_id IN
       (
           SELECT t.document_id
           FROM   orbeon_form_data t,
                  (
                      SELECT   max(last_modified_time) last_modified_time,
                               app, form, document_id
                      FROM     orbeon_form_data
                      GROUP BY app, form, document_id
                  ) m
           WHERE  -- Look at "last row" in the "journal"
                  t.last_modified_time = m.last_modified_time AND
                  t.app                = m.app                AND
                  t.form               = m.form               AND
                  t.document_id        = m.document_id        AND
                  -- Take deleted items
                  deleted = 'Y'                               AND
                  -- Deleted at least 1 month ago
                  t.last_modified_time <= ADD_MONTHS(SYSDATE, -1)
       )

Removing form attachments

Next, we want to remove "orphan" attachments. Those are attachments no longer referenced by an existing document. As in the previous step, first check the data returned by the following query, and make sure this is data you want to delete. If that is the case, replace SELECT * by DELETE and run that statement again.

SELECT *
FROM   orbeon_form_data_attach
WHERE  document_id NOT IN
       (
           SELECT document_id
           FROM orbeon_form_data
       )

Removing other content

If that is the case, the relevant rows must also be deleted.

See also

The orbeon_i_current and orbeon_i_control_text tables may contain references, via the data_id column, to entries in orbeon_form_data. The content of these tables is recreated when a is performed.

If the is enabled and in use, the orbeon_form_data_lease table may also contain references, via the document_id column, to entries in orbeon_form_data.

[SINCE Orbeon Forms 2023.1]
Purge function
Lease feature
Revision history
Purging historical data
Export of form definitions and form data
Auditing
Forms Admin page
Use Form Runner with a Relational Database
full database reindex