Re: [sqlite] Feature request: add support for COMMENT statement

[Alexey Pechnikov]

Hello!

> Roman Fleysher Mon, 17 Jun 2013 09:14:59 -0700

> ... However, I do not understand why new
> functionality of SQLite is needed. Why can't the mapper use a special
table of
> three columns (I will use example from Alexey):

We can replace the "drop column" functionality by easy SQL script but we
can't replace the COMMENT statements functionality without uncontrolled
grow of database schema complexity and the loss of independance of single
table.

Main questions:
Can you dump only single table from your database and load it into second
database?
Can you use Fossil SCM or other for easy versioning schemas of your tables
independently?

And optionally:
Can you easy transform your database dump by shell utilities (sed,
awk,etc)?
Can you search by grep dump of your database?

The really simple and useful ideology:

sqlite3 1.db '.dump'|sed ... | sqlite3 2.db

or

sqlite3 1.db '.dump' > dump.sql
fossil diff dump.sql

With SQLite we can do many things very simple. Why not?

-- 
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] Feature request: add support for COMMENT statement

[Alexey Pechnikov]

Hello!

> Suppose you have it. What would you do with it? > What's the use case?

> --> Igor Tandetnik


The COMMENTs can be used to store any information for interchanging
with application and external systems. As example, ORM (object
relational mappers) may need some additional metainfo for all mapped
columns.


My web-apps use metainfo like to:


user
{
  username TITLE
  loginKEY
  password PASSWORD
  rolesLIST:role
  note HIDDEN
  modified TIMESTAMP
  author   ID:user
  isactive ACTUAL
}


Where

user - table name

username, login,... - table fields

TITLE, KEY, PASSWORD - definition for rules to process tables fields
in web application and in console (import/export utils). The database
schema (including tables, indicies, FTS indicies tables, etc.) is
generated by this metainfo too. So I have the high-level domain
specific data definition language and low-level application file
format as SQLite database. Of course, it's usefull to have auto
generated schema with records versioning for all tables, fast search
for all key/title fields using FTS extension (my patches add snowball
tokenizers support), fast search for lists of identifiers (using FTS
extension too), import/export utilities, JSON routes, etc.

Unfortunately, now we need additional external [plain-text] file with
metainfo or additional table with non-trivial and non-standard mapping
between database objects and own metainfo table records.



> SQLite saves comments in table/view/index/trigger definitions:

>

> sqlite> create table t(x /* :-) */); > sqlite> .schema > CREATE TABLE t(x
/* :-) */);

>

> Regards, > Clemens


Bad idea. The schema definition can't be modified!

-- 
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] Feature request: add support for COMMENT statement

[Alexey Pechnikov]

Hello!

It's very important to have place to store table metainformation. In all
common DBMSs we can use TABLE/COLUMN COMMENT as meta description of table
but SQLite doesn't support it.

COMMENTs are supported from very old to new PostgreSQL:
http://www.postgresql.org/docs/7.1/static/sql-comment.html
http://www.postgresql.org/docs/9.2/static/sql-comment.html

MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

ORACLE:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4009.htm

IBM DB2:
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_addandretrievecommentsfromcatalog.htm

Sybase:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315695.htm


P.S.  Field type is bad storage for additional information because there
are a lot of conversion problems from some bindings (including official
tclsqlite) when column types are non-standard.

-- 
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 problem with 'NOT term' queries

[Alexey Pechnikov]

Hello!

>
>> And as result it's impossible to search docs in some situations:
>> SELECT * FROM docs WHERE docs MATCH 'NOT sqlite';
>> Error: malformed MATCH expression: [NOT sqlite]
>>
>
> As far as I can tell, in MATCH syntax NOT is a binary operator, denoting
> set difference. You are trying to use it as a unary operator.


Well, and how to rewrite query MATCH 'NOT sqlite'? In simple FTS qery
syntax is possible to use "-sqlite" but is the equal construction by
extended syntax?


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

[sqlite] FTS problem with 'NOT term' queries

[Alexey Pechnikov]

Hello!

From
http://www.sqlite.org/fts3.html#section_3_1
we can see the query

SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';

But the equal query doesn't works:

SELECT * FROM docs WHERE docs MATCH 'NOT sqlite AND database';
Error: malformed MATCH expression: [NOT sqlite AND database]

And as result it's impossible to search docs in some situations:
SELECT * FROM docs WHERE docs MATCH 'NOT sqlite';
Error: malformed MATCH expression: [NOT sqlite]



-- 
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] Any limitation of file size on sqlite?

[Alexey Pechnikov]

Hello!

Sqlite databases 10Gb+ sizes work fine for me in multi-user web environment
and 100Gb+ sizes were tested in my scenarios. The unsupported index
compression is the main problem and so use more scalable FTS4 index when
possible.

Best regards, Alexey.
http://pechnikov.tel
17.12.2012 0:03 пользователь "Çağlar Orhan"  написал:

> Hello again,
> Thank you Simon.
> The page http://www.sqlite.org/limits.html has my answer partly. In MS IIS
>  what gbytes bigger size of .sqlite file should significant to work on it
> properly?
> I mean, is there a 10-12 GB sqlite file that working properly and with any
> problem?
> Thanks
> Caglar
>
> --
> facta non verba
> ordo ab chao
> to code or not to code...
> Follow me (twitter.com/caglaror)
> Biz Contacts for LinkedIn Wellcome
> Script Works www.co-scripts.com
> ___
> 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] FTS4 extension: problem with field-specified search

[Alexey Pechnikov]

Doesn't work search by field name like to "test test":

sqlite> CREATE VIRTUAL TABLE test_fts USING fts4("test test");
sqlite> insert into test_fts values ('test');
sqlite> select * from test_fts where test_fts match '"test test":test';
sqlite> select * from test_fts where test_fts match 'test';
test


-- 
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] BUG:"insert or replace" doesn't works for External Content FTS4 Tables

[Alexey Pechnikov]

> What does the following:
>
>   SELECT * FROM view_address_exists WHERE rowid=64402;
>

sqlite>   SELECT "sys_title:hash" FROM view_address_exists WHERE
rowid=64402;
"sys_title:hash"
e9b4d0bcb5

About documented "When a row is deleted from an external content FTS4
table, FTS4 needs
to retrieve the column values of the row being deleted from the content
table.". I think that "insert or replace" is broken because we can't update
external table in middle of the "insert or replace" command execution
(after the old record complete deletion from FTS table and before  the new
record insertion into FTS table). May be is more reasonable to make the
content of FTS table synchronized with the content of external table by FTS
extension internally?

-- 
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] BUG:"insert or replace" doesn't works for External Content FTS4 Tables

[Alexey Pechnikov]

"insert or replace" doesn't trigger updating of the FTS index but only
'rebuild' do it:

sqlite> .s address_fts0
CREATE VIRTUAL TABLE "address_fts0" USING
fts4(content="view_address_exists", "sys_title:hash");

sqlite> select rowid,"sys_title:hash" from address_fts0 where
"sys_title:hash" match 'e7d4683bb2';
rowid|sys_title:hash
64402|e9b4d0bcb5

sqlite> insert or replace into "address_fts0" (rowid, "sys_title:hash")
select rowid,"sys_title:hash" from "view_address_exists" where sys_id=64402;

sqlite> select rowid,"sys_title:hash" from address_fts0 where
"sys_title:hash" match 'e7d4683bb2';
rowid|sys_title:hash
64402|e9b4d0bcb5

sqlite> INSERT INTO address_fts0(address_fts0) VALUES('rebuild');

sqlite> select count(*) from address_fts0 where "sys_title:hash" match
'e7d4683bb2';
count(*)
0

-- 
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] Anybody using REGEXP in the ICU extension?

[Alexey Pechnikov]

I use REGEXP operator from ICU extension but the patch will not break my
applications.

2012/11/5 Richard Hipp <d...@sqlite.org>

> Is anybody using the REGEXP operator that is included with the ICU
> extension of SQLite?
>
> The reason I ask is that the current ICU REGEXP implementation is broken.
> We are wanting to fix it.  But the fix runs a serious risk of breaking any
> legacy applications that depend on the old broken behavior.
>
> The brokenness is this:  The ICU REGEXP operator assumes that every pattern
> string begins with "^" and ends with "$".  In other words, the pattern must
> match the entire string.  This is how LIKE and GLOB work.  But we are
> thinking that most users expect a REGEXP pattern to match any substring of
> the input, unless the "^" prefix and/or "$" suffix are used.
>
> ICU experts: The fix we want to make is to use uregex_find() instead of
> uregex_matches().
>
> Will any legacy code break if we fix the ICU REGEXP operator to do what
> programmers normally expect?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Improving access speed to fetch indexed column.

[Alexey Pechnikov]

The patch doesn't attached, please apply link to the patch.

2012/7/12 Filip Navara <filip.nav...@gmail.com>

