​

4.12 - DQL Enhancements & Platform Expansion

​

DQL Projections & Aggregates

​

Projections

-- Select specific fields
SELECT make, model, year FROM cars

-- Create calculated fields with aliases
SELECT make, model, price * 0.9 AS discounted_price FROM cars

-- Remove duplicates
SELECT DISTINCT color FROM cars

​

Aggregate Functions

-- Count documents
SELECT COUNT(*) AS total_cars FROM cars

-- Calculate statistics
SELECT 
  AVG(price) AS avg_price,
  MIN(price) AS lowest_price,
  MAX(price) AS highest_price,
  SUM(price) AS total_value
FROM cars

-- Group and aggregate
SELECT make, COUNT(*) AS models, AVG(price) AS avg_price
FROM cars
GROUP BY make
HAVING COUNT(*) > 3

​

DQL Index Management

-- Create an index
CREATE INDEX idx_cars_make ON cars (make)

-- Drop an index
DROP INDEX idx_cars_make

-- View all indexes
SELECT * FROM system::dql_indexes

​

Flutter Platform Expansion

​

Linux Support

• Native Linux binary (libdittoffi.so) included
• Full support for development, testing, and production deployments
• Configurable binary path via $LIBDITTOFFI_PATH environment variable
• Compatible with Flutter’s standard Linux development workflow

​

Legacy Query Builder Deprecation

• SQL-like syntax familiar to developers
• Unified interface for all data operations
• Better performance and optimization
• More powerful query capabilities

​

Additional Enhancements

​

System Improvements

• Enhanced disk observer: Now enabled at startup with configurable batch settings
• Improved error handling: Better error propagation and syntax error identification in DQL
• Memory optimizations: Reduced memory usage for aggregate operations and projections

​

DQL Function Additions

• Duration scalar functions for date/time calculations
• Dynamic field references in queries
• Support for arrays as operation values
• Extended multiplication operations for better numeric handling

​

Security & Diagnostics

• Support for SSLKEYLOGFILE for debugging TLS connections
• New system::data_sync_info virtual collection for sync diagnostics
• Transport diagnostics APIs across multiple SDKs

​

Upgrading to 4.12

1. Update your SDK to version 4.12.0 or later
2. Begin migrating legacy query builder code to DQL
3. Explore projections and aggregates for query optimization
4. Consider Flutter Linux support for desktop deployments

​

4.12.0 Common Changelog

​

Added

