// This is a Preql file, used for setting up a database for developement and testing

//

// It generates tables with various gaps in them, based on the "rating" dataset.

// Assumes prepare_db.pql has already been run.

// Declare table & functions

func run_sql(code) {

print code

force_eval( SQL( nulltype, code ))

}

func drop_table(t) {

run_sql("DROP TABLE IF EXISTS " + t)

}

func create_indices(tbl) {

tbl.add_index("id", true)

tbl.add_index("timestamp")

tbl.add_index(["id", "timestamp"])

}

// Assumes prepare_db already ran

table rating {...}

drop_table("rating_gap1")

drop_table("rating_gap2")

drop_table("rating_gap3")

drop_table("rating_gap1_update0001p")

drop_table("rating_gap2_update0001p")

drop_table("rating_gap3_update0001p")

const table rating_gap1 = rating

const table rating_gap2 = rating

const table rating_gap3 = rating

create_indices(rating_gap1)

create_indices(rating_gap2)

create_indices(rating_gap3)

commit()

table rating_gap1 {

userid: int

movieid: int

rating: float

timestamp: int

}

table rating_gap2 {

userid: int

movieid: int

rating: float

timestamp: int

}

table rating_gap3 {

userid: int

movieid: int

rating: float

timestamp: int

}

rating_gap3[id == 1000] update {id: 2147483548}

// Create many small gaps, for testing low bisection thresholds

run_sql("UPDATE rating_gap1 SET id = id * 1000 + 25000000 where 100000 < id and id <= 500000 ")

// Create increasing gaps, to test many gaps of various sizes at once

run_sql("UPDATE rating_gap2 SET id = cast(id*0.1*id as int) + 26000000 WHERE 10 < id and id < 100000")

// Create one very big gap, to test empty scans and excessive bisection.

run_sql("INSERT INTO rating_gap3(id, userid, movieid, rating, timestamp) VALUES (2047483548, 1, 1, 5.0, 27)")

commit()

print "Create more tables"

const table rating_gap1_update0001p = rating_gap1

const table rating_gap2_update0001p = rating_gap2

const table rating_gap3_update0001p = rating_gap3

create_indices(rating_gap1_update0001p)

create_indices(rating_gap2_update0001p)

create_indices(rating_gap3_update0001p)

rating_gap1_update0001p[random() < 0.000001] update {timestamp: timestamp + 1}

rating_gap2_update0001p[random() < 0.000001] update {timestamp: timestamp + 1}

rating_gap3_update0001p[random() < 0.000001] update {timestamp: timestamp + 1}

rating_gap3[id == 100000] delete [true]

commit()