Re: Formatting Embedded SQL

Rich Shepard wrote:
>    It seems to me that when a large CREATE TABLE, SELECT, or similar command
> is embedded in a python statement with line breaks for readability, that
> either pysqlite or squite3 becomes disoriented by the extra spaces. It then
> complains and refuses to play nice. Is this a correct assumption?

Whitespace should not be an issue.

>    For example, I have a statement that is written thusly;
> 
>   """ Rule tokens after parsing and before compiling """
>      self.cur.execute("CREATE TABLE rule_token (rule_num INTEGER, token_num INTEGER, \
>                                                  token_name TEXT, \
>                                                  token_len INTEGER, \
>                                                  token_type TEXT, \
>                                                  token_sym_value TEXT, \
>                                                  primary key (rule_num,
>  						token_num));")

In such a situation its better to use triple quotes.  I would usually do 
something like:

    stmt = """CREATE TABLE rule_token (
                   rule_num INTEGER,
                   token_num INTEGER,
                   token_name TEXT,
                   token_len INTEGER,
                   token_type TEXT,
                   token_sym_value TEXT,
                   primary key (rule_num, token_num) );"""
    self.cur.execute(stmt)

If you do alot of initialization when you create your database, you can 
also put the SQL in an external file and execute it as such:

    sql = open("database_init.sql", "r").read()
    conn.executescript(sql)

executescript() is nonstandard, but convenient. Your database_init.sql 
would look like:

    -- database_init.sql

    -- SQL comment here
    CREATE TABLE rule_token
        (
            rule_num INTEGER,
            token_num INTEGER,
            token_name TEXT,
            token_len INTEGER,
            token_type TEXT,
            token_sym_value TEXT,
            primary key (rule_num, token_num)
        );

     ....


>    Looks pretty and is very readable. However, when I ask sqlite3 for the
> schema, this is what it shows me:
> 
> sqlite> .schema rule_token 
> CREATE TABLE rule_token (rule_num INTEGER, token_num INTEGER,
> token_name TEXT,                                                 token_len
> INTEGER,                                                 token_type TEXT,
> token_sym_value TEXT,
> primary key (rule_num, token_num));
> CREATE TRIGGER fki_rule_token                       BEFORE INSERT ON
> rule_token                       FOR EACH ROW                       BEGIN
> SELECT CASE                         WHEN NEW.token_num IS NOT NULL AND
> (SELECT rule_num FROM rules WHERE                               token_nuk =
> NEW.rule_num) IS NULL                         THEN RAISE (ROLLBACK, 'insert
> on table rules                               violates foreign key constraint
> fk_rule_tok')                         END;                       END;
> CREATE TRIGGER fku_rule_token                       BEFORE UPDATE ON
> rule_token                       FOR EACH ROW                         BEGIN
> SELECT CASE                           WHEN NEW.token_num IS NOT NULL AND
> (SELECT rule_num FROM rules WHERE                             token_num =
> NEW.rule_num) IS NULL                           THEN RAISE(ROLLBACK, 'update
> on table rule_token                           violates foreign key
> constraint fk_rule_tok')                         END;
> END;
> 
>    I've not yet tried working with this table, but I wonder if the additional
> spaces are an issue.
> 
> Thanks,
> 
> Rich
>