> This roughly resembles an issue I witnessed on our databases about
> year ago (thread "Improving the query optimizer" on this mailing
> list). SQLite doesn't use covering index for fulfilling queries unless
> the query is filtered/ordered by a column included in the index. In
> many cases the covering index is actually smaller than the full table
> and thus it's less data to read.
>
> We use a modified version of SQLite with the attached patch to
> alleviate the issue.
>
> Best regards,
> Filip Navara
>
> On Thu, Jul 12, 2012 at 12:59 PM, Richard Hipp <d...@sqlite.org> wrote:
> > On Wed, Jul 11, 2012 at 7:49 PM, Kohji Nakamura <k.nakam...@nao.ac.jp
> >wrote:
> >
> >> Hello all,
> >>
> >> I found that the access to an indexed column without "order by" is
> slower
> >> than the one with "order by" in SQLite 3071300.
> >> Using an index rather than an actual column is faster even if there is
> no
> >> need to use the index when the column has index.
> >> In general, to fetch column value, there is no need to access actual
> >> column when it has a dedicated index or it is a first column of
> composite
> >> index.
> >> I hope SQLite would do this optimization which is common to other DBMSs.
> >>
> >> Followings are the results of the comparison. Time column of main table
> >> has an index.
> >>
> >> After disk cache is cleared,
> >> SQL: select time from main order by time;
> >> Total : 38.1312 sec
> >>
> >> SQL: select time from main;
> >> Total : 95.395 sec
> >>
> >
> > Can you please send us the output of EXPLAIN QUERY PLAN for these two
> > queries on your schema?
> >
> >
> >>
> >> When data is cached,
> >> SQL: select time from main order by time;
> >> Total : 0.497981 sec
> >>
> >> SQL: select time from main;
> >> Total:: 0.925122 sec
> >>
> >> Thank you for developing a very cool DBMS, SQLite!
> >> Kohji Nakamura
> >> --
> >> k.nakam...@nao.ac.jphttp://www.nao.ac.jp/E/index.html
> >> National Astronomical Observatory of Japan
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > 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-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] SQLite4 (don't scream)

[Alexey Pechnikov]

Will be covering indices on views available as replacement of materialized
views?

2012/6/28 Simon Slavin <slav...@bigfraud.org>

> First, the important bit:
>
> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not
> going away."
>
> Now the URL:
>
> <http://www.sqlite.org/src4/doc/trunk/www/design.wiki>
>
> Just thought some people might enjoy reading and thinking about it.
>
> Simon.
> ___
> 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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

2012/4/2 Jay A. Kreibich <j...@kreibi.ch>

>  It would make more sense to just implement a strptime() SQL function
>  to compliment the existing strftime() function.  That would allow
>  SQLite to understand and convert any incoming date-time format
>  without depending on specific build parameters.
>
>
But may strptime() function returns datetime in format '%Y-%m-%d
%H:%M:%S'?.. It's produce some overhead by expensive (string parsing)
operations.

select strptime('%d.%m.%Y %H:%M:%S', '12.11.2001 18:31:01');
2001-11-12 18:31:01
select strftime('%s', strptime('%d.%m.%Y %H:%M:%S', '12.11.2001 18:31:01'));
1005589861


-- 
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] Bug in SQLite's TCL module

[Alexey Pechnikov]

May be used "onecolumn" function instead of "eval". The "eval" function
returns empty value of single record and so the result is "{}".

2012/4/12 Zbigniew <zbigniew2...@gmail.com>

> At the attempt to get a non-existing value, for example:
>
> set x [dbcomm eval {SELECT max(somecolumn) FROM sometable}]
>
> The returned value of $x will be {} - and no, not "empty", but exactly
> these two characters.
>
> Easy to reproduce.
> --
> Zbigniew
> ___
> 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] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

The sqlite3_stricmp() function is used only once in FTS3 extension and I
did fix the problem by replacing it to sqlite3_strnicmp()
http://sqlite.mobigroup.ru/fdiff?v1=111626ce72b0df93=29b395d2169466ab


2012/4/9 Alexey Pechnikov <pechni...@mobigroup.ru>

> As I find is missed sqlite3_stricmp reference in file loadext.c
>
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
>



-- 
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] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

As I find is missed sqlite3_stricmp reference in file loadext.c


-- 
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] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

Tcl-way is
package require sqlite3

The problem was fired in some old SQLite versions but was fixed before
previous release.

2012/4/8 Black, Michael (IS) <michael.bla...@ngc.com>

> Don't you need to load the sqlite3 library first for tclsh?
>
>
>
> load ./libtclsqlite3.so Sqlite3
>
> Or something like that?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
> Sent: Saturday, April 07, 2012 7:14 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] undefined symbol: sqlite3_stricmp
>
> Command-line shell works fine but tclsqlite package does not:
>
> $ tclsh8.5 test.tcl
> /usr/lib/libsqlitefts3.so: undefined symbol: sqlite3_stricmp
>while executing
> "db eval {SELECT load_extension('/usr/lib/libsqlitefts3.so')}"
>
>
> $ sqlite3
> SQLite version 3.7.12 2012-04-07 11:00:54
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT load_extension('/usr/lib/libsqlitefts3.so');
> load_extension('/usr/lib/libsqlitefts3.so')
> sqlite>
>
>
>
> 2012/4/7 Richard Hipp <d...@sqlite.org>
>
> > On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov <pechni...@mobigroup.ru
> > >wrote:
> >
> > > Compiled as extension FTS3 module produce error "undefined symbol:
> > > sqlite3_stricmp" by loading.
> > > The problem exists in last release and current trunk.
> > >
> >
> >
> > See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231
> >
> > Probably you are trying to link against a pre-3.7.11 version of SQLite,
> > perhaps the one that comes with your OS, instead of the latest version.
> >
> >
> >
> > >
> > > --
> > > 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
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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-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] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

Command-line shell works fine but tclsqlite package does not:

$ tclsh8.5 test.tcl
/usr/lib/libsqlitefts3.so: undefined symbol: sqlite3_stricmp
while executing
"db eval {SELECT load_extension('/usr/lib/libsqlitefts3.so')}"


$ sqlite3
SQLite version 3.7.12 2012-04-07 11:00:54
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT load_extension('/usr/lib/libsqlitefts3.so');
load_extension('/usr/lib/libsqlitefts3.so')
sqlite>



2012/4/7 Richard Hipp <d...@sqlite.org>

> On Sat, Apr 7, 2012 at 7:20 AM, Alexey Pechnikov <pechni...@mobigroup.ru
> >wrote:
>
> > Compiled as extension FTS3 module produce error "undefined symbol:
> > sqlite3_stricmp" by loading.
> > The problem exists in last release and current trunk.
> >
>
>
> See http://www.sqlite.org/src/artifact/906731099c43?ln=225-231
>
> Probably you are trying to link against a pre-3.7.11 version of SQLite,
> perhaps the one that comes with your OS, instead of the latest version.
>
>
>
> >
> > --
> > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] undefined symbol: sqlite3_stricmp

[Alexey Pechnikov]

Compiled as extension FTS3 module produce error "undefined symbol:
sqlite3_stricmp" by loading.
The problem exists in last release and current trunk.

-- 
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] About INSERT into "External Content FTS4 Tables"

[Alexey Pechnikov]

Why INSERT command can't copy all needed fields from external content table
same as "rebuild" command? The definition of all values does not has any
reasons and can produce inconsistent FTS table.

-- 
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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

And see too:

# LANG=C aptitude show libunistring0
Package: libunistring0
New: yes
State: not installed
Version: 0.9.3-3
Priority: optional
Section: libs
Maintainer: Andreas Rottmann <ro...@debian.org>
Uncompressed Size: 1221 k
Depends: libc6 (>= 2.3)
Description: Unicode string library for C
 The 'libunistring' library implements Unicode strings (in the UTF-8,
UTF-16, and UTF-32 encodings), together with functions for
 Unicode characters (character names, classifications, properties) and
functions for string processing (formatted output, width, word
 breaks, line breaks, normalization, case folding, regular expressions).

 This package contains the shared library.
Homepage: http://www.gnu.org/software/libunistring/


2012/4/2 Nico Williams <n...@cryptonector.com>

> On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp <d...@sqlite.org> wrote:
> > On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >> I think ... a higher priority than that would be handling Unicode
> >> correctly.  And having Unicode support would be useful in writing the
> code
> >> which handles dates.
> >>
> >>
> > size of SQLite library:  approx 500 KB
> > size of ICU library: approx 21,919 KB
> >
> > The ICU library (needed to handle Unicode "correctly") is over 40x larger
> > than SQLite.  Can you understand then why we don't want to make SQLite
> > dependent upon ICU?
>
> I completely agree.  It'd be nice if SQLite3 could have an option for
> a weak dependency on ICU.  I.e., if it can be found with dlopen(),
> then use it, else not; a pragma could be used by applications to check
> whether SQLite3 found ICU, or to require that it always be found for
> any given DB file.  Along these lines it'd be nice if a DB file could
> record required loadable extensions and provide a single pragma to
> load them all, with errors returned when compiling or running
> statements other than pragmas until the required extensions are
> loaded.
>
> > If you really need correct ICU support, SQLite will optionally link with
> > ICU and use it.  But *requiring* SQLite to link against ICU is a
> > deal-breaker for many users.
>
> FYI, the OpenSolaris Unicode library is significantly smaller, most
> likely because it deals only with normalization, case conversion, and
> codepoint prohibitions -- everything needed for "stringprep" and
> normalization- and case-insensitive string comparison.  The data
> tables reside in a header file and are 1.8 MB.  The code is 55KB, +
> 3KB of headers.  The license for this is CDDL (a per-file copyleft, as
> opposed to project-wide copyleft), which is not quite as free as ICU's
> license (which is essentially a two-clause BSD license), but probably
> good enough for most projects that use SQLite3.  FreeBSD, for example,
> includes u8_textprep, probably because it also includes ZFS (which
> depends on u8_textprep).  This is still many times larger than
> SQLite3, but still more than ten times smaller than ICU.
>
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/unicode/
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/sys/
> (scroll down to u8_textprep.h and u8_textprep_data.h)
>
> Nico
> --
> ___
> 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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why you need ICU library to parse datetime strings?!! The my previous patch
to parse Russian dates has only single row without any external libs.

