public bool TryGetPrimaryKey(uint relationId, out PrimaryKeyData primaryKey)
 {
     if (!primaryKeyPerRelation.ContainsKey(relationId))
     {
         using (var scope = new DatabaseScope(databaseName))
         {
             var            relation        = relationsRepository.Get(relationId);
             PrimaryKeyData primaryKeyToAdd = null;
             if (relation != null)
             {
                 List <AttributeData> primaryKeyAttributes = new List <AttributeData>();
                 if (relation.PrimaryKeyAttributeNames != null)
                 {
                     foreach (var attributeName in relation.PrimaryKeyAttributeNames)
                     {
                         primaryKeyAttributes.Add(new AttributeData(GetRelation(relationId), attributesRepository.Get(relationId, attributeName)));
                     }
                 }
                 if (primaryKeyAttributes.Count > 0)
                 {
                     primaryKeyToAdd = new PrimaryKeyData(primaryKeyAttributes);
                 }
             }
             primaryKeyPerRelation.Add(relationId, primaryKeyToAdd);
         }
     }
     primaryKey = primaryKeyPerRelation[relationId];
     return(primaryKey != null);
 }
Exemple #2
0
 public HPartitioningAttributeDefinition DesignFor(AttributeData attribute, ISet <string> operators, PrimaryKeyData relationPrimaryKey)
 {
     // if relation has primary key, attribute must be part of it
     if (relationPrimaryKey == null || relationPrimaryKey.Attributes.Select(x => x.Name).ToHashSet().Contains(attribute.Name))
     {
         // range partitioning only for NotNull numeric/datetime attributes
         if (IsTypeSuitableForRange(attribute.IsNullable, attribute.DbType))
         {
             switch (attribute.DbType)
             {
             case DbType.Byte:
             case DbType.Decimal:
             case DbType.Double:
             case DbType.Int16:
             case DbType.Int32:
             case DbType.Int64:
             case DbType.SByte:
             case DbType.Single:
             case DbType.UInt16:
             case DbType.UInt32:
             case DbType.UInt64:
             case DbType.VarNumeric:
             case DbType.Time:
             case DbType.Date:
             case DbType.DateTime:
             case DbType.DateTime2:
             case DbType.DateTimeOffset:
                 return(DesignRange(attribute));
             }
         }
         // hash partitioning only for attributes where user never applied comparison operators
         else if (supportsHashPartitioning && comparableOperators.Intersect(operators).Count() == 0) // hash
         {
             switch (attribute.DbType)
             {
             case DbType.AnsiString:
             case DbType.AnsiStringFixedLength:
             case DbType.Boolean:
             case DbType.Byte:
             case DbType.Currency:
             case DbType.Date:
             case DbType.DateTime:
             case DbType.DateTime2:
             case DbType.DateTimeOffset:
             case DbType.Decimal:
             case DbType.Double:
             case DbType.Guid:
             case DbType.Int16:
             case DbType.Int32:
             case DbType.Int64:
             case DbType.SByte:
             case DbType.Single:
             case DbType.String:
             case DbType.StringFixedLength:
             case DbType.Time:
             case DbType.UInt16:
             case DbType.UInt32:
             case DbType.UInt64:
             case DbType.VarNumeric:
                 return(DesignHash(attribute));
             }
         }
     }
     return(null);
 }
