DevAndy | Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Paperless-ngx installieren

Unter Portainer, auf Stacks und neuen Stack im Editor erstellen.

Wichtig: Unter PAPERLESS_SECRET_KEY: XXX eine Zeichenkette generieren lassen.

Posted on 22. Juni 2024 by Andreas in Docker

SQL Server Datenbank Design -> Kroki ER Diagramm

https://kroki.io/examples.html#erd

DECLARE @FlatTable TABLE
(
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    DataType NVARCHAR(128),
    [MaxLength] INT,
    [Precision] INT,
    Scale INT,
    Nullable BIT,
    [Identity] BIT,
    PrimaryKey BIT,
    ForeignKeyName NVARCHAR(128),
    ParentTable NVARCHAR(128),
    ParentColumn NVARCHAR(128),
    ReferencedTable NVARCHAR(128),
    ReferencedColumn NVARCHAR(128)
);


INSERT INTO @FlatTable
SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN 
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN 
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id 


WHERE 1=1
--  tbl.name does not start with __
AND tbl.name != '__RefactorLog'


ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];


DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))
INSERT INTO @StuffedTable


SELECT 
    TableName,
STUFF
(
    (
        SELECT ',' + 
        CASE 
            WHEN PrimaryKey = 1 THEN '*' 
            WHEN ForeignKeyName IS NOT NULL THEN '+'            
            ELSE '' END +
        
        ColumnName + ' ' + [DataType] +
CASE 
WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'
WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'
ELSE '' END


        FROM @FlatTable As T2
        WHERE T2.TableName = T1.TableName
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, '') + ','


AS [Columns]
FROM @FlatTable As T1
GROUP BY TableName




DECLARE @ResultString NVARCHAR(MAX) = 
(SELECT 
STUFF
(
    (
        SELECT ',' + 
        '[' + TableName + ']' + ',' +
        Columns
        FROM @StuffedTable As T2        
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, ''))


PRINT  
'```kroki' + CHAR(13) + CHAR(10) + 
'erd' + CHAR(13) + CHAR(10) + 
REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))
+ '```' + CHAR(13) + CHAR(10)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

DECLARE @FlatTable TABLE

(

TableName NVARCHAR(128),

ColumnName NVARCHAR(128),

DataType NVARCHAR(128),

[MaxLength] INT,

[Precision] INT,

Scale INT,

Nullable BIT,

[Identity] BIT,

PrimaryKey BIT,

ForeignKeyName NVARCHAR(128),

ParentTable NVARCHAR(128),

ParentColumn NVARCHAR(128),

ReferencedTable NVARCHAR(128),

ReferencedColumn NVARCHAR(128)

);

INSERT INTO @FlatTable

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

WHERE 1=1

-- tbl.name does not start with __

AND tbl.name != '__RefactorLog'

ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];

DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))

INSERT INTO @StuffedTable

SELECT

TableName,

STUFF

(

SELECT ',' +

CASE

WHEN PrimaryKey = 1 THEN '*'

WHEN ForeignKeyName IS NOT NULL THEN '+'

ELSE '' END +

ColumnName + ' ' + [DataType] +

CASE

WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'

WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'

ELSE '' END

FROM @FlatTable As T2

WHERE T2.TableName = T1.TableName

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, '') + ','

AS [Columns]

FROM @FlatTable As T1

GROUP BY TableName

DECLARE @ResultString NVARCHAR(MAX) =

(SELECT

STUFF

(

SELECT ',' +

'[' + TableName + ']' + ',' +

Columns

FROM @StuffedTable As T2

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, ''))

'```kroki' + CHAR(13) + CHAR(10) +

'erd' + CHAR(13) + CHAR(10) +

REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))

+ '```' + CHAR(13) + CHAR(10)

Posted on 18. März 2024 by Andreas in SQL

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id      
ORDER BY tbl.[name], col.[name];

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

ORDER BY tbl.[name], col.[name];

Posted on 18. März 2024 by Andreas in SQL

Host oder Webhost mit Autofac

            /*
             Web:

            var builder = WebApplication.CreateBuilder(args);

            builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory());
             */

            var builder = Host.CreateDefaultBuilder()
                              .UseServiceProviderFactory(new AutofacServiceProviderFactory())
                              .ConfigureAppConfiguration((hostContext, config) =>
                              {
                                  config
                                    .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                                    .AddJsonFile($"appsettings.json", optional: true, reloadOnChange: true)
                                    .AddJsonFile($"appsettings.{hostContext.HostingEnvironment.EnvironmentName}.json", optional: true)
                                    .AddJsonFile($"appsettings.{Environment.MachineName}.json", optional: true)
                                    .AddEnvironmentVariables();

                              })
                              .ConfigureServices((hostContext, services) =>
                              {
                                  IConfiguration config = hostContext.Configuration;
                                  Console.WriteLine(config.GetDebugView());

                                  services.AddLogging(b =>
                                  {
                                      b.AddDebug();
                                      b.AddConsole();
                                  });

                                  // Register over IServiceCollection
                              })
                            .ConfigureContainer<ContainerBuilder>(builder =>
                            {
                                builder.RegisterInstance(new LoggerFactory())
                                                        .As<ILoggerFactory>();
                                builder.RegisterGeneric(typeof(Logger<>))
                                                        .As(typeof(ILogger<>))
                                                        .SingleInstance();

                                // Register over Autofac ContainerBuilder
                            });

            App = builder.Build();
            var service = (AutofacServiceProvider)App.Services;
            this.Container = service.LifetimeScope;

            var myService = this.Container.Resolve<MyService>();

Web:

var builder = WebApplication.CreateBuilder(args);

builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory());

var builder = Host.CreateDefaultBuilder()

.UseServiceProviderFactory(new AutofacServiceProviderFactory())

.ConfigureAppConfiguration((hostContext, config) =>

{

config

.SetBasePath(System.IO.Directory.GetCurrentDirectory())

.AddJsonFile($"appsettings.json", optional: true, reloadOnChange: true)

.AddJsonFile($"appsettings.{hostContext.HostingEnvironment.EnvironmentName}.json", optional: true)

.AddJsonFile($"appsettings.{Environment.MachineName}.json", optional: true)

.AddEnvironmentVariables();

})

.ConfigureServices((hostContext, services) =>

{

IConfiguration config = hostContext.Configuration;

Console.WriteLine(config.GetDebugView());

services.AddLogging(b =>

{

b.AddDebug();

b.AddConsole();

});

// Register over IServiceCollection

})

.ConfigureContainer<ContainerBuilder>(builder =>

{

builder.RegisterInstance(new LoggerFactory())

.As<ILoggerFactory>();

builder.RegisterGeneric(typeof(Logger<>))

.As(typeof(ILogger<>))

.SingleInstance();

// Register over Autofac ContainerBuilder

});

App = builder.Build();

var service = (AutofacServiceProvider)App.Services;

this.Container = service.LifetimeScope;

var myService = this.Container.Resolve<MyService>();

Posted on 11. März 2024 by Andreas in C#

Entity Framework EF – Tabellen Models generieren lassen

Alt + T -> „NU“ eingeben

scaffold-DbContext "Server=XXX; Database=XXX; user id=sa; password = XXX;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models\XXX" -Context XXXContext -Force -UseDatabaseNames -Tables XXX

1	scaffold-DbContext "Server=XXX; Database=XXX; user id=sa; password = XXX;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models\XXX" -Context XXXContext -Force -UseDatabaseNames -Tables XXX

Posted on 1. März 2024 by Andreas in Allgemein, C#

Windows Powershell Dienst einrichten

#Service installieren 
sc.exe create "Service Name" binpath="PathToService\MyService.exe" start= auto

#Service starten
sc.exe start "Service Name"

#Service anhalten
sc.exe stop "Service Name"

#Service löschen
sc.exe delete  "Service Name"

#Service installieren

sc.exe create "Service Name" binpath="PathToService\MyService.exe" start= auto

#Service starten

sc.exe start "Service Name"

#Service anhalten

sc.exe stop "Service Name"

#Service löschen

sc.exe delete "Service Name"

Posted on 12. Februar 2024 by Andreas in Allgemein

SQL Server Statistik IO und Time

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT er.ReportID, er.ReportName, er.ReportNumber
FROM dbo.EmployeeReports er
WHERE er.ReportNumber LIKE '%33%'
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber

FROM dbo.EmployeeReports er

WHERE er.ReportNumber LIKE '%33%'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

Posted on 14. Januar 2024 by Andreas in SQL

Nuxt 3 mit Keycloak autorisieren

In diesem Beitrag will ich eine Schritt für Schritt Anleitung geben, wie man Nuxt 3 mit Keycloak autorisiert.

Die Thematik um Autorisierung und Authentifizierung bedarf einer soliden Kenntnis in der Thematik. Ich empfehle daher unbedingt sich die Zeit zu nehmen und das folgende Video anzuschauen und sicher zu gehen, dass alles verstanden wurde:

Posted on 31. Januar 2023 by Andreas in Allgemein

Nuxt3 mit Vuetify 3 – Teil 3: Entwickeln in Vue / Typescript

Typescript muss in VS Code installiert werden. -g steht für globally (Alle Projekte)

npm i -g typescript

1	npm i -g typescript

Typescript Version prüfen

tsc --version

1	tsc --version

Enums

Ein Fehler ist aufgetreten. Bitte versuchen Sie es später erneut.

Objekte werden mit reactive<type>({…}) reactiviert und primitive Datentypen mit ref<string>(…)

const myVariable = ref<string>("test");
const opel = reactive<myCar>({wheels: 4, name'Astra'});

1 2	const myVariable = ref<string>("test"); const opel = reactive<myCar>({wheels: 4, name'Astra'});

