Esempio n. 1
0
        public static string SqlType(TableMapping.Column p)
        {
            var clrType = p.ColumnType;

            if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
            {
                return("integer");
            }
            if (clrType == typeof(UInt32) || clrType == typeof(Int64))
            {
                return("bigint");
            }
            if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
            {
                return("float");
            }
            if (clrType == typeof(String))
            {
                int len = p.MaxStringLength;
                return("varchar(" + len + ")");
            }
            if (clrType == typeof(DateTimeOffset))
            {
                return("datetime");
            }
            if (clrType == typeof(DateTime))
            {
                return("datetime");

#if !NETFX_CORE
            }
            if (clrType.IsEnum)
            {
#else
            }
            if (clrType.GetTypeInfo().IsEnum)
            {
#endif
                return("integer");
            }
            if (clrType == typeof(TimeSpan))
            {
                return("bigint");
            }
            if (clrType == typeof(byte[]))
            {
                return("blob");
            }
            if (clrType == typeof(Guid))
            {
                return("varchar(36)");
            }
            throw new NotSupportedException("Don't know about " + clrType);
        }
Esempio n. 2
0
        public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks)
        {
            string decl = "\"" + p.Name + "\" " + SqlType(p) + " ";

            if (p.IsPK)
            {
                decl += "primary key ";
            }
            if (p.IsAutoInc)
            {
                decl += "autoincrement ";
            }
            if (!p.IsNullable)
            {
                decl += "not null ";
            }
            if (!string.IsNullOrEmpty(p.Collation))
            {
                decl += "collate " + p.Collation + " ";
            }

            return(decl);
        }
Esempio n. 3
0
        /// <summary>
        /// Get all changes fro SQLite Database
        /// </summary>
        /// <param name="schema">All Tables</param>
        /// <param name="lastModifiedDate">Changes since this date</param>
        internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate)
        {
            List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>();

            using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
            {
                try
                {
                    foreach (var ty in schema.Collections)
                    {
                        // Get mapping from my type
                        var map = manager.GetMapping(ty);

                        // Create query to select changes
                        var querySelect = SQLiteConstants.SelectChanges;

                        var columnsDcl = new List <String>();
                        var columnsPK  = new List <String>();


                        // Foreach columns, create the tsql command to execute
                        foreach (var c in map.Columns)
                        {
                            if (!c.IsPK)
                            {
                                columnsDcl.Add("[s].[" + c.Name + "]");
                            }

                            // If it's the PK, add it from Tracking (because of deleted items not in real table
                            if (c.IsPK)
                            {
                                columnsDcl.Add("[t].[" + c.Name + "]");
                                columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]");
                            }
                        }

                        var decl = string.Join(",\n", columnsDcl.ToArray());
                        var pk   = string.Join(" \nAND ", columnsPK.ToArray());
                        querySelect = String.Format(querySelect, map.TableName, pk, decl);

                        // Prepare command
                        using (var stmt = connection.Prepare(querySelect))
                        {
                            try
                            {
                                // Set Values
                                BindParameter(stmt, 1, lastModifiedDate);

                                // Get mapping form the statement
                                var cols = new TableMapping.Column[map.Columns.Length];

                                // Foreach column, get the property in my object
                                for (int i = 0; i < cols.Length; i++)
                                {
                                    var name = stmt.ColumnName(i);
                                    var c    = map.FindColumn(name);
                                    if (c != null)
                                    {
                                        cols[i] = map.FindColumn(name);
                                    }
                                }

                                // While row is available
                                //while (await stmt.StepAsync().AsTask().ConfigureAwait(false))
                                while (stmt.Step() == SQLiteResult.ROW)
                                {
                                    // Create the object
                                    SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType);

                                    for (int i = 0; i < cols.Length; i++)
                                    {
                                        if (cols[i] == null)
                                        {
                                            continue;
                                        }

                                        // Read the column
                                        var val = ReadCol(stmt, i, cols[i].ColumnType);

                                        // Set the value
                                        cols[i].SetValue(obj, val);
                                    }

                                    // Read the Oem Properties
                                    var newIndex = map.Columns.Count();

                                    obj.ServiceMetadata = new OfflineEntityMetadata();

                                    obj.ServiceMetadata.IsTombstone = (Boolean)ReadCol(stmt, newIndex, typeof(Boolean));
                                    obj.ServiceMetadata.Id          = (String)ReadCol(stmt, newIndex + 1, typeof(String));
                                    obj.ServiceMetadata.ETag        = (String)ReadCol(stmt, newIndex + 2, typeof(String));
                                    String absoluteUri = (String)ReadCol(stmt, newIndex + 3, typeof(String));
                                    obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri);

                                    lstChanges.Add(obj);
                                }
                            }
                            catch (Exception ex)
                            {
                                Debug.WriteLine(ex.Message);
                                throw;
                            }
                            finally
                            {
                                stmt.Reset();
                                stmt.ClearBindings();
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                    throw;
                }
            }
            return(lstChanges);
        }
