I am a Bug Hunter. I am building my bug hunting automation workflow. I have a lot of unique urls in a txt file.
I have this data from the result of https://github.com/projectdiscovery/httpx in a txt file:
---
http://crl.gtm.starbucks.com/policies?AWS_DYNAMODB_TABLE_NAMES=FUZZ [403] [246] [Request Rejected] []
http://ocsp-chd.starbucks.com?COLUMN_ID_SNO=FUZZ [403] [247] [Request Rejected] []
http://ocsp.gtm.starbucks.com/.*[0-9].*?BANNER_ANNOUNCEMENT_TABLE_NAME=FUZZ [403] [246] [Request Rejected] []
http://ocsp-iad.starbucks.com?AWS_DYNAMODB_TABLE_NAMES=FUZZ [403] [247] [Request Rejected] []
https://active.starbucks.com.cn/config/visualized/Web.conf?comment=FUZZ [403] [3712] [] []
https://active.starbucks.com.cn/shortLink/index.html?resume=FUZZ [400] [278] [] []
https://alumni.starbucks.com/.?LANG=FUZZ [400] [16023] [Starbucks Alumni Community] []
https://alumni.starbucks.com/login/''?customize_changeset_uuid=FUZZ [500] [2491] [WordPress › Error] []
https://alumni.starbucks.com/login/join-now?url=FUZZ [301] [0] [] []
https://alumni.starbucks.com/login/''?__proto__=FUZZ [403] [17006] [403 Forbidden] []
https://alumni.starbucks.com/login/''?rest_route=FUZZ [404] [114] [] []
https://appcafe-iad.starbucks.com/enroll/selerix_common/lib/hover-intent?bid=FUZZ [302] [228] [Object moved] []
https://appcafe-iad.starbucks.com/enroll/selerix_common/lib/hover-intent?mode=FUZZ [302] [246] [Object moved] []
https://appcafe-iad.starbucks.com//Images?AWSAccessKeyId=FUZZ [301] [184] [Document Moved] []
https://appcafe-iad.starbucks.com/Images/Custom?AWSAccessKeyId=FUZZ [301] [191] [Document Moved] []
https://apply.starbucks.com/api/application/v2/profile?-is-there-a-limit-to-how-many=FUZZ [400] [39] [] []
https://apply.starbucks.com/api/apply/ranking/personalization_preferences?UPDATE_TABBABLES_DEBOUNCED=FUZZ [403] [30] [] []
https://apply.starbucks.com/api/apply/v2/chat?SHOW_NEW_MESSAGES_BELOW_BUTTON=FUZZ [403] [138] [] []
https://apply.starbucks.com/api/apply/v2/geocode_location?_au=FUZZ [422] [69] [] []
https://customerservicenew.starbucks.com/./log?CSV=FUZZ [401] [0] [] []
https://customerservicenew.starbucks.com/login?sysparm_view=FUZZ [200] [54398] [CCC] []
https://customerservicenew.starbucks.com/./log?RP=FUZZ [200] [15] [] []
https://customerservicenew.starbucks.com/./log?sysparm_view=FUZZ [200] [19755] [CCC] []
https://d01-1-ag.productioncontroller.starbucks.com?PHPSESSID=FUZZ [403] [179] [403 Forbidden] []
https://datapulse-uat.starbucks.com//error.html?PHPSESSID=FUZZ [403] [1261] [Service unavailable] []
https://deepbrewbeta-prod-api.starbucks.com/&&?VBScrollspyPlugin=FUZZ [403] [1892] [Web App - Unavailable] []
https://dev-digitalcontent-webpreview.starbucks.com/!=?PHPSESSID=FUZZ [406] [1379] [Service unavailable] []
https://developer.starbucks.com/core/assets?__CONFIG=FUZZ [301] [162] [301 Moved Permanently] []
https://developer.starbucks.com/errorcorrectlevel:?_format=FUZZ [404] [76] [] []
---

For example:

