Class SQLUtils

Hierarchy

SQLUtils

Constructors

constructor

new SQLUtils(): SQLUtils
Returns SQLUtils

Properties

`Static` NUMBER_PATTERN

NUMBER_PATTERN: string = '\\d+'

Pattern for matching numbers

Methods

`Static` columnDefaultValue

columnDefaultValue(defaultValue: any, dataType: GeoPackageDataType): string
Get the column default value as a string

Returns
default value
Parameters
- defaultValue: any
  
  default value
- dataType: GeoPackageDataType
  
  data type
Returns string
- Defined in lib/db/sqlUtils.ts:526

`Static` columnDefinition

columnDefinition(column: UserColumn): string
Create the column definition SQL in the format: column_type[(max)] [NOT NULL] [PRIMARY KEY AUTOINCREMENT]

Returns
column definition SQL
Parameters
- column: UserColumn
  
  user column
Returns string
- Defined in lib/db/sqlUtils.ts:83

`Static` columnSQL

columnSQL(column: UserColumn): string
Create the column SQL in the format: "column_name" column_type[(max)] [NOT NULL] [PRIMARY KEY AUTOINCREMENT]

Returns
column SQL
Parameters
- column: UserColumn
  
  user column
Returns string
- Defined in lib/db/sqlUtils.ts:73

`Static` count

count(connection: GeoPackageConnection, tableName: string, where: string, whereArgs: [] | Record<string, any>): number
Count for query

Returns
number the count of rows
Parameters
- connection: GeoPackageConnection
  
  connection
- tableName: string
  
  table
- where: string
  
  where clause
- whereArgs: [] | Record<string, any>
  
  where arguments
Returns number
- Defined in lib/db/sqlUtils.ts:594

`Static` countSqlQuery

countSqlQuery(connection: DBAdapter | GeoPackageConnection, sql: string, selectionArgs: any[]): number
Attempt to count the results of the query

Returns
count if known, -1 if not able to determine
Parameters
- connection: DBAdapter | GeoPackageConnection
  
  connection
- sql: string
  
  SQL statement
- selectionArgs: any[]
  
  selection arguments
Returns number
- Defined in lib/db/sqlUtils.ts:610

`Static` createName

createName(db: GeoPackageConnection, name: string, replace: string, replacement: string): string
Create a new name by replacing a case insensitive value with a new value. If no replacement is done, create a new name in the form name_#, where # is either 2 or one greater than an existing name number suffix. When a db connection is provided, check for conflicting SQLite Master names and increment # until an available name is found.

Returns
new name
Parameters
- db: GeoPackageConnection
  
  optional connection, used for SQLite Master name conflict detection
- name: string
  
  current name
- replace: string
  
  value to replace
- replacement: string
  
  replacement value
Returns string
- Defined in lib/db/sqlUtils.ts:487

`Static` createTableSQL

createTableSQL(table: UserTable<UserColumn>): string
Create the user defined table SQL

Returns
create table SQL
Parameters
- table: UserTable<UserColumn>
  
  user table
Returns string
- Defined in lib/db/sqlUtils.ts:38

`Static` delete

delete(connection: GeoPackageConnection, table: string, where: string, args: []): number
Execute a deletion

Returns
deleted count
Parameters
- connection: GeoPackageConnection
  
  connection
- table: string
  
  table name
- where: string
  
  where clause
- args: []
  
  where arguments
Returns number
- Defined in lib/db/sqlUtils.ts:767

`Static` dropTable

dropTable(db: GeoPackageConnection, tableName: string): void
Drop the table if it exists
Parameters
- db: GeoPackageConnection
  
  connection
- tableName: string
  
  table name
Returns void
- Defined in lib/db/sqlUtils.ts:192

`Static` dropTableSQL

dropTableSQL(tableName: string): string
Create the drop table if exists SQL

Returns
drop table SQL
Parameters
- tableName: string
  
  table name
Returns string
- Defined in lib/db/sqlUtils.ts:202

`Static` dropView

