Skip to content

SQL syntax incorrectly capitalized "SQL" when it's a lowercase column name #16

@gfairchild

Description

@gfairchild

http://www.gfairchild.com/2012/08/03/how-to-rename-columns-in-an-sqlite-database/ shows the example in practice. Here's the chunk of code to be highlighted:

BEGIN; --start a transaction; this ensures that your changes won't be written until you COMMIT
.schema --this will show you your current schema
PRAGMA writable_schema=1; --this enables you to edit the schema
SELECT * FROM sqlite_master; --show the raw data SQLite saves related to your schema
UPDATE sqlite_master SET sql=replace(sql, 'county', 'county_id') WHERE name='people'; --simple string replace
.schema --verify CREATE TABLE is correct
UPDATE sqlite_master SET sql='CREATE INDEX people_county_id_index on people(county_id)' WHERE name='people_county_index'; --update index SQL
UPDATE sqlite_master SET name='people_county_id_index' WHERE name='people_county_index'; --update index name
.schema --verify index is correct
SELECT * FROM sqlite_master; --double-check all raw data once more
PRAGMA writable_schema=0; --disable editing the schema
COMMIT; --save the changes to the database
.exit

The important thing to note here is what's on line 5:

UPDATE sqlite_master SET sql=replace(sql, 'county', 'county_id') WHERE name='people'; --simple string replace

Here's the output WP-Syntax creates:

BEGIN; --start a transaction; this ensures that your changes won't be written until you COMMIT
.schema --this will show you your current schema
PRAGMA writable_schema=1; --this enables you to edit the schema
SELECT * FROM sqlite_master; --show the raw data SQLite saves related to your schema
UPDATE sqlite_master SET SQL=REPLACE(SQL, 'county', 'county_id') WHERE name='people'; --simple string replace
.schema --verify CREATE TABLE is correct
UPDATE sqlite_master SET SQL='CREATE INDEX people_county_id_index on people(county_id)' WHERE name='people_county_index'; --update index SQL
UPDATE sqlite_master SET name='people_county_id_index' WHERE name='people_county_index'; --update index name
.schema --verify index is correct
SELECT * FROM sqlite_master; --double-check all raw data once more
PRAGMA writable_schema=0; --disable editing the schema
COMMIT; --save the changes to the database
.exit

Notice how in line 5, all instances of sql are now capitalized. Here, sql is a lowercase column name and should remain lowercase when WP-Syntax renders it.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions