Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Roger,

Why is APSW not findable on the Pypi infrastructure ? It should be the
place where a newcomer would look for it.

I just published my work as "sqlite_bro" (pip install sqlite_bro), and it
doesn't seem to be too complex.
(just one full day lost to figure it out, in my case)


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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/06/14 14:15, big stone wrote:
> No doubt that ASPW is much much better than SQLite3 standard library
> module.

Just wanted to make sure you knew :-)  Also you are welcome (and
encouraged) to appropriate whatever pieces of APSW code would be helpful
to you (like the dump code).

https://github.com/rogerbinns/apsw/blob/master/LICENSE

> Unfortunately, many People won't install ASPW because : - Python "out
> of the box" SQLite experience has been complex for them (newcomers in
> Python),

Note that by default APSW embeds SQLite statically within.  This means you
don't need to worry about DLL hell or similar issues.  It is exactly one
file and hence far less to go wrong.

> - or more simply, they can't install anything on their school PC.

You don't actually need to install - you just need that one file (apsw.so
or .pyd).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlOMn4wACgkQmOOfHg372QST2wCguYZU7JhKp1SBz8vKqaJYl/65
QvoAoMrotMYObKT2TZ2MNa8MKvffkfbN
=0is+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Roger,

No doubt that ASPW is much much better than SQLite3 standard library module.

Unfortunately, many People won't install ASPW because :
- Python "out of the box" SQLite experience has been complex for them
(newcomers in Python),
- no small utility was there to keep their SQLite interest and motivate
them further,
- or more simply, they can't install anything on their school PC.

The small utility may be a small click-click-run-run  Database Browser in
one python file, with all basics functions.
==> So my questions since a few weeks, for which ASPW can't help me.

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[Roger Binns]

On 06/01/2014 07:30 AM, big stone wrote:

There is indeed an iterdump function in sqlite3 module, that I didn't
notice.


The one in APSW is far more thorough.  If you just have some regular 
data tables then it won't make any difference.  However if you are about 
correctness then be wary of the pysqlite implementation.


The APSW implementation is 250 lines long while pysqlite is 64.  These 
are the problems I see in the pysqlite implementation at 
https://github.com/ghaering/pysqlite/blob/master/lib/dump.py


- Doesn't do the dump inside a transaction so changes while database is 
being dumped may or may not be present and may or may not be consistent


- Deletes all sqlite_sequence contents (APSW only deletes/updates for 
tables in the dump)


- Runs analyze whenever the sqlite_stat1 table is found rather than when 
contents of all tables have been restored


- Only knows about the original analyze (ie not STAT3, STAT4)

- Will create an almighty mess if there are any virtual tables

- Very sloppy with quoting and reserved names, will truncate string 
values at first null


- Doesn't disable/enable foreign key checking during restore

- Doesn't restore various things like user version, or settings like 
page size, auto vacuum


I got bored at this point, but there will be more of these issues.
 > - then understand the "transaction" strange default settings of SQlite3.

pysqlite tries to follow DBAPI which in turn is modelled on other 
databases like Postgres.  That mainly means that transactions are 
automatically started, but must be manually ended.


SQLite 3 also automatically starts transactions, but then automatically 
ends them at the end of the statement.


Personally I find the SQLite behaviour more sensible.  In either case 
when you care about transactions you should manually do the transaction 
boundaries yourself.


pysqlite implements the DBAPI semantics by parsing supplied SQL and does 
occasionally get outwitted.  APSW just lets SQLite do its thing.


http://rogerbinns.github.io/apsw/pysqlite.html

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Domingo,

You were right !
There is indeed an iterdump function in sqlite3 module, that I didn't
notice.

Many thanks,

I just needed to :
- wrap it around "PRAGMA foreign_keys = OFF;" and "PRAGMA foreign_keys =
ON;"
- then understand the "transaction" strange default settings of SQlite3.

For people interested, about the "transaction" strange default settings of
SQlite3 :
- there is a written explanation here :

http://stackoverflow.com/questions/15856976/transactions-with-python-sqlite3
and a video about it, at :

- and a Django conf. video  :
   https://www.youtube.com/watch?v=09tM18_st4I#t=1751

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[E.Pasma]

Op 29 mei 2014, om 15:38 heeft Domingo Alvarez Duarte het volgende  
geschreven:


On Thu, May 29, 2014 at 2:25 PM, E.Pasma  wrote:


Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven:

Hello,


I would like to save my sqlite ':memory:' database into a sql  
command text

file.

With sqlite.dll, what is the procedure/algorithm ?
Is it already explained somewhere on Internet ?


At first look, I may imagine that I need to :
- create tables in a certain order (keeping the comments for  
documentation

purpose)
- then insert datas,
- then create views and index, in another certain order
- then create stored procedures at the end, to avoid changing  
datas during

the  re-feeding of tables

Regards,




Hello, as Your name also appeared in the Python SQLite discussion  
group ,
it is useful to note that a Python based solution can likely be   
obtainded

there. I believe the command line tool of APSW (another Python SQLite
wrapper) includes an appropiarte method. It is to be seen if and  
how this

can be built upon from other projects. Best regards, E. Pasma.


I'm seeing this thread for a while and don't remember anyone  
mentioning the

sqlite3_backup_* api functions, isn't that what are you looking for ?

Cheers !

Hello Domingo, the backup API was mentioned and somehow disappeared  
from the repies. Here below it still is. xxqq, E. Pasma



Op 29 mei 2014, om 13:53 heeft Stephen Chrzanowski het volgende  
geschreven:





