Divooka.Database.PostgreSQL
Divooka.Database.PostgreSQL.Entities.DatabaseInfo
Remarks
We do not decorate this class with [Table(...)] because it will not map 1-to-1 with a physical table; instead, EF will fill it from a raw SQL query.
Divooka.Database.PostgreSQL.Entities.DatabaseInfo.Name
Not truly a key, but EF needs something marked as key.
Divooka.Database.PostgreSQL.Entities.DatabaseInfo.AccessPrivileges
This can be large or null; we map it as a string
Divooka.Database.PostgreSQL.PostgreSQLConfiguration
Remarks
Design note: All functions should generally return a table (divooka data grid), just like when using psql
Divooka.Database.PostgreSQL.PostgreSQLConfiguration.ConnectionStringTemplate
We expect a `<Database>` to be replaced. E.g. postgresql://<username>:<password>@<host>:<port>/<Database>?sslmode=require%26pooling=true, where the <Database> is literal.
Divooka.Database.PostgreSQL.PostgreSQLConfiguration.Database
Name of the database
Divooka.Database.PostgreSQL.PostgreSQLConfiguration.FormatAsParameters()
Remarks
TODO: This is ChatGPt generated lazy code and it is not safe. Let's rewreite it and parse properly as key-value pairs and pass the unit tests.
Divooka.Database.PostgreSQL.PostgreSQLHelper.ConfigurePostgreSQL()
Divooka.Database.PostgreSQL.PostgreSQLHelper.GetTables()
Query table information.
Divooka.Database.PostgreSQL.PostgreSQLHelper.GetCreateTableCommand()
Gets the full CREATE-TABLE statement that was used to build tableName.
Returns
null if the table does not exist.
Divooka.Database.PostgreSQL.PostgreSQLHelper.GetCreateTableCommand()
Gets the full CREATE-TABLE statement that was used to build tableName.
Returns
null if the table does not exist.
Divooka.Database.PostgreSQL.PostgreSQLHelper.GetCreateTableCommands()
Gets the full CREATE-TABLE statement that was used to build existing tables.
Divooka.Database.PostgreSQL.PostgreSQLHelper.SelectTable()
Parameters
| Name | Description | Default |
|---|---|---|
limit |
Specify the numbers of rows to take. |
Divooka.Database.PostgreSQL.PostgreSQLHelper.CreateTable()
Parameters
| Name | Description | Default |
|---|---|---|
primaryKeys |
A comma separated list of primary keys; If there is a single numerical primary key, it's auso set to auto-increment | |
throwsException |
If true, throws exception if table already exists; If not, will use "CREATE TABLE IF NOT EXISTS" | |
referenceTable |
An input data grid that contains the column definitions and potentially some initial data. | |
schemaOnly |
Whether the table creation should populate (insert) data or just use the reference table for schema.. |
Divooka.Database.PostgreSQL.PostgreSQLHelper.InsertTable()
Remarks
For large data, consider using (PostgreSQL) COPY or parameterized bulk inserts.
TODO: Might want to use transaction?
Divooka.Database.PostgreSQL.PostgreSQLHelper.UpdateTable()
Parameters
| Name | Description | Default |
|---|---|---|
conditions |
E.g., "id = 1 AND status = 'Active'" | |
sets |
E.g., "col1 = 123, col2 = 'Hello'" | |
configuration |
Divooka.Database.PostgreSQL.PostgreSQLHelper.UpdateTable()
Parameters
| Name | Description | Default |
|---|---|---|
keys |
Comma-separated list of column names. Identifies which columns from the source data table to use as keys, i.e. in the where clause. |
Divooka.Database.PostgreSQL.PostgreSQLHelper.CreateTable()
Equivalent to but semantically explicit.
Parameters
| Name | Description | Default |
|---|---|---|
sql |
SQL query for the CREATE TABLE command. | |
configuration |
Service configuration. |
Divooka.Database.PostgreSQL.PostgreSQLHelper.Query()
Parameters
| Name | Description | Default |
|---|---|---|
sql |
Divooka.Database.PostgreSQL.PostgreSQLHelper.IsValidIdentifier()
Check that the table name or column name matches typical identifier rules.
Remarks
Must begin with letter or underscore, contain only letters, digits, or underscores.
Divooka.Database.PostgreSQL.PostgreSQLHelper.EscapeIdentifierForPostgres()
Escapes an identifier for use in PostgreSQL, only use double quotes when necessary.
Remarks
Unquoted identifiers must start with a letter (of any language) or underscore, and can only contain letters, digits, or underscores.
This function uses Unicode character classes (\p{L} for any letter and \p{N} for any digit) so that names in scripts such as Chinese are allowed.
Also, PostgreSQL identifiers are limited to a maximum of 63 characters.
Divooka.Database.PostgreSQL.PostgreSQLHelper.MapDotNetTypeToPostgresType()
Mapping from .NET type to a Postgres type name.
Divooka.Database.PostgreSQL.PostgreSQLHelper.BuildInsertStatements()
Builds insert statements row-by-row (simple string-based).
For large datasets, consider using parameterized queries or COPY.
Divooka.Database.PostgreSQL.PostgreSQLHelper.EscapeValueForPostgres()
Escapes a value as a single-quoted literal for PostgreSQL.
Strings get quotes, null => NULL, etc.