示例#1
0
        static async Task queryAirportsByTimezoneOffset(string tzOffset)
        {
            // SELECT * FROM c where c.timezone_num = "-5"
            // SELECT c.pk FROM c where c.timezone_num = "-5" offset 1 limit 2
            // SELECT COUNT(1) FROM c     <-- 1459
            // SELECT COUNT(1) FROM c where c.timezone_num = "-5" <-- 522

            // https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit

            log("queryAirportsByTimezoneOffset");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = dbNameEnvVar();
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select c.name, c.city, c.longitude, c.timezone_num from c where c.timezone_num = '{tzOffset}'";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#2
0
        static async Task deleteDocuments(string dbname, string cname, int maxCount)
        {
            log($"deleteDocuments; db: {dbname}, container: {cname}, maxCount: {maxCount}");

            CosmosUtil cu = new CosmosUtil();
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string         sql   = $"select * from c";
            List <dynamic> items = await cu.queryDocuments(sql, maxCount);

            log($"deleteDocuments, count: {items.Count}");
            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
                log($"deleting item: {items[i].id}, {items[i].pk}");
                string          id  = items[i].id;
                string          pk  = items[i].pk;
                GenericDocument doc = new GenericDocument();
                doc.id = items[i].id;
                doc.pk = items[i].pk;

                ItemResponse <GenericDocument> response = await cu.deleteGenericDocument(doc);

                log($"status code:    {response.StatusCode}");
                log($"request charge: {response.RequestCharge}");
            }
        }
示例#3
0
        static async Task upsertAirports(int maxCount)
        {
            log($"upsertAirports: {maxCount}");

            FileUtil       fsu      = new FileUtil();
            List <Airport> airports = new FileUtil().ReadAirportsCsv();

            log($"airports read from csv file: {airports.Count}");

            CosmosUtil cu     = new CosmosUtil();
            string     dbname = dbNameEnvVar();
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            for (int i = 0; i < airports.Count; i++)
            {
                if (i < maxCount)
                {
                    Airport a = airports[i];
                    Console.WriteLine(a.ToJson());
                    ItemResponse <Airport> response = await cu.upsertAirportDocument(a);

                    log($"status code:    {response.StatusCode}");
                    log($"request charge: {response.RequestCharge}");
                    log($"diagnostics:    {response.Diagnostics}");
                    log($"resource:       {response.Resource}");
                }
            }
            return;
        }
示例#4
0
        static async Task queryAirportsPaginated()
        {
            // SELECT COUNT(1) FROM c  <-- 1459
            // SELECT COUNT(1) FROM c where c.timezone_num = "-5"  <-- 522
            // SELECT c.pk FROM c where c.timezone_num = '-5' order by c.pk offset 140 limit 20

            // https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit
            // https://www.postgresql.org/docs/current/queries-limit.html
            // https://github.com/azure/azure-documentdb-datamigrationtool
            //
            // "The rows skipped by an OFFSET clause still have to be computed inside the server;
            //  therefore a large OFFSET can be inefficient."

            log("queryAirportsPaginated");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = dbNameEnvVar();
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string predicate = "where c.timezone_num = '-5'";
            int    itemCount = await cu.count(predicate);

            int offset       = 0;
            int itemsPerPage = 20;

            log("itemCount:    " + itemCount);
            log("itemsPerPage: " + itemsPerPage);
            double pagesDouble = ((double)itemCount / (double)itemsPerPage);
            double pagesCount  = Math.Ceiling(pagesDouble);

            log("pagesCount:   " + pagesCount);

            for (int p = 0; p < pagesCount; p++)
            {
                offset = offset + itemsPerPage;
                string sql = $"SELECT c.pk FROM c {predicate} order by c.pk offset {offset} limit {itemsPerPage}";
                log("===");
                log($"page: {p} sql: {sql}");

                List <dynamic> items = await cu.queryDocuments(sql, itemsPerPage, true);

                for (int i = 0; i < items.Count; i++)
                {
                    Console.WriteLine(items[i]);
                }
            }
            return;
        }
示例#5
0
        static async Task queryAirportsByPkId(string pk, string id)
        {
            log("queryAirportsByPkId");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select * from c where c.pk = '{pk}' and c.id = '{id}'";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#6
0
        static async Task queryAirportsByPk(string pk)
        {
            log("queryAirportsByPk");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = dbNameEnvVar();
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select * from c where c.pk = '{pk}'";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#7
0
        static async Task queryAirportsByTimezoneCode(string tzCode)
        {
            log("queryAirportsByTimezoneCode");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = dbNameEnvVar();
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select c.name, c.city, c.longitude, c.timezone_num from c where c.TimezoneCode = '{tzCode}'";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#8
0
        static async Task queryLatestEvents(int limit)
        {
            log("queryLatestEvents");

            CosmosUtil cu     = new CosmosUtil();
            string     dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
            string     cname  = "events";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select * from c order by c.epoch desc offset 0 limit {limit}";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#9
0
        static async Task queryEvents(double startEpoch, double endEpoch)
        {
            log($"queryEvents: {startEpoch} to {endEpoch}");

            CosmosUtil cu     = new CosmosUtil();
            string     dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
            string     cname  = "events";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string sql = $"select * from c where c.epoch >= {startEpoch} and c.epoch < {endEpoch}";

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#10
0
        static async Task queryAirportsByGPS(double lng, double lat, double meters)
        {
            log("queryAirportsByGPS");
            CosmosUtil cu     = new CosmosUtil();
            string     dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
            string     cname  = "airports";
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            string location = $"[{lng}, {lat}]";
            string sql      = "select c.pk, c.Name, c.City, c.location.coordinates from c " +
                              "where ST_DISTANCE(c.location, { \"type\": \"Point\", \"coordinates\":" +
                              location + "}) < " + meters;

            List <dynamic> items = await cu.queryDocuments(sql);

            for (int i = 0; i < items.Count; i++)
            {
                Console.WriteLine(items[i]);
            }
            return;
        }
示例#11
0
        static async Task upsertAirports(int maxCount, int pauseMs)
        {
            log($"upsertAirports: {maxCount}  pauseMs: {pauseMs}");

            FileUtil       fsu      = new FileUtil();
            List <Airport> airports = new FileUtil().ReadAirportsCsv();

            log($"airports read from csv file: {airports.Count}");

            CosmosUtil cu     = new CosmosUtil();
            string     dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
            string     cname  = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_COLLNAME");
            await cu.setCurrentDatabase(dbname);

            await cu.setCurrentContainer(cname);

            for (int i = 0; i < airports.Count; i++)
            {
                if (i < maxCount)
                {
                    Airport a = airports[i];
                    a.UpdateEpoch();
                    log("===");
                    Console.WriteLine(a.ToJson());
                    ItemResponse <Airport> response = await cu.upsertAirportDocument(a);

                    log("---");
                    log($"status code:    {response.StatusCode}");
                    log($"request charge: {response.RequestCharge}");
                    //log($"diagnostics:    {response.Diagnostics}");
                    //log($"resource:       {response.Resource}");
                    Thread.Sleep(pauseMs);
                }
            }
            log($"airports read from csv file: {airports.Count}");
            return;
        }