SQLDBRestBridge: what changed

Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		## Purpose
2	2
3	3		More and more applications move to Web: today Pas2JS allows to program pascal for the web.
4	4
5	5		Data needs to be stored on a server, and REST is the architecture of choice these days for doing so. Most often, the data ends up in a database.
6	6
7	7		So, an easy way to make data available through a REST service is needed.
8	8
9	9		The `SQLDBRestBridge` unit in fpc/packages/fcl-web/src/restbridge/sqldbrestbridge.pp offers a means to expose any database that can be accessed by SQLDB using a REST API.
10	10
11	11		It is not meant as a general REST API framework, for this other frameworks exist. It is also not a RPC framework.
12	12
13	13		The design is such that a complete database can be exposed using a single line of code, with reasonable default settings.
14	14
15	15		### What does it do ?
16	16
17	17		Basically, `SQLDBRestBridge` can be used in a FCL-Web application to turn it into a REST server:
18	18
19	19		The server will then accept the usual REST GET/POST/PUT/Delete commands and automatically translate these to the appropriate database CRUD (Create/Read/Update/Delete) statements.
20	20
21	21		It can be used simply: one line of code is enough to expose a complete database in this way.
22	22
23	23		Or it can be completely customized and fine tuned: The schema can be completely specified: all SQL statements can be specified, field lists can be defined, aliases can be given, business rules can be implemented.
24	24
25	25		The resulting server can be used in a client/server setup (using pas2js as a client, for example) to serve data without needing to writing all the plumbing code needed in a REST server.
26	26
27	27		### What it does not do ?
28	28
29	29		- This is a server-side technology.
30	30
31	31
32	32		To consume the data served by this component, you need to write a client.
33	33
34	34		No assumptions are made about the client, except that it knows how to execute HTTP requests, and that it understands one of the available data formats.
35	35
36	36		- This does not expose objects as REST resources.
37	37
38	38
39	39		Using some tricks, this could be done, but this is not a design goal.
40	40
41	41		Eventually, all data ends up in a database, and this component uses a direct mechanism to achieve that.
42	42
43	43		## Features
44	44
45	45		### SQL Database side.
46	46
47	47		1. Every REST resource is defined by up to 4 SQL statements, corresponding to the 4 CRUD operations.
48	48		2. The CRUD Statements can be auto generated on the fly, based on a table name and field definitions.
49	49		3. For every field, an alias can be provided.
50	50		4. The following client-side types are available, automatically mapped from the native database type:
51	51		1. String
52	52		2. 64-bit integer
53	53		3. 32-bit integer
54	54		4. Boolean
55	55		5. Date, Time, DateTime
56	56		6. Blob (base64-encoded)
57	57		5. Support for a sequence to generate IDs is built-in.
58	58		6. Full control over which operations (GET,POST,PUT,DELETE) are allowed.
59	59		7. Resources are collected in a schema.
60	60		8. Multiple schemas can be attached to the service.
61	61		9. Multiple databases (connections) can be defined.
62	62		10. A schema can be tied to a single connection, or connections can share a schemas (use case: a different connection per client).
63	63		11. Business processor components can be hooked to a resource, to make it easier to implement business logic.
64	64		12. SQL Statements can contain parameters, values for the parameters will be picked up from the request URL.
65	65		13. Support to get custom datasets.
66	66		14. Support for client-side provided SQL SELECT statements (optional, disabled by default)
67	67		15. Full support for configuration through an `.ini` file out of the box.
68	68
69	69		### HTTP side
70	70
71	71		1. Authentication is handled using the HTTP protocol.
72	72		2. Basic authentication is included by default, but is completely pluggable.
73	73		3. Basic authentication can look up valid users in a database (by default the database being exposed)
74	74		4. Output format can be fixed or detected per request (`?fmt=format`). Detection based on content-type is also available.
75	75		5. list of fields to include in output can be specified in the URL:\
76	76		`fl=field1,field2`
77	77		6. Field list of fields to exclude from output can be specified in the URL:\
78	78		`fe=field1,field2`
79	79		7. Various output formats are available out of the box
80	80		1. JSON (the default)
81	81		2. XML (a custom format)
82	82		3. CSV (for in and output)
83	83		4. CDS (Format used by Delphi `TClientDataset`)
84	84		5. Planned: ADO data packets (as used by MS Access)
85	85		8. A factory pattern is used, new formats can be added at will.
86	86		9. Simple URL schemes. 2 basic schemes are available
87	87		BASEURL/Resource/
88	88		BASEURL/Resource/ID
89	89
90	90		or using the connection as prefix:
91	91
92	92		BASEURL/Connection/Resource/
93	93		BASEURL/Connection/Resource/ID
94	94		10. Support for introspection/discovery or metadata:
95	95		BASEURL/metadata/
96	96
97	97		returns the list of available resources and their operation.
98	98
99	99		BASEURL/metadata/resourcename
100	100
101	101		returns the structure of the resource: fields, types, etc.
102	102		11. `limit` and `offset` parameters for paging of results:
103	103		BASEURL/resourcename?limit=10
104	104		BASEURL/resourcename?limit=10&Offset=50
105	105
106	106		A maximum limit can be enforced. When the SQL statements support it, the limit and offset are translated to SQL fetch/offset clauses.
107	107		12. Sort order can be specified in the request URL, if the resource definition allows it, using `?sort=fieldname`. The ability to sort can be specified on a field basis.
108	108		BASEURL/resourcename?sort=MyField
109	109		BASEURL/resourcename?sort=MyField%20desc
110	110
111	111		Multiple sort fields can be specified, separated by a comma.
112	112		13. Filtering can be specified on a field basis in the request URL, and the filter operation is translated to SQL Where:
113	113		BASEURL/resourcename?MyField=10
114	114		BASEURL/resourcename?MyField_null=1
115	115		BASEURL/resourcename?MyField_null=0
116	116		BASEURL/resourcename?MyField_lt=10
117	117		BASEURL/resourcename?MyField_lte=10
118	118		BASEURL/resourcename?MyField_gt=10
119	119		BASEURL/resourcename?MyField_gte=10
120	120
121	121		is translated to a SQL where clause:
122	122
123	123		(MyField=10)
124	124		(MyField is null)
125	125		(MyField is not null)
126	126		(MyField<10)
127	127		(MyField_lte<=10)
128	128		(MyField>10)
129	129		(MyField>=10)
130	130
131	131		The ability to filter on a field can be specified in the schema for each field.
132	132		14. The request can specify whether metadata should be included in the response:
133	133		BASEURL/resourcename?metadata=1
134	134		15. The request can specify whether the result should be human-readable or not
135	135		BASEURL/resourcename?humanreadable=1
136	136
137	137		If set to 1, the request result will be formatted. Not all formats support this.
138	138
139	139		## Available Components
140	140
141	141		### TSQLDBRestSchema
142	142
143	143		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestschema.pp
144	144
145	145		Represents a REST schema : a list of available resources, with their allowed operations, fields definitions, SQL statements. A schema can be designed visually using a [[SQLDBRestSchemaEditor]] in a separate application or in the lazarus IDE.
146	146
147	147		#### Properties
148	148
149	149		- Resources A collection of resources. Each item is of type TSQLDBRestResource
150	150		- ConnectionName Set this to a connection name if you want all resources of this schema to use this connection.
151	151
152	152		#### Methods
153	153
154	154		- SaveToFile save the resource definitions to file (as JSON)
155	155
156	156		```delphi
157	157		Procedure SaveToFile(Const aFileName : UTF8String);
158	158		```
159	159
160	160		- SaveToStream save the resource definitions to stream (as JSON).
161	161
162	162		```delphi
163	163		Procedure SaveToStream(Const aStream : TStream);
164	164		```
165	165
166	166		- AsJSON return the resource definitions as a JSON object.
167	167
168	168		```delphi
169	169		function AsJSON(const aPropName: UTF8String=''): TJSONData;
170	170		```
171	171
172	172		If specified, aPropName is the name of the property below which all definitions are stored.
173	173
174	174		- LoadFromFile Load the resource definitions from a JSON file.
175	175
176	176		```delphi
177	177		Procedure LoadFromFile(Const aFileName : UTF8String);
178	178		```
179	179
180	180		- LoadFromStream Load the resource definitions from a JSON file.
181	181
182	182		```delphi
183	183		Procedure LoadFromStream(Const aStream : TStream);
184	184		```
185	185
186	186		- FromJSON Load the resource definitions from a JSON file.
187	187
188	188		```delphi
189	189		Procedure FromJSON(aData: TJSONData;const aPropName: UTF8String='');
190	190		```
191	191
192	192		If specified, aPropName is the name of the property below which all definitions are located.
193	193
194	194		- PopulateResourceFields create resource fields based on table name.
195	195
196	196		```delphi
197	197		procedure PopulateResourceFields(aConn: TSQLConnection; aRes: TSQLDBRestResource; aMinFieldOpts : TRestFieldOptions = []);
198	198		```
199	199
200	200		PopulateResourceFields populates the field definitions in aRes by checking the table in database connection aConn. Every created field gets at least aMinFieldOptions in its Options property.
201	201
202	202		- PopulateResources creates a resource definition for each table in a database connection.
203	203
204	204		```delphi
205	205		procedure PopulateResources(aConn: TSQLConnection; aTables: array of string; aMinFieldOpts: TRestFieldOptions= []);
206	206		Procedure PopulateResources(aConn : TSQLConnection; aTables : TStrings = Nil; aMinFieldOpts : TRestFieldOptions = []);
207	207		```
208	208
209	209		- PopulateResources retrieves the list of tables from aConn, creates a resource definition for the table and calls PopulateResourceFields to fill the resource with fields. If aTables is specified, definitions will only be created for the tables in that list.
210	210
211	211		### TSQLDBRestDispatcher
212	212
213	213		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestbridge.pp
214	214
215	215		#### Properties
216	216
217	217		- Active (Boolean) Register or unregister HTTP routes.
218	218		- Connections (TSQLDBRestConnectionList) List of database connections to connect to.
219	219		- Schemas (TSQLDBRestSchemaList) List of REST schemas to serve
220	220		- BasePath (UTF8String) Base URL for REST URLS.
221	221		- DefaultConnection (UTF8String) Default connection to use if none is detected from request/schema
222	222		- Strings (TRestStringsConfig Property) Input/Output strings configurations
223	223		- OutputOptions (TRestOutputOptions) default Output options, modifiable by query. One or more of
224	224
225	225		1. ooMetadata Send metadata in request
226	226		2. ooSparse Do not send null values (if format allows it)
227	227		3. ooHumanReadable Pretty-print output.
228	228
229	229		- InputFormat (string) Name of Input format to use for all requests.
230	230		- OutputFormat (string) Name of Output format to use for all requests.
231	231		- DispatchOptions (TRestDispatcherOptions) Dispatcher options. One or more of
232	232
233	233		1. rdoConnectionInURL Use the connection as a path element in the URL.
234	234		2. rdoExposeMetadata Expose the metadata URL.
235	235		3. rdoConnectionResource Expose the connection resource, allowing to manage connections through the REST protocol.
236	236		4. rdoCustomView allow the use of a customview URL (Select SQL specified in request)
237	237		5. rdoEmptyCorsDomainToOrigin if the CORDAllowedOrigins is empty, then the request server will be echoed to the client.
238	238		6. rdoHandleCORS Handle CORS preflight request.
239	239		7. rdoAccessCheckNeedsDB This controls when the access check is done for resources: before or after a connection with the database is made. If you need to make additional queries on a database to decide whether access is granted (such as checking ACLs in the Db) then this option should be enabled.
240	240
241	241		- AdminUserIDs A list of user IDs (one per line) which are allowed to use the connection resource. If empty, all users can use it.
242	242		- LogOptions Logging options. Logging happens through the OnLog event handler. This property controls which events are logged. The following values can be specified:
243	243
244	244		1. rloUser Include username in log messages, when available
245	245		2. rtloHTTP Log HTTP request (remote, URL)
246	246		3. rloResource Log resource requests (operation, resource)
247	247		4. rloConnection Log database connections (connect to database)
248	248		5. rloAuthentication Log authentication attempt
249	249		6. rloSQL Log SQL statements. (not on user-supplied connection)
250	250		7. rloResultStatus Log result status.
251	251
252	252		- LogSQLOptions SQL Logging options. if rloSQL is specified, this option controls which SQLDB events are logged for new connections.
253	253		- Authenticator (TRestAuthenticator) Authenticator for requests. If OnBasicAuthentication is set, this can be left empty.
254	254		- EnforceLimit (Integer) If larger than zero, Enforce a limit on output results.
255	255		- CORSAllowedOrigins (String) Comma separated list of Domains that are allowed to use this REST service. If empty, * will be returned in preflight request.
256	256		- CORSMaxAge (Integer) Value for HTTP Access-Control-Max-Age header. If zero, the header is not sent.
257	257		- CORSAllowCredentials (Boolean) value for HTTP header Access-Control-Allow-Credentials. Set to false not to send the header
258	258
259	259		#### Methods
260	260
261	261		- ExposeDatabase exposes a database as a REST service. This will creates a connection definition with the provided parameters and calls ExposeConnection with this definition.
262	262		```delphi
263	263		Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : Array of String; aMinFieldOp
264	264		Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : TStrings = nil; aMinFieldOpt
265	265		```
266	266
267	267		The connection gets the name ConnectionN, where N is a unique number, and the schema is named SchemaConnectionN. The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema
268	268		- ExposeConnection exposes a database (represented by aConnection) as a REST service. This will create a schema with a resource definition.
269	269		```delphi
270	270		Function ExposeConnection(Const aConnection : TSQLDBRestConnection; aTables : TStrings = nil; aMinFieldOpts : TRestFieldOptions
271	271		```
272	272
273	273		The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema
274	274
275	275		### TRestBasicAuthenticator
276	276
277	277		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestauth.pp
278	278
279	279		This class implements HTTP Basic authentication for the SQLDB REST Dispatcher
280	280
281	281		- AuthConnection (TSQLConnection) a SQL connection that will be used to run the SQL. By default the connection of the request is used. Set this if your users are in a separate database.
282	282		- AuthenticateUserSQL (TStrings) an SQL Statement that will be executed on the connection (AuthConnection or connection of the request). This must contain 2 parameters userName and password and return a single field. This field will be used as UserID (available in the rest of the request flow.
283	283		- DefaultUserName (UTF8String) a valid username, not in the database.
284	284		- DefaultPassword (UTF8String) password of a default user.
285	285		- DefaultUserID (UTF8String) the user ID reported if the default user is authenticated.
286	286		- AuthenticationRealm : (UTF8String) Real sent in the WWW-Authenticate challenge to the client.
287	287		- OnBasicAuthentication (event) triggered when basic authentication is requested. Here you can enter custom code to validate the user.
288	288
289	289		### TSQLDBRestModule
290	290
291	291		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestmodule.pp
292	292
293	293		- This is a simple TWebModule descendant which is usable in Lazarus IDE. It has a single Dispatcher property which must be set to a TSQLDBRestDispatcher instance.
294	294
295	295		### TSQLDBRestResource
296	296
297	297		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestschema.pp
298	298
299	299		This is a collection item, found in the 'Resources' property of the TSQLDBRestSchema. It represents a single REST resource.
300	300
301	301		#### properties
302	302
303	303		It has the following properties:
304	304
305	305		- Fields (TSQLDBRestFieldList) A list of fields that will be included in the output, or can be supplied in the input.
306	306		- Enabled (Boolean) Is this resource enabled ?
307	307		- InMetadata (Boolean) Should this resource be shown in the metadata list ?
308	308		- ConnectionName (UTF8String) Connection name to use for this resource.
309	309		- TableName (UTF8String) Database table name. This must only be specified if the SQL is auto generated.
310	310		- ResourceName (UTF8String) Name of this resource as exposed to the outside world.
311	311		- AllowedOperations (TRestOperations) The operations allowed on this resources (Get/Put/Post/Delete/Options/Head)
312	312		- SQLSelect (TStrings) SQL select statement for a GET request. Left empty, it is autogenerated based on the table name. The where clause uses the fields with the foInKey flag set for single resources.
313	313		- SQLInsert (TStrings) SQL INSERT statement for a POST request. Left empty, it is autogenerated based on the table name.
314	314		- SQLUpdate (TStrings) SQL UPDATE statement for a PUT request. Left empty, it is autogenerated based on the table name. The where clause uses the fields with the foInKey flag set.
315	315		- SQLDelete (TStrings) SQL DELETE statement for a DELETE request. Left empty, it is autogenerated based on the table name. The where clause uses the fields with the foInKey flag set.
316	316		- 'SQL an array of SQL statements, based on the previous ones.
317	317		- BusinessProcessor (TSQLDBRestCustomBusinessProcessor) at runtime this contains the business processor for this resource (if any)
318	318
319	319		#### Events
320	320
321	321		The following events are available:
322	322
323	323		- OnResourceAllowed (TSQLDBRestAllowResourceEvent) Called to check whether the user is allowed to access this resource.
324	324		- OnAllowedOperations (TSQLDBRestAllowedOperationsEvent) Called to allow to fine-tune the allowed operations for the user.
325	325		- OnGetDataset (TSQLDBRestGetDatasetEvent) You can return a custom dataset for GET operations.
326	326		- OnCheckParams (TSQLDBRestCheckParamsEvent) You can check the INSERT/UPDATE/DELETE SQL Statement parameters in this request/
327	327		- OnAllowRecord (TSQLDBRestAllowRecordEvent) Called for each record, you can ddecide whether the record should be included in the output.
328	328
329	329		Note: when set, the corresponding event in the business processor is not triggered.
330	330
331	331		#### Methods
332	332
333	333		The following methods exist:
334	334
335	335		` Function GetDataset(aContext : TBaseRestContext; aFieldList : TRestFieldPairArray; aOrderBy : TRestFieldOrderPairArray; aLimit, aOffset : Int64) : TDataset;`
336	336
337	337		Get the custom dataset for this resource.
338	338
339	339		` function GenerateDefaultSQL(aKind: TSQLKind): UTF8String; virtual;`
340	340
341	341		Get the default SQL for this resource if none was specified.
342	342
343	343		` function GetFieldList(aListKind: TFieldListKind): UTF8String;`
344	344
345	345		Return a list of fields as a string, separated by a correct separator token
346	346
347	347		` function GetFieldArray(aListKind: TFieldListKind): TSQLDBRestFieldArray;`
348	348
349	349		Return a list of fields, similar to GetFieldList, but returns the raw fields.
350	350
351	351		` Function GetResolvedSQl(aKind : TSQLKind; Const AWhere : UTF8String; Const aOrderBy : UTF8String = ``; aLimit : UTF8String =``) : UTF8String;`
352	352
353	353		Return the actual SQL Statement that will be executed, with macros
354	354
355	355		` Procedure PopulateFieldsFromFieldDefs(Defs : TFieldDefs; aIndexFields : TStringArray; aProcessIdentifier : TProcessIdentifier; aMinFieldOpts : TRestFieldOptions);`
356	356
357	357		Populate the fields collection from a TFieldDefs collection.
358	358
359	359		### TSQLDBRestField
360	360
361	361		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestschema.pp
362	362
363	363		The TSQLDBREstResource has a collection of fields (TSQLDBRestField) that determine the output fields of the REST resource
364	364
365	365		It has the following properties;
366	366
367	367		- FieldName (UTF8String Read FFieldName Write FFieldName;
368	368		- PublicName (UTF8String Read GetPublicName Write FPublicName;
369	369		- GeneratorName (String) name of a generator to privide a unique value.
370	370		- FieldType (TRestFieldType) Data type of the exposed field.One of (the names speak for themselves)
371	371		- rftInteger
372	372		- rftLargeInt
373	373		- rftFloat
374	374		- rftDate
375	375		- rftTime
376	376		- rftDateTime
377	377		- rftString
378	378		- rftBoolean
379	379		- rftBlob (sent as base64-encoded data)
380	380		- NativeFieldType (TFieldType) Native database field type of this field.
381	381		- Options (TRestFieldOptions) options for this field. One of more of:
382	382		- foInKey This is a key field.
383	383		- foInInsert The field can be inserted (if statement is autogenerated)
384	384		- foInUpdate The field can be updated (if statement is autogenerated)
385	385		- foRequired A field value is required.
386	386		- foFilter Filtering is allowed on this field.
387	387		- foOrderBy Ordering is allowed on this field.
388	388		- foOrderByDesc Ordering (descendent) is allowed on this field.
389	389		- Filters (TRestFieldFilters) Allowed filter expressions for this field. One or more of:
390	390		- rfEqual
391	391		- rfLessThan
392	392		- rfGreaterThan
393	393		- rfLessThanEqual
394	394		- rfGreaterThanEqual
395	395		- rfNull
396	396		- MaxLen (integer) for string fields, the maximum allowed length of the field.
397	397
398	398		### TSQLDBRestBusinessProcessor
399	399
400	400		Unit: fpc/packages/fcl-web/src/restbridge/sqldbrestschema.pp
401	401
402	402		This class serves to implement any business rules you want to attach to your rest resource. You can drop one TSQLDBRestBusinessProcessor instance per resource on a module, set the schema and resource name (a list of resource names is available in the Object Inspector) and implement the events.
403	403
404	404		The following properties and events are available:
405	405
406	406		- Schema : The schema in which the resource resides.
407	407		- ResourceName The resource for which the business rules are valid.
408	408		- OnGetDataset : Event called when you want to create a custom dataset. You must free the dataset yourself.
409	409		- OnCheckParams : Event in which you can verify whether the parameters for insert/update/delete/select queries are OK.
410	410		- OnAllowResource : Event to decide whether a property is allowed for a request. You can use this e.g. to forbid certain users from accessing a resource.
411	411		- OnAllowedOperations : Event to decide whether a REST operation is allowed for a request. You can use this to forbid certain users from writing to a resource, but allow them to read.
412	412		- OnAllowRecord : Called for each record which will be output. This can be used to prevent certain records from being streamed to the output. Note that if Limit is specified, this will not be taken into account, i.e. if the limit is 10, and you forbid 2 records from being streamed at this point, only 8 records will be returned.
413	413
414	414		All calls get passed a TRestContext instance. This contains 2 properties and a method:
415	415
416	416		` Function GetVariable(Const aName : UTF8String; aSources : TVariableSources; Out aValue : UTF8String) : Boolean;`
417	417
418	418		Call this to get a HTTP Query variable, header,... The function returns true if the variable was found.
419	419
420	420		` Property UserID : UTF8String`
421	421
422	422		This will be set when calling.
423	423
424	424		` Property Data : TObject`
425	425
426	426		You can attach data to this if you want to. It will be kept for the duration of the request. You are responsible for freeing this data, though.
427	427
428	428		## Special Resources
429	429
430	430		### metaData
431	431
432	432		The /baseURL/metaData resource lists all the resources defined in the schema, together with the allowed operations.
433	433
434	434		The usual formats and parameters are supported.
435	435
436	436		### metaData/resourceName
437	437
438	438		The /baseURL/metaData/resourceName resource lists all the fields in the resourceName resource, together with the properties.
439	439
440	440		The usual formats and parameters are supported for this resource.
441	441
442	442		### customview
443	443
444	444		The customView resource is special. Its availability must be enabled using the Dispatcher's Options. When rdoCustomView is included in the options, the
445	445
446	446		` baseURL/customView`
447	447
448	448		URL becomes available. It allows the client to specify an SQL Select statement using the SQL parameter:
449	449
450	450		` baseURL/customView?SQL=select count(*) as thecount from person`
451	451
452	452		(you must encode the sql using the usual URI encoding mechanism, it is omitted for readability reasons here) The usual parameters for fieldlists, format are supported, but not the limit and offset parameters.
453	453
454	454		The engine will check that only SELECT statements passed on. Nevertheless, this is an inherently less safe mechanism which should only be used in case of need.
455	455
456	456		### _connection
457	457
458	458		This is a resource that allows you to manage the connections through REST. This can be useful in development environments, where you can simply create a single binary that does not need any configuration, the whole configuration can be done through REST, including the management of the available connections.
459	459
460	460		Because this is security-wise a dangerous option to enable, it is disabled by default, and must be enabled explicitly with the rdoConnectionResource option of the dispatcher.
461	461
462	462		Use cases are a development environment where this can be used to confiure the restserver from within the IDE. When moving to production, the option can be disabled, and only fixed and known connections will be allowed/
463	463
464	464		## Available Output formats
465	465
466	466		### JSON (the default)
467	467
468	468		- A simple JSON object encapsulating data, metadata and possibly error nodes.
469	469		- rows are exported as JSON objects, field names are property names of the object.
470	470		- Name: 'json'
471	471		- Content-type: 'application/json'
472	472		- Input and output.
473	473		- Data by default under 'data'
474	474		- Metadata by default under 'metaData'
475	475		- Errors reported under 'error'
476	476		- Property names are configurable in REST dispatcher.
477	477
478	478		### XML (a custom format)
479	479
480	480		- A simple XML document encapsulating data, metadata and possibly error nodes, under a root element 'datapacket'
481	481		- rows are exported as XML elements (named 'row') , the contents as text in the element.
482	482		- Name: 'xml'
483	483		- Content-type: 'text/xml'
484	484		- Input and output.
485	485		- Data by default under 'data'
486	486		- Metadata by default under 'metadata'
487	487		- Errors reported under 'error'
488	488		- Element names are configurable in REST dispatcher.
489	489
490	490		### CSV
491	491
492	492		- Simple comma-separated CSV list.
493	493		- Name: 'csv'
494	494		- Content-type: 'text/csv'
495	495		- Input and output.
496	496		- Separator: Comma
497	497		- Quotes when needed.
498	498		- Metadata means first line contains fieldnames.
499	499
500	500		### CDS
501	501
502	502		- XML package using the XMLFormat used by Delphi `TClientDataset`.
503	503		- Name: 'cds'
504	504		- Content-type: 'text/csv'
505	505		- Input and output.
506	506
507	507		A Delphi demo program is provided.
508	508
509	509		### ADO
510	510
511	511		- XML format as used by ADO recordsets (as used by MS Access)
512	512		- Name: 'ado'
513	513		- Input and output.
514	514
515	515		## Ini file Support
516	516
517	517		The following classes can load/save their configuration settings from/to an .ini file
518	518
519	519		- TSQLDBRestDispatcher
520	520		- TRestBasicAuthenticator
521	521		- TSQLDBRestConnection
522	522		- TRestStringsConfig
523	523
524	524		The support for this is implemented in 2 units;
525	525
526	526		- the sqldbauthini unit has a type helper for the TRestBasicAuthenticator class.
527	527		- The sqldbrestini unit has the type helpers for TSQLDBRestDispatcher and TSQLDBRestConnection and TRestStringsConfig.
528	528
529	529		The helpers implement LoadFromFile/SaveToFile methods, as well as LoadFromIni/SaveToIni methods. There are some options to control the level of depth.
530	530
531	531		The schemas (if so required) are saved as JSON files in the same directory as the .ini file, using the name of the schema as the filename.
532	532
533	533		## Lazarus Support
534	534
535	535		Requires Lazarus 2.1+
536	536
537	537		Lazarus support for the SQLDB REST Bridge is implemented in 2 packages under the components/sqldbrest directory:
538	538
539	539		1. The design/sqldbrestschemadesigner.lpk package.
540	540		2. The lazsqldbrest.lpk package.
541	541
542	542		The latter package registers the TSQLDBRestDispatcher, TSQLDBRestSchema and TRestBasicAuthenticator components on the component palette: The former includes the schema editor. They should be compiled and installed in the order presented here.
543	543
544		-	~~<figure>~~
545		-	~~<img src="sqldbrestcomponents.png" title="sqldbrestcomponents.png" />~~
546		-	~~<figcaption>sqldbrestcomponents.png</figcaption>~~
547		-	~~</figure>~~
	544	+	![](/wiki-images/d/d6/Sqldbrestcomponents.png)
548	545
549	546		Additionally it registers some component- and property editors. In particular the GUI schema editor can be called from the TSQLDBRestSchema component context menu.
550	547
551	548		The GUI schema editor is included in the IDE, it is discussed in a separate page [[SQLDBRestSchemaEditor]]
552	549
553	550		Further integration with Lazarus is planned: 1. Let the IDE act as a REST server. (alternatively, add a tool with a menu entry under tools for easy configuration)
554	551
555	552		Lazarus support can work with FPC 3.0.4. To make this work, the files can be copied from SVN or a 3.2 release to the lazarus components/sqldbrest/src directory.
556	553
557	554		To this end,
558	555
559	556		1. create a directory src below the components/sqldbrest directory (where the package is located)
560	557		2. copy all files in directory
561	558
562	559		` fpc/packages/fcl-web/src/restbridge`
563	560
564	561
565	562		to the new src directory
566	563
567	564		` lazarus/components/sqldbrest/src`
568	565
569	566		1. compile the lazarus/components/sqldbrest/design/sqldbrestschemadesigner.lpk package. It has unitpath ../src, so it should find the files in the src directory.
570	567		2. compile the lazarus/components/sqldbrest/lazsqldbrest.lpk package.
571	568
572	569		## Pas2JS Support
573	570
574	571		The SQLDB Rest bridge will be integrated in the compile server that comes with pas2JS: This means that pas2js will come with a toll that
575	572
576	573		- Can recompile your web project on the fly
577	574		- Can serve files on disc
578	575		- Can act as a REST server.
579	576
580	577		So no actual server is needed during development.
581	578
582	579		## Usage
583	580
584	581		### In a fcl-web application.
585	582
586	583		The rest bridge was designed to be simple to use.
587	584
588	585		#### Expose a single database, no authentication.
589	586
590	587		Simple expose of a database. Run the following code in the startup of your program (for example, DoRun of the web application class, or startup code of the program)
591	588
592	589		```delphi
593	590		FDisp:=TSQLDBRestDispatcher.Create(Self);
594	591		FDisp.ExposeDatabase(
595	592		'postgres','localhost','expensetracker','me','secret'
596	593		,Nil,
597	594		[foFilter,foInInsert,foInUpdate,foOrderByDesc]);
598	595		FDisp.Active:=True;
599	596		FDisp.SaveToFile('demo.ini');
600	597		```
601	598
602	599		This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'
603	600
604	601		The configuration will be saved in a file called 'demo.ini'.\
605	602		It can be reused to quickly set up a REST dispatcher.
606	603
607	604		#### Expose single database, HTTP Basic authentication.
608	605
609	606		Similar to the previous example, but with HTTP BASIC authentication for a single user:
610	607
611	608		```delphi
612	609		FAuth:=TRestBasicAuthenticator.Create(Self);
613	610		FAuth.DefaultUserName:='me';
614	611		FAuth.DefaultPassword:='secret';
615	612		FDisp:=TSQLDBRestDispatcher.Create(Self);
616	613		FDisp.Authenticator:=Fauth;
617	614		FDisp.ExposeDatabase(
618	615		'postgres','localhost','expensetracker','me','secret'
619	616		,Nil,
620	617		[foFilter]);
621	618		FDisp.Active:=True;
622	619		```
623	620
624	621		This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'. No updates are possible: the fields will not be in update/insert statements. Sorting is also not allowed.
625	622
626	623		#### Expose single database, HTTP Basic authentication (2)
627	624
628	625		Smilar to the previous example, but with HTTP BASIC authentication, using the database to authenticate a user:
629	626
630	627		```delphi
631	628		FAuth:=TRestBasicAuthenticator.Create(Self);
632	629		FAuth.AuthenticateUserSQL.Text:='select uID from users where (uLogin=:UserName) and (uPassword=:Password)';
633	630		FDisp:=TSQLDBRestDispatcher.Create(Self);
634	631		FDisp.Authenticator:=Fauth;
635	632		FDisp.ExposeDatabase(
636	633		'postgres','localhost','expensetracker','me','secret'
637	634		,['projects','expenses'],
638	635		[]);
639	636		FDisp.Active:=True;
640	637		```
641	638
642	639		Here only the 'projects' and 'expenses' tables are exposed, read-only.
643	640
644	641		#### Using a Config file and schema files.
645	642
646	643		Start a dispatcher, loading the file demo.ini. The dioSkipReadSchemas tells it not to load schema files, the schemas will be created from the connections.
647	644
648	645		```delphi
649	646		FDisp:=TSQLDBRestDispatcher.Create(Self);
650	647		FDisp.LoadFromFile('demo.ini',[dioSkipReadSchemas]);
651	648		FDisp.Active:=True;
652	649		```
653	650
654	651		The .ini file can be created using SaveToFile on a configured dispatcher instance.
655	652
656	653		### In the lazarus IDE.
657	654
658	655		To work in the lazarus IDE with the SQLDB Rest bridge, you must install the lazsqldbrest package, which can be found in the directory components/sqldbrest.
659	656
660	657		#### Using a datamodule
661	658
662	659		In this approach, a datamodule is created that is in memory throughout the whole lifetime of the application (fcl-web application)
663	660
664	661		1. Create a HTTP server application or FCGI application. (a CGI will also work but is very inefficient)
665	662		2. Add a plain datamodule to the application.
666	663		3. Drop a TSQLDBRestSchema class from the 'FCL-Web' tab of the component palette
667	664		4. Edit the schema (property resources), or import it from file using the component editor context menu
668	665		5. Drop a TSQLDBRestDispatcher class from the 'FCL-Web' tab of the component palette
669	666		6. Edit the connections
670	667		7. Add an item to the Schemas property and point it to the TSQLDBRestSchema added previously
671	668		8. For HTTP Basic authentication, add a TRESTBAsicAuthenticator class, and set the TSQLDBRestDispatcher instance's Authentocator property to it.
672	669
673	670		The end result should look more or less like this:
674	671
675		-	~~<figure>~~
676		-	~~<img src="restmodule.png" title="restmodule.png" />~~
677		-	~~<figcaption>restmodule.png</figcaption>~~
678		-	~~</figure>~~
	672	+	![](/wiki-images/2/2a/Restmodule.png)
679	673
680	674		That's it. The project is ready to run.
681	675
682	676		#### Using a SQLDBRestdatamodule
683	677
684	678		This approach is basically the same as the previous one.
685	679
686	680		1. Create a HTTP application
687	681		2. Under File - New, create a SQLDB Rest Bridge Module.
688	682		3. Drop a SQLDBRestDispatcher component on the module.
689	683		4. The Dispatcher property of the module should be set to the SQLDBRestDispatcher component.
690	684		5. Set up the component as required, as in the previous example.
691	685
692	686		## Examples
693	687
694	688		### FPC
695	689
696	690		- in FPC Packages, the fcl-web/examples/restbridge directory, there is a small example that serves a expenses tracker database.
697	691		- The directory (delphiclient) below contains a small Delphi project that shows how to connect a TClientDataset to the SQLDB Rest bridge.
698	692
699	693
700	694		Design time view:
701	695
702		-	~~<figure>~~
703		-	~~<img src="delphirestclientdesign.png" title="delphirestclientdesign.png" />~~
704		-	~~<figcaption>delphirestclientdesign.png</figcaption>~~
705		-	~~</figure>~~
	696	+	![](/wiki-images/9/9a/Delphirestclientdesign.png)
706	697
707	698		Run time view:
708	699
709		-	~~<figure>~~
710		-	~~<img src="delphirestclient.png" title="delphirestclient.png" />~~
711		-	~~<figcaption>delphirestclient.png</figcaption>~~
712		-	~~</figure>~~
	700	+	![](/wiki-images/7/7b/Delphirestclient.png)
713	701
714	702		### Lazarus
715	703
716	704		Requires Lazarus 2.1+
717	705
718	706		There are 2 server examples available, and several client examples. The client examples are all the same, they just use a different kind of TDataset to show the data coming from the server: the buf, json and CSV formats are demonstrated in this way.
719	707
720	708		#### Server examples
721	709
722	710		- The lazarus/components/sqldbrest/demo/restbridge directory contains the same example program restserver.lpi, using a datamodule.
723	711		- The lazarus/components/sqldbrest/demo/restmodule directory contains the same example program demorestmodule.lpi, using a SQLDB Rest Web datamodule.
724	712
725	713		#### TBufDataset client example
726	714
727	715		- The lazarus/components/sqldbrest/demo/bufclient directory contains a demo, that shows how to load data from a SQLDB REST bridge server in a TBUFDataset dataset. It uses the 'buf' format to do so.
728	716
729	717
730		-	~~<figure>~~
731		-	~~<img src="sqldbrestbufclientdata.png" title="sqldbrestbufclientdata.png" />~~
732		-	~~<figcaption>sqldbrestbufclientdata.png</figcaption>~~
733		-	~~</figure>~~
	718	+	![](/wiki-images/2/2a/Sqldbrestbufclientdata.png)
734	719
735	720
736		-	~~<figure>~~
737		-	~~<img src="sqldbrestbufclientraw.png" title="sqldbrestbufclientraw.png" />~~
738		-	~~<figcaption>sqldbrestbufclientraw.png</figcaption>~~
739		-	~~</figure>~~
	721	+	![](/wiki-images/2/2c/Sqldbrestbufclientraw.png)
740	722
741	723		it also demonstrates how to use the metadata resource of the server to list the available resources
742	724
743	725		#### TJSONDataset client example
744	726
745	727		- The lazarus/components/sqldbrest/demo/jsonclient directory contains a demo that is similar to the bufclient demo:
746	728
747	729
748	730		it shows how to load data from a SQLDB REST bridge server in a TBaseJSONDataset dataset, using the 'csv' format
749	731
750		-	~~<figure>~~
751		-	~~<img src="sqldbrestjsonclientdata.png" title="sqldbrestjsonclientdata.png" />~~
752		-	~~<figcaption>sqldbrestjsonclientdata.png</figcaption>~~
753		-	~~</figure>~~
	732	+	![](/wiki-images/2/29/Sqldbrestjsonclientdata.png)
754	733
755	734
756		-	~~<figure>~~
757		-	~~<img src="sqldbrestjsonclientraw.png" title="sqldbrestjsonclientraw.png" />~~
758		-	~~<figcaption>sqldbrestjsonclientraw.png</figcaption>~~
759		-	~~</figure>~~
	735	+	![](/wiki-images/3/33/Sqldbrestjsonclientraw.png)
760	736
761	737		Like the bufdataset demo, it also demonstrates how to use the metadata resource of the server to list the available resources
762	738
763	739		#### TCSVDataset client example
764	740
765	741		- The lazarus/components/sqldbrest/demo/csvclient directory contains a demo that is similar to the bufclient demo:
766	742
767	743
768	744		it shows how to load data from a SQLDB REST bridge server in a TCSVDataset dataset, using the 'csv' format
769	745
770	746		### Pas2JS
771	747
772	748		Requires pas2js 1.5+
773	749
774	750		- A similar application to the lazarus buffclient/jsonclient programs exists for pas2JS in pas2js/demo/restbridge/simple/restbridgeclient.lpi
775	751
776	752
777		-	~~<figure>~~
778		-	~~<img src="sqldbrestbridgepas2js.png" title="sqldbrestbridgepas2js.png" />~~
779		-	~~<figcaption>sqldbrestbridgepas2js.png</figcaption>~~
780		-	~~</figure>~~
	753	+	![](/wiki-images/c/ca/Sqldbrestbridgepas2js.png)
781	754
782	755		## TODO
783	756
784	757		The following extensions are still planned:
785	758
786	759		- Support for `?q=filterexpression` in URL filters.
787	760		- Connection management API. (zero-config service)
788	761		- Use HTTP credentials to connect to the database.
789	762		- Unknown query params are now ignored. Allow to check for valid query parameters and raise error if unknown query param is encountered.
790	763
791	764		---
792	765		From the Free Pascal wiki, page "SQLDBRestBridge", as it stood on 2023-03-01 after a change by Michael, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).
793	766		Category: Databases

Back to history