Source:Databases

GEODI can also evaluate databases, i.e., structured data. Databases and their file information (including embedded) can be indexed (all tables and rows). With simple rules, you can limit tables, rows, or fields and set how rows are displayed to the user.

Database Clients

Some DBs may require a driver on the server where GEODI is located.

How to Connect Database Source

1. You can start by selecting the Database Source with the Project Wizard/Database.

   1. 

2. You must select the database you want to define with Define Connection from the screen that opens.

   1. 

How to Define Tabular Data

There are multiple ways:

1. File based ones such as SQLite, MDB, AccDB are indexed automatically. Rule files/DBMeta are considered if defined.

2. Excel files are not recognized as structured data by default. But we can do this with appropriate meta and settings.

3. With Project Wizard/Database source we can connect to all listed DBMSs. Rule files/DBMeta are taken into account if defined. In this structure, desired tables and columns can be selected and SQL statements can be written. Views are supported.

4. Settings made with Project Wizard/Database can be stored. A structure in *.xDeceConnection format is now recognized as file based. This structure has multiple benefits.

   1. It is portable. This means you can use the same file in other GEODIs or the same GEODI in other projects.

   2. It is secure. Depending on the system settings security level settings, the password/username inside the connection cannot be known.

   3. Some DBMeta capabilities mentioned on this page only work this way.

How to Index?

Databases can consist of many tables. By default, GEODI traverses all tables and indexes all rows. System administrators can make definitions to restrict tables and rows through GEODI. If desired, "view(s)" can be defined and how GEODI will see the database can also be defined. Many different systems/formats such as Oracle, SQL Server, Postgres, Mysql, sqlite, access, shapefile, excel are supported.

There is no need to define for file based ones like sqlite and access. These files are automatically indexed when found in any source.

You can also store the connection definitions you create with GEODI as *.dececonnection and let GEODI scan this content like any other file.

IIndexed tables and rows are not taken into account in the crawl for changes process as long as they do not change. Changed rows will be automatically versioned. 

1. Tables to be crawled must have a primary key. You can remove this constraint with a setting, but the crawl changes or versioning features will not work for tables without a primary field.

2. . If you do not want Geodi Table objects in the search results, but only records, DisableIndexTables should be set to True in the Enumerator Detail settings.

3. Files embedded in tables or linked within tables can also be included in indexing. How these features work is explained on this page.

4. You overcome the limited search capabilities offered by the software that creates the database

Changing Table Indexing Settings

• You can change the default behavior with jsettings settings files under Settings\Reader\DBMeta. Within the same settings file you can apply different settings for different tables or projects as shown in the example.

Separate setting types require a separate __type key.

Example:

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
         "WorkspaceName":"0000-Promotional and Educational Videos",
         "FileFullPathColumnMacro":"[FILEPATH]"
      },
      {
         "__type":"Geodi.Database.Meta.DBView, Geodi.Database",
         "WorkspaceName":"0000-Promotional and Educational Videos",
         "Columns":"-FILEPATH,-SILICENECEK,-Hazırlayan,-TARIH,-GEODIFILELINK",
         "TableFilter":"*",
         "ColumnFilter":"*"
      },
      {
         "__type":"Geodi.Database.Meta.DBRowDisplayName, Geodi.Database",
         "WorkspaceName":"0000-Promotional and Educational Videos",
         "DisplayNameMacro":"[TITLE]"
      }
   ]
}

With the values in the table you can limit the scope of the settings. For all settings, you can specify all with "*", exclude with "-" and considere with ",".

How rows appear in GEODI

In databases, each record appears on a record-by-record basis. The default title is the first text column value. You can change this for each table individually using DisplayNameMacro. You can also use other columns in the title 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 that have file paths in the database or are embedded in the database 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.