Mixins werden in eine Vue Instanz hinzugefügt. So als würde man 2 Vue Instanzen verschmelzen. Composables sind dem ähnlich. In Mixins wird alles importiert, bei Composables muss jede Variable, Methode, etc. einzeln importiert werden.

Guter Artikel dazu:

What is a Vue.js Composable? – Vue.js Tutorials (vueschool.io)

Posted on 4. Mai 2022 by Andreas in Allgemein

Nuxt3 mit Vuetify 3 – Teil 2: Layout

Wenn man durch eine App navigiert, dann ist der Seitenaufbau überall identisch. Das Menü, die Kopfzeile und Fusszeile sind auf jeder Seite identisch. Lediglich der Inhalt ändert sich.

In Nuxt 3 werden Layouts im „layouts“-Ordner erstellt und anschließend in die app.vue hinzugefügt. Einzelne Seiten werden im „pages“-Ordner erstellt.

Nuxt 3 routet die pages anhand des Namens. Nur die page „index.vue“ wird zum root Knoten geroutet.

Also erstellen wir ein Verzeichnis „layouts“ und ein Verzeichnis „pages“.

In das „layouts“ Verzeichnis, eine Datei erstellen „default.vue“.

und folgenden Inhalt kopieren:

<template>
  <v-app>
    <v-app-bar>
      <v-app-bar-nav-icon @click.stop="drawer = !drawer"></v-app-bar-nav-icon>

      <v-toolbar-title>Meine neue App</v-toolbar-title>

      <v-spacer></v-spacer>

      <v-btn variant="text" icon="mdi-magnify"></v-btn>

      <v-btn variant="text" icon="mdi-filter"></v-btn>

      <v-btn variant="text" icon="mdi-dots-vertical"></v-btn>
    </v-app-bar>

    <!-- prepend-icon="mdi-folder"  -->
    <v-navigation-drawer v-model="drawer" bottom permanent>
      <v-list nav>
        <v-list-item
          v-for="item in items"
          :key="item.to"
          :title="item.title"
          :to="item.to"
        ></v-list-item>
      </v-list>
    </v-navigation-drawer>
    <v-main>
      <slot />
    </v-main>
  </v-app>
</template>

<script>
export default {
  data: () => ({
    drawer: true,
    group: null,
    currentItem: null,
    items: [
      {
        title: "Start",
        to: "/",
      },
      {
        title: "Page 1",
        to: "pageOne",
      },
    ],
  }),

  watch: {
    group() {
      this.drawer = true;
    },
  },
};
</script>

<v-app>

<v-app-bar>

<v-app-bar-nav-icon @click.stop="drawer = !drawer"></v-app-bar-nav-icon>

<v-toolbar-title>Meine neue App</v-toolbar-title>

<v-spacer></v-spacer>

<v-btn variant="text" icon="mdi-magnify"></v-btn>

<v-btn variant="text" icon="mdi-filter"></v-btn>

<v-btn variant="text" icon="mdi-dots-vertical"></v-btn>

</v-app-bar>

<v-navigation-drawer v-model="drawer" bottom permanent>

<v-list nav>

<v-list-item

v-for="item in items"

:key="item.to"

:title="item.title"

:to="item.to"

></v-list-item>

</v-list>

</v-navigation-drawer>

<v-main>

</v-main>

</v-app>

</template>

export default {

data: () => ({

drawer: true,

group: null,

currentItem: null,

items: [

{

title: "Start",

to: "/",

{

title: "Page 1",

to: "pageOne",

}),

watch: {

group() {

this.drawer = true;

};

</script>

Dort, wo <slot /> steht, kommt der Inhalt der einzelnen Pages hinein.

Jetzt in das Verzeichnis „pages“

die Datei index.vue mit folgenden Inhalt

<template>
  <h1>Index page</h1>
</template>

<h1>Index page</h1>

</template>

und die Datei „pageOne.vue“ mit folgenden Inhalt erstellen

<template>
  <h1>Page One</h1>
</template>

</template>

In der app.vue müssen wir nun sagen, dass wir nicht mehr die Nuxt Welcome page sehen wollen, sondern unser Layout:

<template>
  <NuxtLayout name="default">
    <NuxtPage />
  </NuxtLayout>
</template>

</NuxtLayout>

</template>

Posted on 4. Mai 2022 by Andreas in Allgemein

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Paperless-ngx installieren

SQL Server Datenbank Design -> Kroki ER Diagramm

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

Host oder Webhost mit Autofac

Entity Framework EF – Tabellen Models generieren lassen

Windows Powershell Dienst einrichten

SQL Server Statistik IO und Time

Nuxt 3 mit Keycloak autorisieren

Nuxt3 mit Vuetify 3 – Teil 3: Entwickeln in Vue / Typescript

Nuxt3 mit Vuetify 3 – Teil 2: Layout

Seiten

Neueste Beiträge

Neueste Kommentare

Archive

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive