Re: [sqlite] sqlite3 db open/close

[Durga D]

Thank you.


On Sun, Nov 18, 2012 at 8:18 PM, Yuriy Kaminskiy  wrote:

> Durga D wrote:
> >What happens if sqlite3_close() called multiple times but
> > sqlite3_open_v2() called only once.
> >
> > Practically I dint see any malfunction/corruption here. I would like
> to
> > know the behavior of sqlite in this scenario.
>
> About same as
>
>   char *foo = malloc(10);
>   free(foo);
>   free(foo);
>
> or
>
>FILE *f = fopen("file", "r");
>fclose(f);
>fclose(f);
>
> Sometimes it does nothing, but display error message (e.g. with some "debug
> malloc" modes/libraries). Sometimes second sqlite3_close/free/fclose kills
> your
> program on assertion or sigsegv. Sometimes it corrupts heap internal
> structures,
> and your program may die *at random time* after that.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Vladislav Bolkhovitin]

Vladislav Bolkhovitin, on 11/17/2012 12:02 AM wrote:

The easiest way to implement this fsync would involve three things:
1. Schedule writes for all dirty pages in the fs cache that belong to
the affected file, wait for the device to report success, issue a cache
flush to the device (or request ordering commands, if available) to make
it tell the truth, and wait for the device to report success. AFAIK this
already happens, but without taking advantage of any request ordering
commands.
2. The requesting thread returns as soon as the kernel has identified
all data that will be written back. This is new, but pretty similar to
what AIO already does.
3. No write is allowed to enqueue any requests at the device that
involve the same file, until all outstanding fsync complete [3]. This is
new.


This sounds interesting as a way to expose some useful semantics to userspace.

I assume we'd need to come up with a new syscall or something since it doesn't
match the behaviour of posix fsync().


This is how I would export cache sync and requests ordering abstractions to the
user space:

For async IO (io_submit() and friends) I would extend struct iocb by flags, 
which
would allow to set the required capabilities, i.e. if this request is FUA, or 
full
cache sync, immediate [1] or not, ORDERED or not, or all at the same time, per
each iocb.

For the regular read()/write() I would add to "flags" parameter of
sync_file_range() one more flag: if this sync is immediate or not.

To enforce ordering rules I would add one more command to fcntl(). It would make
the latest submitted write in this fd ORDERED.


Correction. To avoid possible races better that the new fcntl() command would 
specify that N subsequent read()/write()/sync() calls as ORDERED.


For instance, in the simplest case of N=1, one next after fcntl() write() would be 
handled as ORDERED.


(Unfortunately, it doesn't look like this old read()/write() interface has space 
for a more elegant solution)


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

Re: [sqlite] Which classes of nested requests are not expressible as joins?

[emilbarton]

Thanks for your answer. I'd like to know to what extent I can convert a Sqlite 
schema to a Mysql one, but I find the question interesting for itself. What if 
we forget about modifying commands and care only about retrieval?


Clemens Ladisch wrote:



I wonder why you want to know this?


Well, anything where you cannot use joins in the first place, such as
UPDATE/DELETE commands:

  DELETE FROM question
  WHERE type_id IN (SELECT id
                    FROM question_type
                    WHERE name LIKE '%homework%')
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Tim Streater]

On 19 Nov 2012 at 01:57, ZikO  wrote: 

> The script looks like this:
> CREATE TABLE IF NOT EXISTS imiona (
>   id INTEGER PRIMARY KEY,
>   data TEXT,
>   imie1 TEXT,
>   imie2 TEXT,
>   imie3 TEXT);
>
> INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
> ('01/01/2012','Masława','Mieczysława','Mieszka'),
> ('16/01/2012','Marcelego','Walerii','Włodzimierza'),
> ('17/09/2012','Franciszka','Lamberty','Narcyza');

This works exactly as it should on my Mac, both inputting and displaying, so 
it's not sqlite.

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

[sqlite] malformed database schema

[sqdev sqdev]

Hello,

I'm using ADO.NET-Provider for SQLite (System.Data.SQLite, Version 1.0.80).
My database is password-protected. The password is passed in the connection
string.

Sporadically, I get the following exception:

Exception: The database disk image is malformed
malformed database schema (VALUE1) - near "VALUE2": syntax error

VALUE1 seems to be a value my application has recently added (or tried to
add) by means of an INSERT INTO statement issued by a SQLiteCommand.
VALUE2 might also be such a value, but I'm not sure.
VALUE1 and VALUE2 differ each time the problem occurrs.

Once this exception has occurred, it's impossible to open the database file.
Opening the database in Visual Studio Server Explorer produces the
following output:
---
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (1): near "08": syntax error
SQLite error (11): database corruption at line 93674 of [036395c0a8]
SQLite error (11): malformed database schema (001) - near "08": syntax error
---

