[sqlite] Introduction
Hello. I've just joined this list, so an introduction may be in order. I'll follow up with a real message separately. I work in IT Services at UCD, Ireland's largest university. While there, I've worked with OS/360, TOPS-20, VM/370, VMS, SunOS (before it became Solaris), and Linux, to mention only some operating systems. These days I work mainly on provisioning for DNS and DHCP. I like SQLite a lot, as it gives me SQL without the administrative overhead of managing (securing ...) a server process. I've used it at home (with Tcl/Tk) to build a document-imaging system to help me with my tax returns, and at work to provide a web-mediated retrieval system for our DHCP and RADIUS logs. I'm currently working on an IPAM application using SQLite to store the data; it's not clear just yet whether this will enter production, or rather end up as a tool to help migration to a well-known IPAM system. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug hunting in SQLite
Thank you for all your support. Thank you, Roger. You pointed out the most likely error: I didn't set the sqlite-task to exclusively use the connection when reading from the database, only when writing. I used a share lock when reading. Thank you, Teg. You made me confident with having large databases. For a moment I believed that I couldn't have databases of some gigabyte. Patrik On 02/27/2012 12:14 AM, Patrik Nilsson wrote: Thank you, Roger, for your piece of advice. I will consider it, but it will be a great deal to rework. Patrik On 02/26/2012 11:41 PM, Roger Binns wrote: On 26/02/12 12:40, Patrik Nilsson wrote: Yes. My program starts two thread, the main one and a worker. There are mutexes so only one at a time can the sqlite interface. In previous postings to the mailing list when people do things like this, I believe that it turning out to be a threading bug in their program is 100%. The onus will be on you to prove that you do not have a threading bug. Some examples. Unless you call sqlite3_db_mutex you cannot safely get the error string. Unless you keep sqlite3_stmt per thread you can have memory changed underneath you. Unless you use SQLITE_TRANSIENT, the memory that gets used may not be what you intended (your symptoms correlate with this BTW). Even if you write perfect thread safe and correct code in 999 places in your code, getting the thousandth one slightly wrong is enough to cause problems. By far the safest thing to do is to either only do SQLite activity in one thread, or to give each thread its own sqlite3 connection. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IPv{4,6} addressing extension
Hello. For a current project, I need an extension to SQLite which supports IP addresses and routing/subnet prefixes. Before I start building one, I'ld be glad to learn of any that are out there, other than those mentioned at either of the following URLs: http://www.mail-archive.com/sqlite-users@sqlite.org/msg35680.html http://freebsd.ntu.edu.tw/FreeBSD/distfiles/sqlite-ext/ipv4-ext.c From what I can see, neither of these supports IPv6, nor provides a sortable encoding for Internet addresses and/or routes. I'm looking for the following functionality: - feature parity between IPv4 and IPv6; - an internal format which allows sorting a collection of prefixes and addresses so that a containing prefix is sorted before a more specific contained prefix, and this before a contained address; - functions to convert between display and internal formats for representing IP addresses and prefixes; - functions for extracting the bounding addresses of a prefix; - functions for testing membership (address or prefix in prefix); - functions for extracting the count of addresses covered by a prefix (perhaps only for IPv4, as a 64-bit integer isn't adequate for doing this with IPv6). I expect to take inspiration from the extensions cited above, as well as from the CPAN Net::IP module. If I'm about to re-invent the wheel, I'ld appreciate a warning. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS simple tokenizer
Thanks Dan. Have just checked how to report bug, and apparently we already have
:)
Please excuse the brevity -- sent from my phone
On 27 Feb 2012, at 07:06, Dan Kennedy danielk1...@gmail.com wrote:
On 02/27/2012 05:59 AM, Hamish Allan wrote:
The docs for the simple tokenizer
(http://www.sqlite.org/fts3.html#tokenizer) say:
A term is a contiguous sequence of eligible characters, where
eligible characters are all alphanumeric characters, the _
character, and all characters with UTF codepoints greater than or
equal to 128.
If I do:
CREATE VIRTUAL TABLE test USING fts3();
INSERT INTO test (content) VALUES ('hello_world');
SELECT * FROM test WHERE content MATCH 'orld';
SELECT * FROM test WHERE content MATCH 'world';
I get no match for the first query, because it doesn't match a term,
but I get a match for the second, whereas according to my reading of
the docs world shouldn't be a term because the underscore character
shouldn't be considered a term break.
Can anyone please help me understand this behaviour?
Documentation bug. Eligible characters are just alphanumerics and
UTF codepoints greater than 128.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IPv{4,6} addressing extension
You can use integer representation of IPv4 addresses as your internal
format for sorting and sumilar tasks:
SELECT IP2INT('0.0.0.0');
==0
SELECT IP2INT('192.168.1.1');
==3232235777
SELECT IP2INT('255.255.255.255');
==4294967295
The integer value of first IP address by mask can be obtained as
SELECT NETFROM('192.168.1.1',32);
==3232235777
And NETTO() function returns integer value of last IP address by mask.
So count of addresses calculation is simple:
SELECT NETTO('192.168.1.1/24') - NETFROM('192.168.1.1/24');
==255
See module documentation for other functions. The home page of extension is
http://sqlite.mobigroup.ru/wiki?name=ext_inet
2012/2/27 Niall O'Reilly niall.orei...@ucd.ie:
Hello.
For a current project, I need an extension to SQLite which supports
IP addresses and routing/subnet prefixes. Before I start building
one, I'ld be glad to learn of any that are out there, other than
those mentioned at either of the following URLs:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg35680.html
http://freebsd.ntu.edu.tw/FreeBSD/distfiles/sqlite-ext/ipv4-ext.c
From what I can see, neither of these supports IPv6, nor provides
a sortable encoding for Internet addresses and/or routes.
I'm looking for the following functionality:
- feature parity between IPv4 and IPv6;
- an internal format which allows sorting a collection of
prefixes and addresses so that a containing prefix is
sorted before a more specific contained prefix, and this
before a contained address;
- functions to convert between display and internal formats
for representing IP addresses and prefixes;
- functions for extracting the bounding addresses of a
prefix;
- functions for testing membership (address or prefix in
prefix);
- functions for extracting the count of addresses covered
by a prefix (perhaps only for IPv4, as a 64-bit integer
isn't adequate for doing this with IPv6).
I expect to take inspiration from the extensions cited above, as
well as from the CPAN Net::IP module.
If I'm about to re-invent the wheel, I'ld appreciate a warning.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] transaction when storing large and many files
I have a .NET C# application that is using System.Data.SQLite.
The application goes through a filesystem and extracts file names,
builds md5sums on every file
and builds up a database. Reason behind is, to reduce the number of
duplicates.
The files as a tar file sum up to 66 GB. It's about a million files. I
learnt that it is a good idea to do all
in one transaction in SQLite, but I wonder if it is still a good idea
when inserting many files as BLOBs (I decided to put everything into the
database).
My first attempt ran overnight and when I came to the machine next
morning, the SQLITe database file was grown
to (only) 3.4GB and an alert box had popped up saying something like
bad library use of System.Data.Sqlite.
Would it be possibly better to commit and close the transaction after
every blob update?
Whether or not I'm inserting a file into the list of resources depends
on the existence of a unique
ref_id which I build from the name_md5sum_size of the file. So, when two
files have the same name, same size
and the same md5sum, I decide they are identical.
When I hit a UNIQUE violation (try ExecuteNonQuery()) I decide not to
UPDATE the record with the BLOB.
If it's a first time entry, I decide to UPDATE the BLOB.
Below is the code. Especially the transaction portion might be wirth
considering.
Thank you.
--
Christoph
using System;
using System.Data;
using System.Data.Common;
using System.Security.Cryptography;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SQLite;
namespace sqliteForm
{
///summary
/// Summary description for Form1
summary
/// public class Form1 : System.Windows.Forms.Form
public partial class Form1 : System.Windows.Forms.Form
{
internal System.Windows.Forms.Button btnSearch;
internal System.Windows.Forms.TextBox txtFile;
internal System.Windows.Forms.Label lblFile;
internal System.Windows.Forms.Label lblDirectory;
internal System.Windows.Forms.ListBox lstFilesFound;
internal System.Windows.Forms.ComboBox cboDirectory;
private int totalcount;
private SQLiteTransaction dbTrans;
private SQLiteCommand resource_cmd;
private SQLiteCommand res_data_cmd;
private SQLiteCommand cdcmd;
private SQLiteCommand delcmd;
private SQLiteParameter size;
private SQLiteParameter creation_date;
private SQLiteParameter name;
private SQLiteParameter data;
private SQLiteParameter md5sum_res;
private SQLiteParameter md5sum_cd;
private SQLiteParameter size_cd;
private SQLiteParameter tag;
private SQLiteParameter suite;
private SQLiteParameter ref_id;
private SQLiteParameter ref_id_cd;
private SQLiteParameter prob_ref;
private SQLiteParameter mandant;
private SQLiteParameter basename;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call.
//
}
///summary
/// Clean up any resources being used.
summary
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///summary
/// Required method for Designer support: do not modify
/// the contents of this method with the code editor.
summary
private void InitializeComponent()
{
this.btnSearch = new System.Windows.Forms.Button();
this.txtFile = new System.Windows.Forms.TextBox();
this.lblFile = new System.Windows.Forms.Label();
this.lblDirectory = new System.Windows.Forms.Label();
this.lstFilesFound = new System.Windows.Forms.ListBox();
this.cboDirectory = new System.Windows.Forms.ComboBox();
this.statusStrip1 = new System.Windows.Forms.StatusStrip();
this.toolStripStatusLabel1 = new
System.Windows.Forms.ToolStripStatusLabel();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.statusStrip1.SuspendLayout();
this.SuspendLayout();
//
// btnSearch
//
this.btnSearch.Location = new System.Drawing.Point(725, 266);
this.btnSearch.Name = btnSearch;
this.btnSearch.Size = new System.Drawing.Size(75, 23);
this.btnSearch.TabIndex = 0;
this.btnSearch.Text = Search;
this.btnSearch.Click += new
System.EventHandler(this.btnSearch_Click);
//
// txtFile
//
this.txtFile.Location = new System.Drawing.Point(380, 432);
this.txtFile.Name = txtFile;
this.txtFile.Size = new System.Drawing.Size(120, 20);
this.txtFile.TabIndex = 4;
this.txtFile.Text = *.*;
//
// lblFile
Re: [sqlite] IPv{4,6} addressing extension
On 27 Feb 2012, at 10:51, Alexey Pechnikov wrote: You can use integer representation of IPv4 addresses as your internal format for sorting and sumilar tasks: Thanks, Alexey. I know that, but it's an approach which fragments the problem which I very much want to unify: it's not common to both IP versions, and it leaves the representation of prefixes mainly to the application. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem compiling the c source produced by lemon
I compiled lemon with no problem and input the grammar file as stated in http://souptonuts.sourceforge.net/readme_lemon_tutorial.html. But when i compile the c source file produced by lemon i am encountering errors; syntax error : ':' from cstdio. What is the solution for this. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite, portable, image store
Hi I would like some help to use SQLite as a general information resource on my pc's. I would like to use open source software but will consider proprietary software. Even better would be portable software, I could take all my photos and the means of searching them on a disk drive. In fact I would like my first project to be a database of all my photos. I think I need: dbadmin tool, db front end, image viewer from BLOB to screen view. Means of storing image in db. To get me started could anyone suggest software tools please. Intend to learn ADO, ColdFusion, I program a little in DBA with msAccess, html, CSS, basic. Have used C. Hello Scriptham, I think MyJSQLView can do this for you. Download and extract the folder contents to your hard drive. Install the Xerail SQLite JDBC to your Java Runtime lib/ext directory. Use default settings for SQLite login, specifying the database file. You can use the test sqliteType.sql file to create your own table with a id key, and blob type. Import the table to create the table in the database. Though MyJSQLView does not have a image viewer for the blob, pictures, I have seen some. You could modify the Java source to view them or make a plugin. If you need additional help let me know I may have some other projects that do have a image viewer that I could steer you to. danap MyJSQLView - myjsqlview.org SQLiteJDBC - xerial.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS simple tokenizer
It was reported before (and not solved)
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55959.html
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hamish Allan
Sent: maandag 27 februari 2012 11:27
To: General Discussion of SQLite Database
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] FTS simple tokenizer
Thanks Dan. Have just checked how to report bug, and
apparently we already have :)
Please excuse the brevity -- sent from my phone
On 27 Feb 2012, at 07:06, Dan Kennedy danielk1...@gmail.com wrote:
On 02/27/2012 05:59 AM, Hamish Allan wrote:
The docs for the simple tokenizer
(http://www.sqlite.org/fts3.html#tokenizer) say:
A term is a contiguous sequence of eligible characters, where
eligible characters are all alphanumeric characters, the _
character, and all characters with UTF codepoints greater than or
equal to 128.
If I do:
CREATE VIRTUAL TABLE test USING fts3(); INSERT INTO test (content)
VALUES ('hello_world');
SELECT * FROM test WHERE content MATCH 'orld'; SELECT * FROM test
WHERE content MATCH 'world';
I get no match for the first query, because it doesn't
match a term,
but I get a match for the second, whereas according to my
reading of
the docs world shouldn't be a term because the
underscore character
shouldn't be considered a term break.
Can anyone please help me understand this behaviour?
Documentation bug. Eligible characters are just
alphanumerics and UTF
codepoints greater than 128.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Determining end locations of phrase matches in FTS3/4
Hi,
I can use the offsets() function to determine the start locations of
phrase matches, but is there any straightforward way to determine the
end locations?
CREATE VIRTUAL TABLE test USING fts4();
INSERT INTO test VALUES ('i am what i am');
SELECT offsets(test) FROM test WHERE content MATCH '\i...a*\';
0 0 0 1 0 1 2 2 0 0 10 1 0 1 12 2
-- what I want to determine is the full range of the phrase match: (0, 14)
Do I need to parse the query expression myself to determine how many
tokens are in the phrase? Is there a function I can tap into for that?
Thanks,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Determining end locations of phrase matches in FTS3/4
On 27 February 2012 17:11, Hamish Allan ham...@gmail.com wrote:
-- what I want to determine is the full range of the phrase match: (0, 14)
Sorry, correction: I want the full range*s* of the phrase match: (0,
4) and (12, 4).
H
On 27 February 2012 17:11, Hamish Allan ham...@gmail.com wrote:
Hi,
I can use the offsets() function to determine the start locations of
phrase matches, but is there any straightforward way to determine the
end locations?
CREATE VIRTUAL TABLE test USING fts4();
INSERT INTO test VALUES ('i am what i am');
SELECT offsets(test) FROM test WHERE content MATCH '\i...a*\';
0 0 0 1 0 1 2 2 0 0 10 1 0 1 12 2
-- what I want to determine is the full range of the phrase match: (0, 14)
Do I need to parse the query expression myself to determine how many
tokens are in the phrase? Is there a function I can tap into for that?
Thanks,
Hamish
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT average timestamp to get average time of day?
On Fri, Feb 24, 2012 at 4:53 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: You're trying to calculate it for individual people? Can you count on night-time people to stay night-time, or do you need to worry about someone shifting by 12 hours? It's for individuals, and it is possible for individuals to shift or drift by any amount. If not, your best bet is, for the night-time people, add, say 6 hours to all of their times, do your average, then subtract the 6 hours back out. Yes, this is a good idea, the same as was given in another response. Thanks. I found that this type of measure is referred to as the mean of circular quantities, and there is even a Wikipedia page about that...I had just never thought about it before. I also found the Mitsuta Method for dealing with this type of issue. But in any approach, things break down if data is strewn all over a 24 hour period. There are cases where this will fail, but you might be able to detect data sets that will cause this issue and ignore them. I will have to just come up with a reasonable check of the data's variance and if I find it is all over the clockface, let the user know that the mean bedtime can't really be computed due to the erratic data. Maybe if only a few outliers are found I could filter them out. I may post a follow-up question regarding that. Thanks! Che ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RE SELECT average timestamp to get average time of day?
On Sat, Feb 25, 2012 at 8:44 AM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
I subtract 12 hours...so any time from 24:00:00 to 12:00:00 will work.
Time from noon to noon becomes midnight to midnight. Then you just add
the 12 hours back in.
CREATE TABLE tijd(t int(11));
INSERT INTO tijd VALUES('2012-02-25 22:00:00');
INSERT INTO tijd VALUES('2012-02-27 01:00:00');
INSERT INTO tijd VALUES('2012-02-27 23:00:00');
sqlite select time(avg(time(t,'-12 hour')+12)*3600,'unixepoch') from
tijd;
23:20:00
You may pick an offset other than 12 depending on your data.
Thank you, this should work well for me, and it is good to see how one
should write it as an SQLite query.
Che
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] perl parameter binding by name
I'm trying to figure out how to bind parameters by name (:VVV or @VVV or $VVV ) using the DBI Perl module. I can't find any examples. All the bind_param methods look like they want a param num ($p_num). Maybe some %attr's need to be set? I want to be able to use SQL like this: $sql = qq/INSERT INTO table (name) VALUES (:name)/; Thanks, Bill ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem compiling the c source produced by lemon
On 26 February 2012 04:17, Everard Mark Padama thetrans...@gmail.com wrote: I compiled lemon with no problem and input the grammar file as stated in http://souptonuts.sourceforge.net/readme_lemon_tutorial.html. But when i compile the c source file produced by lemon i am encountering errors; syntax error : ':' from cstdio. What is the solution for this. Thanks. Have you tried using a C compiler? cstdio sounds like you're using C++, not C. -- Christopher Vance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
