Enterprise Data Management
Virtual Database Engine
What does Virtuoso mean by a 'Virtual database'?
Virtual database in the simplest form provides the ability to search across several databases with a single query. Virtuoso extends this query functionality and data transformation through hosted logic, SQL, SQL stored procedures and XML, and the Virtuoso PL.
What types of databases does Virtuoso interface with?
Virtuoso supports a number of databases platforms and can simultaneously connect your ODBC, JDBC, UDBC, OLE-DB client applications and services to data. These databases include Oracle, Microsoft SQL Server, DB/2, Informix, Progress, Ingres and as sell as other ODBC compliant database engines.
How does Virtuoso deal with distributed transaction issues?
Under Windows, Virtuoso can act as an MS DTC (Distributed Transaction Coordinator) resource manager and client. This means that for a client aware of distributed transactions, Virtuoso can be enlisted as a participant in a 2PC transaction via ODBC, OLE/DB and .net client interfaces. If Virtuoso acts as a participant in a distributed transaction and involves data from remote databases, it will propagate the enlistment if the remote database in question supports this. A virtuoso stored procedure can also initiate a distributed transaction into which it then enlists multiple other resource managers as the need may arise. The enlistment is transparently handled by the VDB logic once the Virtuoso transaction has been declared as distributed.
Any transactions involving updates to exactly one remote database plus Virtuoso can be handled as single phase commit without danger of corruption.
Under non-Windows platforms, Virtuoso can act as an XA compatible resource manager. In these environments Virtuoso cannot at present enlist remote databases but can be enlisted itself. The most convenient client API for this is JDBC with its distributed transaction support.
How does Virtuoso deal with differences in SQL dialects?
At the time of defining a remote data source, Virtuoso makes queries concerning its capabilities. It will use these results in generating SQL statements to pass to this data source. For example, if a remote database does not support a specific SQL function or construct, e.g. outer join, it will not present it with SQL statements containing these and will perform the unsupported operation locally instead. This is entirely transparent to the Virtuoso client.
How do you set up a virtual database?
Select a data source from the available ODBC data sources on your system, view the tables, views and stored procedures available on each, select which ones you want to access via Virtuoso and you are set. From now on, you may manipulate these remote objects from Virtuoso SQL and applications. A web based UI takes you through this process in a few clicks.
What are the security implications of a virtual database with Virtuoso?
The Security Manager inside the Virtual Database Engine is responsible for protecting data and data transmission through encryption. The security manager is also responsible for managing Application, User, Group, Role and Domain privileges as they relate to the creation, manipulation and destruction of Virtual Database data and metadata.
Can I join data across different database engines?
Certainly, Virtuoso provides the end-user or application developer with the ability to make single concurrent connections to multiple back end databases and data sources (XML and XML Web Services). It is important to note that Virtuoso's data access interfaces are all industry standards compliant (XML, Web Services, ODBC, JDBC, NET, and OLE DB) thereby broadening the number of applications, tools, and application development environments capable of exploiting this Virtual Database functionality.
A simple heterogeneous join scenario could involve joining the "Customers" table in an Oracle database with the "Orders" tables in a Microsoft SQL Server database, and then making this available for ODBC and/or XML application consumption.
A more complex scenario could involve joining data across Oracle, SQL Server, and an XML URI (such as an RDF or RSS data feed from a web log, content syndicate, or web service), and then making this available to ODBC, XML, Web Services, JDBC, OLE DB and NET client applications.
SQL Database Engine Functionality
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.
Object-Relational Functionality
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.
What does Virtuoso offer in terms of User Defined Types or other datatype extensions?
Virtuoso supports SQL 2K User Defined Types (UDTS) and User defined functions (UDFS) which can be defined in SQL, Java and .Net bound languages. Virtuoso also offers Procedure Views which are similar to table-valued functions that allows you to define a view as a stored procedure.
How does 3rd Party Runtime Hosting modules execute?
Runtime Hosting Languages such as PERL, Python, Ruby loaded in process. These are callable via special
functions from SQL. Web pages in these languages can be hosted without any special programming.
What types of security or privileges are provided for 3rd Party Runtime Hosting?
Privileges are roles based at the table, column, procedure and type levels. Row Level security is based on table by table policy functions.
Does Virtuoso Object-Relational database support arbitrary length types?
Virtuoso supports all combinations of narrow, wide and binary strings.
What does Virtuoso offer in terms of Complex Objects like the creation of Composite Types?
Virtuoso allows you to create UDTS which can reference any other UDTs. You can also declare columns to contain sort and long UDT instances regardless of implementation be it SQL, Java or .Net.
How are Composite Types handled and called?
User Defied Functions arguments and return values are passed by value and by reference. OID referenced objects have no persistent identity. Virtuoso functions and composite types support dot operators for data members and member functions like other formal languages. In addition, since the objects are types at runtime arrays and other complex data structures can also contain UDT instances as well as another other data.
What about Data Inheritance?
Single inheritance for tables is provided via the under table and under for UDT functionality. Function overloading is provided via dynamic binding of methods based on runtime type of self when multiple eligible methods, discrimination on compile time types of arguments.
Does Virtuoso Object-relational Database Support Triggers?
Virtuoso supports SQL 99 row level triggers that can call outside code for signaling to clients although no special client notification mechanism is supported by the Virtuoso client server protocol.
XML Data Management
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.
RDF Data Management
How do you load RDF data into Virtuoso RDF Triple Store?
Turtle and RDF/ XML files can be loaded into a specified graph using SQL API functions. Single triples may be added to graphs using SQL API functions. Metadata from resources loaded into Virtuoso DAV will automatically go to specific RDF graphs if metadata extraction is enabled. The DAV to RDF store integration is in flux.
How do you query and perform searches with Virtuoso RDF Triple Store?
SPARQL statements can be written inside SQL statements or presented as top level SQL queries. This means that any ODBC, JDBC, .net or OLE/DB application can simply make SPARQL queries just as if they were SQL queries.
Virtuoso further supports the SPARQL protocol, allowing SPARQL queries to be executed over HTTP.
What RDF Datatype's are support in Virtuoso RDF Triple Store?
If an RDF type corresponds to a SQL data type, the data is stored as a native instance of the SQL type in question. For strings with language tags and other RDF data that has no direct SQL counterpart, a special representation preserving the RDF semantic is used.
How many graph models can Virtuoso RDF Triple Store support?
Unlimited.
What Data Access methods are supported ?
SPARQL protocol over HTTP, any SQL client library can be used for issuing SPARQL queries just as well as SQL queries.