http://crl.gtm.starbucks.com/policies?AWS_DYNAMODB_TABLE_NAMES=FUZZ [403] [246] [Request Rejected] []
Here http://crl.gtm.starbucks.com/policies?AWS_DYNAMODB_TABLE_NAMES=FUZZ is URL, [403] is status code, [246] is response content length, [Request Rejected] is page title

https://alumni.starbucks.com/.?LANG=FUZZ [400] [16023] [Starbucks Alumni Community] []
Here https://alumni.starbucks.com/.?LANG=FUZZ is URL, [400] is status code, [16023] is response content length, [Starbucks Alumni Community] is page title


https://customerservicenew.starbucks.com/./log?RP=FUZZ [200] [15] [] []
Here https://customerservicenew.starbucks.com/./log?RP=FUZZ is URL, [200] is status code, [15] is response content length, page title is empty []

https://developer.starbucks.com/core/assets?__CONFIG=FUZZ [301] [162] [301 Moved Permanently] []
Here https://developer.starbucks.com/core/assets?__CONFIG=FUZZ is URL, [301] is status code, [162] is response content length, [301 Moved Permanently] is page title

So you can see how it's formed.

Now I want to develop a nodejs script that:
1) Takes /path/to/httpx/result.txt
2) Takes /path/to/sqlite/folder where it will create sqlite database file with random 8 alphanumeric character name which can have lower or uppercase values anything random. 
2) Uses puppeteer browser to open these urls one by one and then take a screenshot and then save the screenshot along with it's URL, Status code, Response content length, Page title in a sqlite database. The name of the sqlite databse should have random 8 alphanumeric character name which can have lower or uppercase values anything random.

Note:
1) It should always open a new browser for each url. And don't forget to close the last browser properly.
2) It should wait for the URL to be properly opened before taking a screenshot. And the max timeout for a single url should be 60 seconds.
3) Add the data in the sqlite database in realtime.
4) Handle every exception that can occur. The script should work no matter what. It should be able to handle every situation. If there is an error for a url then it should not stop the scanning of other urls.
5) The script should verbose some information on the cli terminal.


Now here is some information regarding storing files in sqlite database:
---

sqlite.org
SQLite As An Application File Format
19–24 minutes

SQLite

Small. Fast. Reliable.
Choose any three.
Executive Summary

An SQLite database file with a defined schema often makes an excellent application file format. Here are a dozen reasons why this is so:

    Simplified Application Development
    Single-File Documents
    High-Level Query Language
    Accessible Content
    Cross-Platform
    Atomic Transactions
    Incremental And Continuous Updates
    Easily Extensible
    Performance
    Concurrent Use By Multiple Processes
    Multiple Programming Languages
    Better Applications 

Each of these points will be described in more detail below, after first considering more closely the meaning of "application file format". See also the short version of this whitepaper.
What Is An Application File Format?

An "application file format" is the file format used to persist application state to disk or to exchange information between programs. There are thousands of application file formats in use today. Here are just a few examples:

    DOC - Word Perfect and Microsoft Office documents
    DWG - AutoCAD drawings
    PDF - Portable Document Format from Adobe
    XLS - Microsoft Excel Spreadsheet
    GIT - Git source code repository
    EPUB - The Electronic Publication format used by non-Kindle eBooks
    ODT - The Open Document format used by OpenOffice and others
    PPT - Microsoft PowerPoint presentations
    ODP - The Open Document presentation format used by OpenOffice and others 

We make a distinction between a "file format" and an "application format". A file format is used to store a single object. So, for example, a GIF or JPEG file stores a single image, and an XHTML file stores text, so those are "file formats" and not "application formats". An EPUB file, in contrast, stores both text and images (as contained XHTML and GIF/JPEG files) and so it is considered an "application format". This article is about "application formats".

The boundary between a file format and an application format is fuzzy. This article calls JPEG a file format, but for an image editor, JPEG might be considered the application format. Much depends on context. For this article, let us say that a file format stores a single object and an application format stores many different objects and their relationships to one another.