dropView(db: GeoPackageConnection, viewName: string): void
Drop the view if it exists
Parameters
- db: GeoPackageConnection
  
  connection
- viewName: string
  
  view name
Returns void
- Defined in lib/db/sqlUtils.ts:211

`Static` dropViewSQL

dropViewSQL(viewName: string): string
Create the drop view if exists SQL

Returns
drop view SQL
Parameters
- viewName: string
  
  view name
Returns string
- Defined in lib/db/sqlUtils.ts:221

`Static` execSQL

execSQL(connection: GeoPackageConnection, sql: string): void
Execute the SQL
Parameters
- connection: GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
Returns void
- Defined in lib/db/sqlUtils.ts:546

`Static` foreignKeyCheck

foreignKeyCheck(db: GeoPackageConnection): any[]
Perform a foreign key check

Returns
empty list if valid or violation errors, 4 column values for each violation. see SQLite PRAGMA foreign_key_check
Parameters
- db: GeoPackageConnection
  
  connection
Returns any[]
- Defined in lib/db/sqlUtils.ts:143

`Static` foreignKeyCheckForTable

foreignKeyCheckForTable(db: GeoPackageConnection, tableName: string): any[]
Perform a foreign key check

Returns
empty list if valid or violation errors, 4 column values for each violation. see SQLite PRAGMA foreign_key_check
Parameters
- db: GeoPackageConnection
  
  connection
- tableName: string
  
  table name
Returns any[]
- Defined in lib/db/sqlUtils.ts:154

`Static` foreignKeyCheckSQL

foreignKeyCheckSQL(tableName: string): string
Create the foreign key check SQL

Returns
foreign key check SQL
Parameters
- tableName: string
  
  table name
Returns string
- Defined in lib/db/sqlUtils.ts:164

`Static` foreignKeys

foreignKeys(db: GeoPackageConnection): boolean
Query for the foreign keys value

Returns
true if enabled, false if disabled
Parameters
- db: GeoPackageConnection
  
  connection
Returns boolean
- Defined in lib/db/sqlUtils.ts:109

`Static` foreignKeysSQL

foreignKeysSQL(on: boolean): string
Create the foreign keys SQL

Returns
foreign keys SQL
Parameters
- on: boolean
  
  true to turn on, false to turn off
Returns string
- Defined in lib/db/sqlUtils.ts:134

`Static` insert

insert(connection: GeoPackageConnection, table: string, values: ContentValues): number
Insert a new row

Returns
row id or -1 on an exception
Parameters
- connection: GeoPackageConnection
  
  connection
- table: string
  
  table name
- values: ContentValues
  
  content values
Returns number
- Defined in lib/db/sqlUtils.ts:823

`Static` insertOrThrow

insertOrThrow(connection: GeoPackageConnection, table: string, values: ContentValues): number
Insert a new row

Returns
row id
Parameters
- connection: GeoPackageConnection
  
  connection
- table: string
  
  table name
- values: ContentValues
  
  content values
Returns number
- Defined in lib/db/sqlUtils.ts:839

`Static` integrityCheckSQL

integrityCheckSQL(): string
Create the integrity check SQL

Returns
integrity check SQL

Returns string
- Defined in lib/db/sqlUtils.ts:175

`Static` modifySQL

modifySQL(db: GeoPackageConnection, name: string, sql: string, tableMapping: TableMapping): string
Modify the SQL with a name change and the table mapping modifications

Returns
updated SQL, null if SQL contains a deleted column
Parameters
- db: GeoPackageConnection
  
  optional connection, used for SQLite Master name conflict detection
- name: string
  
  statement name
- sql: string
  
  SQL statement
- tableMapping: TableMapping
  
  table mapping
Returns string
- Defined in lib/db/sqlUtils.ts:353

`Static` modifySQLWithTableMapping

modifySQLWithTableMapping(sql: string, tableMapping: TableMapping): string
Modify the SQL with table mapping modifications

Returns
updated SQL, null if SQL contains a deleted column
Parameters
- sql: string
  
  SQL statement
- tableMapping: TableMapping
  
  table mapping
Returns string
- Defined in lib/db/sqlUtils.ts:376

`Static` query

query(connection: DBAdapter | GeoPackageConnection, sql: string, selectionArgs: [] | Record<string, any>): ResultSet
Query for results

Returns
result set
Parameters
- connection: DBAdapter | GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
- selectionArgs: [] | Record<string, any>
  
  selection arguments
Returns ResultSet
- Defined in lib/db/sqlUtils.ts:557

`Static` queryResults

queryResults(connection: DBAdapter | GeoPackageConnection, sql: string, args: [] | Record<string, any>, limit: number): any[][]
Query for values up to the limit

Returns
results
Parameters
- connection: DBAdapter | GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
- args: [] | Record<string, any>
  
  arguments
- limit: number
  
  result row limit
Returns any[][]
- Defined in lib/db/sqlUtils.ts:574

`Static` querySingleResult

querySingleResult(connection: GeoPackageConnection, sql: string, args: [] | Record<string, any>, columnName: string): any
Query the SQL for a single result object with the expected data type

Returns
result, null if no result
Parameters
- connection: GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
- args: [] | Record<string, any>
  
  arguments
- columnName: string
  
  column name
Returns any
- Defined in lib/db/sqlUtils.ts:727

`Static` querySingleResultWithColumnIndex

querySingleResultWithColumnIndex(connection: DBAdapter | GeoPackageConnection, sql: string, args: [] | Record<string, any>, columnIdx?: number): any
Query the SQL for a single result object with the expected data type

Returns
result, null if no result
Parameters
- connection: DBAdapter | GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
- args: [] | Record<string, any>
  
  arguments
- columnIdx: number = 0
  
  column index
Returns any
- Defined in lib/db/sqlUtils.ts:747

`Static` quickCheckSQL

quickCheckSQL(): string
Create the quick check SQL

Returns
quick check SQL

Returns string
- Defined in lib/db/sqlUtils.ts:183

`Static` quoteWrap

quoteWrap(str: string): string
Parameters
- str: string
Returns string
- Defined in lib/db/sqlUtils.ts:27

`Static` replaceName

replaceName(sql: string, name: string, replacement: string): string
Replace the name (table, column, etc) in the SQL with the replacement. The name must be surrounded by non word characters (i.e. not a subset of another name).

Returns
null if not modified, SQL value if replaced at least once
Parameters
- sql: string
  
  SQL statement
- name: string
  
  name
- replacement: string
  
  replacement value
Returns string
- Defined in lib/db/sqlUtils.ts:411

`Static` setForeignKeys

setForeignKeys(db: GeoPackageConnection, on: boolean): boolean
Change the foreign keys state

Returns
previous foreign keys value
Parameters
- db: GeoPackageConnection
  
  connection
- on: boolean
  
  true to turn on, false to turn off
Returns boolean
- Defined in lib/db/sqlUtils.ts:120

`Static` tempTableName

tempTableName(db: GeoPackageConnection, prefix: string, baseName: string): string
Get an available temporary table name. Starts with prefix_baseName and then continues with prefix#_baseName starting at 1 and increasing.

Returns
unused table name
Parameters
- db: GeoPackageConnection
  
  connection
- prefix: string
  
  name prefix
- baseName: string
  
  base name
Returns string
- Defined in lib/db/sqlUtils.ts:336

`Static` transferTableContent

transferTableContent(db: GeoPackageConnection, tableName: string, columnName: string, newColumnValue: any, currentColumnValue: any, idColumnName?: string): void
Transfer table content to itself with new rows containing a new column value. All rows containing the current column value are inserted as new rows with the new column value.
Parameters
- db: GeoPackageConnection
  
  connection
- tableName: string
  
  table name
- columnName: string
  
  column name
- newColumnValue: any
  
  new column value for new rows
- currentColumnValue: any
  
  column value for rows to insert as new rows
- Optional idColumnName: string
  
  id column name
Returns void
- Defined in lib/db/sqlUtils.ts:309

`Static` transferTableContentForTableMapping

transferTableContentForTableMapping(db: GeoPackageConnection, tableMapping: TableMapping): void
Transfer table content from one table to another
Parameters
- db: GeoPackageConnection
  
  connection
- tableMapping: TableMapping
  
  table mapping
Returns void
- Defined in lib/db/sqlUtils.ts:230

`Static` transferTableContentSQL

transferTableContentSQL(tableMapping: TableMapping): string
Create insert SQL to transfer table content from one table to another

Returns
transfer SQL
Parameters
- tableMapping: TableMapping
  
  table mapping
Returns string
- Defined in lib/db/sqlUtils.ts:240

`Static` update

update(connection: GeoPackageConnection, table: string, values: ContentValues, whereClause: string, whereArgs: DBValue[]): number
Update table rows

Returns
updated count
Parameters
- connection: GeoPackageConnection
  
  connection
- table: string
  
  table name
- values: ContentValues
  
  content values
- whereClause: string
  
  where clause
- whereArgs: DBValue[]
  
  where arguments
Returns number
- Defined in lib/db/sqlUtils.ts:780

`Static` vacuum

vacuum(db: GeoPackageConnection): void
Rebuild the GeoPackage, repacking it into a minimal amount of disk space
Parameters
- db: GeoPackageConnection
  
  connection
Returns void
- Defined in lib/db/sqlUtils.ts:534

`Static` wrapQuery

wrapQuery(connection: DBAdapter | GeoPackageConnection, sql: string, selectionArgs: [] | Record<string, any>): ResultSetResult
Perform the query and wrap as a result

Returns
result
Parameters
- connection: DBAdapter | GeoPackageConnection
  
  connection
- sql: string
  
  sql statement
- selectionArgs: [] | Record<string, any>
  
  selection arguments
Returns ResultSetResult
- Defined in lib/db/sqlUtils.ts:711

Class SQLUtils

Hierarchy

Index

Constructors

Properties

Methods

Constructors

constructor

Returns SQLUtils

Properties

Static NUMBER_PATTERN

Methods

Static columnDefaultValue

Returns

Parameters

defaultValue: any

dataType: GeoPackageDataType

Returns string

Static columnDefinition

Returns

Parameters

column: UserColumn

Returns string

Static columnSQL

Returns

Parameters

column: UserColumn

Returns string

Static count

Returns

Parameters

connection: GeoPackageConnection

tableName: string

where: string

whereArgs: [] | Record<string, any>

Returns number

Static countSqlQuery

Returns

Parameters

connection: DBAdapter | GeoPackageConnection

sql: string

selectionArgs: any[]

Returns number

Static createName

Returns

Parameters

db: GeoPackageConnection

name: string

replace: string

replacement: string

Returns string

Static createTableSQL

Returns

Parameters

table: UserTable<UserColumn>

Returns string

Static delete

Returns

Parameters

connection: GeoPackageConnection

table: string

where: string

args: []

Returns number

Static dropTable

Parameters

db: GeoPackageConnection

tableName: string

Returns void

Static dropTableSQL

Returns

Parameters

tableName: string

Returns string

Static dropView

Parameters

db: GeoPackageConnection

viewName: string

Returns void

Static dropViewSQL

`Static` NUMBER_PATTERN

`Static` columnDefaultValue

`Static` columnDefinition

`Static` columnSQL

`Static` count

`Static` countSqlQuery

`Static` createName

`Static` createTableSQL

`Static` delete

`Static` dropTable

`Static` dropTableSQL

`Static` dropView

`Static` dropViewSQL

`Static` execSQL

`Static` foreignKeyCheck

`Static` foreignKeyCheckForTable

`Static` foreignKeyCheckSQL

`Static` foreignKeys

`Static` foreignKeysSQL

`Static` insert

`Static` insertOrThrow

`Static` integrityCheckSQL

`Static` modifySQL

`Static` modifySQLWithTableMapping

`Static` query