Esempio n. 4
0
        /// <summary>
        /// Get all changes fro SQLite Database
        /// </summary>
        /// <param name="schema">All Tables</param>
        /// <param name="lastModifiedDate">Changes since this date</param>
        /// <param name="uploadBatchSize">Maximum number of rows to upload</param>
        internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate, int uploadBatchSize)
        {
            List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>();

            using (SQLiteDatabaseConnection connection = SQLitePCL.pretty.SQLite3.Open(localFilePath))
            {
                try
                {
                    foreach (var ty in schema.Collections)
                    {
                        // Get mapping from my type
                        var map = manager.GetMapping(ty);

                        // Create query to select changes
                        var querySelect = SQLiteConstants.SelectChanges;

                        var columnsDcl = new List <String>();
                        var columnsPK  = new List <String>();


                        // Foreach columns, create the tsql command to execute
                        foreach (var c in map.Columns)
                        {
                            if (!c.IsPK)
                            {
                                columnsDcl.Add("[s].[" + c.Name + "]");
                            }

                            // If it's the PK, add it from Tracking (because of deleted items not in real table
                            if (c.IsPK)
                            {
                                columnsDcl.Add("[t].[" + c.Name + "]");
                                columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]");
                            }
                        }

                        var decl = string.Join(",\n", columnsDcl.ToArray());
                        var pk   = string.Join(" \nAND ", columnsPK.ToArray());
                        querySelect = String.Format(querySelect, map.TableName, pk, decl);

                        // add limit if specified
                        if (uploadBatchSize > 0)
                        {
                            querySelect += $" LIMIT {uploadBatchSize}";
                        }

                        try
                        {
                            // Get mapping form the statement
                            var  cols     = new TableMapping.Column[map.Columns.Length];
                            bool firstRow = true;

                            // While row is available
                            foreach (var row in connection.Query(querySelect, P(lastModifiedDate)))
                            {
                                if (firstRow)
                                {
                                    // Foreach column, get the property in my object
                                    for (int i = 0; i < cols.Length; i++)
                                    {
                                        var name = row[i].ColumnInfo.Name;
                                        var c    = map.FindColumn(name);
                                        if (c != null)
                                        {
                                            cols[i] = map.FindColumn(name);
                                        }
                                    }

                                    firstRow = false;
                                }

                                // Create the object
                                SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType);

                                for (int i = 0; i < cols.Length; i++)
                                {
                                    if (cols[i] == null)
                                    {
                                        continue;
                                    }

                                    // Read the column
                                    var val = ReadCol(row, i, cols[i].ColumnType);

                                    // Set the value
                                    cols[i].SetValue(obj, val);
                                }

                                // Read the Oem Properties
                                var newIndex = map.Columns.Count();

                                obj.ServiceMetadata = new OfflineEntityMetadata();

                                obj.ServiceMetadata.IsTombstone = (bool)ReadCol(row, newIndex, typeof(Boolean));
                                obj.ServiceMetadata.Id          = (String)ReadCol(row, newIndex + 1, typeof(String));
                                obj.ServiceMetadata.ETag        = (String)ReadCol(row, newIndex + 2, typeof(String));
                                String absoluteUri = (String)ReadCol(row, newIndex + 3, typeof(String));
                                obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri);

                                lstChanges.Add(obj);
                            }
                        }
                        catch (Exception ex)
                        {
                            Debug.WriteLine(ex.Message);
                            throw;
                        }

                        // if we are batching uploads and the upload rowcount has been reached, skip
                        if (uploadBatchSize > 0 && lstChanges.Count >= uploadBatchSize)
                        {
                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
                    throw;
                }
            }

            // if we are batching uploads, limit the in-memory result set as well
            if (uploadBatchSize > 0)
            {
                return(lstChanges.Take(uploadBatchSize));
            }

            return(lstChanges);
        }