Most application formats fit into one of these three categories:

    Fully Custom Formats. Custom formats are specifically designed for a single application. DOC, DWG, PDF, XLS, and PPT are examples of custom formats. Custom formats are usually contained within a single file, for ease of transport. They are also usually binary, though the DWG format is a notable exception. Custom file formats require specialized application code to read and write and are not normally accessible from commonly available tools such as unix command-line programs and text editors. In other words, custom formats are usually "opaque blobs". To access the content of a custom application file format, one needs a tool specifically engineered to read and/or write that format.

    Pile-of-Files Formats. Sometimes the application state is stored as a hierarchy of files. Git is a prime example of this, though the phenomenon occurs frequently in one-off and bespoke applications. A pile-of-files format essentially uses the filesystem as a key/value database, storing small chunks of information into separate files. This gives the advantage of making the content more accessible to common utility programs such as text editors or "awk" or "grep". But even if many of the files in a pile-of-files format are easily readable, there are usually some files that have their own custom format (example: Git "Packfiles") and are hence "opaque blobs" that are not readable or writable without specialized tools. It is also much less convenient to move a pile-of-files from one place or machine to another, than it is to move a single file. And it is hard to make a pile-of-files document into an email attachment, for example. Finally, a pile-of-files format breaks the "document metaphor": there is no one file that a user can point to that is "the document".

    Wrapped Pile-of-Files Formats. Some applications use a Pile-of-Files that is then encapsulated into some kind of single-file container, usually a ZIP archive. EPUB, ODT,and ODP are examples of this approach. An EPUB book is really just a ZIP archive that contains various XHTML files for the text of book chapters, GIF and JPEG images for the artwork, and a specialized catalog file that tells the eBook reader how all the XML and image files fit together. OpenOffice documents (ODT and ODP) are also ZIP archives containing XML and images that represent their content as well as "catalog" files that show the interrelationships between the component parts.

    A wrapped pile-of-files format is a compromise between a full custom file format and a pure pile-of-files format. A wrapped pile-of-files format is not an opaque blob in the same sense as a custom format, since the component parts can still be accessed using any common ZIP archiver, but the format is not quite as accessible as a pure pile-of-files format because one does still need the ZIP archiver, and one cannot normally use command-line tools like "find" on the file hierarchy without first un-zipping it. On the other hand, a wrapped pile-of-files format does preserve the document metaphor by putting all content into a single disk file. And because it is compressed, the wrapped pile-of-files format tends to be more compact.

    As with custom file formats, and unlike pure pile-of-file formats, a wrapped pile-of-files format is not as easy to edit, since usually the entire file must be rewritten in order to change any component part. 

The purpose of this document is to argue in favor of a fourth new category of application file format: An SQLite database file.
SQLite As The Application File Format

Any application state that can be recorded in a pile-of-files can also be recorded in an SQLite database with a simple key/value schema like this:

    CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB);

If the content is compressed, then such an SQLite Archive database is the same size (±1%) as an equivalent ZIP archive, and it has the advantage of being able to update individual "files" without rewriting the entire document.

But an SQLite database is not limited to a simple key/value structure like a pile-of-files database. An SQLite database can have dozens or hundreds or thousands of different tables, with dozens or hundreds or thousands of fields per table, each with different datatypes and constraints and particular meanings, all cross-referencing each other, appropriately and automatically indexed for rapid retrieval, and all stored efficiently and compactly in a single disk file. And all of this structure is succinctly documented for humans by the SQL schema.

In other words, an SQLite database can do everything that a pile-of-files or wrapped pile-of-files format can do, plus much more, and with greater lucidity. An SQLite database is a more versatile container than key/value filesystem or a ZIP archive. (For a detailed example, see the OpenOffice case study essay.)

The power of an SQLite database could, in theory, be achieved using a custom file format. But any custom file format that is as expressive as a relational database would likely require an enormous design specification and many tens or hundreds of thousands of lines of code to implement. And the end result would be an "opaque blob" that is inaccessible without specialized tools.

