|
Size: 7112
Comment:
|
Size: 8335
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| The community database is the foundation for a set of applications that will serve the wireless community. The primary focus is a The database is currently implemented in SQL tables on postgresql. | The community database is the foundation for a set of social software applications. The primary focus is a user roster, a node roster, and a whuffie/mojo calculation and management system. The database is currently implemented in SQL tables on postgresql. |
| Line 4: | Line 4: |
| AdamShand, JerrittCollord, AaronBaer, (insert your name here) | AdamShand, JerrittCollord, AaronBaer, DonPark, (insert your name here) |
| Line 6: | Line 6: |
|
A one to one mapping between table A and table B - create a field in table A with attribute 'REFERENCES B'. the field contains a unique ID into table B. [[BR]] A one to many mapping between table A and table B - [[BR]] ""Plan A"" no field is required in table A, create a field in table B that contains the unique ID type of table A. Then add records in table B as needed which all point to the unique ID from table A [[BR]] ""Plan B"" Create a table called A_B that has two fields, uniqueID for A and uniqueID for B with attribute 'REFERENCES B'. Create a field in table A with attribute 'REFERENCES A_B'. Add an entry in table B. This will support cascading deletes since there is a path from a record in A to every record in B.[[BR]] |
community database take three. February 2004 |
| Line 12: | Line 9: |
| User Table [[BR]] | '''Users Table''' [[BR]] |
| Line 17: | Line 14: |
| * 1...member of.............N Communitygroup |
* 1...N Community * 1...N Node |
| Line 20: | Line 18: |
|
* userid - character varying(20) - Unique ID for the user * createdate - ? - record creation date * modificationdate - ? - last modification date |
* id - numeric, primary key - Unique ID * createdate - timestamp - record creation date * modificationdate - timestamp - last modification date * username - character varying(20), unique - login name |
| Line 27: | Line 26: |
| * communitygroupid - An index into the communitygroup table | |
| Line 31: | Line 29: |
| * url_faceimage - character varying(200) - URL for a profile image |
* url_profileimage - character varying(200) - URL for a profile image * url_foaf - character varying(200) - URL to a FOAF description |
| Line 33: | Line 32: |
| * certificationid - numeric - unknown | * certificationid - numeric - An index into an adhoc certification table |
| Line 35: | Line 34: |
|
* MOIN_ID - character varying(24) - login to the moinmoin system * tempkey - character varying(16) - unknown |
* jabber - character varying(100) - User's jabber id (optional) * moinlogin - character varying(24) - login to the moinmoin system (optional) * sponsorid - numeric - Index to the user who sponsors this person * mailingaddressid - numeric - Index into the postal address table Table attributes - Cascade [[BR]] |
| Line 47: | Line 50: |
| Node table [[BR]] | '''Node table''' [[BR]] |
| Line 55: | Line 58: |
| * 1...has...................N Router | |
| Line 57: | Line 61: |
| * nodeid - numeric - unique ID | * id - numeric, primary key - unique ID |
| Line 68: | Line 72: |
| * latitude - character varying(200) - latitude | |
| Line 69: | Line 74: |
| * latitude - character varying(200) - latitude | |
| Line 83: | Line 87: |
|
* pri_pub_ip_block - character varying(128) - IP block provided by DHCP. this would be part of the radio/interface specification. Indexes: node_nodeid_key unique btree (nodeid) |
* dhcp_ip_block - character varying(128) - IP block provided by DHCP. this would be part of the radio/interface specification. |
| Line 88: | Line 90: |
|
"$1" (srid(node_geom) = -1) "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL)) |
"$1" (srid(node_geom) = -1) [[BR]] "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL)) [[BR]] |
| Line 92: | Line 94: |
|
'''Router table''' [[BR]] Description A network router at a node location Table relationships * 1..has.......N Interface Fields * id - numeric, primary key - unique ID * createdate - ? - record creation date * modificationdate - ? - last modification date * name - character varying(80) - Given name of the box or device |
|
| Line 94: | Line 108: |
| 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 :-). |
'''Interface table''' [[BR]] Description An interface = radio + antenna on a router |
| Line 96: | Line 112: |
| 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 |
Table relationships 1..provides...N ProtocolService |
| Line 98: | Line 115: |
| 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 |
Fields * id - numeric, primary key - unique ID * createdate - ? - record creation date * modificationdate - ? - last modification date * name - character varying(10) - Interface name * media - integer - 1=802.3 2=802.11b 3=802.11g 4=802.11a * radio_power - integer - dBm * radio_receive_sensitivity - integer - units? * antenna_gain - integer - dBi * antenna_beamwidth_horiz - integer - degrees * antenna_beamwidth_vert - interger - degrees * antenna_facing_horiz - integer - direction antenna is facing, degrees from north * antenna_facing_vert - integer - direction antenna is facing, degrees from north |
| Line 100: | Line 129: |
|
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) |
'''Addresses Table''' [[BR]] Description Street address of users, nodes, etc. |
| Line 117: | Line 133: |
|
* 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 |
Table Relationships Fields [[BR]] * id - numeric, primary key - unique ID * createdate - ? - record creation date * modificationdate - ? - last modification date * address1 - character varying(100) - Street address line 1 * address2 - character varying(100) - Street address line 2 * city - character varying (50) - City * state - character varying (20) - State or Province * zipcode - character varying (9) - ZIP or ZIP+4 * country - character varying (2) - [http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/iso_3166-1_decoding_table.html ISO 3166] 2 character Country Code '''Community''' [[BR]] Description An organization, like a community wireless group or a company. Fields * id - numeric, primary key - unique ID * createdate - ? - record creation date * modificationdate - ? - last modification date * name - character varying(10) - Organization name |
| Line 136: | Line 157: |
|
* GPS Loc * float lat * float long * float altitude |
'''General table design notes''': |
| Line 141: | Line 159: |
|
* Network * GUID varchar(32,Key) * date created * date modified * name varchar(128) |
When indexing one table to another table, ie when table A contains a foreign key from table B, follow these practices: [[BR]] * 1..1 one to one mapping between table A and table B - create a field in table A with attribute 'REFERENCES B'. this tell postgresql that the field contains a unique ID into table B. * 1..N one to many mapping between table A and table B - Create a table called A_B with two fields. One field is a uniqueID for table A. the other field is uniqueID for B with attribute 'REFERENCES B'. Create a field in table A with attribute 'REFERENCES A_B'. This will support cascading deletes since there is a path from a record in table A to the corresponding record(s) record in B. * All tables should have an 'id' field of type numeric and attribute PRIMARY KEY. most tables should have a creationdate and modificationdate field of type timestamp - the default time is 'without timezone', all stored times should be in GMT. * To make a major change to a table, run 'pg_dump -t tablename databasename > file.sql' to get a record of the existing schema. Then modify and run through psql to recreate the table. Note that this method does not seem to save the PRIMARY KEY attribute so be sure to put that back into the id field. |
| Line 149: | Line 168: |
| * xmlrpc.personaltelco.net:3322 |
* http://xmlrpc.personaltelco.net/user * search(string regex) /* xml sql query */ * retrieve(integer record id) * retrieve(string username) * modify(string fieldname, string fieldvalue) * delete(integer record id) * delete(string username) * http://xmlrpc.personaltelco.net/node * http://xmlrpc.personaltelco.net/map * nodes_in_area(GPS centerpoint, integer radius_in_meters) |
| Line 151: | Line 179: |
|
* inserts * updates * queries * Q: <nodequery sql="select * where name=\"PCHS\" " /> * A: <node> <name>PCHS</name> </node> ... |
The community database is the foundation for a set of social software applications. The primary focus is a user roster, a node roster, and a whuffie/mojo calculation and management system. The database is currently implemented in SQL tables on postgresql.
community database take two. March 2003 AdamShand, JerrittCollord, AaronBaer, DonPark, (insert your name here)
community database take three. February 2004
Users Table BR Description
- A record in the user table refers to an individual person.
Table Relationships
- 1...N Community
- 1...N Node
Fields
- id - numeric, primary key - Unique ID
- createdate - timestamp - record creation date
- modificationdate - timestamp - last modification date
- username - character varying(20), unique - login name
- firstname - character varying(50) - The user's first name
- lastname - character varying(100) - The user's last name
- password - character varying(8) - The user's password, in plaintext
- memberstatus - character varying(10) - An index into the memberstatus table
- phone_home - character varying(15) - Home phone number
- phone_cell - character varying(15) - Cell phone number
- url_homepage - character varying(200) - URL for a home page/info page
- url_profileimage - character varying(200) - URL for a profile image
- url_foaf - character varying(200) - URL to a FOAF description
- comments - character varying(2000) - miscellaneous comments
- certificationid - numeric - An index into an adhoc certification table
- mailto - character varying(100) - User's email address
- jabber - character varying(100) - User's jabber id (optional)
- moinlogin - character varying(24) - login to the moinmoin system (optional)
- sponsorid - numeric - Index to the user who sponsors this person
- mailingaddressid - numeric - Index into the postal address table
Table attributes - Cascade BR
Create Record BR
- Generate a Unique ID - either ask the user or generate one randomly
- Minimum fields to fill: userid
Delete Record BR
- Remove the record in the User table
- Remove records in all tables listed in the relationship table list that have the same unique id
Node table BR Description
- A wireless accesspoint at a particular location.
Table relationships
- 1...owned by..............N User
- 1...managed by............N User
- 1...upstream comes from...N Provider
- 1...has...................N Router
Fields
- id - numeric, primary key - unique ID
- createdate - ? - record creation date
- modificationdate - ? - last modification date
- name - character varying(1000) - Given name of the phone
- providerid - numeric - index into the provider table
- carrierid - numeric - index into the carrier table
- neighborhoodid - numeric - index into the neighborhood table
- bandwidthid - numeric - index into the bandwidth table
- statusid - numeric - index into the status table
- certificationid - numeric - index into the certification table
- comments - character varying(2000) - notes on the node
- latitude - character varying(200) - latitude
- longitude - character varying(200) - long
- altitude - ? - meters above sealevel
- essid - character varying(500) - essid in use (this should index into a table for multiple radios)
- channel - character varying(2) (parrt of the radio channel)
- address - character varying(200) - (this should be an index into an address table)
- city - character varying(100)
- state - character varying(100)
- zip - character varying(20)
- url_sitephoto - character varying(300) - profile shot to include in a profile page
- url_information - character varying(300) - Information about the site
- maintainer1 - character varying(20) - index into user table (change into a new table to support N photos)
- maintainer2 - character varying(20) -
- owner - character varying(20) - index into the user table
- node_geom - geometry - ?
- dhcp_ip_block - character varying(128) - IP block provided by DHCP. this would be part of the radio/interface specification.
Check constraints: BR
"$1" (srid(node_geom) = -1) BR "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL)) BR
Router table BR Description
- A network router at a node location
Table relationships
- 1..has.......N Interface
Fields
- id - numeric, primary key - unique ID
- createdate - ? - record creation date
- modificationdate - ? - last modification date
- name - character varying(80) - Given name of the box or device
Interface table BR Description
- An interface = radio + antenna on a router
Table relationships
1..provides...N ProtocolService
Fields
- id - numeric, primary key - unique ID
- createdate - ? - record creation date
- modificationdate - ? - last modification date
- name - character varying(10) - Interface name
- media - integer - 1=802.3 2=802.11b 3=802.11g 4=802.11a
- radio_power - integer - dBm
- radio_receive_sensitivity - integer - units?
- antenna_gain - integer - dBi
- antenna_beamwidth_horiz - integer - degrees
- antenna_beamwidth_vert - interger - degrees
- antenna_facing_horiz - integer - direction antenna is facing, degrees from north
- antenna_facing_vert - integer - direction antenna is facing, degrees from north
Addresses Table BR Description Street address of users, nodes, etc.
Table Relationships
Fields BR
- id - numeric, primary key - unique ID
- createdate - ? - record creation date
- modificationdate - ? - last modification date
- address1 - character varying(100) - Street address line 1
- address2 - character varying(100) - Street address line 2
- city - character varying (50) - City
- state - character varying (20) - State or Province
- zipcode - character varying (9) - ZIP or ZIP+4
country - character varying (2) - [http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/iso_3166-1_decoding_table.html ISO 3166] 2 character Country Code
Community BR Description
- An organization, like a community wireless group or a company.
Fields
- id - numeric, primary key - unique ID
- createdate - ? - record creation date
- modificationdate - ? - last modification date
- name - character varying(10) - Organization name
General table design notes:
When indexing one table to another table, ie when table A contains a foreign key from table B, follow these practices: BR
- 1..1 one to one mapping between table A and table B - create a field in table A with attribute 'REFERENCES B'. this tell postgresql that the field contains a unique ID into table B.
- 1..N one to many mapping between table A and table B - Create a table called A_B with two fields. One field is a uniqueID for table A. the other field is uniqueID for B with attribute 'REFERENCES B'. Create a field in table A with attribute 'REFERENCES A_B'. This will support cascading deletes since there is a path from a record in table A to the corresponding record(s) record in B.
- All tables should have an 'id' field of type numeric and attribute PRIMARY KEY. most tables should have a creationdate and modificationdate field of type timestamp - the default time is 'without timezone', all stored times should be in GMT.
To make a major change to a table, run 'pg_dump -t tablename databasename > file.sql' to get a record of the existing schema. Then modify and run through psql to recreate the table. Note that this method does not seem to save the PRIMARY KEY attribute so be sure to put that back into the id field.
System for getting at the data
- XML-RPC interface
http://xmlrpc.personaltelco.net/user
search(string regex) xml sql query
- retrieve(integer record id)
- retrieve(string username)
- modify(string fieldname, string fieldvalue)
- delete(integer record id)
- delete(string username)
http://xmlrpc.personaltelco.net/map
- nodes_in_area(GPS centerpoint, integer radius_in_meters)
Implementation
- PHP page talking to a MySQL server
[http://www.nodedb.com/ndbcom.dtd] NodeDB.com xml schema...