{
   "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.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
         "TableFilter":"TQA",
         "IDColumnMacro":"[OBJECTID]_1",
         "FileNameColumnMacro":"[file1]",
         "FileMemoColumn":"file2"
      },
      {
         "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
         "TableFilter":"TQA",
         "IDColumnMacro":"[OBJECTID]_2",
         "FileNameColumnMacro":"[dosya1]",
         "FileMemoColumn":"dosya2"
      }
   ]
}

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.

  • {
       "Defines":[
          {
             "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
             "TableFilter":"CMS.FileContentCore",
             "IDColumnMacro":"[FieldId][VersionNumber][MinorVersionNumber]",
             "FileNameColumnMacro":"[Internalid][Extension]",
             "FileMemoColumn":"FileContent"
          }
       ]
    }

  • In the following 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.

    {
       "Defines":[
           {
             "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
             "TableFilter":"Documents",
             "IDColumnMacro":"[DocumentsID]",
             "FileNameColumnMacro":'=string.Concat(d["Internalid"],"-",d["CreateDate"],string.IsNullOrEmpty(d["Extension"])?".pdf":d["Extension"])',
             "FileMemoColumn":"File"
          }
    
       ]
    }

Indexing what is given with File Path in Table

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

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSubContent, Geodi.Database",
         "FileFullPathColumnMacro":"[PATH]",
         "FileFullPathColumnSplitter":"|"
      }
   ]
}

Specifying a Primary Key Column

A primary column is required for each table 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.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBPKey, Geodi.Database",
         "WorkspaceName":"BLOBDataset Deneme",
         "KeyColumns":"BelgeBelgeID"
      }
   ]
}

      {
         "__type":"Geodi.Database.Meta.DBPKey, Geodi.Database",
         "TableFilter":"tablo1",
         "KeyColumns":"id"
      },

Indexing the result of a 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.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSQL, Geodi.Database",
         "TableFilter":"ADA,IRTIFAK_HAKKI",
         "ColumnFilter":"ADA.ADA_NO,ADA.OBJECTID,IRTIFAK_HAKKI.OBJECTID,IRTIFAK_HAKKI.TABAKA",
         "NewName":"ADALAR2",
         "SQL":"SELECT * FROM ADA,IRTIFAK_HAKKI WHERE IRTIFAK_HAKKI.OBJECTID=ADA.OBJECTID",
         "KeyColumns":"ADA.OBJECTID"
      }
   ]
}

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBSQL, Geodi.Database",
         "TableFilter":"TEST,TEST1",
         "ColumnFilter":"TEST.TARIH,TEST.OBJECTID,TEST1.OBJECTID,TEST.TAMS1",
         "NewName":"DENEME12",
         "SQL":"SELECT TEST.* FROM TEST,TEST1 WHERE TEST.OBJECTID=TEST1.TAMS2",
         "KeyColumns":"OBJECTID"
      }
   ]
}

Line 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-separated.

Example 1 : SQL Query Result Authorization

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowDisplayName, Geodi.Database",
         "TableFilter":"",
         "DisplayNameMacro":"[TEXT4]/[TEXT2]-[TAMS1]"
      },
      {
         "__type":"Geodi.Database.Meta.DBSQL, Geodi.Database",
         "TableFilter":"TEST,TEST2",
         "ColumnFilter":"TEST.TARIH,TEST.OBJECTID,TEST2.OBJECTID,TEST.TAMS1",
         "NewName":"DENEME12",
         "SQL":"SELECT TEST.* FROM TEST,TEST2 WHERE TEST.OBJECTID=TEST2.TAMS2",
         "KeyColumns":"OBJECTID"
      },
      {
         "__type":"Geodi.Database.Meta.DBRowPermission, Geodi.Database",
         "TableFilter":"DENEME12",
         "PermitMacro":"DECE\\kullanıcıadı",
         "DenyMacro":"[geodi:kullanıcıadı]"
      }
   ]
}

Example 2 : Created Group Based Authorization

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"birimler",
         "PermitMacro":"[birimler]",
         "DenyMacro":""
      }
   ]
}

