One of the main problems with the old nodedb is that it wasn't documented very much, if at all. I want to get good solid documentation and backend code in place for the new nodedb so it can be extended by other people, and expanded upon, rather than forcing people to spend ages scratching their heads and reading code.
I also want to make the planning procress as open as possible, so people that want to contribute code get a chance to have their say and send in patches etc.
I think there is a lot of room to be had in doing lots of this kind of planning up front - if all the developers (and potential developers) can agree on (or at least see) a plan then it's much eaiser to have parallel development.
apache_1.3.20.tar.gz php-4.0.6.tar.gz postgresql-7.1.2.tar.gz libpng-1.0.12.tar.gz gd-1.8.4.tar.gz jpegsrc.v6b.tar.gz '--with-gd' '--with-gd-dir=/usr/local' '--with-jpeg-dir=/usr/local' '--with-xpm-dir=/usr/X11R6' '--with-zlib' --with-png=/usr/local' '--with-mysql' '--with-pgsql' XXX - we may want the bcmath extentions to php for more accurate lat/long conversions... http://www.php.net/manual/en/ref.bc.php and gettext for i18n : http://www.php.net/manual/en/ref.gettext.php and possibly snmp for quering access points and doing stats #!/bin/sh -x cd /home/software/unpacked/zsh-4.0.2 && gmake install echo '/usr/local/bin/zsh' >> /etc/shells
We may use javascript - we can do nice mouseovers on the node map for example, but I want to get a decent backend in place first.
Rectangles are tables, ovals are columns, diamonds are relationships. Solid underlined column labels are primary keys, dotted underlines are forign keys.
(this diagram is now out of date, but you get the general idea).
Well i havn't learned UML, but i did find a perl script that will convert a postgresql database into a dia uml diagram:
db table definations. [mostly done] person, login code + login.inc + cookies/session etc. [mostly done] node, port map stuff && check check features && test && RELEASE node_diary test && RELEASE node_bookmarks test && RELEASE node_link test && RELEASE
XXX lots of input validation
finalise sql [nearly done]
create db [can be done now]
finish OSGB <-> osx,osy <-> (Airy)lat,long [done]
do (Airy)lat,long <-> (WGS84)lat,long <-> ???
do user creating/login/session code. [done, finish login verify, do confim email]
do user editing/deleteing
colour node by status [done]
make range circle optional [done]
sync map grid to OS grid. [done]
do node creation, searching, displaying,and editing code. (and port the
existing map code, not hard).
compare functionality with existing db, if missing features, implement them.
look at moveing the existing data && code script to move.
XXX NB the above is hard, the existing data has a lot of crap in it, will
have to e-mail lots of people and ask questions, XXX
XXX byebye international nodes.
XXX need good links page to other projects so we can pass people on, or get
people involved in porting the map code to their local grid.
XXX and a section on the links page for 'X is interested in setting up a
consume like project in Y, please contact him at user@site'.
porting existing data can work in parallel with development.
One other thing: what exactly is a node?
I think the general concensus is that it's one physical router/access point, but it has some of the characteristics of a 'site' which may have multiple routers present. I'm happy to leave it a bit ambigous for the moment, people can always explain wierd setups in the 'description' field.
This and 'Things for the future' could be merged.
map -> streatmap -> ariel photo
map -> mapblast
map : reflect node status [done]
map : node status filter
map : node links status filter
map : jump to pcode [done]
map : jump to lat/long{WGS84, ...}
map : jump to OS grid
map : os grid lines + numbers
node : add by postcode
node : add by lat/long{WGS84, ...}
node : add by osgrid
node : more than one node per person
email person when someone bookmarks one of their nodes
email person when a node they've bookmarked is modified
email person when someone creates a link to one of their nodes.
auto link to all nodes whithin {range} (button, or when a nodes is created).
list nodes in {range} with {bearing, distance}.
In no particular order.
html checking code for the node diary and description fields (we don't want people putting in crap, see how slashdot & kour5hin do it). -> php has strip_tags() -> <P> <B> <I> -> <A> - but not javascript (onMouseOver, onClick, etc.)! http://freshmeat.net/projects/phphtmlparse/ http://phpclasses.upperdesign.com/browse.html/package/172 Replace \' with \\ \'
vars got via POST seem to have slashes added for you, ones via GET don't...
object {methods}
person {create, activate, login, logout, delete, update {url, email}, change password}
node {add, get-by-person, get-by-name, get-by-whatever, update, delete}
node_bookmarks {add, get, delete}
node_diary {add, get, edit(?), delete}
node_link {add, update {status, (bandwidth? physical link type?)}, delete}
A person has:
nodes
node bookmarks
nodes have:
a node diary
links to other nodes.
-- person:
CREATE TABLE "person" (
"person_id" SERIAL PRIMARY KEY,
-- We require e-mail confirmation...
"status" character varying(8) NOT NULL
CHECK (status ~ '^Pending$|^Active$')
DEFAULT 'Pending',
"name" character varying(255) NOT NULL CHECK (name <> ''),
-- the regexp here is overly tight - i may be being too clever here
-- we can't eaisly modify the table (?)...
-- emails have to be globally unique anyway.
"email" character varying(255) UNIQUE NOT NULL CHECK (email ~ '[a-zA-Z0-9_.-]@[a-z0-9A-Z.-]+$'),
"url" character varying(255), -- optional - make UNIQUE?
"passwd" character varying(50) NOT NULL,
"created" timestamp DEFAULT NOW() -- if (!ACTIVE && created > NOW() - 1 month) then nuke_account();
);
-- store a person_id, random token pair for account confirmation
-- we could do without this and just use the md5'd password as the token.
-- it would make the url longer tho. whatever.
-- account_confirm:
CREATE TABLE "account_confirm" (
"person_id" INTEGER NOT NULL REFERENCES person (person_id) MATCH FULL ON DELETE CASCADE,
"token" character varying(10) NOT NULL
);
-- postcodelocation:
-- makeing postcode unique is sane - the old nodedb had several duplicates
--
-- More alarmingly some of the duplicates had different osx and osy
-- co-ords.
-- The date_entered field will let us purge entries older than a couple
-- of months
CREATE TABLE "postcodelocation" (
"postcode" character varying(10) UNIQUE NOT NULL,
"latitude" character varying(14) NOT NULL,
"longitude" character varying(14) NOT NULL,
"osx" BIGINT NOT NULL,
"osy" BIGINT NOT NULL,
"date_entered" timestamp DEFAULT NOW()
);
-- loc-data:
-- loc {x, y, alt} [how do we do locations?!?!?] -- alt will default to 0.
CREATE TABLE "loc-data" (
"x" BIGINT NOT NULL,
"y" BIGINT NOT NULL,
"alt" INTEGER NOT NULL DEFAULT 0
);
-- Speculative
-- In Testing
-- Operational
-- Disabled
--
-- This is probably not the best way to represent this - maybe we
-- should have a table with a numeric status value, and text and link to
-- it.
--
CREATE TABLE "status" (
"status" character varying(12) NOT NULL CHECK (status ~ '^Speculative$|^In Testing$|^Operational$|^Disabled$')
);
-- node:
-- url (?)
-- ip range (?)
CREATE TABLE "node" (
"node_id" SERIAL PRIMARY KEY,
"owner_id" INTEGER NOT NULL REFERENCES person (person_id) MATCH FULL ON DELETE CASCADE,
"name" character varying(255) UNIQUE NOT NULL CHECK (name <> ''),
"description" text,
"created" timestamp DEFAULT NOW(),
"modified" timestamp DEFAULT NOW()
) INHERITS ( "loc-data", "status" );
-- XXX the person_id and node_id pair should be unique.
CREATE TABLE "node_bookmarks" (
"person_id" INTEGER NOT NULL REFERENCES person (person_id) MATCH FULL ON DELETE CASCADE,
"node_id" INTEGER NOT NULL REFERENCES node (node_id) MATCH FULL ON DELETE CASCADE
);
CREATE TABLE "node_diary" (
"node_id" INTEGER NOT NULL REFERENCES node (node_id) MATCH FULL ON DELETE CASCADE,
"date" timestamp DEFAULT NOW(), -- [we don't allow editing once created(?) - have preview button].
"text" text NOT NULL -- [blob, html allowed etc.]
);
--
-- The same pair of nodes can link to each other more than once
-- - e.g. wireless and an ip over ip tunnel...
--
CREATE TABLE "node_link" (
"node_id_a" INTEGER NOT NULL REFERENCES node (node_id) MATCH FULL ON DELETE CASCADE,
"node_id_b" INTEGER NOT NULL REFERENCES node (node_id) MATCH FULL ON DELETE CASCADE,
"link_type" character varying(255) NOT NULL -- [an ENUM, or a link to another table, or just text?]
-- bandwidth (?)
-- ip_info (?)
) INHERITS ("status");
# we want to confirm the person's email works. INSERT INTO person (name, email, url, passwd) VALUES ($name, $email, $url, MD($password)); $person_id = SELECT last_value from person_person_id_seq; # generate a random token for acking the person's email. $token = generate_passwd() INSERT INTO account_confirm (person_id, token) VALUES ($person_id, '$token'); send_confirmation_email($email, urlencode($url.$token));
[person clicks on link in email and jumps to:]
($person_id, $token) = demunge_url(self);
SELECT token FROM account_confirm WHERE person_id = $person_id;
if ($r[0] == urldecode($token) )
{
UPDATE person SET status='Active' WHERE person_id=$person_id;
login_user($person_id);
location('homepage.php');
} else {
error: "wtf???";
}
#
# and run a script weekly to purge Pending users that are > a month old.
#
# email is the 'username', but we have a name too, so we can do # 'hello $name, welcome back to consume!' # etc.... SELECT person_id,name FROM person where email=$email AND passwd=MD5($password); setcookie(foo);
Done. (it's just a sectookie(''), no SQL involved).
# hope this dosn't happen too much ;-) # postgresqls 'ON DELETE CASCADE' stuff takes care of the rest for us. DELETE FROM person where person_id=$person_id
#XXX if their email changes we need to revalidate #XXX more state to be stored (and do we e-mail the old or new e-mail? or #both?) # session: do we reset it our selves, or get them to login again? UPDATE person SET url=$url, email=$email WHERE person_id=$person_id
# ask for the same password twice, and compare etc. # N.B. postgres has no builtin MD5 function, but thats not a problem. UPDATE person SET passwd=MD5($password) WHERE person_id=$person_id
# person needs to be logged in, make sure we have a valid person first &&
# person.status != PENDING.
#
# write some generic 'wizard' code to guide the user through this.
#
INSERT INTO node (owner_id, name, x, y, alt, description, status, url, {possibly more stuff})
VALUES ($person_id, $name, $x, $y, $alt, $descr, $status, $url, {etc...});
SELECT last_value from node_node_id_seq;
# add the new node to the person'd node bookmarks.
# Yes, this is redudant cos we also have node.owner_id, Hmmm, should we
# bother? [have Your nodes, and Your bookmarked nodes?]
INSERT INTO node_bookmarks (person_id, node_id) VALUES ($person_id, $node_id);
# optional (?) confirmation e-mail (?)
# e-mail all nodes whithin range (?) (optional i hope).
SELECT * from node where owner_id = $person_id
all values above, apart from owner_id, or shouldn't we allow people to change node names?
DELETE FROM node WHERE node_id=$id;
INSERT INTO node_bookmarks (person_id, node_id) VALUES ($person_id, $node_id)
# XXX this isn't exactly right, but you get the picture. SELECT n.name, n.x, n.y, nb.node_id FROM node n, node_bookmarks nb WHERE nb.person_id = $person_id AND n.node_id = nb.node_id
DELETE FROM node_bookmarks where person_id=$person_id AND node_id=$node_id
Speculative In Testing Operational Disabled Speculative == I'd like to put a node up, but don't yet know where to put it/how to do it. In Testing == I know where i'm going to put my node and i've got the kit, I'm now playing with ranges and connectivty Operational == My node's running Disabled == My node is down for some reason (moved house, lightning strike, hardware failure, etc).