Динамическая компиляция пользовательских функций на языке PL/pgSQL

In recent years, as performance and capacity of main and external memory grow, performance of database management systems (DBMSes) on certain kinds of queries is more determined by raw CPU speed. Currently, PostgreSQL uses the interpreter to execute SQL queries. This yields an overhead caused by indirect calls to handler functions and runtime checks, which could be avoided if the query were compiled into native code "on-the-fly", i.e. just-in-time (JIT) compiled: at run time the specific table structure is known as well as data types and built-in functions used in the query as well as the query itself. This is especially important for complex queries, performance of which is CPU-bound. We’ve developed a PostgreSQL extension that implements SQL query JIT compilation using LLVM compiler infrastructure. In this paper we show how to implement JIT compilation to speed up sequential scan operator (SeqScan) as well as expressions in WHERE clauses. We describe some important optimizations that are possible only with dynamic compilation, such as precomputing tuple attributes offsets only for attributes used by the query. We also discuss the maintainability of our extension, i.e. the automation for translating PostgreSQL backend functions into LLVM IR, using the same source code both for our JIT compiler and the existing interpreter. Currently, with LLVM JIT we achieve up to 5x speedup on synthetic tests as compared to original PostgreSQL interpreter.

Efficient XML-to-SQL Query Translation: Where to Add the Intelligence?

We study the problem of evaluating xpath queries over xml data that is stored in an rdbms via schema-based shredding. The interaction between recursion (descendants-axis) in xpath queries and recursion in dtds makes it challenging to answer xpath queries using rdbms. We present a new approach to translating xpath queries into sql queries based on a notion of extended XP ath expressions and a simple least fixpoint (lfp) operator. Extended xpath expressions are a mild extension of xpath, and the lfp operator takes a single input relation and is already supported by most commercial rdbms. We show that extended xpath expressions are capable of capturing both dtd recursion and xpath queries in a uniform framework. Furthermore, they can be translated into an equivalent sequence of sql queries with the lfp operator. We present algorithms for rewriting xpath queries over a (possibly recursive) dtd into extended xpath expressions and for translating extended xpath expressions to sql queries, as well as optimization techniques. The novelty of our approach consists in its capability to answer a large class of xpath queries by means of only low-end rdbms features already available in most rdbms, as well as its flexibility to accommodate existing relational query optimization techniques. In addition, these translation algorithms provide a solution to query answering for certain (possibly recursive) xml views of xml data. Our experimental results verify the effectiveness of our techniques.

We demonstrate I-Rex 1 , a system designed to help users understand SQL query evaluation and debug SQL queries. I-Rex lets users interactively "trace" the evaluation of complex SQL queries, including those with correlated subqueries. I-Rex also explains why a query returns an incorrect answer with respect to a reference query over a test database instance---a common use case in education and software regression testing. To avoid the cognitive overload caused by debugging over a large database instance, I-Rex lets users focus on smaller instances contained in the large one (which we call "counterexamples") that still distinguish the two queries. Supporting these features for SQL queries poses two key challenges. First, unlike debugging for procedural languages, it is not clear how to trace a declarative SQL query, because its execution plan often differs from how it was originally written. I-Rex offers a novel interface for tracing SQL query evaluation in a way faithful to how queries are written syntactically, even for complex queries involving multiple levels of nesting and correlation. Second, we need a method for finding small counterexamples that handles the complexity of practical SQL. I-Rex extends provenance support for SQL in non-trivial ways to work with various query constructs. This demonstration walks through use cases in which I-Rex helps users understand and debug SQL queries.

Extensible Markup Language (XML) is a popular format for the interchange of data between disparate, heterogeneous systems. However, when hierarchical data is exchanged between relational database management systems (RDBMSs), the full expressive power of XML is often ignored. A major factor is the inability of RDBMSs to manipulate hierarchical data, such as an organization chart. We address this by replacing traditional link tables with a nested set approach, which has several benefits: SQL queries may be used to extract data subtrees and perform aggregate functions on them; nested set data may be converted to and from the hierarchical XML form; and the hierarchical structure is explicitly included with the data. It is important for servers to be able to manipulate subtrees quickly and efficiently in order to fill data requests from their clients. In this paper, we adapt a nested set approach to include recursive, hierarchical XML data. An algorithm is presented to use nested set information to restore the original XML document without requiring the entire tree to be reconstructed by a procedural language, and it is tested and evaluated with a prototype application. Our approach allows two relational systems to efficiently exchange subtree data as XML hierarchical data instead of merely using XML as a wrapper around relational data.

We consider the scenario where existing relational data is exported as XML. In this context, we look at the problem of translating XML queries into SQL. XML query languages have two different notions of duplicates: node-identity based and value-based. Path expression queries have an implicit node-identity based duplicate elimination built into them. On the other hand, SQL only supports value-based duplicate elimination. In this paper, using a simple path expression query we illustrate the problems that arise when we attempt to simulate the node-identity based duplicate elimination using value-based duplicate elimination in the SQL queries. We show how a general solution for this problem covering the class of views considered in published literature requires a fairly complex mechanism.

In this paper, we introduce a novel framework for processing SQL queries directly over encrypted financial data using homomorphic encryption (HE). Our solution preserves data confidentiality without sacrificing essential SQL operations, including SELECT, WHERE, SUM, and AVG, all performed on encrypted columns. Unlike traditional approaches that rely on trusted execution environments or secure multiparty computation, our method ensures that data remains encrypted throughout the entire query lifecycle at rest, in transit, and during computation. We present a prototype implementation and evaluate its performance using realistic financial datasets. The results demonstrate a practical balance between computational overhead and data privacy, offering a promising foundation for secure, privacy-preserving analytics in cloud-based financial systems