2012/4/2 Richard Hipp <d...@sqlite.org>

> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> > I think ... a higher priority than that would be handling Unicode
> > correctly.  And having Unicode support would be useful in writing the
> code
> > which handles dates.
> >
> >
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?
>
> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

> 2012/4/2 Simon Slavin <slav...@bigfraud.org>

Please see http://www.sqlite.org/lang_datefunc.html and you will be
frustrated because SQLite can format date/time to different string formats
but can't read the produced date/time strings.

-- 
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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why we can't control this? As example, in Russia the date format is
DD.MM. and is needed the patch
http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a

I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
internationalization. These may be used for parsing and formatting dates.

-- 
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] Bug in init of ICU extension

[Alexey Pechnikov]

$ sqlite3
SQLite version 3.7.12 2012-03-30 07:13:09

sqlite> .load './libsqliteicu.so'
sqlite> SELECT icu_load_collation('ru_RU', 'russian');
sqlite> select 'ы' like 'Ы' collate russian;
1


$ sqlite3
SQLite version 3.7.12 2012-03-30 07:13:09

sqlite> SELECT load_extension('./libsqliteicu.so');
sqlite> SELECT icu_load_collation('ru_RU', 'russian');
sqlite> select 'ы' like 'Ы' collate russian;
0


The problem is old - I did find it first time at 2011.06.28.

-- 
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] Feature request: references from view to himself

[Alexey Pechnikov]

Is it possible to permit this behaviour?

CREATE TEMP TABLE user_record (id INTEGER PRIMARY KEY, user_id INTEGER,
name TEXT);
CREATE TEMP VIEW v_user AS SELECT id, name, (SELECT name FROM v_user WHERE
id=r.user_id) as "user" FROM user_record as r;
Error: no such table: v_user

-- 
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] Sqlite FTS retrieve inverted index

[Alexey Pechnikov]

See
http://www.sqlite.org/draft/fts3.html#fts4aux

2012/3/13 Mario Annau <mario.an...@gmail.com>:
> Hello,
>
> unfortunately I have already posted this question on
> stackoverflow<http://stackoverflow.com/questions/9657016/get-inverted-index-from-sqlite-fts-table>,
> hope that this mailing list is right address.
>
> After I have implemented a full text search function in my application
> using Sqlite and FTS tables I would be interested
> in a (performant) way of retrieving the FULL inverted index (or large part)
> out of my FTS (sub-)table.
>
> In effect - I would need a result table including the terms, docid's and
> number of occurences.
>
> I am actually searching for some basic code/examples to read the segdir /
> segments table (where the actual index is stored ) and construct my
> desired result table (in effect - the inverted index). But any solution
> which could retrieve the full (or large part of) my inverted index using
> queries including MATCH, MATCHINFO , etc. (sorry, I'm no Sqlite export)
> would be highly appreciated!
>
> Best,
> mario
> ___
> 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] 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 <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

Re: [sqlite] SQLite extensions repository

[Alexey Pechnikov]

Developers support a private repositories usually. As example:

http://sqlite.mobigroup.ru/wiki?name=extensions

http://sqlite.mobigroup.ru/wiki?name=utils

2012/2/12 Marco Bambini <ma...@sqlabs.net>:
> Hello,
> I am wondering if there is there a place that collect all available sqlite 
> extensions.
>
> Please let me know.
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
>
> ___
> 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] Elegant printout of table (with header) via TCL

[Alexey Pechnikov]

See pragma table_info

2012/2/9 rod <crimson.blu...@gmail.com>:
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (csv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> First solution I add the rowid tag after the *  then check to see if
> rowid==1 in the output and if so print the headers first
>
> 
>
> # the following code should print the headers and the
> # results from the SELECT command in CSV format
> sql eval {
> SELECT *, rowid \
> FROM Fxyz_max_min limit 10} row {
> # if at first row print headers first
> if $row(rowid)==1 {puts  [join $row(*) "\t"] }
> # define an EMPTY list
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
> The second solution makes use of a test of count
> (not really a counter just gets set to 1 instead of 0)
>
> 
> set count 0
> sql eval {
> SELECT * \
> FROM Fxyz_max_min limit 10} row {
> # define an EMPTY list
> if $count==0 {puts  [join $row(*) "\t"] }
> set count 1
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
>
>
> Each of these needs something added; either the rowid to the output or
> a counter test.
> is there a better way??
>
>
> Thanks
>
> P.S.   can the TCL sqlite3 statement open the database using command switchs
> (ie    --cvs  -headers)
> --
> -Rod
> ___
> 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] Slow inserts in indexed table

[Alexey Pechnikov]

Modification of big index-tree is disk-expensive operation.
You can try to insert pre-sorted data. Did yoy search only
by equals conditions? Did you think about packing a set
of values in single string or blob like to
Insert into t1 values ('1 2 3 4 5 6 7 8 9');
You can search by string content using FTS3/4 index.


2012/2/9 Valentin Davydov <sqlite-u...@soi.spb.ru>:
> Hi, All!
>
> I've got a database containing single table with a dozen of columns and some
> indices like that:
>
> CREATE TABLE T(X, Y, Z, ..., UNIQUE (X, Y, Z, ...) ON CONFLICT IGNORE);
> CREATE INDEX IX ON T(X);
> CREATE INDEX IY ON T(Y);
> CREATE INDEX IZ ON T(Z);
> .
>
> Data in the different columns are small positive integers, 32-bit integers
> or few-bytes blobs. Neither of the X, Y, Z, ... are unique by themselves,
> only their combination is unique (that's why I opt using relational database
> to process them). My application treats this table as append-only, that is,
> doing either INSERT or SELECT on it, without any UPDATEs. Few millions of
> inserts are wrapped in a single transaction in order to reduce journal usage.
> Total number of records in the table is more than 10^10, so it doesn't fit
> in any RAM. PRAGMA CACHE_SIZE is adjusted to use most of the available memory.
> Journal file (of enough size to hold all the pages dirtied by a biggest
> transaction) is created in advance and PRAGMA JOURNAL_MODE is set to PERSIST
> in order not to bother operating system with creating/deleting files. Page
> size is matched to the underlying filesystem block size and to the stripe
> size of RAID containing that filesystem. Sqlite version is now 3.7.3, but
> it seems that exact version doesn't matter.
>
> When trying to insert data already present in the table, performance is
> fairly well, most of the CPU time is spent only on parsing SQL statements
> and converting data to internal format, while database operation itself
> (that is checking data against the constraint) is almost instantaneous,
> which is quite impressive given the table size. But when the application
> inserts new data, things change drastically: total throughput drops by a
> 2-3 orders of magnitude. CPU is staying almost idle, and all time is spent
> waiting for disk _reading_ (rarely interspersed with fast and happy write
> bursts on each COMMIT). What is sqlite reading there? Does it try to
> perfectly balance each index on each insert (million times per
> transaction) or something else?
>
> Sincerely,
> Valentin Davydov.
> ___
> 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] About new ticket "Per page/region checksums"

[Alexey Pechnikov]

Roger, it's good to know the reasons of SQLITE_CORRUPT signal. And
very useful is possibility to mark all corrupted database pages as
unused in repair process and so make the database workable again.
And this repair may be processed only by SQLite internals because is
needed ignore corrupted pages and all related pages too. As example
ZFS can't resolve dependencies in SQLite files between tables and
indicies pages.

P.S. RAID solutions is not the "silver bullet" and we can get two or
more versions of the _differently_ corrupted database files on RAID
disks after crash of RAID. And there are a lot of embedded/mobile
devices without additional protection by RAID and without of regular
backups.

P.P.S. Is it possible to dump SQLite database with corrupted schema? I
think no. But we can copy some first database pages from any work copy
of the database like to
dd if=backup.db of=corrupted.db bs=4096 count=100
and repair after database by using per pages checksums.

-- 
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] About new ticket "Per page/region checksums"

[Alexey Pechnikov]

It's very important but there are some questions about
http://www.sqlite.org/src/info/72b01a982a
Some times ago DRH wrote that checksum calculation don't slow down
SQLite significantly.
But can be this realized in current SQLite 3.x branch? When checksum
can help to restore
damaged pages/database? Does powersave overwrite feature conflicts
with idea of per pages
checksums?

P.S. The article "Berkeley DB Recoverability" provides some info about
WAL+checksum mode:
http://help.bdbxml.net/html/ed539869-eebd-478f-97de-7e5377e87f66.htm

