/* Copyright (c) 2009, Perforce Software, Inc. All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL PERFORCE SOFTWARE, INC. BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ /* Author; Jason Gibson - jgibson@perforce.com EXAMPLE trigger to store spec depot files with the +X filetype archive trigger. Could also serve as a basis for other files with some modification. Has not had extensive testing performed on it. See sqlite_archiver-test.pl for the basic tests. Feedback appreciated! If you find bugs, let me know. Behavior: Will wait for 100 seconds for access to its database before giving up and failing to read/write a file (see sqlite_busyhandler). Will use RAM according to the size of the data read/written. The database will have a persistent journal file called dbname-journal which can safely be removed when the trigger is not in use. Note that obliteration of +X spec depot files is a no-op; you must manually remove the files from the sqlite database via SQL. Compilation: It's basically up to you how to compile it since there are many options, but here's what I have been using: Unix: gcc -c sqlite3.c -D SQLITE_THREADSAFE=0 -D SQLITE_OMIT_LOAD_EXTENSION=1 gcc -c sqlite_archiver.c # static linking results in a bit faster execution since it loads faster. gcc -static sqlite_archiver.o sqlite3.o -o sqlite_archiver Unix/pre-installed SQLite: gcc -o sqlite_archiver sqlite_archiver.c -l sqlite3 -l pthread # (-l dl)? Some warning flags to use: -Wconversion -Wshadow -Wcast-qual -Wwrite-strings Some other useful flags : -D_FORTIFY_SOURCE=2 -fstack-protector-all On Windows: # Visual Studio doesn't like my C, so compile as C++ for now. cl /TP /c sqlite_archiver.c cl /c sqlite3.c cl /Fesqlite_archiver.exe sqlite_archiver.obj sqlite3.obj Two ways you can backup the database: sqlite3 -batch a.db '.dump' > dumpfile.sql sqlite3 -batch a.db '.backup main a.db.ba' Todo: log all command line args when reporting an error, disallow opening log as STDOUT, reduce code duplication in fetch/save/delete_spec funcs. Possible future work: multiple server support, transactions (not strictly necessary). BEGIN TRANSACTION; COMMIT; ROLLBACK; Would need a call to sqlite3_reset( stmt ) if reusing statements. */ # include <stdio.h> # include <stdlib.h> # include <string.h> # include <errno.h> # include <wchar.h> # include <sys/stat.h> //# include <sqlite3.h> // uncomment to use your system SQLite library. # include "sqlite3.h" # ifdef _WIN32 # include <windows.h> # include <fcntl.h> # include <io.h> # define USLEEP Sleep # else # include <sys/statvfs.h> # define USLEEP usleep # endif # if defined( __APPLE_CC__ ) || defined( __FreeBSD__ ) # include <sys/param.h> # include <sys/mount.h> # endif const char* src_id = "$File: //guest/jason_gibson/misc/triggers/sqlite_archiver.c $\n" "$Author: jason_gibson $\n" "$Change: 7387 $\n"; FILE* err_file_handle = NULL; // Write errors to a named log file and to STDOUT (for the end-user.) #include <time.h> void errlog( const char* fmt, ... ) { va_list ap; time_t t = time( NULL ); const char* time_str = ctime( &t ); va_start( ap, fmt ); // STDERR doesn't make it back to the end-user, so use STDOUT. fprintf( stdout, "%s", time_str ); vfprintf( stdout, fmt, ap ); fprintf( stdout, "\n" ); va_end( ap ); // True when initially creating tables, etc. if( !err_file_handle ) return; va_start( ap, fmt ); fprintf( err_file_handle, "%s", time_str ); vfprintf( err_file_handle, fmt, ap ); fprintf( err_file_handle, "\n" ); va_end( ap ); } int sqlite_busyhandler( void *arg3, int num ) { const int wait = 100; // ms const int tries = 1000; // 100 seconds if( wait * num < wait * tries ) { USLEEP( wait * 1000 ); // Tell sqlite to keep trying. return 1; } const float seconds = ((float)(wait * tries)) / 1000; errlog( "ERROR: SQLite database busy! Attempted access" " over %f seconds.\n", seconds ); // Tell sqlite not to try any more. return 0; } int run_sql_unescaped( sqlite3* sqlite, const char* q ) { char *zErrMsg = NULL; int rc = 0; rc = sqlite3_exec( sqlite, q, NULL, 0, &zErrMsg ); if( rc != SQLITE_OK ) { if( !zErrMsg ) errlog( "run_sql_unescaped1(): [%s]: %s", q, sqlite3_errmsg( sqlite ) ); else errlog( "run_sql_unescaped2(): [%s]: %s", q, zErrMsg ); sqlite3_free( zErrMsg ); return 0; } sqlite3_free( zErrMsg ); return 1; } int has_tables( sqlite3* sqlite ) { const char* q = "SELECT COUNT(*) FROM SQLITE_MASTER;"; char **pazResult = NULL, *zErrMsg = NULL; int pnRow = 0, pnColumn = 0, rc = 0; rc = sqlite3_get_table( sqlite, q, &pazResult, &pnRow, &pnColumn, &zErrMsg ); if( rc != SQLITE_OK ) { if( !zErrMsg ) errlog( "has_tables(): [%s]: %s", q, sqlite3_errmsg( sqlite ) ); else errlog( "has_tables(): [%s]: %s", q, zErrMsg ); sqlite3_free( zErrMsg ); return 1; } sqlite3_free( zErrMsg ); if( pazResult && atoi( pazResult[ 1 ] ) ) { errlog( "ERROR: Database tables already exist!\n" ); sqlite3_free_table( pazResult ); return 1; } if( pazResult ) sqlite3_free_table( pazResult ); return 0; } // Return the file@rev and its size in bytes. char* fetch_spec( sqlite3* sqlite, const char* file, const char* rev, size_t* spec_size ) { const char* q = "SELECT `data` from `spec` WHERE `file` = ? AND `rev` = ?"; sqlite3_stmt* stmt = NULL; int rc = sqlite3_prepare_v2( sqlite, q, -1, &stmt, NULL ); if( rc != SQLITE_OK ) { errlog( "ERROR: fetch_spec sqlite3_prepare_v2(): %s\n", sqlite3_errmsg( sqlite ) ); return NULL; } rc = sqlite3_bind_text( stmt, 1, file, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: fetch_spec sqlite3_bind_text() 1: %s\n", sqlite3_errmsg( sqlite ) ); return NULL; } rc = sqlite3_bind_text( stmt, 2, rev, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: fetch_spec sqlite3_bind_text() 2: %s\n", sqlite3_errmsg( sqlite ) ); return NULL; } rc = sqlite3_step( stmt ); // A failure here might mean that the spec rev isn't present. if( rc != SQLITE_ROW ) { errlog( "ERROR: fetch_spec sqlite3_step()1: %s\n\n" "Spec not present? file: %s, rev: %s\n", sqlite3_errmsg( sqlite ), file, rev ); return NULL; } const char* r = (const char*)sqlite3_column_text( stmt, 0 ); if( !r ) { errlog( "ERROR: fetch_spec sqlite3_column_text(): %s\n", sqlite3_errmsg( sqlite ) ); return NULL; } char* spec_data = strdup( r ); *spec_size = (size_t)sqlite3_column_bytes( stmt, 0 ); // It's an error if we have more than one result. if( sqlite3_step( stmt ) != SQLITE_DONE ) { errlog( "ERROR: fetch_spec sqlite3_step()2: %s\n\n" "Duplicate spec entry? file: %s, rev: %s\n", sqlite3_errmsg( sqlite ), file, rev ); free( spec_data ); return NULL; } rc = sqlite3_finalize( stmt ); if( rc != SQLITE_OK ) { errlog( "ERROR: fetch_spec sqlite3_finalize(): %s\n", sqlite3_errmsg( sqlite ) ); return NULL; } return spec_data; } int save_spec( sqlite3* sqlite, const char* file, const char* rev, const char* spec_data, const size_t spec_size ) { const char* q = "INSERT INTO `spec` VALUES ( ?, ?, ? )"; sqlite3_stmt* stmt = NULL; int rc = sqlite3_prepare_v2( sqlite, q, -1, &stmt, NULL ); if( rc != SQLITE_OK ) { errlog( "ERROR: save_spec sqlite3_prepare_v2(): %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_bind_text( stmt, 1, file, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: save_spec sqlite3_bind_text() 1: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_bind_text( stmt, 2, rev, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: save_spec sqlite3_bind_text() 2: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } // Downcasts size_t to int. Probably not a problem. rc = sqlite3_bind_blob( stmt, 3, spec_data, (int)spec_size, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: save_spec sqlite3_bind_blob() 3: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_step( stmt ); if( rc != SQLITE_DONE ) { errlog( "ERROR: save_spec sqlite3_step() 1: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_finalize( stmt ); if( rc != SQLITE_OK ) { errlog( "ERROR: save_spec sqlite3_finalize(): %s\n", sqlite3_errmsg( sqlite ) ); return 0; } return 1; } // Does not give an error when deleting a non-existent file. int delete_spec( sqlite3* sqlite, const char* file, const char* rev ) { const char* q = "DELETE FROM `spec` WHERE `file` = ? AND `rev` = ?"; sqlite3_stmt* stmt = NULL; int rc = sqlite3_prepare_v2( sqlite, q, -1, &stmt, NULL ); if( rc != SQLITE_OK ) { errlog( "ERROR: delete_spec sqlite3_prepare_v2(): %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_bind_text( stmt, 1, file, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: delete_spec sqlite3_bind_text() 1: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_bind_text( stmt, 2, rev, -1, SQLITE_STATIC ); if( rc != SQLITE_OK ) { errlog( "ERROR: delete_spec sqlite3_bind_text() 2: %s\n", sqlite3_errmsg( sqlite ) ); return 0; } rc = sqlite3_step( stmt ); if( rc != SQLITE_DONE ) { errlog( "ERROR: delete_spec sqlite3_step(): %s\n\n" "file: %s, rev: %s\n", sqlite3_errmsg( sqlite ), file, rev ); return 0; } rc = sqlite3_finalize( stmt ); if( rc != SQLITE_OK ) { errlog( "ERROR: delete_spec sqlite3_finalize(): %s\n", sqlite3_errmsg( sqlite ) ); return 0; } return 1; } // Sets up the SQLite library and opens a connection to the database file. sqlite3* init_db( const char* db_file ) { // More or less an arbitrary version to require. It's the latest // and greatest at this time. const int min_lib_version = 3006014; const int lib_version = sqlite3_libversion_number(); // This comparison isn't strictly necessary, but we do it anyways in // case someone is linking against a copy in their system that's really // old. if( lib_version < min_lib_version ) { errlog( "ERROR: minimum version of SQLite not met. " "Needed %d, got %d.\n", min_lib_version, lib_version ); return NULL; } sqlite3* sqlite = NULL; int rc = sqlite3_open_v2( db_file, &sqlite, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL ); if( rc != SQLITE_OK ) { errlog( "ERROR: Couldn't open SQLite database \"%s\": %s\n", db_file, sqlite3_errmsg( sqlite ) ); return NULL; } sqlite3_busy_handler( sqlite, sqlite_busyhandler, (void*)sqlite ); // PRAGMA statements don't result in an error when running them. // Just zero-out the journal file instead of deleting it on // every transaction. const char* q_jnl = "PRAGMA journal_mode = PERSIST;"; run_sql_unescaped( sqlite, q_jnl ); // Don't wait for everything to be on-disk. Faster, but only marginally // less safe. const char* q_sync = "PRAGMA synchronous = OFF;"; run_sql_unescaped( sqlite, q_sync ); return sqlite; } int create_tables( sqlite3* sqlite ) { // Not sure if this is the right place to set the db encoding. The // docs say that you must run this pragma before the main db is // created, but doesn't sqlite3_open_v2() create it when you open it? const char* q_set_charset = "PRAGMA encoding = 'UTF-8';"; if( !run_sql_unescaped( sqlite, q_set_charset ) ) return 0; if( has_tables( sqlite ) ) return 0; const char* q_create_table = "CREATE TABLE `spec` ( `file` TEXT, `rev` TEXT, `data` CLOB );"; if( !run_sql_unescaped( sqlite, q_create_table ) ) return 0; const char* q_create_index = "CREATE INDEX `spec_index` on `spec` ( `file`, `rev`, `data` );"; if( !run_sql_unescaped( sqlite, q_create_index ) ) return 0; return 1; } // Read data from STDIN. Returns the number of bytes read. size_t get_trigger_content( char** buf_ret ) { size_t buf_size = 32768; size_t buf_read = 0; char* buf = (char*) malloc( buf_size ); if( !buf ) { errlog( "ERROR: get_trigger_content() - unable to allocate %d" " bytes: %s\n", buf_size, strerror( errno ) ); return 0; } memset( buf, (int)buf_size, '\0' ); while( !feof( stdin ) ) { char* pos = buf + buf_read; size_t rsize = buf_size - (size_t)( pos - buf ), read = fread( pos, sizeof( char ), rsize, stdin ); if( ferror( stdin ) ) { errlog( "ERROR: get_trigger_content() - fread: %s\n", strerror( errno ) ); free( buf ); return 0; } buf_read += read; if( read == rsize ) { const size_t new_size = buf_size * 2; char* tmp = (char*) realloc( buf, new_size ); if( !tmp ) { errlog( "ERROR: get_trigger_content() - unable to " "realloc %d bytes: %s\n", new_size, strerror( errno ) ); free( buf ); return 0; } buf = tmp; buf_size = new_size; } else break; } *buf_ret = buf; return buf_read; } void print_usage() { printf( "*EXAMPLE*" " P4D SQLite3 archive trigger for spec depot data.\n\n" "Version:\n%sSQLite: %s\n\n", src_id, sqlite3_libversion() ); printf( "USAGE:\n\n" "0. Understand your server's configuration, as these steps may need" " to be modified.\n\n" "1. Before installing as a P4D trigger, initialize the SQLite database\n\n" " trigger_exe /p4root/triggers/spec_trigger_db_file" "\n\n" "2. Install as a P4D trigger (using absolute paths)\n\n" " trigger_name archive //spec/... \"trigger_exe db_file log_file" " %%op%% %%rev%% %%file%%\"\n" "\n" "3. Update typemap to give ownership of spec depot files to the trigger\n\n" " Typemap:\n" " text+X //spec/...\n" "\n" "4. Create a spec depot (if it doesn't exist)\n\n" " p4 depot spec # change the \"Type\" to \"spec\"\n" "\n" "5. Populate the (newly created) spec depot with existing specs\n\n" " p4 admin updatespecdepot -a\n\n" "" ); // Optionally, follow up with the unsupported "p4 retype" to move // all of the existing spec data into the new location. "p4 help retype" } int main( int argc, char** argv ) { #ifdef _WIN32 // Important: On Windows, STDIN defaults to TEXT mode. // Changing it to BINARY to avoid data corruption. if( _setmode( _fileno( stdin ), _O_BINARY ) == -1 ) { errlog( "ERROR: _setmode(): %s\n", strerror( errno ) ); return 1; } #endif // If the trigger's arguments are changed in the trigger table // such that this check is true, then updated specs are lost. // The same goes for other errors. if( argc != 6 && argc != 2 ) { print_usage(); return 1; } const char* db_file = argv[ 1 ]; // /p4root/triggers/data/spec.db sqlite3* sqlite = init_db( db_file ); if( !sqlite ) return 1; if( argc == 2 ) { if( !create_tables( sqlite ) ) { sqlite3_close( sqlite ); return 1; } printf( "Database created successfully.\n" ); sqlite3_close( sqlite ); return 0; } const char* logfile = argv[ 2 ]; // /p4root/spec_trigger.log const char* op = argv[ 3 ]; // delete, read or write const char* arc_rev = argv[ 4 ]; // 1.N const char* arc_file = argv[ 5 ]; // //spec/triggers // Specs can't have spaces in them, so // the whole path will be here. err_file_handle = fopen( logfile, "a+" ); if( !err_file_handle ) { errlog( "ERROR: Couldn't open log file '%s': %s\n", logfile, strerror( errno ) ); sqlite3_close( sqlite ); return 1; } // The op's first character is unique. switch( op[ 0 ] ) { case 'r': { size_t spec_size = 0; char* spec_data = fetch_spec( sqlite, arc_file, arc_rev, &spec_size ); if( !spec_data ) { sqlite3_close( sqlite ); return 1; } // Send the data back to the user on STDOUT. // todo: check fwrite's return? size_t wrote = fwrite( spec_data, spec_size, 1, stdout ); if( ferror( stdout ) ) { errlog( "ERROR: Problem on fwrite() to STDOUT: %s\n", strerror( errno ) ); sqlite3_close( sqlite ); free( spec_data ); return 1; } free( spec_data ); break; } case 'w': { // Content of the updated spec coming from the end-user. char* spec_data = NULL; const size_t spec_size = get_trigger_content( &spec_data ); if( !spec_data ) { errlog( "ERROR: Couldn't get spec data from STDIN!\n" "Spec not saved!\n" ); sqlite3_close( sqlite ); return 1; } // Could check for duplicated inserts here, if paranoid. if( !save_spec( sqlite, arc_file, arc_rev, spec_data, spec_size ) ) { sqlite3_close( sqlite ); free( spec_data ); return 1; } free( spec_data ); break; } case 'd': { // Called on obliterate. // // Not currently exercised, due to P4D bug where it doesn't call // +X triggers on obliterate of spec depot files. if( !delete_spec( sqlite, arc_file, arc_rev ) ) { sqlite3_close( sqlite ); return 1; } break; } default: errlog( "ERROR: Unknown op: %s\n", op ); sqlite3_close( sqlite ); return 1; } int rc = sqlite3_close( sqlite ); if( rc != SQLITE_OK ) { errlog( "ERROR: Problem closing SQLite database: %s\n", sqlite3_errmsg( sqlite ) ); return 1; } if( fclose( err_file_handle ) ) fprintf( stdout, "ERROR: Problem closing log file '%s': %s\n", logfile, strerror( errno ) ); return 0; }
# | Change | User | Description | Committed | |
---|---|---|---|---|---|
#2 | 7423 | Jason Gibson | Add a disclaimer for employee-contributed work. | ||
#1 | 7387 | Jason Gibson |
Example SQLlite archive trigger for spec depot files. See http://blog.perforce.com/blog for more details |