1. Owen Nelson
  2. django-ipyfield
  3. Issues
Issue #3 new

Use efficient datatypes

Rowan Thorpe
created an issue

Presently (presumably as a temporary measure) the only datatype used in the database end is VARCHAR(39), and then straight conversion string -> IPy.IP -> string is used. Obviously this will be much more efficient when more efficient datatypes are used. After exhaustive Googling I have found what I believe to be a list of the "best" (most efficient/consistent) types for each of the databases that Django apparently handles. I will put it as a first comment to this issue, where it can be improved upon, corrected or whatever in subsequent comments. Obviously the code in model.py (and elsewhere) needs to be changed to convert to/from these types too.

Comments (1)

  1. Rowan Thorpe reporter

    Obviously where:

    1. There is no native IP(v4 & v6) type (like PostgreSQL)

    2. 128-bit numerical values can't be natively stored (everywhere?)

    The most efficient way around that is to store as BLOB/VARBINARY/RAW(16) and pack/unpack to/from integer IP values (which then allows range comparisons, etc - which string values don't).

    For these DBs the lengths can be set explicitly and validated:

    • PostgreSQL -> INET (handles v4 and v6)

    • MySQL -> VARBINARY(16)

    • Oracle -> RAW(16)

    • SQL Server -> VARBINARY(16)

    For these DBs lengths have to be validated in-code:

    • Generic -> BLOB

    • SQLite3 -> BLOB

    • Firebird -> BLOB

    Extra Notes:

    • From v5.6.3 and up MySQL has INET6_NTOA and INET6_ATON functions to convert to/from VARBINARY(16).

    I am relatively new to Django, so am sure any attempt I make to code this in will be buggy at best. Nevertheless I am keen to see the functionality happen so will try to give whatever input/feedback is requested.

    NB: If wishing to store IP networks as well as just IP addresses (both are covered by IPy) then an extra field would be needed (e.g. TINYINT) for the prefixlength in each case. If wishing to store arbitrary IP ranges (perhaps not covered by a single CIDR, also covered by IPy) then two such "binary" fields would be needed (a "from" and a "to").

  2. Log in to comment