-- 
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] Speeding up Sqlite reads after DML

[Alexey Pechnikov]

You can try page size 8192 and cache size 128000.
Note: is required vacuuming after changing page size on existing database.

Inserting/updating big indexed tables may be slow. In last SQLite versions
a index creating performance is optimized and so indexing fully populated
table is a good idea when it's possible.

Don't use b-tree index for text fields. Use instead FTS4 extension or integer
hash value to index text fields. Note: index compression is not supported by
SQLite.

100+ Gb table and database is not too much for SQLite. I think you have
problems with big indexed tables but not with big tables. Big cache or
RAM drive or SSD disk may increase index updating speed. When a index
is larger than cache size (for parameters above cache size will be
128 000 *8 192 bytes) all index moditications is very disk expensive
operations.

P.S. Do not use cache size > 2Gb on 32-bit hosts.

2012/2/3 Udi Karni <uka...@gmail.com>:
> Marcus - thanks. I will experiment with those 2 PRAGMAs.
>
> Meanwhile - I was trying to update some columns in a table with 130 million
> rows and got this error -
>
> "Error: out of memory"
>
> I am not sure why. I thought the "UPDATE" just writes out new rows and
> maybe a journal of the old rows for recoverability - but I am not sure why
> it would need very much memory.
>
> Be that as it may - and with regard to your suggestion - and in light of
> this error message - given that I only have 4GB of RAM on my PC - is this
> really enough RAM to handle tables of this size ? Or am I giving Sqlite an
> unreasonably small amount of resources and it's time for a serious hardware
> upgrade?
>
> Thanks,
>
> Udi
>
> On Thu, Feb 2, 2012 at 10:03 PM, Marcus Grimm <mgr...@medcom-online.de>wrote:
>
>> > Given how clever and compelling Sqlite is - I am testing how it scales to
>> > tables in the 100GB / 200 million row range. This is for a strictly "read
>> > only" application - but first the tables must be populated in a one time
>> > process. As is often the case with Big Data - the data is a little dirty
>> -
>> > so the process involves importing - selecting - counting - inspecting -
>> > updating some rows - deleting some rows - selecting - counting -
>> > inspecting, etc. until clean.
>> >
>> > Placing the Sqlite database on a traditional C: drive - IO was too slow.
>> > At
>> > 15 MB/sec - reading a 50GB table would take an hour. So I moved it to
>> > external Raid array where I ran across an interesting find. IO wasn't
>> that
>> > much faster - until I vaccuumed the database - which increase IO 10X to
>> > 150
>> > MB/sec - with the same CPU utilization.
>> >
>> > This is good news for the final implementation of this read-only database
>> > -
>> > but still a dilemma at the data load phase. After a ".vaccuum" - issueing
>> > a
>> > single DML against a table - even a DELETE which deletes no rows at all -
>> > causes IO to drop back down to 15 MB/sec - on the table I'm selecting /
>> > DMLing - which makes the data loading / cleansing phase very long.
>> >
>> > So I have 2 questions -
>> >
>> > (1) Why would simple DML cause such an extreme slowdown as compared with
>> > "post vaccuum" speeds ?
>> >
>> > (2) Any knobs to turn to try and maintain the higher speeds post DML -
>> > without resorting to ".vaccuum" ?
>>
>>
>> You didn't tell if you already set the usual tricks to speed up
>> your load phase. That would be to increase the page cache and try
>> with reduced syncs.
>> See
>> PRAGMA cache_size
>> PRAGMA synchronous
>>
>> In particular the page cache should be increased dramatically
>> for huge DB files.
>>
>> Marcus
>>
>> >
>> > Thanks,
>> >
>> > Udi
>> > ___
>> > 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



-- 
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] support of %y in strftime()

[Alexey Pechnikov]

See "2-digit year patch" here:
http://sqlite.mobigroup.ru/wiki?name=patches

I did write this becouse 2-digit year number is needed very often for me.

2012/2/3 LacaK <la...@zoznam.sk>:
> Hi *,
> there is date-time formating function strftime(), which supports some (not
> all) string formating parameters (like %d, %m, %Y)
> There is %Y for 4-digit year.
> Is possible add also %y for 2-digit year ? (like in strftime() in standard C
> library)
> If it is not a big problem (I hope, that isn't), I would be very happy if it
> will be implemented.
> (can I post somewhere bug report, feature request)
> TIA
> -Laco.
>
> ___
> 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] [sqlite 3.6.22] database ".dump" function cannot export table of large number of columns < 2000

[Alexey Pechnikov]

2012/1/28 Simon Slavin <slav...@bigfraud.org>:
> If this database is for longterm use, you're doing the wrong thing.  Those 
> should be different rows, not all one huge long row.  If it's a one-off hack, 
> then do it and get rid of it before anyone else sees it and shames you.

Why not? Please send link to SQLite documentation instead of any astral reasons.

-- 
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] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

Of cource you may report your bug! But I'm not sure about possibility
of the bugfix in upstream and so I speak about patch to SQLite binding
for your language.

-- 
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] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

I think there are some problems with dynamic datatypes in sqlite.
This may be redesigned in SQLite4 but not in SQLite3.

P.S. Datatypes recognized differently after Shift-Insert SQLite3
commands into Tcl shell. This is similar to problem with datatypes
in triggers. I did report bug but for backward compability problem
wil not be resolved by upstream. So I did write patch for myself.

-- 
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] Bug in trigger: when comparing a value of an int column to a quoted value

[Alexey Pechnikov]

The problem can be fixed by variables bindings patch:
http://sqlite.mobigroup.ru/wiki?name=tclsqlite
I think, you can do same for you lang.

-- 
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] sqlite support for geometric column types?

[Alexey Pechnikov]

2011/11/26 Peter Aronson <pbaron...@att.net>:
> Not directly.  But Alessandro Furieri has created a version of SQLite with
> OGC conforming geometry columns, called SpatiaLite.  It can be found here:
>
> http://www.gaia-gis.it/spatialite/
>
> It's based on an older version of SQLite (3.6.16, but a beta version
> supports 3.7.3), and it uses a more restrictive license (not hard, since any
> license is more restrictive than SQLite's non-license!)

It can be used as extensions set for any new SQLite version.

-- 
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] Fts4 table + triggers

[Alexey Pechnikov]

Use view + triggers on view. Triggers on view can modify FTS4 table
and any other.

2011/11/22 Ephraim Stevens <ephraim.stev...@gmail.com>:
> Greetings all,
>
> It is well known that you cannot create triggers against virtual tables and
> fts4 tables are a form of virtual table.
>
> Has anyone developed a work around method for this or simulating the end
> effect?
>
> I have a full text search table which is comprised of joining and selecting
> from some underlying tables. I need to synch this full text search table
> with underlying tables upon insert or update (of the full text search
> table).
>
> A trigger would be ideal here but you can't use them against virtual
> tables. I'm sure someone has run into this same dillema. Thanks for any
> suggestions.
> ___
> 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] 200 lines of fun sqlite3 code

[Alexey Pechnikov]

iple(m.op->stmt,*(TRIPLE *)p);
> }
> if(m.status == SQLITE_OK) {
> do {
>
> if(m.op->stmt)
> m.status = sqlite3_step(m.op->stmt );
> m.status = ghandler(m.top);
> if(m.status == G_RESTART) return;
> }  while( m.status == SQLITE_ROW || (m.status == SQLITE_OK) );
> if(m.op->stmt)
> m.status = sqlite3_reset(m.op->stmt);
> }
> else gerror("bind \n");
> }
> void init_gbase() {
>   m.status = sqlite3_open(GBASE,);
>   debug("Gbase");
>    m.status = sqlite3_create_function_v2(m.db,GFUN,2,SQLITE_UTF8
> ,0,gfunction,0,0,
>  NULL);
>   m.status = sqlite3_prepare_v2(m.db,POP_SQL,  strlen(POP_SQL),
> [G_POP].stmt,0);
>     m.status = sqlite3_prepare_v2(m.db,TEST_SQL,  strlen(TEST_SQL),
> [G_TEST].stmt,0);
>
> }
>
> int _tmain(int argc, _TCHAR* argv[])
> {
> init_dll();
> init_gbase();
> m.top=POP_TRIPLE;
> for(;;) {
>  m.status = SQLITE_OK;
> m.called = POP_TRIPLE;
> if(m.status == G_RESTART)
> m.self_rowid=0;
> read_triples();}
> }
> ___
> 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] Virtual Tables and tcl / apsw

[Alexey Pechnikov]

2011/10/25 Dan Kennedy <danielk1...@gmail.com>:
> Not possible. The Tcl interface has no bindings for either the
> virtual table or VFS interfaces.

But why? Is there any technical/ideological problems?

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

[Alexey Pechnikov]

2011/10/19 Fabian <fabianpi...@gmail.com>:
> I always do inserts in batches of 100.000 rows, and after each batch I
> manually merge the b-trees using:
>
>  INSERT INTO table(table) VALUES('optimize');
>
> Is there a possibility that it will do automatic maintenance half-way during
> a batch? Or will it always wait untill the transaction is finished?

I think you are victim of the premature optimization :)

See documentation:
"several different b-trees that are incrementally merged as rows are inserted,
updated and deleted. This technique improves performance when writing to an
FTS table, but causes some overhead for full-text queries that use the index."

So you can work with a big FTS tables without using the "optimize" method.
I use some FTS tables with tens of millions records and effect of the
"optimize"
isn't measurable.

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

[Alexey Pechnikov]

2011/10/19 Fabian <fabianpi...@gmail.com>:
> Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that
> same multi-tree mechanism for regular indexes, but that's a whole different
> question.

It's impossible with SQLite3 database format. May be SQLite4 will be
support it :)

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

[Alexey Pechnikov]

FTS use index multi-tree and de-facto has _no_ insert speed degradation.
I did do test for 400+ millions of records.
With b-tree index there is insert speed degradation:
http://geomapx.blogspot.com/2010/04/sqlite-index-degradation-tests.html
http://geomapx.blogspot.com/search?q=index+speed

So FTS as hash-index is nice.

2011/10/19 Fabian <fabianpi...@gmail.com>:
> Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT
> INDEX column? I don't need many of the extra features of FTS, because I
> always need to look up rows by prefix or exact match, and both can be
> implemented efficiently via TEXT INDEX too. But if the overhead is
> comparable, I'd rather use FTS.
> ___
> 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] Problems building/running SQLite test.exe with ICU enabled

[Alexey Pechnikov]

Yes, on linux host I have a lot of problems in SQLite tests with ICU enabled.

2011/9/30 Marc Henrickson <marc.henrick...@wtsparadigm.com>:
> I have downloaded the SQLite source code (sqlite-netFx-source-1.0.74.0) as 
> well as the latest ICU libraries (icu4c-4_8-Win32-msvc10).  I then build the 
> SQLite projects and run the test.2008 application and it runs fine when 
> SQLITE_ENABLE_ICU is undefined.  However, when I do define SQLITE_ENABLE_ICU, 
> I get the following exception when I click "Run" on the test.exe application.
>
> System.Reflection.TargetInvocationException: Exception has been thrown by the 
> target of an invocation. ---> System.TypeInitializationException: The type 
> initializer for 'System.Data.SQLite.SQLiteFactory' threw an exception. ---> 
> System.DllNotFoundException: Unable to load DLL 'SQLite.Interop.DLL': The 
> specified module could not be found. (Exception from HRESULT: 0x8007007E)
>   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config(Int32 op, 
> SQLiteLogCallback func, IntPtr pvUser)
>   at System.Data.SQLite.SQLite3.SetLogCallback(SQLiteLogCallback func) in 
> c:\SQLiteTest\System.Data.SQLite\SQLite3.cs:line 935
>   at System.Data.SQLite.SQLiteFactory..ctor() in 
> c:\SQLiteTest\System.Data.SQLite\SQLiteFactory.cs:line 121
>   at System.Data.SQLite.SQLiteFactory..cctor() in 
> c:\SQLiteTest\System.Data.SQLite\SQLiteFactory.cs:line 131
>
> I am building and running the application with Visual Studio 2008 on a 
> Windows 7 64-bit machine.
> SQLite.Interop.2008 is set to the Win32 configuration.
> Both test.2008 and System.Data.SQLite.2008 projects are set to x86 
> configuration.
>
> I have updated the SQLite.Interop.2008 project to build the 
> SQLite.Interop.DLL and both the Interop.DLL and the System.Data.SQLite.DLL is 
> included in the test.exe folder.  I have also updated the SQLite.Interop.2008 
> project to include the proper ICU library files.  The project builds without 
> error and the test.exe will open up the main window.  I get the exception 
> when clicking "Run".  When I remove the SQLITE_ENABLE_ICU definition from the 
> SQLite.Interop.2008 project, it will build, and run as expected without error.
>
> Has anyone seen this before, and have a solution?  I am happy to provide more 
> details if needed.
> ___
> 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] Internal v. External BLOBs

[Alexey Pechnikov]

There is a problem with access to file in directory with big amount of files.
FS directory indicies are not really good. I did test 100 millions of 1k files
in SQLite and results were better than reading from set of directories in FS.
But for files about 1 Mb and more the SQLIte performance is not good.
Is any reason why SQLite big blobs reading may be slowly? The
performance of the BLOBS may limit performance of FTS and
other custom storage/index realizations (Spatialite, etc). And it's more
important I think. Especially when we need FTS index as fast hash index.

2011/9/21 Richard Hipp <d...@sqlite.org>:
> If you are storing large BLOBs in SQLite, can you read them faster if they
> are stored directly in the database file, or can you get to them quicker if
> you store just a filename in the database and read the BLOB content from a
> separate file?
>
> We did some experiments to try to answer this question, and the results
> seemed interesting enough to share with the community at large.  Bottom
> line:  On Linux workstations, it is faster to store BLOBs in the database if
> they are less than about 100KB in size, and faster to store them in a
> separate file if they are larger than about 100KB.  This is on Ubuntu with
> EXT4 and a fast SATA disk - your mileage may vary with different operating
> systems, filesystems, and hardware.
>
> The complete report is here:
> http://www.sqlite.org/intern-v-extern-blob.html
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Does coalesce terminate early?

[Alexey Pechnikov]

Richard, is the future documented anywhere?

2011/9/15 Richard Hipp <d...@sqlite.org>:
> On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
>
>> Richard Hipp <d...@sqlite.org> wrote:
>> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
>> scarle...@miltonstreet.com>wrote:
>> >
>> >> Forgive me, fore I have forgotten the term used to describe the behavior
>> if
>> >> a C if statement where it stops executing on the first false statement,
>> >> but...  Does coalesce do that?
>> >>
>> >
>> > "Short-circuit evaluation" is the usual term applied to this kind of
>> thing,
>> > and yes, COALESCE() does short-circuit evaluation.  If you say
>> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be
>> a
>> > significant performance win if, for example, B is a complex subquery.
>>
>> When did this start, with what SQLite version?
>
>
> 3.6.21 - December 2009
>
>
>> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at
>> the moment), but in the version I use, in expression coalesce(someField,
>> customFunction()) I definitely see customFunction() called even when
>> someField is not null.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/6 jerome moliere <jerome.moli...@gmail.com>:
> Could you give me more  details about corruption cases ?
> Is there a list of contexts where we can get corrupted tables ?

http://www.sqlite.org/howtocorrupt.html


-- 
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] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/6 jerome moliere <jerome.moli...@gmail.com>:
>  Did you set corresponding pragmas
>> page_size, cache_size, journal_mode?
> page_size yes
> cache_size no because we are using a higher level cache (in Java)
> journal_mode is in the list of the pragmas to be tested through the
> benchmark setting this parameter to OFF speeds up transactions for
> around 20%

I think your cache size in smaller than the modified indicies size. Use
more big cache or reduce indicies size.

> Indicies can be very
>> slow... may be the FTS4 table will be much faster for your queries.
>
> can you give more details about this concept ? what is the FTS4 table
> ? I am sorry don't know what is is !!!

See
http://www.sqlite.org/fts3.html
The full-text index is very fast and scalable. You can use it instead of
a lot of btree-indicies when  you can rewrite your search queries as FTS.

>> Did you try to use in-memory database as temp storage and
>> copy set of records into main database in single transaction?
>
> it's one of the goal of the benchmark but this solution has av ery
> high level of database corruption isn't it ..so I prefer to keep it as
> a joker...

Database corruption can't be the result of the coping from temp in-memory
database to main disk-database!

-- 
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] Fine tuning of Sqlite

[Alexey Pechnikov]

Jerome, can you show your database structure and queries?
Which FS is used? Did you set corresponding pragmas
page_size, cache_size, journal_mode? Indicies can be very
slow... may be the FTS4 table will be much faster for your queries.
Do you use a prepared queries?.. Which type of storage are you
use? Can you group a set of inserts into single transaction?
Did you try to use in-memory database as temp storage and
copy set of records into main database in single transaction?
There are a lot of questions without answers.

2011/9/6 jerome moliere <jerome.moli...@gmail.com>:
> Hi Alexey,
> I thought I answered to those questions but once again :
> * my application runs on an embedded device (Honeywell 9900) , it's a
> very complex application (too much I think)
> and we are facing performance problems while inserting datas (about
> 500 objects to be inserted inducing about 2000 queries and the same
> amount of transactions because the application runs with NON EXCLUSIVE
> flag, using serializable default isolation level). As any application
> running on an embedded device it 's not well suited for multi users!!!
> The application stores data for ONE employee of the company (our
> customers)...
> We have indexes (too much I think) but as far as I know in our case
> indexing is one performance problem rather than a solution (indexing
> in an INSERT phase adds overheads..)
>
> But now my initial problem is solved , it was a problem with Xerial
> JDBC classes , sqlite manages well group of PRAGMA (where Xerial
> SQLiteConfig objects don't)
>
> I'd like to use this post to thank you because I did not see such a
> good group for a while , with people reacting quickly and with a lot
> of value added comments...
> So thank you guys ,keep this very special Open Source mind alive for a while
>
>
> I just post an entry on my blogger.com page..
> For those interested :
> http://romjethoughts.blogspot.com/2011/09/sqlite-performance-tuning.html
>
>
> kind regards
> Jerome
> ___
> 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] Fine tuning of Sqlite

