1. Licensing

1.1 What is the Product Key?

The Product Key – which appears above the Licence Key field in the About popup – is a generated string that should uniquely identify your computer for our Xtractor applications. It is just internally and transparently used by us to validate the Licence Key and its right to be used on that computer.

1.2 How complex is it to activate an application?

Any Xtractor application may run freely, at full functionality, since the first time you start it on one computer, for a trial period. After which you’ll still enjoy its full functionality, but – if you use it often – you’ll see some query executions are slowed down on purpose with random delays.

If you decide to buy a license, just fill in the order online, pay with your credit card or PayPal account, and you’ll instantly receive an email with the license key(s) you paid for. Open now the About popup and paste this value into the License Key field, then click Activate. If you are connected to the Internet and there is no connectivity problem, and if your license key and licence key usage are valid, you’ll get a message about the valid license key and …that’s it! You may continue to use the application at its full capacity.

1.3 Do I loose the trial period when I enter a license key?

Since the first time you start one Xtractor application on one computer, you automatically start a free trial period. If during this time you decide to purchase the product and enter a valid license key, the trial period is not cancelled. If you later decide to remove/deactivate the key, and you are still within the trial period, this will continue until the date it was initially supposed to expire.

1.4 May I have more than one Xtractor application running in the same time?

No, not on the same computer. If you already launched let’s say Model Xtractor, you cannot launch a second Model Xtractor application.

What’s more, you cannot launch an instance of Query Xtractor or Data Xtractor, if you have them installed. This is why we strongly recommend to purchase a license of Data Xtractor, instead of paying just for Query Xtractor and getting Model Xtractor for free.

1.5 On how many computers may I install a single paid license?

On one single computer at a time. If you already have installed our product, with a license key, on a computer, and you install it also on another computer, you must deactivate the license key from the first computer before activating it on the second computer. If at some point you decide to move back to the first computer, deactivate it from your second computer and activate it again on your first computer.

1.6 Anything gets lost after the trial period, if I don’t buy the product?

No, you’ll loose nothing! All your imported database metadata, all your designed models and queries, continue to be fully accessible. What’s more, we don’t even deny you ANY functionality you had before. You continue to use the product with its full functionality even after the trial period expired, for as long as you want!

The only difference is we may introduce some random delays in the queries execution. If you use the product just once in a while, we don’t want you to pay anything for it. It’s only when you use it on a frequent basis that we beg you to consider acquiring a paid licence.

1.7 Buy Data Xtractor? Or just Query Xtractor?

Why should you pay for Data Xtractor, when you may get Model Xtractor for free, and pay less just for Query Xtractor?

Because you cannot possibly have both Model Xtractor and Query Xtractor applications running in the same time on the same computer. When you have one of them running and try to launch the other, you get an error message. And this could be annoying if you frequently switch between models and queries.

But if you already paid just for Query Xtractor and figure out now you would prefer both modules in the same application, just upgrade to Data Xtractor for a very small fee. Those who purchased from start Data Xtractor pay the same amount as those who purchased Query Xtractor and later upgraded to Data Xtractor.

1.8 Why delay query execution when trial ends?

Most vendors completely cut product’s functionality when trial period ends. But we still want you to use the product and figure out in time if it’s worth buying it.

If you are not a frequent user of our product, you’ll find this convenient. You will continue to have access to full functionality of Query Xtractor or Data Xtractor, but execution of most queries is randomly delayed.

1.9 Future plans?

Short glimpse of our future plans at this moment regarding implementation of new features and enhancements.

  • Surrogate primary keys from unique indexes. Detection of identity or sequence columns. Set custom primary and foreign keys.
  • Models with images.
  • Refresh metadata and keep in sync with the database.
  • Schema queries, from INFORMATION_SCHEMA or system catalogs.
  • Extend supported databases with Informix, HyperSQL, Sybase IQ, SAP HANA, InterBase, etc. NoSql support: MongoDB SQL converter. XML/JSON parsers.
  • Improved logging.
  • SSH connections.
  • Import Synonyms, from Oracle or SQL Server.
  • .NET API for automation.
  • Online models.
  • Table Inheritance.
  • Find Natural Joins.
  • Case Sensitive and Case Insensitive queries.
  • Hierarchical and expandable queries. Recursive WITH. Oracle’s CONNECT BY.
  • “Using” query relationships.
  • Asynchronous query processing.
  • Data viewer.
  • Undo/Redo buffers.
  • Quick sort on results.
  • Regex search.
  • Cross database joins.
  • Improved parameterized queries.
  • Time-based queries. Geometry queries.
  • DateTime picker. Improved datetime support.
  • Full PostgreSQL arrays support.
  • Query scheduler.
  • View decompiler.
  • Query execution plans. Optimizers.
  • Automatic type conversions.
  • Boolean data type.
  • FULL JOIN emulator.

2. Configuration

2.1 Is there a way to import connection strings from a text file?