Hence, in comparison to other approaches, the use of an SQLite database as an application file format has compelling advantages. Here are a few of these advantages, enumerated and expounded:

    Simplified Application Development. No new code is needed for reading or writing the application file. One has merely to link against the SQLite library, or include the single "sqlite3.c" source file with the rest of the application C code, and SQLite will take care of all of the application file I/O. This can reduce application code size by many thousands of lines, with corresponding saving in development and maintenance costs.

    SQLite is one of the most used software libraries in the world. There are literally tens of billions of SQLite database files in use daily, on smartphones and gadgets and in desktop applications. SQLite is carefully tested and proven reliable. It is not a component that needs much tuning or debugging, allowing developers to stay focused on application logic.

    Single-File Documents. An SQLite database is contained in a single file, which is easily copied or moved or attached. The "document" metaphor is preserved.

    SQLite does not have any file naming requirements and so the application can use any custom file suffix that it wants to help identify the file as "belonging" to the application. SQLite database files contain a 4-byte Application ID in their headers that can be set to an application-defined value and then used to identify the "type" of the document for utility programs such as file(1), further enhancing the document metaphor.

    High-Level Query Language. SQLite is a complete relational database engine, which means that the application can access content using high-level queries. Application developers need not spend time thinking about "how" to retrieve the information they need from a document. Developers write SQL that expresses "what" information they want and let the database engine to figure out how to best retrieve that content. This helps developers operate "heads up" and remain focused on solving the user's problem, and avoid time spent "heads down" fiddling with low-level file formatting details.

    A pile-of-files format can be viewed as a key/value database. A key/value database is better than no database at all. But without transactions or indices or a high-level query language or a proper schema, it is much harder and more error prone to use a key/value database than a relational database.

    Accessible Content. Information held in an SQLite database file is accessible using commonly available open-source command-line tools - tools that are installed by default on Mac and Linux systems and that are freely available as a self-contained EXE file on Windows. Unlike custom file formats, application-specific programs are not required to read or write content in an SQLite database. An SQLite database file is not an opaque blob. It is true that command-line tools such as text editors or "grep" or "awk" are not useful on an SQLite database, but the SQL query language is a much more powerful and convenient way for examining the content, so the inability to use "grep" and "awk" and the like is not seen as a loss.

    An SQLite database is a well-defined and well-documented file format that is in widespread use by literally millions of applications and is backwards compatible to its inception in 2004 and which promises to continue to be compatible in decades to come. The longevity of SQLite database files is particularly important to bespoke applications, since it allows the document content to be accessed far in the future, long after all traces of the original application have been lost. Data lives longer than code. SQLite databases are recommended by the US Library of Congress as a storage format for long-term preservation of digital content.

    Cross-Platform. SQLite database files are portable between 32-bit and 64-bit machines and between big-endian and little-endian architectures and between any of the various flavors of Windows and Unix-like operating systems. The application using an SQLite application file format can store binary numeric data without having to worry about the byte-order of integers or floating point numbers. Text content can be read or written as UTF-8, UTF-16LE, or UTF-16BE and SQLite will automatically perform any necessary translations on-the-fly.

    Atomic Transactions. Writes to an SQLite database are atomic. They either happen completely or not at all, even during system crashes or power failures. So there is no danger of corrupting a document just because the power happened to go out at the same instant that a change was being written to disk.

    SQLite is transactional, meaning that multiple changes can be grouped together such that either all or none of them occur, and so that the changes can be rolled back if a problem is found prior to commit. This allows an application to make a change incrementally, then run various sanity and consistency checks on the resulting data prior to committing the changes to disk. The Fossil DVCS uses this technique to verify that no repository history has been lost prior to each change.

    Incremental And Continuous Updates. When writing to an SQLite database file, only those parts of the file that actually change are written out to disk. This makes the writing happen faster and saves wear on SSDs. This is an enormous advantage over custom and wrapped pile-of-files formats, both of which usually require a rewrite of the entire document in order to change a single byte. Pure pile-of-files formats can also do incremental updates to some extent, though the granularity of writes is usually larger with pile-of-file formats (a single file) than with SQLite (a single page).

    SQLite also supports continuous update. Instead of collecting changes in memory and then writing them to disk only on a File/Save action, changes can be written back to the disk as they occur. This avoids loss of work on a system crash or power failure. An automated undo/redo stack, managed using triggers, can be kept in the on-disk database, meaning that undo/redo can occur across session boundaries.

    Easily Extensible. As an application grows, new features can be added to an SQLite application file format simply by adding new tables to the schema or by adding new columns to existing tables. Adding columns or tables does not change the meaning of prior queries, so with a modicum of care to ensuring that the meaning of legacy columns and tables are preserved, backwards compatibility is maintained.

    It is possible to extend custom or pile-of-files formats too, of course, but doing is often much harder. If indices are added, then all application code that changes the corresponding tables must be located and modified to keep those indices up-to-date. If columns are added, then all application code that accesses the corresponding table must be located and modified to take into account the new columns.

    Performance. In many cases, an SQLite application file format will be faster than a pile-of-files format or a custom format. In addition to being faster for raw read and writes, SQLite can often dramatically improves start-up times because instead of having to read and parse the entire document into memory, the application can do queries to extract only the information needed for the initial screen. As the application progresses, it only needs to load as much material as is needed to draw the next screen, and can discard information from prior screens that is no longer in use. This helps keep the memory footprint of the application under control.

    A pile-of-files format can be read incrementally just like SQLite. But many developers are surprised to learn that SQLite can read and write smaller BLOBs (less than about 100KB in size) from its database faster than those same blobs can be read or written as separate files from the filesystem. (See 35% Faster Than The Filesystem and Internal Versus External BLOBs for further information.) There is overhead associated with operating a relational database engine, however one should not assume that direct file I/O is faster than SQLite database I/O, as often it is not.

    In either case, if performance problems do arise in an SQLite application those problems can often be resolved by adding one or two CREATE INDEX statements to the schema or perhaps running ANALYZE one time and without having to touch a single line of application code. But if a performance problem comes up in a custom or pile-of-files format, the fix will often require extensive changes to application code to add and maintain new indices or to extract information using different algorithms.

    Concurrent Use By Multiple Processes. SQLite automatically coordinates concurrent access to the same document from multiple threads and/or processes. Two or more applications can connect and read from the same document at the same time. Writes are serialized, but as writes normally only take milliseconds, applications simply take turns writing. SQLite automatically ensures that the low-level format of the document is uncorrupted. Accomplishing the same with a custom or pile-of-files format, in contrast, requires extensive support in the application. And the application logic needed to support concurrency is a notorious bug-magnet.

    Multiple Programming Languages. Though SQLite is itself written in ANSI-C, interfaces exist for just about every other programming language you can think of: C++, C#, Objective-C, Java, Tcl, Perl, Python, Ruby, Erlang, JavaScript, and so forth. So programmers can develop in whatever language they are most comfortable with and which best matches the needs of the project.

    An SQLite application file format is a great choice in cases where there is a collection or "federation" of separate programs, often written in different languages and by different development teams. This comes up commonly in research or laboratory environments where one team is responsible for data acquisition and other teams are responsible for various stages of analysis. Each team can use whatever hardware, operating system, programming language and development methodology that they are most comfortable with, and as long as all programs use an SQLite database with a common schema, they can all interoperate.

    Better Applications. If the application file format is an SQLite database, the complete documentation for that file format consists of the database schema, with perhaps a few extra words about what each table and column represents. The description of a custom file format, on the other hand, typically runs on for hundreds of pages. A pile-of-files format, while much simpler and easier to describe than a fully custom format, still tends to be much larger and more complex than an SQL schema dump, since the names and format for the individual files must still be described.

    This is not a trivial point. A clear, concise, and easy to understand file format is a crucial part of any application design. Fred Brooks, in his all-time best-selling computer science text, The Mythical Man-Month says:

        Representation is the essence of computer programming.
        ...
        Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowcharts; they'll be obvious.

    Rob Pike, in his Rules of Programming expresses the same idea this way:

        Data dominates. If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident. Data structures, not algorithms, are central to programming.

    Linus Torvalds used different words to say much the same thing on the Git mailing list on 2006-06-27:

        Bad programmers worry about the code. Good programmers worry about data structures and their relationships. 

    The point is this: an SQL database schema almost always does a far better job of defining and organizing the tables and data structures and their relationships. And having clear, concise, and well-defined representation almost always results in an application that performs better, has fewer problems, and is easier to develop and maintain. 

