Other scripting languages (GodotJS etc.)

[Benjamin-Dobell]

Thanks for building this! I'm a contributor of GodotJS and by default this works great with GodotJS. GodotJS automatically generates types for everything added to ClassDB, so there's nothing special that needs to be done to make it work.

Do you have a place where we could contribute examples in other scripting languages? I imagine it could get pretty out of hand in the repo if people start adding all sorts of esoteric languages 😅. If you were to enable Discussions for this repo that'd probably work well enough though.

In any case, here's the demo database.gd ported to TypeScript.

import {
  Node,
  SQLite,
  OS,
  DirAccess,
  Image,
  ImageTexture,
  RegEx,
  PackedByteArray,
  GArray,
  GDictionary,
  ResourceLoader,
  Callable, Signal, Texture2D,
} from 'godot';
import { ExportSignal } from 'godot.annotations';

type Creature = {
  id: number;
  health: number;
  name: string;
  experience: number;
}

type Company = {
  id: number;
  name: string;
  address: string;
  age: number;
  salary: number;
}

export default class Database extends Node {
  private db: SQLite | null = null;

  private readonly verbosityLevel: number = SQLite.VerbosityLevel.Verbose;

  private dbName = 'res://data/test';
  private packagedDbName = 'res://data_to_be_packaged';
  private persistentDbName = 'user://my_database';
  private jsonName = 'res://data/test_backup';

  private tableName = 'company';
  private otherTableName = 'expenses';
  private packagedTableName = 'creatures';

  private ids = GArray.create([1, 2, 3, 4, 5, 6, 7]);
  private names = GArray.create(['Paul', 'Allen', 'Teddy', 'Mark', 'Robert', 'Julia', 'Amanda']);
  private ages = GArray.create([32, 25, 23, 25, 30, 63, 13]);
  private addresses = GArray.create(['California', 'Texas', 'Baltimore', 'Richmond', 'Texas', 'Atlanta', 'New-York']);
  private salaries = GArray.create([20000.00, 15000.00, 20000.00, 65000.00, 65000.00, 65000.00, 65000.00]);

  private percentageAboveThirty = 0.05;
  private percentageBelowThirty = 0.1;
  private doomedCity = 'Texas';

  @ExportSignal()
  declare outputReceived: Signal<(text: string) => void>;

  @ExportSignal()
  declare textureReceived: Signal<(texture: Texture2D) => void>;

  override _ready(): void {
    if (OS.getName() in ['Android', 'iOS', 'Web']) {
      this.copyDataToUser();
      this.dbName = 'user://data/test';
      this.jsonName = 'user://data/test_backup';
    }

    // Enable/disable examples here:
    this.exampleOfBasicDatabaseQuerying();
    this.exampleOfInMemoryAndForeignKeySupport();
    this.exampleOfCallExternalFunctions();
    this.exampleOfBlobIo();
    this.exampleOfReadOnlyDatabase();
    this.exampleOfDatabasePersistency();
    this.exampleOfFts5Usage();
  }

  private cprint(text: string): void {
    console.log(text);
    this.outputReceived.emit(text);
  }

  private copyDataToUser(): void {
    const dataPath = 'res://data';
    const copyPath = 'user://data';

    DirAccess.makeDirAbsolute(copyPath);
    const dir = DirAccess.open(dataPath);
    if (dir) {
      dir.listDirBegin();
      let fileName = dir.getNext();
      while (fileName !== '') {
        if (dir.currentIsDir()) {
          // pass
        } else {
          this.cprint('Copying ' + fileName + ' to /user-folder');
          dir.copy(dataPath + '/' + fileName, copyPath + '/' + fileName);
        }
        fileName = dir.getNext();
      }
    } else {
      this.cprint('An error occurred when trying to access the path.');
    }
  }

