Reference for unit 'sdsmain'

uses

substrings,

fileshare,

pwufileutil,

sysutils;

Notes

SDS (since Powtils v 1.5.x) is partially SQL-compliant electronic table manager with human-readable text file format. It is not a real database. It matches simple and average tasks rather well but it is NOT designed for complex multi-table relations. It implements basic features of SQL'92 but it has no keys, stored procedures, triggers, etc. You'd better use specialized database servers (Firebird, MySQL, PostgreSQL, MSSQL, Oracle, etc.) for difficult tasks.

SDS works with tables, not databases. Each table is a single human-readable file. If you want to learn more about SDS file format read SDS file format description. Files are locked on access which secures i/o operations. The only weak side in performance is that it can't lock and rewrite file areas, it has to rewrite whole file on INSERT, DELETE and UPDATE operations. Other sides are optimized from the point of CPU and memory usage.

SDS implements some simple SQL'92 standards but not all of them. Common CREATE, DROP, INSERT, DELETE, UPDATE and SELECT queries are supported. It also has its own features. Read SDS2/SQL Manual to learn more about SDS queries. Types

  // Query result handle
  SDSResult = Pointer;

  // Result row handle
  SDSArray = Pointer;

  // Column information handle
  SDSColumnInfo = Pointer;

SDS File Format

An SDS file looks like this:

  This is line one containing a comment. Then the next lines..
  number of fields
  last insert id
  number of rows
  
  column name
  column type

  row0 cell0
  row0 cell1
  row0 cell2
  row1 cell0
  row1 cell1
  row1 cell2
  row2 cell0
  row2 cell1
  row2 cell2

So for example, here is a real world SDS file from PasForum:

  -- Simple Data Storage File --
  6
  1
  2
  
  id
  2
  aid
  2
  name
  1
  dscr
  1
  date
  6
  posts
  2

  0
  1
  First post
  testing
  2005-10-23 17:27:31
  1
  1
  2
  Test message
  forum test
  26-09-2007 02:48:50
  1

Lines are separated with end-of-line characters. You can separate whole table into 3 parts: header, column info and data. Header data is stored as is. Column data contains several pairs of rows. First row of each pair is SDS-encoded field name, second is number standing for column data types. Numbers and types are:

1 = VARCHAR/TEXT/STRING (string data with unlimited length)
2 = INT/LONGINT (4-byte integer)
3 = FLOAT/REAL/DOUBLE (8-byte double precision float)
4 = DATE (date in YYYY-MM-DD format)
5 = TIME (time in HH:MM:SS format)
6 = DATETIME (date and time in YYYY-MM-DD HH:MM:SS format)

Data is SDS-encoded. The only difference from plain text is that CR (carriage return) and LF (line feed) characters are replaced with #SDS_CHAR_CR# and #SDS_CHAR_LF#. If you want to learn more about SDS file format, just run a few queries and open table file with any text editor.

Supported SQL

See SDS Supported Sql

Tips and Tricks

Use SdsEscape to filter all data used in SDS queries to protect your code from SQL-injection.
Using Escape() function is still not 100 percent secure and unfortunately our only recommendation is to wait until SDS procedural access is available so that no SQL is required, and no SQL injections will occur. This may be available in version 1.7.X of Powtils.
Don't forget to free all memory your result/row/colinfo pointers allocate with SdsFreeResult, SdsFreeRow and SdsColumnFree.
Take care optimizing your queries and conditions to get less execution time. For example try not to SELECT * FROM. Instead SELECT specific FROM.