polymorphic/README.html at main · itbhat/polymorphic · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Polymorphic Pattern Sphere Schema</title>
  <style>
    h1, h2 {
	font-family: sans-serif;
    }
    dt {
	font-weight:bold;
	margin-left: 1em;
    }
    pre {
	margin-left: 1em;
    }
  </style>
</head>
<body>

<h1>Polymorphic Pattern Sphere Schema</h1>

<p>The Public Sphere Project is in the early stages of working toward
building tools and data representations for pattern languages of many
types. This document describes our current testbed and specifically
the schema of the SQL database design we are using.</p>

<p>Key elements are missing!</p>

<p>Our strategy for data representation has these elements:</p>

<dl>

 <dt>pattern feature</dt>
 <dd>A <em>pattern feature</em> has a name and a type. For example, the
   <code>title</code> is of type <code>string</code>. Other types
   that a feature may have are <code>image</code>. Feature names must be
   unique.</dd>

 <dt>pattern template</dt>
 <dd>A <em>pattern template</em> is a named collection of features.
   Features in a template may be required.</dd>

 <dt>pattern</dt>
 <dd>A <em>pattern</em> has an associated <em>pattern template</em> that
   defines what features it may have.</dd>

 <dt>feature value</dt>
 <dd>Our data design calls for a distinct database table for each pattern
   type, used to store feature values. We follow a table naming convention
   based on the feature type. For example, the table storing values for the
   <code>title</code> feature is <code>pf_string</code>, which has
   a <code>value</code> column suitable for storing strings.</dd>
 <dd>Values for all of the feature types - except <code>image</code>
   - are stored in these feature value tables. Image values live in the
  file system, named with the SHA1 hash of the contents, and that hash
  value is stored in the value table.</dd>

 <dt>pattern language</dt>
 <dd>A <em>pattern language</em> is a collection of <em>patterns</em>, which
  share a <em>pattern template</em>.</dd>

<dt>pattern view</dt>
 <dd>A <em>pattern view</em> is a specification for how to render patterns
   using a common pattern template for display or printing. It has a name,
   a reference to a template, and a layout. The layout is a skeletal HTML
   page that has tokens showing where to insert the feature value.</dd>

 <dd>Here is a <a href="simple.html">simple layhout file</a>. This
   particular layout is designed to work with the <em>Liberating
     Voices</em> template, displaying the features <code>title</code> -
   a <code>string</code> - <code>problem</code> - type <code>text</code> -
   and <code>thematicimage</code> - type <code>image</code>.</dd>

</dl>

<h2>Toy Application</h2>

<p>To test the concepts and data architecture, a toy <a
href="/publicsphereproject/polymorphic/">Polymorphic Pattern
Sphere</a> application is being developed. The code - PHP - is in the
GitHub repository at <a href="https://github.com/smrose/polymorphic/">
github.com/smrose/polymorphic</a>. We are using PDO to access the data
store, which is MariaDB/MySQL. <em>Please use the issue tracker
there to reports bugs and request features</em>.</p>

<p>We have loaded the datastore with the <em>Liberating Voices</em>
patterns as they exist in the existing <a
href="https://labs.publicsphereproject.org/ps">Pattern Sphere</a>
application on <code>labs.publicsphereproject.org</code>. To do that,
we have:</p>

<ol>

 <li>Created pattern features for each feature those patterns have in the
  Pattern Sphere.</li>

 <li>Created pattern feature value tables for each feature - including
  thematic images - and populated them with values.</li>

 <li>Created a <code>Liberating Voices</code> pattern template that includes
  all of those features.</li>

 <li>Created a <code>Liberating Voices</code> pattern language that includes
  all of the patterns.</li>

</ol>

<p>Besides the code for the application, scripts for creating the
database and populating it with values from <em>Liberating Voices</em>
is in the repository.</p>

<p>The toy application supports these actions:</p>

