User:Crtn/CMSDocumentation
From iA wiki
Documentation for a CMS written in PHP (and PEAR), using MySQL for starters.
- PHP because it is easy, fast, and well used.
- PEAR mainly for its html form package, but maybe also for MySQL access.
- MySQL because it is simple and fast.
The main idea of this CMS is to have a database with metadata about the tables that are to be manipulated, then to use the metadata to generate input forms, validate, and actually input data into another database.
A seperate tool, such as myPhpAdmin, should be used to define the layout of the database (creating tables, determining their relations, etc). The CMS *is not* intended to do any complex selection queries; it is only for the backend management. Front-end layout, queries, etc should be implemented independently of the CMS, though they rely on the CMS architecture.
The CMS will have sections to:
- select existing databases
- select the tables that the CMS should gather metadata about
- manipulate field properties
- select which fields should be omitted from forms (primary keys, auto_incremented numbers, etc)
- select the field 'type' (e.g. 'file' --> blob, e.g. 'filename' --> text, with file stored in filesystem)
- link a field to another record
Forms will provide:
- add a record (row)
- delete a record (row)
- HOPEFULLY this will have an option akin to foreign keys where linked records will get updated/deleted
- alter a record (row)
Contents |
Status
- 2003-09-11: Worked on this for a while, realized that my initial ideas were too controlling of the DB and took the flexibility away from the DB schema. I am going to re-work this to be a much less intrusive CMS, to the point where essentially all it will do is generate the forms necessary to input information into a DB.
- 2003-09-02: Getting started. Created base tables, initial functions to generate queries.
Database
Table format
For a single example object: <pre> object <-- object_attribute --> object_attribute_type --> sqltype
--> formtype
--> object (distinct) </pre> Our object can have multiple object_attributes, though only one is shown here. Each object_attribute has a type associated with it, which in turn has a formtype and a sqltype. Each object_attribute also may have a link to another object, via that object's id (note that this links to the 'object' table itself via an id, rather than a specific instance of that object, since this is metadata).
Table creation, initial propogation
<pre> /* use cms database */ use cms;
/* tables, for fun */ create table object (id int not null auto_increment primary key, name tinytext not null); create table object_attributes (id int not null auto_increment primary key, object_id int not null, name tinytext not null, type_id int not null, linkedto int); create table object_attribute_type (id int not null auto_increment primary key, name tinytext not null, sqltype int not null, formtype int not null); create table sqltype (id int not null auto_increment primary key, name tinytext not null, maxlength int unsigned); create table formtype (id int not null auto_increment primary key, name tinytext not null);
/* for mysql fields */ insert into sqltype (name, maxlength) values ('text', '65535'); insert into sqltype (name, maxlength) values ('tinytext', '255');
/* html form elements */ insert into formtype (name) values ('text'); insert into formtype (name) values ('textarea');
/* example types for attributes */ insert into object_attribute_type (name, sqltype, formtype) values ('text', 1, 1); insert into object_attribute_type (name, sqltype, formtype) values ('tinytext', 2, 2);
/* example attributes */ insert into object (name) values ('item'); insert into object_attributes (object_id, name, type_id) values (1, 'creator', 1); insert into object_attributes (object_id, name, type _id) values (1, 'name', 1); </pre>
Possible Extenstions
- create indexes on certain columns
- add fulltext on columns
- add more complex query support (ranges, etc) to CMS
Code
mysql_get_queries
Queries generated:
- create table
- drop table
- insert data
- delete data (by id)
- update data (by id)
Not generated:
- alter table (add/drop/modify columns)
alter table queries rely on new user input and not existing definitions, so these must be generated seperately.
<pre> /* mysql_get_queries
- generates queries for a given object type
- returns an array with
- create, drop, insert, delete, update queries
- values to be filled in are of the form %columnname%
- /
function mysql_get_queries($objecttype) {
/* array of queries to return */
$query_arr = array();
/* escape itemname, to be safe, formulate metadata query */
$objecttype = mysql_escape_string($objecttype);
$query = "select o.name as object,
a.name as attribute,
t.name as type,
s.name as sqltype,
s.maxlength as maxlength,
f.name as formtype
from
object o,
object_attributes a,
object_attribute_type t,
sqltype s,
formtype f
where
o.name = '$objecttype'
and o.id = a.object_id and t.id = a.type_id
and s.id = t.sqltype and f.id = t.formtype";
/* query db, check for result */
$result = mysql_query($query);
if($result && (mysql_num_rows($result) > 0)) {
/* initialize each query */
$query_arr["create"] = "create table $objecttype ";
$query_arr["create"] .= "(id int not null auto_increment primary key";
$query_arr["drop"] = "drop table $objecttype";
$query_arr["insert"] = "insert into $objecttype ";
$query_arr["delete"] = "delete from $objecttype where id = '%id%'";
$query_arr["update"] = "update $objecttype set ";
$attributes = array();
/* true on first row; for formatting */
$first = true;
while($row = mysql_fetch_assoc($result)) {
$theatt = $row["attribute"];
if($first) {
$attributes_list .= $theatt;
$values_list .= "'%$theatt%'";
$query_arr["update"] .= "$theatt = '%$theatt%'";
$first = false;
} else {
$attributes_list .= ", $theatt";
$values_list .= ", '%$theatt%'";
$query_arr["update"] .= ", $theatt = '%$theatt%'";
}
$attributes[] = $theatt;
$query_arr["create"] .= ", $theatt " . $row["sqltype"];
}
/* finalize each query */
$query_arr["create"] .= ")";
$query_arr["insert"] .= "(" . $attributes_list . ") ";
$query_arr["insert"] .= "values (" . $values_list . ")";
$query_arr["update"] .= " where id = '%id%'";
return $query_arr;
} else {
return null;
} } </pre>