  // Basic example that goes over all the basic features available in the addon, such
  // as creating and dropping tables, inserting and deleting rows and doing more elementary
  // PRAGMA queries.
  private exampleOfBasicDatabaseQuerying(): void {
    // Make a big table containing the variable types.
    const tableDict = GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      name: { data_type: 'text', not_null: true },
      age: { data_type: 'int', not_null: true },
      address: { data_type: 'char(50)' },
      salary: { data_type: 'real' },
    });

    this.db = new SQLite();
    this.db.path = this.dbName;
    this.db.verbosityLevel = this.verbosityLevel;
    // Open the database using the db_name found in the path variable
    this.db.openDb();
    // Throw away any table that was already present
    this.db.dropTable(this.tableName);
    // Create a table with the structure found in table_dict and add it to the database
    this.db.createTable(this.tableName, tableDict);

    const rowArray = new GArray<GDictionary<Company>>();
    for (let i = 0; i < this.ids.size(); i++) {
      const rowDict = GDictionary.create({
        'id': this.ids.get(i),
        'name': this.names.get(i),
        'age': this.ages.get(i),
        'address': this.addresses.get(i),
        'salary': this.salaries.get(i),
      });
      rowArray.pushBack(rowDict);

      // Insert a new row in the table
      this.db.insertRow(this.tableName, rowDict);
    }

    // Select the id and age of the employees that are older than 30
    const selectCondition = 'age > 30';
    const selectedArray = this.db.selectRows(this.tableName, selectCondition, GArray.create(['id', 'age']));
    this.cprint('condition: ' + selectCondition);
    this.cprint(`result: ${JSON.stringify(selectedArray)}`);

    // Change name of 'Amanda' to 'Olga' and her age to 30
    this.db.updateRows(this.tableName, 'name = \'Amanda\'', GDictionary.create({ 'AGE': 30, 'NAME': 'Olga' }));

    // Select the employee with the name Olga and with age 30
    const selectCondition2 = 'name = \'Olga\' and age = 30';
    const selectedArray2 = this.db.selectRows(this.tableName, selectCondition2, GArray.create(['*'])) as GArray<GDictionary<Company>>;
    this.cprint('condition: ' + selectCondition2);
    this.cprint(`result: ${JSON.stringify(selectedArray2)}`);

    // Delete the employee named Olga
    this.db.deleteRows(this.tableName, 'name = \'Olga\'');

    // Select all employees
    const selectCondition3 = '';
    const selectedArray3 = this.db.selectRows(this.tableName, selectCondition3, GArray.create(['*'])) as GArray<GDictionary<Company>>;
    this.cprint('condition: ' + selectCondition3);
    this.cprint(`result: ${JSON.stringify(selectedArray3)}`);

    // Check the types of the values in the dictionary
    this.cprint('Types of selected columns:');
    this.cprint(`salary: ${typeof selectedArray3.get(0).get('salary')}`);
    this.cprint(`age:    ${typeof selectedArray3.get(0).get('age')}`);
    this.cprint(`name:   ${typeof selectedArray3.get(0).get('name')}`);

    // Delete all employees
    this.db.deleteRows(this.tableName, '*');

    // Add all employees again
    this.db.insertRows(this.tableName, rowArray);

    // Do a normal query
    this.db.query('SELECT COUNT(*) AS \'number_of_employees\' FROM ' + this.tableName + ';');
    const normalQueryResult1 = this.db.queryResult as GArray<GDictionary<{ number_of_employees: number }>>;
    this.cprint(`There are ${normalQueryResult1.get(0).get('number_of_employees')} employees in the company`);

    this.db.query('PRAGMA encoding;');
    const pragmaQuery = this.db.queryResult as GArray<GDictionary<{ encoding: string }>>;
    this.cprint(`Current database encoding is: ${pragmaQuery.get(0).get('encoding')}`);

    // Create a TRIGGER and trigger it!
    this.db.query('CREATE TRIGGER increase_salary_after_employee_termination AFTER DELETE ON ' + this.tableName + ' BEGIN UPDATE ' + this.tableName + ' SET salary = salary + 100;END;');

    this.db.selectRows(this.tableName, '', GArray.create(['name', 'salary']));
    this.cprint(`employees: ${JSON.stringify(this.db.queryResultByReference)}`);

    this.cprint('Firing that slacker Paul!');
    this.db.deleteRows(this.tableName, 'name = \'Paul\'');

    this.db.selectRows(this.tableName, '', GArray.create(['name', 'salary']));
    this.cprint(`employees: ${JSON.stringify(this.db.queryResultByReference)}`);

    // Create a VIEW and use it!
    this.db.query('CREATE VIEW cheapest_employee AS SELECT id, name FROM ' + this.tableName + ' WHERE salary = (SELECT MIN(salary) FROM company) LIMIT 1;');

    // Fire the cheapest employee!
    this.cprint('Firing the cheapest employee!');
    this.db.deleteRows(this.tableName, 'id = (SELECT id FROM cheapest_employee)');

    this.db.selectRows(this.tableName, '', GArray.create(['name', 'salary']));
    this.cprint(`employees: ${JSON.stringify(this.db.queryResultByReference)}`);

    // Create an INDEX!
    this.db.query('create index idx_name on ' + this.tableName + '(name);');

    // Export the table to a json-file with a specified name
    this.db.exportToJson(this.jsonName + '_new');

    // Close the current database
    this.db.closeDb();

    // Import (and, consequently, open) a database from an old backup json-file
    this.cprint('Overwriting database content with old backup...');
    this.db.importFromJson(this.jsonName + '_old');

    // Check which employees were present in this old json-file
    const selectCondition4 = '';
    const selectedArray4 = this.db.selectRows(this.tableName, selectCondition4, GArray.create(['*'])) as GArray<GDictionary<Company>>;
    this.cprint('condition: ' + selectCondition4);
    this.cprint(`result: ${JSON.stringify(selectedArray4)}`);

    // Check the types of the values in the dictionary
    this.cprint('Types of selected columns:');
    this.cprint(`salary: ${typeof selectedArray4.get(0).get('salary')}`);
    this.cprint(`age:    ${typeof selectedArray4.get(0).get('age')}`);
    this.cprint(`name:   ${typeof selectedArray4.get(0).get('name')}`);

    // Import the data (in a destructive manner) from the new backup json-file
    this.cprint('Overwriting database content again with latest backup...');
    this.db.importFromJson(this.jsonName + '_new');

    this.db.query('SELECT * FROM sqlite_master;');
    this.cprint(JSON.stringify(this.db.queryResultByReference));

    // Try to delete a non-existant table from the database.
    if (!this.db.deleteRows(this.otherTableName, '*')) {
      this.cprint('SQL error: ' + this.db.errorMessage);
    }

    // Close the imported database
    this.db.closeDb();
  }

  // This example demonstrates the in-memory and foreign key support. It's
  // rather contrived, but it gets the point across.
  private exampleOfInMemoryAndForeignKeySupport(): void {
    // Create the database as usual.
    this.db = new SQLite();
    // Enable in-memory storage.
    this.db.path = ':memory:';
    this.db.verbosityLevel = this.verbosityLevel;
    // Enable foreign keys.
    this.db.foreignKeys = true;
    // Open the database as usual.
    this.db.openDb();

    // Create a table for all your friends.
    this.db.createTable('friends', GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      name: { data_type: 'text', not_null: true, unique: true },
      hobby: { data_type: 'int', foreign_key: 'hobbies.id', not_null: true },
    }));

    // Create a table for all your friends' hobbies.
    this.db.createTable('hobbies', GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      description: { data_type: 'text', not_null: true, unique: true },
    }));

    // ATTENTION: The important thing to note about the "friends" table is the
    // definition of the foreign key "hobbies.id". This tells SQLITE to enforce
    // the foreign key constraint, and that the field "friends.hobby" is now
    // tied to the field "hobbies.id". Consequently, you are now required to
    // specify a valid hobby when adding a friend to the database, which in
    // turn means you first need to add some hobbies to the database before
    // you can add any of your friends and assign them a hobby.

    // This won't work! There is no valid hobby with id 23 yet!
    this.db.insertRows('friends', GArray.create([
      { id: 1, name: 'John', hobby: 23 },
    ]));

    // This will work! You create the hobby with id 23 first, then you can
    // create your friend referencing that hobby.
    this.db.insertRows('hobbies', GArray.create([
      { id: 23, description: 'Extreme Relaxing' },
    ]));
    this.db.insertRows('friends', GArray.create([
      { id: 1, name: 'John', hobby: 23 },
    ]));

    // Close the database.
    this.db.closeDb();
  }

  private shouldEmployeeBeFired(address: string): boolean {
    if (address === this.doomedCity) {
      return true;
    } else {
      return false;
    }
  }

  private increaseWages(salary: number, age: number): number {
    if (age > 30) {
      return (1.0 + this.percentageAboveThirty) * salary;
    } else {
      return (1.0 + this.percentageBelowThirty) * salary;
    }
  }

  private exampleOfCallExternalFunctions(): void {
    // Make a big table containing the variable types.
    const tableDict = GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      name: { data_type: 'text', not_null: true },
      age: { data_type: 'int', not_null: true },
      address: { data_type: 'char(50)' },
      salary: { data_type: 'real' },
    });

    this.db = new SQLite();
    this.db.path = this.dbName;
    this.db.verbosityLevel = this.verbosityLevel;
    // Open the database using the db_name found in the path variable
    this.db.openDb();
    // Throw away any table that was already present
    this.db.dropTable(this.tableName);
    // Create a table with the structure found in table_dict and add it to the database
    this.db.createTable(this.tableName, tableDict);

    const rowArray = new GArray<GDictionary<Company>>();
    for (let i = 0; i < this.ids.size(); i++) {
      const rowDict = GDictionary.create({
        id: this.ids.get(i),
        name: this.names.get(i),
        age: this.ages.get(i),
        address: this.addresses.get(i),
        salary: this.salaries.get(i),
      });
      rowArray.pushBack(rowDict);

      // Insert a new row in the table
      this.db.insertRow(this.tableName, rowDict);
    }

    // Create SQL functions using Callable
    const shouldFireCallable = Callable.create(this, this.shouldEmployeeBeFired);
    this.db.createFunction('should_employee_be_fired', shouldFireCallable, 1);

    const increaseWagesCallable = Callable.create(this, this.increaseWages);
    this.db.createFunction('increase_wages', increaseWagesCallable, 2);

    // Use the SQL functions in queries
    const updateQuery = `update ${this.tableName}
                         set salary = increase_wages(salary, age);`;
    const deleteQuery = `DELETE FROM ${this.tableName} WHERE should_employee_be_fired(address);`;

    // Execute the queries
    this.db.query(updateQuery);
    this.db.query(deleteQuery);

    const selectCondition = '';
    const selectedArray = this.db.selectRows(this.tableName, selectCondition, GArray.create(['id', 'salary', 'name']));
    this.cprint(`result: ${JSON.stringify(selectedArray)}`);

    // Close the database
    this.db.closeDb();
  }

  // The BLOB-datatype is useful when lots of raw data has to be stored.
  // For example images fall into this category!
  private exampleOfBlobIo(): void {
    // Make a big table containing the variable types.
    const tableDict = GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      data: { data_type: 'blob', not_null: true },
    });

    const texture = ResourceLoader.load('res://icon.png');
    this.textureReceived.emit(texture);
    const texData = texture.getImage()!.savePngToBuffer();

    this.db = new SQLite();
    this.db.path = this.dbName;
    this.db.verbosityLevel = this.verbosityLevel;
    // Open the database using the db_name found in the path variable
    this.db.openDb();
    // Throw away any table that was already present
    this.db.dropTable(this.tableName);
    // Create a table with the structure found in table_dict and add it to the database
    this.db.createTable(this.tableName, tableDict);

    // Insert a new row in the table and bind the texture data to the data column.
    this.db.insertRow(this.tableName, GDictionary.create({ id: 1, data: texData }));

    const selectedArray = this.db.selectRows(this.tableName, '', GArray.create(['data'])) as GArray<GDictionary<{
      data: PackedByteArray
    }>>;
    for (const selectedRow of selectedArray) {
      const selectedData = selectedRow.get('data', new PackedByteArray());

      const image = new Image();
      const _error = image.loadPngFromBuffer(selectedData);
      const loadedTexture = ImageTexture.createFromImage(image);
      this.textureReceived.emit(loadedTexture);
    }

    // Export the table to a json-file and automatically encode BLOB data to base64.
    this.db.exportToJson(this.jsonName + '_base64_new');

    // Import again!
    this.db.importFromJson(this.jsonName + '_base64_old');

    // Check out the 'old' icon stored in this backup file!
    const selectedArray2 = this.db.selectRows(this.tableName, '', GArray.create(['data'])) as GArray<GDictionary<{
      data: PackedByteArray
    }>>;
    for (const selectedRow of selectedArray2) {
      const selectedData = selectedRow.get('data', new PackedByteArray());

      const image = new Image();
      const _error = image.loadPngFromBuffer(selectedData);
      const loadedTexture = ImageTexture.createFromImage(image);
      this.textureReceived.emit(loadedTexture);
    }

    // Close the current database
    this.db.closeDb();
  }

  private regexp(pattern: string, subject: string): boolean {
    const regex = new RegEx();
    regex.compile(pattern);
    const result = regex.search(subject);
    if (result) {
      return true;
    } else {
      return false;
    }
  }

  // Example of accessing a packaged database by using the custom Virtual File System (VFS)
  // which allows packaged databases to be opened in read_only modus.
  // Databases used in this way can be added to the project's export filters
  // and will be readable, but not writable, by Godot.
  private exampleOfReadOnlyDatabase(): void {
    this.db = new SQLite();
    this.db.path = this.packagedDbName;
    this.db.verbosityLevel = this.verbosityLevel;
    this.db.readOnly = true;

    this.db.openDb();

    const callable = Callable.create(this, this.regexp);
    this.db.createFunction('regexp', callable, 2);

    // Select all the creatures
    const selectCondition = '';
    const selectedArray = this.db.selectRows(this.packagedTableName, selectCondition, GArray.create(['*'])) as GArray<GDictionary<Creature>>;
    this.cprint('condition: ' + selectCondition);
    this.cprint(`result: ${JSON.stringify(selectedArray)}`);

    // Select all the creatures that start with the letter 'b'
    const selectCondition2 = 'name LIKE \'b%\'';
    const selectedArray2 = this.db.selectRows(this.packagedTableName, selectCondition2, GArray.create(['name'])) as GArray<GDictionary<Pick<Creature, 'name'>>>;
    this.cprint('condition: ' + selectCondition2);
    this.cprint('Following creatures start with the letter \'b\':');
    for (const row of selectedArray2) {
      this.cprint('* ' + row.get('name'));
    }

    // Do the same thing by using the REGEXP operator
    // This function has to be user-defined as discussed here:
    // https://www.sqlite.org/lang_expr.html#regexp
    const selectCondition3 = 'name REGEXP \'^s.*\'';
    const selectedArray3 = this.db.selectRows(this.packagedTableName, selectCondition3, GArray.create(['name'])) as GArray<GDictionary<Pick<Creature, 'name'>>>;
    this.cprint('condition: ' + selectCondition3);
    this.cprint('Following creatures start with the letter \'s\':');
    for (const row of selectedArray3) {
      this.cprint('* ' + row.get('name'));
    }

    // Open another simultanous database connection in read-only mode.
    const otherDb = new SQLite();
    otherDb.path = this.packagedDbName;
    otherDb.verbosityLevel = this.verbosityLevel;
    otherDb.readOnly = true;

    otherDb.openDb();

    // Get the experience you would get by kiling a mimic.
    const selectCondition4 = 'name = \'mimic\'';
    const selectedArray4 = otherDb.selectRows(this.packagedTableName, selectCondition4, GArray.create(['experience'])) as GArray<GDictionary<Pick<Creature, 'experience'>>>;
    this.cprint(`Killing a mimic yields ${selectedArray4.get(0).get('experience')} experience points!`);

    // Close the current database
    this.db.closeDb();
  }

  private exampleOfDatabasePersistency(): void {
    const tableDict = GDictionary.create({
      id: { data_type: 'int', primary_key: true, not_null: true },
      count: { data_type: 'int', not_null: true, 'default': 0 },
    });

    this.db = new SQLite();
    this.db.path = this.persistentDbName;
    this.db.verbosityLevel = this.verbosityLevel;
    this.db.openDb();
    this.db.createTable(this.tableName, tableDict);

    // Does the row already exist?
    this.db.selectRows(this.tableName, 'id = 1', GArray.create(['count']));
    const queryResult = this.db.queryResult as GArray<GDictionary<{ count: number }>>;
    let count = 0;
    if (queryResult.size() === 0) {
      // It doesn't exist yet! Add it!
      this.db.insertRow(this.tableName, GDictionary.create({ id: 1, count: 0 }));
    } else {
      const result = queryResult.get(0);
      count = Number(result.get('count') || count);
    }

    this.cprint(`Count is: ${count}`);

    // Increment the value for the next time!
    this.db.updateRows(this.tableName, 'id = 1', GDictionary.create({ count: count + 1 }));

    // Close the current database
    this.db.closeDb();
  }

  private fts5TableName = 'posts';

  // Basic example that showcases seaching functionalities of FTS5...
  private exampleOfFts5Usage(): void {
    this.db = new SQLite();
    if (!this.db.compileoptionUsed('ENABLE_FTS5')) {
      this.cprint('No support for FTS5 available in binaries (re-compile with compile option `enable_fts5=yes`)');
      return;
    }

    this.db.path = this.dbName;
    this.db.verbosityLevel = this.verbosityLevel;
    // Open the database using the db_name found in the path variable
    this.db.openDb();
    this.db.dropTable(this.fts5TableName);

    this.db.query('CREATE VIRTUAL TABLE ' + this.fts5TableName + ' USING FTS5(title, body);');

    const rowArray = GArray.create([
      {
        title: 'Learn SQlite FTS5',
        body: 'This tutorial teaches you how to perform full-text search in SQLite using FTS5',
      },
      {
        title: 'Advanced SQlite Full-text Search',
        body: 'Show you some advanced techniques in SQLite full-text searching',
      },
      { title: 'SQLite Tutorial', body: 'Help you learn SQLite quickly and effectively' },
    ]);

    this.db.insertRows(this.fts5TableName, rowArray);

    this.db.query('SELECT * FROM ' + this.fts5TableName + ' WHERE posts MATCH \'fts5\';');
    this.cprint(`result: ${JSON.stringify(this.db.queryResult)}`);

    this.db.query('SELECT * FROM ' + this.fts5TableName + ' WHERE posts MATCH \'learn SQLite\';');
    this.cprint(`result: ${JSON.stringify(this.db.queryResult)}`);

    // Close the current database
    this.db.closeDb();
  }
}

