Diff for "CommunityDatabase" - Personal Telco Project

Differences between revisions 11 and 18 (spanning 7 versions)

DonPark and AdamShand are thinking about ways to create a SQL (or maybe LDAP or XML or something) schema which a community wireless group can use as a universal data collection point. We're trying to catalog everything we can think of that a community wireless group would want to keep track of (yes we know we're doomed to failure :-).

Don, it occurred to me that it might be worth checking out the NoCatAuth SQL schema (etc/nocat.schema in the tarball), they had originally planned for it to be more then just the auth server. -- Adam. I looked it over. added bandwidth and date to Node. Created Network. added URLs and password to User. (8Jun) -- DonPark
How would the local *Cat box regulate bandwidth to the user? Would the local *Cat box have a table with users and their respective classes (owner, member, guest, etc..), or would there be another table for that? Somehow we have to account for the bandwidth shaping aspect of things. We could add a field of Type STATUS, memberstatus (nodeOwner, fullMember, guest), and have the local node shape bandwidth for those values. Make sense? -- DanRichardson

Databases

User
- varchar GUID (32,Key)
- date created
- date modified
- varchar username (64)
- varchar first_name (64)
- varchar last_name (64)
- varchar password (64)
- Type ICQ, icq_id
- Type Jabber, jabber_id
- Type URL, homepage
- Type URL, faceimage
- Type Address, mailing
- Type Login, last_login
- varchar notes (5k)
Node
- varchar GUID (32,Key)
- date created
- date modified
- varchar name (128)
- Type Antenna, antenna_type
- Type Direction antenna_pointing_direction
- int power_output
- int kbits_second
- Type Address, antenna_address
- Type GPS, antenna_gps
- Type Affiliated group [n]
- Type User, Owner [n]
- Type User, Administrator [n]
- varchar notes (5k)
- Type URL, description
- Type URL, login_help
GPS Loc
- float lat
- float long
- float altitude
Network
- GUID varchar(32,Key)
- date created
- date modified
- name varchar(128)

System for getting at the data

XML-RPC interface
xmlrpc.personaltelco.net:3322
inserts
updates
queries
- Q: <nodequery sql="select * where name=\"PCHS\" " />
- A: <node> <name>PCHS</name> </node> ...

Implementation

PHP page talking to a MySQL server

[http://www.nodedb.com/ndbcom.dtd] NodeDB.com xml schema...

community database take two. March 2003 AdamShand, JerrittCollord, AaronBaer, (insert your name here)

I pulled this right out of postgresql since it doesnt appear to be documented anywhere else -- DonPark

User Table

userid	character varying(20)
firstname	character varying(50)
lastname	character varying(100)
password	character varying(8)
memberstatus	character varying(10)
phone1	character varying(15)
phone2	character varying(15)
url	character varying(200)
comments	character varying(2000)
certificationid	numeric
mailto	character varying(100)
MOIN_ID	character varying(24)
tempkey	character varying(16)

Node table

Table "public.node"

Column	Type
nodeid	numeric
name	character varying(1000)
providerid	numeric
carrierid	numeric
neighborhoodid	numeric
bandwidthid	numeric
statusid	numeric
certificationid	numeric
comments	character varying(2000)
longitude	character varying(200)
latitude	character varying(200)
essid	character varying(500)
channel	character varying(2)
address	character varying(200)
city	character varying(100)
state	character varying(100)
zip	character varying(20)
url1	character varying(300)
url2	character varying(300)
maintainer1	character varying(20)
maintainer2	character varying(20)
maintainer3	character varying(20)
maintainer4	character varying(20)
createdate	date
lastuseddate	date
node_geom	geometry
owner	numeric
height	numeric
pri_pub_ip_block	character varying(128)

Indexes: node_nodeid_key unique btree (nodeid) Check constraints: "$1" (srid(node_geom) = -1)

"$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom

IS NULL))

[CategoryDocumentation]

-  ← Revision 11 as of 2002-06-08 09:41:10 →
  Size: 1958
  Editor: DonPark
  Comment:
+  ← Revision 18 as of 2003-04-24 11:47:26 →
  Size: 5180
  Editor: DonPark
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 1:
-DonPark and AdamShand are thinking about ways to create a SQL (or maybe LDAP or XML or something) schema which a community wireless group can use as a universal data collection point.  We're trying to catalog everything we can think of that we're going to want to store in it (yes we know we're doomed to failure :-).
+DonPark and AdamShand are thinking about ways to create a SQL (or maybe LDAP or XML or something) schema which a community wireless group can use as a universal data collection point.  We're trying to catalog everything we can think of that a community wireless group would want to keep track of (yes we know we're doomed to failure :-).
 Line 3:
- Don, it occurred to me that it might be worth checking out the NoCatAuth SQL schema (etc/nocat.schema in the tarball), they had originally planned for it to be more then just the auth server.  -- Adam. I looked it over. added bandwidth and date to Node. Created Network.  -- DonPark
+ Don, it occurred to me that it might be worth checking out the NoCatAuth SQL schema (etc/nocat.schema in the tarball), they had originally planned for it to be more then just the auth server.  -- Adam. I looked it over. added bandwidth and date to Node. Created Network. added URLs and password to User. (8Jun)  -- DonPark



 How would the local *Cat box regulate bandwidth to the user?  Would the local *Cat box have a table with users and their respective classes (owner, member, guest, etc..), or would there be another table for that?  Somehow we have to account for the bandwidth shaping aspect of things.  We could add a field of Type STATUS, memberstatus (nodeOwner, fullMember, guest), and have the local node shape bandwidth for those values.  Make sense?  -- DanRichardson
-Line 10:
+Line 12:
-  * String username 

  * String first_name

  * String last_name

  * String wireless_community_id (off the back of your wifi id card :)
+  * varchar username (64)

  * varchar first_name (64)

  * varchar last_name (64)

  * varchar password (64)
-Line 22:
+Line 24:
  * Node
-Line 46:
+Line 48:
  * Network
-Line 51:
+Line 53:
-Line 55:
+Line 57:
-Line 60:
+Line 62:
   * A: <node> <name>PCHS</name> </node> ...
-Line 66:
+Line 68:
+[http://www.nodedb.com/ndbcom.dtd] NodeDB.com xml schema...



----



community database take two. March 2003

AdamShand, JerrittCollord, AaronBaer, (insert your name here)



I pulled this right out of postgresql since it doesnt appear to be documented anywhere else -- DonPark



User Table

|| userid          || character varying(20)   ||

|| firstname       || character varying(50)   ||

|| lastname        || character varying(100)  ||

|| password        || character varying(8)    ||

|| memberstatus    || character varying(10)   ||

|| phone1          || character varying(15)   ||

|| phone2          || character varying(15)   ||

|| url             || character varying(200)  ||

|| comments        || character varying(2000) ||

|| certificationid || numeric                 ||

|| mailto          || character varying(100)  ||

|| MOIN_ID         || character varying(24)   ||

|| tempkey         || character varying(16)   ||



Node table





                  Table "public.node"

||      Column      ||          Type           ||

|| nodeid           || numeric                 ||

|| name             || character varying(1000) ||

|| providerid       || numeric                 ||

|| carrierid        || numeric                 ||

|| neighborhoodid   || numeric                 ||

|| bandwidthid      || numeric                 ||

|| statusid         || numeric                 ||

|| certificationid  || numeric                 ||

|| comments         || character varying(2000) ||

|| longitude        || character varying(200)  ||

|| latitude         || character varying(200)  ||

|| essid            || character varying(500)  ||

|| channel          || character varying(2)    ||

|| address          || character varying(200)  ||

|| city             || character varying(100)  ||

|| state            || character varying(100)  ||

|| zip              || character varying(20)   ||

|| url1             || character varying(300)  ||

|| url2             || character varying(300)  ||

|| maintainer1      || character varying(20)   ||

|| maintainer2      || character varying(20)   ||

|| maintainer3      || character varying(20)   ||

|| maintainer4      || character varying(20)   ||

|| createdate       || date                    ||

|| lastuseddate     || date                    ||

|| node_geom        || geometry                ||

|| owner            || numeric                 ||

|| height           || numeric                 ||

|| pri_pub_ip_block || character varying(128)  ||



Indexes: node_nodeid_key unique btree (nodeid)

Check constraints: "$1" (srid(node_geom) = -1)

                   "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom

 IS NULL))







----