Database specific functionality, inet_aton in MySQL, inet type in PostgreSQL

[Pete]

Hi,

I'm hoping someone can help. I'm a network engineer by day and find
myself dealing with databases with IP addresses in them on a routine
basis. I've just started using ScalaQuery and can't see how to use it
to deal with IPs. The standard way to interact with an IP in mysql is
to have a int field and use inet_ntoa and inet_aton[1] to convert back
and forth to text representations.

mysql> SELECT INET_ATON('10.0.5.9');
-> 167773449

How would I go about calling dbms specific functions like this?

PostgreSQL takes this one step further supporting an inet type with
several operators used solely to manipulate them.[2]
I guess this would build on the dbms specific SQL above and also need
to add a type.

Any one got any pointers they could give me?

Thanks

Pete
[1] http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton
[2] http://www.postgresql.org/docs/8.2/static/functions-net.html

[virtualeyes]

try this:
https://github.com/szeiger/scalaquery-examples/blob/master/src/main/scala/org/scalaquery/examples/CallNativeDBFunction.scala

> [1]http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#f...
> [2]http://www.postgresql.org/docs/8.2/static/functions-net.html

[bryan hunt]

Yeah, SimpleFunction is very useful.

Also use it to get the last inserted ID when working with Auto-
incremented primary keys.

//Call an arbitrary MySql function
val scopeIdentity = SimpleFunction.nullary[Int]("LAST_INSERT_ID")

//Fetch the last autogenerated key via above
def lastInsertedId = {
Query(scopeIdentity).first
}

How would this gentleman's example work, would it be something like
this?

I'm not sure how it would fit together.

object Nets extends ExtendedTable[Net]("users") {
def id = column[Option[Int]]("iUser", O.NotNull)
def rawip = SimpleFunction[String]("ATOI")
def * = id ~ password <> (Net, Net.unapply _)
}

Wouldn't he have to also configure some sort of mapper, like this, but
using SimpleFunctions?

object AssignedAccountTypeMapper
extends MappedTypeMapper[AssignedAccountType.Value, String] with
BaseTypeMapper[AssignedAccountType.Value] {
def map(e: AssignedAccountType.Value) = e.toString
def comap(s: String) = AssignedAccountType.withName(s)
override def sqlTypeName = Some("enum('user','group') NOT NULL
DEFAULT 'user'")
}


I'm confused.

[virtualeyes]

You don't want:

def rawip = SimpleFunction[String]("ATOI")

use this:
def rawip = column[String]("ATOI")

the "simple" conversion occurs when invoking the query (q.list,
q.first, q.foreach, etc.).

Just mimic Stephan's example and see what comes back:
val q1 = for {
dow ~ count <- SalesPerDay.map(s => dayOfWeek2(s.day) ~ s.count)
_ <- Query groupBy dow
} yield dow ~ count.sum.get

instead of dayOfWeek2 you need an inetAton() method that calls the
SimpleFunction method.

Experiment, this is very much a lab oriented space, the author is away
working on SIQ for TypeSafe. He pops in from time-to-time it seems,
but otherwise he's off evolving ScalaQuery into something unbelievably
awesome-r, pardon my engrish, it's my native language ;-)

[Pete]

Hi,

I've played around and got something for the archives. The following
seems to work and has
been serving me well. As ever comments are appreciated.

object implicits {
implicit val InetMapper = new InetMapperImpl
implicit def toInet(x: NamedColumn[Inet]) = new InetFuncs(x)
}
case class Inet(s: String) {
def cidr = s
}
class InetMapperImpl extends BaseTypeMapper[Inet] with
TypeMapperDelegate[Inet] {
override val sqlTypeName = "inet"
override def valueToSQLLiteral(value: Inet) =
"'%s'".format(value.cidr)
def nextValue(r: PositionedResult): Inet = Inet(r.nextString)
def sqlType: Int = java.sql.Types.OTHER
def updateValue(v: Inet, r: PositionedResult): Unit =
r.updateObject(v.s)
def setValue(v: Inet, p: PositionedParameters): Unit =
p.setObject(v.s, sqlType)
def zero: Inet = Inet("0.0.0.0/0")
def apply(p: BasicProfile) = this
def setOption(v: Option[Inet], p: PositionedParameters): Unit = {
if (v eq None) p.setObjectOption(None, sqlType) else
p.setObjectOption(Some(v.get.cidr), sqlType)
}
}
class InetFuncs(x: NamedColumn[Inet]) {
import implicits._
def contains(y: NamedColumn[Inet]): OperatorColumn[Boolean] =
SimpleBinaryOperator[Boolean](">>=").apply(x, y)
def family = SimpleFunction[Int]("family").apply(Seq(x))
def host = SimpleFunction[String]("host").apply(Seq(x))
def masklen = SimpleFunction[Int]("masklen").apply(Seq(x))
def netmask = SimpleFunction[Inet]("netmask").apply(Seq(x))
}

Pete