記事一覧 - ほぼC#の話とそれ以外の話

ASP.NET Core Webアプリで、SQLiteを使用して認証用のDBを作成

C#, Sqlite, Identity

プロジェクトの作成

ASP.NET Core Webアプリ(Model-View-Controller)を選択

以下で作成

パッケージの更新及び追加

ソリューションのNugetパッケージの管理を開く
更新プログラムがあるので、更新を行う

Microsoft.EntityFrameworkCore.Sqliteをインストールする

認証関係のDBを作成

以下ファイルを変更する
./appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=Identity.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

SQLiteの接続文字列を設定。

./Program.cs

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(connectionString));

UseSqlServerをUseSqliteに変更する。

パッケージマネージャーコンソールを起動

Update-Database -Context ApplicationDbContext

Update-Databaseコマンドを実行しDBを作成する。 ※複数のプロジェクトがある場合、パッケージマネージャーコンソールの規定のプロジェクトは、対象のプロジェクトを選択しておくこと。

エラー発生

PM> Update-Database -Context ApplicationDbContext
Build started...
Build succeeded.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "MigrationId", "ProductVersion"
      FROM "__EFMigrationsHistory"
      ORDER BY "MigrationId";
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '00000000000000_CreateIdentitySchema'.
Applying migration '00000000000000_CreateIdentitySchema'.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoles" (
          "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
          "Name" nvarchar(256) NULL,
          "NormalizedName" nvarchar(256) NULL,
          "ConcurrencyStamp" nvarchar(max) NULL
      );
Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "AspNetRoles" (
    "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
    "Name" nvarchar(256) NULL,
    "NormalizedName" nvarchar(256) NULL,
    "ConcurrencyStamp" nvarchar(max) NULL
);
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "max": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'near "max": syntax error'.

エラー対応

SQLiteでは、 .HasColumnType("nvarchar(max)"); がエラーとなるので、コメントアウトする。

./Data/Migrations/00000000000000_CreateIdentitySchema.Designer.cs(修正の一例)

            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityRole", b =>
                {
                    b.Property<string>("Id")
                        .HasColumnType("nvarchar(450)");

                    b.Property<string>("ConcurrencyStamp")
                        .IsConcurrencyToken();
                        //.HasColumnType("nvarchar(max)"); // コメントアウトする

                    b.Property<string>("Name")
                        .HasColumnType("nvarchar(256)")
                        .HasMaxLength(256);

                    b.Property<string>("NormalizedName")
                        .HasColumnType("nvarchar(256)")
                        .HasMaxLength(256);

                    b.HasKey("Id");

                    b.HasIndex("NormalizedName")
                        .IsUnique()
                        .HasName("RoleNameIndex")
                        .HasFilter("[NormalizedName] IS NOT NULL");

                    b.ToTable("AspNetRoles");
                });

再度DB作成

パッケージコンソールマネージャーを開きコマンドを実行

Update-Database -Context ApplicationDbContext

Done.

PM> Update-Database -Context ApplicationDbContext
Build started...
Build succeeded.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "MigrationId", "ProductVersion"
      FROM "__EFMigrationsHistory"
      ORDER BY "MigrationId";
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '00000000000000_CreateIdentitySchema'.
Applying migration '00000000000000_CreateIdentitySchema'.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoles" (
          "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
          "Name" nvarchar(256) NULL,
          "NormalizedName" nvarchar(256) NULL,
          "ConcurrencyStamp" TEXT NULL
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetUsers" (
          "Id" nvarchar(450) NOT NULL CONSTRAINT "PK_AspNetUsers" PRIMARY KEY,
          "UserName" nvarchar(256) NULL,
          "NormalizedUserName" nvarchar(256) NULL,
          "Email" nvarchar(256) NULL,
          "NormalizedEmail" nvarchar(256) NULL,
          "EmailConfirmed" bit NOT NULL,
          "PasswordHash" TEXT NULL,
          "SecurityStamp" TEXT NULL,
          "ConcurrencyStamp" TEXT NULL,
          "PhoneNumber" TEXT NULL,
          "PhoneNumberConfirmed" bit NOT NULL,
          "TwoFactorEnabled" bit NOT NULL,
          "LockoutEnd" datetimeoffset NULL,
          "LockoutEnabled" bit NOT NULL,
          "AccessFailedCount" int NOT NULL
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoleClaims" (
          "Id" int NOT NULL CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY,
          "RoleId" nvarchar(450) NOT NULL,
          "ClaimType" TEXT NULL,
          "ClaimValue" TEXT NULL,
          CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetUserClaims" (
          "Id" int NOT NULL CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY,
          "UserId" nvarchar(450) NOT NULL,
          "ClaimType" TEXT NULL,
          "ClaimValue" TEXT NULL,
          CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetUserLogins" (
          "LoginProvider" nvarchar(128) NOT NULL,
          "ProviderKey" nvarchar(128) NOT NULL,
          "ProviderDisplayName" TEXT NULL,
          "UserId" nvarchar(450) NOT NULL,
          CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
          CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetUserRoles" (
          "UserId" nvarchar(450) NOT NULL,
          "RoleId" nvarchar(450) NOT NULL,
          CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
          CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
          CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetUserTokens" (
          "UserId" nvarchar(450) NOT NULL,
          "LoginProvider" nvarchar(128) NOT NULL,
          "Name" nvarchar(128) NOT NULL,
          "Value" TEXT NULL,
          CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
          CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL;
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
      VALUES ('00000000000000_CreateIdentitySchema', '8.0.8');
Done.

作成できた。

投稿日時： 2024-08-27 13:23:27

コメント数(0)

Ctrl + F5

キャッシュを無視してページをリロードする。
そうだったのか・・・

投稿日時： 2024-08-15 07:23:15

コメント数(0)

画像の一部を表示する方法

Phaser, JavaScript

60pxの市松模様で10行、10列(縦横600px)の画像があったとします。

これを中心に配置すると以下のようになります。
./src/main.js

import Phaser from "phaser";
import GameScene from "./scenes/GameScene";

const config = {
    type: Phaser.AUTO,
    width: 1200,
    height: 600,
    parent: 'game-container',
    backgroundColor: '#fff',
    scale: {
        mode: Phaser.Scale.NONE,
        autoCenter: Phaser.Scale.CENTER_BOTH
    },
    dom: {
        createContainer: true
    },
    scene: [
        GameScene
    ]
};

export default new Phaser.Game(config);

./src/scenes/GameScene.js

import { Scene } from 'phaser';

export default class GameScene extends Phaser.Scene {
    constructor() {
        super('GameScene');
    }

    preload() {
        this.load.image("bg", "./assets/bg.png");
    }

    create() {
        const centerX = this.scale.width / 2;
        const centerY = this.scale.height / 2;
        const bg = this.add.image(centerX, centerY, "bg");
        bg.setOrigin(0.5);
    }

    update() {
    }
}

そのうち、左上を基準に、2行、4列(縦120px × 横240px)を表示しようとしてcropを使うと
./src/scenes/GameScene.js

import { Scene } from 'phaser';

export default class GameScene extends Phaser.Scene {
    constructor() {
        super('GameScene');
    }

    preload() {
        this.load.image("bg", "./assets/bg.png");
    }

    create() {
        const centerX = this.scale.width / 2;
        const centerY = this.scale.height / 2;
        const bg = this.add.image(centerX, centerY, "bg");
        bg.setOrigin(0.5);

        const cropX = 0;
        const cropY = 0;
        const cropWidth = 240;
        const cropHeight = 120;
        bg.setCrop(cropX, cropY, cropWidth, cropHeight);
    }

    update() {
    }
}

setOriginはあくまでも全体のサイズにおけるセンターのため、
切り取った部分をセンターにもっていくためには、調整が必要。

./src/scenes/GameScene.js

import { Scene } from 'phaser';

export default class GameScene extends Phaser.Scene {
    constructor() {
        super('GameScene');
    }

    preload() {
        this.load.image("bg", "./assets/bg.png");
    }

    create() {
        const centerX = this.scale.width / 2;
        const centerY = this.scale.height / 2;
        const width = 600;
        const height = 600;

        // 切り取り領域
        const cropX = 0;
        const cropY = 0;
        const cropWidth = 240;
        const cropHeight = 120;

        const bg = this.add.image(centerX + (width - cropWidth) / 2, centerY + (height - cropHeight) / 2, "bg");
        bg.setOrigin(0.5);
        bg.setCrop(cropX, cropY, cropWidth, cropHeight);
    }

    update() {
    }
}

どちらかというと、原点をセンターにするより、左上の位置から調整した方が楽ですね。

    create() {
        const centerX = this.scale.width / 2;
        const centerY = this.scale.height / 2;

        // 切り取り領域
        const cropX = 0;
        const cropY = 0;
        const cropWidth = 240;
        const cropHeight = 120;

        const bg = this.add.image(centerX - cropWidth / 2, centerY - cropHeight / 2, "bg");
        bg.setOrigin(0);
        bg.setCrop(cropX, cropY, cropWidth, cropHeight);
    }

１つの画像に同一サイズの画像をまとめているのであれば、SpriteSheetを使って切り出してつかうことができます。
↓縦横20pxのデータが8つある。

./src/scenes/GameScene.js

import { Scene } from 'phaser';

export default class GameScene extends Phaser.Scene {
    constructor() {
        super('GameScene');
    }

    preload() {
        this.load.spritesheet("charList", "./assets/CharList.png", { frameWidth: 20, frameHeight: 20 });
    }

    create() {
        const charList = [];

        for (let n = 0; n < 8; n++) {
            const row = 20 * Math.trunc(n / 4);
            const col = 20 * (n % 4);
            const char = this.add.image(col, row, "charList", n);
            char.setOrigin(0); // 原点は左上
            charList.push(char);
        }
    }

    update() {
    }
}

↓左上から２行４列で表示

投稿日時： 2024-08-14 15:34:14
更新日時： 2024-08-14 16:17:14

コメント数(0)

ASP.NET Core Webアプリで、SQLiteを使用して認証用のDBを作成

プロジェクトの作成

パッケージの更新及び追加

認証関係のDBを作成

エラー対応

再度DB作成

Ctrl + F5

画像の一部を表示する方法

最近の投稿

最近のコメント

タグ

アーカイブ

その他