Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Added a new SQL parser for migrations. #215

Merged
merged 7 commits into from
Apr 18, 2014

Conversation

markuspfeiffer
Copy link

This pull request adds a new option for the SQL script parser used during database migration.

This parser supports both SQL style line comments and comment blocks. SQL statements can span multiple lines but must be delimited with a semicolon. This allows for improved readability of SQL scripts.

The old parser expects one statement per line which may or may not end with a semicolon. This means the new parser can also process scripts written for the old parser if those scripts end each line with a semicolon.

For backwards compatibility reasons this parser isn't used unless the manifest contains a meta-data entry AA_SQL_PARSER with the value delimited. If this value isn't specified or is set to legacy the old parser implementation is used.

Example:
Add the following entry to the AndroidManifest.xml:

        <meta-data
            android:name="AA_SQL_PARSER"
            android:value="delimited" 

It is now perfectly legal to write a migration script (e.g. 2.sql) that looks like this:

-- Create table for migration
CREATE TABLE Entity2
(
    Id INTEGER AUTO_INCREMENT PRIMARY KEY,
    Column TEXT NOT NULL,
    Column2 INTEGER NULL /* this column is new */
);

-- Migrate data
INSERT INTO Entity2
(
    Id,
    Column,
    Column2
)
SELECT  Id,
        Column,
        0 -- there's no such value in the old table
        FROM Entity;

-- Rename Entity2 to Entity
DROP TABLE Entity;
ALTER TABLE Entity2 RENAME TO Entity;

The same script, using the "legacy" parser, must look like this:

CREATE TABLE Entity2 (Id INTEGER AUTO_INCREMENT PRIMARY KEY, Column TEXT NOT NULL, Column2 INTEGER NULL);
INSERT INTO Entity2 (Id, Column, Column2) SELECT Id, Column, 0 FROM Entity;
DROP TABLE Entity;
ALTER TABLE Entity2 RENAME TO Entity;

Sure, its more compact but far less readable. Imagine doing the same with a table containing 20 or more columns... :)

…ents and comment blocks. SQL statements can span multiple lines but must be delimited with a semicolon. This allows for improved readability of SQL scripts.

The old parser expects one statement per line which may or may not end with a semicolon. This means the new parser can also process scripts written for the old parser if those scripts end each line with a semicolon.

For backwards compatibility reasons this parser isn't used unless the manifest contains a meta-data entry "AA_SQL_PARSER" with the value "delimited". If this value isn't specified or is set to "legacy" the old parser implementation is used.
@joshuapinter
Copy link
Contributor

Much more readable formatting. Can you write a simple test for this SQL parser and ensures the old one is used by default?

@markuspfeiffer
Copy link
Author

Sure, I'll look into writing tests for this and my other pull request. The old parser should be default already but I'll try to cover that with a test as well.

@markuspfeiffer
Copy link
Author

All right, that's all the tests I can think of. Tell me if you still find anything missing.

@joshuapinter
Copy link
Contributor

@markuspfeiffer, this is excellent work. Just waiting for @SeanPONeil to merge this in. I'll take a look at the other commits as well.

@pardom-zz
Copy link
Owner

Nice commit.

pardom-zz pushed a commit that referenced this pull request Apr 18, 2014
Added a new SQL parser for migrations.
@pardom-zz pardom-zz merged commit 7b2ed13 into pardom-zz:master Apr 18, 2014
@nobre84
Copy link

nobre84 commented Oct 7, 2014

Hi, should this commit support a script with some insert text that happens to contain a comma character ?

@markuspfeiffer
Copy link
Author

Yes, it should. Take a look at the test cases, they cover this case and a lot more. If you get errors with your script make sure you have the following line in your manifest:

<meta-data
            android:name="AA_SQL_PARSER"
            android:value="delimited" />

Without this line the old parser is used which doesn't support multi-line scripts, comments, etc.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

5 participants