SqlAutoGen 0.0.9

SqlAutoGen

Introduction

Welcome to SqlAutoGen, a C# library designed to generate complete executables using a straightforward naming convention. It integrates a SQLite database using Microsoft.EntityFrameworkCore and exposes a local API via GenHTTP, transforming an ORM database structure into JSON. Additionally, it creates API endpoints for each table with common CRUD methods and includes data validation, creation and generation capabilities through the Bogus Library.

Goal

To provide an easy and efficient way to manage, validate, score, and query the vast amounts of data produced daily. The focus is on local machine usage and ease of integration, aiming to automate repetitive tasks.

Features

Completed Features:

• Roslyn Source Code Generation: Roslyn Source Code Generation: Automatically generates source code to build executables based on naming conventions.
• Local SQLite Database Integration: Utilize Microsoft.EntityFrameworkCore for seamless local SQLite database integration.
• Local API via GenHTTP: Provide a local API facilitating straightforward database interactions.
• ORM to JSON Conversion: Transform ORM database structures into JSON format.
• CRUD API Endpoints: Offer API endpoints for basic CRUD operations.
• Data Generation Using Bogus: Populate databases with synthetic data for testing.
• Command Execution: Incorporate Spectre.Console for easy Task Execution via Command.

In Development:

• Data Validation: Implement validation and scoring for incoming data.
• Data Creation: Generate aggregated data based on evaluations.
• Task Scheduling: Manage application methods using a database or API.
• Interactive Dashboard: Facilitate data manipulation through a UI frontend and tabulator.js.

Planned:

• Data Visualization: Introduce data plotting with relationships using d3-force.

Usage

Tables

Every partial class with DatabaseSqlAttribute will follow these rules:

Each table created with the DatabaseCreate has a primary key "Id" which will be used for all the common CRUD operations. Each line represents one Table where the left Side are the Table type.TableName(options) and on the right side are all the Columns with the same structure as the Table type.ColumnName(generate) but inside the brackets are the data creation options

The rules for the SqlAutoGen scheme is as follows:

• multiple lines are allowed where one line is a Table with its Columns
• Table and Columns delimiter is colon and a comma seperates the Columns from ech other
• the common scheme is always:type.TableName(options):type.ColumnName(generate),type.ColumnName(generate.ColumnName) ,type.ColumnName(generate.ColumnName.ColumnName)
• default type for Table is "key" and default for Columns is "string"
• example "Account:Name(firstname),LastName(lastname),datetime.LastSeen(dateupdate), int.Visited(uint)"
• the only requirment is the TableName, the rest is optional
• type and TableName,ColumName allows for letters and numbers. TableName,ColumName also allows special characters as prefix
• inside the brackets all characters are allowed but nested brackets needs to be closed
• all errors get discarded

Table Types

• "link"
• "enum"

!!! Not Ready yet !!!

• "score"
• "task"

Columns

Column Types

• "string"
• "guid"
• "byte"
• "bytes"
• "float"
• "int"
• "datetime"
• "bool"
• "boolean"
• "double"
• "dateupdate"

Column Options

• "!"
• "?"
• "#"
• "+"
• "+"

Examples

Account:Name,LastName,Username,Mail

will create a table with the name Accounts and the columns Id, Name, LastName, Username, Mail and all corresponding methods.

Enum

With the enum prefix, each column is a read-only value in a table with Id, Name, and Description (optional) columns. The id corresponds to the enum value, and the name to the value name. You can choose the value by prefixing it on the column name, for example 22.Regional. You can choose the description by using brackets after the column name 22.Regional(Only for inner country usage).

enum.AddressTypes:Local,Global,Country,22.Regional(Only for inner country usage)

It is also possible to use an existing enum directly by passing the full namespace and name through the brackets

enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)

enum.EnumTest(SqlAutoGen.Sample.Enums.TestEnum)

Link

link.Location:Account,Address,Login
enum.AddressTypes:Local,Global,Country,22.Regional(Only for inner country usage)
City:Address(address),Street(street)
Account:Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName),Email
Address:Street, Prefix, Gps, AddressTypes, Number

#TODO finish link explanations

Score

Task

Examples

Create Database

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;

namespace TablesExample;

[DatabaseSql(name: "TestDatabase", path: "database")]
[DatabaseCreate("""

enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)

Address:AddressTypes,Prefix,Name

""")]

Naming

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;
using SqlAutoGen.Data.Enums.Builder;

namespace NamingExample;

[DatabaseSql(name: "TestDatabase", path: "database")]
[DatabaseCreate("""
enum.AddressType:Local,Global,Country,22.Regional(Only for inner country usage)

enum.LoggerTypes(Microsoft.Extensions.Logging.LogLevel)

enum.EnumTest(SqlAutoGen.Sample.Enums.TestEnum)

Email:Provider(protocol), FullEmail(email.Provider)

Street:string.Name(street)

Gps:double.Lat(lat),Hash(hash),double.Lon(lon)

Login:Hash(hash),Password(password)

Account:string.Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName),Email

Address:Street,Prefix(streetsuffix),Gps,int.Number(int),AddressTypes,EnumTest

link.Location:Account,Address,Login
""")]

Templates

Templates need to be referenced in the .csproj file and have the ending .template. After that, they can be referenced in DatabaseCreateAttribute. Important. Any changes done to these templates need a restart of the IDE.

<ItemGroup>
    <AdditionalFiles Include="Templates\testdatabase.template" />
    <None Remove="testdatabase.template" />
    <AdditionalFiles Include="Templates\account.template" />
    <None Remove="accounts.template" />
</ItemGroup>

Program

ApiAppAttribute is only active on the Program class and will discarded everwhere else.

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;
namespace AppExample;

[ApiApp("0.0.0.0", 5555)]
partial class Program;

using SqlAutoGen.Data.Attributes;
using SqlAutoGen.Data.Enums;
namespace AppExample;

[ApiApp("0.0.0.0", 5555)]
partial class Program;

generation types example:

double.Accuracy(udouble)

data generation available:

• "udouble"
• "ufloat"
• "uint"
• "normalized"
• "alphanumeric"
• "boolean"
• "decimal"
• "double"
• "float"
• "int"
• "long"
• "uuid"
• "bytes"
• "byte"
• "id"
• "avatar"
• "email"
• "exampleemail"
• "username"
• "name"
• "usernameunicode"
• "domainname"
• "domainword"
• "domainsuffix"
• "ip"
• "port"
• "ipendpoint"
• "ipv6"
• "ipv6address"
• "ipv6endpoint"
• "useragent"
• "mac"
• "password"
• "internetcolor"
• "protocol"
• "url"
• "urlwithpath"
• "urlrootedpath"
• "abbreviation"
• "adjective"
• "noun"
• "verb"
• "ingverb"
• "phrase"
• "phone"
• "phonenumber"
• "phonenumberformat"
• "firstname"
• "lastname"
• "fullname"
• "prefix"
• "suffix"
• "findname"
• "jobtitle"
• "jobdescriptor"
• "jobarea"
• "jobtype"
• "word"
• "words"
• "letter"
• "sentence"
• "sentences"
• "paragraph"
• "paragraphs"
• "text"
• "lines"
• "slug"
• "datauri"
• "placeimgurl"
• "picsumurl"
• "placeholderurl"
• "loremflickrurl"
• "lorempixelurl"
• "account"
• "accountname"
• "amount"
• "transactiontype"
• "currency"
• "creditcardnumber"
• "creditcardcvv"
• "bitcoinaddress"
• "ethereumaddress"
• "litecoinaddress"
• "routingnumber"
• "bic"
• "iban"
• "zipcode"
• "postalcode"
• "postal"
• "city"
• "street"
• "cityprefix"
• "citysuffix"
• "streetname"
• "buildingnumber"
• "streetsuffix"
• "secondaryaddress"
• "county"
• "country"
• "fulladdress"
• "countrycode"
• "state"
• "stateabbr"
• "latitude"
• "longitude"
• "direction"
• "cardinaldirection"
• "ordinaldirection"
• "past"
• "pastoffset"
• "soon"
• "soonoffset"
• "future"
• "futureoffset"
• "between"
• "betweenoffset"
• "recent"
• "recentoffset"
• "timespan"
• "month"
• "weekday"
• "timezonestring"
• "companysuffix"
• "companyname"
• "catchphrase"
• "bs"
• "department"
• "price"
• "categories"
• "productname"
• "commercecolor"
• "product"
• "productadjective"
• "productmaterial"
• "productdescription"
• "filename"
• "directorypath"
• "filepath"
• "commonfilename"
• "mimetype"
• "commonfiletype"
• "commonfileext"
• "filetype"
• "fileext"
• "semver"
• "version"
• "exception"
• "androidid"
• "applepushtoken"
• "blackberrypin"
• "column"
• "databasetype"
• "collation"
• "engine"
• "review"
• "reviews"
• "vin"
• "manufacturer"
• "model"
• "vehicletype"
• "fuel"
• "guid"
• "hash"
• "day"
• "year"
• "number"
• "productcategory"
• "currencysymbol"
• "creditcardexpiry"
• "dateofbirth"
• "height"
• "width"
• "createdat"
• "datetimeupdate"
• "datemonthyear"
• "dateyearmonth"
• "monthdateyear"
• "monthyeardate"
• "yearmonthdate"
• "yeardatemonth"
• "addresscity"
• "addresspostalcode"
• "cityaddress"
• "citypostalcode"
• "postalcodeaddress"
• "postalcodecity"
• "addresscitypostalcode"
• "addresspostalcodecity"
• "cityaddresspostalcode"
• "citypostalcodeaddress"
• "postalcodeaddresscity"
• "postalcodecityaddress"
• "hour24minutesecond"
• "second"
• "secondminute"
• "secondhour"
• "secondminutehour"
• "hourminute"
• "currencycode"
• "duration"
• "durationdouble"
• "durationfloat"
• "gender"
• "age"
• "ageadult"
• "ageteen"
• "agechild"
• "agetwenties"
• "agethirties"
• "agefourties"
• "agefifties"
• "agesixties"

data generation: example: double.Accuracy(username)

data generation with params example:

Name(firstname),LastName(lastname),Username(username.Name.LastName),Mail(email.Name.LastName)

A dot after the selected generation type is used to determine which data to take as a parameter from a previously specified column that also generates data with the purpose of using part of it to generate the one its passed to.

generation types with params:

• "username"
• "usernameunicode"
• "email"
• "exampleemail"
• "urlwithpath"
• "urlrootedpath"
• "url"
• "name"
• "firstname"
• "lastname"
• "fullname"
• "findname"
• "prefix"
• "suffix"
• "jobtitle"
• "companyname"
• "companycatchphrase"
• "companybs"
• "phonenumber"
• "phonenumberformat"
• "randomword"
• "randomwords"
• "addresscity"
• "addresszipcode"
• "addresstate"