Author Archives: Andreas

Jupyter unter Windows installieren

Mit Jupyter (Julia + Python) kann man interaktive Notebooks erstellen. Ein Notebook besteht aus Cells, die entweder als Text (markdown) oder Code (Python, etc.) nacheinander erstellt werden können. Der Code kann im Notebook ausgeführt werden und visuell dargestellt werden. Variablen können Code-Zelle übergreifend genutzt werden. Damit der Code ausgeführt werden kann, wird ein Kernel benötigt, […]

Posted on 3. September 2024 by Andreas in Allgemein

VS Code SQL Fenster horizontal ausrichten

Posted on 18. August 2024 by Andreas in Allgemein

Cronicle in Rocky Linux installieren

Cronicle nodeJs über dnf installieren. Nicht über nvm, weil nvm nur für den jeweilligen User installiert

sudo dnf install nodejs

1	sudo dnf install nodejs

So wie hier beschrieben, manuelle Schritte durchführen. Wichtig, vorher die aktuelle Version herausfinden und im Archiv ersetzen

https://github.com/jhuckaby/Cronicle/blob/master/docs/Setup.md

mkdir -p /opt/cronicle
cd /opt/cronicle
curl -L https://XXXXXX.tar.gz | tar zxvf - --strip-components 1
npm install
node bin/build.js dist

https://github.com/jhuckaby/Cronicle/blob/master/docs/Setup.md

mkdir -p /opt/cronicle

cd /opt/cronicle

curl -L https://XXXXXX.tar.gz | tar zxvf - --strip-components 1

npm install

node bin/build.js dist

Firewall Port freischalten

sudo firewall-cmd --zone=public --add-port=3012/tcp --permanent
sudo firewall-cmd --reload

1 2	sudo firewall-cmd --zone=public --add-port=3012/tcp --permanent sudo firewall-cmd --reload

Maschine neustarten

sudo reboot

1	sudo reboot

Posted on 27. Juni 2024 by Andreas in Docker, Selfhost

rclone OneDrive

rclone Konfigurieren 1. zu mnt Verzeichnis bewegen mit cd und ls 2. OneDrive Ordner erstellen:

mkdir OneDrive

1	mkdir OneDrive

3. rclone installieren

dnf install rclone

1	dnf install rclone

4. Neue rclone config erstellen

rclone config

1	rclone config

5. n -> New remote Name: OneDrive Nach Nummer suchen. In meinem Fall 27 Microsoft OneDrive client_id mit Enter überspringen, client_secret mit Enter auch überspringen 1 für Microsoft Cloud […]

Posted on 25. Juni 2024 by Andreas in Docker

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

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

Author Archives: Andreas

Jupyter unter Windows installieren

VS Code SQL Fenster horizontal ausrichten

Cronicle in Rocky Linux installieren

rclone OneDrive

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

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!

Author Archives: Andreas

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive