Task 683: .SQL File Format

File Format Specifications for .SQL

.SQL files (case-insensitive extension, commonly .sql) are plain text files used to store SQL scripts, statements, or database dumps. They do not have a structured binary format like proprietary file types (e.g., no headers, magic numbers, or fixed fields). Instead, they follow general text file conventions:

• Content: Sequence of SQL commands (e.g., CREATE, INSERT, SELECT) typically separated by semicolons (;), with optional comments (e.g., -- or /* */).
• Encoding: Usually UTF-8, ASCII, or UTF-16, depending on the system or editor.
• Line endings: Platform-dependent (CRLF on Windows, LF on Unix/Linux).
• MIME type: text/plain or application/sql.
• No formal specification beyond SQL standards (e.g., ISO/IEC 9075) for the language syntax; the file itself is unstructured text.
• Common uses: Database schema creation, data import/export, or batch queries.

There is no official "file format specification" document from a standards body, as it's not a binary or encoded format. Tools like database management systems (e.g., MySQL, PostgreSQL) parse the text content as SQL.

1. List of all the properties of this file format intrinsic to its file system:

Since .SQL is a plain text format, it has no format-specific intrinsic properties (e.g., no embedded metadata like in EXIF for images). The properties are standard file system attributes common to any file, but here's a comprehensive list of those typically available across operating systems (e.g., via stat() system call on Unix-like systems or equivalent on Windows):

• File name (including extension)
• File size (in bytes)
• Creation time (birth time, if supported by FS)
• Last modification time
• Last access time
• File permissions (mode, e.g., read/write/execute for owner/group/others)
• Owner user ID (UID)
• Owner group ID (GID)
• Inode number (on Unix-like systems, for unique file identification)
• Number of hard links
• File type (regular file)
• Device ID (for the storage device)

These are retrieved via file system APIs and are not part of the .SQL content itself.

1. Two direct download links for files of format .SQL:

• https://raw.githubusercontent.com/harryho/db-samples/master/mysql/northwind.sql (Northwind sample database schema and data for MySQL)
• https://raw.githubusercontent.com/jOOQ/sakila/master/mysql-sakila-db/mysql-sakila-schema.sql (Sakila sample database schema for MySQL)

1. Ghost blog embedded HTML JavaScript for drag-and-drop .SQL file property dump:

This is a self-contained HTML snippet with JavaScript that can be embedded in a Ghost blog post (or any HTML page). It creates a drop zone where users can drag and drop a .SQL file. Upon drop, it reads the file using the browser's File API and displays all the file system-intrinsic properties listed above (note: browser APIs limit access to full FS properties for security; it provides available ones like name, size, type, lastModified. For full FS properties, a server-side tool is needed, but this approximates client-side).

1. Python class for .SQL file handling:

This class uses os.stat to retrieve file system properties. It opens the file (though not necessary for properties), "decodes" (reads as text, though properties are FS-based), prints properties, and includes a write method to create/modify a .SQL file with sample content.

import os
import time

class SQLFileHandler:
    def __init__(self, filepath):
        self.filepath = filepath
        if not filepath.lower().endswith('.sql'):
            raise ValueError("File must have .sql extension")
        self.properties = self._get_properties()

    def _get_properties(self):
        stat = os.stat(self.filepath)
        return {
            'File name': os.path.basename(self.filepath),
            'File size (in bytes)': stat.st_size,
            'Creation time': time.ctime(stat.st_ctime),
            'Last modification time': time.ctime(stat.st_mtime),
            'Last access time': time.ctime(stat.st_atime),
            'File permissions (mode)': oct(stat.st_mode),
            'Owner user ID (UID)': stat.st_uid,
            'Owner group ID (GID)': stat.st_gid,
            'Inode number': stat.st_ino,
            'Number of hard links': stat.st_nlink,
            'File type': 'regular file' if os.path.isfile(self.filepath) else 'unknown',
            'Device ID': stat.st_dev
        }

    def read_and_print_properties(self):
        # Open and "decode" (read as text), but properties are FS-based
        with open(self.filepath, 'r', encoding='utf-8') as f:
            content = f.read()  # Decode as text
            print(f"File content decoded (first 100 chars): {content[:100]}...")
        print("File System Properties:")
        for key, value in self.properties.items():
            print(f"{key}: {value}")

    def write_sample(self, content="CREATE TABLE example (id INT);"):
        with open(self.filepath, 'w', encoding='utf-8') as f:
            f.write(content)
        print(f"Written to {self.filepath}. Updating properties...")
        self.properties = self._get_properties()
        self.read_and_print_properties()

# Example usage:
# handler = SQLFileHandler('example.sql')
# handler.read_and_print_properties()
# handler.write_sample()

1. Java class for .SQL file handling:

This class uses java.nio.file to get attributes. It opens the file, reads (decodes as text), prints properties, and includes a write method.

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.nio.file.attribute.*;
import java.time.Instant;

public class SQLFileHandler {
    private final Path filepath;
    private final BasicFileAttributes attrs;

    public SQLFileHandler(String filepathStr) throws IOException {
        this.filepath = Paths.get(filepathStr);
        if (!filepathStr.toLowerCase().endsWith(".sql")) {
            throw new IllegalArgumentException("File must have .sql extension");
        }
        this.attrs = Files.readAttributes(filepath, BasicFileAttributes.class);
    }

    private void printProperties() {
        System.out.println("File System Properties:");
        System.out.println("File name: " + filepath.getFileName());
        System.out.println("File size (in bytes): " + attrs.size());
        System.out.println("Creation time: " + Instant.ofEpochMilli(attrs.creationTime().toMillis()));
        System.out.println("Last modification time: " + Instant.ofEpochMilli(attrs.lastModifiedTime().toMillis()));
        System.out.println("Last access time: " + Instant.ofEpochMilli(attrs.lastAccessTime().toMillis()));
        // Permissions, owner, etc., require PosixFileAttributes on Unix
        if (FileSystems.getDefault().supportedFileAttributeViews().contains("posix")) {
            PosixFileAttributes posixAttrs = Files.readAttributes(filepath, PosixFileAttributes.class);
            System.out.println("File permissions (mode): " + posixAttrs.permissions());
            System.out.println("Owner: " + posixAttrs.owner());
            System.out.println("Group: " + posixAttrs.group());
        }
        System.out.println("File type: regular file");
    }

    public void readAndPrintProperties() throws IOException {
        // Open and decode (read as text)
        try (BufferedReader reader = Files.newBufferedReader(filepath, StandardCharsets.UTF_8)) {
            StringBuilder content = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null && content.length() < 100) {
                content.append(line);
            }
            System.out.println("File content decoded (first 100 chars): " + content + "...");
        }
        printProperties();
    }

    public void writeSample(String content) throws IOException {
        if (content == null) {
            content = "CREATE TABLE example (id INT);";
        }
        Files.write(filepath, content.getBytes(StandardCharsets.UTF_8));
        System.out.println("Written to " + filepath + ". Updating properties...");
        readAndPrintProperties();
    }

    // Example usage:
    // public static void main(String[] args) throws IOException {
    //     SQLFileHandler handler = new SQLFileHandler("example.sql");
    //     handler.readAndPrintProperties();
    //     handler.writeSample(null);
    // }
}

1. JavaScript class for .SQL file handling:

This is for Node.js (uses fs module). It opens the file, reads (decodes as text), prints properties to console, and includes a write method. For browser, see the drag-drop script above.

const fs = require('fs');
const path = require('path');

class SQLFileHandler {
  constructor(filepath) {
    this.filepath = filepath;
    if (!filepath.toLowerCase().endsWith('.sql')) {
      throw new Error('File must have .sql extension');
    }
    this.properties = this._getProperties();
  }

  _getProperties() {
    const stat = fs.statSync(this.filepath);
    return {
      'File name': path.basename(this.filepath),
      'File size (in bytes)': stat.size,
      'Creation time': stat.birthtime.toString(),
      'Last modification time': stat.mtime.toString(),
      'Last access time': stat.atime.toString(),
      'File permissions (mode)': stat.mode.toString(8),
      'Owner user ID (UID)': stat.uid,
      'Owner group ID (GID)': stat.gid,
      'Inode number': stat.ino,
      'Number of hard links': stat.nlink,
      'File type': stat.isFile() ? 'regular file' : 'unknown',
      'Device ID': stat.dev
    };
  }

  readAndPrintProperties() {
    // Open and decode (read as text)
    const content = fs.readFileSync(this.filepath, 'utf8');
    console.log(`File content decoded (first 100 chars): ${content.substring(0, 100)}...`);
    console.log('File System Properties:');
    for (const [key, value] of Object.entries(this.properties)) {
      console.log(`${key}: ${value}`);
    }
  }

  writeSample(content = 'CREATE TABLE example (id INT);') {
    fs.writeFileSync(this.filepath, content, 'utf8');
    console.log(`Written to ${this.filepath}. Updating properties...`);
    this.properties = this._getProperties();
    this.readAndPrintProperties();
  }
}

// Example usage:
// const handler = new SQLFileHandler('example.sql');
// handler.readAndPrintProperties();
// handler.writeSample();

1. C class (using C++ for class support) for .SQL file handling:

This C++ class uses <sys/stat.h> for properties (Unix-like; for Windows, adapt with _stat). It opens the file, reads (decodes as text), prints properties, and includes a write method.

#include <iostream>
#include <fstream>
#include <string>
#include <sys/stat.h>
#include <ctime>
#include <stdexcept>

class SQLFileHandler {
private:
    std::string filepath;
    struct stat fileStat;

    void getProperties() {
        if (stat(filepath.c_str(), &fileStat) != 0) {
            throw std::runtime_error("Failed to get file stats");
        }
    }

public:
    SQLFileHandler(const std::string& fp) : filepath(fp) {
        if (filepath.substr(filepath.find_last_of(".") + 1) != "sql" &&
            filepath.substr(filepath.find_last_of(".") + 1) != "SQL") {
            throw std::invalid_argument("File must have .sql extension");
        }
        getProperties();
    }

    void readAndPrintProperties() {
        // Open and decode (read as text)
        std::ifstream file(filepath);
        if (!file) {
            throw std::runtime_error("Failed to open file");
        }
        std::string content(100, '\0');
        file.read(&content[0], 100);
        std::cout << "File content decoded (first 100 chars): " << content << "..." << std::endl;
        file.close();

        std::cout << "File System Properties:" << std::endl;
        std::cout << "File name: " << filepath.substr(filepath.find_last_of("/\\") + 1) << std::endl;
        std::cout << "File size (in bytes): " << fileStat.st_size << std::endl;
        std::cout << "Creation time: " << std::ctime(&fileStat.st_ctime);
        std::cout << "Last modification time: " << std::ctime(&fileStat.st_mtime);
        std::cout << "Last access time: " << std::ctime(&fileStat.st_atime);
        std::cout << "File permissions (mode): " << std::oct << fileStat.st_mode << std::endl;
        std::cout << "Owner user ID (UID): " << fileStat.st_uid << std::endl;
        std::cout << "Owner group ID (GID): " << fileStat.st_gid << std::endl;
        std::cout << "Inode number: " << fileStat.st_ino << std::endl;
        std::cout << "Number of hard links: " << fileStat.st_nlink << std::endl;
        std::cout << "File type: regular file" << std::endl;
        std::cout << "Device ID: " << fileStat.st_dev << std::endl;
    }

    void writeSample(const std::string& content = "CREATE TABLE example (id INT);") {
        std::ofstream file(filepath);
        if (!file) {
            throw std::runtime_error("Failed to write file");
        }
        file << content;
        file.close();
        std::cout << "Written to " << filepath << ". Updating properties..." << std::endl;
        getProperties();
        readAndPrintProperties();
    }
};

// Example usage:
// int main() {
//     try {
//         SQLFileHandler handler("example.sql");
//         handler.readAndPrintProperties();
//         handler.writeSample();
//     } catch (const std::exception& e) {
//         std::cerr << e.what() << std::endl;
//     }
//     return 0;
// }