Virtuoso Database FAQ

Object-Relational Database

Is Virtuoso a relational DBMS?

Virtuoso implements a traditional relational database engine's functionality. This functionality includes SQL (Structured Query Language) for relational data, a Query processor, View Support, Standard Datatype support including character, number and dates, stored procedures, concurrency support, transactions, distributed transactions, scrollable cursor support just to name a few.

What client API's does Virtuoso support?

ODBC, JDBC, .net data provider, OLE/DB. The Java and .net clients are pure Java and .Net respectively. ODBC is Virtuoso's native low level CLI. What level of SQL is supported?

Virtuoso is fully SQL-92 compliant and supports several of the SQL 200n features, including: User Defined Data Types with methods, inheritance etc. User defined aggregates, including 20+ built-in statistical operations. SQLX for generating XML results in queries, including a native XML data type. Cubes and Rollup in GROUP BY.

What is the SQL security model supported by Virtuoso?

Virtuoso supports standard SQL role-based security and full table and column level granting of privileges. Additionally, Virtuoso offers row level security through a system of policy functions. Event hooks can be defined for performing extra validation at login time, such as consulting an LDAP directory for the user's credentials.

What is Virtuoso's transaction support?

Virtuoso's Transaction Manager component ensures that transactions are Atomic, Consistent, Isolated and Durable (ACID). The Transaction Manager ensures VDB Engines are capable of supporting Online Transaction Processing (OLTP) and Distributed Transaction oriented applications and services. Virtuoso?s Transaction Manager component ensures that transactions are Atomic, Consistent, Isolated and Durable (ACID). Virtuoso provides the 4 standard levels of isolation, dirty read, read committed, repeatable read and serializable. Repeatable read is the default setting. See Virtual Database questions for a discussion of distributed transactions.

What is Virtuoso's distributed transaction support?

Virtuoso preserve transaction atomicity, consistency, integrity, and durability across it own database servers as well as heterogeneous servers through the support of transaction commits and rollbacks using a 2-phase commit protocol.

What SQL optimization does Virtuoso perform?

Virtuoso has a cost-based SQL optimizer. It uses table row count, data size and value distribution statistics for evaluating the cost of diverse execution plans. For each plan, loop invariants are extracted, loop and hash join types are evaluated, different indices are compared for access performance and predicates are evaluated as early as possible, most restrictive first, for any join order being contemplated. Additionally, the programmer can explicitly specify the join order and join type (loop/hash) for each table. How does one administrate a Virtuoso database?

Virtuoso database can be administered through the Virtuoso Admin Interface or through the interactive SQL Tool ISQL.

How does Virtuoso handle backups?

Backups can be handled in a variety of ways. Virtuoso has both off-line and on-line incremental backups. Virtuoso's transaction mechanism is based on keeping a read-only checkpoint plus a transaction log for any committed but non-checkpointed transactions. Virtuoso has an incremental, on-line backup function, which can backup a running database in its checkpoint state without restricting concurrent update activity. Virtuoso records at each checkpoint the pages, which were changed since the last backup. Hence the next backup will only cover pages , which are changed in the checkpoint state since the last backup. Each checkpoint can optionally start a new transaction log, leaving a full audit trail of transaction logs. Having a series of full plus optionally incremental backups up to a certain checkpoint and then the transaction logs consecutive to the last backed up checkpoint will guarantee a full recovery up to the latest transaction committed at the time of failure. The backup function automatically compresses the backup and chunks it into fixed size chunks for convenience in handling large databases.

On the other hand, as long as no checkpoint intervenes, it is safe to copy the database file(s) plus the log for an up-to-data clean image of the database.

What is Virtuoso's support of stored procedures?

Virtuoso has a very extensive procedure language called Virtuoso PL. . The syntax of the Virtuoso PL resembles C with in-line SQL 99 and PSM 96 features such as exception handlers. Stored procedures provide a significant performance increase over client applications on the same machine for any application involving a number of short SQL operations, as in the case of OLTP .

Can one extend Virtuoso's SQL?

Virtuoso can extend SQL by import Java and .net classes and functions as well as build-in SQL functions written in C. In addition , you can persist instances of imported classes into Virtuoso tables. For all intents and purposes, a hosted class instance is indistinguishable from a native SQL user defined type instance.

Is there full text indexing support?

Virtuoso supports full text indexing which provides the ability to choose complex, multi-part document id's for application specific sorting of hits, efficient storage of secondary, non-free text data in the free text index for best retrieval performance, options for restarting searches at a specific hit as well as ascending and descending orders of the document id's. What is the internationalization support of Virtuoso?

Virtuoso supports Unicode (NCHAR/LONG NVARCHAR) columns as well as supporting a national character set, which define how strings will get converted from narrow to wide characters. There is a number of pre-defined character sets included in a system table called SYS CHARSETS. This list can be updated by defining new characters using a build-in function called charset_define().

What encoding formats are supported?

Virtuoso supports Unicode, ASCII and UTF. What development environment/debugging/profiling facilities are there for SQL and stored procedures?

Virtuoso comes with a library of online tutorials which demonstrate all salient aspects of software development in Virtuoso/PL. Examples include XML processing, full text features, dynamic web pages, hosting Java and .Net and various scripting languages etc. Virtuoso has a rich set of debugging and profiling tools. The interactive SQL utility has a debug mode where it can set breakpoints and single step stored procedures with functionality similar to what gdb or dbx offer for C.

On Windows, there is a MS Visual Studio extension package which allows defining Virtuoso SQL projects, provides IntelliSense?? syntax highlighting and completion and many other features. The Virtuoso .net data provider has design time interfaces to Visual Studio for drag and drop use in C# projects.

For performance profiling, Virtuoso offers a call graph profiler and test coverage utility. These show elapsed times and line-by-line execution counts for stored procedure code.

For regular database statistics, Virtuoso provides index by index cache hit rates, wait times, deadlock counts and other information for assisting database tuning.

Does Virtuoso support stored procedures across different database engines?

Yes, Virtuoso enables you to write stored procedures that reference tables hosted in different database engines. This has two major benefits: You can store more of your database centric application logic within virtuoso and then leverage the performance advantages that stored procedures have over dynamic SQL. Your stored procedures no longer have to be database specific, a major reason why stored procedures are not used in when writing database independent applications.

Does Virtuoso support VIEWS that include tables hosted different database environments?

Yes, you can create logical VIEWS that include joins across tables within different database engines and data sources (XML and Web Services).

What tools does Virtuoso support for analyzing data?

Virtuoso supports the basic SQL OLAP extensions for GROUP BY, i.e. CUBE, ROLLUP, GROUPING etc. Virtuoso does not offer specialized storage for OLAP cubes.

What about SQL 200n Object Oriented features?

Virtuoso SQL supports SQL 2000 style objects as standalone data in procedures and as column data types. The Virtuoso Object System supports single inheritance, late binding, polymorphism and persistence of objects as column values in SQL tables. An object's implementation may be native SQL, with methods in/PL, or the objects may be implemented in Java, or any Microsoft .NET or Mono ECMA CLI bound language. Hosted and native objects are indistinguishable from the perspective of a Virtuoso SQL application. The native methods and data members are automatically made accessible from SQL when the class or class hierarchy is imported into the SQL schema (data dictionary). Virtuoso supports single inheritance between tables through the SQL 200n UNDER clause. A subtable will inherit the supertable's columns, primary key and indices and can for itself add new columns and indices. Selects on supertables will also include rows that belong to subtables.

XML Database

How does Virtuoso store XML?

Virtuoso offers the XMLType datatype. This data type can be made from a text representation and can again be converted into text. XPATH, XSLT, Xquery and SQLX operations can be applied to this type. This type can be stored as either text or in a pre-parsed binary format. XMLType defined as a column value, a XML tree can be specifically full text indexed so that XML elements are taken into account. This indexing allows, besides normal text index operations, also resolving occurrence of elements, inclusion of text inside elements etc. How does Virtuoso generate XML from relational data?

Virtuoso has several mechanisms for transforming SQL data into XML. One option is the ?FOR XML? clause, which is a SQL option that can convert the output of a SELECT statement into a tree. SQLX is the standard SQL way of creating XML content from relational data. Special aggregates and functions allow composing trees in queries Mapping Schemas - A mapping schema is an XML schema, which defines how the data is to be extracted from, joins of relational tables. When an XPATH query is evaluated against a mapping schema, Virtuoso generates an SQL query, which will retrieve only the required rows and then generate the XML tree.

Can Virtuoso present legacy RDBMS's as XML stores?

The SQL based XML generation features do not differentiate between local and remote tables, hence any relational data will be accessible via these means as XML.

How does one combine native XML and relational data in queries?

Virtuoso SQL offers special predicates for testing stored XML against XPATH and text conditions as well as extracting fragments from stored XML trees. New XML can be made from these with the SQLX functions or XSLT style sheets, for example. A result column of a query may fully well consist of XML data. Inside a stored procedure, further XML operations can be applied to the data, in the case of a client the XML is seen as text via the client API. Another approach is to write queries in XQuery and access SQL data from there. Any XML mapping schema appears as a document accessible with the XMLView XPATH extension function. Joins of tables can thus be accessed and filtered as if they were XML data to begin with. Note that this does not entail constructing XML for data, which is actually not needed by the Xquery statement.

How do you make and transform XML inside queries and SQL procedures?

XSLT and XQuery are most convenient for extensive processing of XML. For quickly generating simple XML trees from SQL data, SQLX functions are most convenient. For complex mapping of relational and XML data, mapping schemas are most convenient . For a one-to-one mapping between a foreign key relation in SQL and tree hierarchy in XML, the FOR XML SQL extension is most convenient.

What is the XPATH, XSLT, XQuery standard compliance?

Virtuoso currently supports XPATH 2.0, XSLT 1.1, and XQuery 1.0. What are the searching functions available: Standard XML languages (XQuery, XPath or/and proprietary languages)? XPath, Xquery, Freetext queries, and, or not proximity and wild cards. A free text expression may be embedded inside an XPath predicate using a special XPath expression.

What about SQLX standard support?

Virtuoso supports offers full support of SQLX which SQL with a collection of functions added for creating XML entities from standard relational queries. The SQL/XML is an emerging standard driven by the H2.3 Task Group (Formerly the SQLX Group). Current support for these functions include XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLCONCAT, and XMLAGG when combined with SQL result in XML that is returned in a column in a result set. How can one combine full text and XML structure based queries in document centric applications?

This is possible in either SQL or XQuery. In SQL, one can for example join a table of XML documents with author information by writing a SQL join between a publications table and an author?s table. The author reference would just be extracted with XPATH after application of a free text based content filtering. In XQuery, the documents table and authors tables would be represented as documents via mapping schemas and the one could write flwr statements for joining the two.