Supporting SQLite3 would be handy for single developer setups. Databases can be backed up by cp and dropped by rm. However, PHP doesn't have built-in SQLite 3 support. The adapter at http://php-sqlite3.sourceforge.net/ appears mostly to work.
What's not working for me, likely SQLite3-related: - favorites: No response at all. - groups: Everyone seems to be in all groups. Untested or attributable to other things: - Avatars: Local permissions problem. - Remote subscriptions: Haven't set up multiple web servers for testing yet. - OpenID: PHP setup doesn't seem to allow it right now. - SMS, Email, XMPP, etc.: Not going to bother at the moment. Signed-off-by: Jason Riedy <[email protected]> --- I don't have time to really support this, so I don't expect this to be included. I'm posting it in case someone else wants to run with it. actions/favorited.php | 19 ++- actions/publictagcloud.php | 9 +- config.php.sample | 11 +- db/laconica_sqlite3.sql | 423 ++++++++++++++++++++++++++++++++++++++++++ lib/popularnoticesection.php | 18 ++- 5 files changed, 468 insertions(+), 12 deletions(-) create mode 100644 db/laconica_sqlite3.sql diff --git a/actions/favorited.php b/actions/favorited.php index 4155b3a..0adafa6 100644 --- a/actions/favorited.php +++ b/actions/favorited.php @@ -166,11 +166,20 @@ class FavoritedAction extends Action function showContent() { - $qry = 'SELECT notice.*, '. - 'sum(exp(-(now() - fave.modified) / %s)) as weight ' . - 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . - 'GROUP BY fave.notice_id ' . - 'ORDER BY weight DESC'; + if(common_config('db','type')=='sqlite3') { + # Replace with linear scaling to avoid exp(). + $qry = 'SELECT notice.*, '. + 'sum(((julianday(\'now\') - 2440587.5)*86400.0 - fave.modified) / %s) as weight ' . + 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . + 'GROUP BY fave.notice_id ' . + 'ORDER BY weight DESC'; + } else { + $qry = 'SELECT notice.*, '. + 'sum(exp(-(now() - fave.modified) / %s)) as weight ' . + 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . + 'GROUP BY fave.notice_id ' . + 'ORDER BY weight DESC'; + } $offset = ($this->page - 1) * NOTICES_PER_PAGE; $limit = NOTICES_PER_PAGE + 1; diff --git a/actions/publictagcloud.php b/actions/publictagcloud.php index 6f5fc75..b595613 100644 --- a/actions/publictagcloud.php +++ b/actions/publictagcloud.php @@ -91,10 +91,17 @@ class PublictagcloudAction extends Action $tags->selectAdd('max(notice_id) as last_notice_id'); if(common_config('db','type')=='pgsql') { $calc='sum(exp(-extract(epoch from (now()-created))/%s)) as weight'; + $tags->selectAdd(sprintf($calc, common_config('tag', 'dropoff'))); + } else if(common_config('db','type')=='sqlite3') { + # Use the second-order approximation exp(-x) = 1 - x + x**2/2. + $scalefact = common_config('tag', 'dropoff'); + $exprstr='(((julianday(\'now\') - 2440587.5)*86400.0 - created)/%s)'; + $calc=sprintf("sum(1 - %s + %s * %s / 2) as weight", $exprstr, $exprstr, $exprstr); + $tags->selectAdd(sprintf($calc, $scalefact, $scalefact, $scalefact)); } else { $calc='sum(exp(-(now() - created)/%s)) as weight'; + $tags->selectAdd(sprintf($calc, common_config('tag', 'dropoff'))); } - $tags->selectAdd(sprintf($calc, common_config('tag', 'dropoff'))); $tags->groupBy('tag'); $tags->orderBy('weight DESC'); diff --git a/config.php.sample b/config.php.sample index db1a216..ff147ca 100644 --- a/config.php.sample +++ b/config.php.sample @@ -37,6 +37,13 @@ $config['site']['path'] = 'laconica'; $config['db']['database'] = 'mysql://laconica:microb...@localhost/laconica'; #$config['db']['ini_your_db_name'] = $config['db']['schema_location'].'/laconica.ini'; +# +# To use SQLite3, use the database interface from http://php-sqlite3.sourceforge.net +#$config['db']['database'] = 'sqlite3:////var/www/laconica/laconica.db'; +# For some reason, the PHP SQLite3 is not setting up variables correctly, +# so you need to leave off the "your_db_name" part. +#$config['db']['ini_'] = $config['db']['schema_location'].'/laconica.ini'; +# # *** WARNING *** WARNING *** WARNING *** WARNING *** # Setting debug to a non-zero value will expose your DATABASE PASSWORD to Web users. # !!!!!! DO NOT SET THIS ON PRODUCTION SERVERS !!!!!! DB_DataObject's bug, btw, not @@ -46,7 +53,9 @@ $config['db']['database'] = 'mysql://laconica:microb...@localhost/laconica'; #$config['db']['db_driver'] = 'MDB2'; #Database type. For mysql, these defaults are fine. For postgresql, set -#'quote_identifiers' to true and 'type' to 'pgsql': +#'quote_identifiers' to true and 'type' to 'pgsql'. For SQLite3, set +#the 'type' to 'sqlite3'. If your version of SQLite3 does not support +#MySQL's quoting style, also set 'quote_identifiers' to true. #$config['db']['quote_identifiers'] = false; #$config['db']['type'] = 'mysql'; diff --git a/db/laconica_sqlite3.sql b/db/laconica_sqlite3.sql new file mode 100644 index 0000000..4a83f4a --- /dev/null +++ b/db/laconica_sqlite3.sql @@ -0,0 +1,423 @@ +/* local and remote users have profiles */ + +create table profile ( + id serial primary key /* comment 'unique identifier' */, + nickname varchar(64) not null /* comment 'nickname or username' */, + fullname varchar(255) /* comment 'display name' */, + profileurl varchar(255) /* comment 'URL, cached so we dont regenerate' */, + homepage varchar(255) /* comment 'identifying URL' */, + bio varchar(140) /* comment 'descriptive biography' */, + location varchar(255) /* comment 'physical location' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ +); +create index profile_nickname_idx on profile(nickname); + +create table avatar ( + profile_id integer not null /* comment 'foreign key to profile table' */ references profile (id) , + original integer default 0 /* comment 'uploaded by user or generated?' */, + width integer not null /* comment 'image width' */, + height integer not null /* comment 'image height' */, + mediatype varchar(32) not null /* comment 'file type' */, + filename varchar(255) null /* comment 'local filename, if local' */, + url varchar(255) unique /* comment 'avatar location' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + + primary key(profile_id, width, height) +); +create index avatar_profile_id_idx on avatar(profile_id); + +create table sms_carrier ( + id serial primary key /* comment 'primary key for SMS carrier' */, + name varchar(64) unique /* comment 'name of the carrier' */, + email_pattern varchar(255) not null /* comment 'sprintf pattern for making an email address from a phone number' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified ' */ +); + +/* local users */ + +create table "user" ( + id integer primary key /* comment 'foreign key to profile table' */ references profile (id) , + nickname varchar(64) unique /* comment 'nickname or username, duped in profile' */, + password varchar(255) /* comment 'salted password, can be null for OpenID users' */, + email varchar(255) unique /* comment 'email address for password recovery etc.' */, + incomingemail varchar(255) unique /* comment 'email address for post-by-email' */, + emailnotifysub integer default 1 /* comment 'Notify by email of subscriptions' */, + emailnotifyfav integer default 1 /* comment 'Notify by email of favorites' */, + emailnotifynudge integer default 1 /* comment 'Notify by email of nudges' */, + emailnotifymsg integer default 1 /* comment 'Notify by email of direct messages' */, +emailmicroid integer default 1 /* comment 'whether to publish email microid' */, + language varchar(50) /* comment 'preferred language' */, + timezone varchar(50) /* comment 'timezone' */, + emailpost integer default 1 /* comment 'Post by email' */, + jabber varchar(255) unique /* comment 'jabber ID for notices' */, + jabbernotify integer default 0 /* comment 'whether to send notices to jabber' */, + jabberreplies integer default 0 /* comment 'whether to send notices to jabber on replies' */, + jabbermicroid integer default 1 /* comment 'whether to publish xmpp microid' */, + updatefrompresence integer default 0 /* comment 'whether to record updates from Jabber presence notices' */, + sms varchar(64) unique /* comment 'sms phone number' */, + carrier integer /* comment 'foreign key to sms_carrier' */ references sms_carrier (id) , + smsnotify integer default 0 /* comment 'whether to send notices to SMS' */, + smsreplies integer default 0 /* comment 'whether to send notices to SMS on replies' */, + smsemail varchar(255) /* comment 'built from sms and carrier' */, + uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI' */, + autosubscribe integer default 0 /* comment 'automatically subscribe to users who subscribe to us' */, + urlshorteningservice varchar(50) default 'ur1.ca' /* comment 'service to use for auto-shortening URLs' */, + inboxed integer default 0 /* comment 'has an inbox been created for this user?' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ + +); +create index user_smsemail_idx on "user"(smsemail); + +/* remote people */ + +create table remote_profile ( + id integer primary key /* comment 'foreign key to profile table' */ references profile (id) , + uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI' */, + postnoticeurl varchar(255) /* comment 'URL we use for posting notices' */, + updateprofileurl varchar(255) /* comment 'URL we use for updates to this profile' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ +); + +create table subscription ( + subscriber integer not null /* comment 'profile listening' */, + subscribed integer not null /* comment 'profile being listened to' */, + jabber integer default 1 /* comment 'deliver jabber messages' */, + sms integer default 1 /* comment 'deliver sms messages' */, + token varchar(255) /* comment 'authorization token' */, + secret varchar(255) /* comment 'token secret' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + + primary key (subscriber, subscribed) +); +create index subscription_subscriber_idx on subscription(subscriber); +create index subscription_subscribed_idx on subscription(subscribed); + +create table notice ( + + id serial primary key /* comment 'unique identifier' */, + profile_id integer not null /* comment 'who made the update' */ references profile (id) , + uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI' */, + content varchar(140) /* comment 'update content' */, + rendered text /* comment 'HTML version of the content' */, + url varchar(255) /* comment 'URL of any attachment (image, video, bookmark, whatever)' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + reply_to integer /* comment 'notice replied to (usually a guess)' */ references notice (id) , + is_local integer default 0 /* comment 'notice was generated by a user' */, + source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */ + +/* FULLTEXT(content) */ +); +create index notice_profile_id_idx on notice(profile_id); +create index notice_created_idx on notice(created); + +create table notice_source ( + code varchar(32) primary key not null /* comment 'source code' */, + name varchar(255) not null /* comment 'name of the source' */, + url varchar(255) not null /* comment 'url to link to' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ +); + +create table reply ( + + notice_id integer not null /* comment 'notice that is the reply' */ references notice (id) , + profile_id integer not null /* comment 'profile replied to' */ references profile (id) , + modified timestamp not null default 'now' /* comment 'date this record was modified' */, + replied_id integer /* comment 'notice replied to (not used, see notice.reply_to)' */, + + primary key (notice_id, profile_id) + +); +create index reply_notice_id_idx on reply(notice_id); +create index reply_profile_id_idx on reply(profile_id); +create index reply_replied_id_idx on reply(replied_id); + +create table fave ( + + notice_id integer not null /* comment 'notice that is the favorite' */ references notice (id), + user_id integer not null /* comment 'user who likes this notice' */ references "user" (id) , + modified timestamp not null /* comment 'date this record was modified' */, + + primary key (notice_id, user_id) + +); +create index fave_notice_id_idx on fave(notice_id); +create index fave_user_id_idx on fave(user_id); +create index fave_modified_idx on fave(modified); + +/* tables for OAuth */ + +create table consumer ( + consumer_key varchar(255) primary key /* comment 'unique identifier, root URL' */, + seed char(32) not null /* comment 'seed for new tokens by this consumer' */, + + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ +); + +create table token ( + consumer_key varchar(255) not null /* comment 'unique identifier, root URL' */ references consumer (consumer_key), + tok char(32) not null /* comment 'identifying value' */, + secret char(32) not null /* comment 'secret value' */, + type integer not null default 0 /* comment 'request or access' */, + state integer default 0 /* comment 'for requests; 0 = initial, 1 = authorized, 2 = used' */, + + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + + primary key (consumer_key, tok) +); + +create table nonce ( + consumer_key varchar(255) not null /* comment 'unique identifier, root URL' */, + tok char(32) not null /* comment 'identifying value' */, + nonce char(32) not null /* comment 'nonce' */, + ts timestamp not null /* comment 'timestamp sent' */, + + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + + primary key (consumer_key, tok, nonce), + foreign key (consumer_key, tok) references token (consumer_key, tok) +); + +/* One-to-many relationship of user to openid_url */ + +create table user_openid ( + canonical varchar(255) primary key /* comment 'Canonical true URL' */, + display varchar(255) not null unique /* comment 'URL for viewing, may be different from canonical' */, + user_id integer not null /* comment 'user owning this URL' */ references "user" (id) , + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ + +); +create index user_openid_user_id_idx on user_openid(user_id); + +/* These are used by JanRain OpenID library */ + +create table oid_associations ( + server_url varchar(2047), + handle varchar(255), + secret bytea, + issued integer, + lifetime integer, + assoc_type varchar(64), + primary key (server_url, handle) +); + +create table oid_nonces ( + server_url varchar(2047), + "timestamp" integer, + salt character(40), + unique (server_url, "timestamp", salt) +); + +create table confirm_address ( + code varchar(32) not null primary key /* comment 'good random code' */, + user_id integer not null /* comment 'user who requested confirmation' */ references "user" (id), + address varchar(255) not null /* comment 'address (email, Jabber, SMS, etc.)' */, + address_extra varchar(255) not null default '' /* comment 'carrier ID, for SMS' */, + address_type varchar(8) not null /* comment 'address type ("email", "jabber", "sms")' */, + claimed timestamp /* comment 'date this was claimed for queueing' */, + sent timestamp /* comment 'date this was sent for queueing' */, + modified timestamp /* comment 'date this record was modified' */ +); + +create table remember_me ( + code varchar(32) not null primary key /* comment 'good random code' */, + user_id integer not null /* comment 'user who is logged in' */ references "user" (id), + modified timestamp /* comment 'date this record was modified' */ +); + +create table queue_item ( + + notice_id integer not null /* comment 'notice queued' */ references notice (id) , + transport varchar(8) not null /* comment 'queue for what? "email", "jabber", "sms", "irc", ...' */, + created timestamp not null /* comment 'date this record was created' */, + claimed timestamp /* comment 'date this item was claimed' */, + + primary key (notice_id, transport) + +); +create index queue_item_created_idx on queue_item(created); + +/* Hash tags */ +create table notice_tag ( + tag varchar( 64 ) not null /* comment 'hash tag associated with this notice' */, + notice_id integer not null /* comment 'notice tagged' */ references notice (id) , + created timestamp not null /* comment 'date this record was created' */, + + primary key (tag, notice_id) +); +create index notice_tag_created_idx on notice_tag(created); + +/* Synching with foreign services */ + +create table foreign_service ( + id int not null primary key /* comment 'numeric key for service' */, + name varchar(32) not null unique /* comment 'name of the service' */, + description varchar(255) /* comment 'description' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */ +); + +create table foreign_user ( + id int not null unique /* comment 'unique numeric key on foreign service' */, + service int not null /* comment 'foreign key to service' */ references foreign_service(id) , + uri varchar(255) not null unique /* comment 'identifying URI' */, + nickname varchar(255) /* comment 'nickname on foreign service' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + + primary key (id, service) +); + +create table foreign_link ( + user_id int /* comment 'link to user on this system, if exists' */ references "user" (id), + foreign_id int /* comment 'link' */ references foreign_user (id), + service int not null /* comment 'foreign key to service' */ references foreign_service (id), + credentials varchar(255) /* comment 'authc credentials, typically a password' */, + noticesync int not null default 1 /* comment 'notice synchronisation, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies' */, + friendsync int not null default 2 /* comment 'friend synchronisation, bit 1 = sync outgoing, bit 2 = sync incoming */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp not null /* comment 'date this record was modified' */, + + primary key (user_id,foreign_id,service) +); +create index foreign_user_user_id_idx on foreign_link(user_id); + +create table foreign_subscription ( + service int not null /* comment 'service where relationship happens' */ references foreign_service(id) , + subscriber int not null /* comment 'subscriber on foreign service' */ , + subscribed int not null /* comment 'subscribed user' */ , + created timestamp not null /* comment 'date this record was created' */, + + primary key (service, subscriber, subscribed) +); +create index foreign_subscription_subscriber_idx on foreign_subscription(subscriber); +create index foreign_subscription_subscribed_idx on foreign_subscription(subscribed); + +create table invitation ( + code varchar(32) not null primary key /* comment 'random code for an invitation' */, + user_id int not null /* comment 'who sent the invitation' */ references "user" (id), + address varchar(255) not null /* comment 'invitation sent to' */, + address_type varchar(8) not null /* comment 'address type ("email", "jabber", "sms") '*/, + created timestamp not null /* comment 'date this record was created' */ + +); +create index invitation_address_idx on invitation(address,address_type); +create index invitation_user_id_idx on invitation(user_id); + +create table message ( + + id serial primary key /* comment 'unique identifier' */, + uri varchar(255) unique /* comment 'universally unique identifier' */, + from_profile integer not null /* comment 'who the message is from' */ references profile (id), + to_profile integer not null /* comment 'who the message is to' */ references profile (id), + content varchar(140) /* comment 'message content' */, + rendered text /* comment 'HTML version of the content' */, + url varchar(255) /* comment 'URL of any attachment (image, video, bookmark, whatever)' */, + created timestamp not null /* comment 'date this record was created' */, + modified timestamp /* comment 'date this record was modified' */, + source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */ + +); +create index message_from_idx on message(from_profile); +create index message_to_idx on message(to_profile); +create index message_created_idx on message(created); + +create table notice_inbox ( + + user_id integer not null /* comment 'user receiving the message' */ references "user" (id), + notice_id integer not null /* comment 'notice received' */ references notice (id), + created timestamp not null /* comment 'date the notice was created' */, + source integer default 1 /* comment 'reason it is in the inbox; 1=subscription' */, + + primary key (user_id, notice_id) +); +create index notice_inbox_notice_id_idx on notice_inbox(notice_id); + +create table profile_tag ( + tagger integer not null /* comment 'user making the tag' */ references "user" (id), + tagged integer not null /* comment 'profile tagged' */ references profile (id), + tag varchar(64) not null /* comment 'hash tag associated with this notice' */, + modified timestamp /* comment 'date the tag was added' */, + + primary key (tagger, tagged, tag) +); +create index profile_tag_modified_idx on profile_tag(modified); +create index profile_tag_tagger_tag_idx on profile_tag(tagger,tag); + +create table profile_block ( + + blocker integer not null /* comment 'user making the block' */ references "user" (id), + blocked integer not null /* comment 'profile that is blocked' */ references profile (id), + modified timestamp /* comment 'date of blocking' */, + + primary key (blocker, blocked) + +); + +create table user_group ( + + id integer primary key /* comment unique identifier */, + + nickname varchar(64) /* comment nickname for addressing */, + fullname varchar(255) /* comment display name */, + homepage varchar(255) /* comment URL, cached so we dont regenerate */, + description varchar(140) /* comment descriptive biography */, + location varchar(255) /* comment related physical location, if any */, + + original_logo varchar(255) /* comment original size logo */, + homepage_logo varchar(255) /* comment homepage (profile) size logo */, + stream_logo varchar(255) /* comment stream-sized logo */, + mini_logo varchar(255) /* comment mini logo */, + + created datetime not null /* comment date this record was created */, + modified timestamp /* comment date this record was modified */ + +); +create index user_group_nickname_idx on user_group (nickname); + +create table group_member ( + + group_id integer not null /* comment foreign key to user_group */ references user_group (id), + profile_id integer not null /* comment foreign key to profile table */ references profile (id), + is_admin boolean default false /* comment is this user an admin? */, + + created datetime not null /* comment date this record was created */, + modified timestamp /* comment date this record was modified */, + + primary key (group_id, profile_id) + +); + +create table related_group ( + + group_id integer not null /* comment foreign key to user_group */ references user_group (id), + related_group_id integer not null /* comment foreign key to user_group */ references user_group (id), + + created datetime not null /* comment date this record was created */, + + primary key (group_id, related_group_id) + +); + +create table group_inbox ( + group_id integer not null /* comment group receiving the message */ references user_group (id), + notice_id integer not null /* comment notice received */ references notice (id), + created datetime not null /* comment date the notice was created */, + + primary key (group_id, notice_id) +); +create index group_inbox_created_idx on group_inbox(created); + +/* Textsearch stuff */ + diff --git a/lib/popularnoticesection.php b/lib/popularnoticesection.php index 89daaa5..ee0e4be 100644 --- a/lib/popularnoticesection.php +++ b/lib/popularnoticesection.php @@ -50,11 +50,19 @@ class PopularNoticeSection extends NoticeSection { function getNotices() { - $qry = 'SELECT notice.*, '. - 'sum(exp(-(now() - fave.modified) / %s)) as weight ' . - 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . - 'GROUP BY fave.notice_id ' . - 'ORDER BY weight DESC'; + if(common_config('db','type')=='sqlite3') { + $qry = 'SELECT notice.*, '. + 'sum((date(\'now\') - fave.modified) / %s) as weight ' . + 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . + 'GROUP BY fave.notice_id ' . + 'ORDER BY weight DESC'; + } else { + $qry = 'SELECT notice.*, '. + 'sum(exp(-(now() - fave.modified) / %s)) as weight ' . + 'FROM notice JOIN fave ON notice.id = fave.notice_id ' . + 'GROUP BY fave.notice_id ' . + 'ORDER BY weight DESC'; + } $offset = 0; $limit = NOTICES_PER_SECTION + 1; -- 1.6.1.235.g87891 _______________________________________________ Laconica-dev mailing list [email protected] http://mail.laconi.ca/mailman/listinfo/laconica-dev
