CODEWITHSUNDEEP
sqlitetcl
Avba
Avba

Reputation: 15266

Is it possible to add sqlite scripts in sqlite console without overhead of creating c/c++ loadable extension

Curious to know if it is possible in a reasonable manner to "avoid" creating a loadable c sqlite extension and directly create a "script" inside the console to do some transformations for me . For example sqlite documentation has a site devoted to some core functions https://www.sqlite.org/lang_corefunc.html like "ABS" or "random" but it would be convenient to create client side scripts in TCL / JavaScript or what ever language to do other manipulations. For instance:

# User script
proc reversedResult(result) {
  return [string reverse $a]
}


select reversedResult(db.name) from users where id=1

EDIT:

The data base is loaded directly from a CSV file using a SQLite shell script as follows:

-- created at 2017-10-17 10:43:42.430129
-- script path /tmp/popDb/cmd1508226222.txt

CREATE TABLE 'hash_gen' ( 
                        'key' TEXT PRIMARY KEY NOT NULL,
                        'hash' INT NOT NULL <<<<<< HERE I WANT TO USE A HASHING FUNCTION
                    );
CREATE INDEX hash_gen_index ON hash_gen (hash);

CREATE TABLE 'info' (
                        key TEXT PRIMARY KEY NOT NULL,
                        value   TEXT NOT NULL
                    );

INSERT INTO 'info' VALUES('primary_table','products');

INSERT INTO 'info' VALUES('primary_key','sku');

INSERT INTO 'info' VALUES('db_version','1.0.0');

CREATE TABLE products (
sku TEXT PRIMARY KEY NOT NULL,
...many more columns...
);
CREATE TRIGGER hash_gen_trigger
    AFTER INSERT
    ON 'products'
    BEGIN
        INSERT INTO hash_gen(key,hash) VALUES(new.sku,MY_CUSTOM_HASH_FUNCTION(new.sku,...all other fields,....));
END;
.mode csv
.import /tmp/inputCSV/1503331189113.csv products
....

<<<< MY_CUSTOM_HASH_FUNCTION(input) {return custom_hash_on_(input)...}

and then finally the db is created as part of a separate flow (in a different tool in a different programming language (scala)):

sqlite3 /output/path/db.db < /tmp/popDb/cmd1508226222.txt

Upvotes: 0

Views: 92

Answers (1)

CL.
CL.

Reputation: 180060

The SQLite drivers in most scripting languages (except JavaScript) allow to create user-defined functions.

For example, the SQLite Tcl documentation says:

The "function" method registers new SQL functions with the SQLite engine. The arguments are the name of the new SQL function and a TCL command that implements that function. Arguments to the function are appended to the TCL command before it is invoked.

The following example creates a new SQL function named "hex" that converts its numeric argument in to a hexadecimal encoded string:

db function hex {format 0x%X}

However, the sqlite3 command-line shell itself does not use any scripting language; there is no mechanism to add functions to it, except by loading a module.

Upvotes: 1

Related Questions