func (self *BundleManager) BundleFileBoolSet(conn *sqlite.Conn, user string, bundle_id int, file_id int, column string, value bool) error { self.mutex.Lock() if conn == nil { conn = self.conn } if self.shutdown { return errors.New("BundleManager is shutdown.") } defer self.mutex.Unlock() if column != "disable_on_error" { old_state, err := self.bundleStateGetNoLock(conn, user, bundle_id) if err != nil { log.Printf("%v\n", err) return err } if old_state != BundleState_Unsubmitted { return errors.New("Can't set string on submitted bundle.") } } //FIXME check for bundle->file. err := conn.Exec("update files set "+column+" = ? where id = ?;", value, file_id) if err != nil { log.Printf("%v\n", err) return err } return nil }
func StoreTableToSqlite(conn *sqlite.Conn, name string, tinfo *Table) error { queryStr := fmt.Sprintf("create table %s (", name) for i := 0; i < len(tinfo.ColumnNames); i++ { if i != 0 { queryStr += "," } queryStr += tinfo.ColumnNames[i] + " " + mapDatatypeToSqlite[tinfo.ColumnTypes[i]] } queryStr += ");" err := conn.Exec(queryStr) if err != nil { return fmt.Errorf("StoreTableToSqlite(): %s , %s,", err.Error(), queryStr) } row := make([]*interface{}, len(tinfo.ColumnTypes)) for i := 0; i < len(row); i++ { row[i] = new(interface{}) } for i := 0; i < tinfo.rowCount(); i++ { queryStr = fmt.Sprintf("insert into %s values (", name) err := tinfo.readRow(i, row) if err != nil { return err } for j := 0; j < len(row); j++ { if j != 0 { queryStr += "," } value := *(row[j]) if value == nil { queryStr += "null" continue } switch tinfo.ColumnTypes[j] { case IntegerDatatype: queryStr += fmt.Sprintf("%d", value) case StringDatatype: re, err := regexp.Compile("'") if err != nil { return err } escaped := re.ReplaceAllString(value.(string), "''") queryStr += fmt.Sprintf("'%s'", escaped) case FloatDatatype: queryStr += fmt.Sprintf("%f", value) } } queryStr += ");" err = conn.Exec(queryStr) if err != nil { return fmt.Errorf("StoreTableToSqlite(): %s , %s,", err.Error(), queryStr) } } return nil }
func (self *BundleManager) BundleFileGroupsSet(conn *sqlite.Conn, user string, bundle_id int, file_id int, groups [][2]string) error { self.mutex.Lock() if self.shutdown { return errors.New("BundleManager is shutdown.") } defer self.mutex.Unlock() state, err := self.bundleStateGetNoLock(conn, user, bundle_id) if err != nil { return err } if state != BundleState_Unsubmitted { return errors.New("The bundle specified is not editable.") } err = self.bundleFileValidateNolock(conn, user, bundle_id, file_id) if err != nil { return nil } err = conn.Exec("delete from groups where file_id = ?;", file_id) if err != nil { log.Printf("%v\n", err) return err } for _, item := range groups { err = conn.Exec("insert into groups(file_id, type, name) values(?, ?, ?);", file_id, item[0], item[1]) if err != nil { log.Printf("%v\n", err) return err } } return nil }
func (self *BundleMD) FileAdd(pacifica_filename string, local_filename string, commit bool) (*BundleFileMD, error) { var conn *sqlite.Conn if commit == false { var err error conn, err = self.bm.connGet() if err != nil { return nil, err } err = conn.Exec("begin transaction") if err != nil { return nil, err } } else { conn = self.bm.conn } id, err := self.bm.bundleFileIdAdd(conn, self.user, self.id, pacifica_filename, local_filename) if err != nil { if commit == false { conn.Close() } return nil, err } return self.bm.bundleFileGet(conn, self, "", -1, id) }
func (self *BundleManager) bundleFileIdAdd(conn *sqlite.Conn, user string, bundle_id int, pacifica_filename string, local_filename string) (int, error) { self.mutex.Lock() if self.shutdown { return -1, errors.New("BundleManager is shutdown.") } defer self.mutex.Unlock() sql := ` insert into files(bundle_id, local_filename, myemsl_filename) values( (select case when (state == ?) then ? else null end from bundles where user = ? and id = ?), ?, ? );` err := conn.Exec(sql, int(BundleState_Unsubmitted), bundle_id, user, bundle_id, local_filename, pacifica_filename) if err != nil { old_state, terr := self.bundleStateGetNoLock(conn, user, bundle_id) if terr != nil { log.Printf("%v\n", terr) return -1, err } if old_state != BundleState_Unsubmitted { return -1, errors.New("Can't add files to submitted bundle.") } s, terr := conn.Prepare("select id, local_filename from files where bundle_id = ? and myemsl_filename = ?;") if terr != nil { log.Printf("%v\n", terr) return -1, err } defer s.Finalize() terr = s.Exec(bundle_id, pacifica_filename) if terr != nil { log.Printf("%v\n", terr) return -1, err } for { if !s.Next() { break } var tid int var tlocal_filename string terr = s.Scan(&tid, &tlocal_filename) if terr != nil { log.Printf("%v\n", terr) return -1, err } if tlocal_filename == local_filename { return -1, errors.New(fmt.Sprintf("The file with the specified Server filename (%s) already exists in the bundle (%d)!", pacifica_filename, bundle_id)) } else { return -1, errors.New(fmt.Sprintf("The file with the specified Server filename (%s) already exists in the bundle (%d) but with a different local filename (%s != %s)!", pacifica_filename, bundle_id, local_filename, tlocal_filename)) } } log.Printf("%v\n", err) log.Printf("SQL: %s, %d, %d, %s, %d, %s, %s\n", sql, int(BundleState_Unsubmitted), bundle_id, user, bundle_id, local_filename, pacifica_filename) return -1, err } s, err := conn.Prepare("select last_insert_rowid();") if err != nil { log.Printf("%v\n", err) return -1, err } defer s.Finalize() for { if !s.Next() { break } var value int err = s.Scan(&value) if err != nil { log.Printf("%v\n", err) return -1, err } return value, nil } return -1, errors.New("Unknown error.") }
// Create fact table by joing source table to dimension tables // Three cases for source data column: // 1. Matches dimension column, fact row value is id in matching dimesnion table row // 2. Is null, and dimension table for column has > 0 rows, fact row value is -1 // 3. Is null, and dimension table for column has 0 rows, fact row value is -1 func (d *Datamart) CreateFactTable(dimDefs map[string]*DimensionDefinition, conn *sqlite.Conn) error { rowCountCache := make(map[string]int) // make list of non-zero dim tables nzDim := make(map[string]*DimensionDefinition) for name, dim := range dimDefs { count, err := getRowCount(name, conn) if err != nil { return err } rowCountCache[name] = count if count > 0 { nzDim[name] = dim } } // make fact table query := "create table fact as select" i := 0 for name, dim := range dimDefs { if i != 0 { query += "," } if rowCountCache[name] == 0 { query += " -1 " + dim.IndexColumn } else { // dim.IndexColumn value - 1 because want ids to start at 0 query += " case when source." + dim.UniqueColumn + " is null then -1 else " + dim.IndexColumn + " - 1 end " + dim.IndexColumn } i++ } query += "\nfrom source" for name, dim := range nzDim { query += " left outer join " + name + " on " query += "source." + dim.UniqueColumn + " = " + name + "." + dim.UniqueColumn } // if all dimension tables a empty return emtpy fact table if i == 0 { query += "\nwhere " query += "1 = 0" } query += ";" err := conn.Exec(query) if err != nil { return fmt.Errorf( "CreateFactTable() error, sqlite error: %s\nquery:\n%s\n", err.Error(), query, ) } return nil }
func (d *Datamart) CreateDimensionTables(dimDefs map[string]*DimensionDefinition, conn *sqlite.Conn) error { var queryStr string var err error for name, dim := range dimDefs { // "integer primary key" creates as auto incrementing column queryStr = fmt.Sprintf( "create table %s (%s integer primary key, %s %s", name, dim.IndexColumn, dim.UniqueColumn, d.findDatatype(dim.UniqueColumn), ) extraStr := "" for _, extra := range dim.ExtraColumns { queryStr += fmt.Sprintf(", %s %s", extra, d.findDatatype(extra)) extraStr += "," + extra } queryStr += "); " err = conn.Exec(queryStr) if err != nil { goto Error } queryStr = fmt.Sprintf( `insert into %s select null, %s %s from (select %s, %s sort %s from source where %s is not null group by %s, sort %s) a order by sort %s;`, name, dim.UniqueColumn, extraStr, dim.UniqueColumn, dim.SortExpr, extraStr, dim.UniqueColumn, dim.UniqueColumn, extraStr, dim.SortDirection, ) err = conn.Exec(queryStr) if err != nil { goto Error } queryStr = fmt.Sprintf( "create index %s_idx on %s (%s);", dim.UniqueColumn, name, dim.UniqueColumn, ) err = conn.Exec(queryStr) if err != nil { goto Error } } return nil Error: return fmt.Errorf( "CreateDimensionTables() error, sqlite error: %s\nquery:\n%s\n", err.Error(), queryStr, ) }