Query on the database must be done in a REBOL dialect very near to standard SQL query. Les requêtes sur la base de données doivent être faite en utilisant un dialecte REBOL vraiment très proche du langage standard de requêtes SQL.

The engine carries out only the following requests: Le moteur de requête supporte uniquement les requêtes suivantes:

SELECT ... FROM ... WHERE ... ORDER BY ...
INSERT ... INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...
CREATE TABLE ... [...] TYPE ...
DROP TABLE ...

SQL-PROTOCOL supports 4 table types (format) :

SQL-PROTOCOL supporte 4 formats de table

• DATA format which is text file containing a REBOL block for each table row
• DATA format texte contenant un bloc REBOL pour chaque ligne de la table
• HEAP format which is for in memory table (transient table) containing a REBOL block for each row
• HEAP format employé pour les table en mémoire (transient table) contenant un bloc REBOL pour chaque ligne
• CSV format which is delimited file (CSVDelimited, TABDelimited or Delimited by any caracter except doublequote (“), newline (^/) or linefeed (^M).
• CSV format de fichier délimité (délimité par virgule (comma), délimité par TAB ou délimité par n’importe quel caractère à l’exception de (”), nouvelle ligne (^/) ou saut de ligne (^M)).
• FIXED format which is fixed length file (Fixed) (not implemented up now)
• FIXED format dont la taille du fichier est fixe (non disponible à ce jour)

By default : Par défaut:

• DATA format is used when not specidfied in the create table query.
• DATA : ce format est utilisé quand il n’y a pas de spécification de format dans une requête de création de table
• CSV table use “;” as delimiter and header is on (for Excel compatibility)
• CSV : ces tables utilisent “;” comme séparateur et les en-têtes sont activées (pour la compatibilité avec Microsoft EXCEL)

SQL-PROTOCOL implements the following queries :

SQL-PROTOCOL implémente les requêtes suivantes:

SELECT which allows you to select rows from table(s).

SELECT qui vous permet de choisir les lignes d’une(des) table(s)

INSERT which allows you to insert row(s) in a table.

INSERT qui vous permet d’insérer une(des) ligne(s) dans une table

UPDATE which allows you to update row(s) in a table.

UPDATE qui vous permet de modifier la(les) ligne(s) dans une table

DELETE which allows you to delete row(s) from a table.

DELETE qui vous permet de suppprimer une(des) lignes d’une table

• Columns must be either alias.column, alias/column or row/index (ex. a.c1, a/c1 or a/1)
• Les colonnes doivent être soit alias.colonne, alias/colonne ou ligne/indice (ex: a.c1, a/c1 ou a/1)
• Table & Alias couple can be placed in a block (ex. [my-table my alias]) or with the AS clause (ex. my-table AS myAlias)
• Le couple table et alias peuvent être placé dans un block (ex: [ma-table monAlias]) ou avec la clause AS (ex. ma-table AS monAlias)
• Columns & Asc | Desc couple must be placed in a block (ex. [1 ASC] [a.c2 DESC])
• Le couple Colonne et Asc | Desc doivent être placé dans un block (e: [1 ASC] [a.c2 DESC])
• LIKE and IN are not implemented (will be done later certainly)
• “LIKE” et “IN” ne sont pas implémentés (ils le seront prochainement)
• ORDER BY, HAVING and statistical function (COUNT, MAX, …) are not implemented (will be done later certainly)
• “ORDER BY’, ”HAVING“ et les fonctions statistiques (”COUNT“, ”MAX“, …) ne sont pas implémentées (elles le seront prochainement)
• AS clause for columns is not implemented (needed and will be done probably soon)
• La clause ”AS" pour les colonnes n’est pas non plus implémentée ( requise et sera réalisée prochainement)
• only one select strategy is implemented (table scan / nested loop) so poor response time can occurs
• Une seule stratégie de selection est implémentée (scan de la table ) les performances de réponse de la base de données peuvent s’en trouver affectées.

Opening a database is very simple: L’ouverture de la base de données est vraiement simple

    open sql:my-database

In this case, the protocol open the database contained in the directory my-database.

Dans ce cas, le protocol ouvre la base de données contenue dans le dossier my-database

• If this directory does not exist, it is created.
• Si ce dossier n’existe pas, il est créé.
• If a schema.ini or schema.ctl file is found in this directory, this file is used
• Si un fichier schema.ini ou schema.ctl est trouvé dans ce dossier, ce fichier est utilisé.
• If the schema file does not exists, it is created.
• Si le fichier schéma n’existe pas, il est créé.

You can specify the standard /new refinement in order to reinitilize the database (this is not implemented up to now but I will make it soon):

Vous pouvez spécifier le rafinement standard /new afin de réinitilizer la base de données (ceci n’est pas encore implémenté à ce jour mais le serra très prochainemen):

    open/new sql-my-database

In this case, all the file contained in the directory are erased and a new schema.ctl file is created. Dans ce cas, tout les fichier contenus dans le dossier sont effacés et un nouveau fichier schéma.ctl est créé.

Other refinement will cause an error (error are not thrown up to now but I will change it soon).

L’utilisation d’autres raffinements causeront une erreur (les erreures ne sont pas retournées mais je corrigerai ça prochainement)

When you open a database, you can also precise the file name of the schema :

Quand vous ouvrez une base de données vous pouvez aussi préciser le nom du fichier de schéma :

    open sql:my-database/my-schema.txt

In this case, if the schema file does’nt exist, it is created. It’s the same with the directory and the /new refinement is also authorized.

Dans ce cas, si le fichier de schéma n’existe pas, il est créé. De façon analogue au fonctionnement des dossiers et le raffinement /new est aussi autorisé

The SQL dialect for SELECT is very similar to the SQL To query the database, you have to insert the query block into the port and next to get the result either by a copy, a first or a pick.

Le dialecte SQL pour la focntion SELECT est vraiement similaire à la requette SQL pour intérroger la base de données, vous pouvez inserrer le bloc de requête dans le port et en suite obtenir le résultat soit par un copy, soit par un first ou par un pick.

Quick example: Example simple:

    db: open sql:my-db
    insert db [SELECT * FROM my-table]
    foreach item copy db [probe item]
    close db

To select all columns from a table you can use the * :

Pour choisir toutes les colonnes d’une table pouvez utiliser *:

    insert db [SELECT * FROM a]

If you want to select some specific columns, you have to use the notation alias.column:

Si vous voulez selectionner quelques colonnes spécifiques, vous devez utiliser la notation alias.colonne:

    insert db [SELECT a.c1 a.c2 FROM a]

The same notation is mandatory in the WHERE clause :

La même notation est obligatoire dans une clause WHERE:

    insert de [SELECT * FROM a WHERE a.c1 = 2]

You can also give an alias name to a table. In this case, you have to set the table / alias pair into a block :

Vous pouvez aussi donner un nom d’alias a une table. Dans ce cas vous devez fournir la paire table / alias dans un bloc:

    insert db [SELECT t1.c1 t1.c2 FROM [a t1]]

Nota Bene Remember, it’s a dialect so you have to respect REBOL syntax and don’t forget to place a space before an after the = or all other operator (+, -*, /, >, <, ).

Rappel, c’est un dialecte donc vous devez respecter la syntaxe REBOL et ne pas oublier de placer un espace avant et après le = ou tout autre opérateur (+, -, *, /, >, <, ).

To do a join, you do simply :

Pour faire un JOIN, faire simplement:

    insert db [SELECT * from a b]

If you want only some columns, you have to give the list (always with the notation alias.column) :

Si vous voulez uniquement quelques colonnes, vous devez fournir une liste (toujours aves la notation alias.colonne) :

    insert db [SELECT a.c1 a.c2 b.c1 FROM a b WHERE a.c1 = b.c2]

If you want select some columns from a table and all from another, you can use the alias.* notation :

Si vous voulez choisir quelques colonnes depuis une table et toutes c’est d’une autre table, vous pouvez utiliser la notation alias.* :

    insert db [SELECT a.c1 b.* FROM a b]

When you join a table with itself, you have to give an alias for each table :

Quand vous réaliser la jointure d’une table avec elle même, vous devez fournir un alias pour chacune des tables :

    insert db [SELECT t1.c1 t2.c1 FORM [a t1] [a t2] WHERE t1.c1 = t2.c2]

Nota Bene Remember, it’s a dialect so you have to respect REBOL syntax and don’t forget to place a space before an after the = or all other operator (+, -*, /, >, <, ).

Rappel, c’est un dialecte donc vous devez respecter la syntaxe REBOL et ne pas oublier de placer un espace avant et après le = ou tout autre opérateur (+, -, *, /, >, <, ).

You can give an ORDER BY clause in order to sort the result.

Vous pouvez fournir une clause ORDER BY afin de trier le résultat.

You can either give the column(s) number

Vous pouvez fournir les indices des colonnes

    insert db [SELECT * FROM a ORDER BY 1 2 3]

Or you can give the columns name by its alias.columns name :

Ou vous pouvez fournier une colonne désignée par son nom alias.colonne :

    insert db [SELECT * FROM a ORDER BY a.c1 a.c2 a.c3]

By default, the engine sort the result in ascending order, but you can sort it in descending order by giving the pair alias.columns ASC or DESC in a block :

Par défaut, le moteur tri de le résultat par ordre croissant, mais vous pouvez le trier dans l’ordre décroissant en fournissant la paires alias.colonnes ASC ou DESC dans un bloc :

    insert db [SELECT * FROM a ORDER BY [a.c1 ASC] [a.c2 DESC]]

ASC or DESC can also be given with columns index :

ASC ou DESC peuvent aussi être donné avec les indices des colonnes :

    insert db [SELECT * FROM a ORDER BY [1 ASC] 2 [DESC]]

Nota Bene Remember that GROUP BY, HAVING and statistical function (COUNT(…), MAX(…), etc…) are not implemented. It’s the same for LIKE and IN word.

Rappel, GROUP BY, HAVING et les fonctions statistiques (COUNT(…), MAX(…), etc…) ne sont pas implémentées. Il en va de même pour les mots LIKE et IN.

For INSERT, the dialect is quite simple.

Pour INSERT, le dialecte est vraiement simple.

Quick example:

Example rapide:

    insert db [INSERT INTO a VALUES [1 2 3]]

When you insert a row in a table, you must give a value for all columns otherwise, they are set to none (NULL).

Quand vous insérer une ligne dans une table, vous devez fournir une valeur pour toutes les colonnes autrement, elles sont configurées à none (NUL).

When you give the columns list, you must give all the columns otherwise the mussing columns are set to none (NULL).

Quand vous donner une liste de colonne, vous devez fournir toutes les colonnes autrement la colonne manquante est configurée à none (NUL).

The value are inserted in the table columns order even if the columns are in another order in the columns list.

La valeur est insérée dans l’ordre des colonnes d’une table même si les colonnes sont dans un ordre différent dans le liste des colonnes

You can give many rows after the VALUES clause just by giving many rows (one block of value for each rows)

Vous pouvez fournir plusieurs lignes après la clause VALUES juste en donnant plusieurs lignes (un bloc de valeurs pour chaque lignes)

You can also insert rows with values selected from another query (not implemented now)

Vous pouvez aussi insérer des lignes avec des valeurs choisies à partir d’une autre requete (pas implémenté actuellement)

Inserting one row without specifying the column list :

    insert db [INSERT INTO a VALUES [1 2 3]]

Inserting one row with a column list :

    insert db [INSERT [c b a] INTO a VALUES [3 2 1]]

Inserting many rows at a time :

    insert db [INSERT INTO a VALUES [1 2 3] [2 3 4] [3 4 5]]

Inserting rows selected from another query (this is not implemented but it will be soon because it’s easy to make) :

    insert db [INSERT INTO temp SELECT * FORM a b WHERE a.c2 = b.c1 ORDER BY a.c1 b.c1]

For UPDATE, the dialect is also very similar to SQL.

Quick example :

    insert db [UPDATE a SET c2: 3 WHERE c1 = 1]

You can update only one table at a time. If the WHERE clause correspond to many rows, all the corresponding rows are updated The where clause is optional. If WHERE is ommited, all the row of the table are updated.

Nota Bene You must use the simple column notation, not the alias.columnnotation

If you want, you can set many columns at a time :

    insert db [UPDATE a SET c2: 3 c3: 4 WHERE c1: 1]

Nota Bene Remember, it’s a dialect so you have to respect REBOL syntax and don’t forget to place a space before an after the = or all other operator (+, -*, /, >, <, ).

For DELETE, it’s very simple.

Quick example :

    insert db [DELETE FROM a WHERE c1 = 1]

• If you ommit the WHERE clause, all the rows are deleted.
• If the WHERE clause correspond to many rows, all the corresponding rows are deletes

Nota Bene You must use the simple column notation, not the alias.columnnotation

Deleting all rows in a table :

    insert db [ DELETE FROM a ]

SQL-PROTOCOL implements the following DDL (Data Definition Language) queries:

• CREATE TABLE which allows you to create a new table.
• DROP TABLE which allows you to delete a table.

• FIXED table is not implemented (up now, only DATA, HEAP and CSV table can be used)
• columns type and length (CHAR, NUMBER, etc…) is not implemented for DATA and HEAP table(probably will be never done)
• sql-protocol load all the data in memory, so table with many thousand of rows can be very slow or fail (I am waiting for the rif protocol which will be integrated as soon as possible)

For CREATE TABLE the dialect is extended in order to have a better control on the file creation.

Quick example .

    insert db [CREATE TABLE a [c1 c2 c3]]

• If you ommit the table type, DATA format is used
• You can specify the type of the table (DATA, HEAP, CSV or FIXED)
• If you ommit the columns type and length, untyped columns is create (can contain any REBOL type of data)
• Columns type is depending of table type.
• If you specify the IF NOT EXIST clause, the table is created only if it doesn’t exist, otherwise an error is thrown

Creating a table without column type :

    insert db [ CREATE-TABLE table-name [C1 C2 C3] ]

As REBOL is an untyped programming language, it’s more simple to ommit the columns type for DATA or HEAP protocol.

Creating a DATA or HEAP table with full columns description (this is not implement up to now and I will make later) :

    insert db [ CREATE TABLE a [
        [F01 binary!]
        [F02 bitset!]
        [F03 block!]
        [F04 char!]
        [F05 datatype!]
        [F06 date!]
        [F07 decimal!]
        [F08 email!]
        [F10 file!]
        [F11 get-word!]
        [F12 hash!]
        [F13 hex!]
        [F14 image!]
        [F15 integer!]
        [F16 issue!]
        [F17 list!]
        [F18 lit-path!]
        [F19 lit-word!]
        [F20 logic!]
        [F21 money!]
        [F22 pair!]
        [F23 paren!]
        [F24 path!]
        [F25 refinement]
        [F26 set-path]
        [F27 set-word]
        [F28 string]
        [F29 tag]
        [F30 time]
        [F31 tuple]
        [F32 url]
        [F33 word]
    ] TYPE = HEAP ]

You can create a CSV table without giving the columns type and length. In this case, you can put what you want in each row but this is not supported by {Microsoft text driver).

    insert db [ CREATE TABLE a [C1 C2 C3] type = CSV]

By default CSV table include the header, the format is Delimited and use semicolumn delimiter (;).

Creating a CSV table with full columns description. By default CSV table include the header, the format is Delimited and use semicolumn delimiter (;).

    insert db [ CREATE TABLE a [
        [F01 Bit]
        [F02 Byte]
        [F03 Char Width 255]
        [F04 Currency]
        [F05 Date]
        [F06 Float]
        [F07 Integer]
        [F08 LongChar]
        [F09 Short]
        [F10 Single]
    ] TYPE = CSV ]

Creating a CSV table with full columns description and specific table type

    insert db [ CREATE TABLE a [
        [C1 Integer]
        [C2 Char 255]
        [C3 Date]
    ] TYPE = [ CSV ColNameHeader: false format: CSVDelimited ] ]

• header can be true or false
• format can be Delimited, CSVDelimited, TABDelimited
• delimiter can be any char or string
  • except doublequote (") line-feed (^M) or newline (^/)
  • is mandatory for Delimited format
  • must not be specified for CSV or TAB Delimited

Nota Bene TYPE [….] is still not implemented, only TYPE CSV is allowed

Creating a FIXED table with full columns description

    insert db [ CREATE TABLE a [
        [F01 Bit 1]
        [F02 Byte 3]
        [F03 Char 255]
        [F04 Currency 15]
        [F05 Date 6]
        [F06 Float 21]
        [F07 Integer 11]
        [F08 LongChar 32]
        [F09 Short 5]
        [F10 Single 255]
    ] TYPE = FIXED ]

• With FixedLength table, if you don’t give the column type, CHAR 255 is used.
• format can be Delimited, CSVDelimited, TABDelimited

You can also precise if the header is saved in the file or not:

    insert db [ CREATE TABLE a [
        [C1 Integer 11]
        [C2 Char 255]
        [C3 Date 6]
    ] TYPE = [ FIXED ColnameHeader: true ] ]

• ColNameHeader can be true or false

To drop a table, the dialect is very simple:

    insert db [ DROP TABLE table-name ]

If the table doesn’t exist, an error is thrown.

You can if needed use directly the DATA, HEAP, CSV and FIXED protocol. These protocols are named file protocol.

DATA, HEAP, CSV and FIXED protocol are usefull to store and access REBOL datatype block thru protocol.

DATA to store REBOL values as block of values into a file. HEAP to store REBOL values as block of values into memory. CSV to store REBOL value as character separated records. FIXED to store REBOL value as fixed length record.

The advantage is to have with a standard input, output and interface access to many file format. These normalized access simplify file read and write and reduce the debuggind time. It’s allows you to insert, update or delete records as simple as managing blocks.

The usage of file protocol differs form one protocol to the other, only when you open the file. All other access are standized.

To open a DATA or HEAP file, you use the standard open function :

  my-file: open DATA:my-datafile.data

If the extension is ommited, the .data exetension is automaticaly added. If the file doesn’t exist, it is created. If you use the /new extension, the file is cleared during the open. When /line and /direct refinement are used an error is thrown.

To open a CSV or FIXED file, you use the standard open function :

  my-file: open CSV:my-datafile.data

If the file doesn’t exist, it is created. If you use the /new extension, the file is cleared during the open. When /line and /direct refinement are used an error is thrown. If you want to precise the file structure, you have to make the port before opening it and to give the file schema in a block :

  my-file: open make port! [scheme: 'csv schema: [
      ColNameHeader: true
      format: "Delimited" ; or CSVDelimited or TABDelimited
      delimiter: ";"      ; or any character except doublequote ("), line feed (^M) and newline (^/)
      cols: [
          [C1 Char width 10]
          [C2 Integer]
      ]
  ]

If you don’t give the schema, the CSV protocol detect automaticaly the fileds and their type. By default, char field have a length of 255. For FIXED (FixedLength) file, the schema and field length are mandatory.

To insert data, use the standard insert function :

  insert my-file ["data 1" "data 2" 3 4]

If you want to insert many records, you can insert a block containing one or more blocks :

  insert my-file [
      ["Record 1" 1 2 3]
      ["Record 2" 2 3 4]
  ]

If you want to insert one record containing blocks, you can une the /only refinement:

  insert/only my-file [
      ["Value 1"]
      ["Value 2"]
  ]

To insert data at end of the file, use the standard append function :

  insert my-file ["data 1" "data 2" 3 4]

To insert data for example at the third position, use the standard at function :

  my-file: at my-file 3
  insert my-file ["data 1" "data 2" 3 4]

To change data, use the standard change function :

  my-file: open DATA:my-data-file.data
  my-file: at my-file 3
  change my-file ["data 1" "data 2" 3 4]
  close my-file

This example change the third record.

If you want to change many records, you can change a block containing one or more blocks :

  change my-file [
      ["Record 1" 1 2 3]
      ["Record 2" 2 3 4]
  ]

If you want to change one record containing blocks, you can une the /only refinement:

  change/only my-file [
      ["Value 1"]
      ["Value 2"]
  ]

If you want to change for example 1 by 3 record, you can use the /part refinement

  change/part my-file [
      ["Record 1" 1 2 3]
      ["Record 2" 2 3 4]
      ["Record 3" 4 5 6]
  ] 1

To remove record (s), use the standard remove function :

  remove my-file

If you want to remove many record, use the /part refinement. For example, to to remove 3 records, do this :

  remove/part my-file 3

To get data, you can use either the standard copy or pick function

  probe pick my-file 1
  probe copy my-file

With the copy function you can use the /part refinement:

  probe copy/part my-file 5

With file protocol, you can it as for series

You can sort the records in the file. It’s very usefull and can be done like this

  my-file: open data:my-file.data
  sort my-file
  close my-file

With file protocol, you can manage file and directory. This can done in the same way as standard file.

You can use the following function :

query to obtain file or directory information exists to verify if a file or directory exists make-dir to create a directory delete to delete a file or directory

  query data:my-file.data
  exsits data:my-file.data
  make-dir data:my-directory/
  delete/any data:my-directory/*.data

For each database, the table file structure is stored in a file.

schema.ctl is used for default database type. It’s a standard REBOL script file which is simple loaded when the database is opened. schema.ini is used for {Microsoft text driver} database. It’s a standard .ini file

All the table files are stored in the same directory as the schema file.

In schema.ctl file, SQL-PROTOCOL store the schema structure in the following manner :

  table-name: [
      scheme: table-file-schema
      target: table-file-name
      schema: [
          cols: [
              col-specs-1
              ...
              col-specs-n
          ]
          property-1: value-1
          ...
          property-n: value-n
      ]
  ]

  col-specs = col-name
  col-specs = [col-name col-type col-length]

The schema.ctl is not implemented like this now, but this will be done soon. Actualy, the schema is stored in a database.ctl file with a different format

Example schema for DATA or HEAP protocol :

  data-table: [
      scheme: 'DATA
      target: %data-table.data 
      schema: [
          cols: [
              col-1            ; untyped column
              [col-2 date!]    ; a date
          ]
      ]
  ]

Example schema for CSV or FIXED protocol :

  csv-table: [
      scheme: 'CSV
      target: %csv-table.csv
      schema: [
          ColnameHeader: false
          format: 'Delimited
          delimiter: ";"
          cols: [
              col-1            ; untyped column
              [col-2 char 32]  ; 32 char
          ]
      ]
  ]

The schema.ini file is compatible with the {Microsoft text driver (*.csv *.txt)} You can find the specification here :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

The schema.ini is not implemented now, but this will be done soon.

Here is an example of schema.ini with all possible columns type :

  [csvdelimited.csv]
  ColNameHeader=True
  Format=CSVDelimited
  MaxScanRows=0
  CharacterSet=OEM
  Col1=F01 Bit
  Col2=F02 Byte
  Col3=F03 Char Width 255
  Col4=F04 Currency
  Col5=F05 Date
  Col6=F06 Float
  Col7=F07 Integer
  Col8=F08 LongChar
  Col9=F09 Short
  Col10=F10 Single
  [fixedlength.txt]
  ColNameHeader=True
  Format=FixedLength
  MaxScanRows=0
  CharacterSet=OEM
  Col1=F01 Bit Width 1
  Col2=F02 Byte Width 3
  Col3=F03 Char Width 10
  Col4=F04 Currency Width 15
  Col5=F05 Date Width 6
  Col6=F06 Float Width 21
  Col7=F07 Integer Width 11
  Col8=F08 LongChar Width 32
  Col9=F09 Short Width 5
  Col10=F10 Single Width 255

I decided to use file protocol to acces to table data. This choice is motivated by two reason :

The interface of all protocol is standardised, so I didn’t need to reinvente the wheel These protocol can be use also outside the database so it can be usefull for other application. One important point was to implemement the directory capability so I can reused the standard function for the creation and deletion of table file.

Also, by implementing the HEAP protocol, I could use always the same interface even if data could be stored in a simple block in memory.

I use a unique SELECT strategy, it’s a table scan / nested loop algorithm.

The algorithm is quiet simple, for each table in the SELECT, the script scan all the row of the table, and for each row, scan all the rows of the next table, and when it’s the last table, the where clause is simply applied.

The speed performance is not verw good but that’s enough with small table and few join. In fact, what is most time consuming, it’s the insert in the resulting block when thousand of rows are inserted.

DATA, HEAP and CSV protocols are quiet well tested. SQL protocol is quiet well tested with DATA, HEAP and CSV table. SQL SELECT, UPDATE and DELETE are quiet well tested SQL CREATE and DROP are tested but they need more tests.

Incomplete list

• Implementation of LIKE and perhaps IN
• Implementation of GROUP BY, HAVING ans statisical function (COUNT, MAX, …)
• Implementation of column AS clause in the SELECT
• Implementation of SELECT instead of VALUES clause in INSERT query (INSERT INTO … SELECT …)
• Implementation of the open/NEW refinement for SQL:..
• Raise an error when other refinement are used with open sql:…
• Implementation of column type and length.
• Improvement of CSV protocol for an automatic detection of columns type when schema is not given.
• Implementation of RIF protocol. Perhaps, I will make a small protocol which handle only the RIF table format but

certainly with a completely database organisation in order to store all the tables and also the schema in one unique file.

• Implementation of FixedLength file (FIXED protocol).
• Implementation of columns without alias.column notation and AS clause for columns in SELECT query
• Implementation of SQL string request instead of SQL dialect (or keep both)
• More and more, improve performance and simplify the script

      0.0.1 [15-Sep-2004 {Initial alpha version} marco@adyreb.org]
      0.0.2 [16-Sep-2004 {Documentation & order bug correction & tests} marco@adyreb.org]
      0.0.3 [17-Sep-2004 {Change path notation to dot notation for columns} marco@adyreb.org]
      0.0.4 [20-Sep-2004 {Add table alias and where normalization} marco@adyreb.org]
      0.0.5 [21-Sep-2004 {Interface stabilization, sort improvement} marco@adyreb.org]
      0.0.6 [22-Sep-2004 {First optimization and select * or select table.* implementation} marco@adyreb.org]
      0.0.7 [27-Sep-2004 {RAPID Documentation, to-rebol-cols debugging and order interface change} marco@adyreb.org]
      0.1.0 [28-Sep-2004 {Change in provision of SQl protocol - DATA: protocol & database object} marco@adyreb.org]
      0.1.1 [29-Sep-2004 {Change of test case & documentation & SQL: protocol} marco@adyreb.org]
      0.1.2 [30-Sep-2004 {Dismiss of RAPID documentation & various changes} marco@adyreb.org]
      0.1.3 [04-Oct-2004 {Transient debug & developpment of sql INSERT, UPDATE & DELETE} marco@adyreb.org]
      0.1.4 [06-Oct-2004 {Change in UPDATE syntaxe and DELETE debug} marco@adyreb.org]
      0.1.5 [08-Oct-2004 {Major change to resolve the path problem} marco@adyreb.org]
      0.1.6 [11-Oct-2004 {Implements HEAP protocol for transient table} marco@adyreb.org]
      0.1.7 [12-Oct-2004 {Implements CREATE/DROP TABLE and HEAP & DATA optimization} marco@adyreb.org]
      0.1.8 [13-Oct-2004 {Improve path an target management & add get-url function} marco@adyreb.org]
      0.2.0 [13-Oct-2004 {First alpha published on www.rebol.org} marco@adyreb.org]
      0.2.1 [15-Oct-2004 {Change on the data protocol - implement directory management} marco@adyreb.org]
      0.2.2 [18-Oct-2004 {Try to improve select performance} marco@adyreb.org]
      0.2.3 [27-Oct-2004 {Change on the data and heap protocol - improve directory management} marco@adyreb.org]
      0.3.0 [11-Nov-2004 {Add CSV protocol, change on DATA protocol and preparation to FIXED protocol} marco@adyreb.org]
      0.3.1 [19-Nov-2004 {Various bug correction and header extension} marco@adyreb.org]
      0.3.2 [14-Nov-2004 {Add SORT on DATA and CSV protocol plus bug correction (thank Shad)} marco@adyreb.org]
      0.4.0 [14-Dec-2004 {Implement new schema.ctl and extend TYPE = clause} marco@adyreb.org]
      0.4.1 [20-Dec-2004 {Implement schema.ini and extend test case} marco@adyreb.org]
      0.4.2 [10-Jan-2005 {Test, debug and correction on port/local/table and schema.ctl} marco@adyreb.org]
      0.4.3 [13-Jan-2005 {Lot of test, debug and correction on compatibility with Microsoft Text Driver} marco@adyreb.org]
      0.5.0 [17-Jan-2005 {Alpha version published on www.rebol.org} marco@adyreb.org]

You can find the sql-protocol.zip archive in the french framework here :

http://www.agora-dev.org/forums/view.php?site=rebol&bn=rebol_protocols&key=1095276031

The archive contains :

• sql-protocol.r The REBOL sql database protocol
• launch.r is a simple script to launch REBOL, load the protocol and open the database
• RUn.r The Rebol Unit tool
• test-unit.r To run one or many of the test case
• test-suite.r To run all the test cases
• bench-select-01.r To launch the bench case file
• bench-select-01.test A bench case file (for RUn) wich build table with 1000 rows and 1’000’000 of row
• bench-select-02.r To launch the bench case file
• bench-select-02.test A bench case file (for RUn) wich build table with 1000 rows and 1’000’000 of row
• /test-suite contains all the test case.
• /my-db is the database for the test case
• /my-text-db is the database for the Microsoft text driver compatibility test
• /my-db-save contains the initial database for the test case
• /my-text-db-save contains the initial database for the Microsoft text driver compatibility test
• /bench-db is the database used for bench case
• sql-parse.r a tentative of sql request parser
• sql-parse-request.r the parser that must be done
• test-fct-parse-request.test the test case to check the sql-request-parse

Text File Format.doc The microsoft CSV file specification