DB Meta Definitions

GEODI, by default, indexes all or selected tables in a database with some default behavior. DBMetas are the optional definitions to change this behavior.

To index embedded or linked files, to index SQL, to change to indexed rows will be viewed, you need to define a DBmeta.

DBmeta files are *.jsettings and should be in “Settings\Reader\DBMeta"

Limiting the Settings Scope

You may limit the settings by the following criteria. This way, you may have multiple meta-definitions that each is effective for a limited scope.

Key	Description

Key	Description
WorkspaceName	WorkSpace(s) to which the settings will apply.
TableFilter	Tables to which the settings will apply.
ColumnFilter	Tables that contain the given columns (if - is given, tables that do not contain the relevant columns are considered). If more than one is given, the relationship is AND.
Columns	Columns to be Indexed or Excluded

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBView, Geodi.Database",
         "WorkspaceName":"0000-Promotional and Educational Videos",
         "TableFilter":"*",
         "ColumnFilter":"*",         
         "Columns":"-FILEPATH,-ID,DATE,GEODIFILELINK"
      },
   ]
}

Determining Row names

Each row is a document/content. GEODI behaves rows just like other documents. By default first text column is the name. The name is the content used by doc:(…) query.

You can change row names for each table using DisplayNameMacro. You can also use other columns in the macro. Macro rules are given at the end of the page.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowDisplayName, Geodi.Database",
         "DisplayNameMacro":"[TEXT4]/[TEXT2]-[TEXT3]"
      }
   ]
}

Indexing files embedded in tables

Geodi can scan files with file paths in the database or are embedded as blobs. This document explains how to make the necessary settings.

File1 column shows file names File2 column shows blob files. In the created database, the BLOB file column is selected as "bytea" data type.
You can use it in Access,Postgres,Mssql,Oracle,Sqlıte,MySql databases.
FileMemoColumn in VT must have a file extension in the given column.
- If there is no extension in the column value and all files are of the same type, an extension can be added to the macro value.

Key	Description

Key	Description
`IDColumnMacro`	Macro for the unique number of the file. The rules for macros are at the end of the document.
`FileNameColumnMacro`	Macro for the name of the file to appear in searches and viewers. Rules for macros are at the end of the document.
`FileMemoColumn`	BLOB/MEMO field containing the file content. GEODI will automatically determine the file content

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
         "TableFilter":"TEST",
         "IDColumnMacro":"[OBJECTID]",
         "FileNameColumnMacro":"[FILE1]",
         "FileMemoColumn":"file2"
      }
   ]
}

Indexing Multiple Embedded Files in a Table

You can use the following meta to index files embedded in multiple columns in the same table.

Indexing an Embedded File Without a Filename in a Table

When there is no file name in the table in the database, you can build one using the FileNameColumnMacro value. There are two examples below. The first example is a simple one. the second one assumes that some rows do not have an extension value. FileMemoColumn is the file stream.
- In the example, the Extension column is assumed to have the file extension. Some rows may have empty values. We assumed that the file is PDF if no extension. The macro might have returned an empty string to skip the files.

Indexing with File Paths

You can also index files specified by file links in a record. (CSV files are not supported).

Key	Description

Key	Description
`FileFullPathColumnMacro`	A macro that calculates the full paths for the files. This files becomes child of of the rows.
`FileFullPathColumnSplitter`	If there is more than one file in the file path, this is the splitter character.

Specifying a Primary Key Column

Each table requires a primary column for scanning and versioning changes. GEODI sets the ObjectID value by default. You can specify a different column with the definition described here.

The "KeyColumns" value will be the unique ID value.
The primary column name must be written in the meta the same as the name in the database.

Indexing the result of an SQL statement

You create a new table named NewName with the SQL key. The SQL statement must be compatible with the relevant DBMS.

Alternatively, you can specify the value of KeyColums in SQL by writing "as P_KEY" or "as OBJECTID" in the column.
This feature is valid for file-based ones such as *.xDeceConnection and MDB.
Standard SQL must be used.
It should be ensured that there is no more than one repeating column with the same name in the result of the SQL statement.

Row Based Authorization

