Ferreteria/sql/user client: Difference between revisions
< Ferreteria | sql
Jump to navigation
Jump to search
(New page: ==About== * '''Status''': design is under construction * '''Fields''' ** '''CRC''': checksum uniquely identifying user's IP address and browser client software. See notes. * '''Not...) |
m (17 revisions imported: moving this project here) |
||
| (16 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Status''': | * '''Status''': working / tested / debugged | ||
* '''Fields''' | * '''Fields''' | ||
** '''CRC''': [[checksum]] uniquely identifying user's [[IP address]] and browser client software. See notes. | ** '''CRC''': [[checksum]] uniquely identifying user's [[IP address]] and browser client software. See notes. | ||
* '''Notes''': | * '''Notes''': | ||
** For identifying the user, decided to use [[CRC32]] instead of compression because the compressed strings always came out at least as long as the original string. They were probably designed to be effective for rather longer data. | ** For identifying the user, decided to use [[CRC32]] instead of compression because the compressed strings always came out at least as long as the original string. They were probably designed to be effective for rather longer data. | ||
==History== | |||
* '''2009-06-16''' Changing name to singular | |||
* '''2009-09-11''' CRC comes out negative sometimes, so making this SIGNED | |||
* '''2013-11-09''' Renamed from '''shop_client''' to '''user_client''' | |||
* '''2014-09-18''' ''Really'' renamed '''user_client'''; removed unwanted DROP TABLE statement. | |||
* '''2015-06-08''' Changed CRC back to UNSIGNED, because otherwise it refuses to add large values | |||
** [http://php.net/manual/en/function.crc32.php crc32()] can return negative numbers on 32-bit systems, but not on 64-bit. | |||
** Use [http://php.net/manual/en/function.sprintf.php sprintf('%u',$CRC)] to ensure unsigned string for SQL insertion. | |||
** For converting this field from signed to unsigned: so far, I haven't worked out any better method than deleting all the data first (otherwise you get errors). I also deleted everything in {{l/same|user_session}}, since it points here. | |||
* '''2017-01-26''' moved to Ferreteria; changed from MYISAM to INNODB | |||
* '''2017-08-18''' changed Address from VARCHAR(15) to VARCHAR(63) in order to allow for [[IPv6]] (which should only need up to 39 chars, but allowing 63 shouldn't take up any more bits) | |||
* '''2018-08-31''' updated table in live db on cloud5 to use longer varchar; thought I had already done this months ago. cloud4 already fixed. | |||
==SQL== | ==SQL== | ||
< | <syntaxhighlight lang=mysql>CREATE TABLE `user_client` ( | ||
CREATE TABLE ` | `ID` INT NOT NULL AUTO_INCREMENT, | ||
`ID` INT NOT NULL AUTO_INCREMENT, | |||
`CRC` INT UNSIGNED NOT NULL COMMENT "crc32(Address+Browser) - unique integer defined by client specs", | `CRC` INT UNSIGNED NOT NULL COMMENT "crc32(Address+Browser) - unique integer defined by client specs", | ||
`Address` VARCHAR( | `Address` VARCHAR(63) NOT NULL COMMENT "IP address of client", | ||
`Domain` VARCHAR(255) COMMENT "Domain name of client", | `Domain` VARCHAR(255) COMMENT "Domain name of client", | ||
`Browser` VARCHAR(255) COMMENT "Browser USER_AGENT string", | `Browser` VARCHAR(255) COMMENT "Browser USER_AGENT string", | ||
`WhenFirst` DATETIME NOT NULL COMMENT "When this client was first seen", | `WhenFirst` DATETIME NOT NULL COMMENT "When this client was first seen", | ||
`WhenFinal` DATETIME COMMENT "When this client was most recently seen", | `WhenFinal` DATETIME COMMENT "When this client was most recently seen", | ||
PRIMARY KEY(`ID`), | PRIMARY KEY(`ID`), | ||
UNIQUE KEY(`CRC`) | UNIQUE KEY(`CRC`) | ||
) ENGINE = | ) ENGINE = INNODB;</syntaxhighlight> | ||
Latest revision as of 16:42, 22 May 2022
About
- Status: working / tested / debugged
- Fields
- CRC: checksum uniquely identifying user's IP address and browser client software. See notes.
- Notes:
- For identifying the user, decided to use CRC32 instead of compression because the compressed strings always came out at least as long as the original string. They were probably designed to be effective for rather longer data.
History
- 2009-06-16 Changing name to singular
- 2009-09-11 CRC comes out negative sometimes, so making this SIGNED
- 2013-11-09 Renamed from shop_client to user_client
- 2014-09-18 Really renamed user_client; removed unwanted DROP TABLE statement.
- 2015-06-08 Changed CRC back to UNSIGNED, because otherwise it refuses to add large values
- crc32() can return negative numbers on 32-bit systems, but not on 64-bit.
- Use sprintf('%u',$CRC) to ensure unsigned string for SQL insertion.
- For converting this field from signed to unsigned: so far, I haven't worked out any better method than deleting all the data first (otherwise you get errors). I also deleted everything in user_session, since it points here.
- 2017-01-26 moved to Ferreteria; changed from MYISAM to INNODB
- 2017-08-18 changed Address from VARCHAR(15) to VARCHAR(63) in order to allow for IPv6 (which should only need up to 39 chars, but allowing 63 shouldn't take up any more bits)
- 2018-08-31 updated table in live db on cloud5 to use longer varchar; thought I had already done this months ago. cloud4 already fixed.
SQL
CREATE TABLE `user_client` (
`ID` INT NOT NULL AUTO_INCREMENT,
`CRC` INT UNSIGNED NOT NULL COMMENT "crc32(Address+Browser) - unique integer defined by client specs",
`Address` VARCHAR(63) NOT NULL COMMENT "IP address of client",
`Domain` VARCHAR(255) COMMENT "Domain name of client",
`Browser` VARCHAR(255) COMMENT "Browser USER_AGENT string",
`WhenFirst` DATETIME NOT NULL COMMENT "When this client was first seen",
`WhenFinal` DATETIME COMMENT "When this client was most recently seen",
PRIMARY KEY(`ID`),
UNIQUE KEY(`CRC`)
) ENGINE = INNODB;