In the present computing and fast growing world, information plays a key role in humans day to day life. Information can be stored in databases in an organized manner and SQL allows users to access process and manage data on database. But not all users are accustomed to writing SQL queries. This resulted in need for non-sophisticated users to access the database using natural language. A Natural Language Interface for Database (NLIDB) acts as an intelligent interface for database which allows users to access the database by typing requests in their natural language. The paper is going to discuss about a NLIDB system based on HMM. The system contains two modules: linguistic module and database module. Linguistic module makes uses of various Natural Language Processing (NLP) techniques which are used to identify tokens that correspond to the constraints and predicates required to build possible SELECT, WHERE and FROM clauses. The output of linguistic module is passed to database module. Identified tokens are then mapped to SQL query tokens using a query translation algorithm. A template is being used to generate corresponding SQL query. If the algorithm fails to identify sufficient details, a query prediction module based on HMM identifies the attributes and predicates list. The system used either a template based system that produce nearly 100% correct result to user query or prediction module that generate a highest probable result that matches to the user query. No situation exists in which the system fails to produce a result to the NL query.

High-level language virtual machines (HLL VMs) are now widely used to implement high-level programming languages. To a certain extent, their widespread adoption is due to the software engineering benefits provided by these managed execution environments, for example, garbage collection (GC) and cross-platform portability. Although HLL VMs are widely used, most research has concentrated on high-end optimizations such as dynamic compilation and advanced GC techniques. Few efforts have focused on introducing features that automate or facilitate certain software engineering activities, including software testing. This paper suggests that HLL VMs provide a reasonable basis for building an integrated software testing environment. As a proof-of-concept, we have augmented a Java virtual machine (JVM) to support weak mutation analysis. Our mutation-aware HLL VM capitalizes on the relationship between a program execution and the underlying managed execution environment, thereby speeding up the execution of the program under test and its associated mutants. To provide some evidence of the performance of our implementation, we conducted an experiment to compare the efficiency of our VM-based implementation with a strong mutation testing tool (muJava). Experimental results show that the VM-based implementation achieves speedups of as much as 89% in some cases.

A database is a collection of some knowledge. Knowledge can be presented as some semantic network. Entity-relationship model is one of representations of the semantic network. When using the entity-relationship model, it is possible to distinguish entities and relations between these entities. The entity-relationship model can be then converted into a database schema. The user interacts with the database by writing requests and receiving answers containing the requested information. There are several ways to write queries to databases with different convenience of creating and speed of execution. The article reviews three different approaches to writing queries: SQL query, Prolog query and Object-Relational Mapping (ORM) query. Each of the approaches has its own advantages and disadvantages. You need to know the basics of relational algebra to write queries with the SQL language, while ORM libraries and the Prolog don't require any additional knowledge. Writing queries with the Prolog language is similar to writing text in natural language, which makes these queries understandable for people who have never worked with databases. There was made the comparison of the plainness of the approaches when explaining them to listeners who are studying databases. The listeners participated in the compilation were divided into groups according to their specialties. The following groups took part in the study: first-year students of an economic and managerial specialty, engineering students and students with a specialty software engineering. The purpose of this comparison is to determine the method of compiling database queries, which is most suitable for teaching students of various specialties.

Preface: Volume 59, Issue 4

Just-in-time compilation of SQL queries into native code has recently emerged as a viable technique for query processing and an alternative to the dominant interpretation-based approach. We present the salient results of research in this fresh area, addressing all aspects of the query processing stack: from traditional query compilation techniques, to compilation in managed environments, to state-of-the-art approaches on intermediate and native code emission. Throughout the discussion we refer and draw analogies to the general code generation techniques used in contemporary compiler technology. At the same time we describe the open research problems of the area.

Software Integration of Real-Time Expert Systems

Intuitionistic logic programming provides the notion of embedded implication in rule bodies, which can be used to reason about a current database modified by the antecedent. This can be applied to a system that translates SQL to Datalog to solve SQL WITH queries, for which relations are locally defined and can therefore be understood as locally added to the current database. In addition, assumptions in SQL queries as either adding or removing data can be modelled in this way as well, which is an interesting feature for decision-support scenarios. This work suggests a way to apply intuitionistic logic programming to SQL, and provides a pointer to a working system implementing this idea.

In Long Term Evolution-Advanced (LTE-A) networks, Mobile Relay Nodes (MRNs) are installed in fast moving buses and trains to connect the passengers' devices to evolved Node B (eNB). However, since the MRNs and eNBs are installed in open environment, they can be compromised to launch security and privacy attacks. In this paper, we propose a privacy preserving intra Mobility Management Entity (MME) group handover scheme in LTE-A networks for repeated trips. Comparing to the existing schemes, the proposed scheme is devised to achieve the following requirements. First, the MRNs should be able to authenticate the received messages so that the messages sent from external attackers can be dropped by the MRNs rather than forwarding them to the core network. Second, the proposed scheme also aims to reduce the computational and signaling overhead and establish secure session keys. Third, the scheme aims to prevent MRNs and eNBs from tracking passengers' locations especially if they take same trip regularly. Our analysis demonstrates that the proposed scheme can achieve our security and privacy objectives. Our performance evaluations demonstrate that the proposed scheme requires a few number of messages and low computation overhead.