You can authorize based on Table, SQL or View row.
You can use user and/or GEODI groups in "PermitMacro" & "DenyMacro" for authorization.
- You can also use [geodi:username] for the users you create.
- If you want to write more than one user, group, you should start with = and use advanced macro. Simple macro definition can only be used to define a single group/user.
Usernames or groups must be generated from a column in a table (or SQL, or View).
Rows will be authorized in the table, and these authorizations will be used in files (child content).

The generated group name is case sensitive.

Example 1 : SQL Query Result Authorization

Example 2 : Created Group Based Authorization

Example 3 : Advanced Macro Examples

Example 4 :

Example 5 :

Example 6 :

Specifying the Text/Text Result of a Record

This feature is used to change the GEODI search index. When the nomenclature given in the context is searched through GEODI, it finds all the contents in the table. With Content, we can select the search word of the column of a record, we can turn words without a column name into a search word with this feature. You can write and name more than one [Column Name] in Content. It allows to turn off and on the setting made in the Ignore state in DBmeta.

Translated with DeepL

If “Ignore”:”False”, the settings written in Content are valid.
If “Ignore”:”True” , the settings written in Content are not valid.
This feature is valid for file based ones like *.xDeceConnection and MDB.

Adjusting the Way Records Look

The HTML template you provide with the "TemplateName" key will determine the appearance of the records that meet the criteria. This template with the ".html" extension should be under the Templates folder under the "DBMeta" file, and the generated meta should be saved under the "DBMeta" folder.

Using TemplateName gives you visual flexibility but can be a performance penalty.

FieldIndex Settings (Limiting Searches by Column Name)

The default search covers all tables and all columns. To limit the search result based on columns, you should set FieldIndex. After setting "columnname:<your search phrase>", the criteria will be limited to the related column.

You should set ContentReaderEnumerators → Your Database → EnableAutoFieldIndex to true in the project detail settings and rescan your project. Rescanning can take time with big data, so planning it from the beginning is better.

When this feature is active, each discovery for a DB will have the column name with it.

Sampled Data Discovery in Databases

By default, GEODI discovers all database contents. You can optionally sample and explore your database contents, so you can save on scanning time and storage space. GEODI samples random N records per table.

The sampling settings will be effective for File-based databases if this setting is used in folder sources. such as *.xlsx, *.mdb, *.accdb.

Make column names multilingual and define aliases

If you want, you should make the following definitions.

"fields" must be included in resx file names. These files must be located in the globalization directory.
The name value used must be att{fieldname} and must match the one in the table. You must use the same key in different languages.
The value contains alternative column names (aliases). You can separate multiple alternatives with “|”.
If any of the aliases you use match another column name, they will be ignored.
In multilingual representations, the first alias is taken into account.

myfields.resx

myfields.en-us.resx

Macros

You can write macros for values such as Document ID, File path. Macros allow you to solve situations where column values are directly insufficient. For which values macros can be used is specified in the related sections.

Macro text is a text where columns are given between "[]". As shown in the example, you can give the same column more than once.

In macros, some characters must be used with the escape character ("\"). For example "\" should be given as "\\".

"FileFullPathColumnMacro":"C:\TEST\KUR-1166 VT\files\[FILE1]"

"DisplayNameMacro":“[BLOCKNO] Ada [PLOTNO] Plot”

"DisplayNameMacro":“[TITLE]-[POSITION]”

If the value of settings whose type is macro starts with =, it switches c# to macro usage. This brings unlimited flexibility

Examples

"FileFullPathColumnMacro":'=Path.Combine(@"C:\\TEST\\KUR-1166 VT\\files\\",d["FILE1"])'

"DisplayNameMacro":'=string.Concat(d["BLOCKNO"]," Block ",d["PLOTNO"]," Plot")'

"DisplayNameMacro":'=string.Concat(d["TITLE"],"-",d["POSITION"])'

"DisplayNameMacro":'=d.Get<int>("DEGER")>-1?"Pozitif":"Negatif"'

Detail Information

Jsettings files should be syntax validated, you can use https://jsonformatter.curiousconcept.com/ site for this validation.
We should check the correctness of the column, table and GEODI project name and their names on the Table/Column/Project database.
Scan error logs should be examined and analyzed. You can examine the errors received in the scan with the developer report via GEODI.