I suspect the SQLITE_MASTER table has been overwritten by the data which
should have been inserted into another table by the INSERT INTO statement
mentioned above.
I cannot verify that though, as I'm not able to open the database file any
more.
For the same reason, I cannot issue "PRAGMA integrity_check".

The application issues DDL and DML statements.
Sometimes, I also get the following error, but I don't know if it has to do
anything with the database corruption problem.
sqlite error (17): statement aborts at 21: [SELECT * [FROM MY_TABLE]]
database schema has changed
No exception is thrown for this error though. I get it in the
SQLiteConnection.Trace event only.

Now my questions are:
- What could have caused the database schema and database file to become
corrupt?
- Is there any chance to open the corrupt database file again? Most tools
(including SQLite3-CLI) fail to open even non-corrupted files, because they
do not support the encryption algorithm used by System.Data.SQLite, which
is RSA I think.
- Is it possible to decrypt a corrupted database file if it cannot be
opened by System.Data.SQLite any more?

Thank you in advance for any help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Simon Slavin]

On 19 Nov 2012, at 1:57am, ZikO  wrote:

> The script was written in Notepad++
> that controls which charset coding is used but I also double checked it in
> Notepad and it indicated UTF-8; it can also be confirmed by reading the
> script text file in Hex editor that shows BOM at the beginning:
> [*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

If you try the command

INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka');

(type it, do not use copy-and-paste) directly into the shell tool and then

SELECT * FROM imiona;

does it show the characters correctly or is it converting those too ?

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

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Clemens Ladisch]

ZikO wrote:
> INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
> ('01/01/2012','Masława','Mieczysława','Mieszka'),
>
> As you can see, it contains accents. The script was written in Notepad++
> that controls which charset coding is used but I also double checked it in
> Notepad and it indicated UTF-8; it can also be confirmed by reading the
> script text file in Hex editor that shows BOM at the beginning:
> [*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

The BOM ("byte-order mark") was intended to differentiate between the
little- and big-endian forms of UTF-16.  Using it for UTF-8 is somewhat
common but is not recommended because it will break many programs.

> sqlite3 imieniny.db < test.sql
> Error: near line 1: near "´╗┐CREATE": syntax error

You should just use plain UTF-8 text files without BOM.

> I then used the conding UTF-8 without BOM and the command was accepted but
> unfortunately I get the strange result:
> sqlite> select * from imiona;
> 1   01/01/2012  MasławaMieczysława  Mieszka

So you are running sqlite3.exe in a Windows console?

If you redirect the output to a file:
  sqlite3 imieniny.db "select * from imiona;" > output.txt
you will see that the output is correct; it's just the console display
that is wrong.

Configure the console to show a TrueType font (the raster fonts do not
support Unicode), and change the codepage in that console to UTF-8 by
executing "chcp 65001".

(It might be useful for shell.c to automatically call SetConsoleCP and
SetConsoleOutputCP when running on Windows.  When I tried this, output
worked correctly, but inputting non-ASCII characters crashed, but it's
possible that my old compiler (BCC 5.5) doesn't support multibyte
characters correctly.)

> Sent from the SQLite mailing list archive at Nabble.com.

Please do not use services that insert ads into your posts.


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

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Igor Tandetnik]

ZikO  wrote:
> I then used the conding UTF-8 without BOM and the command was accepted but
> unfortunately I get the strange result:
> sqlite> select * from imiona;
> 1   01/01/2012  MasławaMieczysława  Mieszka

This looks like correct UTF-8, it's just that the console window doesn't know 
about that, and treats it as a string in current OEM codepage (so each byte of 
a two-byte sequence is rendered as a separate character).

ł (U+0142, Latin Small Letter L with Stroke) is represented in UTF-8 as two 
bytes C5 82, which happen to correspond to characters ┼ é in codepage 852 
(Central European OEM codepage), as well as several others.

> Now, I don't know how to test if the text is OK because SQLite2009Pro
> displays everything converted to ANSI even ithough I set it to UTF-8 :/
> Perhaps this program is not very good then. What would you recommend,
> instead?

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

