• <GetStart>
  • CSPro User's Guide
    • The CSPro System
    • Data Dictionary Module
    • The CSPro Language
    • Data Entry Module
    • Batch Editing Applications
    • Tabulation Applications
    • Data Sources
    • CSPro Statements and Functions
    • Text Templates
    • Templated Reporting System
    • HTML, Markdown, and JavaScript Integration
    • Action Invoker
    • Appendix
      • Appendix A - Installation
      • Appendix B - Keys Summary
      • Appendix C - Menu Summary
      • Appendix D - Toolbar Summary
      • Appendix E - Application Properties
      • Appendix F - Converting Within IMPS or ISSA
      • Appendix G - Errors in Censuses and Surveys
      • Appendix H - File Types
        • File Types
        • JSON Specification Files
        • SQLite Use in CSPro
        • Importing Data to CSPro Format
        • Locking Application Files
        • Temporary Data File
        • CSPro DB File Format
        • Files Description
      • Appendix I - JSON Representations
  • <CSEntry>
  • <CSBatch>
  • <CSTab>
  • <DataManager>
  • <TextView>
  • <TblView>
  • <CSFreq>
  • <CSDeploy>
  • <CSPack>
  • <CSDiff>
  • <CSConcat>
  • <Excel2CSPro>
  • <CSExport>
  • <CSIndex>
  • <CSReFmt>
  • <CSSort>
  • <ParadataConcat>
  • <ParadataViewer>
  • <CSCode>
  • <CSDocument>
  • <CSView>
  • <CSWeb>

SQLite Use in CSPro

Overview
SQLite is a relational database engine that "is the most used database engine in the world." SQLite is in the public domain but CSPro uses the SQLite Encryption Extension (SEE), an add-on that allows CSPro to interact with encrypted databases. CSPro uses SQLite for a variety of reasons, including:
  • CSPro DB data sources (.csdb)
  • Encrypted CSPro DB data sources (.csdbe)
  • Data source index files (.csidx)
  • Paradata logs (.cslog)
  • MBTiles offline mapping (.mbtiles)
  • CommonStore settings storage (CommonStore.db)
Because SQLite is such a popular format, there are many free viewers for SQLite that allow you to access unencrypted databases outside of CSPro.
The version of SQLite included with CSPro supports mathematical functions such as atan2 and sin.
Working With SQLite Databases in CSPro
Within CSPro, there are several ways to interact with SQLite databases:
  • Perform a variety of operations on SQLite databases using the actions in the Sqlite namespace, part of the Action Invoker.
  • Execute a SQL query using the CSPro logic functions sqlquery and paradata.
  • Run SQL queries on paradata logs using the Paradata Viewer.
Using the sql function modifier, you can call user-defined functions from SQL queries.
Encrypted SQLite Databases
When working with an encrypted SQLite database in CSPro, the database is opened using "AES-256 in OFB mode." In instances when a password is used to open the database, the specified password is not used as the key input to SEE but is instead hashed to create a 256-byte key that is used to encrypt the file. A fixed salt is used for Encrypted CSPro DB data sources, but in other instances a random salt may be used.
See also: Sqlite Action Invoker Namespace, SQLite Callback Functions