<dl>

  <dt>pattern features</dt>
  <dd>Create, edit, and delete.</dd>

  <dt>pattern templates</dt>
  <dd>Create, edit - both metadata and included features - and delete.
    TODO: support cloning of pattern templates.</dd>

  <dt>patterns</dt>
  <dd>Create, edit, and view. Feature values can be edited and deleted. TODO:
    support deletion of patterns. TODO: support cloning of patterns.</dd>

  <dt>pattern languages</dt>
  <dd>Create and edit - both metadata and pattern membership.</dd>

  <dt>pattern views</dt>
  <dd>Pattern views, which determine how patterns using a particular
    template may be displayed, can be created, edited, and deleted. A
    key concept of views is that of the <code>layout</code>, which is
    an HTML file with pattern tokens that are replaced by feature
    values at display time. Layouts can be uploaded and downloaded.</dd>

</dl>

<h2>Schema</h2>

<p>Here is an alphabetic list of tables linked to their definitions:</p>
<ul>
 <li><a href="#language">language</a></li>
 <li><a href="#pattern">pattern</a></li>
 <li><a href="#pattern_feature">pattern_feature</a></li>
 <li><a href="#pattern_language">pattern_language</a></li>
 <li><a href="#pattern_template">pattern_template</a></li>
 <li><a href="#pattern_view">pattern_view</a></li>
 <li><a href="#pf_image">pf_image</a></li>
 <li><a href="#pf_integer">pf_integer</a></li>
 <li><a href="#pf_string">pf_string</a></li>
 <li><a href="#pf_text">pf_text</a></li>
 <li><a href="#plmember">plmember</a></li>
 <li><a href="#pt_feature">pt_feature</a></li>
</ul>

<p id="pattern_template">A <code>pattern_template</code> is a named set of
<code>pattern_feature</code> elements. As with many other tables,
the <code>notes</code> is for author annotation and the <code>created</code>
and <code>modified</code> columns document (a little bit of) the history.</p>

<pre>
CREATE TABLE pattern_template (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE,
  notes varchar(16383) DEFAULT NULL,
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp()
)
</pre>

<p id="pattern">Each row in the <code>pattern</code> table represents
- surprise! - a pattern. The <code>ptid</code>, linking to
a <code>pattern_template</code>, defines the features it may have.</p>

<pre>
CREATE TABLE pattern (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ptid int unsigned NOT NULL,
  notes varchar(16383),
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp(),
  CONSTRAINT FOREIGN KEY (ptid) REFERENCES pattern_template(id)
   ON DELETE CASCADE
);
</pre>

<p id="language">The <code>language</code> is a lookup table for
  natural languages, intended to support internationalization but not
  yet used and subject to refinement. US English, coded
  as <code>en</code>, is the default language.</p>

<pre>
CREATE TABLE language (
  code CHAR(2) NOT NULL,
  description varchar(255) NOT NULL,
  PRIMARY KEY (code)
);
</pre>

<p id="pattern_language">A <code>pattern_language</code> is a named set of
patterns that share a <code>pattern_template</code>.</p>

<pre>
CREATE TABLE pattern_language (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ptid int unsigned NOT NULL,
  name varchar(255) NOT NULL UNIQUE,
  notes varchar(16383),
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp(),
  CONSTRAINT FOREIGN KEY(ptid) REFERENCES pattern_template(id)
);
</pre>