File-Batch Connection Loader opens a custom ConnectionStrings.txt user file from your AppData\Local\Xtractor folder.

If you frequently connect and disconnect from databases and you’re not concerned with exposing those connection strings in a plain text file, create such file and fill it with your own data. Each line must start with a {dbms_type}; prefix and a valid ASP.NET connection string to your database, followed by a TAB and an optional description of your entry. Prefix with the whole line if for some reason the database became unavailable.

2.2 Where can I find the password I entered when I connected to a database?

Nowhere. This is actually an important security measure we took: we never show in clear the password someone entered to connect to a database. In connection strings, the password always appears hidden, as ********.

If the connectivity has been established on your behalf by a database administrator, make sure the Save Password field is checked. This way, your connection parameters are persisted on our side, but you never need to know or enter the password.

Uncheck Save Password when you rather know the credentials and want to protect the database access better. This way, each time you launch the application, you are required again to enter the password.

2.3 Where is my user data collected?

By default, all your user data is collected into the SQLite file Xtractor.db, located under your AppData\Local\Xtractor user folder. If you remove an application and reinstall it, the user database is not removed and you may automatically reuse it again.

All our Xtractor applications will use and share by default this system database. If you use two different Xtractor applications – such as Model Xtractor and Data Xtractor – they will both use and share the same data. After you create some new models in Model Xtractor, the next time you launch Data Xtractor you’ll see those models automatically appear there as well.

2.4 Do I need administrative privileges to install the app?

It depends where you install it. If you install it in its default Program Files folder, all applications installed there require administrative privileges on setup. If you install it in a less restrictive folder – such as your AppData\Local folder, you may not have any restrictions at all. It is still possible some related database drivers may require admin privileges, because they are registered in the Registry.

2.5 Why import on some databases may fail with “Importing tables failed”?

There is a collation database settings we recently became aware of and it could be the origin of some failed imports. It will be fixed asap, stay tuned.

3. Data Model

3.1 When to use minified shapes?

Use minified table, view or query shapes when you want to see the connected path between two (expanded) shapes and you’re not concerned through which other shapes it passes through. Unlike the collapsed shapes, minified shapes take less space and completely hide the underlying object name.

One typical example is when you have a model with many irrelevant intersection tables, with just the foreign keys of the related tables propagated as primary keys. In most cases, intersection tables are just a technical implementation detail of the relational many-to-many relationship.

Minified Shapes

For instance, take the many-to-many relationship between Playlist and Track in the picture above, where PlaylistTrack is their intersection table. Most data modeling tools will show you only the first image, with all shapes expanded. But if you want to focus just on the main Playlist and Track tables and their n-n relationship, you can either collapse or minify the PlaylistTrack shape. Or remove it completely from the diagram and rather expand the Xtractor-specific derived chain relationship between the main tables.

3.2 Can I create new database tables and views with Model Xtractor?

No. None of the Xtractor applications can modify remote database data or metadata! We did this on purpose, to offer your database administrators a safe and secure interface through which users can query data and metadata, but never alter them.

What you can do is expand your database models with artifacts and extensions transparently saved in your local user database. You can create custom joins and aliases, chain relationships, hide table columns etc. None of these transformations will alter anything in the database itself. They will all come on top of the remote database model, as safe client side extensions.

3.3 How can I create a custom join?

In a model or query diagram, you start by dragging and dropping a table column over another table column. If a custom join is possible, a popup will open to help you eventually add more then one pair of columns for this join.

The join should make sense. For instance, you cannot pair columns with fundamentally different data types, such as one integer and the other one binary. You can however pair a tiny int column with a long int column. Examples of typical custom joins include on-the-fly relationships on First Name and Last Name columns. Unlike the database built-in foreign key joins, you can also remove the custom joins.

3.4 How can I print a diagram?

There is no Print command, but you have several options:

  • Save it as a PDF file (with File – Save PDF). Then use a free reader like Acrobat Reader or Sumatra PDF, that have full Print and Print Preview support. You may send it to a printer, fax it, email it etc. PDF page format and margins can be adjusted from the toolbar at the bottom of your screen. Large diagrams are cut into separate pages.
  • If you want just one large diagram, with no pages, to send to a plotter, save it rather as an image (with File – Save Image) and use one of the many free image tools to print it as it is.
  • You can also copy it in the clipboard (with Edit – Copy) and paste it into another document, such as Word, then use that tool’s print support.

4. Query Design

4.1 What is the purpose of the Selection check boxes in a query design grid?

When checked, the expression you have in that column will also appear in the top list of a SELECT SQL query. When unchecked, it doesn’t. But you may use that expression as filter, in a WHERE or HAVING clause, or for the ORDER BY.

Hide the whole Selection row if you want all column expressions to appear in the SELECT SQL query.

4.2 Can I create SQL queries that alter data with Query Xtractor?