Probably worth noting since this is TypeScript, a real project would typically wrap the database API and make it type safe rather than inline casting query results (as I've done).

[Benjamin-Dobell]

Related to the above, I mentioned a real project would likely want a type-safe TypeScript query API. I've published a godot-sqlite dialect for Kysely: https://github.com/BreakaClub/godot-sqlite-kysely

It supports running godot-sqlite on either the main Godot thread or running on a background thread (GodotJS Worker). Basically this gives you full access to everything Kysely provides.

Example Usage

import {
  Node,
  OS,
  DirAccess,
  Signal,
  Texture2D,
  SQLite,
} from 'godot';
import { ExportSignal } from 'godot.annotations';
import { Generated, Insertable, Kysely, sql } from 'kysely';
import GodotSQLiteKyselyDialect from 'godot-sqlite-kysely';

type CreatureTable = {
  id: Generated<number>;
  health: number;
  name: string;
  experience: number;
}

type CompanyTable = {
  id: Generated<number>;
  name: string;
  address: string;
  age: number;
  salary: number;
}

export interface Database {
  creatures: CreatureTable;
  company: CompanyTable;
}

export default class KyselyTest extends Node {
  private dbName = 'res://data/test';
  private jsonName = 'res://data/test_backup';

  private ids = [1, 2, 3, 4, 5, 6, 7];
  private names = ['Paul', 'Allen', 'Teddy', 'Mark', 'Robert', 'Julia', 'Amanda'];
  private ages = [32, 25, 23, 25, 30, 63, 13];
  private addresses = ['California', 'Texas', 'Baltimore', 'Richmond', 'Texas', 'Atlanta', 'New-York'];
  private salaries = [20000.00, 15000.00, 20000.00, 65000.00, 65000.00, 65000.00, 65000.00];

  @ExportSignal()
  declare outputReceived: Signal<(text: string) => void>;

  override _ready(): void {
    if (OS.getName() in ['Android', 'iOS', 'Web']) {
      this.copyDataToUser();
      this.dbName = 'user://data/test';
      this.jsonName = 'user://data/test_backup';
    }

    this.exampleOfBasicDatabaseQuerying();
  }

  private cprint(text: string): void {
    console.log(text);
    this.outputReceived.emit(text);
  }

  private copyDataToUser(): void {
    const dataPath = 'res://data';
    const copyPath = 'user://data';

    DirAccess.makeDirAbsolute(copyPath);
    const dir = DirAccess.open(dataPath);
    if (dir) {
      dir.listDirBegin();
      let fileName = dir.getNext();
      while (fileName !== '') {
        if (dir.currentIsDir()) {
          // pass
        } else {
          this.cprint('Copying ' + fileName + ' to /user-folder');
          dir.copy(dataPath + '/' + fileName, copyPath + '/' + fileName);
        }
        fileName = dir.getNext();
      }
    } else {
      this.cprint('An error occurred when trying to access the path.');
    }
  }

  // Basic example that goes over all the basic features available in the addon, such
  // as creating and dropping tables, inserting and deleting rows and doing more elementary
  // PRAGMA queries.
  private async exampleOfBasicDatabaseQuerying(): Promise<void> {
    let db = new Kysely<Database>({
      dialect: new GodotSQLiteKyselyDialect(
        {
          path: this.dbName,
          workerModule: 'src/database/worker',
        },
      ),
    });

    // Throw away any table that was already present
    await db.schema.dropTable('company').execute();

    // Create a table with the structure matching CompanyTable
    await db.schema.createTable('company')
      .addColumn('id', 'integer', col => col.primaryKey())
      .addColumn('name', 'text', col => col.notNull())
      .addColumn('age', 'integer', col => col.notNull())
      .addColumn('address', 'text', col => col.notNull())
      .addColumn('salary', 'real', col => col.notNull())
      .execute();

    const rowArray: Insertable<CompanyTable>[] = [];
    for (let i = 0, l = this.ids.length; i < l; i++) {
      const company = {
        id: this.ids[i]!,
        name: this.names[i]!,
        age: this.ages[i]!,
        address: this.addresses[i]!,
        salary: this.salaries[i]!,
      };
      rowArray.push(company);

      await db.insertInto('company')
        .values(company)
        .execute();
    }

    // Select the id and age of the employees that are older than 30
    const selectCondition = 'age > 30';
    const selectedArray = await db.selectFrom('company')
      .select(['id', 'age'])
      .where('age', '>', 30)
      .execute();
    this.cprint('condition: ' + selectCondition);
    this.cprint(`result: ${JSON.stringify(selectedArray)}`);

    // Change name of 'Amanda' to 'Olga' and her age to 30
    await db.updateTable('company')
      .set('name', 'Amanda')
      .where('age', '=', 30)
      .where('name', '=', 'Olga')
      .execute();

    // Select the employee with the name Olga and with age 30
    const selectCondition2 = 'name = \'Olga\' and age = 30';
    const selectedArray2 = await db.selectFrom('company')
      .selectAll()
      .where('name', '=', 'Olga')
      .where('age', '=', 30)
      .execute();
    this.cprint('condition: ' + selectCondition2);
    this.cprint(`result: ${JSON.stringify(selectedArray2)}`);

    // Delete the employee named Olga
    await db.deleteFrom('company')
      .where('name', '=', 'Olga')
      .execute();

    // Select all employees
    const selectedArray3 = await db.selectFrom('company')
      .selectAll()
      .execute();
    this.cprint('condition: ');
    this.cprint(`result: ${JSON.stringify(selectedArray3)}`);

    // Check the types of the values in the dictionary
    this.cprint('Types of selected columns:');
    this.cprint(`salary: ${typeof selectedArray3[0]!.salary}`);
    this.cprint(`age:    ${typeof selectedArray3[0]!.age}`);
    this.cprint(`name:   ${typeof selectedArray3[0]!.name}`);

    // Delete all employees
    await db.deleteFrom('company')
      .execute();

    // Add all employees again
    await db.insertInto('company')
      .values(rowArray)
      .execute();

    // Do a normal query
    const normalQueryResult1 = await sql<{ number_of_employees: number }>`select count(*) as number_of_employees
                                                                          from company;`.execute(db);
    this.cprint(`There are ${normalQueryResult1.rows[0]!.number_of_employees} employees in the company`);

    const pragmaQuery = await sql<{ encoding: string }>`PRAGMA encoding;`.execute(db);
    this.cprint(`Current database encoding is: ${pragmaQuery.rows[0]!.encoding}`);

    // Create a TRIGGER and trigger it!
    await sql`create trigger increase_salary_after_employee_termination
        after delete
        on company begin update company set salary = salary + 100;
    end;`.execute(db);

    const employeesBeforeDeletePaul = await db.selectFrom('company')
      .select(['name', 'salary'])
      .execute();
    this.cprint(`employees: ${JSON.stringify(employeesBeforeDeletePaul)}`);

    this.cprint('Firing that slacker Paul!');
    await db.deleteFrom('company')
      .where('name', '=', 'Paul')
      .execute();

    const employeesAfterDeletePaul = await db.selectFrom('company')
      .select(['name', 'salary'])
      .execute();
    this.cprint(`employees: ${JSON.stringify(employeesAfterDeletePaul)}`);

    // Create a VIEW and use it!
    await db.schema.createView('cheapest_employee')
      .as(
        db.selectFrom('company')
          .select(['id', 'name'])
          .where('salary', '=', sql<number>`(select min(salary) from company)`)
          .limit(1),
      )
      .execute();

    // Fire the cheapest employee!
    this.cprint('Firing the cheapest employee!');
    await db.deleteFrom('company')
      .where('id', '=', sql<number>`(select id from cheapest_employee)`)
      .execute();

    const employeesAfterFireCheapest = await db.selectFrom('company')
      .select(['name', 'salary'])
      .execute();
    this.cprint(`employees: ${JSON.stringify(employeesAfterFireCheapest)}`);

    // Create an INDEX!
    await db.schema
      .createIndex('idx_name')
      .on('company')
      .columns(['name']);

    const sqlite = new SQLite();
    sqlite.path = this.dbName;
    // Open the database using the db_name found in the path variable
    sqlite.openDb();

    // Export the table to a json-file with a specified name
    sqlite.exportToJson(this.jsonName + '_new');

    // Close the current database
    sqlite.closeDb();

    await db.destroy();

    // Import (and, consequently, open) a database from an old backup json-file
    this.cprint('Overwriting database content with old backup...');
    sqlite.importFromJson(this.jsonName + '_old');

    db = new Kysely<Database>({
      dialect: new GodotSQLiteKyselyDialect(
        {
          path: this.dbName,
          workerModule: 'src/database/worker',
        },
      ),
    });

    // Check which employees were present in this old json-file
    const selectCondition4 = '';
    const selectedArray4 = await db.selectFrom('company')
      .selectAll()
      .execute();
    this.cprint('condition: ' + selectCondition4);
    this.cprint(`result: ${JSON.stringify(selectedArray4)}`);

    // Check the types of the values in the dictionary
    this.cprint('Types of selected columns:');
    this.cprint(`salary: ${typeof selectedArray4[0]!.salary}`);
    this.cprint(`age:    ${typeof selectedArray4[0]!.age}`);
    this.cprint(`name:   ${typeof selectedArray4[0]!.name}`);

    // Import the data (in a destructive manner) from the new backup json-file
    this.cprint('Overwriting database content again with latest backup...');
    sqlite.importFromJson(this.jsonName + '_new');

    const sqliteMaster = await sql`select *
                                   from sqlite_master;`.execute(db);
    this.cprint(JSON.stringify(sqliteMaster.rows));

    // Close the imported database
    await db.destroy();

    // Reset
    this.cprint('Restoring base64 backup...');
    sqlite.importFromJson(this.jsonName + '_base64_old');
    sqlite.closeDb();
  }
}

[2shady4u]

Hello @Benjamin-Dobell

Very cool that you made this plugin work with GodotJS 🤩
I've never used Discussions, but I think (?) it should be enabled now for this repository 😄