Conclusion

SQLite is not the perfect application file format for every situation. But in many cases, SQLite is a far better choice than either a custom file format, a pile-of-files, or a wrapped pile-of-files. SQLite is a high-level, stable, reliable, cross-platform, widely-deployed, extensible, performant, accessible, concurrent file format. It deserves your consideration as the standard file format on your next application design.

This page last modified on 2022-01-08 05:02:57 UTC
---

---

sqlite.org
SQLite Archive Files
10–12 minutes

SQLite Archive Files
Table Of Contents
1. Introduction

An "SQLite Archive" is a file container similar to a ZIP archive or Tarball but based on an SQLite database.

An SQLite Archive is an ordinary SQLite database file that contains the following table as part of its schema:

CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- name of the file
  mode INT,               -- access permissions
  mtime INT,              -- last modification time
  sz INT,                 -- original file size
  data BLOB               -- compressed content
);

Each row of the SQLAR table holds the content of a single file. The filename (the full pathname relative to the root of the archive) is in the "name" field. The "mode" field is an integer which is the unix-style access permissions for the file. "mtime" is the modification time of the file in seconds since 1970. "sz" is the original uncompressed size of the file. The "data" field contains the file content. The content is usually compressed using Deflate, though not always. If the "sz" field is equal to the size of the "data" field, then the content is stored uncompressed.
1.1. Database As Container Object

