Farshid/Ashouri

database

20 posts tagged “database”.

  1. 2025

  2. Geopoly in SQLite

    I noticed this morning that one of my Datasette installations had the Geopoly SQLite extension enabled.

    sqlite geopoly gis geojson database
    4 min read

  3. Creating a minimal SpatiaLite database with Python

    When writing a test for datasette-leaflet-freedraw I realized I didn't have a simple tiny recipe for creating an in-memory SpatiaLite database in Python.

    spatialite sqlite python gis database
    1 min read

  4. Compiling and running sqlite3-rsync

    Today I heard about the sqlite3-rsync command, currently available in a branch in the SQLite code repository.

    sqlite compilation cli database synchronization
    1 min read

  5. Granting a PostgreSQL user read-only access to some tables

    I wanted to grant a PostgreSQL user (or role) read-only access to a specific list of tables. I created the role using the Heroku PostgreSQL web console. Having done that, it had the name .

    postgresql sql permissions database security
    1 min read
  6. 2024

  7. Combining CTEs and VALUES in SQLite

    Here's how to use SQLite's syntax with a CTE to create a temporary table that you can then perform joins against in a query: Try that here.

    sqlite sql cte database
    1 min read

  8. Calculating the size of a SQLite database file using SQL

    I learned this trick today while browsing the code of Blacklite, a neat Java library for writing diagnostic logs to a SQLite database.

    sqlite sql database cli bash
    1 min read

  9. Cumulative total over time in SQL

    This is a quick trick for creating a cumulative chart of the total number of items created over time based just on their creation date.

    sql database data-analysis cumulative-sum github
    1 min read

  10. Combining substr and instr to extract text

    Derek Willis has a Datasette instance full of political campaign emails running on Heroku. Matt Hodges pointed out that a lot of these emails include codes, which are used by ActBlue campaigns to…

    sqlite sql string-manipulation database
    1 min read

  11. Comparing database rows before and after with SQLite JSON functions

    Here's a trick I've been using to compare the rows in a table before and after I perform an operation against it. It works well for a few hundred (and maybe a few thousand) rows.

    sqlite sql json database data-analysis
    1 min read

  12. Enabling WAL mode for SQLite database files

    I was getting occasional messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep).

    sqlite database wal performance cli
    1 min read

  13. Finding duplicate records by matching name and nearby distance

    I wanted to find potentially duplicate records in my data, based on having the exact same name and being geographically located within 500 meters of each other.

    sql gis database duplicates spatialite
    1 min read

  14. Figuring out if a text value in SQLite is a valid integer or float

    Given a table with a column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it's a good idea…

    sqlite sql data-types validation database
    1 min read
  15. 2023

  16. Constructing GeoJSON in PostgreSQL

    In order to efficiently generate a GeoJSON representation of a vast number of locations, I'm currently experimenting with generating the GeoJSON directly inside a PostgreSQL SQL query using and…

    postgresql sql geojson gis json
    1 min read

  17. Compile a new sqlite3 binary on Ubuntu

    I wanted to try the backup command that was released in SQLite3 3.27.0 on 2019-02-07. Ubuntu 18.04.4 LTS has SQLite 3.22.0 from 2018-01-22.

    sqlite ubuntu linux compilation cli
    1 min read

  18. Enabling a gin index for faster LIKE queries

    I tried using a gin index to speed up queries against a column. PostgreSQL: More performance for LIKE and ILIKE statements provided useful background.

    django postgresql database performance indexing
    1 min read

  19. Copy tables between SQLite databases

    I figured out a pattern for doing this today using the CLI tool - given two SQLite databases in the current folder, called and : I'm using that in this GitHub Actions workflow.

    sqlite database cli bash data-transfer
    1 min read

  20. Counting SQLite virtual machine operations

    When SQLite executes a query, it does so by executing a sequence of virtual machine operations. There are mechanisms for cancelling a query after a specific number of these operations, to protect…

    sqlite database performance cli python
    1 min read

  21. Deno KV

    Initial article: April 28th 2023 - see below for an update I got intrigued by Deno KV, which describes itself as "a global database for global apps".

    deno deno-kv sqlite database key-value-store
    4 min read

  22. Consecutive groups in SQL using window functions

    I have a database table with all of my Swarm checkins since 2011, created using my swarm-to-sqlite tool.

    sql window-functions database data-analysis sqlite
    3 min read

  23. Allowing a container in Docker Desktop for Mac to talk to a PostgreSQL server on the host machine

    I like using Postgres.app to run PostgreSQL on my macOS laptop. I use it for a bunch of different projects.

    docker postgresql macos networking database
    1 min read