[Alexey Pechnikov]

2011/9/2 Simon Slavin <slav...@bigfraud.org>:
>
> On 2 Sep 2011, at 7:39am, jerome moliere wrote:
>
>> I must give some real clues to my customers to fine tune Sqlite
>
> 1) What problem(s) are you trying to solve ?
>
> 2) Must your system be set up for multi-process (or multi-user) access, or 
> can we ignore all problems concerning those ?
>
> 3) Are you using transactions to batch together data changes which relate to 
> one-another ?
>
> 4) If speed is a problem, do you have indexes defined appropriate to your 
> commands ?

Really, these are nice questions! Jerome, different environments may
have different pragmas...
we don't know about your environment enough. You may show some
problematic tests as example.



-- 
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] Read only scaling optimization

[Alexey Pechnikov]

2011/8/12 Pavel Ivanov <paiva...@gmail.com>:
> It's a little surprising to me that with all the same conditions 2
> files residing on the same drive have better performance than the same
> files residing on different drives. Theoretically that shouldn't
> happen.

Yes, it's not right behaviour. Is needed the sources of the test programm.

-- 
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] SQLite with 10M record

[Alexey Pechnikov]

2011/8/11 Sumit Gupta <gamersu...@gmail.com>:
> 10-10-2011 18:48:42, 10,20, 30, 40, 50, 60, 80 .. <48 such entries>

Use unixtime (time in seconds) and index on this field and your
queries will be fast.
You can use single blob as integers array for more compact database.

-- 
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] Read only scaling optimization

[Alexey Pechnikov]

2011/8/10 Wiktor Adamski <bardzotajneko...@interia.pl>:
> You
> may try increasing page size - bigger block means less near-random
> reads from the disc.

It's good way. With page size 8k instead of default 1k selects performance
may increasing ~3x. Note: PostgreSQL use 8k disk pages.

-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

2011/8/8 Simon Slavin <slav...@bigfraud.org>:
> You're quite right.  If someone was going to write that functionality into a 
> SQLite app in real life they'd probably use 'LIKE' or 'GLOB' and store all 
> the possibilities in one row.

FTS3/FTS4 is better as index for lists. See my test script for Igor.
We can fast search any id by using "match" operator on FTS table.

P.S. FTS table has nice scalability. Check insertion a lot of records
and insertion speed is constant. I did  try 400 millions of records
(and did get database size > 100 Gb).

-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

Igor, how about simple test? Normalized database is big and slow.

$ time ./test_norm_idx.tcl
real32m54.978s
user32m14.885s
sys 0m39.842s

$ time ./test_idx.tcl
real7m19.005s
user6m55.226s
sys 0m11.717s

$ ls -lh *db
2,1G test_idx.db
7,1G test_norm_idx.db


test_idx.tcl
=
#!/usr/bin/tclsh8.5
package require sqlite3

sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}

db eval {
create table parent(dt DATETIME, user_id INTEGER);
create index parent_dt_idx on parent(dt);
create index parent_user_id_idx on parent(user_id);
create virtual table parent_fts using fts4(childs TEXT);}
db transaction {
for {set i 1} {$i<=10} {incr i} {
set time [clock microseconds]
set childs ""
for {set j [expr {$i*1000}]} {$j<=[expr {$i*1000+1000}]} {incr j} {
lappend childs $j
}
db eval {insert into parent(dt, user_id) values ($time, 1)}
db eval {insert into parent_fts(childs) values ($childs)}
}
}


test_norm_idx.tcl
=
#!/usr/bin/tclsh8.5
package require sqlite3

# test normalized
sqlite3 db [string map {.tcl .db} $argv0]
db eval {PRAGMA page_size=8192}
db eval {create table link(dt DATETIME, user_id INTEGER, parent_id
INTEGER, child_id INTEGER);
create index link_child_id_idx on link(child_id);
create index link_dt_idx on link(dt);
create index link_user_id_idx on link(user_id);}
db transaction {
for {set i 1} {$i<=1} {incr i} {
set time [clock microseconds]
db eval {insert into link(dt, user_id, parent_id, child_id)
values ($time, 1, $i%1000, $i)}
}
}


P.S. With versioning of all records we need some additional fields and
normalized database
is very big and very slow.

-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

2011/8/7 Simon Slavin <slav...@bigfraud.org>:
> You don't need to.  The SQLite expressions I listed tell you how to achieve 
> the result without doing that.

Really? And how can you perform the query like to:

sqlite> create table t1(ids text);
sqlite> insert into t1 (ids) values ('1 2 3');
sqlite> insert into t1 (ids) values ('2 3 4');
sqlite> insert into t1 (ids) values ('3 4 5');
sqlite> create table t2(name text);
sqlite> insert into t2 (name) values ('name1');
sqlite> insert into t2 (name) values ('name2');
sqlite> insert into t2 (name) values ('name3');
sqlite> insert into t2 (name) values ('name4');
sqlite> insert into t2 (name) values ('name5');
sqlite> select * from t2 where rowid in (select ids from t1 where rowid=2);

A simple calculation: if each list of identifiers have about 1000
items and there are
1 000 000 lists than the table of relations (t1.rowid, t2.rowid) will
have 1 000 000 000
rows! It's too slow and is not useful in real world. Of cource all
systems store lists of
identifiers in similar situations.

-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

2011/8/7 Stephan Beal <sgb...@googlemail.com>:

> Also keep in mind that sqlite3 tries, to a large degree, to be compatible
> with ANSI SQL, and (IN "A B C"), in the form you describe, is not
> ANSI-specified.

Yes, but in PostgreSQL (as example) we can create user-defined
function returns table
from the list. SQLite can't do it and is impossible to use user extension too.

P.S. Example for PostgreSQL:

select list2items('a b c');

CREATE OR REPLACE FUNCTION public.list2items(in_list text)
  RETURNS SETOF text AS
$BODY$

SELECT lindex($1, s) FROM generate_series(0,llength($1)-1) AS s;

$BODY$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION public.lindex(list text, index int4)
  RETURNS text AS
$BODY$

  return [lindex $1 $2]

$BODY$
  LANGUAGE 'pltcl' IMMUTABLE;

CREATE OR REPLACE FUNCTION public.llength(list text)
  RETURNS int4 AS
$BODY$

  return [llength $1]

$BODY$
  LANGUAGE 'pltcl' IMMUTABLE;

-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

2011/8/7 Simon Slavin <slav...@bigfraud.org>:
> For example,
>
> SELECT * FROM cars WHERE cars.name IN ('Corolla', 'Fiesta')

create table t (names TEXT);
insert into t(name) values ('Corolla Fiesta');
SELECT * FROM cars WHERE cars.name IN (select names from t where rowid=1);


-- 
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] Feature request: extend the IN operator

[Alexey Pechnikov]

Is it possible to support construction like to

where x IN "1 2 3"

There are a lot of situations when is very useful to store list of
identifiers in table field.
I know about the intarray virtual table but it's need
constructor/destructor calls (and
produce segfaults with the autorizer function).

Does somebody interesting in this functionality too?

-- 
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] Example of implementing undo/redo using sqlite3?

[Alexey Pechnikov]

2011/8/4 Stephan Beal <sgb...@googlemail.com>:
> i'm wondering if anyone can point me to an example of implementing such a
> beast?

http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6
http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b

P.S. Many of the design ideas were cribbed from:
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

-- 
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] Need Help! -- SQlite database on server

[Alexey Pechnikov]

2011/8/4 Vinoth raj <vinoth@gmail.com>:
> So, the requirement is to save sqlite database on a server from a C++
> application.

There are a lot of ways to copy file to server. SQLite database is single file.
And you can send SQL dump. And you can send diff of SQL dump.
And you can export your data and send RDF or other data format.
I don't see your problem. May be you want any different?..

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/8/2 Eric Scouten <e...@scouten.com>:
> It falls apart badly in a highly distributed environment where ...
>
> ...

May be a RDF storage is more reasonable for this. Operations with
atomic facts can be highly distributed. And SPARQL is similar to SQL.

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/8/1 Simon Slavin <slav...@bigfraud.org>:
> I'm sorry Alexey, I was trying to be funny and failed.  Your question is very 
> important for this situation.

Oh, I'm sorry! My english is bad by night :)

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/8/1 Simon Slavin <slav...@bigfraud.org>:
>
> On 1 Aug 2011, at 6:56pm, Alexey Pechnikov wrote:
>
>> 2011/8/1 Black, Michael (IS) <michael.bla...@ngc.com>:
>>> This is a side-question to this thread...but has anybody every done 
>>> row-level locking for edit?
>>
>> What problem are you solving?
>
> Please stop asking key questions.

I don't understand the problem. IMHO Redis+SQLite is quick way to do
it. Use Redis key for
locking and incremented key as unique id generator.

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/8/1 Black, Michael (IS) <michael.bla...@ngc.com>:
> This is a side-question to this thread...but has anybody every done row-level 
> locking for edit?

What problem are you solving?

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

