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

Question

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

CL. · Accepted Answer · 2017-10-18 07:01:13Z

1

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.

edited Oct 18, 2017 at 7:01

answered Oct 17, 2017 at 17:08

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Avba Over a year ago

I agree but since I am not using a TCL script directly but rather a SQLITE shell script not sure how to add those functions. i.e. a SQL "command" file. I'll update my question my to reflect that

Johannes Kuhn Over a year ago

Your best bet would be probably to implement the sqlite3 shell in Tcl.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related