An SQLite Archive is one example of a more general idea that an SQLite database can behave as a container object holding lots of smaller data components.

With client/server databases like PostgreSQL or Oracle, users and developers tend to think of the database as a service or a "node", not as an object. This is because the database content is spread out across multiple files on the server, or possibly across multiple servers in a service cluster. One cannot point to a single file or even a single directory and say "this is the database".

SQLite, in contrast, stores all content in a single file on disk. That single file is something you can point to and say "this is the database". It behaves as an object. An SQLite database file can be copied, renamed, sent as an email attachment, passed as the argument a POST HTTP request, or otherwise treated as other data object such as an image, document, or media file.

Studies show that many applications already use SQLite as a container object. For example, Kennedy (no relation to the SQLite developer) reports that 14% of Android applications never write to their SQLite databases. It is believed that these applications are downloading entire databases from the cloud and then using the information locally as needed. In other words, the applications are using SQLite not so much as a database but as a queryable wire-transfer format.
1.2. Applications Using SQLite Archives

The Fossil Distributed Version Control system provides users with the option to download check-ins as either Tarballs, ZIP Archives, or SQLite Archives.
2. Advantages Of SQLite Archives

    An SQLite Archive is flexible. ZIP Archives and Tarballs are limited to storing only files. An SQLite Archive stores files plus whatever other tabular and/or relational data seems useful to the application.

    An SQLite Archive is transactional. Updates are atomic and durable, even if there are crashes or power losses in the middle of the update. Readers see a consistent and unchanging version of the content even is some other process is simultaneously updating the archive.

    An SQLite Archive can be updated incrementally. Individual files can be added or removed or replaced without having to rewrite the entire archive.

    An SQLite Archive can be queried using a high-level query language (SQL). Some examples:
        What is the total size of all files in the archive whose names end in ".h" or ".cpp"?
        What percentage of the files are compressed by less than 25%?
        How many executable files are in the archive? 
    Questions like these (and countless others) can be answered without having to uncompress or extract any content.

    Applications that already use SQLite for other purposes can easily add support for SQLite Archives using a small extension (https://sqlite.org/src/file/ext/misc/sqlar.c) to handle the compression and decompression of content. Even this tiny extension can be omitted if the files in the archive are uncompressed. In contrast, supporting ZIP Archives and/or Tarballs requires either separate libraries or lots of extra custom code, or sometimes both.

    An SQLite Archive can work around firewall-imposed censorship. For example, certain file types that are considered "dangerous" (examples: DLLs) will be blocked by Gmail and probably many other email services and firewalls, even if the files are wrapped inside a ZIP Archive or Tarball. But these firewalls usually do not (yet) know about SQLite Archives and so content can be put inside an SQLite Archive to evade censorship. 

3. Disadvantages Of SQLite Archives

    The SQLite Archive is a relatively new format. It was first described in in 2014. ZIP Archives and Tarballs, on the other hand, have been around for decades and are well-entrenched as standard formats. Most programmers know what a ZIP Archive or Tarball is, but if you say "SQLite Archive" you are more likely to get a reply of "What?" Tooling to process ZIP Archives and Tarballs is more likely to be installed on stock computers.

    Since an SQLite database is a more general format (it is designed to do much more than simply store a bunch of files) it is not as compact as either the ZIP Archive or Tarball formats. An SQLite Archive is usually about 1% larger than the equivalent ZIP Archive. Tarballs are compressed as a single unit rather than compressing each file separately as is done by both SQLite and ZIP Archives. For these reason, Tarballs tend to be smaller than either ZIP or SQLite Archives.

    As an example, the following table show the relative sizes for an SQLite Archive, a ZIP Archive, and a Tarball of the 1,743 files in the SQLite 3.22.0 source tree:
    SQLite Archive	10,754,048
    ZIP Archive (using Info-ZIP 3.0)	10,662,365
    ZIP Archive (using zipfile)	10,390,215
    Tarball	 9,781,109

    An SQLite Archive supports only the Deflate compression method. Tarballs and ZIP Archive support a wider assortment of compression methods. 

4. Managing An SQLite Archive From The Command-Line

The recommended way of creating, updating, listing, and extracting an SQLite Archive is to use the sqlite3.exe command-line shell for SQLite version 3.23.0 (2018-04-02) or later. This CLI supports the -A command-line option that allows easy management of SQLite Archives. The CLI for SQLite version 3.22.0 (2018-01-22) has the .archive command for managing SQLite Archives, but that requires interacting with the shell.

To list all of the files in an SQLite Archive named "example.sqlar" using one of these commands:

sqlite3 example.sqlar -At
sqlite3 example.sqlar -Atv

To extract all files from an SQLite Archive named "example.sqlar":

sqlite3 example.sqlar -Ax

To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt files in the current directory:

sqlite3 alltxt.sqlar -Ac *.txt

To add or update files in an existing SQLite Archive:

sqlite3 example.sqlar -Au *.md

For usage hints and a summary of all options, simply give the CLI the -A option with no additional arguments:

All of these commands work the same way if the filename argument is is a ZIP Archive instead of an SQLite database.

Just as there is the "zip" program to manage ZIP Archives, and the "tar" program to manage Tarballs, the "sqlar" program exists to manage SQL Archives. The "sqlar" program is able to create a new SQLite Archive, list the content of an existing archive, add or remove files from the archive, and/or extract files from the archive. A separate "sqlarfs" program is able to mount the SQLite Archive as a Fuse Filesystem.
5. Managing SQLite Archives From Application Code

Applications can easily read or write SQLite Archives by linking against SQLite and including the ext/misc/sqlar.c extension to handle the compression and decompression. The sqlar.c extension creates two new SQL functions.

sqlar_compress(X)

    The sqlar_compress(X) function attempts to compress a copy of the blob X using the Default algorithm and returns the result as a blob. If the input X is not a compressible blob, then a copy of X is returned. This routine is used when inserting content into an SQLite Archive. 
sqlar_uncompress(Y,SZ)

    The sqlar_uncompress(Y,SZ) function will undo the compression accomplished by sqlar_compress(X). The Y parameter is the compressed content (the output from a prior call to sqlar_compress()) and SZ is the original uncompressed size of the input X that generated Y. If SZ is less than or equal to the size of Y, that indicates that no compression occurred, and so sqlar_uncompress(Y,SZ) returns a copy of Y. Otherwise, sqlar_uncompress(Y,SZ) runs the Inflate algorithm on Y to uncompress it and restore it to its original form and returns the uncompressed content. This routine is used when extracting content from an SQLite Archive. 

Using the two routines above, it is simple for applications to insert new records into or extract existing records from an SQLite Archive. Insert a new into an SQLite Archive using code like this:

INSERT INTO sqlar(name,mode,mtime,sz,data)
 VALUES ($name,$mode,strftime('%s',$mtime),
         length($content),sqlar_compress($content));

Extract an entry from the SQLite Archive using code like this:

SELECT name, mode, datetime(mtime,'unixepoch'), sqlar_uncompress(data,sz)
  FROM sqlar
 WHERE ...;

The code above is for the general case. For the special case of an SQLite Archive that only stores uncompressed or uncompressible content (this might come up, for example, in an SQLite Archive that stores only JPEG, GIF, and/or PNG images) then the content can be inserted into and extracted from the database without using the sqlar_compress() and sqlar_uncompress() functions, and the sqlar.c extension is not required.

This page last modified on 2023-01-12 11:08:35 UTC

---

---

sqlite.org
SQLite Archiver: Documentation
4–5 minutes

This repository contains sources for the "SQLite Archiver" program. This program (named "sqlar") operates much like "zip", except that the compressed archive it builds is stored in an SQLite database instead of a ZIP archive.

The motivation for this is to see how much larger an SQLite database file is compared to a ZIP archive containing the same content. The answer depends on the filenames, but 2% seems to be a reasonable guess. In other words, storing files as compressed blobs in an SQLite database file results in a file that is only about 2% larger than storing those same files in a ZIP archive using the same compression.
Compiling

On unix, just type "make". The SQLite sources are included. The zlib compression library is needed to build.
Usage

To create an archive:

    sqlar ARCHIVE FILES...

All files named in FILES... will be added to the archive. If another file with the same name already exists in the archive, it is replaced. If any of the named FILES is a directory, that directory is scanned recursively.

To see the contents of an archive:

    sqlar -l ARCHIVE

To extract the contents of an archive:

    sqlar -x ARCHIVE [FILES...]

If a FILES argument is provided, then only the named files are extracted. Without a FILES argument, all files are extracted.

All commands can be supplemented with -v for verbose output. For example:

    sqlar -v ARCHIVE FILES..
    sqlar -lv ARCHIVE
    sqlar -xv ARCHIVE

File are normally compressed using zlib prior to being stored as BLOBs in the database. However, if the file is incompressible or if the -n option is used on the command-line, then the file is stored in the database exactly as it appears on disk, without compression.
Storage

The database schema looks like this:

    CREATE TABLE sqlar(
      name TEXT PRIMARY KEY,  -- name of the file
      mode INT,               -- access permissions
      mtime INT,              -- last modification time
      sz INT,                 -- original file size
      data BLOB               -- compressed content
    );

Both directories and empty files have sqlar.sz==0. Directories can be distinguished from empty files because directories have sqlar.data IS NULL. The file is compressed if length(sqlar.blob)<sqlar.sz and is stored as plaintext if length(sqlar.blob)==sqlar.sz.

Symbolic links have sqlar.sz set to -1, and the link target stored as a text value in the sqlar.data field.

SQLAR uses the "zlib format" for compression. ZIP uses the raw deflate format. The difference is that the zlib format contains a two byte compression-type indentification header (0x78 0x9c) and a 4-byte checksum at the end. Thus the "data" for SQLAR is always 6 bytes larger than the equivalent data for ZIP. The SQLAR program uses the zlib format rather than the slightly smaller raw deflate format because that is what the zlib documentation recommends.

SQLAR might someday be extended to support additional compression formats other than deflate. If so, the data field will contain new header values to identify entries compressed using the new formats.
Fuse Filesystem

An SQLite Archive file can be mounted as a Fuse Filesystem using the "sqlarfs" utility, including with this project.

To build the "sqlarfs" utility, run:

    make sqlarfs

Then to mount an SQLite archive as a filesystem, run:

    mkdir ~/fuse
    ./sqlarfs ARCHIVE_NAME -f ~/fuse

Replace ARCHIVE_NAME with the filename of the SQLite archive file to be mounted, of course. The -f option keeps sqlarfs running in the foreground, so that you can unmount the filesystem by simply pressing the interrupt key (usually Ctrl-C).

---