Roger, thanks for good explanation and examples of the multi-master
replication problems.
But I think a conflict-resolutions strategies may be independent. And
checking logical data
integrity is needed for systems without replication too. This checking
is not a part of the
replication but it's part of data processing. Of cource you can start
replication and checking
in single SQLite transaction. A normalized database can't be easy
replicated and checked.
The problem is not in SQLite. CouchDB has simple replication as result of simple
database schema. You can create simple schema of SQLIte database and
will have simple
replication. Some complex data structures can be serialized (I use Tcl
structures and you
can use JSON) and stored in simple SQLite database scheme. But there
is the problem
with indexing serialized data... I use FTS3 extension as index of Tcl
structures.
And I want to know how UnQL can resolve this problem.

> SQLite has no library level language other than very limited SQL.
It's trivial to add Tcl language to SQLite as example:
http://sqlite.mobigroup.ru/wiki?name=ext_tcl
And we can add javascript support for JSON data processing. Are you
really want it?

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/7/31 Simon Slavin <slav...@bigfraud.org>:
> Right.  So if a customer orders one of my products they'll be presented with 
> two different costs and pick the one they like.  Great system.

You customers have edit privilegies to you prices?! It's not the
replication problem of cource.

As example the name in your passport can be differ to others
documents. But you passport is valid.
The price in the shop now and tomorrow can be different. And prices in
dirrefent shops are not
equal. Do you going to shops or you frustrated by these conflicts?..
Your applications may be ready
to work with real data. There are a lot of causes of _potential_
conflicts and replication is only one
of these. Replication is aggregation process but is not "silver
bullet" for extracting correct data.

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/7/31 Roger Binns <rog...@rogerbinns.com>:
> My original point was that CouchDB has multi-master replication builtin -
> you do not have to write extra code or do anything special.

and

> You can
> programmatically find conflicts and then it is up to you how to resolve them
> since only your code knows the semantics of the data.

You write extra code to find and resolve CouchDB replication conflicts but you
are speak about "do not have to write extra code". One of these
approvals is false!

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

2011/7/31 Roger Binns <rog...@rogerbinns.com>:
> It takes more than a few lines of SQL.  You've also got to be careful how
> you write data (result is a DAG), you need replication code and you need
> conflict resolution code.

There are no conflicts! Two versions are two _different_ versions of the row.
Simple copy _all_ un-exists rows by SQL query with attached database
or simple script
http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff
It can be included into custom sqlite3 shell :)
On application level we may show the last version of each record (and
full history of record).
And user can view and revert any changes.

I think easy replication is not feature of NoSQL solutions. With big
disks we can create and
use append-only datasets in SQL DBMS. And virtual table can
incapsulate all details.

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

Multi-master replication can be easy with correct data structures.

As example:
CREATE TABLE test
(
  id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
  cid INTEGER UNIQUE -- equal to test_record.id
);
CREATE TABLE test_record
(
  id INTEGER NOT NULL,  -- record version id
  rid INTEGER PRIMARY KEY,  -- equal to test.id - permanent record id
  rev INTEGER,   -- record revision number
...
  FOREIGN KEY(id) REFERENCES test
);
CREATE VIEW view_test AS
  SELECT r.* FROM test as t, test_record as r WHERE t.cid=r.rid;

For multimaster replication is needed copy all un-exists "test_record"
rows from host A to host B and from B to A and then regenerate some
rows in "test" tables.

And Fossil is well-known example of SQLite database replication :D

-- 
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] Will SQLite supports UnQL?

[Alexey Pechnikov]

Very interesting annonce:
http://www.couchbase.com/press-releases/unql-query-language

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

2011/7/30 Gabor Cselle <ga...@google.com>:
> Just a heads-up that we just posted an update to the benchmarks:
> - No more superfluous index on the primary key
> - WAL turned on with auto-checkpointing every 4096 pages

You may use "PRAGMA synchronous = NORMAL" instead of "PRAGMA
synchronous = FULL" in WAL mode.

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>:
> 2X-20X is hardly "small...overhead" in my world.
>
> Even 2X is the difference between 30 days and 15 days.  One 16-computer blade 
> rack vs two racks ($200,000 vs $400,000).
>
> That's why google did this.  Works for what they need and is lots cheaper.

And single-thread without correct synchronous. Plus full in-memory
copy of LevelDB log.
And tests is adopted for specific scenarious (IMHO test of fixed value
size is incorrect).
I'm sure key-value database may be faster.

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>:
> What they don't say explicitly is that if all you need is key/value 
> capability then an SQL database is overkill and only slows you down (bit of a 
> duh factor there though not obvious to neophytes).

The overhead by SQL layer is small. And are used prepared statements in test.
As I see LevelDB use data integrity equal to SQLite WAL mode with
disabled fsync. And LevelDB is limited only single-thread access. With
the patched test SQLite is about
2x - 20x slower and it's absolutly normal I think. With dirrefent
page_size we can make
some tests faster.

P.S. There is constant database created by DJB. And exists patch to
drop "constant"
limitation. IMHO it's functionally equal and better solution than LevelDB...

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

With integer->blob mapping patch I get these results:


$ ./db_bench_sqlite3
SQLite: version 3.7.7.1
Date:   Fri Jul 29 05:32:05 2011
CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
CPUCache:   512 KB
Keys:   16 bytes each
Values: 100 bytes each
Entries:100
RawSize:110.6 MB (estimated)

fillseq  :  77.394 micros/op;1.3 MB/s
fillseqsync  : 133.326 micros/op;0.7 MB/s (1 ops)
fillseqbatch :  31.511 micros/op;3.1 MB/s
fillrandom   : 518.605 micros/op;0.2 MB/s
fillrandsync : 227.374 micros/op;0.4 MB/s (1 ops)
fillrandbatch : 411.859 micros/op;0.2 MB/s
overwrite: 793.869 micros/op;0.1 MB/s
overwritebatch : 743.661 micros/op;0.1 MB/s
readrandom   :  31.236 micros/op;
readseq  :  20.331 micros/op;
fillrand100K :4872.027 micros/op;   19.6 MB/s (1000 ops)
fillseq100K  :7249.182 micros/op;   13.2 MB/s (1000 ops)
readseq100K  : 634.887 micros/op;
readrand100K : 606.026 micros/op;


$ ./db_bench
LevelDB:version 1.2
Date:   Fri Jul 29 11:20:59 2011
CPU:2 * Intel(R) Atom(TM) CPU N450   @ 1.66GHz
CPUCache:   512 KB
Keys:   16 bytes each
Values: 100 bytes each (50 bytes after compression)
Entries:100
RawSize:110.6 MB (estimated)
FileSize:   62.9 MB (estimated)
WARNING: Snappy compression is not enabled

fillseq  :  10.107 micros/op;   10.9 MB/s
fillsync : 276.920 micros/op;0.4 MB/s (1000 ops)
fillrandom   :  21.275 micros/op;5.2 MB/s
overwrite:  30.717 micros/op;3.6 MB/s
readrandom   :  48.781 micros/op;
readrandom   :  39.841 micros/op;
readseq  :   2.227 micros/op;   49.7 MB/s
readreverse  :   3.549 micros/op;   31.2 MB/s
compact  : 5274551.868 micros/op;
readrandom   :  35.392 micros/op;
readseq  :   1.743 micros/op;   63.5 MB/s
readreverse  :   2.927 micros/op;   37.8 MB/s
fill100K :6631.138 micros/op;   14.4 MB/s (1000 ops)
crc32c   :  11.447 micros/op;  341.2 MB/s (4K per op)
snappycomp   :   8.106 micros/op; (snappy failure)
snappyuncomp :  26.941 micros/op; (snappy failure)
acquireload  :   1.407 micros/op; (each op is 1000 loads)



-- 
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] LevelDB benchmark

[Alexey Pechnikov]

There are the LevelDB sources and tests
svn checkout http://leveldb.googlecode.com/svn/trunk/ leveldb-read-only

Build SQLite test as
make db_bench_sqlite3
And LevelDB test as
make db_bench

My patch for leveldb-read-only/doc/bench/db_bench_sqlite3.cc to disable
redudant index and enable WAL is here:
http://pastebin.com/dM2iqdvj

And patch as above plus integer keys instead of blobs
http://pastebin.com/CnBeChWg

P.S. For blob-to-blob mapping we may use table with index on hashed key.
Virtual table can simplify this.

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

Hm, I test I find index on PK field:

CREATE TABLE test (key blob, value blob, PRIMARY KEY(key))
CREATE INDEX keyindex ON test (key)

Epic fail, I think :D


Default test on Intel(R) Atom(TM) CPU N450   @ 1.66GHz
fillseq  : 442.937 micros/op;0.2 MB/s
fillseqsync  :1678.168 micros/op;0.1 MB/s (1 ops)
fillseqbatch :  73.016 micros/op;1.5 MB/s
...

And with enabled WAL and synchronous=NORMAL and wal_autocheckpoint=4096
(LevelDB log size is 4Mb by default) and without index on PK field (!):
fillseq  : 139.190 micros/op;0.8 MB/s
fillseqsync  : 228.869 micros/op;0.5 MB/s (1 ops)
fillseqbatch :  56.131 micros/op;2.0 MB/s
...

