Documenting the sqlite data base TABLEs?

Databases
1

I was working with SQLite Data Base structures and…

Is there any comments that can be set in the file (about the TABLEs / Fields, for future reference) ?

I do not saw anything that looks like that.

Until recently, I created using screen shots and data I get using:
TableColumns(TableName As String) As RowSet

and save the whole as pdf for future reference.

I also have a custom application that displays the list of available TABLEs (including sqlite_master and sqlite_sequence if one exist) and their Columns (Fields).

The only, alternative (so the information will always be with the .sqlite file) is to add a suffixed “_Comments” TABLE that will hold the comments about the TABLE.

What are you doing for that ?
(How do you save the data base inside explanations ?)

I mean outside, not in the Xojo project.

2

You could add comments to the schema for documentation.

3

Thanks Vince.

Can you explain a bit more, please ?

4

Comments in a schema can be inserted in 2 formats.

A single line starts with a # such as:

This is a schema comment

Or you can use the old style C comments such as:

/*******************************************************************************
Create Tables
********************************************************************************/

5

Nope. Don’t use #. SQL Comment Syntax

-- One line comment

/*
      Multi-line comment
      Multi-line comment
*/
6

Rick, You are absolutely correct. Guess I’ve bee using Filemaker too long.

As Maxwell Smart would say, “Sorry about that.”

1 Like
7

Thank you guys.

I didn’t mean inside the project source, but inside the sqlite file, so… in some months, years, I have its documentation withing the file.

Unless it is a bad idea ?

8

If you want it within the database, you need a table to put it in.

9

That was my conclusion.

I had hopes something else may exists.

Thank you all.

10

SQLite stores your creation schema including comments you can consult later.

E.g.

If you execute this SQL, creating a table, with line endings with Chr(10) (this is very important) :

-- outside content should be lost
/* lost lost */
CREATE TABLE "my_data" (
/*
** Let's describe this table 
** That's just an example    
*/
    [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, -- just the ID
    [name] NVARCHAR(160)  NOT NULL, -- Put names here
    [code] INTEGER  NOT NULL        -- a code...
);

And later you query:

SELECT sql 
FROM sqlite_schema 
WHERE name = 'my_data';

You will receive this string in the sql field, including your annotations:

CREATE TABLE "my_data" (
/*
** Let's describe this table 
** That's just an example    
*/
    [id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, -- just the ID
    [name] NVARCHAR(160)  NOT NULL, -- Put names here
    [code] INTEGER  NOT NULL        -- a code...
)