<p id="pattern_feature">A row in the <code>pattern_feature</code>
  defines a named and typed feature that a <code>pattern</code> may
  have. The <code>required</code> is true if each pattern
  that <em>may</em> have the feature <em>must</em> have that
  feature. (Arguably, this would better be a column in
  the <a href="#pt_feature"><code>pt_feature</code></a> table, but
  that's not the current design.)
</p>

<pre>
CREATE TABLE pattern_feature (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE,
  type enum('string', 'text', 'image', 'integer'),
  required tinyint(1) DEFAULT 0,
  notes varchar(16383) DEFAULT NULL,
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp()
);
</pre>

<p id="pt_feature">Each row in <code>pt_feature</code> specifies
a <code>pattern_feature</code> that is linked to
a <code>pattern_template</code> - that is, a feature that a pattern
using that template may have.</p>

<pre>
 CREATE TABLE pt_feature (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ptid int unsigned NOT NULL,
  fid int unsigned NOT NULL,
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp(),
  CONSTRAINT UNIQUE (ptid, fid),
  CONSTRAINT FOREIGN KEY(ptid) REFERENCES pattern_template(id)
   ON DELETE CASCADE,
  CONSTRAINT FOREIGN KEY(fid) REFERENCES pattern_feature(id)
   ON DELETE CASCADE
 );
</pre>

<p id="plmember">Each row in the <code>plmember</code> table specifies a
  <code>pattern</code> and a <code>pattern_language</code>, specifying
  that the referenced pattern language includes that pattern. The
  <code>rank</code> column determines the ordering of the patterns in the
  language.</p>

<pre>
CREATE TABLE plmember (
  pid int unsigned NOT NULL,
  plid int unsigned NOT NULL,
  rank int unsigned NOT NULL,
  CONSTRAINT UNIQUE(plid, pid),
  UNIQUE key doggy (plid, rank),
  KEY pid (pid),
  FOREIGN KEY (pid) REFERENCES pattern(id),
  FOREIGN KEY (plid) REFERENCES pattern_language(id)
);
</pre>

<p id="pattern_view">A <code>pattern_view</code> describes how to display
  a pattern.</p>

<pre>
CREATE TABLE pattern_view (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ptid int unsigned NOT NULL,
  name varchar(255) NOT NULL,
  layout mediumtext NOT NULL,
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp()
   ON UPDATE current_timestamp(),
  notes varchar(16383),
  CONSTRAINT FOREIGN KEY (ptid) REFERENCES pattern_template(id)
);
</pre>

<p id="pf_string">The <code>pf_string</code> table is the feature
value table for pattern feature values of type <code>text</code>.</p>

<pre>
CREATE TABLE pf_text (
  id int unsigned NOT NULL AUTO_INCREMENT,
  pid int unsigned NOT NULL,
  pfid int unsigned NOT NULL,
  language char(2) NOT NULL DEFAULT 'en',
  value mediumtext NOT NULL,
  PRIMARY KEY (id,language),
  KEY pid (pid),
  KEY pfid (pfid),
  CONSTRAINT FOREIGN KEY (language) REFERENCES language (code),
  CONSTRAINT FOREIGN KEY (pid) REFERENCES pattern (id),
  CONSTRAINT FOREIGN KEY (pfid) REFERENCES pattern_feature (id)
);
</pre>

<p id="pf_string">The <code>pf_string</code> table is the feature
value table for pattern feature values of type <code>string</code>.</p>

<pre>
CREATE TABLE pf_string (
  id int unsigned NOT NULL AUTO_INCREMENT,
  pid int unsigned NOT NULL,
  pfid int unsigned NOT NULL,
  language char(2) NOT NULL DEFAULT 'en',
  value varchar(255) NOT NULL,
  PRIMARY KEY (id,language),
  KEY pid (pid),
  KEY pfid (pfid),
  CONSTRAINT FOREIGN KEY (language) REFERENCES language (code),
  CONSTRAINT FOREIGN KEY (pid) REFERENCES pattern (id),
  CONSTRAINT FOREIGN KEY (pfid) REFERENCES pattern_feature (id)
);
</pre>

<p id="pf_image">The <code>pf_image</code> table is the
feature value table for  pattern feature values
of type <code>image</code>. The <code>filename</code> column
stores the filename of the file that was
uploaded. The <code>alttext</code> column is for alternative text; a
value for this field is required. The <code>hash</code> field is an
SHA1 hash of the file contents, used to name the file and to detect
when an uploaded file is a duplicate.</p>

<pre>
CREATE TABLE pf_image (
  id int unsigned NOT NULL AUTO_INCREMENT,
  pid int unsigned NOT NULL,
  pfid int unsigned NOT NULL,
  filename varchar(255) DEFAULT NULL,
  alttext varchar(1023) NOT NULL,
  hash char(40) NOT NULL,
  language char(2) NOT NULL DEFAULT 'en',
  created timestamp NOT NULL DEFAULT current_timestamp(),
  modified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (id),
  KEY language (language),
  KEY pid (pid),
  KEY pfid (pfid),
  CONSTRAINT FOREIGN KEY (language) REFERENCES language (code),
  CONSTRAINT FOREIGN KEY (pid) REFERENCES pattern (id),
  CONSTRAINT FOREIGN KEY (pfid) REFERENCES pattern_feature (id)
);
</pre>

<script>
  document.write('<p>Last modified: <code>' + document.lastModified + '</code></p>')
</script>

</body>
</html>