Example 3 : Advanced Macro Examples

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"birimler",
         "PermitMacro":'=d.Get<string>("birimler").Split(\',\')',
         "DenyMacro":""
      }
   ]
}

Example 4 :

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"birimler",
         "PermitMacro":'=new string[] {d.Get<string>("YETKILI_GRUP"),"S-1-5-21-128668610-1027347169-903626496-1222","geodi:guest"}',
         "DenyMacro":""
      }
   ]
}

Example 5 :

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"birimler",
	 "PermitMacro":'=new string[] {string.Concat("Grubum_",d["KOLON1"]),string.Concat("Grubum_",d["KOLON2"])}'
         "DenyMacro":""
      }
   ]
}

Example 6 :

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"BIRIMLER,BIRIMLER_TEST",
         "PermitMacro":'=new string[] {d.Get("BIRIMLER"),d.Get("BIRIMLER_TEST")}',
         "DenyMacro":""
      },
   ]
}
________________________________________________________________________________________
{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBRowPermission,Geodi.Database",
         "TableFilter":"test",
         "ColumnFilter":"BIRIMLER,BIRIMLER_TEST",
         "PermitMacro":'=new string[] {d["BIRIMLER"],d["BIRIMLER_TEST"]}',
         "DenyMacro":""
      }
   ]
}

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.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DBContent, Geodi.Database",
         "WorkspaceName":"otf_meta_testV1",
         "TableFilter":"TEST",
         "ColumnFilter":"TARIH",
         "Ignore":"False",
         "Content":"[TAMS2] ZAMAN [TEXT3]"
      }
   ]
}

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 ".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.

{
   "Defines":[
      {
         "__type":"Geodi.Database.Meta.DB_DLV_View, Geodi.Database",
         "TemplateName":"PortalAnkaraGeziveMesire.html"
      }
   ]
}

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 it is better to plan it from the beginning.  

  • If we are scanning the database connection to GEODI on a file basis, we cannot use this setting.

When this feature is active Words that are recognized in the database and fall in the KLV will be written next to the column in which they are found.

Sampling 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.

• Project detail settings ContentReaderEnumerators → Veritabanınız → GenericSettings to add "DB.SamplingMode":100 Tables/Selected Table/SQL query 100 records are randomly process.

  • File-Based databases for use in Folder enumerator GenericSettings to add "DB.SamplingMode":100 Such as *.xlsx , *.mdb, *.accdb from all types 100 records per table are random processed.

Make column names multilingual and define aliases

If you want, you should make the following definitions.

1. "fields" must be included in resx file names. These files must be located in the globalization directory.

2. 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.

3. The value value contains alternative column names (alias). You can separate multiple alternatives with "|".

4. If any of the alias you use is the same as another column name, it will be ignored.

5. In multilingual representations, the first alias is taken into account.

myfields.resx

  <data name="att_{fieldname}" xml:space="preserve">
    <value>{alias1}|{alias2}|{alias3}</value>
  </data>
  
  <data name="att_EnvanterNo" xml:space="preserve">
    <value>Envanter Numarası|Envanter N.|Envanter Sırası</value>
  </data>
  <data name="att_ADI" xml:space="preserve">
    <value>Ad|Adı Soyadı|AdSoyad</value>
  </data>

myfields.en-us.resx

  <data name="att_{fieldname}" xml:space="preserve">
    <value>{alias1}|{alias2}|{alias3}</value>
  </data>
  
  
  <data name="att_EnvanterNo" xml:space="preserve">
    <value>Inventory Number|Inventory N|Inventory Position</value>
  </data> 
  <data name="att_PersonName" xml:space="preserve">
    <value>Name|Pname|Person Name</value>
  </data> 

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":“[ADANO] Ada [PARSELNO] Parsel”

"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["ADANO"]," Ada ",d["PARSELNO"]," Parsel")'

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

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

Detail Information