[sqlite] FTS3/4 bug with tokenize=icu and parentheses

[Stadin, Benjamin]

A few days ago I suspected a bug with SQLite FTS4 parenthesis when using
the ICU tokenizer. To rule out it was my slightly altered SQLite build
environment to compile SQLite + ICU for the iPhone, I reproduced this
issue today with the current version of SQLite on OS X.

Steps to reproduce:

SQLite (3.8.4.3) was configured and built on OS X 10.9.2 using:
./configure CFLAGS="-DSQLITE_ENABLE_ICU `/opt/local/bin/icu-config
—cppflags`-DSQLITE_ENABLE_FTS3_PARENTHESIS -I/usr/local/opt/icu4c/include
-L/usr/local/opt/icu4c/lib" LDFLAGS="`/opt/local/bin/icu-config --ldflags`“


./sqlite3 test.sqlite3



— Test without ICU
create virtual table test using fts4(intcol, stringcol);
insert into test(intcol, stringcol) values (1, "a");
insert into test(intcol, stringcol) values (2, "b");
insert into test(intcol, stringcol) values (3, "c");

insert into test(intcol, stringcol) values (4, "c");




select * from test where test match '(intcol:1 OR intcol:2)';
1|a
2|b


=> OK

sqlite> select * from test where test match '(intcol:1 OR intcol:2) AND
stringcol:a';
1|a



=> OK

select * from test where test match '(intcol:1 OR intcol:2 OR intcol:3 OR
intcol:4) AND (stringcol:a* OR stringcol:c*)';
1|a
3|c
4|c


=> OK

drop table test;

— Test with ICU
SELECT icu_load_collation("de_DE", "LOCALIZED");


create virtual table test using fts4(tokenize=icu LOCALIZED, intcol,
stringcol);
insert into test(intcol, stringcol) values (1, "a");
insert into test(intcol, stringcol) values (2, "b");
insert into test(intcol, stringcol) values (3, "c");
insert into test(intcol, stringcol) values (4, "c");

select * from test where test match '(intcol:1 OR intcol:2)';




=> No result. Expected to return two rows 1|a, 2|b.

select * from test where test match '(intcol:1 OR intcol:2 OR intcol:3 OR
intcol:4) AND (stringcol:a* OR stringcol:c*)';
2|b
3|c


=> Wrong result. 2|b must not be in the result, and 4|c is is missing.

sqlite> select * from test where test match 'intcol:1 OR intcol:2';
1|a
2|b


=> Result OK

However, leaving away "tokenize=icu LOCALIZED“ and adding „COLLATE
LOCALIZED“ to each column in the create statement returns the expected
results. The documentation however doesn’t mention COLLATE. Therefore I’m
not sure if that has any drawback, e.g. whether or not it’s using the ICU
tokenizer.

Regards
Benjamin Stadin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows Phone 8.1

[Ryan Finnesey]

Hi Lane

I was wondering if you have found a solution.  I am running into the same issue 
with Azure Mobile Services and a Windows Phone 8.1 App.

Cheers
Ryan


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Lane Williams
Sent: Sunday, April 13, 2014 4:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Windows Phone 8.1

I have used the SQLite on several Windows Store and Windows Phone projects 
including the latest version 3.8.4.3 on a Windows Phone 8 project from VS 2013, 
they all work Great.

However the 3.8.4.3 version will not recognize in my latest Windows Phone
8.1 project.  I am trying to use the new "Universal Apps" method in VS 2013, 
the Windows 8.1 will load (3.8.4.3) but the Windows Phone 8.1 does not show the 
SQLite as an option to load.

Is an update in the works to support the latest Windows Phone 8.1 platform.  Is 
there a suggested work around for using the current version of SQLite with 
Windows Phone 8.1.

Thanks,
Lane
___
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] printf function is shown in docs but not found when I try it

[Simon Slavin]

On 18 Apr 2014, at 2:49pm, c...@isbd.net wrote:

> Xubuntu 13.10 (which I'm currently using) has sqlite 3.7.17.
> 
> Xubuntu 14.04 has sqlite 3.8.2, I'm not sure if they're likely to move
> to anything newer during its lifetime. 

If you're writing C or C++ code then you don't need to use something supplied 
with your OS.  Just download current amalgamation version files and include the 
.h and .c files in your application source code.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf function is shown in docs but not found when I try it

[Andy Goth]

On 4/18/2014 8:49 AM, c...@isbd.net wrote:

Alternatively are there any other reasonably practical ways to format
numeric output?  Specifically I want to format floating point numbers
into a fixed format like NNN.N.


Your application can provide custom functions callable from queries.

Tcl example:

$ tclsh
% package require sqlite3
% sqlite3 db :memory:
% db function printf format
% db eval {select printf('%05.1f', 12.3)}
012.3

--
Andy Goth | 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding SQLITE installation

[DELOGET, Emmanuel]

Hello, 

Le jeudi 17 avril 2014 à 12:04 -0700, Nitin Nimran a écrit :
> I want to install sqlite for - Linux (X86_64) and NetBSD 6.1  (X86_64)
> 
> What should be appropriate options for executing configure script to
> install for specified target.
> 
> I don’t see any option to specify x86_64-linux and x86_64-netbsd

If you *compile* on the target, you don't need to specify any option.

If you *cross-compile** sqlite, then the standard configuration option
are to be used (--target=${target_triplet}, --host=${target_triplet}).


> Can anyone help me at earliest convenience…
> 
> Regards,
> Nitin

BR,

-- Emmanuel Deloget

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf function is shown in docs but not found when I try it

[cl]

Richard Hipp  wrote:
> On Thu, Apr 17, 2014 at 8:03 AM,  wrote:
> 
> > In the documentation at http://www.sqlite.org/lang_corefunc.html
> > there's a printf function, however when I try and use it in sqlite3
> > (on xubuntu 13.10) it says "Error: no such function: printf".
> >
> > Is this because I need to load some extra library to make it work or
> > is it just not available in the version of sqlite3 on Ubuntu, or what?
> >
> > All the other functions in 'core functions' seem to work.
> >
> 
> The printf() function was added for SQLite version 3.8.3.  What version of
> SQLite does xubuntu have installed?

Ah, that explains it, thank you!

Xubuntu 13.10 (which I'm currently using) has sqlite 3.7.17.

Xubuntu 14.04 has sqlite 3.8.2, I'm not sure if they're likely to move
to anything newer during its lifetime.  

I guess I could build/install a newer sqlite on my system.
Alternatively are there any other reasonably practical ways to format
numeric output?  Specifically I want to format floating point numbers
into a fixed format like NNN.N.

-- 
Chris Green
·

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

[mm.w]

Hello,

First off, agreed an ODBC interface would be more suitable and secure,
don't trust today-most-web-devs to make the RPC layer secure
[cruft-obfuscation open backdoor they know], not trolling around just the
day to day reality; when I sit on some server-codes ... if you can call
that "code" at all.

Second off, concurrent writings/readings to a sqlite's db-file is a bit
odd, challenging 40 years of evolution,
e.g there are a lot of stuff you don't need at runtime, then do you extract
 a suitable "workable runtime image"
or you just use the provided plain-sqlite-interface ?

Best.



On Fri, Apr 18, 2014 at 7:23 AM, Dennis Jenkins  wrote:

> On Fri, Apr 18, 2014 at 6:32 AM, Christian Smith <
> csm...@thewrongchristian.org.uk> wrote:
>
> > On Tue, Apr 01, 2014 at 01:08:59PM +, Harmen de Jong - CoachR Group
> > B.V. wrote:
> > > We have built our own SQLite database server application and are
> > considering making this open source. Since there will be some time
> involved
> > into making it an open source project and maintaining it, we would first
> > like to make an inventory to find out if there is any interest in this
> > server application.
> > >
> > > ==> How it works:
> > >
> > > Clients can communicate with this server over TCP/IP sockets. Queries
> > are submitted and returned in XML format (BLOB results are returned in
> > binary format to prevent CPU intensive encoding and decoding). The server
> > application is written in native Visual C++ (without using MFC). If we
> > would make this project open source we would also include a client
> example.
> >
> >
> > I dare say that parsing and encoding XML would be more processor
> intensive
> > that BLOB encoding.
> >
> > IMO, you'd also increase your potential target audience if you could also
> > provide reasonably functional JDBC, ADO.NET, PHP and/or Python database
> > drivers. Providing any of these would allow existing users to plug your
> new
> > database into existing applications with the minimal of fuss.
> Personally, I
> > don't like the idea of XML as the protocol, largely because of the
> parsing
> > overhead, and have been looking at a similar server based on RPC, but for
> > debugging purposes it would be great.
> >
> > Regards,
> > Christian
> > ___
> >
>
>
> Not all XML libraries are inefficient.  TinyXml2 would probably work great
> for this use-case.  (disclaimer: I use TinyXml2 in an unrelated project,
> but I am not the author).
> ___
> 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] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 7:29pm, Donald Griggs  wrote:

> I tried a simple test with recent [Postgres] (9.3.4) version (using default 
> settings,
> if that matters) and verified that an insert with oversized string will
> fail to insert, unless the overage characters are spaces only, in which
> case it succeeds with truncation.

Made even more complicated because MySQL has a 'strict' mode where this will 
fail, but also another mode where it will issue a warning message, but a 
truncated copy of the string will be stored.

I had seen the truncating behaviour so many times I assumed it must be in the 
Standard.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Donald Griggs]

I know very little about Postgres, but I see this interesting page in their
documentation:

http://www.postgresql.org/docs/8.0/interactive/datatype-character.html

Excerpt:

*An attempt to store a longer string into a column of these types will
result in an error, unless the excess characters are all spaces, in which
case the string will be truncated to the maximum length. (This somewhat
bizarre exception is required by the SQL standard.) If the string to be
stored is shorter than the declared length, values of type character will
be space-padded; values of type character varying will simply store the
shorter string.*

*If one explicitly casts a value to character varying(n) or character(n),
then an over-length value will be truncated to n characters without raising
an error. (This too is required by the SQL standard.)*

*Note: Prior to PostgreSQL 7.2, strings that were too long were always
truncated without raising an error, in either explicit or implicit casting
contexts.*



I tried a simple test with recent (9.3.4) version (using default settings,
if that matters) and verified that an insert with oversized string will
fail to insert, unless the overage characters are spaces only, in which
case it succeeds with truncation.   I did NOT experiment with number
conversions, nor with a version prior to 7.2, nor did I attempt to review
the sql standards.

-
drop table if exists j2;
create table j2( a2 varchar(2), b3 varchar(3));
insert into j2 values ('a', 'b');
-- following will fail with error
insert into j2 values ('aaa', 'bbb');

ERROR:  value too long for type character varying(2)
** Error **

-- but following insert will succeed with truncated column b3
insert into j2 values ('aa', 'bbb');
select *, length(b3) from j2;

Output was:
"a";"b";1
"aa";"bbb";3

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does Installer.exe do in the binary packages?

[Joe Mistachkin]

Drago, William @ MWG - NARDAEAST wrote:
>
> This is a binary package, not a setup or bundle package, so what does the
> installer do?
> 

The binary package simply includes all binaries built from the
System.Data.SQLite
source tree and their associated files (i.e. the ones that end up in the
binary
output directory during the build process).

>
> BTW, SQLite works just fine for me without running the installer, I'm just
> wondering what it's there for and if there things that I'm missing out on
by
> not running it.
>

The installer is only used when setting up the design-time components for
Visual
Studio.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Luuk]

On 18-4-2014 18:56, Simon Slavin wrote:


On 18 Apr 2014, at 5:28pm, Dominique Devienne  wrote:


I'm not sure where you get that declaring a column as varchar()
implicitly truncate


While I can't find any reference one way or another in a SQL standard, all 
implementations I've seen that understand VARCHAR(n) truncate for any column 
defined with a number inside the brackets:



"When character expressions are converted to a character data type of a different 
size, values that are too long for the new data type are truncated."



"If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR 
column that exceeds the column's maximum length, the value is truncated to fit and a 
warning is generated."

('warnings' in MySQL are not an indication of failure and are routinely 
ignored.)

SQLite is unusual in that it ignores the number in the brackets.  I don't know 
what various implementations do for just VARCHAR or for VARCHAR() with no 
number inside the brackets.

Simon.


1> select @@version
2> go


--
--
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1  (Build 7601: 
Service Pack 1)



(1 rows affected)
1> create table test (a varchar(4));
2> go
1> insert into test values('abcd');
2> insert into test values('ABCDE');
3> go

(1 rows affected)
Msg 8152, Level 16, State 14, Server ACER\MSSQL, Line 2
String or binary data would be truncated.
The statement has been terminated.
1> select * from test;
2> go
a

abcd

(1 rows affected)
1>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite has no sqlite_bind_auto or similary named function

[Max Vlasov]

On Fri, Apr 18, 2014 at 8:08 PM, Andy Goth  wrote:
> On 4/18/2014 12:29 AM, Max Vlasov wrote:
>>
>> So it seems like if general queries allow affinity automatical
>> selection while bind api does not have the corresponent function. I
>> know that I can analize incoming data myself, but since general
>> queries use a similar function probably, making some kind of
>> sqlite_bind_auto should be no big deal. But probably this decision was
>> deliberate.
>
>
> This was done for C compatibility.  A C++ wrapper for SQLite can have an
> sqlite_bind_auto() function which is overloaded for a variety of types,
> but it would in reality be multiple functions that have the same name
> but otherwise different type signatures.

I meant something different. A function accepting a text value while
sqlite doing affinity conversion according to how the value "looks".
Actually it seems that all the problems I faced was related to the
type being typeless in declaration. For integer-declared fields sqlite
correctly converts them to the integer affinity even with
sqlite3_bind_text function. So it makes no sense to force other kind
of detection if the type is declared. But if I stay with typeless
fields, I will definitely will have problems with indexes on them,
because seems like the query planner relies on declared types and
doesn't care how many rows have particular affinity. So it looks like
I should prescan some rows for type detection if I want an automatic
conversion not knowing type info in advance

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does Installer.exe do in the binary packages?

[Drago, William @ MWG - NARDAEAST]

I just downloaded sqlite-netFx20-binary-Win32-2005-1.0.92.0.zip and in addition 
to the core SQLite .dll files there are a lot of other files including an 
installer.

This is a binary package, not a setup or bundle package, so what does the 
installer do?

I read the information on the download page and there is no information about 
installers.
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

BTW, SQLite works just fine for me without running the installer, I'm just 
wondering what it's there for and if there things that I'm missing out on by 
not running it.

Thanks,
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 5:28pm, Dominique Devienne  wrote:

> I'm not sure where you get that declaring a column as varchar()
> implicitly truncate

While I can't find any reference one way or another in a SQL standard, all 
implementations I've seen that understand VARCHAR(n) truncate for any column 
defined with a number inside the brackets:



"When character expressions are converted to a character data type of a 
different size, values that are too long for the new data type are truncated. "



"If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR 
column that exceeds the column's maximum length, the value is truncated to fit 
and a warning is generated."

('warnings' in MySQL are not an indication of failure and are routinely 
ignored.)

SQLite is unusual in that it ignores the number in the brackets.  I don't know 
what various implementations do for just VARCHAR or for VARCHAR() with no 
number inside the brackets.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Dominique Devienne]

On Fri, Apr 18, 2014 at 4:42 PM, Simon Slavin  wrote:
> On 18 Apr 2014, at 3:21pm, Dominique Devienne  wrote:
>> No need to resort to triggers. A simple check constraint will do
>
> A constraint can prevent you from putting too-long values in the field.  A 
> trigger can truncate the value to the correct length, which is what declaring 
> a column as VARCHAR() would do.

I'm not sure where you get that declaring a column as varchar()
implicitly truncate, but at least Oracle's VARCHAR2 behaves just like
the check constraint I demonstrated in SQLite earlier. --DD

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t (name varchar2(2));

Table created.

SQL> insert into t values ('a');

1 row created.

SQL> insert into t values ('aa');

1 row created.

SQL> insert into t values ('aaa');
insert into t values ('aaa')
  *
ERROR at line 1:
ORA-12899: value too large for column "xxx"."T"."NAME" (actual: 3, maximum: 2)


SQL>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite has no sqlite_bind_auto or similary named function

[Andy Goth]

On 4/18/2014 12:29 AM, Max Vlasov wrote:

The problem was with my program that automatically converts xml data
into an sqilte table. It looks for an attribute and appends a column
if it does not exists, but stating no particular type. All values were
appended with sqlite_bind_text. Everything was fine, but an index
created after this on a ParentId field that was in real life integer
or null, actually appeared text-based. Sure, typeof all inserted
fields appeared to be text.

So it seems like if general queries allow affinity automatical
selection while bind api does not have the corresponent function. I
know that I can analize incoming data myself, but since general
queries use a similar function probably, making some kind of
sqlite_bind_auto should be no big deal. But probably this decision was
deliberate.


This was done for C compatibility.  A C++ wrapper for SQLite can have an
sqlite_bind_auto() function which is overloaded for a variety of types,
but it would in reality be multiple functions that have the same name
but otherwise different type signatures.  C doesn't mangle function
symbol names according to argument types, so each function must have a
distinct name.

You're free to write an sqlite_bind_auto() function (use whatever name
you choose) that takes a text argument but tries to coerce the data to
integer or real if it thinks it can do so reversibly.  It's not enough
to call strtod() or whatever on the argument and check for success; you
have to make sure the input is that number expressed in canonical form.

There are many potential gotchas here.  For instance, consider entering
telephone numbers.  You may have a database which has entries like:

(800) 123-4567

But also has:

8001234567

These should both be represented as text even though the latter appears
to be an integer (which, by the way, is larger than the 32-bit signed
maximum integer, so don't go putting it in an int).

And why should they both be text?  Because that's in accordance with the
database schema.

Now we're getting to the real reason SQLite doesn't have this auto
function you're asking about.  That is: SQLite expects the programmer to
know the schema and to embed it in the program's structure.  Providing
an automatic function means the programmer gives some of that control
and responsibility back to SQLite, and SQLite may well do it incorrectly
or suboptimally.  Better not to have a feature that breeds bugs and
solves a non-problem.

What you're doing is atypical usage.  You don't have a prearranged
schema, you're trying to detect it from your incoming data.  If you're
going to take on that responsibility, you have to not only figure out
your tables and columns, but also their types.  And if you don't want to
do that latter bit of work, you have to accept that SQLite will insert
everything as text.

And what's the problem with that, anyway?  Sure, it may take a bit more
space on disk, but that's what XML was doing anyway.  Sure, it may give
"incorrect" typeof(), but does your database application really need
that?  At some point, something must know and require a particular
schema, otherwise it can't truly use the data, only pass it along, maybe
converting along the way.  That end user will *expect* an integer here
and a datetime there, and SQLite will do the conversions on demand.
Read up on duck typing sometime.

--
Andy Goth | 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 3:21pm, Dominique Devienne  wrote:

> On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin  wrote:
>> There are ways to enforce field length limits entirely within SQLite but 
>> they're complicated so post again if you want me to explain TRIGGERs.
> 
> No need to resort to triggers. A simple check constraint will do

A constraint can prevent you from putting too-long values in the field.  A 
trigger can truncate the value to the correct length, which is what declaring a 
column as VARCHAR() would do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

[Dennis Jenkins]

On Fri, Apr 18, 2014 at 6:32 AM, Christian Smith <
csm...@thewrongchristian.org.uk> wrote:

> On Tue, Apr 01, 2014 at 01:08:59PM +, Harmen de Jong - CoachR Group
> B.V. wrote:
> > We have built our own SQLite database server application and are
> considering making this open source. Since there will be some time involved
> into making it an open source project and maintaining it, we would first
> like to make an inventory to find out if there is any interest in this
> server application.
> >
> > ==> How it works:
> >
> > Clients can communicate with this server over TCP/IP sockets. Queries
> are submitted and returned in XML format (BLOB results are returned in
> binary format to prevent CPU intensive encoding and decoding). The server
> application is written in native Visual C++ (without using MFC). If we
> would make this project open source we would also include a client example.
>
>
> I dare say that parsing and encoding XML would be more processor intensive
> that BLOB encoding.
>
> IMO, you'd also increase your potential target audience if you could also
> provide reasonably functional JDBC, ADO.NET, PHP and/or Python database
> drivers. Providing any of these would allow existing users to plug your new
> database into existing applications with the minimal of fuss. Personally, I
> don't like the idea of XML as the protocol, largely because of the parsing
> overhead, and have been looking at a similar server based on RPC, but for
> debugging purposes it would be great.
>
> Regards,
> Christian
> ___
>


Not all XML libraries are inefficient.  TinyXml2 would probably work great
for this use-case.  (disclaimer: I use TinyXml2 in an unrelated project,
but I am not the author).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Dominique Devienne]

On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin  wrote:
> On 17 Apr 2014, at 11:24pm, David Clark  wrote:
>> If I have a table of
>> field1 varchar(25)
>> field2 varchar(50)
>> field3 varchar(75)
>
> You don't.  SQLite does not support a datatype of varchar().  Fields you 
> declare like that will be implemented as TEXT fields and handled the same as 
> any other TEXT field, and the length of the contents ignored.
>
>> I know sqlite does not enforce limits, but in my program it would be useful 
>> if I could find the declared lengths of
>> 25, 50 and 75 in this case.  How might I do that in sqlite?
>
> Technically you could retrieve and parse the CREATE command used to make the 
> TABLE and figure them out.  But since they aren't going to be enforced 
> there's little point.
>
> There are ways to enforce field length limits entirely within SQLite but 
> they're complicated so post again if you want me to explain TRIGGERs.

No need to resort to triggers. A simple check constraint will do:

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (a varchar(2), b varchar(2) check (length(b) < 3));
sqlite> insert into t (a) values ('a'), ('aa'), ('aaa');
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb'), ('bbb');
Error: CHECK constraint failed: t
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb');
sqlite> select * from t;
a|
aa|
aaa|
|b
|bb
sqlite>

You still have to explicitly hard-code the max-length to check against though.

Perhaps you could create a function that takes 3 params, table-name,
column-name (or index), and column value, and use
sqlite3_column_decltype as Igor mentioned, to generate a simple select
column-name from table-name and introspect the varchar(x) declared
type to infer the max length (caching the result with aux-data?), but
that seems like too much work for little gain to me. --DD

PS: I didn't check whether length() returns the byte-length or the
char-length (unicode codepoint length), which matters if you go beyond
ASCII and care about Oracle-like VARCHAR2(64 CHAR) vs VARCHAR2(64).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding SQLITE installation

[Zsbán Ambrus]

On Thu, Apr 17, 2014 at 9:04 PM, Nitin Nimran  wrote:
> I want to install sqlite for - Linux (X86_64) and NetBSD 6.1  (X86_64)
>
> What should be appropriate options for executing configure script to
> install for specified target.

Please see http://sqlite.org/howtocompile.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf function is shown in docs but not found when I try it

[Richard Hipp]

On Thu, Apr 17, 2014 at 8:03 AM,  wrote:

> In the documentation at http://www.sqlite.org/lang_corefunc.html
> there's a printf function, however when I try and use it in sqlite3
> (on xubuntu 13.10) it says "Error: no such function: printf".
>
> Is this because I need to load some extra library to make it work or
> is it just not available in the version of sqlite3 on Ubuntu, or what?
>
> All the other functions in 'core functions' seem to work.
>

The printf() function was added for SQLite version 3.8.3.  What version of
SQLite does xubuntu have installed?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] printf function is shown in docs but not found when I try it

[cl]

In the documentation at http://www.sqlite.org/lang_corefunc.html
there's a printf function, however when I try and use it in sqlite3
(on xubuntu 13.10) it says "Error: no such function: printf".

Is this because I need to load some extra library to make it work or
is it just not available in the version of sqlite3 on Ubuntu, or what? 

All the other functions in 'core functions' seem to work.

-- 
Chris Green
·

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regarding SQLITE installation

[Nitin Nimran]

I want to install sqlite for - Linux (X86_64) and NetBSD 6.1  (X86_64)

What should be appropriate options for executing configure script to
install for specified target.

I don’t see any option to specify x86_64-linux and x86_64-netbsd

Can anyone help me at earliest convenience…

Regards,
Nitin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

[Christian Smith]

On Tue, Apr 01, 2014 at 01:08:59PM +, Harmen de Jong - CoachR Group B.V. 
wrote:
> We have built our own SQLite database server application and are considering 
> making this open source. Since there will be some time involved into making 
> it an open source project and maintaining it, we would first like to make an 
> inventory to find out if there is any interest in this server application. 
> 
> 
> ==> Advantages and features of this server application:
> 
> * Takes semi-automatically care of concurrent writes (1*).
> * Very stable server application (eliminates long startup times in case of 
> client application crash with large DB's).
> * Database connections are cached.
> * Multiple parameter sets to prepare a query and execute it multiple times 
> with a different parameter set.
> * The result of a previous select query can be used in the next query.
> * Auto-checkpointing is disabled and manual checkpointing is done in a 
> separate thread, preventing transaction commits (read and write) to be 
> occasionally slowed down by a checkpoint event.
> * Built-in LZMA2 compressed and SHA256 encrypted backup and restore.
> * Built-in a-synchronous 'continuous data protection' (real-time backup) to 
> secondary location (4*).
> * Built-in slow query log and email notifications (2* 3*).
> * Built-in query error log and email notifications (3*).
> 
> 1.) A programmer can mark a transaction to be splittable and set splitting 
> points. If set, the server application will automatically split write queries 
> that take longer than the time set in milliseconds, thus allowing other write 
> queries to be executed in between.


Is this level of complexity worth it? If you really have such long transactions 
that you need this level of detail, perhaps more concurrent database server 
would suit better?


> 2.) The application can automatically log slow queries (that exceed the set 
> maximum query execution time) and send notifications by email. Maximum query 
> execution time can be set separately for read and write queries.

That's a good plan. I like this idea.


> 3.) All logging is done into separate databases, to prevent eating up costly 
> writing time from the main database.
> 4.) When a full backup has been made successfully, the real-time backup is 
> cleared. 

How much overhead does this add? Is this a high availability feature?


> 
> 
> ==> Cons:
> 
> * It has been built as a Windows service and therefore only runs on Microsoft 
> platforms

Boo. I'm sure we can "fix" that.


> * Transactions that cannot be split will block all writers.


For a typical SQLite use case, this is probably not a problem, else you'd be 
using a more scalable database.


> 
> 
> ==> How it works:
> 
> Clients can communicate with this server over TCP/IP sockets. Queries are 
> submitted and returned in XML format (BLOB results are returned in binary 
> format to prevent CPU intensive encoding and decoding). The server 
> application is written in native Visual C++ (without using MFC). If we would 
> make this project open source we would also include a client example.


I dare say that parsing and encoding XML would be more processor intensive that 
BLOB encoding.


> 
> A few examples of the XML the client sends to perform a query:
> 
> 
>   
>   
>   
>   
>name="@MinClickTime">
>   
>   
>name="@MinClickTime">
>   
> 


What form does a result set take? Is it all enclosed in an XML element, or do 
you get a cursor "object" returned and step through the result set 1 or more 
rows at a time. I ask, because the former might not be scalable for large 
result sets. Does it handle multiple result sets per query?


> 
> 
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
>   
> 
> 
> Because the result of the last select query is returned to the client, the 
> client can read the new ID from the result.
> 
> 
> Please let us know if you would be interested in this project!
> 

Would certainly be curious.

IMO, you'd also increase your potential target audience if you could also 
provide reasonably functional JDBC, ADO.NET, PHP and/or Python database 
drivers. Providing any of these would allow existing users to plug your new 
database into existing applications with the minimal of fuss. Personally, I 
don't like the idea of XML as the protocol, largely because of the parsing 
overhead, and have been looking at a similar server based on RPC, but for 
debugging purposes it would be great.

Regards,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about merge rows

[mm.w]

@hello,

it looks like an old sybase
-ms-flatten-like
export, if I understand well you got that just after splitting then
pivoting the properties, am I wrong?


On Thu, Apr 17, 2014 at 6:43 PM, Igor Tandetnik  wrote:

> On 4/17/2014 9:26 PM, YAN HONG YE wrote:
>
>> I want to merge all the mnote, how to do this?
>>
>
> What do you mean "merge"? What should the table look like afterward?
> --
> Igor Tandetnik
>
>
> ___
> 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