No. None of the Xtractor applications can modify remote database data or metadata! We did this on purpose, to offer your database administrators a safe and secure interface through which users can query data and metadata, but never alter them.

SELECT is the only kind of SQL query automatically generated by our designer. That’s actually a main strength of our application, that should give most database administrators the peace of mind, when they allow novice users connecting directly to a database.

4.3 How can I add DISTINCT to a query?

Simply click on the Show Distinct small toggle toolbar button while in design mode of a query. Generated query will switch between SELECT and SELECT DISTINCT.

4.4 Why Rows is sometimes zero for tables with data?

After you connect to Postgres or another database type, you may see Rows 0 in the Objects tab, for tables which are not actually empty.

This is because both Rows and Size columns from the Objects browser are initially just estimates collected from your database on the first import. Some platforms give us the exact row count, others (like PostgreSQL) provide an just approximate number. For 1-2 rows in the table, collected Rows could be 0.

Rows value is never automatically updated when rows are added or removed. But, whenever you need a refresh, you can select one or more tables, and execute the contextual Update Row Count command, as in the image below. A SELECT COUNT(*) will be executed right away for each selected table, and Row value updated. Be careful with this command, because the query may be slow on very big tables.

Row Count

4.5 Is there a way to turn off the drill-down feature?

Not at this point, but we may add a switch option in a future version.

Drill-down is automatically determined by the PK-FK custom or built-in relationships. One way to avoid it is to exclude participating keys from the current query. For instance, assume you have an Order with a ProductID foreign key. If you exclude the ProductID from your query, there will be no drill-down to the related Product when you run it.

4.6 What are the unsupported SQL features?

Here is a list of SQL features that our models and query builder do not directly supports at this time, that we will unlikely support in the future and the reasons why. We fully encourage your feedback! Give us some constructive reasons why we should reconsider.

  • BETWEEN … AND … – You can always take the >= … AND < = … approach. Between requires two parameters and it’s harder to accommodate this Boolean predicate in our builder.
  • [NOT] EXISTS – We may generate some inline queries with EXISTS, but we found no use case where EXISTS couldn’t be successfully expressed with rather a different operator.
  • INNER, FULL and LEFT/RIGHT JOIN – We always translate our inclusive and exclusive joins into one of these SQL joins. What users may find confusing is the notion of LEFT and RIGHT in the visual builder. What if you have both shapes one below the other? Which is right and which is left?
  • GROUPING_ID – We support GROUPING and we use it to generate Grouping Flag and Grouping Fields, which both return better information than GROUPING_ID, are automatic and do not require manual handling of group columns.

4.7 Why BETWEEN should be avoided as SQL operator?

We did not find an east way to design it as an intuitive operation in our query builder. But there is always the more reliable syntax a >= 1 and a <= 10, for a BETWEEN 1 AND 10.

According to SQL All-in-One For Dummies (2011):

The BETWEEN keyword may be confusing because it doesn’t tell you explicitly whether the clause includes the end points.

Is 10 BETWEEN 10 AND 100 true? It is. Is 10 NOT BETWEEN 10 AND 100 true? It is not. The clause include the end points, but …still unclear.

If you use BETWEEN, you must be able to guarantee that the first term in your comparison is always equal to or less than the second term.

And this is an “element of surprise” we try to avoid. People may expect 20 BETWEEN 100 and 10 to be a legal statement and return true, but in fact Microsoft Access was our only supported database to return true. All other databases returned false and did not complain at all that this was not a valid statement. If we disallow this in our application, we;ll have to explain why.

4.8 How to sort by an expression that’s not in SELECT?

Assume you have a date of birth and you want to order by the month of birth. However, you do not want the month of birth to appear in the SELECT list.

Sort does not refer to the single column you may check for the top Field, but to the whole eventual expression you may add with Formula, based on your top field.

You may hide an expression from the SELECT list with an uncheck in Selection. That’s the typical way to create a “hidden” expression for another clause, such as ORDER BY, GROUP BY, or as a query expression reused by some other SELECT list item.

For this date of birth example:

    1. Select date of birth in your Field.
    2. Show Formula and add ToMonths function.
    3. Show Sort and select the order by type.
    4. Show Select and uncheck selection of this whole expression.

In the generated SQL, this whole expression will now appear only in the ORDER BY clause.

See also the Sort by Expression chapter in the Sorting Data article of the Knowledge Base.

4.9 How do you create a new folder for queries?

Select your queries, right click and choose Move to New Folder. A new custom subfolder is created, your queries are moved here and you can rename your folder now. Whenever you move the last child object out of a custom folder, the folder is automatically deleted.

Move to Folder

We call them custom folders to separate them from the special top folders under your database node: Tables, Views, Models, Queries, Bookmarks. You cannot move, delete or rename these special folders . Any object can be moved to another custom folder, but within the same branch: you cannot move a query under Models, table under Queries and so on. To move objects and folders under a different existing folder, select them, then drag and drop them on that folder.