ALTER TABLE for ADO.NET sqlite for xamarin android

I need to add new columns in my sqlite table. i have already read about ALTER TABLE and PRAGMA table_info, but as an indie (self-taught) developer, i do not know how to implement it. here is the code in my database class (which i got from Tasky sample app from xamarin):

public class YesDatabase
{
    static object locker = new object ();

    public SqliteConnection connection;

    public string path; 

    public YesDatabase (string dbPath)
    {
        var output = "";
        path = dbPath;
        // create the tables
        bool exists = File.Exists (dbPath);

        if (!exists) 
        {
            connection = new SqliteConnection ("Data Source=" + dbPath);

            connection.Open ();

            var ccommandsc = new[] {
                "CREATE TABLE IF NOT EXISTS [Citems] (_id INTEGER PRIMARY KEY ASC, Cname NTEXT, Cnotes NTEXT, Ccat NTEXT);"
            };
            foreach (var commandc in ccommandsc) {
                using (var d = connection.CreateCommand ()) {
                    d.CommandText = commandc;
                    var j = d.ExecuteNonQuery ();
                }
            }


        } else 
        {
            // already exists, do nothing. 
        }
        Console.WriteLine (output);
    }


            Contact FromReaderc (SqliteDataReader r) {
        var c = new Contact ();
        c.ID = Convert.ToInt32 (r ["_id"]);
        c.Cname = r ["Cname"].ToString ();
        c.Cnotes = r ["Cnotes"].ToString ();
        c.Ccat = r ["Ccat"].ToString ();
        return c;  

}

1 answer

  • answered 2018-04-17 06:03 MikeT

    You'd use

    ALTER TABLE your_table ADD COLUMN you_column_definition
    

    So it could be

    ALTER TABLE [Citems] ADD COLUMN Cother REAL NOT NULL DEFAULT 10.12345
    
    • [Citems] replaces your_table
    • Cother is the column name
    • Column type would be REAL
    • constraints would be NOT NULL.
    • the default value for the column would be 10.12345

    Invoking the above (amended of course to suit) would be executed in place of // already exists, do nothing.

    Note that there are restrictions as to the columns that can be added :-

    The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

    • The column may not have a PRIMARY KEY or UNIQUE constraint.
    • The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
    • If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
    • If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

    Note also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.

    SQL As Understood By SQLite - ALTER TABLE

    I have read that the command PRAGMA table_info is needed to prevent errors in adding that column again when that app is re-opened

    That's not really true you could check that the column exists by querying the sqlite_master table, which includes a column named sql that is the SQL needed to create the table.

    For example (assuming the column to be checked is Cother ) then :-

    SELECT instr(sql,' Cother ') > 0 AS result FROM sqlite_master WHERE name = 'mytable';
    

    will return 1 row if the table exists with 0 if the column doesn't exist or with 1 if the column does exist.

    If the SQLite version is less then 3.7.15 and therefore the instr core function doesn't exist

    Then the following could be used:-

     SELECT 1 FROM sqlite_master WHERE name = 'mytable' AND sql LIKE '% Cother %'
    

    This would return a row if the column does exist or no rows if the column doesn't exist.

    You'd use PRAGMA table_info([Citems]) to return rows with columns :-

    • cid
    • name
    • type
    • notnull
    • dflt_value
    • pk

    You'd then have to traverse the rows checking if the name columns contains the column, so is a little more complex to utilise.

    and the part where ALTER TABLE command can be written

    As previously stated you'd do this if the database exists and therefore to replace the line :-

    // already exists, do nothing.