Exemple #3
0
        /// <summary>
        /// Conflict handler, allows multiple methods of conflict resolution.
        /// </summary>
        /// <param name="ctx">information about how to handler conflicts per table</param>
        /// <param name="eConflict">reason for callback</param>
        /// <param name="pIter">conflict change set, may iterate through values</param>
        /// <returns>how to respond to conflicting value, leave unchanged, replace, or abort</returns>
        public static ConflictResolution CallbackConflictHandler(IntPtr pCtx, ConflictReason eConflict, IntPtr pIter)
        {
            try
            {
                // get our context
                object ctx = null;
                if (pCtx != IntPtr.Zero)
                {
                    System.Runtime.InteropServices.GCHandle gch = System.Runtime.InteropServices.GCHandle.FromIntPtr(pCtx);
                    ctx = gch.Target;
                }
                var dbSync       = ctx as DBSync;
                var dbConnection = dbSync.db;

                // Note: pIter should be converted to a Sqlite3ChangesetIterator to allow using ChangeSetOp,ChangeSet*Value methods
                // however must actually create Sqlite3ConflictChangesetIterator to avoid unnecessary finalize of iterator
                var iter = new Sqlite3ConflictChangesetIterator(pIter);

                // get table, column, and other information about this change
                ChangeSetOp(iter, out string tableName, out int columnCount, out ActionCode op, out bool indirect);

                // get information about columns in current table
                var    tableInfo = dbSync.tableInformation[tableName];
                object currentValue, oldValue, newValue;

                // get primary key value for current row - NOTE: we support a single or multiple column PK
                var pkValues = SQLiteSession.ChangeSetPrimaryKeyValues(iter);
                if (pkValues.Count < 1)
                {
                    // we require a primary key and failed to get it!
                    return(ConflictResolution.SQLITE_CHANGESET_ABORT);
                }
                PrimaryKeyData[] pk = new PrimaryKeyData[pkValues.Count];
                for (var i = 0; i < pkValues.Count; i++)
                {
                    // get primary key column's value
                    pk[i].value = pkValues[i].Item1.GetValue();
                    // get primary key column's name from index for table
                    pk[i].name = tableInfo[pkValues[i].Item2].Name;
                }

                // log some debug information about conflict (data to sync)
                {
                    var sb = new StringBuilder($"Conflict ({eConflict}) in table '{tableName}' for pk='{pk}', op={op}, Row values:"); sb.AppendLine();
                    for (int i = 0; i < tableInfo.Count; i++)
                    {
                        GetConflictValues(ref iter, op, i, out currentValue, out oldValue, out newValue);
                        sb.AppendLine($"[{i}] current={currentValue}, old={oldValue}, new={newValue}");
                    }
                    logger.Debug(sb.ToString());
                }

                // TODO handle constraint violations better
                if ((eConflict == ConflictReason.SQLITE_CHANGESET_CONSTRAINT) || (eConflict == ConflictReason.SQLITE_CHANGESET_FOREIGN_KEY))
                {
                    return(ConflictResolution.SQLITE_CHANGESET_ABORT);
                }

                // if op==insert then in current db and not in replica
                // so is this a new record (keep) or a deleted record in replica (do we want to replicate delete?)
                // newValue (and currentValue) has current's value including timestamp if available
                // oldValue is null, need to query replica's shadow table to see if deleted (and timestamp) or if
                // not found then assume a new row (so keep)
                //
                // if op==delete then in replica db and not in current
                // so is this a new record (insert) or a deleted record in current (do we want keep delete?)
                // oldValue has the replica's value including timestamp if available
                // newValue (and currentValue) are null, need to query current's shadow table to see if deleted (and timestamp) or if
                // not found then assume a new row (so insert)
                //
                // if op==update then which do we keep, current or replica => we can do this per column or per row
                // oldValue and newValue are null then column has no conflict (currentValue has column's value)
                // oldValue is replica's value, newValue (and currentValue) is current's value
                //
                // TODO - how to handle constraint and foreign key violations, structuring order of replicating tables
                // should avoid these in most cases, for now we punt and abort!


                // get timestamps from current and replica row, if have a timestamp (-1 for column if not one)
                int      timestampColumn = FindColumn(tableInfo, "timestamp");
                DateTime?currentTimestamp = null, replicaTimestamp = null;
                if (timestampColumn >= 0)
                {
                    GetConflictValues(ref iter, op, timestampColumn, out currentValue, out oldValue, out newValue);

                    if (op == ActionCode.SQLITE_UPDATE) // both are in conflict values
                    {
                        currentTimestamp = currentValue as DateTime?;
                        replicaTimestamp = (oldValue as DateTime?) ?? currentTimestamp; // no oldValue indicates identical values
                    }
                    else // must query a shadow table
                    {
                        DateTime?shadowTimestamp = null;
                        var      query           = new StringBuilder($"SELECT [del_timestamp] FROM ");
                        query.Append((op == ActionCode.SQLITE_INSERT) ? "replica" : "main");
                        query.Append(".");
                        query.Append(tableName);
                        query.Append("_ WHERE ");  // shadow table has _ suffix, e.g. [MyTable] shadow table is [MyTable_]
                        var keys = new List <object>(pk.Length);
                        for (var i = 0; i < pk.Length; i++)
                        {
                            if (i != 0)
                            {
                                query.Append(" AND ");
                            }
                            query.Append(pk[i].name);
                            query.Append("=?");

                            keys.Add(pk[i].value);
                        }
                        query.Append(";");
                        try
                        {
                            // if no shadow table or not a deleted row then this will throw since doesn't exist
                            shadowTimestamp = dbConnection.ExecuteScalar <DateTime>(query.ToString(), keys.ToArray());
                        }
                        catch (Exception) { /* swallow error if no shadow record */ }

                        if (op == ActionCode.SQLITE_INSERT)
                        {
                            if (currentValue != null)
                            {
                                currentTimestamp = new DateTime((long)currentValue);
                            }
                            replicaTimestamp = shadowTimestamp;
                        }
                        else
                        {
                            currentTimestamp = shadowTimestamp;
                            if (oldValue != null)
                            {
                                replicaTimestamp = new DateTime((long)oldValue);
                            }
                        }
                    }
                }


                // Note: the way we are using Session, we explicitly make the changes
                // in all cases except on Update where we currently keep or replace whole row
                // TODO allow selection of individual fields? which would require us to manually
                // do the update - hence also returning SQLITE_CHANGESET_OMIT
                var action = ConflictResolution.SQLITE_CHANGESET_OMIT;
                if (op == ActionCode.SQLITE_INSERT)
                {
                    // keep current value unless replicaTimestamp is newer
                    if ((currentTimestamp != null) && (replicaTimestamp != null) && (currentTimestamp < replicaTimestamp))
                    {
                        // TODO - we need to actually issue a DELETE!
                        // TODO - then update shadow table so del_timestamp matches replica
                    }
                }
                else if (op == ActionCode.SQLITE_DELETE)
                {
                    // indicate nothing to do, as we have to manually insert if desired to add
                    action = ConflictResolution.SQLITE_CHANGESET_OMIT;

                    // are we a new row only in replica? or was modified in replica after we deleted so re-add modified value?
                    if ((currentTimestamp == null) ||
                        ((replicaTimestamp != null) && (currentTimestamp < replicaTimestamp)))
                    {
                        // then insert it
                        {
                            var sb = new StringBuilder("INSERT INTO ");
                            sb.Append(tableName);
                            sb.Append(" (");
                            for (var i = 0; i < tableInfo.Count; i++)
                            {
                                if (i != 0)
                                {
                                    sb.Append(", ");
                                }
                                sb.Append("[");               // double quoted "column names" are standard, but [name] works and easier to read
                                sb.Append(tableInfo[i].Name); // if any chance of user driven names then use " and escape internal "s
                                sb.Append("]");
                            }
                            sb.Append(") VALUES (");
                            var values = new object[tableInfo.Count];
                            for (var i = 0; i < tableInfo.Count; i++)
                            {
                                if (i != 0)
                                {
                                    sb.Append(", ");
                                }
                                sb.Append("?");

                                oldValue = null;
                                if (ChangeSetOldValue(iter, i, out SQLiteNetSessionModule.SQLiteValue value) == SQLite3.Result.OK)
                                {
                                    if (value.HasValue())
                                    {
                                        oldValue = value.GetValue();
                                    }
                                }
                                values[i] = oldValue;
                            }
                            sb.Append(");");
                            logger.Debug("Inserting: '{0}' with values={1}", sb.ToString(), statePrinter.PrintObject(values));
                            dbConnection.Execute(sb.ToString(), values);
                        }
                    }
                    // else assume we deleted on purpose so leave that way
                }
                else if (op == ActionCode.SQLITE_UPDATE)
                {
                    // TODO allow more fine grained selection - for now simply keep latest change
                    if ((currentTimestamp == null) || (replicaTimestamp == null) || (currentTimestamp >= replicaTimestamp))
                    {
                        action = ConflictResolution.SQLITE_CHANGESET_OMIT;
                    }
                    else
                    {
                        action = ConflictResolution.SQLITE_CHANGESET_REPLACE;
                    }
                }
                else
                {
                    logger.Error($"Unknown or unexpected op {op} - aborting!");
                }


                // replace only valid on conflict or data
                //if ((eConflict == ConflictReason.SQLITE_CHANGESET_CONFLICT) || (eConflict == ConflictReason.SQLITE_CHANGESET_DATA))
                logger.Debug($"returning action={action}\r\n");
                return(action);
            }
            catch (Exception e)
            {
                logger.Error(e, "Unexpected error during conflict handler callback.");
                return(ConflictResolution.SQLITE_CHANGESET_ABORT);
            }
        }