I used SQLite 3 Explorer (http://www.singular.gr/sqlite/) and SQLite Manager 
(http://sqlite-manager.googlecode.com/)
-- 
Igor Tandetnik

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

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Kevin Benson]

On Sun, Nov 18, 2012 at 8:57 PM, ZikO  wrote:

> Hello,
> thanks for your answers.
>
> I downloaded both precompiled binaries and shell for windows from
> www.sqlite.org.
>
> The script looks like this:
> CREATE TABLE IF NOT EXISTS imiona (
> id INTEGER PRIMARY KEY,
> data TEXT,
> imie1 TEXT,
> imie2 TEXT,
> imie3 TEXT);
>
> INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
> ('01/01/2012','Masława','Mieczysława','Mieszka'),
> ('16/01/2012','Marcelego','Walerii','Włodzimierza'),
> ('17/09/2012','Franciszka','Lamberty','Narcyza');
>
>
> As you can see, it contains accents. The script was written in Notepad++
> that controls which charset coding is used but I also double checked it in
> Notepad and it indicated UTF-8; it can also be confirmed by reading the
> script text file in Hex editor that shows BOM at the beginning:
> [*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]
>
> The sequence at start 0xEF, 0xBB, 0xBF confirms the file has been created
> using UTF-8. I tested copy / paste between two text editors in windows 7
> and
> everything works ok; texts with accents are copy / pasted without losing
> accents. I have noticed though that when I used command line I got this
> error:
>
> D:\Wydarzenia\Events>sqlite3 imieniny.db < test.sql
> Error: near line 1: near "´╗┐CREATE": syntax error
>
> I then used the conding UTF-8 without BOM and the command was accepted but
> unfortunately I get the strange result:
> sqlite> select * from imiona;
> 1   01/01/2012  MasławaMieczysława  Mieszka
> 2   16/01/2012  Marcelego   Walerii   Włodzimie
> 3   17/09/2012  Franciszka  Lamberty  Narcyza
> 4   01/01/2012  MasławaMieczysława  Mieszka
> 5   16/01/2012  Marcelego   Walerii   Włodzimie
> 6   17/09/2012  Franciszka  Lamberty  Narcyza
>
> Now, I don't know how to test if the text is OK because SQLite2009Pro
> displays everything converted to ANSI even ithough I set it to UTF-8 :/
> Perhaps this program is not very good then. What would you recommend,
> instead?
>
> once again thanks for your effort towards this. I am really really
> surprised
> this behaviour.
>
>
I suspect your problem is not with the importer (sqlite3.exe), but instead
with the exporter (cmd.exe). See here:
http://stackoverflow.com/questions/1259084/what-encoding-code-page-is-cmd-exe-using

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite converts all unicode characters into ANSI

[ZikO]

Hello,
thanks for your answers.

I downloaded both precompiled binaries and shell for windows from
www.sqlite.org.

The script looks like this:
CREATE TABLE IF NOT EXISTS imiona (
id INTEGER PRIMARY KEY,
data TEXT,
imie1 TEXT,
imie2 TEXT,
imie3 TEXT);

INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
('01/01/2012','Masława','Mieczysława','Mieszka'),
('16/01/2012','Marcelego','Walerii','Włodzimierza'),
('17/09/2012','Franciszka','Lamberty','Narcyza');


As you can see, it contains accents. The script was written in Notepad++
that controls which charset coding is used but I also double checked it in
Notepad and it indicated UTF-8; it can also be confirmed by reading the
script text file in Hex editor that shows BOM at the beginning:
[*ef bb bf* 43 52 45 41 54 45 20 54 41 42 4c 45 20] [CREATE TABLE]

The sequence at start 0xEF, 0xBB, 0xBF confirms the file has been created
using UTF-8. I tested copy / paste between two text editors in windows 7 and
everything works ok; texts with accents are copy / pasted without losing
accents. I have noticed though that when I used command line I got this
error:

D:\Wydarzenia\Events>sqlite3 imieniny.db < test.sql
Error: near line 1: near "´╗┐CREATE": syntax error

I then used the conding UTF-8 without BOM and the command was accepted but
unfortunately I get the strange result:
sqlite> select * from imiona;
1   01/01/2012  MasławaMieczysława  Mieszka
2   16/01/2012  Marcelego   Walerii   Włodzimie
3   17/09/2012  Franciszka  Lamberty  Narcyza
4   01/01/2012  MasławaMieczysława  Mieszka
5   16/01/2012  Marcelego   Walerii   Włodzimie
6   17/09/2012  Franciszka  Lamberty  Narcyza

Now, I don't know how to test if the text is OK because SQLite2009Pro
displays everything converted to ANSI even ithough I set it to UTF-8 :/
Perhaps this program is not very good then. What would you recommend,
instead?

once again thanks for your effort towards this. I am really really surprised
this behaviour.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-converts-all-unicode-characters-into-ANSI-tp65589p65602.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which classes of nested requests are not expressible as joins?

[Clemens Ladisch]

emilbarton wrote:
> I wonder which classes of nested SQL requests are not expressible as joins?

I wonder why you want to know this?


Well, anything where you cannot use joins in the first place, such as
UPDATE/DELETE commands:

  DELETE FROM question
  WHERE type_id IN (SELECT id
FROM question_type
WHERE name LIKE '%homework%')


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

[sqlite] Which classes of nested requests are not expressible as joins?

[emilbarton]

Hello,

I wonder which classes of nested SQL requests are not expressible as joins? 
Could you answer in non-jargon with examples please?
Thank you in advance (and thank you for Sqlite by the way!).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users