• The transports_discovered_peers system parameter to dynamically update peers discovered out-of-band (#17009)
• The new system::indexes virtual collection for viewing DQL indexes on small peers (#17011)
• Support for SSLKEYLOGFILE for inspecting network traffic in tools like Wireshark (#17029)
• UPDATE_LOCAL_DIFF id conflict strategy for INSERT statements (#17310)
• A system:data_sync_info virtual collection that exposes per-remote data sync status via DQL (#17360)
• transports_tcp_so_nodelay system parameter to control TCP_NODELAY for TCP and TCP-based websockets connections (#17673)
• Additional metadata to keep track of the timestamp of the last update file received (#DS-290)
• Observers for DQL queries can now access system virtual collections (#DS-294)
• Query executor runner to observers - they now support all of the new DQL features like projections, aggregates, use ids… (#QE-143)
• SYSTEM:COLLECTIONS & SYSTEM:ALL_COLLECTIONS virtual collections to list accessible collections (#QE-146)
• Support for ARRAYs as the value options list in IN DQL expressions (#QE-167)
• DQL scalar functions for durations (#QE-170)
• DQL support for CREATE INDEX (#QE-182)
• DQL support for DROP INDEX (#QE-183)
• Support for dynamic field references in DQL statements (#QE-2)
• Query request history cache qualifiers for system datasource deletes and general mutations (#QE-207)
• Basic RBO for index scan selection (#QE-210)
• Query parser handles context dependent keyword (#QE-241)
• Virtual collections for active and historical DQL engine requests (#QE-82)

​

Changed

• device_disk_observer_is_enabled system parameter can now be changed at runtime (#CORE-612)
• After eviction, document sync now retains high-level session metadata for non-expired sessions, for sync status tracking purposes (#DS-348)
• Projection, distinct and aggregate features out of preview mode! (#QE-100)
• Wildcard expansion to top-level with left-to-right, last-wins projection aliasing (#QE-156)
• Improved ScalarExpr representation in EXPLAIN output (#QE-193)
• Improved syntax error identification in UPDATE statements (#QE-199)
• ALTER SYSTEM DQL statements now accept static expressions (#QE-204)
• USE IDS clause to accept expressions that can be statically evaluated (#QE-217)
• Permit use of unary plus in DQL expressions (#QE-220)
• Extended DQL multiplication to better handle multiplying two large negative integers (#QE-224)
• Query directives processing to accept USE INDEX as the equivalent of USE DIRECTIVES {"#index":...} (#QE-255)
• CREATE & DROP INDEX statements accept IF [NOT] EXISTS to permit repeated execution without error (#QE-259)
• Websocket connectivity errors are now logged with additional detail (#TRAN-511)
• Ditto no longer includes a peer’s device name and OS during P2P advertisement (BLE, mDNS, Wi-Fi Aware, AWDL) (#TRAN-652)

​

Fixed

• A rare race condition which caused connection handshakes to erroneously fail (#17251)
• Merge tombstone summaries when applying remote tombstone (#17654)
• Set default TCP_NODELAY socket option to true to improve latency, notably on Linux (#17673)
• DQL collation order is based on length of arrays / objects, not padding values with extra MISSING (#QE-171)
• DQL scalar function clock() and “local” timezone in WASM environments (#QE-181)
• Error propagation from collection scans (#QE-191)
• Collation in DQL aggregates (#QE-216)
• Panic when using COUNT(*) in a dynamic object (#QE-219)
• Panic when subtracting a large positive integer value from a large negative integer value (#QE-223)
• Aggregate output on empty result sets (#QE-242)

​

Performance

• Revised fetching documents by ID in Query to operate in batches (#QE-248)

​

Other

• Added a warning to the log when a subscription query uses a LIMIT or ORDER BY (#QE-169)

​

4.12.0 JavaScript & React Native Specific Changes

• Added method QueryResult.mutatedDocumentIDsV2(), use this instead of the deprecated QueryResult.mutatedDocumentIDs. (#SDKS-907) Deprecated: class BasePendingCursorOperation, use DQL (Ditto Query Language) instead. (#SDKS-907)

​

4.11.4 Common Changes

​

4.11.3 Common Changes

​

4.11.2 JavaScript & React Native Specific Changes

​

4.11.2 Common Changes

​

4.11.1 Common Changes

​

4.11 - DQL & Legacy Compatibility

​

DQL Strict Mode

• Objects are treated as CRDT maps
• Scalars and arrays are treated as Registers
• Counters and attachments are inferred from operations
• Collection definitions are no longer required

ALTER SYSTEM SET DQL_STRICT_MODE = false

​

DQL Differ

​

DQL Transactions

​

Advanced DQL Features

​

Tombstone Reaper

• Automatically removes tombstones after a configurable time-to-live (TTL)
• Uses read transactions for efficiency
• Includes random scheduling variations to prevent performance problems
• Tracks the number of tombstones evicted for monitoring
• Remembers previous reap times across restarts

​

4.11.0 Common Changelog

​

Added

• Introduced DQL_STRICT_MODE as a system parameter to opt-out of requiring collection definitions (#16573)
• Added transports_ble_adapter_mac system parameter to allow configuring bluetooth adapter MAC address (#16159)
• Small peers now log the result of OnlinePlayground authentication, including if the supplied shared token was incorrect (#16301)
• A cumulative running total of the number of tombstones ever evicted by the reaper to Small Peer Info (#16424)
• Added system parameters to fine-tune Ditto behavior when it encounters a locked persistence directory (#CORE-479)
• New DQL CAST function to explicitly cast between types (#QE-108)
• Conditional functions for unknowns (#QE-111)
• DQL DATE processing functions (#QE-112)
• New DQL clause USE IDS for direct document retrieval by id (#QE-114)
• Additional string functions (#QE-124)
• SIMILAR TO filter expression (#QE-125)
• PN_COUNTER support (APPLY clause in UPDATE statement) (#QE-127)
• UNSET clause to the UPDATE statement (#QE-128)
• MERGE as an alias for UPDATE in the ON ID CONFLICT clause of an INSERT statement (#QE-132)
• The ability to use runtime-evaluated expressions in object constructs in DQL statements (#QE-133)
• The ability to use runtime-evaluated expressions in array constructs in DQL statements (#QE-134)
• Static expressions in mutators (#QE-139)
• Additional object manipulation DQL scalar functions (#QE-159)
• EXPLAIN for query plan (#QE-105)
• More detailed messages about network problems are logged when authentication fails (#SEC-124)

​

Changed

• Use the modern rustls-platform-verifier TLS crate on all platforms rather than rustls-native-certs (#15956)
• The tombstone reaper scheduling to remember the previous reap times across shutdown and restart of the small peer (#16237)
• The tombstone reaper scheduling code to add random variations, which helps prevent performance problems if/when all peers run the reaper simultaneously (#16286)
• The tombstone reaper to be enabled by default for all SDKs (#16350)
• INSERT to now accept either DOCUMENTS or VALUES as the keyword introducing the list of values to insert (#QE-140)
• Date functions accept “local” as a timezone & use the executing peer’s local timezone (#QE-168)
• Unified dedicated TCP servers across different transports (Multicast, mDNS, and WiFi Aware) by utilizing a single static TCP server implementation (#TRANS-131)

​

Fixed

• Fixed issue where user_collection_sync_scopes could not be updated after initial set (#16299)
• Fixed: A rare crash on Android devices when shutting down Bluetooth. (#17069)
• Fixed: Bluetooth connectivity on Apple devices works when background Bluetooth permissions are missing. (#17065)

​

Removed

• Old multihop networking stack and system parameter network_use_nextgen_multihop_stack (#16370)
• TLS certificate compression, which led to incompatibilities (#SDKS-1046)

​

Performance

• The tombstone reaper uses read transactions for some of its work instead of holding open a long-running write transaction, thus reducing contention on the small peer database (#16338)
• Improved speed and efficiency of the tombstone reaping process (#16535)

​

4.11.0 JavaScript & React Native Specific Changes

​

4.10.5 JavaScript & React Native Specific Changes

​

4.10.5 Common Changes

​

4.10.2 Common Changes

​

4.10.1 Common Changes

​

4.10.0 New Capabilities

​

Support for React Native Expo CNG

​

Small Peer User Collection Sync Scopes

​

Details

ALTER SYSTEM SET USER_COLLECTION_SYNC_SCOPES = {
    collection_1: "BigPeerOnly",
    collection_2: "SmallPeersOnly",
    collection_3: "LocalPeerOnly"
}

var syncScopes = [
"local_mesh_orders": "SmallPeersOnly"
];
await ditto.store.execute(
    query: "ALTER SYSTEM SET USER_COLLECTION_SYNC_SCOPES = :syncScopes",
    arguments: ["syncScopes": syncScopes]);

​

Store Observers Run Multi-Threaded By Default

ALTER SYSTEM SET STORE_OBSERVERS_NUM_THREADS = 1

​

DQL Support for LIKE operator

SELECT * FROM my_collection
WHERE some_field LIKE 'abc%'

​

DQL New Objects operators

• object_keys(object): Returns an array with all the keys in the given object.
• object_values(object): Returns an array with all the values in object.

​

DQL LIMIT/ OFFSET / ORDER BY support for mutation operators

EVICT FROM users
WHERE status = 'inactive'
LIMIT 10;

​

4.10.0 JavaScript & React Native Specific Changelog

• Add support for React Native Expo CNG
• Repeated reads of a document using an ID-specific transaction cursor of the query builder API now reflect changes made in the same transaction. (SDKS-245)
• Errors regarding missing dittoffi_query_result_item_free() and ditto_clear_presence_callback() functions in React Native. (#SDKS-555)
• Interface IdentityOnlinePlayground to add property customAuthURL and allow authenticating with a custom authentication service instead of the Ditto cloud. (#15904)

​

4.10.0 Common Changelog

​

Added

• In DQL, an element of an array can be accessed by expression. (feat: Disallow double-quoted identifiers inside of new operators #12529)
• DQL field and element selection (DQL: Support field and element selection. #13920)
• Full support for DQL virtual collections, and the system:dual virtual collection (parser changes for virtual collections #14954)
• On Android, logs now indicate if a missing permission is not declared in an app’s AndroidManifest.xml. (Clarify Android missing permission messages based on manifest presence #15094)
• LIKE DQL operator. (#QE-104)
• Added object_keys and object_values scalar DQL functions. (#QE-73)
• A new system parameter network_use_nextgen_multihop_stack to switch between our existing and experimental next generation network stacks (#15362)
• Enabled zlib certificate compression (RFC8879) for small peer TLS 1.3 session negotiation ([security] enable TLS certificate compression #15801)
• A system parameter to selectively disable the BLE peripheral (server) transport. (New System Parameter: transports_ble_peripheral_is_enabled #15802)
• A STORE_OBSERVERS_NUM_THREADS system parameter to tweak the number of threads used by our store observer runtime to dispatch the sdk callback invocations. (Allow different live queries to be called in parallel #15814)
• The ability to configure a TTL for tombstones in the small peer store, which can be cleaned up via periodic sweeps from the new “reaper” module. (Finish porting the prototype tombstone reaping implementation into main #15886)
• Support for the new DQL DELETE statement on small peers. (Stabilize the DQL DELETE statement on SP #15899)
• A new Small Peer Info section named “reaper” and a field named “last_reap_time” that indicates the last time the tombstone reaper successfully completed its cleanup of expired tombstones. (Add small-peer-info field to track last completed reaper timestamp #16000)
• Add support for LIMIT, OFFSET and ORDER BY for mutation statements iin DQL (feat(query/dql): Support ORDER BY, LIMIT, OFFSET in mutation statements #16020)
• Added support for permanent system wide document deletes with the DELETE keyword in DQL for all Small Peer SDKs. This change requires a coordinated release with Ditto Server to ensure data is completely removed from the system. The Ditto Server change will be rolling out over the coming month and Ditto will be making a larger announcement upon release. For usage on deletes prior to the larger announcement see the following documents. Note that these documents are only accessible via their direct links and will not appear in the navigation bar just yet.
  • DQL DELETE Syntax
  • Removing Documents with Deletes
  For support on using deletes in your application reach out to Ditto Customer Support to get started.

​

Fixed

• A rare scenario where stopping sync with in-progress TCP connections could result in a crash. (#TRANS-303)
• If Ditto’s storage is cloned to another device (not recommended), clones which connect to each other will automatically create a new Peer Key and reauthenticate so that sync can proceed (Trigger reset peer identity when another peer clearly has the same Peer Key, add integration test #14653)
• On Android, missing system permissions will no longer crash Ditto. (Check for normal permissions in DittoSyncPermissions #15233)
• Fixed initialisation failing for the Linux Bluetooth transport due to invalid dbus messages (Linux BLE baseline bugfixes #15717)
• Fix OldQL and DQL comparison between large floats and integers returning an arbitrary result (Fix OldQL and DQL comparison between large floats and integers #15949)
• Reduced duplicate notifications for authentication state changes. (auth client: validity_update only on changes #15981)

​

Changed

• The link_enabled system parameter has been renamed to network_enable_multihop ([network] Add configurability for switching between legacy & next gen networking stacks #15362)
• Improve authentication flow over bad connections (Auth client/server improvements around challenge tokens #15430)
• Store observers are now invoked in parallel by default. (STORE_OBSERVERS_NUM_THREADS to default to 0 = num_cpus #16064)
• Synchronization protocol enhanced to reduce re-transmission of document data and metadata after eviction. (#DS-188)

​

4.9.4 Common Changes

​

4.9.3 JavaScript & React Native Specific Changes

​

4.9.3 Common Changes

​

4.9.1 JavaScript & React Native Specific Changes

​

4.9.1 Common Changes

​

4.9.0 JavaScript Release Notes

​

4.9.0 Logging Clarity Improvements

• More information is included when physical connections start and end (such as the type of connection and the remote peer’s key)
• Devices that try to connect to themselves now say “device has connected to itself” instead of an error about the remote site ID being the same as the local peer
• Authentication client logs have been made more human readable. In particular:
• Recoverable errors are logged together with information about the recovery actions that are being undertaken
• Several abbreviations have been replaced with human-readable descriptions
• Errors are printed using their descriptions rather than by dumping debug representations
• Abbreviated jargon has been replaced with clearer wording in various log lines
• “phy ended” has become “physical connection ended”
• “error getting char” has become “required Ditto BLE characteristic not found”
• “error getting radio state” has become “error getting Windows Bluetooth radio state”
• Peer events that represent a transport starting or stopping are now logged with the message “transport started”/”transport ended” rather than as a raw peer_event=… log
• Reduced the noisiness of “starting” log lines around Ditto initialization time
• Noise from long peer keys in logs has been reduced in some places by replacing them with their truncated form
• Noise from duplicated peer ID/peer key information in replication logs has been reduced by consolidating the information
• “starting compression and export” is now followed by a “completed export” log at the same level
• Successful SQLite WAL recovery logs were further lowered from INFO to DEBUG

​

4.9.0 Common Changes

​

4.8.4 Common Release Notes

​

4.8.3 React Native Specific Changes

​

4.8.3 Common Release Notes

​

4.8.2 JavaScript Specific Changes

​

4.8.2 Common Changes

​

4.8.1 JavaScript Release Notes

​

4.8.1 Common Changes

​

4.8.0 JavaScript Release Notes

​

4.8.0 Common Changes

• Timestamp (YYYY-MM-DDTHH:MM.ssssss):
• Format: ISO 8601 format (e.g., 2024-08-16T14:23:05.123456Z).
• Description: The exact time when the log entry was generated.
• Log Level (LEVEL):
• Format: One of the following levels— DEBUG, INFO, WARNING, ERROR.
• Description: Indicates the severity or importance of the log entry.
• Target (TARGET):
• Format: A string, representing the internal module/area
• Description: The source of the log entry, often corresponding to the internal module where the error was called. (e.g. ditto_init or peer_event_listener)
• Message (MESSAGE):
• Format: A human-readable string.
• Description: The main content of the log entry.
• Structured Fields ({field1=value1, field2=value2, …}):
• Format: A list of key-value pairs enclosed in curly braces.
• Description: Any additional structured data associated with the log entry. These fields can include custom data that you attach to the log entry using the tracing::info!(field1 = value1, “Message”) syntax.

​

ADDED: Support for specifying the mDNS connection ports for Small Peer SDK instances

ALTER SYSTEM SET tcp_server_bind_mdns_server_port TO 1234

let result = await ditto.store.execute(query: "ALTER SYSTEM SET tcp_server_bind_mdns_server_port TO 1234")

​

FIXED: A finalizer issue that was causing crashes when closing sync subscriptions (#13849, #13732)

​

IMPROVED: (Linux) Increased mDNS multicast TTL from 1 to 255 to allow for cross-VLAN announcements (#13590)

​

FIXED: An issue causing a race condition with a Live Query callback. (#13180)

​

FIXED: A compatibility issue between documents created using different CRDT versions. (#13236)

​

IMPROVED: Logging for batch write transactions. (#13339, #13339)

​

FIXED: An issue in the Small Peer datastore causing slow performance or process termination. (#13395)

​

IMPROVED: The Ditto SDKs compilation performance. (#13174)

​

ADDED: mesh_chooser_avoid_redundant_bluetooth system parameter. (#12984)

• Benefits:
  • Provides additional fallback to maintain connection stability when primary connections cease to function.
  • Reduces time to establish new connections since, by having multiple fallback options, Ditto can more quickly re-establish connections.
• Drawbacks:
  • Increases network traffic since using multiple transports results in additional network overhead
  • Using Bluetooth LE alongside other transports increases resource consumption. In the worst case, enabling this setting can result in slower sync performance and battery drain.

​

CHANGED: The AuthClient by improving the log message for certificate issues returned from the Big Peer Authentication Service. (#13138)

​

FIXED: A performance-related issue affecting bulk eviction operations for documents with attachments. (#12331)

​

ADDED: Improved data compression in the handshake protocol when establishing connections to other peers. (#11264)

​

FIXED: The HTTP protocol by adding content-length: 0 header compliance in empty POST requests. (#12346)

​

FIXED: Handling of malformed subscription queries in the legacy query builder API by adding validation logic early to ensure invalid queries are identified early and logged. (#12463)

​

FIXED: Subscription assumption logic so all acknowledgments are processed, regardless of subscription changes. (#12541)

​

FIXED: Sync inefficiency where changing a subscription resulted in unnecessary syncing of redundant update files. (#12967)

​

FIXED: Compatibility issues preventing the React Native SDK from working correctly with older versions of Ditto. (#12641)

​

FIXED: Blank deviceName property on Ditto instances in React Native apps. (#12661)

​

Fixed: Misleading errors related to attachment fetch operations. Now, associated error logs are more accurate and clear. (#12409)

​

Fixed: A specific issue where incorrect sync metadata rollback caused problems with data consistency at merge. (#12422)

​

Fixed: Potential for a crash when closing a Ditto instance immediately following operations such as setPeerMetadata or setPeerMetadataJSON. (#12351)

​

Fixed: Potential crash when closing a Ditto instance after calling Ditto.presence.observe() to register a presence observer. (#12388)

​

Fixed: Premature triggering of asynchronous callback handlers created using observeLocal(). (#12388)

​

FIXED: The legacy query language error-throwing mechanism to accept invalid field names properly.

​

REMOVED: The is_connected_to_big_peer field from the Small Peer Info document. (#11883)

​

CHANGED: The small peer info JSON metadata by increasing the depth limit to 64 levels. (#11891)

​

IMPROVED: Transaction contention time for the ATTACHMENT data type, reducing potential timeouts. (#11893)

​

FIXED: The issue causing sync crashes due to missing update files. (#11952)

​

ADDED: Parameters for adjusting certain settings during runtime

​

RESOLVED: The warning message when peers connect is no more. (#11977)

​

IMPROVED: Sync performance by sending the entire state of the data. (#12029)

​

FIXED: The issue causing the Small Peer Info collector to become stuck. (#12051)

​

FIXED: The issue causing fetch operations to appear unresponsive. (#9501, #12067)

​

FIXED: The issue causing type check failures when setting duration parameters through ALTER SYSTEM SET. (#12070)

​

Fixed (Linux): Similar to .NET, LAN communication (P2P) failed to advertise on newly added interfaces, such as enabling WiFi at runtime.

​

Added: Similar to .NET, setting the deviceName property of a Ditto instance after sync has started will now log a warning, indicating that changes to the value only take effect after restarting sync.

​

Fixed: The deviceName property on Ditto instances now correctly reflects the truncation of the value to 24 bytes when starting sync.

​

Changed: Documentation for DittoError has been updated to include a usage example.

​

Added: Implemented the connectionRequestHandler property in ditto.presence, enabling filtering of incoming connections from other peers.

​

Added: Introduced the peerMetadataJSONString property and the setPeerMetadataJSONString method to ditto.presence.

​

Added: Introduced the peerMetadata property and the setPeerMetadata() method to ditto.presence.

​

Added: Added properties peerMetadata and identityServiceMetadata to Peer.

​

Added: Implemented the peerKeyString property in Peer as a replacement for the now deprecated peerKey.

​

Deprecated: The peerKey property on Peer has been deprecated. Use the newly added peerKeyString property instead.

​

Fixed: Addressed an issue where DQL methods might include sensitive information in error logs. This fix removes the query string and query arguments from the context property of errors thrown by ditto.store.execute(), ditto.store.registerObserver(), and ditto.sync.registerSubscription() methods.