3> Use the backup API and skip the whole management of SQL commands as
certain characters just make you pull out your hair.  Unless you're  
bald.

Which would make pulling out your hair difficult.  .. I'd presume...


...





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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Domingo,

I don't know what this API is and if it's usable from standard Python.

As I wanted also to backup my internal Python Procedures, I just finished
my first attempt.

I don't know if I did it in the right order.
==> Casual testers are welcome.

screenshot
https://github.com/stonebig/baresql/blo ...
anager.GIF

source (one file, design to work on Python3, but Python2 may survive ) :
https://github.com/stonebig/baresql/blo ...
manager.py

Procedure :
- launch the python3 program
- open one of your 'test' database (not the big one...)
- try last icon.

function to dump the database is  "savdb_script(self):" line142

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[Domingo Alvarez Duarte]

I'm seeing this thread for a while and don't remember anyone mentioning the
sqlite3_backup_* api functions, isn't that what are you looking for ?

Cheers !


On Thu, May 29, 2014 at 2:25 PM, E.Pasma  wrote:

> Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven:
>
>  Hello,
>>
>> I would like to save my sqlite ':memory:' database into a sql command text
>> file.
>>
>> With sqlite.dll, what is the procedure/algorithm ?
>> Is it already explained somewhere on Internet ?
>>
>>
>> At first look, I may imagine that I need to :
>> - create tables in a certain order (keeping the comments for documentation
>> purpose)
>> - then insert datas,
>> - then create views and index, in another certain order
>> - then create stored procedures at the end, to avoid changing datas during
>> the  re-feeding of tables
>>
>> Regards,
>>
>
>
> Hello, as Your name also appeared in the Python SQLite discussion group ,
> it is useful to note that a Python based solution can likely be  obtainded
> there. I believe the command line tool of APSW (another Python SQLite
> wrapper) includes an appropiarte method. It is to be seen if and how this
> can be built upon from other projects. Best regards, E. Pasma.
>
> ___
> 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] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[E.Pasma]

Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven:


Hello,

I would like to save my sqlite ':memory:' database into a sql  
command text

file.

With sqlite.dll, what is the procedure/algorithm ?
Is it already explained somewhere on Internet ?


At first look, I may imagine that I need to :
- create tables in a certain order (keeping the comments for  
documentation

purpose)
- then insert datas,
- then create views and index, in another certain order
- then create stored procedures at the end, to avoid changing datas  
during

the  re-feeding of tables

Regards,



Hello, as Your name also appeared in the Python SQLite discussion  
group , it is useful to note that a Python based solution can likely  
be  obtainded there. I believe the command line tool of APSW (another  
Python SQLite wrapper) includes an appropiarte method. It is to be  
seen if and how this can be built upon from other projects. Best  
regards, E. Pasma.


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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Simon,

For structure, all is in "select sql from master_sqlite", the big subtility
is to play them in a pertinent order.

For the data themselves,  it is already done in the source sqlite.exe for
the ".dump" function, so I have just to digg .
Apparently , it's in shell.c around line 1275 .

"if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){"

... maybe not so complex ...

 I suppose, for my simple wish case, I can omit the SAVEPOINT/RELEASE  and
  PRAGMA writable_schema=ON parts
(https://www.mail-archive.com/sqlite-users@sqlite.org/msg27037.html)

There is some obscure things in the code I don't know what it is, so I will
suppose they don't exist :

'sqlite_sequence'
WHERE tbl_name LIKE shellstatic()

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[Stephen Chrzanowski]

I agree with Simon, there is no direct way to export your data as SQL
commands.  But there are a few things you can do;

1> Code it out yourself, but you'll need to be aware of how you're dealing
with the order you need
2> As you're making changes to the database, write out the
inserts/updates/deletes into another file, or, put them into another
:memory: table and export as needed
3> Use the backup API and skip the whole management of SQL commands as
certain characters just make you pull out your hair.  Unless you're bald.
Which would make pulling out your hair difficult.  .. I'd presume...


On Thu, May 29, 2014 at 5:59 AM, big stone  wrote:

> Hello,
>
> I would like to save my sqlite ':memory:' database into a sql command text
> file.
>
> With sqlite.dll, what is the procedure/algorithm ?
> Is it already explained somewhere on Internet ?
>
>
> At first look, I may imagine that I need to :
> - create tables in a certain order (keeping the comments for documentation
> purpose)
> - then insert datas,
> - then create views and index, in another certain order
> - then create stored procedures at the end, to avoid changing datas during
> the  re-feeding of tables
>
> Regards,
> ___
> 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] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[Simon Slavin]

On 29 May 2014, at 10:59am, big stone  wrote:

> I would like to save my sqlite ':memory:' database into a sql command text
> file.

There are no functions built into SQLite which turn schema or data into SQL 
commands.  The Shell Tool can do it, but the code which does it is built into 
the shell tool, not the SQLite API.

I have written code in JavaScript which does what you describe (apart, of 
course, from stored procedures which don't exist in SQLite) but I had to do 
just that: write my own code.  I argued some years ago that SQLite could 
usefully include such a function but people didn't seem to think it would be 
worth the extra code size.

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

[sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hello,

I would like to save my sqlite ':memory:' database into a sql command text
file.

With sqlite.dll, what is the procedure/algorithm ?
Is it already explained somewhere on Internet ?


At first look, I may imagine that I need to :
- create tables in a certain order (keeping the comments for documentation
purpose)
- then insert datas,
- then create views and index, in another certain order
- then create stored procedures at the end, to avoid changing datas during
the  re-feeding of tables

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