Re: [sqlite] Problem compiling the c source produced by lemon

[Christopher Vance]

On 26 February 2012 04:17, Everard Mark Padama  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?  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

[sqlite] perl parameter binding by name

[Bill McCormick]

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] RE SELECT average timestamp to get average time of day?

[C M]

On Sat, Feb 25, 2012 at 8:44 AM, Black, Michael (IS)  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

Re: [sqlite] SELECT average timestamp to get average time of day?

[C M]

On Fri, Feb 24, 2012 at 4:53 PM, Marc L. Allen
wrote:

> 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] Determining end locations of phrase matches in FTS3/4

[Hamish Allan]

On 27 February 2012 17:11, Hamish Allan  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  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

[sqlite] Determining end locations of phrase matches in FTS3/4

[Hamish Allan]

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] FTS simple tokenizer

[Jos Groot Lipman]

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  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

Re: [sqlite] SQLite, portable, image store

[danap]

> 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

[sqlite] Problem compiling the c source produced by lemon

[Everard Mark Padama]

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] IPv{4,6} addressing extension

[Niall O'Reilly]

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] transaction when storing large and many files

[Christoph P.U. Kukulies]

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 description for Form1
 ///
 /// 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.
 //
 }

 ///
 /// Clean up any resources being used.
 ///
 protected override void Dispose( bool disposing )
 {
 if( disposing )
 {
 if (components != null)
 {
  components.Dispose();
 }
 }
 base.Dispose( disposing );
 }

 #region Windows Form Designer generated code
 ///
 /// Required method for Designer support: do not modify
 /// the contents of this method with the code editor.
 ///
 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

[Alexey Pechnikov]

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 :
> 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

Re: [sqlite] FTS simple tokenizer

[Hamish Allan]

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  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] IPv{4,6} addressing extension

[Niall O'Reilly]

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] Bug hunting in SQLite

[Patrik Nilsson]

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] Introduction

[Niall O'Reilly]

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