by Joche Ojeda | Jan 22, 2025 | ADO.NET, C#, Data Synchronization, Database, DevExpress, XPO, XPO Database Replication
SyncFramework for XPO is a specialized implementation of our delta encoding synchronization library, designed specifically for DevExpress XPO users. It enables efficient data synchronization by tracking and transmitting only the changes between data versions, optimizing both bandwidth usage and processing time.
What’s New
- Base target framework updated to .NET 8.0
- Added compatibility with .NET 9.0
- Updated DevExpress XPO dependencies to 24.2.3
- Continued support for delta encoding synchronization
- Various performance improvements and bug fixes
Framework Compatibility
- Primary Target: .NET 8.0
- Additional Support: .NET 9.0
Our XPO implementation continues to serve the DevExpress community.
Key Features
- Seamless integration with DevExpress XPO
- Efficient delta-based synchronization
- Support for multiple database providers
- Cross-platform compatibility
- Easy integration with existing XPO and XAF applications
As always, if you own a license, you can compile the source code yourself from our GitHub repository. The framework maintains its commitment to providing reliable data synchronization for XPO applications.
Happy Delta Encoding! 馃殌
by Joche Ojeda | Jan 20, 2025 | ADO, ADO.NET, Database, dotnet
When I first encountered the challenge of migrating hundreds of Visual Basic 6 reports to .NET, I never imagined it would lead me down a path of discovering specialized data analytics tools. Today, I want to share my experience with ADOMD.NET and how it could have transformed our reporting challenges, even though we couldn’t implement it due to our database constraints.
The Challenge: The Sales Gap Report
The story begins with a seemingly simple report called “Sales Gap.” Its purpose was critical: identify periods when regular customers stopped purchasing specific items. For instance, if a customer typically bought 10 units monthly from January to May, then suddenly stopped in June and July, sales representatives needed to understand why.
This report required complex queries across multiple transactional tables:
- Invoicing
- Sales
- Returns
- Debits
- Credits
Initially, the report took about a minute to run. As our data grew, so did the execution time鈥攅ventually reaching an unbearable 15 minutes. We were stuck with a requirement to use real-time transactional data, making traditional optimization techniques like data warehousing off-limits.
Enter ADOMD.NET: A Specialized Solution
ADOMD.NET (ActiveX Data Objects Multidimensional .NET) emerged as a potential solution. Here’s why it caught my attention:
Key Features:
-
Multidimensional Analysis
Unlike traditional SQL queries, ADOMD.NET uses MDX (Multidimensional Expressions), specifically designed for analytical queries. Here’s a basic example:
string mdxQuery = @"
SELECT
{[Measures].[Sales Amount]} ON COLUMNS,
{[Date].[Calendar Year].MEMBERS} ON ROWS
FROM [Sales Cube]
WHERE [Product].[Category].[Electronics]";
-
Performance Optimization
ADOMD.NET is built for analytical workloads, offering better performance for complex calculations and aggregations. It achieves this through:
- Specialized data structures for multidimensional analysis
- Efficient handling of hierarchical data
- Built-in support for complex calculations
-
Advanced Analytics Capabilities
The tool supports sophisticated analysis patterns like:
string mdxQuery = @"
WITH MEMBER [Measures].[GrowthVsPreviousYear] AS
([Measures].[Sales Amount] -
([Measures].[Sales Amount], [Date].[Calendar Year].PREVMEMBER)
)/([Measures].[Sales Amount], [Date].[Calendar Year].PREVMEMBER)
SELECT
{[Measures].[Sales Amount], [Measures].[GrowthVsPreviousYear]}
ON COLUMNS...";
Lessons Learned
While we couldn’t implement ADOMD.NET due to our use of Pervasive Database instead of SQL Server, the investigation taught me valuable lessons about report optimization:
- The importance of choosing the right tools for analytical workloads
- The limitations of running complex analytics on transactional databases
- The value of specialized query languages for different types of data analysis
Modern Applications
Today, ADOMD.NET continues to be relevant for organizations using:
- SQL Server Analysis Services (SSAS)
- Azure Analysis Services
- Power BI Premium datasets
If I were facing the same challenge today with SQL Server, ADOMD.NET would be my go-to solution for:
- Complex sales analysis
- Customer behavior tracking
- Performance-intensive analytical reports
Conclusion
While our specific situation with Pervasive Database prevented us from using ADOMD.NET, it remains a powerful tool for organizations using Microsoft’s analytics stack. The experience taught me that sometimes the solution isn’t about optimizing existing queries, but about choosing the right specialized tools for analytical workloads.
Remember: Just because you can run analytics on your transactional database doesn’t mean you should. Tools like ADOMD.NET exist for a reason, and understanding when to use them can save countless hours of optimization work and provide better results for your users.
by Joche Ojeda | Jun 21, 2024 | Database
Understanding ASCII vs. UTF-8 in Database Queries: A Practical Guide
When dealing with databases, understanding how different character encodings impact queries is crucial. Two common encoding standards are ASCII and UTF-8. This blog post delves into their differences, how they affect case-sensitive queries, and provides practical examples to illustrate these concepts.
ASCII vs. UTF-8: What’s the Difference?
ASCII (American Standard Code for Information Interchange)
- Description: A character encoding standard using 7 bits to represent each character, allowing for 128 unique symbols. These include control characters (like newline), digits, uppercase and lowercase English letters, and some special symbols.
- Range: 0 to 127.
UTF-8 (8-bit Unicode Transformation Format)
- Description: A variable-width character encoding capable of encoding all 1,112,064 valid character code points in Unicode using one to four 8-bit bytes. UTF-8 is backward compatible with ASCII.
- Range: Can represent characters in a much wider range, including all characters in all languages, as well as many symbols and special characters.
ASCII and UTF-8 Position Examples
Let’s compare the positions of some characters in both ASCII and UTF-8:
| Character |
ASCII Position |
UTF-8 Position |
| A |
65 |
65 |
| B |
66 |
66 |
| … |
… |
… |
| Y |
89 |
89 |
| Z |
90 |
90 |
| [ |
91 |
91 |
| \ |
92 |
92 |
| ] |
93 |
93 |
| ^ |
94 |
94 |
| _ |
95 |
95 |
| ` |
96 |
96 |
| a |
97 |
97 |
| b |
98 |
98 |
| … |
… |
… |
| y |
121 |
121 |
| z |
122 |
122 |
| Last ASCII (DEL) |
127 |
127 |
| 每 |
Not present |
195 191 (2 bytes) |
Case Sensitivity in Database Queries
Case sensitivity can significantly impact database queries, as different encoding schemes represent characters differently.
ASCII Example
-- Case-sensitive query in ASCII-encoded database
SELECT * FROM users WHERE username = 'Alice';
-- This will not return rows with 'alice', 'ALICE', etc.
UTF-8 Example
-- Case-sensitive query in UTF-8 encoded database
SELECT * FROM users WHERE username = '脜lice';
-- This will not return rows with '氓lice', '脜LICE', etc.
Practical Example with Positions
For ASCII, the characters included in the range >= 'A' and <= 'z' are:
A has a position of 65.a has a position of 97.
In a case-sensitive search, these positions are distinct, so A is not equal to a.
For UTF-8, the characters included in this range are the same since UTF-8 is backward compatible with ASCII for characters in this range.
Query Example
Let’s demonstrate a query example for usernames within the range >= 'A' and <= 'z'.
-- Query for usernames in the range 'A' to 'z'
SELECT * FROM users WHERE username >= 'A' AND username <= 'z';
Included Characters
Based on the ASCII positions, the range >= 'A' and <= 'z' includes:
- All uppercase letters:
A to Z (positions 65 to 90)
- Special characters:
[, \, ], ^, _, and ` (positions 91 to 96)
- All lowercase letters:
a to z (positions 97 to 122)
Practical Example with Positions
Given the following table:
-- Create a table
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
-- Insert some users
INSERT INTO users (id, username) VALUES (1, 'Alice'); -- A = 65, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (2, 'alice'); -- a = 97, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (3, '脜lice'); -- 脜 = 195 133, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (4, '氓lice'); -- 氓 = 195 165, l = 108, i = 105, c = 99, e = 101
INSERT INTO users (id, username) VALUES (5, 'Z'); -- Z = 90
INSERT INTO users (id, username) VALUES (6, 'z'); -- z = 122
INSERT INTO users (id, username) VALUES (7, '每'); -- 每 = 195 191
INSERT INTO users (id, username) VALUES (8, '_special');-- _ = 95, s = 115, p = 112, e = 101, c = 99, i = 105, a = 97, l = 108
INSERT INTO users (id, username) VALUES (9, 'example'); -- e = 101, x = 120, a = 97, m = 109, p = 112, l = 108, e = 101
Query Execution
-- Execute the query
SELECT * FROM users WHERE username >= 'A' AND username <= 'z';
Query Result
This query will include the following usernames based on the range:
Alice (A = 65, l = 108, i = 105, c = 99, e = 101)Z (Z = 90)example (e = 101, x = 120, a = 97, m = 109, p = 112, l = 108, e = 101)_special (_ = 95, s = 115, p = 112, e = 101, c = 99, i = 105, a = 97, l = 108)alice (a = 97, l = 108, i = 105, c = 99, e = 101)z (z = 122)
However, it will not include:
脜lice (脜 = 195 133, l = 108, i = 105, c = 99, e = 101, outside the specified range)氓lice (氓 = 195 165, l = 108, i = 105, c = 99, e = 101, outside the specified range)每 (每 = 195 191, outside the specified range)
Conclusion
Understanding the differences between ASCII and UTF-8 character positions and ranges is crucial when performing case-sensitive queries in databases. For example, querying for usernames within the range >= 'A' and <= 'z' will include a specific set of characters based on their ASCII positions, impacting which rows are returned in your query results.
By grasping these concepts, you can ensure your database queries are accurate and efficient, especially when dealing with different encoding schemes.
by Joche Ojeda | Jun 21, 2024 | Database, ORM
Why Compound Keys in Database Tables Are No Longer Valid
Introduction
In the realm of database design, compound keys were once a staple, largely driven by the need to adhere to normalization forms. However, the evolving landscape of technology and data management calls into question the continued relevance of these multi-attribute keys. This article explores the reasons why compound keys may no longer be the best choice and suggests a shift towards simpler, more maintainable alternatives like object identifiers (OIDs).
The Case Against Compound Keys
Complexity in Database Design
- Normalization Overhead: Historically, compound keys were used to satisfy normalization requirements, ensuring minimal redundancy and dependency. While normalization is still important, the rigidity it imposes can lead to overly complex database schemas.
- Business Logic Encapsulation: When compound keys include business logic, they can create dependencies that complicate data integrity and maintenance. Changes in business rules often necessitate schema alterations, which can be cumbersome.
Maintenance Challenges
- Data Integrity Issues: Compound keys can introduce challenges in maintaining data integrity, especially in large and complex databases. Ensuring the uniqueness and consistency of multi-attribute keys can be error-prone.
- Performance Concerns: Queries involving compound keys can become less efficient, as indexing and searching across multiple columns can be more resource-intensive compared to single-column keys.
The Shift Towards Object Identifiers (OIDs)
Simplified Design
- Single Attribute Keys: Using OIDs as primary keys simplifies the schema. Each row can be uniquely identified by a single attribute, making the design more straightforward and easier to understand.
- Decoupling Business Logic: OIDs help in decoupling the business logic from the database schema. Changes in business rules do not necessitate changes in the primary key structure, enhancing flexibility.
Easier Maintenance
- Improved Data Integrity: With a single attribute as the primary key, maintaining data integrity becomes more manageable. The likelihood of key conflicts is reduced, simplifying the validation process.
- Performance Optimization: OIDs allow for more efficient indexing and query performance. Searching and sorting operations are faster and less resource-intensive, improving overall database performance.
Revisiting Normalization
Historical Context
- Storage Constraints: Normalization rules were developed when data storage was expensive and limited. Reducing redundancy and optimizing storage was paramount.
- Modern Storage Solutions: Today, storage is relatively cheap and abundant. The strict adherence to normalization may not be as critical as it once was.
Balancing Act
- De-normalization for Performance: In modern databases, a balance between normalization and de-normalization can be beneficial. De-normalization can improve performance and simplify query design without significantly increasing storage costs.
- Practical Normalization: Applying normalization principles should be driven by practical needs rather than strict adherence to theoretical models. The goal is to achieve a design that is both efficient and maintainable.
ORM Design Preferences
Object-Relational Mappers (ORMs)
- Design with OIDs in Mind: Many ORMs, such as XPO from DevExpress, were originally designed to work with OIDs rather than compound keys. This preference simplifies database interaction and enhances compatibility with object-oriented programming paradigms.
- Support for Compound Keys: Although these ORMs support compound keys, their architecture and default behavior often favor the use of single-column OIDs, highlighting the practical advantages of simpler key structures in modern application development.
Conclusion
The use of compound keys in database tables, driven by the need to fulfill normalization forms, may no longer be the best practice in modern database design. Simplifying schemas with object identifiers can enhance maintainability, improve performance, and decouple business logic from the database structure. As storage becomes less of a constraint, a pragmatic approach to normalization, balancing performance and data integrity, becomes increasingly important. Embracing these changes, along with leveraging ORM tools designed with OIDs in mind, can lead to more robust, flexible, and efficient database systems.
by Joche Ojeda | May 29, 2024 | Database, ORM
In today’s data-driven world, the need for more sophisticated and insightful data models has never been greater. Traditional database models, while powerful, often fall short of delivering the depth and breadth of insights required by modern organizations. Enter the augmented data model, a revolutionary approach that extends beyond the limitations of traditional models by integrating additional data sources, enhanced data features, advanced analytical capabilities, and AI-driven techniques. This blog post explores the key components, applications, and benefits of augmented data models.
Key Components of an Augmented Data Model
1. Integration of Diverse Data Sources
An augmented data model combines structured, semi-structured, and unstructured data from various sources such as databases, data lakes, social media, IoT devices, and external data feeds. This integration enables a holistic view of data across the organization, breaking down silos and fostering a more interconnected understanding of the data landscape.
2. Enhanced Data Features
Beyond raw data, augmented data models include derived attributes, calculated fields, and metadata to enrich the data. Machine learning and artificial intelligence are employed to create predictive and prescriptive data features, transforming raw data into actionable insights.
3. Advanced Analytics
Augmented data models incorporate advanced analytical models, including machine learning, statistical models, and data mining techniques. These models support real-time analytics and streaming data processing, enabling organizations to make faster, data-driven decisions.
4. AI-Driven Embeddings
One of the standout features of augmented data models is the creation of embeddings. These are dense vector representations of data (such as words, images, or user behaviors) that capture their semantic meaning. Embeddings enhance machine learning models, making them more effective at tasks such as recommendation, natural language processing, and image recognition.
5. Data Visualization and Reporting
To make complex data insights accessible, augmented data models facilitate advanced data visualization tools and dashboards. These tools allow users to interact with data dynamically through self-service analytics platforms, turning data into easily digestible visual stories.
6. Improved Data Quality and Governance
Ensuring data quality is paramount in augmented data models. Automated data cleansing, validation, and enrichment processes maintain high standards of data quality. Robust data governance policies manage data lineage, security, and compliance, ensuring that data is trustworthy and reliable.
7. Scalability and Performance
Designed to handle large volumes of data, augmented data models scale horizontally across distributed systems. They are optimized for high performance in data processing and querying, ensuring that insights are delivered swiftly and efficiently.
Applications and Benefits
Enhanced Decision Making
With deeper insights and predictive capabilities, augmented data models significantly improve decision-making processes. Organizations can move from reactive to proactive strategies, leveraging data to anticipate trends and identify opportunities.
Operational Efficiency
By streamlining data processing and integration, augmented data models reduce manual efforts and errors. This leads to more efficient operations and a greater focus on strategic initiatives.
Customer Insights
Augmented data models enable a 360-degree view of customers by integrating various touchpoints and interactions. This comprehensive view allows for more personalized and effective customer engagement strategies.
Innovation
Supporting advanced analytics and machine learning initiatives, augmented data models foster innovation within the organization. They provide the tools and insights needed to develop new products, services, and business models.
Real-World Examples
Customer 360 Platforms
By combining CRM data, social media interactions, and transactional data, augmented data models create a comprehensive view of customer behavior. This holistic approach enables personalized marketing and improved customer service.
IoT Analytics
Integrating sensor data, machine logs, and external environmental data, augmented data models optimize operations in manufacturing or smart cities. They enable real-time monitoring and predictive maintenance, reducing downtime and increasing efficiency.
Fraud Detection Systems
Using transactional data, user behavior analytics, and external threat intelligence, augmented data models detect and prevent fraudulent activities. Advanced machine learning models identify patterns and anomalies indicative of fraud, providing a proactive defense mechanism.
AI-Powered Recommendations
Embeddings created from user interactions, product descriptions, and historical purchase data power personalized recommendations in e-commerce. These AI-driven insights enhance customer experience and drive sales.
Conclusion
Augmented data models represent a significant advancement in the way organizations handle and analyze data. By leveraging modern technologies and methodologies, including the creation of embeddings for AI, these models provide a more comprehensive and actionable view of the data. The result is enhanced decision-making, improved operational efficiency, deeper customer insights, and a platform for innovation. As organizations continue to navigate the complexities of the data landscape, augmented data models will undoubtedly play a pivotal role in shaping the future of data analytics.
by Joche Ojeda | Jan 15, 2024 | ADO.NET, C#, Database, Sqlite
SQLite, known for its simplicity and lightweight architecture, offers unique opportunities for developers to integrate custom functions directly into their applications. Unlike most databases that require learning an SQL dialect for procedural programming, SQLite operates in-process with your application. This design choice allows developers to define functions using their application’s programming language, enhancing the database’s flexibility and functionality.
Scalar Functions
Scalar functions in SQLite are designed to return a single value for each row in a query. Developers can define new scalar functions or override built-in ones using the CreateFunction method. This method supports various data types for parameters and return types, ensuring versatility in function creation. Developers can specify the state argument to pass a consistent value across all function invocations, avoiding the need for closures. Additionally, marking a function as isDeterministic optimizes query compilation by SQLite if the function’s output is predictable based on its input.
Example: Adding a Scalar Function
connection.CreateFunction(
"volume",
(double radius, double height) => Math.PI * Math.Pow(radius, 2) * height);
var command = connection.CreateCommand();
command.CommandText = @"
SELECT name,
volume(radius, height) AS volume
FROM cylinder
ORDER BY volume DESC
";
Operators
SQLite implements several operators as scalar functions. Defining these functions in your application overrides the default behavior of these operators. For example, functions like glob, like, and regexp can be custom-defined to change the behavior of their corresponding operators in SQL queries.
Example: Defining the regexp Function
connection.CreateFunction(
"regexp",
(string pattern, string input) => Regex.IsMatch(input, pattern));
var command = connection.CreateCommand();
command.CommandText = @"
SELECT count()
FROM user
WHERE bio REGEXP '\w\. {2,}\w'
";
var count = command.ExecuteScalar();
Aggregate Functions
Aggregate functions return a consolidated value from multiple rows. Using CreateAggregate, developers can define and override these functions. The seed argument sets the initial context state, and the func argument is executed for each row. The resultSelector parameter, if specified, calculates the final result from the context after processing all rows.
Example: Creating an Aggregate Function for Standard Deviation
connection.CreateAggregate(
"stdev",
(Count: 0, Sum: 0.0, SumOfSquares: 0.0),
((int Count, double Sum, double SumOfSquares) context, double value) => {
context.Count++;
context.Sum += value;
context.SumOfSquares += value * value;
return context;
},
context => {
var variance = context.SumOfSquares - context.Sum * context.Sum / context.Count;
return Math.Sqrt(variance / context.Count);
});
var command = connection.CreateCommand
();
command.CommandText = @"
SELECT stdev(gpa)
FROM student
";
var stdDev = command.ExecuteScalar();
Errors
When a user-defined function throws an exception in SQLite, the message is returned to the database engine, which then raises an error. Developers can customize the SQLite error code by throwing a SqliteException with a specific SqliteErrorCode.
Debugging
SQLite directly invokes the implementation of user-defined functions, allowing developers to insert breakpoints and leverage the full .NET debugging experience. This integration facilitates debugging and enhances the development of robust, error-free custom functions.
This article illustrates the power and flexibility of SQLite’s approach to user-defined functions, demonstrating how developers can extend the functionality of SQL with the programming language of their application, thereby streamlining the development process and enhancing database interaction.
Github Repo