-- 
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] LevelDB benchmark

[Alexey Pechnikov]

LevelDB use append log but SQLite is tested without WAL :)
I check and some tests 2.5x faster with WAL.


-- 
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] New madIS v1.3 release

[Alexey Pechnikov]

Why JPack? May be Tcl lists will be more useful? The tcl dictionary
(also known as associative array) can be stored as list too.

SELECT TCLCMD('dict', 'get', 'key 1 mykey 2', 'mykey');
2

SELECT TCLCMD('lindex', 'key 1 mykey 2', 0);
key

SELECT TCLCMD('join', 'key 1 mykey 2', '-');
key-1-mykey-2

SELECT TCLCMD('lreplace', 'key 1 mykey 2', -1, -1, 'test');
test key 1 mykey 2

SELECT TCLCMD('lreplace', 'key 1 mykey 2', 'end', 'end', 'test');
key 1 mykey test

SELECT TCLCMD('linsert', 'a b', 0, 'c');
c a b

SELECT TCLCMD('linsert', 'a b', 'end', 'c');
a b c

SELECT TCLCMD('lsort', 'a c b');
a b c

SELECT TCLCMD('lsort', '-decreasing', 'a c b');
c b a

SELECT TCLCMD('lreverse', 'a c b');
b c a

SELECT TCLCMD('lsearch', 'a c b', 'b');
2

SELECT TCLCMD('lsearch', 'a c b', 'd');
-1

See details here: http://sqlite.mobigroup.ru/wiki?name=ext_tcl

-- 
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] [FTS3] The Compress and Uncompress functions and extension

[Alexey Pechnikov]

2011/7/22 Abhinav Upadhyay <er.abhinav.upadh...@gmail.com>:
> Thanks for pointing out that mail archive discussion. I wasn't using
> compress/uncompress because uncompress requires you to store the size
> of the compressed buffer which is returned by the compress function
> while compressing. But that email discussion suggests a nifty trick to
> overcome this.

And you can get the content size without decompression. It can be useful.

-- 
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] [FTS3] The Compress and Uncompress functions and extension

[Alexey Pechnikov]

But why you don't use compress/uncompress functions from DRH? See
http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html

I did wrap these into extension and add SQLITE_COMPRESS_MIN_LENGTH
http://sqlite.mobigroup.ru/artifact/a5da96353bb851b34114052ba85041fdffb725cd
http://sqlite.mobigroup.ru/artifact/56df1be3c402d7d49c3a13be704a2ff22c3003d2

http://sqlite.mobigroup.ru/dir?name=ext/compress

-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

2011/7/15 Dan Kennedy <danielk1...@gmail.com>:
>> But queries to original text will not work:
>> select text from fts where fts match 'sqlite educate';
>
> I think it will. Query strings - like 'sqlite educate' - are
> also parsed using the tokenizer. So the query will be transformed
> to 'dbms educate' before it is run.

I wanted to say - we can't rank results differently. The query 'sqlite educate'
may return result 'dbms education' with low rank and 'sqlite education'
with high rank.

-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

2011/7/15 Dan Kennedy <danielk1...@gmail.com>:
> I think you could just have the tokenizer return "dbms" whenever
> it sees "sqlite" in the input.

But queries to original text will not work:
select text from fts where fts match 'sqlite educate';

You can see synonyms dictionary in PostgreSQL full-text search
as example. It's very common task for many applications.

-- 
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] my new site is using as backend sqlite

[Alexey Pechnikov]

2011/7/14 Sebastian Bermudez <sebastian.bermu...@yahoo.com>:
> please, don't smile !! this is very very micro site. for my personal url
> shorter function

May be you want to see the test site of the business product on Tcl + SQLite? :)
https://merch.mobigroup.ru/
The site language is russian. You can try stemmed SQLite FTS4 search and
enjoy by fast work.

Note: you can guess the admin user password :D

-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

I want to add the table
CREATE TABLE ext_fts_synonyms(word text not null unique, synonym text not null);
insert into ext_fts_synonyms('sqlite','sqlite dbms');

And replace in tokenizer the term 'sqlite' to 2 terms 'sqlite' and 'dbms'
for search by queries like to
> select text from fts where fts match 'dbms educate';
'SQLite may be useful for education'

But how to return from tokenizer 2 terms or more instead of single term?..

Note: Snowball stemmer and stopwords table I did add and can do:
> select text from fts where fts match 'sqlite educate'
SQLite may be useful for education


-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

> No. Don't return anything for a stop word. Just advance to the next
> non stop-word token and return it.

Thanks, I did and it's work.

And another question... Is there any way to use multi-word synonyms? Like to:
sqlite -> Open Source SQLite DBMS

I think the single token "Open Source SQLite DBMS" will not useful.

-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

With 0-length token in icuNext there is the error:
Error: SQL logic error or missing database

May xNext returns 0 length when the token is stopword?

-- 
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] FTS3: synonyms dictionary and tokens length

[Alexey Pechnikov]

With synonyms dictionary the result token length can be more then
original token length.
Is it problem for current realization of FTS?

-- 
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] Database handle access from tokenizer module (stopwords table support)

[Alexey Pechnikov]

I want to add stopwords table support for ICU tokenizer but there is
no database handle access (icuOpen).
Any ideas?


-- 
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] Storing/editing hierarchical data sets

[Alexey Pechnikov]

See FTS3 extension where the full-text index is stored in multi btree
in regular tables. Note: FTS2 is more simple.

-- 
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] Error: malformed database schema

[Alexey Pechnikov]

$ sqlite3 test.db
-- Loading resources from /home/mbg/.sqliterc
SQLite version 3.7.7.1 2011-06-28 16:48:37
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from sqlite_master;
283
.dump
...
sqlite> select count(*) from sqlite_master;
Error: malformed database schema (job_user_id_idx) - no such table:
main.job_record
.dump
...
sqlite> pragma integrity_check;
*** in database main ***
Page 5 is never used
Page 35 is never used
Page 67 is never used
Page 97 is never used
....

-- 
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] Best solution to import big DBF file?

[Alexey Pechnikov]

1. sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf

2. Or see the VirtualText extension from Spatialite project.
This extension can be builded with SQLite
http://sqlite.mobigroup.ru/dir?name=ext/virtualtext
http://sqlite.mobigroup.ru/dir?name=ext/iconv
I did support SQLite build with this some times ago (deb packages),
but the first solution is more useful for me now.

2011/6/27 Gilles Ganault <gilles.gana...@free.fr>:
> Hello
>
> I have a 87MB file in DBF format that I'd like to import into SQLite.
>
> What is the best tool for this, free or affordable?
>
> Thank you.
>
> ___
> 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] Documentation typos and inconsistent logic

[Alexey Pechnikov]

1. May be added note "All triggers associated with the view are also
deleted." to
http://www.sqlite.org/lang_dropview.html

2. This behaviour is incompatible with other DBMS and undocumented:

sqlite> create table a(a);
sqlite> create view b as select * from a;
sqlite> drop table a;
sqlite> select * from b;
Error: no such table: main.a
sqlite> .s
CREATE VIEW b as select * from a;

sqlite> create view a as select 1;
sqlite> create view b as select * from a;
sqlite> drop view a;
sqlite> select * from b;
Error: no such table: main.a
sqlite> .s
CREATE VIEW b as select * from a;

This strange, really. View cannot reference objects in attached
databases but view can reference deleted object?

-- 
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] How to parse VIEW fields definitions?

[Alexey Pechnikov]

The new PRAGMA or Virtual Table will be more useful. But I don't sure
about possibility of this.

2011/6/14 Nico Williams <n...@cryptonector.com>:
> On Tue, Jun 14, 2011 at 1:58 PM, Alexey Pechnikov
> <pechni...@mobigroup.ru> wrote:
>> As example, we have view:
>>
>> create view vtest as select name1 || ' ' || name2 as name from test;
>>
>> How to get the definition of "name" field (will be "name1 || ' ' ||
>> name2")? Of cource, the view can be more complex.
>
> Take Lemon and the parse.y file from SQLite3, and build a parser that
> does what you want?
>
> Nico
> --
> ___
> 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] How to parse VIEW fields definitions?

[Alexey Pechnikov]

As example, we have view:

create view vtest as select name1 || ' ' || name2 as name from test;

How to get the definition of "name" field (will be "name1 || ' ' ||
name2")? Of cource, the view can be more complex.

-- 
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] Patch for fts3_tokenizer.c to compile FTS3 extension as a loadable module

[Alexey Pechnikov]

In fts3_tokenizer.c these lines are invalid:

#include "sqlite3ext.h"
#ifndef SQLITE_CORE
  SQLITE_EXTENSION_INIT1
#endif

Use single line instead:

#include "sqlite3.h"


-- 
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] can we configure high availibility with SQLITE?

[Alexey Pechnikov]

No, not like to other.  Sqlite engine does not has internal high
availability functions.
Use application-level sync or async replication/logging if needed.

2011/6/11 irfan khan <irfan.8...@gmail.com>:
> Hi,
>
>  Can we configure high availibility with SQLITE databases like we can do
> with other rdbms.
>
> --
>
>
>
> Thanks & Regards
>
> Irfan Khan
> ___
> 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