multiple primary keys

[softlion]

In CreateTable, replace the code creating the string given to the Execute method by

        var sbQuery = new StringBuilder("create table if not exists \"").Append(map.TableName).Append("\"(\n");
        map.Columns.Aggregate(sbQuery, (sb, column) => sb.Append(Orm.SqlDecl(column, StoreDateTimeAsTicks)).Append(",\n"));

        var pks = (from c in map.Columns where c.IsPK select c).ToList();
        if (pks.Count != 0)
        {
            //, PRIMARY KEY (A_ID, B_ID)
            sbQuery.Append("primary key (");
            pks.Aggregate(sbQuery, (sb, c) => sb.Append(c.Name).Append(','));
            sbQuery.Remove(sbQuery.Length - 1, 1).Append(')');
        }
        else
        {
            sbQuery.Remove(sbQuery.Length - 3, 3);
        }

        sbQuery.Append(")");
        var count = Execute(sbQuery.ToString());

And in the SqlDecl method, remove the line

decl += "primary key ";

Also in MigrateTable add a not supported exception:

        foreach (var p in toBeAdded)
        {
            if (p.IsPK)
                throw new Exception("New columns can not be primary keys (unsupported migration)");

            var addCol = "alter table \"" + map.TableName + "\" add column " + Orm.SqlDecl(p, StoreDateTimeAsTicks);
            Execute(addCol);
        }

[OzTK]

This breaks the AutoIncrement attribute. Any solution?

[OzTK]

Found a solution. Modify the if block in your first piece of code to pk number greater than 1:

if (pks.Count > 1)
{
    //, PRIMARY KEY (A_ID, B_ID)
    sbQuery.Append("primary key (");
    pks.Aggregate(sbQuery, (sb, c) => sb.Append(c.Name).Append(','));
    sbQuery.Remove(sbQuery.Length - 1, 1).Append(')');
}

For the SqlSecl method join the 2 first "if" like this:

if (p.IsPK && p.IsAutoInc)
{
     decl += "primary key autoincrement ";
}

[softlion]

This should not work as autoincrement can be set on any columns.

Instead try adding a space in the Append on the first line, and change 3 into 2 on the last line.

        var sbQuery = new StringBuilder("create table if not exists \"").Append(map.TableName).Append("\"( \n");
        map.Columns.Aggregate(sbQuery, (sb, column) => sb.Append(Orm.SqlDecl(column, StoreDateTimeAsTicks)).Append(",\n"));

        var pks = (from c in map.Columns where c.IsPK select c).ToList();
        if (pks.Count != 0)
        {
            //, PRIMARY KEY (A_ID, B_ID)
            sbQuery.Append("primary key (");
            pks.Aggregate(sbQuery, (sb, c) => sb.Append(c.Name).Append(','));
            sbQuery.Remove(sbQuery.Length - 1, 1).Append(')');
        }
        else
        {
            sbQuery.Remove(sbQuery.Length - 2, 2);
        }

[softlion]

Btw:

    public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks)
    {
        return String.Format("\"{0}\" {1} {2} {3} {4} ",
            p.Name,
            SqlType(p, storeDateTimeAsTicks),
            p.IsAutoInc ? "autoincrement" : null,
            !p.IsNullable ? "not null" : null,
            !String.IsNullOrEmpty(p.Collation) ? "collate " + p.Collation : null
            );
    }

[OzTK]

Great! Thanks for your solution :) I noticed the impossibility of using with multiple autoincrement but i was wondering if someone would do that... that's why I was lazy.
Anyway thank you for your help.

[asgerhallas]

Any plans to merge this?

[softlion]

I've modified a bit further the library so it supports new c# types like XElement, but i need 2-4 hours of brain to understand the github concept and tools, without breaking my TFS. My hourly rate is 130$, so i'll try when i have free brain.

[softlion]

Btw, you should look at https://github.com/oysteinkrog/sqlite-net/ fork.
He worked on refactoring and putting all code into a PCL library (cross platform android ios wp7).
Very good work !

[ignacy130]

I'm getting error: "near autoincrement syntax error"? Isn't it because sqlite does not support just "autoincrement" attribute?

Autoincrement can be set on any columns, but there are restrictions with primary key: https://code.google.com/p/wwwsqldesigner/issues/detail?id=90

[sorech]

What is the latest state of affairs on this? Does anyone have code known to work with the latest https://github.com/oysteinkrog/SQLite.Net-PCL fork? Øystein et al has done some nice work but lacking support for compound natural primary keys really cripples a relational database. The workarounds are so ugly and allows for errors.

[softlion]

Look at my fork, it is fixed in it. Works perfectly.

https://github.com/softlion/SQLite.Net-PCL

[gsaldana]

how can I get this version from nuget???

[praeclarum]

Working on this in #280